Wildcards in Dataform sqlx files?

[jmadsen]

I'm trying to figure out the best way to reference multiple tables with the same schema across multiple datasets in, for example, definitions and includes files.

For example, I have a project that has different datasets for each client. And inside those datasets they have FBADS_AD_* sharded tables. My goal would be to dynamically reference all applicable tables and create report-ready views for each table.

Any help would be much appreciated!

[ms4446]

To reference multiple tables with the same schema across multiple datasets in Dataform, you can use the following approach:

1. Use JavaScript to loop through the datasets and tables.
2. For each table, construct the table name and use the reffunction to reference the table.

The following code shows an example of how to do this:

const CLIENTS = ['client1', 'client2', 'client3'];
const TABLE_PREFIX = 'FBADS_AD_';

const fbadsAdTables = [];

CLIENTS.forEach(client => {
  const tableName = `${TABLE_PREFIX}${client}`;
  const tableRef = ref(client, tableName);

  fbadsAdTables.push(tableRef);
});

// Use the fbadsAdTables variable to reference the tables in your definitions and includes files.

Create report-ready views for each table

Once you have referenced all of the tables, you can use Dataform's publish method to create a report-ready view for each table. The publish method allows you to specify the table name and the SQL query that you want to use to create the view.

The following code shows an example of how to create a report-ready view for each table in the fbadsAdTables variable:

fbadsAdTables.forEach(tableRef => {
  dataform.publish(`${tableRef}_report_ready`)
    .query(ctx => `
      SELECT
        campaign_id,
        ad_set_id,
        ad_id,
        impressions,
        clicks,
        spend
      FROM
        ${tableRef}
    `);
});

[jmadsen]

Thank you so much! I was able to figure it out with your help! ..even though I am still absolutely resistant to learning javascript ha

[rambozzz]

Hi and thank you for this answer. Everything seems pretty clear in the logic, but I'm missing some basics, which you already assumed (I guess). Dataform allows to wrap Javascript within a SQLX script. So the question is: the first piece of code in your example stays in a .js file? Same for the second piece of code: is it a SQLX that wraps a piece of Javascript or viceversa? 

[ms4446]

Here are two options on  how SQLX, JavaScript, and file types work together: 

Option 1: Single .sqlx File

config { // SQLX configuration options } 

// JavaScript block for dynamic table references 
{ 
    const CLIENTS = ['client1', 'client2', 'client3']; 
    const TABLE_PREFIX = 'FBADS_AD_'; 
    const fbadsAdTables = CLIENTS.map(client => { 
        const tableName = `${TABLE_PREFIX}${client}`; 
        return `\`${client}.${tableName}\``;  
    }); 

    // Expose fbadsAdTables for later use in SQL queries 
    publish("dynamic_views", { 
        type: "view", 
        dependencies: fbadsAdTables, // Illustrates dependency management
        query: ctx => { 
            // Dynamically generate SQL for each table reference 
            // Note: This step is conceptual; actual implementation may vary 
            return fbadsAdTables.map(tableRef => ` 
                SELECT campaign_id, ad_set_id, ad_id, impressions, clicks, spend 
                FROM ${tableRef} 
            `).join(" UNION ALL "); 
        } 
    }); 
} 

Option 2: Separate .js and .sqlx Files

• definitions/table_utils.js:

  // JavaScript utility function to generate table references 
  function generateTableReferences(clients, tablePrefix) { 
      return clients.map(client => { 
          const tableName = `<span class="math-inline">\{tablePrefix\}</span>{client}`; 
          return `\`${client}.${tableName}\``; 
      }); 
  } 

• my_workflow.sqlx:

  config { // SQLX configuration options } 
  
  // Including JavaScript utility functions 
  include "definitions/table_utils.js"; 
  
  const fbadsAdTables = generateTableReferences(['client1', 'client2', 'client3'], 'FBADS_AD_'); 
  
  // Generating report-ready views 
  // Note: Use fbadsAdTables to generate views, including any additional transformations.

Key Refinements:

• Conciseness: Removed the slightly redundant comment in Option 1.
• Specificity: Adjusted the final note in Option 2 to mention potential additional transformations.