Declare the Grain Before Writing the Query

The grain of a table is the meaning of one row. It might be one order, one order line, one account at the end of a day, or one recorded revision of an account. Tables with similar column names can have different grains. A query that overlooks that difference can return a plausible number with the wrong meaning.

State the grain in a sentence before listing the columns. For example: one row represents one recorded revision of an order line from one source system. That sentence immediately raises useful questions about source identity, revision ordering, deletion and the measure carried by each revision.

Distinguish an entity from its history

Consider three illustrative records:

source  line   revision  amount
alpha   A-101  1         12.00
alpha   A-102  1          7.50
alpha   A-101  2         13.00

There are three recorded versions but only two line identities. Adding every amount produces 32.50, which is not the current line total. If revision 2 replaces revision 1 for A-101, the current total is 20.50. The arithmetic works in both cases; the interpretation determines which arithmetic is useful.

An event table could have a different meaning. If the amounts represented adjustments, summing them might be correct. Do not infer replacement semantics merely because a revision column exists. The producer contract should say whether a row describes state, a change, or an observation.

Write the key that matches the sentence

For a revision table, the source, line identifier and revision together identify the recorded row. For a current-state table, the source and line identifier identify the row. Those are different constraints, even when the same incoming payload supplies both tables.

An illustrative PostgreSQL revision schema is:

CREATE TABLE line_revision (
  source_name text NOT NULL,
  line_id text NOT NULL,
  revision bigint NOT NULL,
  amount numeric(12, 2) NOT NULL,
  PRIMARY KEY (source_name, line_id, revision)
);

This schema states row identity. It does not establish that revisions arrive in order, that amounts use the same currency, or that a higher revision is always valid. Those rules belong in the data contract and processing method. Database constraints and business interpretation work together; one cannot replace the other.

The source prefix matters when different producers can use the same line identifier. Removing it for convenience can combine unrelated entities. Conversely, keeping a source prefix when two sources deliberately describe the same canonical entity requires a documented mapping if reconciliation is expected.

Attach meaning to measures

A measure needs a unit, scope and aggregation rule. An amount without a currency cannot safely be summed with an amount in another currency. An inventory snapshot cannot be added across days and called current inventory. A ratio cannot usually be averaged without the appropriate underlying counts or weights.

Document which dimensions permit addition. Line amounts in one currency may be additive across different lines at the same reporting boundary. A current balance may be additive across distinct accounts while remaining non-additive across successive snapshots of the same account.

Give nulls a meaning as well. Unknown, not applicable and unavailable from the producer are distinct conditions. Using zero to represent all three can make totals look complete while quietly changing the meaning of the data. If downstream work needs those distinctions, carry an explicit state or reason.

Name the reporting boundary

A current-state view needs a rule for choosing the current row. It might use the highest accepted producer revision, not the latest arrival timestamp. Arrival order is unreliable when files are replayed or corrected records arrive late.

An as-of report needs a different boundary. State whether it means producer state at a business instant or information known to the system at that instant. Those questions diverge when a correction arrives today for an event that occurred last week.

Do not hide the boundary inside an unexplained ranking expression. Name the chosen ordering and tie behavior. If two different payloads claim the same source, line and revision, treating one as an arbitrary winner conceals a contract conflict.

Use a fixture that distinguishes meanings

Build a tiny fixture containing one ordinary line, a second revision of that line, another line and an intentionally conflicting record. Inspect the expected rows manually. A test with only one row per entity cannot reveal an accidental sum across revisions.

Check both the output and its declared grain. Does a current-state result contain one row per source and line? Does the revision archive preserve the accepted history? Does the aggregate use only the intended state boundary and unit?

Record these expectations beside the schema and the query. The table's grain should travel with the dataset, because a future reader may see only the exported columns. A precise row sentence, a matching key and a small distinguishing fixture prevent more confusion than a long field list without an explanation of what those fields represent.