Updating Table in DBT

Question

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

Problem Statment is as below:-

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?

Answer 1

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

Answer 2

If I understand your question correctly, you need to run a different query depending on an external status.

You could add a flag as a DBT Variable1 and use different queries in case you are looking to update or create it again.

Assuming you call your variable "update" and value is a boolean, you can:

{% if update %}
Select c1,c2,c3,
case when c1 = ‘US’ then 3.3 else c4 end as c4
from t1
{% else %}
SELECT * FROM member;
{% endif %}

You can also set the variable at the beginning of the sql file using:

{% set update = True %}