Using bigquery wildcard tables with source macro

Help
1

I´d like to use bigquery wildcard tables as source in a dbt pipeline but I´m not sure how can I do this without update the YML code everyday…

For example, I have a wildcard table like: table_name_2024* and its subtables table_name_202401*, table_name_202402*, …, table_name_202408* and for each one there are 30 or 31 subtables like table_name_20240801, table_name_20240802, …

Well, if I declared in my src_file a table as table_name_20240802, this is a fixed name and I will have to edit and update the file name everyday… what can I do to dbt understand that my table is a wildcard table and change the name automatically based on current date?

Use table_name_202408* or table_name_2024* its not a solution because I will need to edit the YML source file every month or every year…

Is there any solution to define a dynamic table_name into the YML source file?

Thanks in advance.

2

You should be able to use Jinja for this.

Option 1: Generate the list of table names into a jinja list, based on the current date, and then generate the sql to union/join/whatever to process them all.

Option 2: Use run_query() macro to query system tables and get the exact list of tables, and then generate the sql to union/join/whatever to process them all.

Note: @Matthew originally posted this reply in Slack. It might not have transferred perfectly.