elementary icon indicating copy to clipboard operation
elementary copied to clipboard
verified publisher icon elementary-data

Elementary timestamp_column doesn't respect dbt column quote config

Open jeremyyeo opened this issue 7 months ago • 1 comments

Describe the bug The column anomaly test does not appear to respect the column quote config. Out of the box dbt tests do.

To Reproduce With a project setup like so:

# packages.yml
packages:
  - package: elementary-data/elementary
    version: 0.18.2

# dbt_project.yml
name: my_dbt_project
profile: all
version: "1.0.0"

models:
  elementary:
    +enabled: True
    +schema: elementary
  my_dbt_project:
    +materialized: table

# models/schema.yml
models:
  - name: events
    config:
      elementary:
        timestamp_column: JeremyLoadedAt
    columns:
      - name: JeremyLoadedAt 
        quote: true
        tests:
          - not_null
      - name: event_count
        tests:
          - elementary.column_anomalies:
              column_anomalies:
                - average
              anomaly_sensitivity: 2
              time_bucket:
                period: day
                count: 1

-- models/events.sql
select '2025-01-01'::date as "JeremyLoadedAt", 1 as event_count
union 
select '2025-01-02'::date as "JeremyLoadedAt", 2 as event_count
union
select '2025-01-03'::date as "JeremyLoadedAt", 3 as event_count
union
select '2025-01-04'::date as "JeremyLoadedAt", 10 as event_count

$ dbt build

04:31:23  Running with dbt=1.9.4
04:31:24  Registered adapter: snowflake=1.9.2
04:31:24  Unable to do partial parsing because of a version mismatch
04:31:26  Found 30 models, 2 operations, 2 data tests, 1287 macros
04:31:26  
04:31:26  Concurrency: 1 threads (target='sf')
04:31:26  
04:31:29  
IMPORTANT - Starting from dbt 1.8, users must explicitly allow packages to override materializations. 
Elementary requires this ability to support collection of samples and failed row count for dbt tests.
Please add the following flag to dbt_project.yml to allow it:

flags:
  require_explicit_package_overrides_for_builtin_materializations: false

Notes - 
* This is a temporary measure that will result in a deprecation warning, please ignore it for now. Elementary is working with the dbt-core team on a more permanent solution.
* This message can be muted by setting the 'mute_ensure_materialization_override' var to true.
        
