Escape reserved words in templates for BigQuery

[lbertelo01]

The note_nlp table uses the reserverd word 'offset' which needs to be escaped in the template(s).

During an etl test for note_nlp, this template failed: https://github.com/RADar-AZDelta/Rabbit-in-a-Blender/blob/main/src/riab/etl/bigquery/templates/etl/%7Bomop_table%7D_merge.sql.jinja

This is the query that fails:

[FannyDHondt]

Note_nlp werkt nog niet. Ik krijg volgende error

Name "offset" not found inside t at [13:15]

Query:

CREATE OR REPLACE TABLE `azd-dev-data-fdhondt01.omop.note_nlp`
LIKE `azd-dev-data-fdhondt01.omop.note_nlp`AS (
    WITH cte_uploaded_tables AS (
        SELECT *
        FROM `azd-dev-data-fdhondt01.work.note_nlp__upload__davinci_prostaat`
    ), cte_keys_swapped AS (
        SELECT
            swap_pk.y as `note_nlp_id`,
            IFNULL(swap_fk0.y, 0) as `note_id`,
            IFNULL(swap_ci0.conceptId, 0) as `section_concept_id`,
            t.`snippet`,
            t.`"offset"`,
            t.`lexical_variant`,
            IFNULL(swap_ci1.conceptId, 0) as `note_nlp_concept_id`,
            IFNULL(swap_ci2.conceptId, 0) as `note_nlp_source_concept_id`,
            t.`nlp_system`,
            t.`nlp_date`,
            t.`nlp_datetime`,
            t.`term_exists`,
            t.`term_temporal`,
            t.`term_modifiers`
        FROM cte_uploaded_tables t
        LEFT OUTER JOIN `azd-dev-data-fdhondt01.work.note_nlp__section_concept_id_usagi` swap_ci0 on swap_ci0.sourceCode = t.`section_concept_id`
                and swap_ci0.mappingStatus = 'APPROVED'
        LEFT OUTER JOIN `azd-dev-data-fdhondt01.work.note_nlp__note_nlp_concept_id_usagi` swap_ci1 on swap_ci1.sourceCode = t.`note_nlp_concept_id`
                and swap_ci1.mappingStatus = 'APPROVED'
        LEFT OUTER JOIN `azd-dev-data-fdhondt01.work.note_nlp__note_nlp_source_concept_id_usagi` swap_ci2 on swap_ci2.sourceCode = t.`note_nlp_source_concept_id`
                and swap_ci2.mappingStatus = 'APPROVED'
        INNER JOIN `azd-dev-data-fdhondt01.work.note_nlp_id_swap` swap_pk on swap_pk.x = t.note_nlp_id
            and IFNULL(swap_pk.`section_concept_id`, 0) = IFNULL(swap_ci0.conceptId, 0)
            and IFNULL(swap_pk.`note_nlp_concept_id`, 0) = IFNULL(swap_ci1.conceptId, 0)
            and IFNULL(swap_pk.`note_nlp_source_concept_id`, 0) = IFNULL(swap_ci2.conceptId, 0)
        LEFT OUTER JOIN `azd-dev-data-fdhondt01.work.note_id_swap` swap_fk0 on swap_fk0.x = t.`note_id` 
    ), cte_duplicates AS (
        SELECT *
            , ROW_NUMBER() OVER(
                PARTITION BY
                note_nlp_id
                , `section_concept_id`
                , `note_nlp_concept_id`
                , `note_nlp_source_concept_id`
            ) AS rn
        FROM cte_keys_swapped
    )
    SELECT * EXCEPT(rn)
    FROM cte_duplicates
    WHERE rn = 1
)

Ik heb de query eens gerund in BigQuery en het werkt als t."offset", veranderd wordt in t.offset,