Issue with snapshots compilation after upgrading to 0.21.0

[andrewlee-trouva]

Describe the bug

I've just upgraded our dbt instance from 0.20 to 0.21, and tried to compile dbt locally to check for any issues. I received the following error:

Encountered an error:
Snapshots must be configured with a 'strategy', 'unique_key', and 'target_schema'.

This occurs despite all my snapshots (we have 8, a combination of timestamp and check_cols strategies) having strategy, unique_key and target_schema all declared in configs.

Steps To Reproduce

with a dbt instance at 0.20, with snapshots, using redshift, upgrade dbt-redshift to 0.21 and run dbt ls or dbt compile

Expected behavior

dbt will output the above error

Screenshots and log output

System information

Which database are you using dbt with?

• postgres
• redshift
• bigquery
• snowflake
• other (specify: ____________)

The output of dbt --version:

installed version: 0.21.0
   latest version: 0.21.0

Up to date!

Plugins:
  - bigquery: 0.20.0
  - snowflake: 0.20.0
  - redshift: 0.21.0
  - postgres: 0.21.0

The operating system you're using:
Mac, Catalina version 10.15.7

The output of python --version:

Python 3.8.2

Additional context

[jtcohen6]

@andrewlee-trouva Thanks for the report!

Could I ask you to be a bit more specific about:

This occurs despite all my snapshots (we have 8, a combination of timestamp and check_cols strategies) having strategy, unique_key and target_schema all declared in configs.

Are those declared in config() Jinja macros within the snapshot blocks? In dbt_project.yml? Would you be able to provide an example of a snapshot that's raising this error (scrubbed of any sensitive/identifying info)?

[andrewlee-trouva]

hey @jtcohen6 sure no problem,

They are declared in the config blocks in the snapshot files.
Here's an example of one of our snapshots, I'm not sure which, if any, snapshot is causing the compilation problem, but they all have a similar set up:

{% snapshot redshift_queries_snapshot %}

{{
    config(
      target_schema='snapshots',
      unique_key='_id',
      alias='redshift_queries_snapshot',
      strategy='timestamp',
      updated_at='finished_at',
    )
}}

select
md5(a.query_id::varchar || final_state::varchar) as _id,
a.query_id,
a.user_id,
username::varchar,
sq.transaction_id,
sq.pid,
database,
started_at,
finished_at,
queue_start_time,
queue_end_time,
total_queue_time,
exec_start_time,
exec_end_time,
total_exec_time,
final_state,
aborted,
datediff(second, exec_start_time, exec_end_time) as total_exec_time_s,
datediff(second, queue_start_time, queue_end_time) as total_queue_time_s
from {{ ref('stl_wlm_query') }} a
left join {{ ref('pg_user') }} u
    on a.user_id = u.user_id
join {{ ref('stl_query') }} sq
    on sq.query_id = a.query_id

{% endsnapshot %}

[jtcohen6]

@andrewlee-trouva Weird! I just copy-pasted that snapshot code into an otherwise bare local project, and I'm unable to reproduce the same error. I'm running dbt v0.21.0 with python 3.8.9 on macOS Big Sur (M1 via Rosetta).

This feels somewhat similar to #3929. It's even a bit eerily reminiscent of config-related bugs we saw on older python versions (<=3.6.3), triggered by some changes v0.19.1 (slack thread), though in #3929 they're using 3.9.6.

So, this might be a big ask and I understand if you can't get to it right away—is there any chance you could try this with a newer version of python?

[andrewlee-trouva]

@jtcohen6 Very weird 🤔 , I'll try moving up to 3.8.9 and see that does, and I'll let you know what happens

[gshank]

This seems like #4000. Is there any snapshot configuration in dbt_project.yml or another schema file?

[jtcohen6]

@gshank Would you expect to see this error if all snapshots define these configs via the in-file config() macro?

@andrewlee-trouva To Gerda's point: Do any snapshots not define those configs via the config() macro? Are the configs for any defined in dbt_project.yml? (I wasn't able to reproduce that when I tested it locally, but worth confirming the exact details)

[andrewlee-trouva]

