Data Engineering SQL Usage

Analytical SQL asks a question and returns an answer. Data-engineering SQL keeps a warehouse table correct after thousands or millions of upstream changes arrive. That shift changes the job: the query is no longer just a report, it is a contract for how target state evolves.

This chapter treats SQL as the language of safe table maintenance. You will learn how to decide which rows are new, changed, unchanged, deleted, late, or historically important before a pipeline mutates a target table.

A source table is where new data arrives. A target table is the trusted warehouse table users query. A staging query sits between them: it cleans the source, removes bad keys, chooses one row per business key, and prepares an action plan.

A business key is the real-world identifier for one entity, such as `customer_id`. “One row per business key” means customer 104 should appear once in the target current table, even if the source sent customer 104 twice in one batch.

target table

The warehouse table the load is maintaining.

source slice

The incoming records considered by this run.

load action

The insert, update, delete, or no-op decision for one key.

WITH ranked_stage AS (
  SELECT
    customer_id,
    email,
    tier,
    updated_at,
    is_deleted,
    batch_id,
    ROW_NUMBER() OVER (
      PARTITION BY customer_id
      ORDER BY updated_at DESC, batch_id DESC
    ) AS row_num
  FROM customer_stage
  WHERE customer_id IS NOT NULL
),
clean_stage AS (
  SELECT customer_id, email, tier, updated_at, is_deleted
  FROM ranked_stage
  WHERE row_num = 1
)
SELECT
  s.customer_id,
  CASE
    WHEN t.customer_id IS NULL AND s.is_deleted = 0 THEN 'insert'
    WHEN s.is_deleted = 1 THEN 'delete_candidate'
    WHEN s.email != t.email OR s.tier != t.tier THEN 'update'
    ELSE 'unchanged'
  END AS load_action
FROM clean_stage AS s
LEFT JOIN dim_customer_current AS t
  ON t.customer_id = s.customer_id
ORDER BY s.customer_id;