Data Engineering Foundations Cheat Sheet

source → pipeline → warehouse → people
# you own the path, not the chart

batch:     a file or table, on a schedule
streaming: an event, as it happens
# start with batch unless the decision cannot wait

bronze  raw, as landed
silver  cleaned, typed, deduped
gold    shaped for a question

SELECT o.order_id
FROM orders o
LEFT JOIN customers c
  ON o.customer_id = c.customer_id
WHERE c.customer_id IS NULL;

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

SELECT c.city, s.total
FROM customers c
JOIN (
  SELECT customer_id, SUM(amount) AS total
  FROM orders GROUP BY customer_id
) s ON s.customer_id = c.customer_id;

CREATE TABLE events (
  id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  user_id bigint NOT NULL,
  happened_at timestamptz NOT NULL DEFAULT now()
);

\copy events (user_id, happened_at)
FROM 'events.csv' CSV HEADER;

CREATE INDEX events_user_time
ON events (user_id, happened_at DESC);

import csv
with open('in.csv') as src, open('out.csv', 'w') as dst:
    rows = csv.DictReader(src)
    writer = csv.DictWriter(dst, fieldnames=rows.fieldnames)
    writer.writeheader()
    writer.writerows(row for row in rows if row['id'])

try:
    amount = int(row['amount'])
except ValueError:
    reject(row)
    continue

# run the job twice, land the row once
INSERT INTO events (id, payload)
VALUES (%s, %s)
ON CONFLICT (id) DO NOTHING;