SQL Reference Guide

Revision Time: 4 mins

19. SQL for Data Engineers Reference

SQL design patterns for ETL, Pipelines, and Data Warehousing.

Deduplication

Return: Value

Use ROW_NUMBER window functions to filter out duplicate records.

Syntax signature:SELECT * FROM (SELECT *, ROW_NUMBER() OVER (PARTITION BY id ORDER BY updated_at DESC) rn FROM t) WHERE rn = 1;

Code snippet:

python

SELECT * FROM (SELECT *, ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY login_time DESC) rn FROM user_logins) WHERE rn = 1;

Expected Output:De-duplicated latest observations.

Remember: Always ensure correct syntax formatting when calling Deduplication.

Incremental Load

Return: Value

Select only modified records since last processed date.

Syntax signature:SELECT * FROM t WHERE updated_at > :last_load_timestamp;

Code snippet:

python

SELECT * FROM orders WHERE updated_at > '2026-07-13 00:00:00';

Expected Output:New or modified orders.

Remember: Always ensure correct syntax formatting when calling Incremental Load.

MERGE

Return: Value

Unified upsert command.

Syntax signature:MERGE INTO target USING source ON keys WHEN MATCHED THEN UPDATE ... WHEN NOT MATCHED THEN INSERT ...

Code snippet:

python

MERGE INTO dim_users t USING staging_users s ON t.user_id = s.user_id
WHEN MATCHED THEN UPDATE SET t.email = s.email
WHEN NOT MATCHED THEN INSERT (user_id, email) VALUES (s.user_id, s.email);

Expected Output:Synced dimension table.

Remember: Always ensure correct syntax formatting when calling MERGE.

UPSERT

Return: Value

Insert record, overwrite on conflict.

Syntax signature:INSERT INTO t (id, val) VALUES (1, 'A') ON CONFLICT (id) DO UPDATE SET val = EXCLUDED.val;

Code snippet:

python

INSERT INTO user_profile (id, city) VALUES (101, 'SF') ON CONFLICT (id) DO UPDATE SET city = EXCLUDED.city;

Expected Output:Overwritten city cell.

Remember: Always ensure correct syntax formatting when calling UPSERT.

JSON

Return: Value

Parse JSON values from column blobs.

Syntax signature:SELECT col->>'key' FROM t;

Code snippet:

python

SELECT meta_data->>'browser' as browser FROM logs;

Expected Output:Parsed browser names.

Remember: Always ensure correct syntax formatting when calling JSON.

CSV

Return: Value

Natively query csv files.

Syntax signature:SELECT * FROM read_csv_auto('file.csv');

Code snippet:

python

SELECT * FROM read_csv_auto('orders.csv');

Expected Output:CSV records.

Remember: Always ensure correct syntax formatting when calling CSV.

SCD Type 1

Return: Value

Slowly Changing Dimension Type 1: Overwrite attributes without tracking history.

Syntax signature:UPDATE dim_table SET attr = source.attr WHERE id = source.id;

Code snippet:

python

UPDATE dim_customers SET address = 'NY' WHERE id = 1002;

Expected Output:Overwrite address.

Remember: Always ensure correct syntax formatting when calling SCD Type 1.

SCD Type 2

Return: Value

Slowly Changing Dimension Type 2: Track history using valid range dates.

Syntax signature:INSERT INTO dim (id, attr, start_date, end_date, active) VALUES (id, attr, today, NULL, 1);

Code snippet:

python

Close active row by setting end_date = today, active = 0, and insert new row.

Expected Output:Two rows: historical and active.

Remember: Always ensure correct syntax formatting when calling SCD Type 2.

Data Cleaning

Return: Value

Standard clean parameters mapping.

Syntax signature:TRIM(LOWER(COALESCE(col, 'default')))

Code snippet:

python

SELECT TRIM(LOWER(COALESCE(email, 'unknown@domain.com'))) FROM users;

Expected Output:Cleaned email formats.

Remember: Always ensure correct syntax formatting when calling Data Cleaning.

ETL Patterns

Return: Value

Chained CTE queries for staging, transformation, and target loading.

Syntax signature:WITH staged AS (...), transformed AS (...) INSERT INTO target SELECT * FROM transformed;

Code snippet:

python

Standard modular layout pattern.

Expected Output:ETL pipeline execution.

Remember: Always ensure correct syntax formatting when calling ETL Patterns.

Previous Topic18. SQL One-Liners Next Topic20. SQL Interview Tricks