1️⃣ raw_orders (messy transactional data)
order_id customer_id order_date total_amount updated_at
101 1 2023-05-01 120 2023-05-01 10:00:00
101 1 2023-05-01 130 2023-05-01 12:00:00
102 2 2023-05-03 80 2023-05-03 09:00:00
103 1 2023-05-10 NULL 2023-05-10 08:00:00
103 1 2023-05-10 150 2023-05-10 11:00:00
104 3 2023-05-12 200 2023-05-12 14:00:00
105 99 2023-05-15 50 2023-05-15 09:00:00
2️⃣ raw_customers
customer_id full_name city signup_date
1 Alice Johnson London 2023-01-10
2 Bob Smith Manchester 2023-02-15
3 Carol White Birmingham 2023-03-05
4 David Brown London 2023-03-20
3️⃣ raw_customer_updates (for SCD logic)
customer_id city updated_at
1 London 2023-01-10 00:00:00
1 Bristol 2023-06-01 00:00:00
2 Manchester 2023-02-15 00:00:00
3 Birmingham 2023-03-05 00:00:00
1️⃣ Deduplicate Orders
Keep only the latest record per order_id based on updated_at.
👉 Use ROW_NUMBER().
Output:
Cleaned order table (no duplicates).
With dedup as (
Select order_id,customer_id,order_date,total_amount,updated_at ,
Row_number()over(partition by order_id order by updated_at desc) as rn
From raw_orders
Select * from dedup where rn = 1
2️⃣ Remove Bad Records
From the deduplicated result:
Remove rows where total_amount IS NULL
Return clean valid orders.
Select * from dedup where total_amount is Not null
3️⃣ Build fact_orders
Create a clean fact table with:
order_id (PK)
customer_id
order_date
total_amount
order_month
order_year
Create table fact_table as
Select
select order_id, customer_id, order_date,total_amount,
extract(month from order_date) as order_month,
extract(year from order_date) as order_year
from raw_order;
4️⃣ Build dim_customers
From customers table:
Add signup_year
Add signup_month
Add customer_tenure_days (today - signup_date)
Create table dim_customers as (
Customer_id integer primary_key,
Full_name string,
City string,
Signup_date date ,
signup_year integer,
signup_month integer,
customer_tenure_days integer
);
Insert into dim_customers
Select customer_id,full_name,city,signup_date, extract(‘year’ from
signup_date) as signup_year ,
extract(month from signup_date)as signup_month,
DATEDIFF(day, signup_date, CURRENT_DATE) AS customer_tenure_days
From raw_customers
5️⃣ Detect Revenue Changes
If an order had multiple updates and amount changed,
identify:
order_id
old_amount
new_amount
change_amount
with
ct0 as (
select order_id,count(*) from raw_orders
group by order_id
having count(*) > 1),
ct1 as
select order_id,total_amount,updated_at,row_number() over(partition by
order_id order by updated_at ) rn,
row_number() over(partition by order_id order by updated_at desc) rn2
from raw_orders where order_id in ( select order_id from ct0)
ct2 as (
select order_id,total_amount as old_amt
from ct1
where rn = 1
),
Ct3 as (
select order_id,total_amount as new_amt from ct1
where rn2 = 1
Select ct2.order_id, ct2.old_amt, ct3.new_amt, ct3.new_amt - ct2.old_amt as
change_amt
From ct2 join ct3 on ct2.order_id = ct3.order_id where ct3.new_amt -
ct2.old_amt <> 0
6️⃣ Data Quality Check
Write a query to detect:
Duplicate order_ids (should be 0 in fact table)
Orders with negative revenue
Orders with future dates
Return count of bad records.
With ct1 as (
Select order_id,customer_id,order_date,total_amount,row_number()over(partition
by order_id order by order_date) as rn
From raw_orders
),
Select count(*) as orders_negative
From ct1
Where total_amount < 0 or order_date > current_date() or rn > 1
7️⃣ Build Customer Revenue Summary Table
Create:
customer_revenue_summary
Columns:
customer_id
lifetime_orders
lifetime_revenue
avg_order_value
first_order_date
last_order_date
select customer_id,count(order_id) as lifetime_orders ,sum(total_amount) as lifetime_revenue,
avg(total_amount) as avg_order_value, min(order_date) as first_order_date, max(order_date) as
last_order_date from raw_orders
group by customer_id
8️⃣ Identify Orphan Records
Find orders where:
customer_id does not exist in customers table.
(This happens often in real systems.)
Select order_id,customer_id from raw_order as ro
Left join raw_customer as rc on rc.customer_id = ro.customer_id
Where rc.customer_id is NULL
9️⃣ Create Slowly Changing Dimension (SCD Type 2 Logic –
Conceptual)
Assume customer city can change.
Write logic to:
Detect when city changes
Create new row with:
o effective_start_date
o effective_end_date
o is_current flag
(You don’t need full dbt logic — just SQL pattern.)
customer_id city updated_at
1 London 2023-01-10 00:00:00
1 Bristol 2023-06-01 00:00:00
2 Manchester 2023-02-15 00:00:00
3 Birmingham 2023-03-05 00:00:00
Select customer_id,city,updated_at as start_Date , lead(updated_at) over(
Partition by customer_id order by updated_at ) as end_date,
Case when lead(updated_at) over(
Partition by customer_id order by updated_at ) is null then ‘True’ else ‘False’
End as is_current_flag
From raw_customer_updates
🔟 Idempotent Logic
Write logic that ensures:
If you run the transformation twice,
it does NOT duplicate data in fact table.
(Hint: Think MERGE or INSERT WHERE NOT EXISTS.)
Merge into fact_order f using raw_orders o
On f.order_id = o.order_id
When matched then
Update set f.customer_id = o. customer_id,
f.order_date = o.order_date
f.total_amount = o.order_amount
when not matched then
insert(
order_id,customer_id,order_date,total_amount)
values( o.order_id,o.customer_id,o.order_date,o.total_amount)