Y

SQL · Intermediate · 5 h

Relational design and safe changes

Normalise tables, wrap changes in transactions, load data with upserts and MERGE, and choose between views and materialized views.

Why employers care: Engineers and senior analysts design the tables everyone else queries and run the loads that keep them fresh. Interviewers ask about 3NF, ACID and how you would load a file of new and changed rows.

In the roadmaps for: Data Analyst, Data Engineer, Software Engineer (Backend)

Lessons, in order

  1. Database normalization: 1NF, 2NF, 3NF with examplesNormalize a real flat sales table: test functional dependencies with SQL, split it into 1NF, 2NF and 3NF tables, prove nothing was lost, and know when to denormalise.18 min
  2. SQL transactions and ACID: COMMIT and ROLLBACKKill a real monthly load halfway with and without a transaction, then make it safe to re-run with BEGIN, COMMIT, ROLLBACK, savepoints and a rejects table.21 min
  3. SQL UPSERT and MERGE: insert or update rowsLoad overlapping daily files with INSERT ON CONFLICT and MERGE, catch duplicate source keys, fix a check-then-insert race, and build a watermark incremental load.20 min
  4. SQL views vs materialized viewsBuild a cleaning view and a monthly revenue materialized view on real data, catch the materialized view going stale, refresh it without blocking readers, and use views for security.16 min

Next shelf: Query plans and performance