Bigquery sharded table reference

[corzani]

Hi,
I was wondering which is the best practice to define/handle BigQuery sharded tables like table_abc_* in Dataform.
Let's say we have a few sharded tables called table_abc_2018, table_abc_2019, table_abc_2020:
Unfortunately those tables are not under our control and so we are using something like:

# 2018 data 
select * from table_abc_* where _TABLE_SUFFIX = "2018"

Is there a way to reference them with ${ref(...)} and using a sort of _TABLE_SUFFIX syntax to select the one we need via variables like in the example above? Is there any example we can check?

We would like to understand how to handle an hypothetical case like:

select * from ${ref("sharded_table_*")} where _TABLE_SUFFIX = "2018"

Thanks,
Jacopo

[corzani]

I asked this question on stack-overflow but no one can answer. I noticed that I can't find an answer even in the dataform discussion board itself. It must be a very stupid question or something like the meaning of life...

[astorrs]

@corzani As long as you define the source table first:

table_abc.sqlx

config {
  type: "declaration",
  name: "table_abc_*"
}

Then you can reference it in your other SQLX files as you wanted:

select * from ${ref("table_abc_*")} where _TABLE_SUFFIX = "2018"

[corzani]

Thanks @astorrs , the solution you mentioned worked.
It seems the star character is always needed even in the declaration. Something like:

config {
  type: "declaration",
  name: "table_abc_*"
}

[astorrs]

Yeah that was a typo on my end (I’ve fixed it for anyone in the future). Glad it’s working for you. 👍