hey @jtcohen6, we don't define anything config wise in a yml, we leave it all in the config() macro. The only thing we have in our dbt_project.yml regarding macros is a couple pre/post hooks for logging and a call to our grant select macro on run start and end.

  - "{{ grant_select_on_schemas(schemas, 'system_users') }}"

on-run-end:
  - "{{ grant_select_on_schemas(schemas, 'analysts') }}"
  - "{{ grant_select_on_schemas(schemas, 'data_engineers') }}"
  - "{{ grant_select_on_schemas(schemas, 'system_users') }}"  


models:
  trouva:
      # Applies to all files under models/demo/
      demo:
          materialized: view
  pre-hook: "{{ logging.log_model_start_event() }}"
  post-hook: "{{ logging.log_model_end_event() }}"

[WesRoach]

I've also run into this issue upgrading to 0.21.0. I tried running on Python 3.8.12, 3.8.9, and 3.9.7, all produced the same error.

I was able to move forward with an upgrade to 0.21.0 by re-stating the config()s which only existed in the <snapshot_name>.sql files from ./snapshots into the dbt_project.yml. The snapshot config parameters can be in both the .sql and dbt_project.yml, but they must exist in the dbt_project.yml to avoid the error.

[gshank]

We still haven't been able to recreate this. If somebody could boil this down to a minimal test case that would be a big help.

[andrewlee-trouva]

Hi @jtcohen6 @gshank Just to update you from our end.

We've been working on adding a docker container to our project, and tried to fix this problem at the same time to no avail, a docker image of python 3.9 with [email protected] still has this error.

Thanks @WesRoach I will investigate doing that our end, I assume this didn't effect the snapshot themselves? (i.e not overwriting?)

[thisiscab]

Hello, I am having the same issue with

• Snowflake
• Also in a Docker Container

16:54:03  Running with dbt=1.0.1
dbt version: 1.0.1
python version: 3.8.12
python path: /usr/local/bin/python
os info: Linux-5.10.76-linuxkit-x86_64-with-glibc2.2.5
Using profiles.yml file at /root/.dbt/profiles.yml
Using dbt_project.yml file at /usr/src/dbt_project.yml

Configuration:
  profiles.yml file [OK found and valid]
  dbt_project.yml file [OK found and valid]

Required dependencies:
 - git [OK found]

The snapshot works for DBT 0.20.x but it doesn't work for 0.21.x and 1.0.x

I've removed all "snapshots" and it worked. When I add only one snapshot file that looks like this:

{% snapshot my_snapshot %}

{{
    config(
        unique_key='id',
        strategy='check',
        target_schema='snapshots',
        check_cols=['my_cols'],
    )
}}

select * from my_source

{% endsnapshot %}

It throws Snapshots must be configured with a 'strategy', 'unique_key', and 'target_schema'.

[ashlee3209]

Hi, we faced the similar issue after upgrading to 0.21.0 and I tried with 1.0.3 which seems still has the same error.

It works ok if we only have config block in the sql file like

{{
    config(
        target_database='dev',
        target_schema='snapshots',
        unique_key='contract_key',
        strategy='check',
        check_cols=['contract_id'],
)

}}

But when we add the property file with config:

- name: contract_snapshot
    description: This is a description that comes from dbt /snapshots/schema.yaml
    schema: snapshots
    meta:
      owner:
        - xxxx
    config:
      target_schema: snapshots
      unique_key: contract_key
      strategy: check
      check_cols: [ contract_id ]
    columns:
      - name: snapshot_date
      - description: xxx

It goes to the wrong schema (the default_schema) instead of target_schema "snapshots".
If we removed the config block in the property file and leave the rest fields in the file, It throws Snapshots must be configured with a 'strategy', 'unique_key', and 'target_schema'.

Any suggestion on how to fix this error? Is that a bit strange to mandatory have config in the property file if we already have config block in the sql file? And I do not know why it changes the schema if we have config in both places.

[ashlee3209]

Some update about our problem:
We added schema to the config in the property file, then it goes to the correct schema. So basically, we create a macro about generate_schema_name to differentiate model node and snapshot node, then it created snapshots using target_schema instead. But still I think it is a bit wired to make the config 'strategy', 'unique_key' and 'target_schema' mandatory if we use property file since we already have config block in the sql file.

[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 remove the stale label or comment on the issue, or 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; add a comment to notify the maintainers.