google-bigquerydataformgoogle-dataform

Defining Sharded BigQuery Tables on Dataform

Which is the best practice to define/handle BigQuery sharded tables like table_abc_* in Dataform? 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? I was thinking to create a view on top of it selecting all the fields with the _table_suffix statement but this seems to me a workaround more than a way to define those in a proper way.

Solution

  • Quoting the answer I found on GitHub:

    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"