search

dbt-labs / dbt-core

dbt enables data analysts and engineers to transform their data using the same practices that software engineers use to build applications.
https://getdbt.com
Apache License 2.0
9.8k stars 1.62k forks source link

[CT-1739] [Bug] materialized configuration in model yml file not attaching. #6500

Closed christopherekfeldt closed 1 year ago

christopherekfeldt commented 1 year ago

Is this a new bug in dbt-core?

Current Behavior

We are trying to set all configurations through a models yml file and keep the models SQL file clean. We have been noticing a disturbing issue with setting materializations through the models yml file. Where it doesn't attach.

For example this is a models yml file:

image

This will still try to create it as a view, even if I try recreating it with --full-refresh. The SQL-file only contains SQL-code and jinja, with no configurations. We have not set a default materialization in our dbt_project.yml.

When i add the config to my SQL at the top like:

{{ config(materialized='table') }}

Then the model is recreated as I want. But we want to keep all configurations in the yml file alone.

Expected Behavior

That the model is created as the materialization i set in the models yml file.

Steps To Reproduce

  1. Add model
  2. Add yml file to model
  3. Add config and materialized: table
  4. Do dbt run and it will be created as a view

Relevant log output

============================== 2023-01-03 09:51:53.669718 | a1e55826-ea87-439b-8526-6c43e365e5dc ==============================
09:51:53.669734 [info ] [MainThread]: Running with dbt=1.3.1
09:51:53.670676 [debug] [MainThread]: running dbt with arguments {'write_json': True, 'use_colors': True, 'printer_width': 80, 'version_check': True, 'partial_parse': True, 'static_parser': True, 'profiles_dir': '/mnt/c/git/bica-dbt/dbt', 'send_anonymous_usage_stats': False, 'event_buffer_size': 100000, 'quiet': False, 'no_print': False, 'select': ['models/conlaybi/consumer_secure/'], 'state': '/mnt/c/git/bica-dbt/dbt/dbt_remote_state', 'defer': 'true', 'full_refresh': True, 'which': 'run', 'rpc_method': 'run', 'indirect_selection': 'eager'}
09:51:53.670871 [debug] [MainThread]: Tracking: do not track
09:51:54.388472 [debug] [MainThread]: Partial parsing enabled: 0 files deleted, 0 files added, 0 files changed.
09:51:54.388768 [debug] [MainThread]: Partial parsing enabled, no changes found, skipping parsing
09:51:54.602671 [info ] [MainThread]: Found 171 models, 38 tests, 0 snapshots, 0 analyses, 818 macros, 4 operations, 1 seed file, 211 sources, 2 exposures, 0 metrics
09:51:54.616740 [info ] [MainThread]: 
09:51:54.617740 [debug] [MainThread]: Acquiring new bigquery connection "master"
09:51:54.618837 [debug] [ThreadPool]: Acquiring new bigquery connection "list_ad25-p-datalab-fg2h"
09:51:54.619022 [debug] [ThreadPool]: Opening a new connection, currently in state init
09:51:56.652609 [debug] [ThreadPool]: Acquiring new bigquery connection "list_ad25-p-datalab-fg2h_dbt_christopher"
09:51:56.653362 [debug] [ThreadPool]: Opening a new connection, currently in state closed
09:51:57.173633 [debug] [MainThread]: Merged 142 items from state (sample: ['model.bica.found_item__s_item_lifecycle', 'model.bica.mart_marketing_base__dtl_contact_response', 'model.bica.found_item__s_soi_consumer_item_bag_of_keys', 'model.bica.found_consumer_sales__h_online_sku_item', 'model.bica.foundprep_item_prep__cdw_s_soi_supplier_trade_item_main'])
09:51:57.234394 [info ] [MainThread]: 
09:51:57.235009 [info ] [MainThread]: Running 4 on-run-start hooks
09:51:57.235611 [debug] [MainThread]: Compiling operation.bica.bica-on-run-start-0
09:51:57.239734 [debug] [MainThread]: Writing injected SQL for node "operation.bica.bica-on-run-start-0"
09:51:57.241206 [info ] [MainThread]: 1 of 4 START hook: bica.on-run-start.0 ......................................... [RUN]
09:51:57.241871 [info ] [MainThread]: 1 of 4 OK hook: bica.on-run-start.0 ............................................ [OK in 0.00s]
09:51:57.242403 [debug] [MainThread]: Compiling operation.bica.bica-on-run-start-1
09:51:57.245723 [debug] [MainThread]: Writing injected SQL for node "operation.bica.bica-on-run-start-1"
09:51:57.246054 [info ] [MainThread]: 2 of 4 START hook: bica.on-run-start.1 ......................................... [RUN]
09:51:57.246702 [info ] [MainThread]: 2 of 4 OK hook: bica.on-run-start.1 ............................................ [OK in 0.00s]
09:51:57.247228 [debug] [MainThread]: Compiling operation.bica.bica-on-run-start-2
09:51:57.251000 [debug] [MainThread]: Writing injected SQL for node "operation.bica.bica-on-run-start-2"
09:51:57.251394 [info ] [MainThread]: 3 of 4 START hook: bica.on-run-start.2 ......................................... [RUN]
09:51:57.252020 [info ] [MainThread]: 3 of 4 OK hook: bica.on-run-start.2 ............................................ [OK in 0.00s]
09:51:57.252542 [debug] [MainThread]: Compiling operation.bica.bica-on-run-start-3
09:51:57.256977 [debug] [MainThread]: Writing injected SQL for node "operation.bica.bica-on-run-start-3"
09:51:57.257380 [info ] [MainThread]: 4 of 4 START hook: bica.on-run-start.3 ......................................... [RUN]
09:51:57.258081 [info ] [MainThread]: 4 of 4 OK hook: bica.on-run-start.3 ............................................ [OK in 0.00s]
09:51:57.258745 [info ] [MainThread]: 
09:51:57.260088 [info ] [MainThread]: Concurrency: 10 threads (target='lab')
09:51:57.260627 [info ] [MainThread]: 
09:51:57.273974 [debug] [Thread-1  ]: Began running node model.bica.conlaybi_consumer_secure__d_consumer_pii
09:51:57.274281 [info ] [Thread-1  ]: 1 of 1 START sql view model dbt_christopher.conlaybi_consumer_secure__d_consumer_pii  [RUN]
09:51:57.274943 [debug] [Thread-1  ]: Acquiring new bigquery connection "model.bica.conlaybi_consumer_secure__d_consumer_pii"
09:51:57.275095 [debug] [Thread-1  ]: Began compiling node model.bica.conlaybi_consumer_secure__d_consumer_pii
09:51:57.275254 [debug] [Thread-1  ]: Compiling model.bica.conlaybi_consumer_secure__d_consumer_pii
09:51:57.288523 [debug] [Thread-1  ]: Writing injected SQL for node "model.bica.conlaybi_consumer_secure__d_consumer_pii"
09:51:57.290257 [debug] [Thread-1  ]: finished collecting timing info
09:51:57.290459 [debug] [Thread-1  ]: Began executing node model.bica.conlaybi_consumer_secure__d_consumer_pii
09:51:57.319215 [debug] [Thread-1  ]: Writing runtime sql for node "model.bica.conlaybi_consumer_secure__d_consumer_pii"
09:51:57.321063 [debug] [Thread-1  ]: Opening a new connection, currently in state closed
09:51:57.321349 [debug] [Thread-1  ]: On model.bica.conlaybi_consumer_secure__d_consumer_pii: /* {"app": "dbt", "dbt_version": "1.3.1", "profile_name": "etlapp", "target_name": "lab", "node_id": "model.bica.conlaybi_consumer_secure__d_consumer_pii"} */

  create or replace view `ad25-p-datalab-fg2h`.`dbt_christopher`.`conlaybi_consumer_secure__d_consumer_pii`
  OPTIONS(
      description="""Table holding dimension with pii data for consumer""",

      labels=[('batchfw_status', 'managed')]
    )
  as with s_consumer_personal_info as (
    select * from (
    select
h_consumer_key,
effective_dttm,
first_name,
last_name,
birth_dt,
email,
correct_email_ind,
mobile_num,
correct_mobile_num_ind,
    row_number() over (partition by h_consumer_key order by effective_dttm desc) as rn
    from `ab73-np-foundlay-tcm-1cae`.`consumer`.`s_consumer_personal_info`
    ) where rn = 1
),