04:31:31  1 of 1 START hook: elementary.on-run-start.0 ................................... [RUN]
04:31:31  1 of 1 OK hook: elementary.on-run-start.0 ...................................... [OK in 2.00s]
04:31:31  
04:31:31  1 of 32 START sql incremental model sch_elementary.data_monitoring_metrics ..... [RUN]
04:31:35  1 of 32 OK created sql incremental model sch_elementary.data_monitoring_metrics  [SUCCESS 0 in 4.38s]
04:31:35  2 of 32 START sql incremental model sch_elementary.dbt_columns ................. [RUN]
04:31:40  2 of 32 OK created sql incremental model sch_elementary.dbt_columns ............ [SUCCESS 0 in 4.70s]
04:31:40  3 of 32 START sql incremental model sch_elementary.dbt_exposures ............... [RUN]
04:31:44  3 of 32 OK created sql incremental model sch_elementary.dbt_exposures .......... [SUCCESS 0 in 4.02s]
04:31:44  4 of 32 START sql incremental model sch_elementary.dbt_invocations ............. [RUN]
04:31:48  4 of 32 OK created sql incremental model sch_elementary.dbt_invocations ........ [SUCCESS 0 in 4.42s]
04:31:48  5 of 32 START sql incremental model sch_elementary.dbt_metrics ................. [RUN]
04:31:52  5 of 32 OK created sql incremental model sch_elementary.dbt_metrics ............ [SUCCESS 0 in 4.04s]
04:31:52  6 of 32 START sql incremental model sch_elementary.dbt_models .................. [RUN]
04:31:57  6 of 32 OK created sql incremental model sch_elementary.dbt_models ............. [SUCCESS 0 in 4.47s]
04:31:57  7 of 32 START sql incremental model sch_elementary.dbt_run_results ............. [RUN]
04:32:01  7 of 32 OK created sql incremental model sch_elementary.dbt_run_results ........ [SUCCESS 0 in 4.31s]
04:32:01  8 of 32 START sql incremental model sch_elementary.dbt_seeds ................... [RUN]
04:32:05  8 of 32 OK created sql incremental model sch_elementary.dbt_seeds .............. [SUCCESS 0 in 3.92s]
04:32:05  9 of 32 START sql incremental model sch_elementary.dbt_snapshots ............... [RUN]
04:32:09  9 of 32 OK created sql incremental model sch_elementary.dbt_snapshots .......... [SUCCESS 0 in 3.89s]
04:32:09  10 of 32 START sql incremental model sch_elementary.dbt_source_freshness_results  [RUN]
04:32:12  10 of 32 OK created sql incremental model sch_elementary.dbt_source_freshness_results  [SUCCESS 0 in 3.49s]
04:32:13  11 of 32 START sql incremental model sch_elementary.dbt_sources ................ [RUN]
04:32:17  11 of 32 OK created sql incremental model sch_elementary.dbt_sources ........... [SUCCESS 0 in 4.16s]
04:32:17  12 of 32 START sql incremental model sch_elementary.dbt_tests .................. [RUN]
04:32:21  12 of 32 OK created sql incremental model sch_elementary.dbt_tests ............. [SUCCESS 0 in 4.47s]
04:32:21  13 of 32 START sql incremental model sch_elementary.elementary_test_results .... [RUN]
04:32:25  13 of 32 OK created sql incremental model sch_elementary.elementary_test_results  [SUCCESS 0 in 3.91s]
04:32:25  14 of 32 START sql table model sch_elementary.metadata ......................... [RUN]
04:32:26  14 of 32 OK created sql table model sch_elementary.metadata .................... [SUCCESS 1 in 0.87s]
04:32:26  15 of 32 START sql incremental model sch_elementary.schema_columns_snapshot .... [RUN]
04:32:30  15 of 32 OK created sql incremental model sch_elementary.schema_columns_snapshot  [SUCCESS 0 in 3.75s]
04:32:30  16 of 32 START sql table model sch.events ...................................... [RUN]
04:32:31  16 of 32 OK created sql table model sch.events ................................. [SUCCESS 1 in 0.91s]
04:32:31  17 of 32 START sql view model sch_elementary.metrics_anomaly_score ............. [RUN]
04:32:31  17 of 32 OK created sql view model sch_elementary.metrics_anomaly_score ........ [SUCCESS 1 in 0.86s]
04:32:31  18 of 32 START sql view model sch_elementary.monitors_runs ..................... [RUN]
04:32:32  18 of 32 OK created sql view model sch_elementary.monitors_runs ................ [SUCCESS 1 in 0.63s]
04:32:32  19 of 32 START sql view model sch_elementary.job_run_results ................... [RUN]
04:32:33  19 of 32 OK created sql view model sch_elementary.job_run_results .............. [SUCCESS 1 in 0.59s]
04:32:33  20 of 32 START sql view model sch_elementary.model_run_results ................. [RUN]
04:32:34  20 of 32 OK created sql view model sch_elementary.model_run_results ............ [SUCCESS 1 in 1.14s]
04:32:34  21 of 32 START sql view model sch_elementary.seed_run_results .................. [RUN]
04:32:35  21 of 32 OK created sql view model sch_elementary.seed_run_results ............. [SUCCESS 1 in 0.80s]
04:32:35  22 of 32 START sql view model sch_elementary.snapshot_run_results .............. [RUN]
04:32:35  22 of 32 OK created sql view model sch_elementary.snapshot_run_results ......... [SUCCESS 1 in 0.80s]
04:32:35  23 of 32 START sql view model sch_elementary.alerts_dbt_source_freshness ....... [RUN]
04:32:38  23 of 32 OK created sql view model sch_elementary.alerts_dbt_source_freshness .. [SUCCESS 1 in 0.98s]
04:32:38  24 of 32 START sql view model sch_elementary.dbt_artifacts_hashes .............. [RUN]
04:32:39  24 of 32 OK created sql view model sch_elementary.dbt_artifacts_hashes ......... [SUCCESS 1 in 0.50s]
04:32:39  25 of 32 START sql view model sch_elementary.alerts_anomaly_detection .......... [RUN]
04:32:40  25 of 32 OK created sql view model sch_elementary.alerts_anomaly_detection ..... [SUCCESS 1 in 0.83s]
04:32:40  26 of 32 START sql view model sch_elementary.alerts_dbt_tests .................. [RUN]
04:32:41  26 of 32 OK created sql view model sch_elementary.alerts_dbt_tests ............. [SUCCESS 1 in 0.98s]
04:32:41  27 of 32 START sql view model sch_elementary.alerts_schema_changes ............. [RUN]
04:32:42  27 of 32 OK created sql view model sch_elementary.alerts_schema_changes ........ [SUCCESS 1 in 1.03s]
04:32:42  28 of 32 START sql incremental model sch_elementary.test_result_rows ........... [RUN]
04:32:45  28 of 32 OK created sql incremental model sch_elementary.test_result_rows ...... [SUCCESS 0 in 3.76s]
04:32:45  29 of 32 START test elementary_column_anomalies_events_2__average__event_count__day__1  [RUN]
04:32:46  Elementary: Started running data monitors on: DB.SCH.EVENTS event_count
04:32:47  29 of 32 ERROR elementary_column_anomalies_events_2__average__event_count__day__1  [ERROR in 1.26s]
04:32:47  30 of 32 START test not_null_events__JeremyLoadedAt_ ........................... [RUN]
04:32:47  30 of 32 PASS not_null_events__JeremyLoadedAt_ ................................. [PASS in 0.43s]
04:32:47  31 of 32 START sql view model sch_elementary.anomaly_threshold_sensitivity ..... [RUN]
04:32:48  31 of 32 OK created sql view model sch_elementary.anomaly_threshold_sensitivity  [SUCCESS 1 in 0.63s]
04:32:48  32 of 32 START sql view model sch_elementary.alerts_dbt_models ................. [RUN]
04:32:48  32 of 32 OK created sql view model sch_elementary.alerts_dbt_models ............ [SUCCESS 1 in 0.74s]
04:32:48  
04:32:53  1 of 1 START hook: elementary.on-run-end.0 ..................................... [RUN]
04:32:53  1 of 1 OK hook: elementary.on-run-end.0 ........................................ [OK in 4.41s]
04:32:54  
04:32:54  Finished running 15 incremental models, 2 project hooks, 2 table models, 2 data tests, 13 view models in 0 hours 1 minutes and 28.48 seconds (88.48s).
04:32:54  
04:32:54  Completed with 1 error, 0 partial successes, and 0 warnings:
04:32:54  
04:32:54    Database Error in test elementary_column_anomalies_events_2__average__event_count__day__1 (models/schema.yml)
  000904 (42000): SQL compilation error: error line 53 at position 13
  invalid identifier 'JEREMYLOADEDAT'
