Reference

Data Engineering Cheat Sheet

The patterns a data engineer uses every day — SQL, Python pipelines, file formats, the shell, orchestration and the medallion architecture — on one page.

Analytical SQL

Window function

SELECT customer_id, order_date,
  ROW_NUMBER() OVER (
    PARTITION BY customer_id
    ORDER BY order_date DESC) AS rn
FROM orders;

Deduplicate (keep latest)

WITH ranked AS (
  SELECT *, ROW_NUMBER() OVER (
    PARTITION BY id ORDER BY updated_at DESC) rn
  FROM src)
SELECT * FROM ranked WHERE rn = 1;

Running total

SUM(amount) OVER (
  ORDER BY day
  ROWS BETWEEN UNBOUNDED PRECEDING
           AND CURRENT ROW)

Period over period

amount - LAG(amount, 1, 0) OVER (ORDER BY month)

Python pipeline

Chunked read

for chunk in pd.read_csv(path, chunksize=100_000):
    process(chunk)   # constant memory

Retry with backoff

delay = min(base * 2 ** attempt, cap)
# respect Retry-After on HTTP 429

Atomic checkpoint

tmp = path + ".tmp"
json.dump(state, open(tmp, "w"))
os.replace(tmp, path)   # write-then-rename

Idempotent upsert

INSERT INTO t (...) VALUES (...)
ON CONFLICT (id) DO UPDATE SET ...;

File formats

When to use

CSV / JSON   exchange, small, human-readable
Parquet / ORC  analytics (columnar, compressed)
Avro          streaming, schema attached

Partitioned layout

s3://lake/bronze/orders/
  date=2024-03-15/part-0001.parquet
# filter on date -> partition pruning

Convert CSV to Parquet

import pyarrow.parquet as pq, pyarrow.csv as csv
pq.write_table(csv.read_csv("in.csv"), "out.parquet")

Linux & shell

Inspect first

df -h /data          # disk space
du -sh /path/*       # dir sizes
tail -f pipeline.log

Rank a column

cut -d, -f4 data.csv | sort | uniq -c | sort -rn | head

Permissions

chmod 600 secrets.env   # owner only
755 scripts   644 configs

Cron

# min hour dom mon dow
0 2 * * *  /opt/etl/run.sh >> /var/log/etl.log 2>&1

Ingestion patterns

Three patterns

Full load      copy all, replace
Incremental    WHERE updated_at > watermark
CDC            read the transaction log (deletes too)

Incremental extract

SELECT * FROM src
WHERE updated_at > :last_watermark
ORDER BY updated_at;

full replace   truncate + reload
append-only    immutable events
upsert         mutable entities (default)

Medallion architecture

Three layers

Bronze  raw, as received, append-only
Silver  cleaned, typed, deduplicated
Gold    business aggregates, ready to serve

Silver (dedup + type)

SELECT id, CAST(amount AS DECIMAL(10,2)) amount
FROM (SELECT *, ROW_NUMBER() OVER (
  PARTITION BY id ORDER BY ts DESC) rn FROM bronze)
WHERE rn = 1;

Orchestration (Airflow)

DAG

with DAG("daily", schedule="0 6 * * *",
         start_date=..., catchup=False) as dag:
    extract >> transform >> load

Logical date

# a daily run at 06:00 Mar 17
# has logical_date = Mar 16 (interval start)

Retries

PythonOperator(task_id="load",
  python_callable=load, retries=3,
  retry_delay=timedelta(minutes=5))

Data quality

Checks (should be 0)

SELECT COUNT(*) FROM orders WHERE customer_id IS NULL;
SELECT COUNT(*) - COUNT(DISTINCT id) FROM orders;

dbt generic tests

columns:
  - name: id
    tests: [unique, not_null]
  - name: customer_id
    tests:
      - relationships: { to: ref('customers'), field: id }

Six dimensions

completeness  accuracy  consistency
timeliness    uniqueness  validity

Reliability

Idempotency

# running N times == running once
- upsert on a unique key
- fixed extraction windows (not "last 24h")
- deduplicate by event_id

Error classes

transient  (timeout, 5xx, 429)  -> retry w/ backoff
permanent  (401, schema)        -> fail fast -> DLQ

Projects to build

The fastest way to make this stick. Build one, then put it in your portfolio.

Beginner

CSV-to-Parquet pipeline

Read daily CSVs from a landing folder, validate the schema, convert to partitioned Parquet in a bronze zone, and log row counts.

PythonParquetpartitioningvalidation

Intermediate

API ingestion with watermarks

Pull from a paginated REST API incrementally using a saved watermark, retry transient errors, and upsert into a warehouse table. Make it safe to rerun.

APIsincremental loadidempotencycheckpoints

Intermediate

Medallion batch pipeline

Build bronze → silver → gold for a public dataset: raw ingest, dedup and typing, then a daily gold aggregate, orchestrated with Airflow.

medallionAirflowSQLdbt

Advanced

Data quality framework

A reusable set of checks (nulls, ranges, uniqueness, referential integrity) that run on every batch, store failures, and alert on anomalies.

data qualityanomaly detectionalerting

Advanced

Streaming CDC to lakehouse

Capture changes from a database with Debezium into Kafka, then land them in a Delta/Iceberg table with MERGE for exactly-once current state.

CDCKafkalakehouseMERGE