s_consumer_address_preferred as (
    select * from (
    select
h_consumer_key,
effective_dttm,
street_address,
postal_cd,
door_num,
floor_num,
apartment_num,
correct_address_ind,
    row_number() over (partition by h_consumer_key order by effective_dttm desc) as rn
    from `ab73-np-foundlay-tcm-1cae`.`consumer`.`s_consumer_address_preferred`
    ) where rn = 1
),

s_consumer_main as (
    select * from (
    select
h_consumer_key,
effective_dttm,
protected_address_ind,
address_preferred_status_cd,
    row_number() over (partition by h_consumer_key order by effective_dttm desc) as rn
    from `ab73-np-foundlay-tcm-1cae`.`consumer`.`s_consumer_main`
    ) where rn = 1
),

s_consumer_bag_of_keys as (
    select * from (
    select
h_consumer_key,
effective_dttm,
google_analytics_id,
cookie_concent_id,
current_light_login_token_id,
consumer_id,
    row_number() over (partition by h_consumer_key order by effective_dttm desc) as rn
    from `ad25-p-datalab-fg2h`.`dbt_christopher`.`found_consumer__s_consumer_bag_of_keys`

    ) where rn = 1
),

s_consumer_ica_employee as (
    select * from (
    select
h_consumer_key,
effective_dttm,
employee_ind,
employee_type,
employee_discount_ind,
    row_number() over (partition by h_consumer_key order by effective_dttm desc) as rn
    from `ab73-np-foundlay-tcm-1cae`.`consumer`.`s_consumer_ica_employee`
    ) where rn = 1
),

