[CT-3074] [Bug] Quoting not respected in freshness query

[albertsgrc]

Is this a new bug in dbt-core?

• I believe this is a new bug in dbt-core
• I have searched the existing issues, and I could not find an existing issue for this bug

Current Behavior

When quoting is enabled for a source table column, the freshness query does not quote the loaded_at_field column.

Expected Behavior

The loaded_at_field column is quoted in the freshness query when quoting is enabled for the column in the source table definition.

Steps To Reproduce

1. Create a table with a lowercase loaded_at timestamp column in a Snowflake data warehouse.
2. Add a source for the table in DBT. Set the source setting loaded_at_field: loaded_at, and configure freshness checks. Set quote: true in the column configuration for loaded_at.
3. Run dbt source freshness.
4. Observe the error invalid identifier 'LOADED_AT'.

Relevant log output

07:51:19  Database Error in source search_analytics_all_fields (models/staging/google_search_console/google_search_console__sources.yml)
07:51:19    000904 (42000): SQL compilation error: error line 2 at position 10
07:51:19    invalid identifier '_AIRBYTE_EXTRACTED_AT'

Environment

- OS: macOS Monterrey 12.6
- Python: 3.10.12
- dbt: 1.5.1

Which database adapter are you using with dbt?

snowflake

Additional Context

No response

[graciegoheen]

Hello! Thanks for opening this up.

The input to the loaded_at_field is actually just any string (you could put something like loaded_at_field: "convert_timezone('UTC', 'Australia/Sydney', created_at_local)" as your input) - it's not actually mapping to a specific column in your source. Because of this, it's not aware that quote: true.

You should be able to add quotes to your string like this loaded_at_field: "'loaded_at'". Let me know if that fixes this issue for you.

This is similar to these issues:

• Quoting in Snowflake Merge Statements #3129
• DBT relationship test does not correctly support column quoting #2468
• dbt does not appropriately quote column names for incremental models and seed files. #1847

where we're just handing inputs as a string.

We could try to do something smarter here and check if the column you've specified for loaded_at_field exists in the table:

dbt-core/core/dbt/include/global_project/macros/adapters/freshness.sql

Line 5 in 7e2a08f

{% macro default__collect_freshness(source, loaded_at_field, filter) %}

[albertsgrc]

Ah, gotcha! Thanks a lot for the answer Grace. I remember trying that and it did not work back then, probably for some other reason.

Now I tried loaded_at_field: '"loaded_at"' and it did work.

[HristiyanIvanovski]

Hello, stumbled upon this one because I need to cast a quoted source column to timestamp when specifying loaded_at_field for table:

 tables:
      - name: name
        identifier: id
        freshness:
          warn_after:
            count: 30
            period: day
        loaded_at_field: ""Month"::timestamp"

getting error:

- Runtime Error
    Syntax error near line 15
    ------------------------------
    12 |           warn_after:
    13 |             count: 30
    14 |             period: day
    15 |         loaded_at_field: ""Month"::timestamp"
    
    Raw Error:
    ------------------------------
    while parsing a block mapping
      in "", line 9, column 9
    did not find expected key
      in "", line 15, column 28

db: Snowflake
source column: "Month" (quoted)
Running with dbt=1.7.18
Registered adapter: snowflake=1.7.5

Is there anything that I am missing? -I tried all possible variations of quoting but it won't work.
looked at issue #2468 as well

Thanks :)