Declare information_schema to multiple projects as a source in Dataform

[benjikj-cera]

Hi all,

We are wanting to create a Dataform model that combines all of the BigQuery INFORMATION_SCHEMA.JOBS data from multiple projects into one table. The way we do this in pure SQl would effectively be like:

SELECT * FROM `<project_id>.<region_id>.INFORMATION_SCHEMA.JOBS`
UNION ALL
SELECT * FROM `<project_id>.<region_id>.INFORMATION_SCHEMA.JOBS`

We need to include a project and region ID in order to point to the correct external (external to where the dataform job is run) project.

However, Dataform has a problem with declaring a source that contains 4 levels of table reference. The source declaration only has the 3 levels of database (project_id), schema (dataset_id) and name (table_id). When I try and combine two of these elements with a full-stop in between it errors saying: " Action target names cannot include '.' "

FYI, this is actually only a problem/error in later versions of Dataform, which is actually the real reason why this has started becoming an issue for us, as we already have this model setup in the old version (2.0.1) but it's erroring in the new version (3.0.8).

The obvious workaround for this is to just hard-code the reference and not declare a source, but this is obviously only a workaround, as we should be using source declarations.

[marckevin]

Hi @benjikj-cera,

Welcome to Google Cloud Community!

The error you're encountering Action target names cannot include '.' means that your action target’s name contains a dot, which is not permitted since it expects a 3-part identifier: source("<database>", "<schema>", "<name>"). As per my understanding of your setup, you're trying to use a source that contains 4 levels of table reference. The current version of Dataform specifically interprets the dot (.) character as a separator for database, schema, and name parameters, hence it’s detecting Action target names cannot include '.'

Here are some suggestions that may help resolve the issue:

• Create predefined tables or views that encapsulate your 4-level of your table reference.
• Check this documentation on how to declare a data source for complete guidance.

If the issue persists, I recommend reaching out to Google Cloud Support for further assistance, as they can provide insights into whether this behavior is specific to your project.

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.