d_consumer_pii_src as (
    select
         h.h_consumer_key,
        h.consumer_token_id,
        first_name,
        last_name,
        birth_dt,
        if(extract(dayofyear from current_date) < extract(dayofyear from birth_dt),
                date_diff(current_date, birth_dt, year) - 1,
                date_diff(current_date, birth_dt, year)
            ) as age,
        street_address,
        floor_num,
        door_num,
        apartment_num,
        postal_cd,
        address_preferred_status_cd as address_preferred_type_cd,
        coalesce(cast(correct_address_ind as tinyint), -1) as correct_address_flag,
        coalesce(cast(protected_address_ind as tinyint), -1) as protected_address_flag,
        mobile_num,
        coalesce(cast(correct_mobile_num_ind as tinyint), -1) as correct_mobile_number_flag,
        email,
        coalesce(cast(correct_email_ind as tinyint), -1) as correct_email_flag,
        cookie_concent_id,
        google_analytics_id,
        current_light_login_token_id,
        coalesce(cast(employee_discount_ind as tinyint), -1) as employee_discount_flag,
        coalesce(cast(employee_ind as tinyint), -1) as employee_flag,
        employee_type,
        consumer_id
    from `ab73-np-foundlay-tcm-1cae`.`consumer`.`h_consumer` as h
    left join s_consumer_personal_info
    on h.h_consumer_key = s_consumer_personal_info.h_consumer_key
    left join s_consumer_address_preferred
    on h.h_consumer_key = s_consumer_address_preferred.h_consumer_key
    left join s_consumer_main
    on h.h_consumer_key = s_consumer_main.h_consumer_key
    left join s_consumer_bag_of_keys
    on h.h_consumer_key = s_consumer_bag_of_keys.h_consumer_key
    left join s_consumer_ica_employee
    on h.h_consumer_key = s_consumer_ica_employee.h_consumer_key
)

select
    a.h_consumer_key as d_consumer_key,
    safe.AEAD.DECRYPT_STRING(KEYS.KEYSET_FROM_JSON(dps.encryptionKey), from_base64(a.consumer_id), cast(json_extract(dps.encryptionKey, '$.primaryKeyId') as string)) as consumer_id,
    safe.AEAD.DECRYPT_STRING(KEYS.KEYSET_FROM_JSON(dps.encryptionKey), from_base64(a.first_name), cast(json_extract(dps.encryptionKey, '$.primaryKeyId') as string)) as first_name,
    safe.AEAD.DECRYPT_STRING(KEYS.KEYSET_FROM_JSON(dps.encryptionKey), from_base64(a.last_name), cast(json_extract(dps.encryptionKey, '$.primaryKeyId') as string)) as last_name,
    birth_dt as birth_date,
    age,
    safe.AEAD.DECRYPT_STRING(KEYS.KEYSET_FROM_JSON(dps.encryptionKey), from_base64(a.street_address), cast(json_extract(dps.encryptionKey, '$.primaryKeyId') as string)) as street_address,
    safe.AEAD.DECRYPT_STRING(KEYS.KEYSET_FROM_JSON(dps.encryptionKey), from_base64(a.floor_num), cast(json_extract(dps.encryptionKey, '$.primaryKeyId') as string)) as floor_number,
    safe.AEAD.DECRYPT_STRING(KEYS.KEYSET_FROM_JSON(dps.encryptionKey), from_base64(a.door_num), cast(json_extract(dps.encryptionKey, '$.primaryKeyId') as string)) as door_number,
    safe.AEAD.DECRYPT_STRING(KEYS.KEYSET_FROM_JSON(dps.encryptionKey), from_base64(a.apartment_num), cast(json_extract(dps.encryptionKey, '$.primaryKeyId') as string)) as apartment_number,
    safe.AEAD.DECRYPT_STRING(KEYS.KEYSET_FROM_JSON(dps.encryptionKey), from_base64(a.postal_cd), cast(json_extract(dps.encryptionKey, '$.primaryKeyId') as string)) as postal_code,
    address_preferred_type_cd as address_preferred_type_code,
    if(correct_address_flag = 1, 'Correct Address', if(correct_address_flag = 0, 'Not Correct Address', 'Unknown')) as correct_address,
    correct_address_flag,
    if(protected_address_flag = 1, 'Protected Address', if(protected_address_flag = 0, 'Not Protected Address', 'Unknown')) as protected_address,
    protected_address_flag,
    safe.AEAD.DECRYPT_STRING(KEYS.KEYSET_FROM_JSON(dps.encryptionKey), from_base64(a.mobile_num), cast(json_extract(dps.encryptionKey, '$.primaryKeyId') as string)) as mobile_number,
    if(correct_mobile_number_flag = 1, 'Correct Mobile Number', if(correct_mobile_number_flag = 0, 'Not Correct Mobile Number', 'Unknown')) as correct_mobile_number,
    correct_mobile_number_flag,
    safe.AEAD.DECRYPT_STRING(KEYS.KEYSET_FROM_JSON(dps.encryptionKey), from_base64(a.email), cast(json_extract(dps.encryptionKey, '$.primaryKeyId') as string)) as email,
    if(correct_email_flag = 1, 'Correct Email', if(correct_email_flag = 0, 'Not Correct Email', 'Unknown')) as correct_email,
    correct_email_flag,
    safe.AEAD.DECRYPT_STRING(KEYS.KEYSET_FROM_JSON(dps.encryptionKey), from_base64(a.cookie_concent_id), cast(json_extract(dps.encryptionKey, '$.primaryKeyId') as string)) as cookie_concent_id,
    safe.AEAD.DECRYPT_STRING(KEYS.KEYSET_FROM_JSON(dps.encryptionKey), from_base64(a.google_analytics_id), cast(json_extract(dps.encryptionKey, '$.primaryKeyId') as string)) as google_analytics_id,
    current_light_login_token_id,
    if(employee_discount_flag = 1, 'Employee Discount', if(employee_discount_flag = 0, 'Not Employee Discount', 'Unknown')) as employee_discount,
    employee_discount_flag,
    if(employee_flag = 1, 'Employee', if(employee_flag = 0, 'Not Employee', 'Unknown')) as employee,
    employee_flag,
    employee_type
