Scenarios

Nothing can say which number is "true", so differences come from meaning, not code. The gap is 4.79 ÷ 4.20, about 14%.

1. Grain: compare each source table's declared grain; look for subtotal rows, or rows repeated once per campaign touch.
2. Identity: do both dashboards count the same entity (orders, or customers)?
3. Time: "as was" or "as is" dimension versions, and which time zone.
4. Boundary: are they reading different copies of the same attribute?

A fast test: reconcile row counts and distinct order numbers at the lowest grain both share, then bisect by month.

Declare the grain of every table, give each metric one owner in a semantic layer, and run a daily job reconciling the two totals.

Governance becomes a bottleneck. And an unexplained difference signals undefined meaning, not a bug to patch.

Reproducible reports need "as was" values for every attribute used in reports. Choose a type per attribute.

• Email: type 1, overwrite; no history needed.
• Region or territory: type 2, with a surrogate key, effective dates and a current flag.
• Loyalty tier: it changes monthly for everyone, so type 2 would add about 5,000,000 × 12 = 60 million rows a year. Use a mini-dimension (type 4) of the few tier-and-band combinations, referenced from the fact table.

Look up versions by effective-date range, with a policy for inferred members and a check that they expire.

Surrogate keys. If loads must run in parallel, use a hash of (source, natural key), knowing it bakes in trust in the natural key.

Late corrections to the dimension rewrite history in proportion to the facts they touch; type 6 would make backfills proportional to versions; and nothing ever deletes old versions, so plan retention.

Balances are semi-additive stocks, so a periodic snapshot is the right fact type. Computing from events instead needs a running sum over each account's whole history, expensive per query but cheap to compute once. So precompute: one new day per night (about 2 million rows), stored in columnar form sorted by (account, date). Balances change rarely, so run-length encoding compresses it well.

Never sum across days; sum across accounts only within one date; for a period, use the last value or the average. A semantic layer should refuse to sum this measure over time.

Storage grows with accounts × days, and late events force recomputing every later snapshot row from the date they change, so keep events as the source of truth.

Joining facts to a many-to-many relationship repeats each fact once per rep: a $1,000 deal with two reps sums to $2,000.

A bridge table keyed by a rep-group key, with weights that sum to 1 within each group (for example 0.6 and 0.4). Revenue by rep = SUM(amount × weight), which adds up to company revenue.

The sum over reps now equals the total, but each rep's share is only as right as the agreed policy, which no data can validate. Offer a second, clearly named "influenced revenue" measure, correct per rep but never to be summed.

An array of reps on each fact moves the trap to whenever it is unnested; a group key alone loses per-rep queries.

The allocation policy needs an owner and a version, because changing it restates every historical rep number.

The sources are many, contested and changing, and the auditor asks what each source said and when. That fits Data Vault.

Hubs of hashed business keys (source plus key), a link from customer to order, and a satellite per source with load timestamps and the record's source. Deterministic hashes make loading independent of order.

Build a star schema or wide marts on top; the Vault is an addition, not a replacement. Use entity resolution to link records across systems, and store each resolved person with their own surrogate key and version history, since a merge restates historical counts.

Complexity for readers, nothing that deletes old data (plan retention), and hashes that bake in trust in the business keys. With one source and one team, it is pure overhead. Whether cheap columnar compute makes the Vault a sensible default is still unsettled.

The metric is a computation with unstated edge cases (refunds, test accounts, time zone, partial periods), and each team re-derives it.

A semantic layer with one definition per metric name, an owner, and code review for changes, so meaning can be diffed. Different meanings get different names, such as engaged_customers. Count resolved people where possible.

With 40 teams, reconciling pairwise is 40 × 39 / 2 = 780 pairs; shared definitions need 40 agreements.

The layer is a query compiler, so predicate pushdown must work or performance suffers, and it becomes the bottleneck for disputes over definitions. A federated model only moves that cost into contracts between domains.

Start with the ten metrics executives use, publish them first, and retire shadow definitions with a deprecation date.

Materialize at the boundaries where reversibility and trust change. Parsing is mechanical, deduplication loses information, and business logic is opinionated.

Three layers: raw (byte-for-byte, with arrival time), clean (parsed and deduplicated), and business (metrics). Publish guarantees at each boundary (schema, freshness, uniqueness, null policy, grain) and name an owner. A logic bug then means rerunning from the clean layer.

Roughly 3 times the storage, and latency that is the sum of the hops, for example 10 + 15 + 20 = 45 minutes.

If the upstream is an immutable log with long retention, the raw copy only adds retention depth and decoupling.

Ask the provider for a re-export, or rebuild from any copy of the business layer's inputs. Data that no longer exists anywhere cannot be recomputed. And layers without published guarantees are three copies with no safety.

It keeps every future question open, since you can build new projections retroactively. But it costs snapshots (replay time keeps growing), event schemas that are permanent (versioned only by translating on read), no canonical current state, and handling of retractions for corrections.

An activity table, one row per activity per entity, makes temporal questions such as "second order within 7 days" into window queries, without moving the operational system to event sourcing.

Keep operational tables ordinary, feed an activity table from change events, and add a semantic layer: both event approaches push meaning into queries, which then get re-derived by every consumer.

An activity table handles set and allocation questions poorly, and needs engine support for as-of and interval joins. Decide by counting how many questions in the last six months needed history the current model could not provide.