How to deploy SQL views to snowflake using config object?

Question

I am trying to deploy sql model to snowflake using config block, however DBT does not take the details from config object when deploying

{{ config(
    materialized="view",
    schema = 'sample_schema',
    alias='SAMPLE_V',    
    copy_grants=true
) }}

SELECT
    *
FROM DBO.SAMPLE_DATE
LIMIT 100

it always picks the schema details from the profiles.yml and does not overwrite, that is part of the problem, the other thing is when it generates the final sql it replaces the view name with file_name instead of alias name.

any one who can explain what I am doing wrong and why this is not being picked.

project.yml

name: 'snowflake_ci_cd'
version: '1.0.0'


profile: 'snowflake_dev'



model-paths: ["models"] 
analysis-paths: ["analyses"]
test-paths: ["tests"]
seed-paths: ["seeds"]
macro-paths: ["macros"]
snapshot-paths: ["snapshots"]

clean-targets:         # directories to be removed by `dbt clean`
  - "target"
  - "dbt_packages"


models:
  snowflake_ci_cd:
    example:
      +materialized: view

[0m08:11:33.564791 [debug] [Thread-1 (]: SQL status: SUCCESS 1 in 0.641 seconds [0m08:11:33.583785 [debug] [Thread-1 (]: Sending event: {'category': 'dbt', 'action': 'run_model', 'label': '1f06956a-4f0e-43d1-9ab0-216a4fa4ebeb', 'context': [<snowplow_tracker.self_describing_json.SelfDescribingJson object at 0x1064ce3c0>]} [0m08:11:33.584290 [info ] [Thread-1 (]: 1 of 1 OK created sql view model DEVABHI_SCH_SAMPLE.SAMPLE_V ................... [[32mSUCCESS 1[0m in 0.72s] [0m08:11:33.584702 [debug] [Thread-1 (]: Finished running node model.snowflake_ci_cd.sample_view [0m08:11:33.585566 [debug] [MainThread]: Connection 'master' was properly closed. [0m08:11:33.585818 [debug] [MainThread]: Connection 'model.snowflake_ci_cd.sample_view' was left open. [0m08:11:33.586018 [debug] [MainThread]: On model.snowflake_ci_cd.sample_view: Close

The config

{{ config(
    materialized='view',
    schema='SAMPLE',
    alias='SAMPLE_V',
    copy_grants=true
) }} 

SELECT
    *
FROM DBO.TRANSACTION_ALL_V
LIMIT 100

but profile has schema=DEVABHI_SCH

so its just clubbing the 2 schema info and creating a new schema, instead of picking a new schema from config. DEVABHI_SCH_SAMPLE.SAMPLE_V

it should come from dbt_project.yml. Can you pls post the config settings from this file? Check ifyou have set some tag there — Koushik Roy
– Koushik Roy, Commented Jan 13 at 9:51
Hi @KoushikRoy i have added the yml. please advise .. thanks! — Rookie111
– Rookie111, Commented Jan 13 at 9:58
the config block in the model should always take preference to what's defined in the dbt_project.yml file, as stated in the docs: docs.getdbt.com/reference/model-configs#configuring-models. So that's weird. Which dbt version are you using? And can you also share the logs of running a dbt run -s <your_model>? — Aleix CC
– Aleix CC, Commented Jan 13 at 10:11
can you pls use a normal config and remove the word example - models: snowflake_ci_cd: +materialized: view — Koushik Roy
– Koushik Roy, Commented Jan 13 at 14:41

Rookie111 · Accepted Answer · 2025-01-14 05:36:24Z

0

Looks like a macro needs to be defined, because dbt by default has the following behaviour

{% macro generate_schema_name(custom_schema_name, node) -%}

    {%- set default_schema = target.schema -%}
    {%- if custom_schema_name is none -%}

        {{ default_schema }}

    {%- else -%}

        {{ default_schema }}_{{ custom_schema_name | trim }}

    {%- endif -%}

{%- endmacro %}

it merges the names of the schema, so due to this, it was merging the schema's, adding that macro with small tweak remove "{{ default_schema }}_" from else, that helped resolve this, at least the preliminary testing looks to be working.

answered Jan 14 at 5:36

Rookie111

518 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How to deploy SQL views to snowflake using config object?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related