Is there a way to declare sources dynamically in a separate sources.js file in Dataform?

[dogvile]

I want to read all tables from a specific dataset in BigQuery's INFORMATION_SCHEMA.TABLES and dynamically populate declare() statements in sources.js.

Example of manually declared sources:
javascript declare({ schema: "demo", name: "demo1" }); declare({ schema: "demo", name: "demo2" }); declare({ schema: "demo", name: "demo3" })

Instead of hardcoding, I’d like to dynamically retrieve the list of tables from a configuration table or INFORMATION_SCHEMA.TABLES and auto-generate these declarations. How can I fetch table names dynamically inside sources.js and use them in declare()?

Would love to hear best practices or workarounds! @ms4446   Any suggestions?

[MarvinLlamas]

Hi @dogvile,

Welcome to Google Cloud Community!

It looks like you are trying to dynamically declare your BigQuery sources in Dataform, retrieving schema and table names programmatically, so that Dataform recognizes new or existing BigQuery tables without manual updates.

Here are the potential ways that might help with your use case:

• Query BigQuery INFORMATION_SCHEMA.TABLES: You may want to use the INFORMATION_SCHEMA.TABLES to get a list of tables. In your sources.js file, you can execute a BigQuery query against INFORMATION_SCHEMA.TABLES that retrieves the table names.
• Iterate and Declare: After you obtain the table names, you'll loop through your results and utilize the declare() function to dynamically set up your source declarations for each table.

Note: Ensure that you have necessary permission to access tables in BigQuery. To declare a data source in Dataform, you must also have the roles/dataform.editor IAM role on workspaces. 

Was this helpful? If so, please accept this answer as “Solution”. If you need additional assistance, reply here within 2 business days and I’ll be happy to help.

[dogvile]

Thank you, @MarvinLlamas, for your detailed response, and sorry for the delayed reply!

I understand the approach of querying INFORMATION_SCHEMA.TABLES to retrieve the table names dynamically. However, in JavaScript, we can't directly query BigQuery and store the results as an intermediate variable before iterating over them in sources.js. Unless I’m missing something, this seems to be a limitation.

Would you happen to have a recommended workaround for dynamically fetching and injecting these declarations into sources.js? Perhaps using an external script or automation process?

Appreciate your insights!