Issues running insert into statement in dbt

user_1592334 0
Viewed 700

I am new to dbt and I am trying to write a data model that would insert its result into an existing table on snowflake. The below code without the config runs smoothly on snowflake, but it doesn't work on dbt. Seems insert statements are not using in DBT?

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

INSERT INTO "v1" ("ID", "Value","Set",ROWNUM )

with LD as(
     
             select "ID",
                     "Value",
                    "Set",
                    ROW_NUMBER()OVER ( PARTITION BY "ID" order by "Set" desc )  as rownum 
             from "Archive"."Prty" l
             where l."Prty" = 'Log' AND "ID"= 111
       ),
       LD2 as (
        select "ID",
                "Value",
                "Set",
                ROWNUM
        from LD where ROWNUM = 1
         )

      SELECT  * FROM LD2
dbt
1 Answers

You could use an incremental model to achieve this all in the same table. You can also use the qualify clause to remove the need for the second CTE. I am assuming ID should be unique, but refer to the link and modify if this is not the case.

{{
    config(
        materialized='incremental',
        unique_key='ID'
    )
}}

select
  "ID",
  "Value",
  "Set"
from "Archive"."Prty" as l
where l."Prty" = 'Log' and "ID" = 111
qualify row_number() over (partition by "ID" order by "Set" desc) = 1

This will insert any rows that satisfy the above query and have an ID not already in the table. Otherwise, it will update any rows where the ID already does exist.

user_154405930
Related