compare_queries does not work on Dremio

[igor-lobanov-maersk]

Describe the bug

compare_queries marco uses "count" as a field name in summary_stats CTE without escaping, which is not allowed in Dremio SQL.

Steps to reproduce

In Dremio environment, create the following model:

-- File regression_test.sql
{% set old_query %}
select 1 as a
union all
select 2 as a
{% endset %}


{% set new_query %}
select 1 as a
union all
select 2 as a
{% endset %}


{{
    audit_helper.compare_queries(
        a_query=old_query,
        b_query=new_query,
        primary_key="a"
    )
}}

Run audit model: dbt run -m regression_test

Expected results

Model runs.

Actual results

Model run fails, see log below.

Additional context

I can go into the compiled model file in "target" directory and manually quote "count" in summary_stats CTE and at the end where it is used. If I then copy and paste the SQL into Dremio console, it runs as works as intended.

Screenshots and log output

17:35:50  1 of 1 START sql view model regression_test .................................... [RUN]
17:35:50  1 of 1 ERROR creating sql view model regression_test ........................... [ERROR in 0.39s]
17:35:50  
17:35:50  Finished running 1 view model in 0 hours 0 minutes and 2.60 seconds (2.60s).
17:35:50  
17:35:50  Completed with 1 error and 0 warnings:
17:35:50  
17:35:50  Runtime Error in model regression_test (models/regression/regression_test.sql)
17:35:50    ERROR: Encountered "as count" at line 102, column 18.
17:35:50    Was expecting one of:
17:35:50        ")" ...
17:35:50        "ORDER" ...
17:35:50        "LIMIT" ...
17:35:50        "OFFSET" ...
17:35:50        "FETCH" ...
17:35:50        "FROM" ...
17:35:50        "," ...
17:35:50        "AS" <IDENTIFIER> ...
<skipped long list of options, clearly Dremio stumbles on "as count">
17:35:50  
17:35:50  Done. PASS=0 WARN=0 ERROR=1 SKIP=0 TOTAL=1

System information

packages:
  - package: dbt-labs/dbt_utils
    version: 1.1.1
  - package: dbt-labs/audit_helper
    version: 0.9.0

Which database are you using dbt with?
Dremio.

The output of dbt --version:

Core:
  - installed: 1.5.8
  - latest:    1.6.6 - Update available!

  Your version of dbt-core is out of date!
  You can find instructions for upgrading here:
  https://docs.getdbt.com/docs/installation

Plugins:
  - dremio: 1.5.0 - Up to date!

The operating system you're using:
Debian inside DevContainer via VSCode.

The output of python --version:
Python 3.11.6

Are you interested in contributing the fix?

PR provided.

[igor-lobanov-maersk]

I'm struggling with a fix that does not break all other supported SQL dialects. In #85 I tried using {{ adapter.quote('count') }}, but it fails Snowflake CI tests here: https://app.circleci.com/pipelines/github/dbt-labs/dbt-audit-helper/352/workflows/86a40594-8964-413c-bb92-4cc48212c4d2/jobs/346

Any help would be appreciated.

[github-actions]

This issue has been marked as Stale because it has been open for 180 days with no activity. If you would like the issue to remain open, please comment on the issue or else it will be closed in 7 days.

[github-actions]

Although we are closing this issue as stale, it's not gone forever. Issues can be reopened if there is renewed community interest. Just add a comment to notify the maintainers.