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.