Search code examples
sqlamazon-redshiftdbt

Updating Table in DBT

I have two SQL scripts and am trying to translate them to DBT.

Problem Statment is as below:-

enter image description here

Current Solution

SQL script No.1 creates the table and SQL script No.2 update the table with the following query

UPDATE T1 
SET C4 = 3.3 WHERE C1 = 'US'

Now, I want to move these creation and updation job(Script1 and Script 2) to DBT.

DBT Solution

I have created a model called T1.sql in DBT which creates Table T1 in the database and its contents are as follows:-

SELECT * FROM member;

Now for updating the table as per the above-mentioned criteria, I wrote the SELECT statement as

Select c1,c2,c3,
case when c1 = ‘US’ then 3.3 else c4 end as c4
from t1

Now the question is where do I put this above the SELECT statement. I cannot put in file T1.sql as each DBT model file should have only one SELECT statement. If new file, then what should be the file name. I cannot have multiple files with the name 'T1.sql'.

Is there any way we can include both these operations in T1.sql file so that we need not to create any other new tables to achieve this?

Solution

  • Here is your T1.sql file:

    SELECT * FROM member

    This query returns the table T1 as you mentioned.

    Now you can create a new sql file in dbt for example T2.sql and this T2.sql will have the following code:

    select 
t1.*,
case when t1.c1 = ‘US’ then 3.3 else c4 end as c4
from {{ref('T1')}} as t1

    Here T2.sql file is referencing the model T1 which is basically your T1.sql file .

    Code 2 with CTE This will be your entire t1.sql file: Updates your c4 values based on case statement provided

    With initial_data as (
select * from member
)
select 
initital_data.*,
case when initital_data.c1 = ‘US’ then 3.3 else c4 end as c4
from initial_data as t1