04:32:54  
04:32:54  Done. PASS=33 WARN=0 ERROR=1 SKIP=0 TOTAL=34

If we look closer at the debug logs, we can see that the elementary_column_anomalies_events_2__average__event_count__day__1 test has run a SQL query like (just sharing a bit of the code for brevity):

...
 as timestamp) as start_bucket_in_data
            from monitored_table
            where
                cast(JeremyLoadedAt as timestamp) >= (select min(edr_bucket_start) from buckets)
                and cast(JeremyLoadedAt as timestamp) < (select max(edr_bucket_end) from buckets)
        ),
...

where JeremyLoadedAt is unquoted resulting in the error: invalid identifier 'JEREMYLOADEDAT'

At the same time, the ootb not null test works as expected and quotes the column name:

select
      count(*) as failures,
      count(*) != 0 as should_warn,
      count(*) != 0 as should_error
    from (
      
    
    



select "JeremyLoadedAt"
from db.sch.events
where "JeremyLoadedAt" is null



      
    ) dbt_internal_test

Expected behavior Expect that the elementary generated SQL to quote JeremyLoadedAt like the ootb not null tests does.

Screenshots If applicable, add screenshots to help explain your problem.

Environment (please complete the following information):

  • elementary dbt package version 0.18.2
  • dbt-core=1.9.4 / dbt-snowflake==1.9.2
  • Snowflake

Additional context I believe this is due to https://github.com/elementary-data/dbt-data-reliability/blob/1517d9df8954be91f2c4b2eb6cc835647f029622/macros/edr/data_monitoring/monitors_query/column_monitoring_query.sql#L34 - we inject the value of elementary.timestamp_column directly without doing any additional checks to see if we need to quote it or not.

Currently we need to do something like this to make the identifier correct/valid:

# models/schema.yml
models:
  - name: events
    config:
      elementary:
        timestamp_column: '"JeremyLoadedAt"'
...

Would you be willing to contribute a fix for this issue? Let us know if you're willing to contribute so that we'll be able to provide guidance.

jeremyyeo avatar Apr 29 '25 04:04 jeremyyeo

This issue is stale because it has been open for too long with no activity. If you would like the issue to remain open, please remove the stale label or leave a comment.

github-actions[bot] avatar Nov 12 '25 02:11 github-actions[bot]

This issue was closed because it has been inactive for too long while being marked as stale. If you would like the issue to reopen, please leave a comment.

github-actions[bot] avatar Dec 12 '25 02:12 github-actions[bot]