How to reference an existing table with dynamic dataset suffix

[claracena]

Hello everyone,

We are in the process of migrating all our jobs/tasks from common Scheduled Queries to Dataform. Everything works fine, except when we need to query an existing table from the same dataset. Sometimes we need to either read or delete/insert data from/to an existing table that resides in the same dataset that the job uses.

For example, we usually have a history table that, during the Dataform process, we use an "operation" SQLX to insert new records. That table is not naturally referenced by Dataform (as it doesn't creates it) so what we normally do is a "declaration" SQLX where we reference that table.

The problem arises when the repository setting has a Schema suffix. We usually use ${workspaceName} to add a new dataset for each new workspace do manual runs don't interfere with the production tables. How can I properly reference the right schema?

The solution I've found is to set the declaration SQLX like:

config {
    type: "declaration",
    database: "my-bigquery-project",
    schema: dataform.projectConfig.defaultSchema + dataform.projectConfig.vars.sufix_fix,
    name: "my_hisotric_table"
}

where the "sufix_fix" variable holds an underscore and the name of the workspace and then run a CI pipeline in Gitlab that changes the variable "sufix_fix" to an empty string, but I'm trying to find a better way as Gitlab pipelines that edit the files in branches are not really that trustable.

Is there a better, more "native" way of doing this in Dataform?

Thanks in advance!

[caryna]

Hi @claracena,

Welcome to Google Cloud Community!

One native way to handle your schema suffix is declaring custom variables through your workflow_settings or dataform.json and setting your suffix name as your value. 

"vars" : {

    "yourVariableName":"workspaceName_schemaSuffix_value"

  }

Then update your source declaration in SQLX file:

schema: dataform.projectConfig.defaultSchema + dataform.projectConfig.vars.yourVariableName

For further details, you may find valuable insights in this related discussion on the Google Cloud Community.

I hope the above information is helpful.