loop through an array and execute union all query with Dataform

Question

I'm working with Dataform plugged to BigQuery.

I have the following script:

function render_script(table, dimensions, date) {
  return `
      select
      '${dimensions.map(field => `${field} is null`)}' as failing_row_condition,
      *
      from ${table}
      where 
        ${date} >= DATE_SUB(CURRENT_DATE(), INTERVAL 3 day) 
      and ${dimensions.map(field => `${field}`)} IS NULL
    `;
}

module.exports = { render_script };

and the following statement in my sqlx file:

${script_builder.render_script(ref("table"),
                               ["dim1"],
                               "date"
                               )}  

I'm not sure I'm taking the right approach but I'd like to be able to loop through a list of dimensions to execute my render_script and "glue" all this with UNION ALL.

Basically right now I have this:

${script_builder.render_script(ref("table"),
                               ["dim1"],
                               "date"
                               )}

UNION ALL

${script_builder.render_script(ref("table"),
                               ["dim2"],
                               "date"
                               )}

How can I programmatically re-write this? within the sqlx file if possible.

Answer 1

Javascript block can be created within SQLX file.

This is documented here. Here is an example:

js {
const tables = [
'table1',
'table2',
'table3'
];

const selectAllFromTables = tables.map(t => {
    return `select * from \`${t}\``;
}).join(`
union all
`);
}

${selectAllFromTables}