BigQuery breaks in `get_table_update_ts` due to missing backticks

[davidwitk]

get_table_update_ts.sql queries the information schema several times, for example here:

upstream-prod/macros/get_table_update_ts.sql

Line 13 in acbb79b

{{ relation.database }}.information_schema.tables

The queries fail on BigQuery because it expects backticks. For example, changing

{{ relation.database }}.information_schema.tables

to

`{{ relation.database }}.information_schema.tables`

does the fix. I guess one way to handle this could be to handle BigQuery in a separate condition?

[LewisDavies]

Hey @davidwitk, thanks for raising this and sorry for the long delayed reply. This seems odd because BigQuery is already handled separately here, just below the line you linked too.

I don't have a BQ project to test with at the moment but I should really set one up. Could you share an example error message in the meantime?

[davidwitk]

Hey @LewisDavies, sorry for the delay on my side as well.
Ah yes, I've linked the wrong section! Cool, we can handle it it in the existing BigQuery section.

The error message is

Syntax error: Missing whitespace between literal and alias at [10:38]

Which comes from the query

            select
                coalesce(
                    timestamp_millis(meta.last_modified_time), 
                    inf_sch.creation_time
                ) as last_altered
            from
                data-warehouse-dev-43d37366.dwitkowski_temporary.INFORMATION_SCHEMA.TABLES inf_sch
                left join data-warehouse-dev-43d37366.dwitkowski_temporary.__TABLES__ meta
                    on inf_sch.table_catalog = meta.project_id
                    and inf_sch.table_schema = meta.dataset_id
                    and inf_sch.table_name = meta.table_id
            where
                inf_sch.table_catalog = 'data-warehouse-dev-43d37366'
                and inf_sch.table_schema = 'dwitkowski_temporary'
                and inf_sch.table_name = 'stg_daily__identity_check'

Our dev project contains numbers 🙈

Adding the backticks does the fix:

            select
                coalesce(
                    timestamp_millis(meta.last_modified_time), 
                    inf_sch.creation_time
                ) as last_altered
            from
                `data-warehouse-dev-43d37366.dwitkowski_temporary.INFORMATION_SCHEMA.TABLES` inf_sch
                left join `data-warehouse-dev-43d37366.dwitkowski_temporary.__TABLES__` meta
                    on inf_sch.table_catalog = meta.project_id
                    and inf_sch.table_schema = meta.dataset_id
                    and inf_sch.table_name = meta.table_id
            where
                inf_sch.table_catalog = 'data-warehouse-dev-43d37366'
                and inf_sch.table_schema = 'dwitkowski_temporary'
                and inf_sch.table_name = 'stg_daily__identity_check'