from d_consumer_pii_src as a
inner join `ab73-np-keystore-tcm-31df`.`keystore`.`dps_v` as dps
  on dps.rowkey = a.consumer_token_id;

09:51:58.650841 [debug] [Thread-1  ]: BigQuery adapter: https://console.cloud.google.com/bigquery?project=ad25-p-datalab-fg2h&j=bq:72b8ef57-37ee-4269-8d67-fd3ee25fde27:EU&page=queryresults
09:51:58.658046 [info ] [Thread-1  ]: Skipping applying grants in target lab.
09:51:58.974062 [debug] [Thread-1  ]: finished collecting timing info
09:51:58.976144 [info ] [Thread-1  ]: 1 of 1 OK created sql view model dbt_christopher.conlaybi_consumer_secure__d_consumer_pii  [CREATE VIEW (0 processed) in 1.70s]
09:51:58.978340 [debug] [Thread-1  ]: Finished running node model.bica.conlaybi_consumer_secure__d_consumer_pii
09:51:58.984559 [debug] [MainThread]: Acquiring new bigquery connection "master"
09:51:58.986130 [info ] [MainThread]: 
09:51:58.987523 [info ] [MainThread]: Finished running 1 view model, 4 hooks in 0 hours 0 minutes and 4.37 seconds (4.37s).
09:51:58.989242 [debug] [MainThread]: Connection 'master' was properly closed.
09:51:58.989655 [debug] [MainThread]: Connection 'model.bica.conlaybi_consumer_secure__d_consumer_pii' was properly closed.
09:51:59.257917 [info ] [MainThread]: 
09:51:59.258808 [info ] [MainThread]: Completed successfully
09:51:59.259663 [info ] [MainThread]: 
09:51:59.260310 [info ] [MainThread]: Done. PASS=1 WARN=0 ERROR=0 SKIP=0 TOTAL=1
09:51:59.261209 [debug] [MainThread]: Flushing usage events

Environment

- OS: Ubuntu 20.04
- Python: Python 3.8.10
- dbt: 1.3.1

Which database adapter are you using with dbt?

bigquery

Additional Context

No response

dbeatty10 commented 1 year ago

Thanks for reaching out @christopherekfeldt !

It looks like materialized is misspelled as materalized within your YAML. Could you try updating the spelling and see if it works?

christopherekfeldt commented 1 year ago

Well isn't that embarrassing.. Thanks, @dbeatty10 it works perfectly fine now.

dbeatty10 commented 1 year ago

No problem @christopherekfeldt. Glad it's working the way you want now 🙌