Data Engineer SQL Guide

SQL beyond SELECT

Data engineers use SQL to build repeatable transformations, reconcile sources, enforce quality checks, and keep pipelines efficient. Correctness and idempotency matter as much as compact syntax.

• Incremental loads and watermark logic
• Deduplication with window functions
• Slowly changing dimensions
• Data quality assertions
• Transaction and isolation basics
• Partitioning, indexes, and query plans

A safe deduplication pattern

The ordering column must make the winning record deterministic. In production, define what happens when timestamps tie.

WITH ranked AS (
  SELECT
    events.*,
    ROW_NUMBER() OVER (
      PARTITION BY event_id
      ORDER BY ingested_at DESC
    ) AS row_num
  FROM events
)
SELECT *
FROM ranked
WHERE row_num = 1;

What to practice next

Combine joins, CTEs, date logic, and window functions in multi-step problems. Then explain how you would validate row counts, uniqueness, nullability, and performance before shipping the transformation.