Data should be efficient, changeable, interpretable, and verifiable

Data1[1]'data' means the data itself rather than the code that produces it. For that, standard code quality precepts will do. should be efficient, changeable, interpretable, and verifiable.

Efficient

1. Is the data stored correctly?

• database type
• data types — numeric for money never float; date/timestamp never text for dates
• proximity 2[2]over a network, on a local disk, in memory

2. Are the data transformations structured well?

• order matters
  • filter early on both fields as well as records
• do common things upstream, do idiosyncratic things downstream

3. Is data retrieval optimized?

Retrieval is optimal when you're fetching

• no more and no less than what you need,
• as soon as it is possible to fetch it, and
• structured and formatted in a way that you need to do as little work as possible to make it useable.

4. Is the work done to move it efficient?

• read the plan, don't guess — EXPLAIN ANALYZE
• is the predicate SARGable, or did a function wrapped around the column (extract, lower, a leading-wildcard like) make an index unusable?
• is there an accidental O(n²) hiding in a correlated subquery?
• is pagination keyset, or a whole-table offset scan that gets slower every page?

Changeable

Changeability presents a tradeoff between the costs of storage and retrieval. The goal is to adhere as much to the Liskov substitution principle3[3]open to extension, closed to modification as one can with as much precomputation as we need. Precomputation eases retrieval but at the cost of malleability.

• normalize enough that a fact lives in exactly one place — correcting it should be one write, not a hunt across N duplicated rows
• denormalize close to the destination where data is consumed; precomputation
• prefer named, composable stages (CTEs, views) over deep nesting, so the next person can change one link without unpicking the whole chain4[4]there is a tradeoff to the view though-- performance

Interpretable

how do we make the data easier to understand? data structures should mirror mental models. if they don't, you're prone to error.

1. structure

   • schema design
   • relationships and constraints
   • consistent and intuitive naming conventions
   • data types
   • the single most useful question about any table or query result is what does one row represent? 5[5]know the table's grain — if you can't answer that, no amount of documentation downstream will save the reader

2. documentation

   • formula
   • upstream inputs
   • downstream consumers
   • versioning

3. quality

   • measure:
   • completeness
   • accuracy
   • freshness
   • communicate known issues or gaps using names6[6]tables, columns or categories for categorical data
   • a stray comment somewhere won't help if it's not being read — a name or a constraint travels with the data and stays true

Verifiable

ensure that data produced is what people expect it to be.

Almost every "the numbers are wrong but nothing errored" bug is the same event: the structure of the data stopped matching the mental model you were holding of it, and nothing complained. Verification is a two-step loop, and it starts before you have a result:

1. predict — before you run the query or the load, state what a correct output must look like

   • a row count and grain ("one row per customer, ~2,000; if I get 2,148 I fanned out or picked up orphaned keys")
   • a known total ("revenue was ~$X; 2X means I double-counted")
   • a boundary ("customers with no orders show 0, not 1")
   • output you didn't predict is unverified output, however clean the code looks

2. check against the prediction — reconcile at each stage, not just the end

   • the actual row count against the predicted one
   • if a join was supposed to preserve grain, count before and after and confirm it didn't change
   • the gap between predicted and actual is exactly the drift between your mental model and the real structure