[CT-1913] Snapshot target_schema & target_database used from dbt_project.yml

[minhajpasha]

Is this a new bug in dbt-core?

• I believe this is a new bug in dbt-core
• I have searched the existing issues, and I could not find an existing issue for this bug

Current Behavior

Profiles.yml

    dev:
      type: bigquery
      method: oauth
      project: gcp-project-test
      **dataset: holding**
      location: EU

snapshot config in dbt_project.yml

snapshots:
    test_dbt_project
        +persist_docs:
            relation: true
            columns: true
        primary:
            snapshot_models:
                **+target_schema: snapshot_dataset**

schema.yml to add the descriptions to snapshots tables

snapshots:
    - name: customers
      config:
        enabled: true
      columns:
        - name: customer_id
           description: 'customer unique id'

snapshot folder structure:

-snapshots
  -primary
      -snapshot_models
         -customers.sql
         -orders.sql
         - schema.yml

When we add the schema.yml file under snapshots/primary/snapshots_models and run the below command

dbt snapshot --select customers then the model is getting created under holding dataset(profile.yml) instead of snapshot_dataset(dbt_project.yml)

Expected Behavior

The snapshots should be created in the dataset defined in dbt_project.yml file. For now have removed the schema.yml file so that it can create the model's under dataset provided in dbt_project.yml file. This is stopping to add the descriptions to table columns

Steps To Reproduce

details provide in descriptions

Relevant log output

No response

Environment

- OS: Airflow 2.2 composer 2.2
- Python:3.8
- dbt: dbt bigquery 1.2.0

Which database adapter are you using with dbt?

bigquery

Additional Context

No response

[dbeatty10]

Thank you for reaching out about this @minhajpasha !

p.s. I added some ``` to your description to help format each or your YAML examples -- hope you don't mind.

I was able to reproduce what you described, and I agree this is a bug. It might be related to #4000

Workaround

Removing your snapshots/primary/snapshot_models/schema.yml file altogether is an option!

But if you want to keep that file (for its column descriptions, etc), then you can use the following workaround.

Remove each config section from your schema.yaml:

version: 2

snapshots:
  - name: customers
    columns:
      - name: customer_id
        description: 'customer unique id'

And add your specific config assignments within each snapshot instead:

{% snapshot customers %}

{{
    config(
      enabled=true,
      ...
    )
}}

...
{% endsnapshot %}

How to reproduce

Here's the simplified setup that I used to reproduce the error using dbt-snowflake:

dbt_project.yml

snapshots:
  +target_schema: dbeatty_snapshot_dataset

snapshots/customers.sql

{% snapshot customers %}

{{
    config(
      enabled=true,
      unique_key='id',
      strategy='check',
      check_cols='all',
    )
}}

select 0 as id 

{% endsnapshot %}

This will not work as intended (since it won't use configured the snapshot target_schema and will use the schema from profiles.yml instead):
snapshots/schema.yml

version: 2

snapshots:
  - name: customers
    # Adding a config section here will break the snapshot `target_schema`!
    config:
      enabled: true

Run it

Run it and observe the (unintended!) schema it uses for snapshots:

dbt snapshot --select customers

But removing the config key will use the intended target schema for snapshots:
snapshots/schema.yml

version: 2

snapshots:
  - name: customers

Run it again

Run it again and observe the intended schema it uses for snapshots:

dbt snapshot --select customers

[minhajpasha]

@dbeatty10 - I hope everything is going well for you. Thanks for providing the workaround. Will try and update you

[honeyp0t]

This bug also applies when defining a "meta" property in the snapshot yml (so not just config).
I forked jaffle_shop and made some changes to illustrate the issue, but the example is identical to the steps to reproduce as given by @dbeatty10

https://github.com/honeyp0t/jaffle_shop

[dbeatty10]

@honeyp0t -- thanks for explaining that this extends beyond config to meta also and showing within that marvelous repo 🏆

[eugenekim-orrum]

I've been pulling my hair over an issue with setting the snapshot database as well. Is this related?

dbt_project.yml

snapshots:
  +target_database: "{{ 'raw' if target.name == 'prod' else target.database }}"

snapshot_name.sql (with simply hard coded database)

{% snapshot snapshot_name %}

    {{
        config(
            target_database='raw',

My dbt snapshot runs would ignore these settings and use the database from profiles.yml, just like the schema issue noted above.
After many hours of hair pulling and probing everywhere, this is what I found.

dbt_project.yml

snapshots:
  +database: 'raw'

This works.

snapshot_name.sql

{% snapshot snapshot_name %}

    {{
        config(
            database='raw',

This also works.

I upgraded from v1.0 to v1.3 and I believe it happened between these versions.