[SEMANTIC-232] [Regression] dbt_metrics_default_calendar on redshift fails to build with "can't execute an empty query" starting in 1.4.1

[kylezeeuwen]

Is this a regression in a recent version of dbt_metrics?

• I believe this is a regression in dbt_metrics functionality
• I have searched the existing issues, and I could not find an existing issue for this regression

Current Behavior

when i dbt run my project (that has metrics and metrics.calculate usage) I get the following failure:

Database Error in model dbt_metrics_default_calendar (models/dbt_metrics_default_calendar.sql)�[0m
can't execute an empty query
compiled Code at target/run/metrics/models/dbt_metrics_default_calendar.sql

Expected/Previous Behavior

the dbt run should succeed

Steps To Reproduce

Note I wrote this up here as well : https://getdbt.slack.com/archives/CBSQTAPLG/p1680218676837929

dbt-core=1.4.5
dbt-redshift=1.4.0

packages.yml

packages:
  - package: dbt-labs/dbt_utils
    version: 1.0.0
  - package: dbt-labs/audit_helper
    version: 0.8.0
  - package: dbt-labs/codegen
    version: 0.9.0
  - package: dbt-labs/metrics
    version: 1.4.1
  - package: calogica/dbt_expectations
    version: 0.8.4

1. run dbt run and get failure
2. change to dbt-metrics 1.4.0 via package.yml change then dbt deps
3. run `dbt run and get pass

I believe this is related to the casing mismatch of column names between these two files:

• https:/dbt-labs/dbt_metrics/blob/main/models/dbt_metrics_default_calendar.yml
• https:/dbt-labs/dbt_metrics/blob/main/models/dbt_metrics_default_calendar.sql

this is described by Jeremy Cohen in a comment on this bug report : dbt-labs/dbt-core#5183 (comment)

Relevant log output

No response

Environment

- dbt-adapter & version: dbt-redshift 1.4.0 and dbt-core 1.4.5
- dbt_metrics (working version): 1.4.0
- dbt_metrics (regression version): 1.4.1

Which database adapter are you using with dbt?

redshift

Additional Context

No response

[kylezeeuwen]

Can confirm that the yml file was first added Feb 28 after the 1.4.0 tag (Jan 21) and before the 1.4.1 tag (Mar 3).
ba330da

[kylezeeuwen]

Further this bug only occurs if you have persist_docs at the column setup in your dbt_project

the compiled and run versions of the model sql is fine.
The error occurs post model creation when DBT is attempting to persist the docs to redshift table metadata.
Note (i will update bug report) our dbt_project.yml

models:
  +persist_docs:
    relation: true
    columns: true

confirmed.

• 1.4.1 with +persist_docs 💥 (can’t execute an empty query)
• 1.4.1 with NO +persist_docs ✅

[callum-mcdata]

@kylezeeuwen do you mind editing the local version of your calendar yml file and seeing if it runs with the lowercase? From my end it appears that this change passes our testing suite but I want to confirm it resolves your issue.

version: 2

models:
  - name: dbt_metrics_default_calendar
    description: |
      An auto generated calendar table that used for metrics.
  
    columns:
      - name: date_day
        description: "Date"
      - name: date_week
        description: "Date truncated into week"
      - name: date_month
        description: "Date truncated into month"
      - name: date_quarter
        description: "Date truncated into quarter"
      - name: date_year
        description: "Date truncated into year"

[kylezeeuwen]

Hi @callum-mcdata I was talking about reproduction steps with a colleague and he mentioned that you would need this setting in your test suite to reproduce the issue:

SET enable_case_sensitive_identifier TO true

If this is set on the redshift session then case sensitive is on, else it defaults to off.

[callum-mcdata]

@kylezeeuwen would you be able to test on your version? This is enough of an edge case and relies on configuring properties outside of dbt that I am leaning towards not adding a test for it (especially as I would prefer not to muck about with our Redshift session parameters 😅 ) . If you can confirm on your local that it works fine with the changes I'll get a PR opened and have it be included in the next release

[kylezeeuwen]

yep I just tested and can confirm changing yml columns to lower case fixes the issue

root@52736896606b:/workspace/datawh# cat /workspace/datawh/dbt_packages/metrics/models/dbt_metrics_default_calendar.yml
version: 2

models:
  - name: dbt_metrics_default_calendar
    description: |
      An auto generated calendar table that used for metrics.
  
    columns:
      - name: DATE_DAY
        description: "Date"

root@52736896606b:/workspace/datawh# run --select dbt_metrics_default_calendar+
14:03:38  Running with dbt=1.4.5
14:03:39  Found 419 models, 495 tests, 23 snapshots, 0 analyses, 816 macros, 3 operations, 8 seed files, 173 sources, 20 exposures, 5 metrics
14:03:39  
14:03:45  Concurrency: 4 threads (target='prod')
14:03:45  
14:03:45  1 of 6 START sql table model analytics.dbt_metrics_default_calendar ............ [RUN]
14:03:53  1 of 6 ERROR creating sql table model analytics.dbt_metrics_default_calendar ... [ERROR in 8.20s]
...
14:03:54  Finished running 6 table models, 3 hooks in 0 hours 0 minutes and 15.19 seconds (15.19s).
14:03:55  
14:03:55  Completed with 1 error and 0 warnings:
14:03:55  
14:03:55  Database Error in model dbt_metrics_default_calendar (models/dbt_metrics_default_calendar.sql)
14:03:55    can't execute an empty query
14:03:55    compiled Code at target/run/metrics/models/dbt_metrics_default_calendar.sql
14:03:55  
14:03:55  Done. PASS=0 WARN=0 ERROR=1 SKIP=5 TOTAL=6

root@52736896606b:/workspace/datawh# #edit to change to date_day
root@52736896606b:/workspace/datawh# vim /workspace/datawh/dbt_packages/metrics/models/dbt_metrics_default_calendar.yml

...

root@52736896606b:/workspace/datawh# cat /workspace/datawh/dbt_packages/metrics/models/dbt_metrics_default_calendar.yml
version: 2

models:
  - name: dbt_metrics_default_calendar
    description: |
      An auto generated calendar table that used for metrics.
  
    columns:
      - name: date_day
        description: "Date"

root@52736896606b:/workspace/datawh# run --select dbt_metrics_default_calendar+
14:05:40  Running with dbt=1.4.5
14:05:41  Found 419 models, 495 tests, 23 snapshots, 0 analyses, 816 macros, 3 operations, 8 seed files, 173 sources, 20 exposures, 5 metrics
14:05:41  
14:05:48  Concurrency: 4 threads (target='prod')
14:05:48  
14:05:48  1 of 6 START sql table model analytics.dbt_metrics_default_calendar ............ [RUN]
14:05:58  1 of 6 OK created sql table model analytics.dbt_metrics_default_calendar ....... [SELECT in 9.90s]
...
14:06:14  Finished running 6 table models, 3 hooks in 0 hours 0 minutes and 32.79 seconds (32.79s).
14:06:14  
14:06:14  Completed successfully
14:06:14  
14:06:14  Done. PASS=6 WARN=0 ERROR=0 SKIP=0 TOTAL=6

[kylezeeuwen]

worth noting that - 'SET enable_case_sensitive_identifier TO true;' is common in redshift warehouses.

If you have json with camel case or title case keys you must have this setting enabled or you cant extract values from json fields with capital case names unless you have the above set (at least thats how we interpret the available options 🤷)