compare_relations adapter.get_relation doesn't work as in the README

christineberger commented 1 year ago

Describe the bug

The compare_relations macro seems to have an issue with the adapter.get_relation value on the get_filtered_columns_in_relation operation. It seems to work with an {% if execute %} wrapper, though.

Steps to reproduce

Copy the code from the README for compare_relations, then use it as-is, swapping the adapter.get_relations call to an accessible table. Try to run the macro - you will get an error with an empty value.

Expected results

A summarized comparison between the two datasets. Using Benoit's suggestion from an internal channel, the macro works fine like this:

{% set dbt_relation=ref('orders') %}

{% if execute %}
    {%- set old_etl_relation = adapter.get_relation(
        database="ANALYTICS",
        schema="DBT_COHMS",
        identifier="FCT_ORDERS") -%}

    {{ audit_helper.compare_relations(
        a_relation=old_etl_relation,
        b_relation=dbt_relation,
        primary_key="order_id"
    ) }}
{% endif %}

Actual results

With the snippet in the README: Screenshot 2023-09-12 at 6 01 17 PM

Benoit's version: Screenshot 2023-09-12 at 6 06 58 PM

System information

The contents of your packages.yml file: Screenshot 2023-09-12 at 6 08 52 PM

Which database are you using dbt with?

[ ] postgres
[ ] redshift
[ ] bigquery
[x] snowflake
[ ] other (specify: ____)

The output of dbt --version: 1.6.0 - dbt Cloud

The operating system you're using: Mac / Arc Browser

The output of `python --version`:

Additional context

It's fine if things now need to be wrapped in if execute, but the README should reflect!

Are you interested in contributing the fix?

Would love to, but don't have time to solution if needed 🥲

github-actions[bot] commented 6 months ago

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.

graciegoheen commented 6 months ago

Just ran into the same issue

This works fine:

{% set old_etl_relation=adapter.get_relation(
      database="DEVELOPMENT",
      schema="DBT_GGOHEEN",
      identifier="stg_worlds"
) -%}

{{ old_etl_relation }}

But when I try to call one of the audit helper macros, I get a failure: Screenshot 2024-03-12 at 2 13 04 PM

graciegoheen commented 6 months ago

I'm not sure how you would create an integration test for this, because building a model works fine - it's just the preview that fails.

graciegoheen commented 6 months ago

Same issue as reported here:

joellabes commented 5 months ago

I think that instead of fixing the readme we can/should fix the various macros!

compare_relation_columns from grace's example can be fixed by adding an if execute block in here before trying to get the information_schema off the (nonexistent) Relation
compare_relations from christine's example can be fixed in dbt_utils, by moving the existing if execute check in get_filtered_columns_in_relation to before the is_relation check

dbt-labs / dbt-audit-helper