Writing Data

01

Goals

Data should be efficient, changeable, interpretable, and verifiable.

Work through the problem in phases; there might be some overlap or circularity, but the only hard rule is that you shouldn't write a query or a schema till Understand and Plan are complete.

Understand

don't touch data whose grain you can't state

  1. Clarify the Question — what decision does this data need to support?
  2. Identify the Grain — one row per what, for every input and for the intended output?
  3. Find the One-to-Manys — where does a join inflate row count? where does a fanout hide?
  4. Find the Nullable Columns — the three-valued-logic traps
  5. Edge Cases
    • Zero: empty tables, no matching rows
    • One: a single row, a single group
    • Many: typical multi-row scenarios
    • Boundaries: nulls, duplicates, orphaned keys
    • Time: late-arriving data, timezone boundaries, midnight/DST edges
    • Systems: what upstream produces this, what downstream consumes it

Plan

predict before you write

  1. Choose the Shape
    • normalize or denormalize — which, and why
    • keys, not-nulls, foreign keys and their on-delete behavior, checks
  2. Design the Transformation
    • what's filtered early vs. late
    • what's common (upstream, once) vs. idiosyncratic (downstream)
  3. Predict the Result
    • the row count and grain
    • a known total, if one exists
    • the invariant or trap you're explicitly avoiding, named out loud

Write

the simplest thing that meets the plan

correct-but-plain beats clever-but-wrong

Review

did reality match the prediction?

  • reconcile the row count against what you predicted, at each stage — not just the end
  • reconcile against a known total
  • for a schema: insert a row that violates each constraint and confirm it's rejected
  • then against the standard: efficient, changeable, interpretable, verifiable
  • any improvement must not change the answer