Skip to content
Tech Interview Prep

Top 100 Business Intelligence Developer Interview Questions and Answers

The questions most likely to come up in your Business Intelligence Developer interview, ranked by likelihood, with senior-level answers that cover what the interviewer is listening for.

Curated: · Written: · Reviewed:

Reviewed 100Review pending 4

104 questions

Q&A-1Before you design a fact table, what is the first thing you write down?

The first thing I would pin down about declaring the grain of a fact table is which decision the number is meant to support.

Every fact table has exactly one grain (one row per order line, per shipment, per daily snapshot) and every measure, key, and join depends on it. A table whose grain is not stated has one anyway, discovered later by whoever finds the wrong total.

Concretely, state the grain in a sentence, derive the primary key from it, and enforce uniqueness on that key as a test rather than as documentation, because a grain that is described but not enforced drifts the first time a source changes.

The reason for that specificity is a failure I have seen: A table described as one row per order in fact held one row per order line after a source change; order-level counts were 2.4 times too high for 5 months before anyone reconciled them.

Same table, two grains.

Declared grainRowsOrder count reportedCorrect
one per order41,00041,000yes
actual: one per line98,40098,400no, 2.4x

I would not consider it settled without evidence: Assert that the declared key is unique on every load, and fail the load rather than the report when it is not.

A grain that is documented but not enforced is a grain that drifts.

Curated: · Written: · Reviewed:

Q&A-32Where does an order number live?

Assume the order number is the operational identifier the source system assigns: no descriptive attributes, no hierarchy, no history of its own. Then it lives on the fact table as a degenerate dimension: a column on fact_order_line (or fact_order), not a row in its own dimension table.

A dimension table earns its join by carrying attributes you filter or group by. An order number carries none. Every query that counts orders, groups revenue by order, or joins back to the source system already has the order number on the fact row it is reading. Putting it in dim_order with a single column adds a join and a surrogate-key lookup for zero filtering power, and the table has the same cardinality as the distinct order numbers on the fact: no compression, no reuse.

The trade-off is what happens when the order acquires attributes. The moment you need order_channel, shipping_method, order-level discount, or the customer segment as of order time, you have two options:

  1. Keep the order number degenerate and put those attributes on the fact. This works only if the attributes are functionally dependent on the order and the fact grain is the order, or you accept the redundancy across order lines. If the grain is order line, you have now repeated order-level attributes on every line, a fan-out waiting to be summed twice.
  2. Promote to a real dim_order with a surrogate order_key, order_number as the natural key, and the attributes as columns. The fact keeps order_key, and often the degenerate order_number too, for drill-through without a join.

The second is the right call once attributes exist, and it is mandatory if you need type-2 history on those attributes: order status transitions, rep reassignment, shipping address changes. A degenerate dimension cannot hold history; it is a single value on the fact row. If you need to see the order as it was at each status change, that is an accumulating snapshot fact or an SCD2 dim_order, not a column.

Failure mode to watch: a one-column dim_order. It looks tidy in the diagram and costs a join on every query. Detection is easy: count dimensions with fewer than three attributes; a one-column dimension whose column is also on the fact is dead weight.

Degenerate (on fact)dim_order
Extra joins for "count orders by day"01
Rows0one per order
Holds attributesnoyes
Type-2 historynoyes (SCD2)
Use whenidentifier only, no attributesattributes or history needed

So: the order number lives on the fact as a degenerate dimension until the order itself has something to describe. Then it becomes the natural key of a real dimension.

Curated: · Written: · Reviewed:

Q&A-35Should you store a row for every product and day, including zeros?

Short answer: no for a transaction fact, yes for a periodic snapshot. The deciding question is whether zero is a value or an absence.

Ask first what a missing row means for this measure. If the measure is an event count (units sold, orders placed) a missing row means nothing happened, and zero is the correct value to show. If the measure is a level (on-hand inventory, account balance, headcount) zero is a real state that must be recorded, and a missing row is ambiguous: it could mean zero, or it could mean the snapshot job failed. That ambiguity is why snapshots are stored dense.

Now the arithmetic. Take 50,000 SKUs × 365 days = 18.25M product-days per year. If 8% of product-days have a sale, the sparse fact holds ~1.46M rows. At roughly 100 bytes per row in a row store, that is ~1.8 GB dense against ~146 MB sparse: a 12.5× difference. Columnar warehouses (Snowflake, BigQuery, Redshift) compress a zero-heavy dense table well, but the gap stays close to an order of magnitude, and every scan of the dense table reads 18.25M rows to answer a question about 1.46M of them.

So store sparse and generate the zeros at read time:

SELECT p.product_key, d.date_key, COALESCE(f.units, 0) AS units
FROM dim_product p
CROSS JOIN dim_date d
LEFT JOIN fact_sales f
  ON f.product_key = p.product_key
 AND f.date_key   = d.date_key
WHERE d.date_key BETWEEN '2024-01-01' AND '2024-01-31'
  AND p.status = 'active';

The cross join is the cost you are trading for the storage. For one month it is 50,000 × 31 = 1.55M rows before the product filter; for a year it is 18.25M. That is cheap in a columnar engine, but bound the date range and filter the product set, or materialize a date spine so the join is against a small table.

Failure mode when you skip the zero generation: a product that stops selling disappears from the trend instead of dropping to zero, so a decline reads as a shorter list rather than a falling line. Detect it with a reconciliation query: count distinct product_key in the report and compare it to the count in dim_product for the same range. If the report claims to show all products, those two numbers must match.

Do not "fix" this by inserting zero rows into a transaction fact. It corrupts AVG: average units per product-day over the dense table divides by 18.25M instead of 1.46M, understating the mean by about 12.5×. SUM is unaffected; COUNT(*) is not.

Dense is the right call when the measure is semi-additive and every period must be present (inventory, balance), when the table is factless and the row's existence is the fact (promotion coverage, attendance), or when a fixed dashboard cannot afford the cross join and the product set is small enough that the dense table stays cheap.

Curated: · Written: · Reviewed:

Q&A-34Why not join on the source system's natural key?

Core answer. The natural key is owned by the source system, and the warehouse cannot enforce its stability. Joining facts to dimensions on it means every source-side change (a renumbering, a recycled ID, a merger that brings a colliding namespace) silently corrupts the join. A surrogate key is generated and owned by the warehouse, so it is immutable, and it lets one natural key map to several dimension rows, which is what versioned dimensions require.

Mechanism. The warehouse's job is to guarantee referential integrity across time; the source guarantees nothing beyond its own current state. Three concrete ways a natural key breaks:

  • Recycling. Source deletes customer_id = 1001, later reuses it for a different customer. A fact loaded against the old 1001 now points at the wrong person, and nothing errors.
  • Collision. Two systems merge, both numbering from 1. customer_id = 1001 exists in each with different meanings.
  • Reformatting. A source migration strips leading zeros from a CHAR key, or changes case, and every historical join misses.

A surrogate key decouples the fact's foreign key from all of that. For SCD Type 2, the same natural key maps to multiple rows, each with its own surrogate; the fact resolves to the version current at event time.

-- Resolve the fact FK to the dimension version current at event time
INSERT INTO fact_sales (sale_id, customer_sk, amount, event_ts)
SELECT s.sale_id, d.customer_sk, s.amount, s.event_ts
FROM   staging_sales s
JOIN   dim_customer d
  ON   d.customer_nk = s.customer_id
 AND   s.event_ts >= d.valid_from
 AND   s.event_ts <  d.valid_to;   -- '9999-12-31' on the current row

Trace it: dim_customer holds two rows for customer_nk = 'C-1001'. customer_sk = 501 (valid 2023-01-01 to 2024-06-01) and customer_sk = 502 (2024-06-01 to 9999-12-31). A sale at 2024-03-15 matches only 501; a sale at 2024-07-01 matches only 502. A natural-key join would match both rows and double-count the sale.

Trade-offs, when a natural key is fine.

  • Degenerate dimensions. order_number stays in the fact table with no dimension and no surrogate.
  • Immutable, externally governed keys. ISBN, ISO 3166 country codes, IATA airport codes: single authority, never reused.
  • Single source, no versioning. A natural-key join is cheaper: one fewer join and no extra column.

Surrogates cost something real: 4–8 bytes per row plus an index on every dimension and fact, an extra join to resolve back to the natural key for debugging, and ETL machinery to generate and persist the mapping. Hash surrogates (MD5/SHA-256 of the natural key) skip the lookup table but are wider, 16 or 32 bytes, and carry a theoretical collision risk; integer sequences are narrower and collision-free.

Natural key joinSurrogate key
Storagenone extra4–8 bytes/row + index
Join costone fewer joinextra join to dimension
Source changebreaks silentlyunaffected
SCD2 versioningimpossible (one row per NK)one row per version
DebuggabilityNK visible everywhereneeds join back to NK

Failure modes to watch. Recycled IDs, merger collisions, source renumbering, and late-arriving facts that resolve to the wrong version. Detect them by asserting the natural key is unique per source per load and by reconciling fact counts against the source before and after the join.

Curated: · Written: · Reviewed:

Q&A-33A row is deleted in the source. What happens in the warehouse?

What a strong candidate says first

Nothing happens. An incremental load is driven by rows the source hands over; a deleted row produces no row, so no event, so the warehouse keeps it. Row counts and totals drift above the source and the gap only grows. The default is silent, permanent overstatement.

So the first question back is: does the source hard-delete, or does it mark? That decides everything downstream.

Why the standard pipeline misses it

An upsert only acts on rows present in the batch:

MERGE INTO wh.dim_customer AS t
USING stg_customer_delta AS s        -- inserts + updates only
  ON t.customer_id = s.customer_id
WHEN MATCHED AND s.op = 'U' THEN UPDATE SET ...
WHEN MATCHED AND s.op = 'D' THEN DELETE
WHEN NOT MATCHED THEN INSERT ...;

The WHEN MATCHED ... 'D' branch only fires if the delete is in the delta. The tempting fix, WHEN NOT MATCHED BY SOURCE THEN DELETE, is worse: it is correct only when s is a full snapshot. Point it at an incremental delta and it deletes every row the batch didn't happen to contain.

The four ways to actually see a delete

ApproachSees deleteCostCatch
CDC (Debezium, binlog/WAL)yes, op: "d"per-change, lowPostgres needs REPLICA IDENTITY FULL or the before-image is only the PK
Soft delete in source (deleted_at)yes, as an updatelowsource must cooperate; every downstream query filters
Full snapshot diffyesfull scan each runthe only option when the source can't emit deletes
dbt snapshotonly with hard_deletes='invalidate' (dbt 1.9+)mediumdefault is ignore: deleted rows are silently retained

Failure modes worth naming

  • Delete-then-reinsert of the same key in one batch. Unordered, the insert can land after the delete and the row survives. Order by LSN or event timestamp, or collapse to the last event per key before the merge.
  • Kafka tombstones. A delete is a null-valued record; log compaction drops the key after delete.retention.ms (default 24h). Replaying a compacted topic from the start cannot reconstruct the delete: the record is gone.
  • Facts, not just dimensions. End-dating a dimension row leaves the fact rows that referenced it. Either keep a tombstone member so the join still resolves, or propagate the delete to the fact.
  • Erasure requests. A soft delete does not satisfy a GDPR/CCPA deletion; the warehouse copy has to go too.

How you'd catch it in production

Reconcile on a schedule, not on trust:

SELECT COUNT(*) AS stale_rows
FROM src.orders AS s
FULL OUTER JOIN wh.fact_orders AS w
  ON s.order_id = w.order_id
WHERE s.order_id IS NULL;   -- in warehouse, gone from source

Run it daily, alert when the count or the sum delta crosses a threshold, and track max(updated_at) lag on both sides. A cancelled-orders case I've seen ran a year before anyone noticed: 410 deletes in month 1, 5,100 by month 12, revenue reported 2.3% above the ledger the whole time. Illustrative figures, but the shape is the point: the error is monotonic and never self-corrects.

Curated: · Written: · Reviewed:

Q&A-53Why add an explicit unknown row to every dimension?

Assumptions first: a star schema where every fact row carries a foreign key to each dimension, and the source can hand you a fact whose dimension key doesn't resolve. A product code not yet in the master, a customer from a feed that hasn't landed, a late-arriving row.

Add the unknown row so the fact table's foreign keys are never null and every fact row survives the join. That's the whole reason. A null FK isn't a value you can group by: NULL = anything is unknown, so an inner join silently drops the row and the total shrinks. An outer join keeps it, but now every query has to remember to use one, and the SQL your BI tool generates won't. A reserved row with a real surrogate key turns the join back into an ordinary inner join, keeps the total correct, and puts the unmatched slice in its own group where you can see it.

The sentinel rows

Surrogate keyNatural keyLabelMeaning
-1NULLUnknownKey was present but didn't resolve
-2NULLNot ApplicableThe dimension genuinely doesn't apply

Two rows, not one, because they mean different things. Unknown is a data-quality signal: something is broken upstream. Not applicable is a legitimate business state: a warehouse-level inventory snapshot has no customer, and forcing it into "unknown" would make your data-quality alarm fire forever. The usual convention in dimensional modelling is -1 and -2, with the natural key left null so no real key can collide with them.

Load and query

-- fact load: FK columns are NOT NULL; unmatched keys land on -1
INSERT INTO fact_sales (product_sk, customer_sk, net_revenue)
SELECT COALESCE(dp.product_sk, -1),
       COALESCE(dc.customer_sk, -1),
       s.net_revenue
FROM staging_sales s
LEFT JOIN dim_product  dp ON dp.product_id  = s.product_id
LEFT JOIN dim_customer dc ON dc.customer_id = s.customer_id;
-- reporting: plain inner joins, no outer joins anywhere
SELECT p.product_name, SUM(f.net_revenue) AS revenue
FROM fact_sales f
JOIN dim_product p ON p.product_sk = f.product_sk
GROUP BY p.product_name;

What it costs you if you skip it

100,000 fact rows, $2,500,000 total revenue, 4,000 rows with a product code that never resolved. Leave the FK null and the grouped report sums to $2,400,000: the two reports disagree by exactly $100,000, 4%, and nothing in either report says why. That gap is the failure mode: silent, stable, and it survives every reconciliation that compares report to report instead of report to source.

Failure modes of the unknown row itself

  • It becomes a dumping ground. If nobody watches it, unknown grows from 0.1% to 4% and the "correct" total is correct only in the arithmetic sense. Alert on the share per dimension, not on the row existing.
  • It pollutes counts. COUNT(DISTINCT product_sk) and "top 10 products" now include a sentinel. Filter with WHERE NOT p.is_sentinel or product_sk > 0 in those queries.
  • It gets confused with late-arriving dimensions. A late-arriving member should get an inferred row carrying its real natural key, then be updated when the master arrives: a placeholder, not a sentinel. Routing it to unknown loses the key forever.

When not to use it

If the fact genuinely has no such dimension, a daily revenue total with no product grain, don't invent a sentinel to satisfy a schema; that's a grain error. And if the ETL is allowed to halt on an unmatched key, you don't need the row, but halting a nightly load over one bad code is usually the worse trade.

Curated: · Written: · Reviewed:

Q&A-6Sales and support each have their own customer table. What is the problem?

Two customer tables means two keys and two definitions of "customer". Facts in sales and facts in support can only be compared through a dimension they share, so with separate customer dimensions no query combines them honestly: the join either drops rows or multiplies them, and the total depends on which table the analyst started from. The real decision is not "conform or not"; it is where to conform and what that costs.

Why it breaks: sales.customer_id and support.customer_id are independent surrogate keys. Joining the two facts on customer_id matches unrelated customers; joining both to a third table that carries neither key gives a cartesian product per customer. Even with a shared natural key (email, account number) the "active" predicate differs, so the denominator of any retention or churn metric changes with the dimension. The two numbers are not incomparable versions of one metric; they are different metrics wearing the same name.

Illustrative figures from a reconciliation:

DefinitionCustomersRetention reported
active from first order96,00062%
active from first ticket74,00076%
conformed96,00062%

Fourteen percentage points, same entity, two definitions.

Conformed dimension in the mart layer. One customer dimension, one key, one definition of active, owned by one team. This is the default when both facts land in the same warehouse and the entity really is the same customer. The cost is change control: a new attribute or a changed active rule is a schema change with downstream consumers, and the ETL that populates it has to be tested against both sources. You have bought comparability with coupling.

Crosswalk or bridge table. Leave sales_customer and support_customer as they are and add a mapping table to a canonical key. Cheaper to introduce, and the only workable option when the sources are owned by systems you cannot change. The cost is that a bridge is many-to-many, so a fact joined through it double counts unless you constrain it to one row per canonical key per fact, and unmatched rows need an explicit rule: park them under an unknown member rather than dropping them.

Conform only in the marts. Keep the operational stores separate and build the shared dimension in each reporting layer. Least invasive, and it lets each team keep its own model. The cost is that the definition now lives in N places and drifts; the second team to change its copy silently breaks the comparison you built the dimension for.

Don't conform at all. When the two "customers" are genuinely different entities (a distributor and the end user, a sold-to and a bill-to) forcing one dimension destroys the distinction. Keep them separate and join through the fact that carries the relationship, or through a bridge only where a query needs it.

Failure modes to watch: bridge double counting when a canonical key maps to several source rows; type-2 versioning where a fact joins the wrong version; active flags drifting because each team updates its own copy; late-arriving dimension rows dropped by an inner join.

Detection: reconcile the entity counts from each dimension and require the difference to be explained before either is certified. A test that the conformed dimension's key set equals the union of source keys with no duplicates catches the bridge case; a row-count check on each fact joined to the dimension catches the multiplication.

Curated: · Written: · Reviewed:

Q&A-29Why build a date dimension rather than using date functions?

What I'd say in the first two minutes

Assume a columnar warehouse (Snowflake, BigQuery, Redshift class) and a question about fiscal periods, holidays and trading days. My answer: put them in a table when more than one consumer needs them and the definitions change on a business schedule; use date functions when the logic is calendar-only and the consumer is one person exploring data.

Date functions derive what the calendar already encodes: year, month, day-of-week, day-of-year, ISO week. They cannot derive what the business decides. Which dates are fiscal Q1. Which days the exchange was closed. Whether week 1 is the week containing 1 January or the first full week. Which days are working days under a moving-holiday calendar. Those are data with an owner, a review, and a change history.

What the dimension costs

  • Generation and upkeep: a job that materialises rows, a process that extends it ahead of the current date, and a backfill path for when a fiscal calendar is restated mid-year.
  • Storage: negligible. Twenty years is 7,305 rows; at roughly 200 bytes per row that is about 1.5 MB (illustrative sizing).
  • Join cost: a broadcast or hash join against a few thousand rows is noise next to the fact scan. The real cost is the contract: every fact query carries a date key, and an inner join against a dimension with a missing row silently drops facts. Detect that with a row-count reconciliation between the fact table and the joined result, run per load.
  • Maintenance: one more artifact with an owner, a load, and a freshness check.

When date functions are the right call

Ad-hoc exploration, a one-off analysis, or a single report with no fiscal or holiday logic. Also when the source system already stamps the fiscal period onto the fact row, then the dimension is redundant and you are paying for a join you do not need.

The failure that decides it

Fiscal quarter logic was reimplemented in 22 reports. A change to the fiscal calendar was applied to 14 of them, and the two versions coexisted for a full quarter before anyone noticed. Detection is cheap: count the distinct places that independently define a fiscal period and alert when the count exceeds one.

ApproachDefinitions of fiscal periodPlaces to change on a calendar changeCost
Per-report logic2214 (and 8 missed)none up front
Date dimension11one ETL job, one join key, one owner

A fiscal calendar is data, not a formula, but only once more than one consumer needs it and it changes on a business schedule. Below that line, a date function is cheaper and correct.

Curated: · Written: · Reviewed:

Q&A-31An order has an order date, a ship date, and a due date. How many date dimensions?

One physical date dimension, joined three times under three roles. In Kimball terms, a role-playing dimension. The fact table carries three foreign keys (order_date_key, ship_date_key, due_date_key) all pointing at the same dim_date.

Why one table: all three roles share the same grain (one row per calendar day) and the same attributes (day, month, quarter, fiscal period, is_weekend, is_holiday). Three copies means three ETL jobs, three storage footprints, and three chances for the fiscal calendar to be patched in one and not the others. Each join is many-to-one from the fact, so joining dim_date three times does not fan out rows: the fact grain is preserved.

When I'd break the rule: when the roles need genuinely different attributes. Ship date often wants a carrier cutoff calendar; due date often wants a business-day calendar with a different holiday set than the order calendar. If those attribute sets diverge, either add role-specific columns to the shared table (ship_cutoff_time, is_business_day) or split into separate dimensions. Splitting is the right call when the grain differs too: a due-date dimension at hour grain for SLA tracking is a different table, not a role.

Failure modes worth naming:

  1. Ambiguous default. The BI tool picks one relationship as the default and the user never sees which. Detection: the role must be visible in the field name a user selects ("Order Date Month" vs "Ship Date Month"), not only in the model diagram.
  2. Null ship date. An order that hasn't shipped has no ship date. If ship_date_key is null and the query inner-joins dim_date on it, unshipped orders vanish. Detection: compare row counts with and without the ship join; the difference is your open-order backlog.
  3. Silent divergence after copying. If someone does build three tables, the fiscal calendar gets fixed in one. Detection: a reconciliation query comparing each copy's row count and max(load_date).

Artefact: the three joins, aliased:

select
  d_order.month,
  sum(f.order_total) as revenue
from fact_order f
join      dim_date d_order on f.order_date_key = d_order.date_key
left join dim_date d_ship  on f.ship_date_key  = d_ship.date_key   -- null until shipped
left join dim_date d_due   on f.due_date_key   = d_due.date_key
group by 1;

Hypothetical fixture showing why the role matters: same orders, grouped by different roles:

MonthBy order dateBy ship dateDelta
Mar4.10m3.73m-9.0%
Apr3.90m4.21m+7.9%

Both columns are correct; they answer different questions. A report that joined on ship date while the business meant order date moved revenue between months by roughly 9% and the gap was written off as timing noise for a quarter.

Before calling it settled I'd confirm two things: the role is visible in the field name the user picks, and the default role matches what the business means by an unqualified "date".

Curated: · Written: · Reviewed:

Q&A-30A customer belongs to several segments. How do you model that?

Model it as a many-to-many with a junction table. A customer does not own a segment, and a segment does not own a customer; membership is its own entity with its own row.

CREATE TABLE customer (
  customer_id bigint PRIMARY KEY,
  name        text NOT NULL
);

CREATE TABLE segment (
  segment_id  int PRIMARY KEY,
  name        text NOT NULL UNIQUE
);

CREATE TABLE customer_segment (
  customer_id bigint NOT NULL REFERENCES customer(customer_id),
  segment_id  int    NOT NULL REFERENCES segment(segment_id),
  assigned_at timestamptz NOT NULL DEFAULT now(),
  PRIMARY KEY (customer_id, segment_id)
);

CREATE INDEX ON customer_segment (segment_id);

(Postgres 16; the shape is the same in MySQL, SQL Server and Oracle.)

The composite primary key on (customer_id, segment_id) is the whole point: it makes a duplicate membership impossible at the storage layer, so no application bug can double-count a customer into a segment. The two foreign keys stop orphan rows when a segment is deleted. The index on segment_id is the reverse lookup, "who is in this segment", which the PK alone does not serve, because the PK is ordered by customer_id first.

"Customers in both enterprise and strategic" is a group-and-count, not a self-join:

SELECT customer_id
FROM customer_segment
WHERE segment_id IN (1, 2)
GROUP BY customer_id
HAVING count(*) = 2;

A customer in segments 1 and 2 produces two rows, both survive the WHERE, they group to one row with count(*) = 2, and it is returned. A customer in only segment 1 groups to count(*) = 1 and is dropped.

Alternatives, and when they win. A segment_ids int[] or jsonb column on customer is fine when memberships are read-mostly, the set is small, and there are no per-membership attributes: one row read, no join. It loses referential integrity, cannot be indexed for "who is in segment 5" without a GIN index, and every membership change rewrites the customer row. A bitmask column is worse: it caps the segment count at the word size and makes the mapping unreadable. Reach for the junction table unless you can name a reason not to.

Failure modes to name. If memberships need history ("was this customer in strategic in March?"), add valid_from/valid_to and move the key to (customer_id, segment_id, valid_from); in Postgres an exclusion constraint over (customer_id, segment_id, tstzrange(valid_from, valid_to)) with btree_gist prevents overlapping periods. Without it you get two open-ended rows for the same pair and every downstream count is wrong. Detect it with a query that groups by (customer_id, segment_id) and flags count(*) > 1.

The follow-up an interviewer will push on: segment totals are not additive. A customer in three segments has their revenue counted three times if you join revenue to the bridge.

SegmentRevenue attributed
enterprise62m
high growth48m
strategic61m
sum171m
company100m

171m against 100m is the overlap, not a data-quality bug. Either add an allocation rule (split revenue evenly, or by a weight column on the bridge) so totals sum, or label segment totals non-additive and never present them as summing to the company figure. Decide which before the first dashboard ships.

Curated: · Written: · Reviewed:

Q&A-82What metadata should every warehouse table carry?

Six columns cover most warehouses: source_system, source_object, extracted_at, load_run_id on every table, plus valid_from / valid_to where rows are versioned and a record_hash where overlapping extracts need dedupe. Same names everywhere, so a consumer can guess them on a table they have never opened.

Assumptions: batch or micro-batch ELT into a columnar warehouse, finance and analytics consumers, a handful of backfills and restatements a year, one loader per table most of the time.

CREATE TABLE fct_orders (
  order_id      BIGINT       NOT NULL,
  amount_cents  BIGINT       NOT NULL,
  event_ts      TIMESTAMP_TZ NOT NULL,  -- when the business event happened
  -- audit columns
  source_system VARCHAR(32)  NOT NULL,  -- 'shopify'
  source_object VARCHAR(128) NOT NULL,  -- 'orders'
  extracted_at  TIMESTAMP_TZ NOT NULL,  -- when the pipeline read the row, UTC
  load_run_id   VARCHAR(64)  NOT NULL,  -- run id + attempt number
  valid_from    TIMESTAMP_TZ NOT NULL,
  valid_to      TIMESTAMP_TZ            -- NULL = current version
);

Type names vary by warehouse (TIMESTAMP_TZ on Snowflake, TIMESTAMPTZ on Postgres). load_run_id can be the orchestrator's run id, or dbt's invocation_id with an attempt counter appended; the requirement is that it changes per attempt, not per schedule slot. Keep extracted_at separate from event_ts: "when we read it" and "when it happened" are different questions, and conflating them is a common source of wrong as-of reports.

Row-level columns are not the only way to get provenance, and they are not always the right one:

MechanismGranularityRetentionCost
Audit columns on the tableThe rowAs long as the table6 columns on every table; rows written before the columns existed stay NULL forever
Table-format commit log (Iceberg snapshots, Delta history)File or snapshotAs long as the log is keptZero columns, but only if every writer commits through the format
Warehouse-native history (query/job history, time travel)Table or statementDays to months: BigQuery time travel is fixed at 7 days and INFORMATION_SCHEMA.JOBS at 180 days; Snowflake Time Travel is 1 day by default, up to 90 on EnterpriseNothing to carry, but it expires and it cannot split a merged table

The case that decides it is a merge or backfill writing rows from two runs into one table. Job history can tell you the table was written at 03:12; it cannot tell you which of the two runs produced order 8813.

Cost side: on a billion-row fact table those six columns are roughly 60-90 bytes per row raw (a UUID stored as text is 36 of them), so 60-90 GB before encoding. If rows land in run order, run-length encoding collapses extracted_at and load_run_id to nearly nothing; a table re-sorted in place gets no such discount, so measure rather than assume. The cost that bites harder is schema drift: four new columns across 800 models is 800 migrations, and every SELECT * inherits them.

Failure modes worth naming, and how to catch them:

  • A backfill or manual INSERT that bypasses the loader leaves the audit columns NULL. Keep them nullable and assert non-null on today's partition in a test, rather than adding NOT NULL that turns a backfill into an outage.
  • A run that retries twice under one id produces three writes sharing an id. Append the attempt number, or nothing downstream can distinguish them.
  • Restatements producing overlapping valid_from / valid_to for one business key. Assert non-overlap per key; this is the failure that silently double-counts revenue.

The check before calling a table auditable: take a row a stakeholder disputed and answer three things from the row alone, which source object, which pipeline run, as of when. If any of the three needs query history or a Slack thread, the metadata is not there.

Where I would skip the columns: an append-only event table with a single loader that commits through Iceberg or Delta, where the snapshot log already records the run. Partition-level lineage answers that question at the granularity anyone asks it, and the row-level columns buy nothing.

Curated: · Written: · Reviewed:

Q&A-75Should the warehouse mirror the source schema?

Mirror the source as the raw landing layer; don't let it be the query interface. That's the short answer, and it rests on one assumption: the warehouse serves analytical reads (aggregations, history, cross-source joins) while the source is shaped for single-row transactional writes. If your warehouse is explicitly a landing zone with a separate serving layer, mirroring is fine. If analysts query it directly, it isn't.

Why the mirror is right for landing. Landing raw is cheap and robust. You take whatever the source emits, including columns nobody has modelled yet, and you don't block ingestion on schema decisions. Snowflake VARIANT, BigQuery's JSON type, or a STRING column with Delta schema evolution all let a new source column arrive without a migration. That is the mirror's job, and it does it well.

Why it's wrong for serving. Three things break when the source shape becomes the reporting shape:

  • History. An UPDATE in the source overwrites the row. "What did this order look like when it shipped?" becomes unanswerable unless you keep every version: SCD2 dimensions or an append-only change log.
  • Conformed dimensions. Two sources both have a customer. Without a conformed dimension, "revenue by customer" means two different customers.
  • Fan-out. Joins across normalized tables multiply rows. Take orders with one row per order, order_items with 3 rows for order 1, and shipments with 2 rows for order 1. Joining all three yields 3 × 2 = 6 rows for order 1, so SUM(orders.amount) returns 6 × 100 = 600 for a 100 order. Aggregate each fact to its own grain before joining, or model a fact table at the grain you report on.

The failure mode that actually bites. Raw gets exposed "temporarily" while the model is being built, and then it is the interface. I've seen it: analysts were given the raw mirror during modelling; two years later 90 reports depended on raw tables and the modelled layer had 6 consumers.

LayerReports dependingIntended
raw mirror900
modelled696

Once that happens, a source column rename is a 90-report incident, and the modelling team can't change the raw layer without breaking production dashboards. That's why access control matters more than the modelling itself: whatever you expose first becomes the interface.

Detect it, don't assume it. Query logs tell you who reads what. In Snowflake, ACCESS_HISTORY records the tables each query touched; in BigQuery, INFORMATION_SCHEMA.JOBS_BY_PROJECT gives you referenced tables per job. Alert when a raw table gains a new distinct user, and treat significant raw consumption as a modelling gap rather than a governance problem: people read raw because the model doesn't answer their question yet.

Alternatives above the mirror. A star schema (facts plus conformed dimensions) is the default and the easiest to explain to analysts. Data Vault suits sources that churn constantly and you need auditable history per source, at the cost of more joins and a harder learning curve. One-big-table works when there is genuinely one grain and one consumer; it collapses the moment a second grain appears. All three beat exposing the mirror.

What would settle it. Report consumption by layer, and the count of distinct raw tables referenced in the last 30 days. If raw consumption is near zero, the model is doing its job. If it isn't, the model is missing a grain or a dimension, and that's the next thing to build.

Curated: · Written: · Reviewed:

Q&A-74How many transformation layers should a warehouse have?

Short answer: three, by default. Raw, staging, and marts. Two if you have a single source and a single consumer; four only when you need a separate conformance layer or a shared semantic layer. The number is not a style choice, it is the count of change rates you actually have.

First, the assumption that decides it: I mean persisted, materialized stages between source and consumption, not CTEs inside one model. In a batch ELT warehouse (dbt-style, one team owning the pipeline), the default is three.

Each layer earns its place by absorbing a change that would otherwise force a rebuild of something downstream. Raw absorbs source schema drift and preserves what arrived. Staging absorbs typing, deduplication, and quality filters. Marts absorb business definitions. If two adjacent layers absorb the same change rate, you have one layer too many.

LayersShapeBuys youCosts
2raw → martsone rebuild boundarytyping and business logic share a model; a definition change re-runs dedup too
3raw → staging → martsseparate change ratesone extra full copy and one extra hop of freshness lag
4raw → staging → core → martscross-source conformance, shared metricstwo extra copies; a definition change now spans two layers

The fourth layer is the one people add without cause. It is justified when you must conform entities across sources, unifying a customer key from two systems with different natural keys, or when several marts need one agreed metric definition. If neither holds, the core layer is a rename.

Cost is real and measurable. Take a hypothetical 2 TB raw table: staging at 1.8 TB after dedup, marts at 0.6 TB. Three layers is 4.4 TB of stored copies; two layers is 2.6 TB. Nightly rebuild time scales with the same ratio, and every hop adds its own freshness lag, if each layer runs hourly, a mart is up to three hours behind the source, two hours with two layers.

Failure modes both ways. Too few: business logic applied during extraction, so a definitional change forces a re-extract. I have seen this against a source retaining 90 days when three years of history was needed: the history could not be restated. Detect it by asking whether the mart can be rebuilt from raw alone. Too many: a layer that only renames columns, adding a rebuild dependency and lag for nothing. Detect it by counting how many layers a single definition change touches; if it is more than one, the boundary is in the wrong place.

When two suffice: one source system, no cross-source keys, one consuming team, and data small enough that a full rebuild is cheap. When a semantic layer is warranted: multiple BI tools or marts disagreeing on the same metric, where you want the definition in one place rather than copied per mart.

What I would verify before calling it settled: drop the marts and rebuild them from raw, with the source disconnected. If that fails, the layering is decorative.

Curated: · Written: · Reviewed:

Q&A-8Why denormalise for a reporting layer when the source is properly normalised?

Denormalise because the reporting layer's consumer is a person writing ad-hoc SQL, not an application with a fixed query plan. A normalised source is the right system of record: one place to update an attribute, no update anomalies. It is the wrong interface for an analyst, because it pushes join semantics onto them, and every join is a place to get the grain wrong.

Assumptions that decide it: the source is the system of record and stays normalised; the reporting layer is read-mostly and can be rebuilt on a schedule; the consumers are analysts or BI tools writing their own queries; storage is cheap relative to analyst time. If any of those is false: fixed parameterised reports, all consumers are specialists who own the join logic, or the data volume makes duplication prohibitive: the case weakens.

The mechanism is fan-out. A fact table has a grain, say one row per order line. A dimension has one row per entity per validity window. Join a fact to a dimension that has more than one row per key and each fact row multiplies. SUM and COUNT inflate. In a normalised model the path from an order line to a customer attribute may cross several tables, and any one-to-many hop relative to the fact grain does the same thing. A star schema makes the join paths shallow and mostly single-hop from fact to conformed dimension, so the analyst has fewer places to introduce that error. A star schema is one denormalised shape; a single wide table or a materialised view is another, trading the same storage for even fewer joins.

The cost is real: duplicated dimension attributes, ETL to maintain slowly changing dimensions, and a reporting layer that lags the source by the load interval. Late-arriving facts and type-2 dimension rows are the usual failure modes. Detection is reconciliation, not inspection: assert join cardinality in tests (fact-to-dimension must be N:1 for the effective-dated join), and compare a known measure's total at the fact grain against the source.

A worked example, figures illustrative rather than measured:

Model shapeMedian joins per analyst queryReports with a fan-out error, sample of 8
normalised source63
star schema20

The fan-out itself, in SQL. dim_customer is type-2, so customer 42 has two rows:

-- counts customer 42's orders twice
SELECT c.region, COUNT(*) AS orders
FROM fact_orders f
JOIN dim_customer c ON c.customer_id = f.customer_id
GROUP BY c.region;

-- effective-dated join: one dimension row per fact row
SELECT c.region, COUNT(*) AS orders
FROM fact_orders f
JOIN dim_customer c
  ON c.customer_id = f.customer_id
 AND f.order_ts >= c.valid_from
 AND f.order_ts <  c.valid_to
GROUP BY c.region;

Before trusting any of this, sample analyst-written queries against both models and count how many produce a total that disagrees with the source at the fact grain. That number, not the schema diagram, is the argument.

Curated: · Written: · Reviewed:

Q&A-101You're designing a warehouse for BI. When do you choose a snowflake schema over a star, and what do you give up?

The short answer

Default to a star in the BI mart. Snowflake a dimension only when you can name the specific thing it buys you, and in a columnar warehouse that is almost never storage.

Assumption I'd state out loud: the warehouse is columnar (Snowflake, BigQuery, Redshift, Synapse) and the BI tool generates SQL from a semantic model. On a row-store with hand-written SQL the calculus shifts slightly, but not enough to change the default.

What actually differs

StarSnowflake
Joins per dimension11 per level
Category renamerewrite every product row, or rebuildupdate one row
SCD2 on the hierarchyversion the leaf row; parent attributes ride alongversion both levels, then point-in-time join
BI tool modellingsingle-hop, usually auto-detectedexplicit join paths per tool
Storagehigherlower, and irrelevant

The storage row is the one candidates over-weight. 1.2M products carrying a 20-byte category name is 24 MB of repetition; the category table it replaces is 400 rows. You saved nothing that matters, and you paid a join in every query that touches the dimension plus a modelling problem for every tool that assumes one hop from fact to dimension.

When snowflake is the right call

Three cases, and I'd want one of them before I normalise:

  1. The leaf is huge and the hierarchy is volatile. A 50M-row customer dimension where sales territory is reassigned quarterly. In a star, a territory rename is a 50M-row update or a full rebuild. In a snowflake it's one row in a 200-row territory table. That's an operational argument, not a storage one.
  2. The sub-dimension is conformed across several facts. Territory is used by sales, quota, and pipeline facts. One territory table with one owner beats three copies that drift.
  3. The sub-dimension has its own load and owner. If finance maintains the cost-centre hierarchy in a separate system on a separate cadence, making it its own table makes that boundary explicit instead of hiding it in a nightly rebuild.

Where it goes wrong

The failure I see most: snowflaking a hierarchy three or four levels deep, then trying to do SCD2 on it. The fact joins to the leaf version, but the parent attributes have their own history, and the point-in-time join that reconciles the two is something most BI tools cannot express. You end up with a category that was correct in March attached to a product row from June. A star handles this for free: the parent attributes sit on the versioned leaf row, so the version carries the whole hierarchy.

Second: fan-out. A snowflaked level with a many-to-many relationship (product to supplier, several suppliers each) turns a SUM into a double count unless you bridge it, and the bridge is invisible to whoever writes the report.

Third: join inference. Many tools detect a star automatically and need manual relationship definitions for a snowflake. Every new analyst then has to learn the paths, and the ones who don't write the wrong join and get a plausible number.

How I'd decide, concretely

Start star. Snowflake a dimension when either the leaf row count makes a parent-attribute update expensive, or the parent is conformed across facts with a separate owner. Keep the normalised form upstream in staging and integration; the mart is where you denormalise. If a query plan shows the same dimension joined three times, or a report's join count grows every quarter, that is the signal you snowflaked something you should not have.

Curated: · Written: · Reviewed: —

Q&A-7A customer moves region. What should last year's report show?

Last year's report should show the region the customer was in when each sale happened. A closed period's regional split is a historical fact; the customer's current region is a different question, and a report titled "2024 revenue by region" answers the first one.

The assumption that matters is that this is a period report over a closed window, not a current-customers snapshot. If the business genuinely wants the current book, that report should be named for it and dated as of today, not presented as last year's.

Mechanism. Keep the customer dimension versioned: one row per customer per region period, with effective dates and a surrogate key. The fact row carries the surrogate key of the version that was current when the sale happened, so the join is a plain equality and the historical attribution is fixed at load time. A Type 1 overwrite keeps one row per customer and updates region in place; every fact row then joins to the current value, and the past moves with it.

Fixture: customer C1 sold 400,000 on 2024-03-15 while in North, then 600,000 on 2024-09-20 after moving to South on 2024-07-01. C2 stayed in North and sold 250,000 on 2024-05-10.

dim_customer (Type 2, valid_to exclusive):

customer_keycustomer_idregionvalid_fromvalid_to
101C1North2023-01-012024-07-01
102C1South2024-07-019999-12-31
103C2North2023-01-019999-12-31
SELECT d.region, SUM(f.amount) AS revenue
FROM fact_sales f
JOIN dim_customer d
  ON d.customer_id = f.customer_id
 AND f.sold_at >= d.valid_from
 AND f.sold_at <  d.valid_to
WHERE f.sold_at >= DATE '2024-01-01'
  AND f.sold_at <  DATE '2025-01-01'
GROUP BY d.region;

Trace: the 400,000 sale matches key 101 (North), the 600,000 sale matches key 102 (South), the 250,000 sale matches key 103 (North). North 650,000, South 600,000, total 1,250,000. Under a Type 1 overwrite the same query with no date predicate puts C1's full 1,000,000 in South: North 250,000, South 1,000,000. The total is unchanged; the split is not, and the North figure that was reported last year has silently dropped by 400,000.

Trade-offs. Type 2 costs a wider dimension, a surrogate key on every fact, and a decision about late-arriving facts: a sale loaded after the customer moved must be attributed to the version valid on the sale date, not the current one, so the load looks up the version by date rather than taking the current row. Type 3 (a previous_region column) holds one prior value and breaks on the second move. Type 6 keeps the current value on every version row so current-state queries stay cheap, at the cost of two columns to keep consistent. If the only question ever asked is current state, Type 1 is right and cheaper; the mistake is using it for a period report. A report already presented should be reproducible byte-for-byte, which is the same requirement as versioned history, not a third option.

Failure modes. The overwrite failure is silent: nothing errors, the closed report just changes. Detection is a reconciliation that re-runs a closed period before and after a dimension load and diffs the regional split, plus the invariant that the sum across regions equals the period total. The overwrite preserves the total, so a total-only check passes while the split is wrong. A second failure is a range join on dates without a surrogate key: it works, but scans the dimension per fact row, and it double-counts if two versions overlap because a valid_to was written inclusive on one row and exclusive on the next.

Curated: · Written: · Reviewed:

Q&A-103A customer's address changes. Walk me through how you track that history in the dimension, and when is keeping every version not worth the cost?

First question back: which address? The billing address printed on an invoice is a fact about the order: it belongs on the fact table, snapshotted at order time, and never changes. The customer's mailing address is a dimension attribute, and that is the one that needs a history decision.

Type 1 overwrites. The row is updated in place. No history, one row per customer, cheapest to join and simplest to reason about. Correct for fixing a typo in a name or backfilling a missing postcode: the old value was wrong, not different.

Type 2 adds a row per version. The dimension gets a surrogate key, effective dates, and a current-row flag:

customer_skcustomer_idaddressvalid_fromvalid_tois_current
1001C-8812 Mill Lane, Leeds2019-03-012022-06-30false
1044C-884 Quay St, Bristol2022-07-012024-01-14false
1090C-889 Rowan Rd, Cardiff2024-01-159999-12-31true

The fact table stores customer_sk, resolved at load time by finding the version whose range contains the order date. That is the whole point: when the address changes, no fact row is touched.

-- point-in-time: what the customer looked like when the order was placed
select f.order_id, f.order_date, d.address
from fact_order f
join dim_customer d on f.customer_sk = d.customer_sk
where f.order_date between d.valid_from and d.valid_to;

-- current state, for operational reporting
select * from dim_customer where is_current;

Use 9999-12-31 rather than NULL for the open valid_to, so between works without an or valid_to is null that kills the join.

Is it worth it? Do the arithmetic. 5M customers, roughly 11% changing address a year (illustrative), five years: 5M + 5M × 0.11 × 5 ≈ 7.75M rows. A 55% growth, and the point-in-time join is still cheap. Now run the same Type 2 treatment on a loyalty tier that changes monthly: 5M × 12 × 5 = 300M rows, and every join gets slower for a question nobody asks. That is the line. Fast-changing attributes go in a mini-dimension (Type 4) or stay Type 1 with the change history in a separate audit table.

The test is whether anyone will slice historical facts by the attribute. Sales territory for commission disputes, customer segment for cohort retention, credit band for risk: yes. If no one has ever asked "as of", Type 1 plus an audit table is cheaper and the reports are simpler. Type 3 (a previous_address column) only answers "is it different from now" and is rarely worth the column.

Failure modes to name. Closing the old row and inserting the new one must be one transaction; otherwise you get two is_current = true rows and every current-state join silently doubles. Late-arriving changes create overlaps or gaps: check with lag(valid_to) over (partition by customer_id order by valid_from) against valid_from - 1 day and alert on both. And decide Type 1 versus Type 2 per column, not per table: address Type 2, phone Type 1 is a perfectly normal dimension.

Curated: · Written: · Reviewed: —

Q&A-2A measure sums correctly across product and gives nonsense across time. What is going on?

I would start additivity of a measure from the grain, because most wrong totals resolve to one.

Measures are fully additive, semi-additive, or non-additive, and the classification is a property of the business meaning rather than of the data type. A balance or an inventory level sums across every dimension except time, where the correct aggregation is a period-end or an average.

Concretely, declare additivity per measure in the model and define the non-summing aggregation explicitly, so a user dropping the measure onto a time axis gets the correct total rather than an accumulation.

The reason for that specificity is a failure I have seen: An account balance summed across 12 months reported 14.2 million against an actual year-end balance of 1.2 million; the report had been in use for two quarters.

Balance aggregated three ways.

AggregationValueCorrect for
sum over 12 months14.2mnothing
period end1.2mbalance at year end
average of month ends1.18maverage balance held

I would not consider it settled without evidence: Test each measure's total against a hand calculation on a fixture that spans more than one period.

Summing is a choice, and for some measures it is the wrong one.

Curated: · Written: · Reviewed:

Q&A-4Why can you not add the distinct customer counts from two regions?

Distinct counts are set cardinalities, not sums of parts. Adding them is valid only when the groups are disjoint; regional customer sets overlap, so the sum counts every multi-region customer once per region.

The arithmetic is inclusion-exclusion:

|A ∪ B| = |A| + |B| − |A ∩ B|

The overlap term is exactly the error you introduce by adding. With three or more regions it generalises to an alternating sum over all intersections, which is why the gap is not something you can eyeball.

Illustrative example: two regions, one customer base:

RegionDistinct customers
north71,000
south57,000
sum of parts128,000
distinct over union96,000
overlap (north ∩ south)32,000

128,000 − 96,000 = 32,000 = |north ∩ south|. The summed figure is not slightly high; it is high by exactly the number of customers who transacted in both regions, and that fraction is a property of the business, not of the query. A retailer with national accounts can see overlap above 40%; a utility with disjoint service territories sees near zero and the sum happens to be right.

What to do instead, in order of preference:

  1. Compute the distinct count at the grain you are reporting. One query over the union of the rows, grouped by whatever dimension you actually need.
  2. If volume forces pre-aggregation, pre-aggregate into a structure that supports union, not into a scalar. HyperLogLog sketches merge by taking the register-wise maximum; with m = 2^14 registers the standard error is about 0.81% (Flajolet et al., 2007), fine for a dashboard and not fine for a billing number. Theta/KMV sketches and exact bitmaps (Roaring) are the other common choices; bitmaps are exact and cheap up to a few million distinct IDs per group.
  3. If you need an additive metric, change the metric. "Customer-days active" or "customer × region pairs" add cleanly because the grain is defined so the rows are disjoint.

Failure modes to watch:

  • Summing distinct counts across time as well as space. Daily uniques do not add into weekly uniques, for the same reason.
  • COUNT(DISTINCT customer_id) after a join that fans out. The distinct hides the duplication, so the number looks plausible while the underlying row set is wrong. Check the row count before and after the join.
  • Averaging distinct counts across days or regions. Same non-additivity, less obvious.

Detection is cheap: run the summed segment counts and the distinct count over the union side by side. The difference is the overlap. If you cannot explain that difference from the data, you do not yet understand the metric.

Curated: · Written: · Reviewed:

Q&A-3Segment conversion rates are 5 percent and 40 percent. What is the overall rate?

Two segment rates on their own don't determine the overall rate. The overall rate is total conversions divided by total sessions, so it depends on how many sessions each segment has. If the two segments are equal in size, the answer is 22.5 percent. If they aren't, the answer sits between 5 and 40 percent, pulled toward the larger segment, and it can land far closer to one end than to the midpoint.

A rate is a ratio of two additive quantities, conversions and sessions. Ratios don't add; only their numerators and denominators do. The aggregate is therefore sum(conversions) / sum(sessions), computed from the raw counts at whatever level the user is viewing. Averaging the segment rates gives every segment equal weight regardless of size, which is a different statistic answering a different question.

Worked example, with segment B small and high-converting:

SegmentSessionsConversionsRate
A38,0001,9005.0%
B2,00080040.0%
total40,0002,7006.75%
unweighted mean of the two rates——22.5%

The unweighted mean of 5 and 40 is 22.5 percent; the volume-weighted rate is 6.75 percent. A budget planned against 22.5 percent would overstate expected conversions by 3.3x on this traffic mix.

The unweighted mean is the right number in one case: when each segment is itself the unit of decision and you want the typical segment rather than the typical session, for example comparing store-level rates where stores are the population of interest, or when segment sizes are an artifact of how the data was pulled. Say which one you mean, because here the two differ by a factor of three.

Failure modes worth naming:

  • Simpson's paradox. Segment A can beat segment B in every segment and still lose overall when the mix differs between them. Detect it by computing the per-segment rates and the aggregate side by side and checking the mix.
  • Averaging a stored rate column. If the warehouse stores conversion_rate per row and the BI tool sums or averages that column, the total is wrong. Detect it by checking that the displayed total equals sum(conversions) / sum(sessions) on a fixture where segment sizes differ deliberately.
  • Missing denominators. If you have only the two rates and no volumes, the overall rate is not computable. Say that instead of averaging.
-- correct: ratio of sums
SELECT SUM(conversions) * 1.0 / SUM(sessions) AS overall_rate
FROM segment_stats;

-- wrong: mean of ratios, weights a 2,000-session segment the same as a 38,000-session one
SELECT AVG(conversions * 1.0 / sessions) AS overall_rate
FROM segment_stats;

Curated: · Written: · Reviewed:

Q&A-5Shipping cost triples after a join. Explain.

The join is at the wrong grain. A header-grain measure like shipping cost is stored once per order; joining that table to a line-grain table repeats the header row once per line, so any SUM over the joined result counts the same shipping cost as many times as the order has lines. Nothing errors, nothing goes null, and the number stays plausible, which is why finance usually finds it, not a test.

Ask two things before touching the query: what grain is the measure stored at, and what is the fan-out of the join. The inflation factor is exactly the average fan-out.

OrderLinesship_cost (header)Rows after joinship_cost summed after join
A112.00112.00
B312.00336.00
C512.00560.00
total936.009108.00

Three orders, 36.00 of real shipping cost, 108.00 reported. The factor is 3 because the orders average 3 lines each. Change the product mix and the factor moves, so the error looks like a pricing or region anomaly rather than a join bug.

-- wrong: header measure fanned out across lines
SELECT SUM(h.ship_cost) AS ship_cost
FROM fct_order_header h
JOIN fct_order_line l USING (order_id);

-- right: aggregate the line fact to order grain first, then join
WITH lines AS (
  SELECT order_id, SUM(extended_price) AS line_revenue
  FROM fct_order_line
  GROUP BY order_id
)
SELECT SUM(h.ship_cost) AS ship_cost,
       SUM(l.line_revenue) AS revenue
FROM fct_order_header h
JOIN lines l USING (order_id);

What survives the fan-out and what does not: MIN/MAX are unaffected, COUNT(DISTINCT order_id) is correct, but SUM, COUNT(*) and AVG are all distorted. AVG(ship_cost) after the join is a line-weighted average, not an order-weighted one, so it drifts toward the shipping cost of orders with many lines.

Fixes, in order of preference. Keep the two facts in separate queries and combine at the reporting layer: no join, no fan-out. If they must meet, pre-aggregate the finer fact to the coarser grain first, as above. If you genuinely need shipping cost attributed to individual lines, that is an allocation decision, not a join: divide by that order's line count, or split by revenue share, and label the column as allocated. Those two choices give different numbers, so the business owns the pick.

Failure modes worth naming. SELECT DISTINCT does not fix it: the line columns differ per row, so the rows are not duplicates and nothing is removed. Dividing the joined total by a single global average line count fixes the aggregate but corrupts every per-order figure. And a LEFT JOIN from header to line silently drops orders with no lines from the line-side measures while keeping their shipping cost, so the two totals move in opposite directions.

Detection is a reconciliation, not a schema check. Assert that the measure is identical before and after the join:

-- must return zero rows
SELECT h_total, j_total
FROM (SELECT SUM(ship_cost) AS h_total FROM fct_order_header) h,
     (SELECT SUM(h.ship_cost) AS j_total
      FROM fct_order_header h JOIN fct_order_line l USING (order_id)) j
WHERE h_total <> j_total;

Run it as a singular test on the joined model, and add a row-count assertion that the join preserves COUNT(DISTINCT order_id) from the header table. A relationships test will not catch this; the keys are valid, the grain is not.

One trade-off: BI tools with symmetric aggregates (Looker, for instance) compute the measure correctly across a fan-out when the view declares a unique primary key, so the same query is safe there and wrong in raw SQL. That is a property of the tool, not of the join: do not carry the assumption into a warehouse query.

Curated: · Written: · Reviewed:

Q&A-52A total falls after adding a join. What happened?

My answer to joins that silently drop rows begins with the definition, agreed before anything is built.

An inner join drops rows with no match, so joining a fact to an incomplete dimension quietly removes facts from the total. The report still runs and the number is simply lower, which is the hardest kind of error to notice.

Concretely, use a left join from the fact and route unmatched rows to an explicit unknown member rather than dropping them, and assert that the fact row count is unchanged across the join.

The reason for that specificity is a failure I have seen: An inner join to a product dimension dropped 3,100 facts whose product had not yet loaded; revenue read 1.8 percent low for the days affected and recovered on the next full refresh, which made it look like a timing artefact.

Rows through the join.

JoinFact rows inFact rows outRevenue
inner172,000168,9001.394m
left with unknown member172,000172,0001.420m

I would not consider it settled without evidence: Compare fact row counts before and after every dimension join and fail on a difference.

An inner join is a filter you did not write.

Curated: · Written: · Reviewed:

Q&A-26The same measure returns a different number in the total row than in the detail rows. Is that a bug?

Short answer: usually not a bug. A measure is evaluated against whatever filter context applies where it appears, and the total row is a different filter context from the rows above it. For a non-additive measure (a ratio, a distinct count, a semi-additive balance) computing the total over the whole set is the correct behaviour, and it will not equal the sum of the detail rows. It is a bug when the measure is additive and the total still disagrees, because that means a filter is being applied that the author did not intend.

So the first move is to say what the total should mean, then check the measure against that. "Different from the rows" is not the same as "wrong".

Why the total is a different calculation

In DAX, a measure is evaluated once per cell. The detail row for product A carries a filter on Product = A; the total row carries no product filter, or the full set. That is the whole mechanism.

  • SUM(Sales[Amount]) is additive: the total row sums over all products and matches the detail rows.
  • DIVIDE(SUM(Margin), SUM(Revenue)) is not: the total row recomputes the ratio over the whole set, not the average of the row ratios.

Three products, totalled two ways:

ProductRevenueMarginMargin %
A900,00090,00010%
B10,0005,40054%
C4,0002,20055%
computed total914,00097,60010.7%
sum of row %119%, wrong

The total margin is 97,600 / 914,000 = 10.7%. Summing the row percentages gives 119% for a mix that is 10.7% margin. Averaging them gives 39.7%: also wrong, and it weights a 4,000-revenue line the same as a 900,000 one.

Where it actually breaks

  • Sum of ratios. Someone "fixes" the total by summing row percentages so the column adds up. The number is now meaningless.
  • Average of ratios. AVERAGE over the ratio column, same problem, less obvious.
  • Distinct counts. DISTINCTCOUNT(Customer) in the total counts distinct customers across all products, which is less than the sum of the per-product distinct counts. Expected, not a bug.
  • Semi-additive balances. Closing inventory or headcount should be the last non-empty date in the total, not the sum over dates. In DAX that is LASTNONBLANKVALUE or a CALCULATE with LASTDATE.

How to tell which one you have

Recompute the total from the underlying numerator and denominator and compare it against what the visual shows. If the base columns are additive and their totals match, the ratio total should be numerator total / denominator total. If it is not, the measure is overriding context somewhere: look for a CALCULATE, an ALL, or a filter the report author added.

Fix it in the measure, not the report

Express any context override explicitly inside the measure. If the total should ignore a slicer, say so with REMOVEFILTERS or ALLSELECTED inside CALCULATE. If the total should genuinely be a sum of rows, use SUMX over the iterated table. Arranging the visual so the total happens to look right breaks the moment someone adds a slicer or a new column.

A total is its own calculation, not a sum of what is above it. Test totals separately from detail rows.

Curated: · Written: · Reviewed:

Q&A-25Daily totals disagree between two reports. Both query the same table.

First, I'd clarify what "same table" means. Are both reports reading the same physical table, or the same logical dataset? Even if the table is identical, the queries can differ in filters, joins, time zone handling, aggregation, or execution time. The disagreement is a symptom of a difference in query logic or data state, not the table itself.

I'd assume both reports run against the same source table but may have different SQL, session settings, or run at different times. The top causes, in order of likelihood:

  1. Time zone / day boundary. One report uses UTC, the other uses a local zone. A transaction at 23:30 UTC falls on different days.
  2. Filters. One excludes test accounts, refunds, or a specific status; the other doesn't.
  3. Joins. One joins to a dimension table that filters rows (e.g., only active users).
  4. Late-arriving data / refresh cadence. One report runs before the ETL job finishes, so it sees fewer rows.
  5. Aggregation logic. One uses COUNT(DISTINCT user_id), the other SUM(amount); or one treats NULLs differently.
  6. Rounding or data type. One casts to DECIMAL(10,2), the other to FLOAT.

To isolate, I'd diff the SQL, compare row counts, and run both queries with the same filters and time zone. A quick diagnostic: run SELECT COUNT(*), SUM(amount) FROM sales WHERE date = '2025-01-15' and compare to each report's underlying query.

Here's a worked example. Suppose the table is sales(id, amount, created_at, status). Report A runs:

SELECT date_trunc('day', created_at AT TIME ZONE 'UTC') AS day,
       SUM(amount) AS total
FROM sales
WHERE status = 'completed'
GROUP BY 1;

Report B runs:

SELECT date_trunc('day', created_at AT TIME ZONE 'America/New_York') AS day,
       SUM(amount) AS total
FROM sales
WHERE status = 'completed'
GROUP BY 1;

For 2025-01-15, Report A shows 142,000; Report B shows 136,400. The difference is 5,600 (3.9%). A transaction at 2025-01-16 02:00 UTC falls on 2025-01-16 in UTC but on 2025-01-15 in America/New_York, shifting that revenue. The month total is identical because the shifted transactions fall back into the same month. This is a time zone issue, but it's only one candidate. If the SQL were identical, I'd check the run time: Report B might have run at 02:00 UTC before the 03:00 UTC ETL load, missing late-arriving rows.

Trade-offs: Standardizing on UTC fixes time zone drift but may not match the business day; using a materialized view fixes refresh cadence but adds storage and latency; adding filters fixes the symptom but can hide data quality issues. I'd fix the root cause and add a daily reconciliation check that compares row counts and sums at the day level, not just month.

Failure modes: If you only check monthly totals, daily differences can cancel out. If you don't pin the exact query, you might fix the wrong thing. Always compare at the granularity of the report.

Curated: · Written: · Reviewed:

Q&A-22A pre-aggregated table makes the dashboard fast. What did you accept?

What I accepted: bounded staleness and a fixed set of dimensions.

The dashboard reads a table rebuilt on a schedule, so every number on it is a snapshot from the last rebuild, not the current state of the detail table. I accepted that gap (in the case I'd quote, a 5-minute refresh with a 15-minute alarm threshold) and I accepted that the table only answers the dimensions baked into it. A new "break down by plan tier" request means a new aggregate and a backfill, not a query.

Why it diverges. An aggregate is a materialized query result. Between rebuilds it is a photograph; the detail table keeps moving. That is the whole trade: you bought a scan of 40M rows down to 40K (illustrative numbers, but that is the shape of the win) and paid with a window in which the two disagree.

What you can and cannot re-aggregate. Most of the pain hides here. Rolling hourly buckets into daily ones, some functions survive and some do not:

FunctionRe-aggregatable across buckets?
SUM, COUNT, MIN, MAXYes
AVGNo: carry sum and count, divide at read time
COUNT(DISTINCT user_id)No: distinct counts do not add; overcounts
p95 latencyNo: keep a sketch (ClickHouse quantilesState, t-digest)

Materialize avg or count(distinct) per hour and sum them and you get a wrong number that looks plausible. That is the failure mode I would name first.

Concrete implementations, and what each lets you accept.

  • TimescaleDB continuous aggregates: add_continuous_aggregate_policy('metrics_hourly', start_offset => INTERVAL '1 month', end_offset => INTERVAL '1 hour', schedule_interval => INTERVAL '1 hour'). The end_offset is the staleness you accepted. Setting timescaledb.materialized_only = false makes queries union the materialized region with raw data for the recent window: current numbers, at the cost of a slower query on the hot edge.
  • BigQuery materialized views: by default a query that would need data newer than the view falls back to the base tables; setting max_staleness trades that fallback for stale reads. That is the knob that says out loud how much lag you will serve.
  • ClickHouse: AggregatingMergeTree with the -State/-Merge combinators, so uniqState merges into an approximate distinct count. Approximate is the acceptance.

Failure modes, and how they surface.

  • Late-arriving events land in a bucket already materialized. Worked example: the aggregate reads 1.42M at 06:00 and still 1.42M at noon while the detail table reads 1.71M. A 20% gap that closes only when the bucket is re-materialized. Fix: refresh a trailing window, not just the newest bucket.
  • A backfill rewrites history and the chart shows a step change that never happened operationally.
  • Timezone or DST bucket boundaries double-count or drop an hour.

Detection is a reconciliation job: run the aggregate's own definition against the detail table for yesterday, compare to a stated tolerance (0.1%, say), alarm on breach. Plus a freshness column on the dashboard, max(updated_at), and grey out tiles past the threshold rather than showing a number you cannot defend.

When not to. If the number pages someone or moves money, do not serve it stale. Keep those on the detail path.

Curated: · Written: · Reviewed:

Q&A-28How do you compute a running total that survives filtering?

The 60-second answer. Compute the running total at query time with a window function over the rows that survived the filter: never read it from a stored cumulative column. SQL evaluates clauses in the logical order FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY → LIMIT, and window functions are evaluated in SELECT. By then WHERE has already removed rows, so the window accumulates over exactly the filtered set. A stored running total was accumulated over one row set and is wrong for every other one.

-- PostgreSQL 16
SELECT
  month,
  region,
  amount,
  SUM(amount) OVER (
    PARTITION BY region
    ORDER BY month, sale_id          -- tiebreaker: month alone is not unique
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
  ) AS running_total
FROM sales
WHERE region = 'north'
  AND month >= DATE '2024-01-01';

Trace on three north rows (Jan 120, Feb 140, Mar 130): WHERE keeps all three, one partition, frame grows row by row → 120, 260, 390. Add a south row and PARTITION BY region gives it its own 0-based accumulation instead of continuing the north series.

The two traps.

  1. Default frame. With ORDER BY and no frame clause, the default is RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW, and under RANGE "current row" means the last peer: every row sharing the same ORDER BY value. Two January rows of 120 and 50 both report 170. ROWS plus a unique tiebreaker makes the order deterministic. With no ORDER BY at all, the default frame is the whole partition, so SUM(amount) OVER (PARTITION BY region) is a partition total, not a running one.

  2. Filtering after the window. If you want "the running total through March, showing only March", the window must run in a subquery and the filter go outside it:

SELECT * FROM (
  SELECT month, region, amount,
         SUM(amount) OVER (PARTITION BY region ORDER BY month, sale_id
                           ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS rt
  FROM sales
) t
WHERE month >= DATE '2024-03-01';   -- rt = 390, not 130

Putting month >= '2024-03-01' in the inner WHERE gives 130, because the window then never saw January or February. Both are correct; they answer different questions.

Alternatives, and when each wins.

ApproachCorrect under ad-hoc filtersCost
Window function at query timeYes, any filterSort of the filtered set by (partition, order); an index on (region, month) can supply that order and skip the sort
Stored cumulative column on the fact tableNo: fixed to one row setCheapest read, wrong answer
Pre-aggregated rollup keyed by (region, month) with a cumulative columnOnly for filters on those exact dimensionsCheapest at scale; breaks the moment someone filters by product

Pre-aggregation is the right call when the filter dimensions are fixed and few, the fact table is large, and query volume is high: a daily region rollup feeding a dashboard. It is the wrong call for ad-hoc slicing, which is exactly where the window function earns its sort.

Failure modes to name in the room. Non-unique ORDER BY under the default RANGE frame silently merges peers. A filter applied outside the window when you meant inside it (or the reverse) returns a plausible wrong number with no error. SUM skips NULLs, so a gap in amount doesn't break the series but does make COUNT(*) and COUNT(amount) diverge. And on a large table the window's sort is the cost: check EXPLAIN (ANALYZE, BUFFERS) for a Sort node above the scan before promising a latency.

Curated: · Written: · Reviewed:

Q&A-19A month-over-month column is built with LAG and the growth figure is wrong for two regions. What did the query assume?

I would settle period-over-period with LAG against a hand calculation on a small fixture before trusting the model.

LAG returns the previous row inside the partition, taken in the window's order, not the previous calendar period. A period-over-period figure is only defensible when every region-period pair exists exactly once and the prior period is guaranteed to be there.

Concretely, aggregate the measures to one row per region and period first, left join them onto a spine of every period in range taken from the date dimension, then apply LAG within region ordered by period and leave the first period's null as not comparable.

The reason for that specificity is a failure I have seen: A growth column ran LAG over region-month rows where absent months had no row at all; three of twelve regions lost their February rows to a late extract, so March was compared with January and reported +38% where the true February-to-March change was −6.1%.

February never arrived, and LAG looked back one row.

MonthRows loadedRevenue in factRow LAG sawGrowth shownIf February had loaded (147)
Jan1100———
Feb0————
Mar1138Jan (100)+38.0%−6.1%
Apr1130Mar (138)−5.8%—

138 / 100 − 1 = +38.0% as printed. 138 / 147 − 1 = −6.1% as it should have been. April is fine again, because March was a row.

I would not consider it settled without evidence: Show the result grid for a region with a gap, and name the test that catches it: every region-period pair in range must return exactly one row, and a growth cell must be null rather than filled when the prior period is genuinely missing.

LAG finds the previous row; the report needs the previous period.

Curated: · Written: · Reviewed:

Q&A-27How would you build a like-for-like comparison against last year?

The short answer: don't offset by days. Align the two periods on the boundary that actually drives the metric (fiscal period, weekday, trading day, holiday position) then compare. A 365-day offset moves Monday to Sunday; a 364-day offset keeps the weekday but slides moveable holidays across the boundary. Both give you a number that is mostly calendar.

Why the offsets fail. Take April 2024 against April 2023. 2024-04-01 is a Monday; minus 365 days lands on 2023-04-02, a Sunday. Minus 364 days lands on 2023-04-03, a Monday: weekday-aligned, which is why 364 is the usual fix. But Easter Sunday was 2024-03-31 and 2023-04-09. The 2023 Easter trading week (Mon 3 – Sun 9 April) sits inside the April window; the 2024 one (Mon 25 – Sun 31 March) does not. The 364-day comparison charges 2023 for a peak week and 2024 for none.

Worked trace, hypothetical figures for a UK general retailer:

  • April 2024 revenue: £4.10M over 30 days.
  • April 2023 revenue: £4.61M over 30 days, of which the Easter week Mon 3 – Sun 9 April contributed £0.63M.
  • Naive year-on-year: 4.10 / 4.61 − 1 = −11.1%.
  • Like-for-like, Easter week stripped from 2023: 4.61 − 0.63 = £3.98M; 4.10 / 3.98 − 1 = +3.0%.

The 364-day offset reports the same −11.1%, because the Easter week falls inside April 2023 whether the window starts on the 2nd or the 3rd. The offset only decides whether the boundary day is a Sunday or a Monday; it does not decide whether the holiday is in the window.

AlignmentChange reported
minus 365 days−11.1%
minus 364 days−11.1%
fiscal period, Easter week excluded from both+3.0%

The mapping I'd build. One row per (current day, prior day) pair, generated from a date dimension that carries fiscal period, day of week, trading-day flag and holiday flags:

-- Postgres 16. date_dim: one row per calendar date.
SELECT c.date_key AS cur_date, p.date_key AS prior_date
FROM   date_dim c
JOIN   date_dim p
  ON   p.fiscal_year   = c.fiscal_year - 1
 AND   p.fiscal_period = c.fiscal_period      -- same fiscal week/month index
 AND   p.day_of_week   = c.day_of_week        -- Mon -> Mon
 AND   p.is_trading_day = c.is_trading_day;

Aggregate facts through that mapping rather than through a date - 364 predicate. For seasonal categories, swap the fiscal-period join for a "weeks relative to Easter" offset column on the dimension.

Choosing the alignment. Weekday alignment is right when day-of-week drives the metric: footfall, covers, call volume. Fiscal-period alignment is right for anything reported to finance, but watch the 53-week year: the 4-5-4 retail calendar inserts a 53rd week roughly every five to six years, and if you don't exclude it the comparison gains a whole week. Holiday-position alignment is right for confectionery, travel and garden, where the Easter or half-term week is the whole story. When trading-day counts differ, compare revenue per trading day and say so.

Failure modes and how they show up.

  • Partial current period against a full prior period. Twelve days against thirty reads as a collapse. Restrict both sides to days elapsed, or the number is meaningless.
  • Estate changes. Like-for-like normally means the same store set in both periods. State whether the figure is total estate or LFL estate; a refit or closure inside one period breaks the comparison silently.
  • Timezone boundary. A midnight-local cutoff versus UTC shifts a day's revenue across the boundary. Pick one and apply it to both periods.
  • Detection. Assert that the trading-day count matches between the two periods before publishing, and make the holiday-flag join fail loudly on NULL rather than dropping days. A year-on-year figure that moves several points when you change only the boundary is a calendar artefact, not a trend.

I'd validate the alignment by running it across a full year containing each moveable holiday, and by checking that a period with no holiday movement reports roughly the same number under all three alignments. Aligning dates is not aligning periods.

Curated: · Written: · Reviewed:

Q&A-54A report needs product, category, and grand totals in one result. How?

Assumptions: PostgreSQL 15. The syntax is shared with Oracle, SQL Server and DB2; MySQL 8 has ROLLUP and GROUPING() but not GROUPING SETS. Table is sales(product, category, amount).

SELECT
  category,
  product,
  sum(amount)        AS total,
  grouping(category) AS category_is_subtotal,
  grouping(product)  AS product_is_subtotal
FROM sales
GROUP BY GROUPING SETS (
  (category, product),   -- detail
  (category),            -- category subtotal
  ()                     -- grand total
)
ORDER BY category_is_subtotal, category, product_is_subtotal, product;

One query, three levels, one result set.

What it returns. With three rows in sales ((widget, hardware, 100), (gizmo, hardware, 60), (manual, docs, 40)) the query emits six rows:

categoryproducttotalcategory_is_subtotalproduct_is_subtotal
docsmanual4000
docsNULL4001
hardwaregizmo6000
hardwarewidget10000
hardwareNULL16001
NULLNULL20011

The NULL in product on the subtotal rows is not data: it is the placeholder for a column the grouping set dropped, and grouping(product) = 1 is what tells you so. Postgres sorts NULL last under ASC, so the grand total lands at the bottom.

Why it is one pass. GROUPING SETS is logically UNION ALL of three GROUP BY queries, but the planner does not run three. It reads sales once and emits a row per grouping set from a single aggregation node (sort-based or hash-based, depending on the plan); the columns absent from a set come out as NULL and GROUPING() reports 1 for them. The subtotal and the detail rows are therefore computed from the same scan of the same rows: there is no second filter that can disagree with the first.

ROLLUP(category, product) produces exactly these three sets, so use it when the levels are a strict prefix. Reach for GROUPING SETS when they are not: say you also want a per-product total with no category. CUBE gives all 2^n combinations, usually more rows than a report wants.

Failure modes. A genuinely NULL product is indistinguishable from a subtotal NULL unless you check GROUPING(product); a report that filters WHERE product IS NULL to find subtotals will also pick up real nulls. WHERE runs before grouping so it is safe, but HAVING on an aggregated column can silently drop subtotal rows: use HAVING GROUPING(product) = 0 to keep detail only. On MySQL 8, GROUPING SETS is unavailable; ROLLUP plus GROUPING() is the fallback.

Cost. Three separate queries scan the fact table three times; grouping sets scan it once. On a 200M-row fact table that is roughly three full scans versus one, but the reason to prefer it is correctness, not speed: the subtotal cannot drift from the detail because it is the same pass.

Curated: · Written: · Reviewed:

Q&A-50How do you show each region's top five products without five queries?

What I'd say first

One statement, not five. Rank inside the query with a window function, filter the rank in an outer query, and fix the tie-break before you run it.

-- PostgreSQL 16
WITH ranked AS (
  SELECT region_id,
         product_id,
         units,
         ROW_NUMBER() OVER (
           PARTITION BY region_id
           ORDER BY units DESC, product_id
         ) AS rn
  FROM sales
  WHERE sold_on >= DATE '2025-01-01'
)
SELECT region_id, product_id, units
FROM ranked
WHERE rn <= 5
ORDER BY region_id, rn;

The CTE is not decoration. Window functions are evaluated after WHERE, GROUP BY and HAVING, so writing WHERE rn <= 5 in the same SELECT fails with ERROR: window functions are not allowed in WHERE. The outer query is where the filter belongs. The date filter stays inside so the sort only sees the rows you care about.

Which ranking function

Take one region, ordered by units DESC, product_id ASC:

product_idunitsROW_NUMBERRANKDENSE_RANK
p1500111
p2450222
p3400333
p4400433
p5350554
p6300665

Filtering <= 5: ROW_NUMBER returns p1–p5, RANK returns p1–p5, DENSE_RANK returns all six. ROW_NUMBER is the only one that guarantees exactly five rows per region. RANK is not a safe substitute: move the tie to the cutoff (500, 450, 400, 350, 300, 300, 200) and RANK assigns 1,2,3,4,5,5,7, so rank <= 5 returns six rows. DENSE_RANK is for when you want every product tied at the fifth position, and you accept the row count varying.

The tie-break is the whole game

ORDER BY units DESC alone is not a total order. Two products tied at 400 units have no defined order, so ROW_NUMBER picks whichever the plan happens to emit first, and that changes when the plan changes, when statistics are refreshed, or when rows are physically reordered. Adding product_id as a second key makes the result reproducible. I've watched a supplier ask why they appeared in the top five one week and not the next, with identical data, for exactly this reason.

When not to use it

If you need five rows for a handful of regions and have an index on (region_id, units DESC), a LATERAL join does one index scan per region and stops early:

SELECT r.region_id, t.product_id, t.units
FROM regions r
CROSS JOIN LATERAL (
  SELECT product_id, units
  FROM sales s
  WHERE s.region_id = r.region_id
  ORDER BY units DESC, product_id
  LIMIT 5
) t;

That is still one statement, but it is N index scans. The window version is one scan plus one sort of the filtered rows; an index on (region_id, units DESC) can let the planner feed the window in order and skip the sort. Pick LATERAL when regions are few and the index is selective; pick the window when you want all regions anyway.

Failure modes to check

  • NULL units: in PostgreSQL, ORDER BY units DESC puts NULLs first, so a NULL row ranks #1. Add NULLS LAST or filter them.
  • Wrong grain: partitioning by a region column that lives on the product rather than the sale silently collapses or duplicates regions.
  • Non-deterministic tie-break: the fifth row changes between runs.

Verification

Run the query twice on unchanged data and diff the output; then SELECT region_id, count(*) FROM ranked WHERE rn <= 5 GROUP BY region_id and confirm every region shows 5.

Curated: · Written: · Reviewed:

Q&A-51When does breaking a query into CTEs help and when does it hurt?

The 60-second answer

Clarify three things before answering: which engine and version, and how many times the CTE is referenced. Those settle the cost far more than any general rule about readability.

A CTE is a name, and what the engine does with that name decides the cost. It either folds the CTE text into each reference, or evaluates it once into a stored intermediate the outer query reads. Break a query into CTEs when the same expensive result is needed two or more times, when you need recursion, or when the fence is deliberate: pinning a plan you trust against bad statistics. Avoid a CTE when it is referenced once and you want filters, join order and LIMIT to flow into it: a materialized CTE is a wall those optimizations cannot cross, and an inlined CTE gives you no reuse at all.

Engine defaults for a side-effect-free CTE

EngineDefaultLever
PostgreSQL 12-17Folded into the parent if referenced exactly once in FROM; materialized once if referenced more than once, recursive, or data-modifyingWITH x AS MATERIALIZED (...) / AS NOT MATERIALIZED
SQL Server 2019/2022Expanded inline at every reference; the optimizer may add a Table Spool, nothing guarantees a single evaluation#temp table with a clustered index
MySQL 8.0/8.4Chooses merge or materialize per CTENo documented hint; read EXPLAIN

Figures below are hypothetical but representative: PostgreSQL 16, single node, work_mem at its 4 MB default.

Worked example

orders holds 240M rows, 60 GB. The 90-day window keeps 24M rows; grouping by account yields 480k rows, about 15 MB. The report joins that result to dimension tables three times.

WITH recent AS MATERIALIZED ( SELECT account_id, sum(amount) AS revenue, count(*) AS n FROM orders WHERE created_at >= now() - interval '90 days' GROUP BY account_id ) SELECT ...

Timings: scanning 60 GB is ~30 s, aggregating 24M rows ~8 s, so the CTE costs ~38 s once; each read of the 15 MB result is ~0.2 s. Three references total ~38.6 s. Force NOT MATERIALIZED and that work repeats per reference: 3 x 38 s, ~114 s. The arithmetic that decides it is 3 x 15 MB against 3 x 60 GB.

The plan confirms which happened. Materialized: one CTE Scan on recent node with loops=3. Folded: that node is gone and the scan-and-aggregate work appears under each reference.

Where it hurts

Materialization is a barrier, not just a cache. SELECT ... FROM recent WHERE account_id = 42 still aggregates all 24M rows, because the predicate cannot reach inside; LIMIT behaves the same way. Pre-12 PostgreSQL always chose this, which is why older advice treats every CTE as an optimization fence. Watch the stored result outgrow work_mem: it spills to temp files, visible as climbing temp_bytes in pg_stat_database and as "temporary file" lines in the log once log_temp_files is enabled. When the intermediate is that large and reused several times, a temp table you can index usually beats the CTE.

Inlining hurts in the opposite direction. SQL Server expands the CTE at each reference, so three references of that aggregate can mean three 60 GB scans unless the optimizer happens to choose a spool: check the plan for a repeated subtree. PostgreSQL 12+ has the same trap when AS NOT MATERIALIZED is set on a multiply-referenced CTE.

Detection, mapped to symptom: a CTE Scan with loops=N means one materialization (fine unless it spills); the same aggregate subtree appearing N times means re-execution; slowness that only shows up with a selective outer filter means a fence blocking pushdown; rising temp_bytes means the intermediate spilled.

Follow-ups to expect

A view versus a CTE for naming a business rule: the view is shared and catalogued, the CTE is scoped to one statement. Recursive and data-modifying CTEs are always materialized, so they cannot be folded at all. And a CTE named after the domain concept (active_subscriptions, billable_seats) carries its weight in reviewability, because reporting SQL encodes rules other teams have to check. That is a correctness argument, not a performance one, and it should not be the thing that decides the plan.

Curated: · Written: · Reviewed:

Q&A-102What's the difference between a measure and a calculated column in Power BI, and when does the choice change the number you get?

They're evaluated at different times, in different contexts, and that's the whole story. A calculated column is computed once per row at refresh, in row context, and stored in the VertiPaq model. A measure is computed at query time, in filter context, once per cell of the visual. Pick the wrong one and you either bloat the model or get a number that ignores the slicer.

Calculated columnMeasure
EvaluatedAt refresh, per rowAt query, per visual cell
ContextRow contextFilter context
StoredYes, in VertiPaqNo
Usable asAxis, slicer, relationship keyValues only
CostRefresh time + model sizeQuery time

Take a line-level margin:

-- Calculated column: one value per Sales row, stored
Margin CC = Sales[Revenue] - Sales[Cost]

-- Measure: evaluated per visual cell, not stored
Margin M = SUMX ( Sales, Sales[Revenue] - Sales[Cost] )

The column gives you something you can put on an axis, slice by, or join on. The measure gives you a number that respects whatever filters the visual applies. Put Margin CC in a card with no row context and you get the sum of the column, fine, but filter to one region and the column doesn't recompute, it just gets filtered. That's usually what you want. It isn't when the column references another table via RELATED: the value is fixed at refresh, so a later dimension change doesn't flow through.

The performance split is what interviewers push on. Calculated columns are materialised, so they cost refresh time and model size: a 100M-row fact table with an 8-byte decimal column adds roughly 800 MB before compression, and VertiPaq compresses that but not to nothing. They make queries fast because the value is already there. Measures cost nothing at rest but are computed on every query; a SUMX over 100M rows is a formula-engine scan that can take seconds per visual. The rule I use: if the value is a property of the row (a bucket, a flag, a concatenated key) make it a column, ideally upstream in the warehouse. If it's an aggregation or depends on filter context, make it a measure.

The trap is context transition. A measure called from a calculated column wraps the row in a filter, CALCULATE does this implicitly, which is expensive and easy to get wrong:

-- Slow and often wrong: forces context transition per row
Customer Rank CC = RANKX ( ALL ( Customer ), [Total Sales] )

That's a measure pattern, not a column pattern. Tableau splits the same way but the mechanics differ: a row-level calculated field is a dimension and is pushed to the source where possible; an aggregate calculated field is a measure computed by Tableau. Tableau doesn't materialise row-level calcs into the extract unless you extract, so the cost lands at query time either way.

Failure modes worth naming. A calculated column on a fact table that should have been a measure inflates the model and slows refresh: detect it with DAX Studio's VertiPaq Analyzer, which shows column cardinality and size. A measure used where a column is required (slicer, axis, relationship key) errors outright. And a calculated column using TODAY() or NOW() is frozen at refresh, so "days since order" is stale by tomorrow morning: grep column definitions for non-deterministic functions.

Curated: · Written: · Reviewed: —

Q&A-9Two teams report different revenue. What is the fix?

The fix is a definition, not a query. Two teams reporting different revenue almost always have two correct queries answering two different questions. Reconcile the numbers to a line-item difference first, agree one certified definition with a named owner, publish it where the number is consumed, then automate a reconciliation that alerts when they diverge again.

What a strong candidate says first

Ask three things before touching SQL: which system each number comes from, what period and grain each is cut at, and who owns the definition. Then reconcile: attribute the entire delta to named causes. Do not edit either query until the delta is fully explained. Changing a query to match the other number hides the definitional difference and just moves which number is wrong.

Then the four-part fix:

  1. One certified definition per metric, written down, with exclusions explicit: refunds, intercompany, internal/test accounts, FX rate source, gross vs net.
  2. A named owner. Usually finance for anything that reaches the board or an audit; product owns product revenue and it is labelled differently.
  3. Publish where the number is consumed: the dashboard header or the semantic layer, not a wiki page nobody opens.
  4. A scheduled reconciliation that runs both definitions and alerts on unexplained delta.

Reconcile at the grain they disagree on

A full outer join surfaces rows that exist in one definition and not the other, which is exactly where scope differences live:

-- Postgres 16
with a as (
  select date_trunc('month', booked_at) as month, account_id, sum(amount) as rev
  from finance.revenue_a group by 1, 2
),
b as (
  select date_trunc('month', booked_at) as month, account_id, sum(amount) as rev
  from finance.revenue_b group by 1, 2
)
select month, account_id,
       a.rev as team_a, b.rev as team_b,
       coalesce(b.rev, 0) - coalesce(a.rev, 0) as delta
from a full outer join b using (month, account_id)
where coalesce(a.rev, 0) <> coalesce(b.rev, 0)
order by abs(coalesce(b.rev, 0) - coalesce(a.rev, 0)) desc;

Rows present only in b are scope the A definition excludes; rows present in both with different values are a filter or FX difference.

Worked attribution (illustrative figures)

ComponentTeam ATeam B
gross sales102.0m102.0m
less refunds−3.9m−3.9m
less intercompany−4.2mnot excluded
reported93.9m98.1m

The 4.2m gap is 4.3% of the larger figure and is fully explained by one exclusion neither definition had written down. Nothing was a bug.

Failure modes and how you catch them

  • Definition drift. Someone edits the dbt model, adds a filter, ships it. Detect with the reconciliation job alerting on delta above a threshold (say 0.5% or 50k, whichever is smaller).
  • Grain mismatch. One counts at order grain, the other at invoice grain; partial refunds double-count. Detect by summing at both grains and comparing row counts.
  • Timing. Late-arriving credit memos and restatements land in different periods. Detect by comparing month-over-month restatement; it nets to zero over the quarter but not monthly.
  • Currency. Daily spot vs month-end rate. Reconcile in one currency at a stated rate source.
  • Gross vs net. A marketplace team reports GMV, finance reports net revenue under ASC 606 principal/agent. These are different metrics and should never share a name.

Trade-off

A central semantic layer removes divergence but adds a release bottleneck and a single point of failure; team-local definitions are fast and drift. The workable middle is certified metrics with owners plus a reconciliation that runs both and alerts: you keep team autonomy and still find the gap the day it opens, not eight months later.

Curated: · Written: · Reviewed:

Q&A-40Where should the definition of a metric live?

Put the definition in the semantic layer, on the metric object itself, and let every consumer render it. The semantic layer is the one place that already knows the metric exists, so it is the only place where the definition cannot drift from the number without someone noticing.

Assumptions that decide this: one metric, several consumption surfaces (BI dashboard, notebook, scheduled export), and a named owner. If any of those is false the answer moves: see the catalogue case below.

Mechanism. Define the metric once, with description, owner and review date as fields on the metric object. The BI tool reads those fields and shows them as a tooltip; the export writes them as a header or column; the notebook pulls them through the same API. One edit propagates. A wiki page has no such link: it is a copy, and copies drift.

# dbt 1.6+ metric spec
metrics:
  - name: weekly_active_users
    label: Weekly Active Users
    description: >
      Distinct users with at least one session in the trailing 7 days.
      Excludes internal test accounts.
    type: simple
    type_params:
      measure: active_users
    meta:
      owner: growth-analytics
      reviewed_at: "2025-04-02"

Trace. An analyst opens the dashboard, hovers the tile, and reads "Distinct users with at least one session in the trailing 7 days. Excludes internal test accounts." plus the owner and review date. No navigation, no second tab. The scheduled export carries the same string in its header. The wiki copy, if one exists, is now redundant rather than authoritative.

Trade-offs: co-location is not free.

  • Duplication. The description lives in the semantic layer, but the BI tool may cache it at publish time and the warehouse table comment is a second copy. Two copies means a sync step or a discipline.
  • Governance overhead. Every metric now needs an owner and a review date, and someone has to enforce that. At 20 metrics that is a spreadsheet; at 2,000 it is a program with staff.
  • Tooling limits. Not every BI tool surfaces arbitrary metadata. Some render only a label, which puts the definition one click away rather than zero.

When a central catalogue is the better home. When discovery, not consumption, is the problem. An analyst asking "is there a metric for churn?" across five domains needs a searchable catalogue with lineage and ownership, and the semantic layer is one of its sources. The catalogue answers what exists; the semantic layer answers what this number means. Large orgs run both, with the catalogue indexing the semantic layer rather than restating it.

Deciding criterion: how does the consumer arrive? If they arrive at the number (dashboard, report, export) co-locate. If they arrive at a question: "what metrics do we have for X?": catalogue. Most teams need co-location first; the catalogue is a later problem that only pays off once metric count and domain count are both large.

HomeWho reaches itCost
Semantic layer, rendered at point of useanyone looking at the numberowner and review date per metric; tool must expose metadata
Central catalogueanyone searching for a metricindexing pipeline, lineage upkeep, a second place to update
Wiki pageonly those who go lookingdrifts silently; no link from the number
SQL comment in the modelengineers reading the modelinvisible to dashboard consumers

Failure modes.

  • Wiki rot: the definition was last edited 8 months ago and nothing links to it from the number. Detect it with a review-date field and an alert when it goes stale.
  • Tooltip drift: the BI tool cached metadata at publish time, so a semantic-layer edit does not appear until republish. Detect it by diffing the rendered description against the source on a schedule.
  • Catalogue with no consumers: built, indexed, nobody searches it. Detect it with search analytics; near-zero queries means it is not earning its maintenance.

If you can only do one thing, put the description, owner and review date on the metric object and make the dashboard render them. That is the version that survives contact with a director reading the number in a meeting.

Curated: · Written: · Reviewed:

Q&A-41The definition of an existing metric needs to change. How do you land it?

What I'd say first

A metric definition is a published contract, so changing it is a schema migration, not an edit. Three decisions, in order: does the number keep its name, does history get restated, and how does anyone comparing across the change see it. Get the order wrong and you either fork the metric forever or silently rewrite the past.

Decision 1: same name or new name

Keep the name only when the new definition is a strict refinement of the old (same population, same grain, a tighter filter) and the movement sits inside the noise band you already tolerate. Otherwise publish metric_v2, freeze v1, and migrate consumers on a deadline.

What pushes me to a new name: the metric appears in a contract (customer SLA, exec comp, external reporting), downstream models join on the metric name, or more than a handful of dashboards consume it. What lets me redefine in place: one team owns the metric and every consumer of it, and the delta is small.

Decision 2: restate history or not

Restate only when the old numbers were wrong: a broken join, a filter that dropped a segment. If the old numbers were merely different, because the scope deliberately changed, don't rewrite them. Publish the new series from the change date forward and mark the discontinuity. Restating a deliberate change destroys reproducibility: last quarter's board deck no longer reconciles to the warehouse.

Decision 3: dual-publish, and for how long

Dual-publishing is a migration tool, not a destination. Both numbers live side by side for one full reporting cycle, long enough for every consumer to see the delta, then v1 is deprecated with a date on it. Indefinite dual-publish is the failure mode where nobody retires the old metric and two dashboards quietly disagree for a year.

The failure this prevents

A definitional change was applied silently. Both definitions showed growth; the published series showed a decline, because Q1 was old-definition and Q2 was new.

SeriesQ1Q2QoQ
old definition4.10m4.16m+1.5%
new definition3.85m3.91m+1.6%
as published, silently4.10m3.91m−4.6%

The business spent two weeks investigating a 4.6% decline that never happened. The fix isn't more dashboards; it's a definition_version on the metric plus a reconciliation query that runs both definitions over the overlap window and fails loudly when the delta isn't the one you predicted.

Landing it

Ship the definition as a code change: a PR against whatever holds it (semantic layer, transformation repo, BI model), a reviewer from the consuming team, a backfill job for the restated window, and a dated note on the metric page. "definition changed 2024-07-01; v1 frozen; level shift −6.1% on the overlap window", so anyone comparing across the boundary sees it. Then delete v1 on the announced date.

Curated: · Written: · Reviewed:

Q&A-45Analysts keep building their own versions of certified measures. Why?

Analysts build their own version when the certified measure fails one of four tests at the moment they need a number: it doesn't cover their grain, it's too slow, they can't find it, or they can't reconcile it. Each failure has a different mechanism, and each leaves a different fingerprint on the duplicate.

Coverage. A certified measure is defined at one grain: usually the grain of the fact table it was built on. A measure certified at order grain can't answer "revenue per line item after partial returns" without a new join, and the analyst often can't add that model to the semantic layer themselves. So they write the SQL. This is the most common cause and the least about trust: the analyst agrees with the definition and still can't use it. The duplicate shows up under the same name at a different grain, which is why the two numbers are close enough to look like a bug.

Latency. If the certified path is a nightly aggregate and the analyst needs a figure at 11am, the certified path isn't a candidate. The mechanism is where the cost sits: the certified path pays per query (queue time, cluster contention, a 40-minute refresh) while a local extract pays once and then answers instantly. Someone running the same query forty times a day builds the extract on roughly the second run, and after that the certified path never gets a chance to be right.

Discoverability. Search failure is indistinguishable from absence. If the analyst can't find the certified measure in less time than it takes to write their own, they write their own. Naming that encodes the team that built the measure rather than the question it answers, fin_rev_adj_v2, makes this worse, because the analyst searching for "net revenue" never sees it.

Reconciliation. The certified number disagrees with the source system the analyst can see, and there's no lineage path from the measure back to the raw rows. An unexplained number is unusable in a review, so they rebuild a pipeline they can trace end to end. This is the one cause where the analyst has stopped trusting the certified definition, and it's the hardest to reverse.

CauseWhat the analyst hitsWhat they buildFingerprint of the duplicate
Coveragemeasure locked to order graintheir own join in SQL or the BI toolsame name, different grain
Latencynightly refresh, long queuea local extract refreshed hourlymatches, but stale by hours
Discoverabilitycan't find it in two minutesa new measure under a new nametwo names, one definition
Reconciliationcertified ≠ source, no lineagea parallel pipeline they can tracediffers by one filter, e.g. returns

Take a concrete divergence: two dashboards both labelled "Net Revenue" differ by 3.2% (hypothetical figures). One excludes returns, one includes them. Nobody notices until a board deck, and by then the argument is about which dashboard is right rather than which definition the business agreed on. That is the cost of the divergence, not the duplicate itself, but that the difference is silent until it's expensive.

The diagnostic follows from the mechanism. Ask the authors of the most-duplicated measures why they built theirs; the answer is almost always one of the four above, and it tells you which fix actually matters. Restricting the tool addresses none of them: it moves the work into spreadsheets, where the duplicates are invisible and the divergence is worse, because now nobody can even enumerate the versions.

Curated: · Written: · Reviewed:

Q&A-10How do you let analysts build freely without producing five versions of the truth?

The 90-second answer

Don't try to stop analysts building. Stop the same number being defined twice. Five versions of the truth come from five definitions of "active user", not from five charts: the charts are just where you notice. So: put metric definitions in one versioned place, make the certified one the path of least resistance, and make anything else visibly uncertified at the point the number is consumed.

Three tiers, each with different obligations, and the tier travels with the number.

# the shape every semantic layer carries (dbt, Cube, LookML); not one tool's schema
metric: active_user
owner: growth-analytics        # named team, on-call rotation
tier: certified
version: 3
definition: count(distinct user_id) where days_since_seen < 30
grain: user
tests: [not_null, unique, freshness < 6h]
changelog: "v3 2024-06-11: window 14d -> 30d, approved in #data-gov"

The tool doesn't matter. What matters is that the window (30 days), the grain (user), the owner and the tests are one artifact, checked in CI, and every downstream chart inherits them. An analyst who wants a 7-day window forks the metric, and the fork is named active_user_7d_exploratory, a different name at a different tier, visible in lineage.

TierAssetsShare of viewsOwnerTestsChange process
certified4071%named teamCI-blockingversioned, deprecation window
team-owned31024%teamsmokePR review
personal2,4005%nonenonenone

40 assets carry 71% of consumption. That is the entire argument for tiering: you can afford CI, a named owner and a deprecation window on 40 things, and you cannot on 2,400. Governance effort goes where the views are, and the long tail stays free.

Failure modes, and how you catch them

  1. The label doesn't survive the export. An exploratory chart gets pasted into a board pack and reads as authoritative because nothing in the image distinguishes it. Detection: render the tier into the exported image/PDF/CSV header, not just the tool chrome, then export one and look at it.
  2. A certified metric changes silently. Say a dashboard moves 8% overnight and nobody knows why. Detection: version plus changelog on the metric, an alert on definition change, and a deprecation window before the old version is removed.
  3. Reverse dependency. A certified dashboard reading from a personal table. Detection: a lineage query for certified assets with non-certified upstreams. That count should be zero, and it's a one-line query against the lineage graph.
  4. Certification inflation. Everything gets certified and the label stops meaning anything. Cap it: certification requires an owner, tests and a change process, so it is expensive by design.
  5. Same name, two definitions, two tools: the BI tool and a notebook. Detection: count distinct definitions per metric name; anything above one is a bug.

Alternatives, and when not to use them

  • Centralize everything: one team owns all metrics. The numbers are right, but the queue is weeks and analysts route around it. Use it only when audit or regulatory pressure demands it.
  • No governance: fast, right up until the board pack.
  • BI-tool-level certification only: fine if you have exactly one tool; it breaks the moment someone opens a notebook or queries the warehouse directly. Metric-as-code in the warehouse is tool-agnostic and testable in CI, which is why it wins.

The check

Export a certified chart and a personal one, put them side by side, and ask whether a stranger can tell which is which. If not, the tier is decoration, and the five versions of the truth are already in circulation.

Curated: · Written: · Reviewed:

Q&A-38Why put a semantic layer between the warehouse and the reports?

Core answer, first 90 seconds. Assume the warehouse is the source of truth and more than one team reads the reports. The semantic layer exists so that one reviewed definition of each metric (its grain, its joins, its expression) is what every report compiles from, instead of each report re-deriving it. The reason it prevents divergence is mechanical, not cultural. The layer declares the entity, the grain, the join cardinality and the metric expression once, then compiles that into dialect-specific SQL for each consumer. Looker's LookML, dbt's MetricFlow and Cube all work this way: the report author picks a metric and dimensions, and the tool emits the SQL. A fan-out bug gets fixed in the model, not in eleven dashboards.

Where wrong totals actually come from. Most bad totals are a grain or join error, not a formula error. Take three orders with totals 100, 50 and 200, and an order_items table where order 1 has two line items. Joining orders to order_items yields four rows, so sum(orders.total) over that join returns 450, not 350. The layer pins the join cardinality and the grain, so that class of error is caught once and every consumer inherits the fix.

Trade-offs, and when not to do this. The layer costs adoption: every consumer has to route through it, and notebooks and extracts need an API or an export path or they quietly bypass it. It costs rigidity: a one-off analysis that does not fit the declared grain needs an escape hatch, or people work around the layer and you are back to divergence. It can cost latency, but only in one shape: a query service like Cube sitting in front of the warehouse adds a hop, whereas compile-time layers like LookML and MetricFlow emit SQL that runs directly on the warehouse with no extra hop. And it is not always worth it: a small org with one team owning a well-tested set of marts, stable metrics and few consumers gets most of the benefit from the marts alone. Reach for the layer when consumers multiply or definitions start disagreeing.

How the layer itself breaks. Stale definitions: a metric changes but a cached extract or a scheduled dashboard still runs the old compiled SQL. Detect by versioning the layer and stamping every report and extract with the layer version it was built from. Extract drift: a BI extract materialises the metric and stops refreshing. Detect by comparing extract totals against a live layer query on the same date. Metric sprawl: the layer becomes a dumping ground, hundreds of metrics, half unused. Detect with usage telemetry and prune. Bottleneck: one team owns all definitions and changes queue. Detect by lead time on definition pull requests.

What I would check before calling it settled. Count distinct implementations of each key metric across consumers and drive that to one. The figures below are a hypothetical illustration of the shape, not a measurement.

MetricImplementations beforeValues reportedAfter
active customer674k–96k1
revenue393.9m–98.1m1

Without one definition, you have as many answers as you have reports.

Curated: · Written: · Reviewed:

Q&A-11A load is re-run after a failure and the totals double. What was wrong?

The load wasn't idempotent. The retry re-ran an interval whose rows were already committed, and the write appended them a second time. Two independent things have to be true for that: the write has no key-based dedup or partition overwrite, and the progress marker (the watermark row, or the orchestrator's success flag) is committed separately from the data. A crash, timeout, or task-level retry between those two commits makes the next run re-read the same window. Fix both; fixing one leaves a window open.

The failure trace. Daily revenue job, interval = one day, 1.42m rows/day.

  1. Run 1 reads watermark = 2024-03-06, processes 2024-03-07, appends 1.42m rows.
  2. The worker is killed (OOM, node eviction, or a timeout where the write actually landed) before UPDATE etl_watermark SET committed_through = '2024-03-07'.
  3. Run 2 reads watermark = 2024-03-06 again, re-processes 2024-03-07, appends another 1.42m rows.
  4. SUM(revenue) for 2024-03-07 now reads 2x. Row counts look plausible, a busy day is a busy day, and no constraint is violated, so nothing errors.

The signature is exact: totals double for the re-run interval only, every other interval is normal. That is what distinguishes it from a genuine upstream double-send, which usually spreads across intervals.

Make the write idempotent. Either overwrite the interval in one transaction, or merge on a stable business key.

-- Partition replace: same interval twice == once
BEGIN;
DELETE FROM fact_revenue WHERE day = DATE '2024-03-07';
INSERT INTO fact_revenue
SELECT ... FROM staging WHERE day = DATE '2024-03-07';
UPDATE etl_watermark SET committed_through = DATE '2024-03-07' WHERE job = 'revenue_daily';
COMMIT;   -- data and watermark land together, or neither does
-- Merge on key: handles late-arriving corrections too
MERGE INTO fact_revenue t
USING staging s ON t.event_id = s.event_id
WHEN MATCHED AND s.updated_at > t.updated_at THEN UPDATE SET ...
WHEN NOT MATCHED THEN INSERT ...;
StrategyAfter 1 runAfter 2 runsCost
append1.42m2.84mcheapest write, wrong
partition replace1.42m1.42mrewrites the whole partition
merge on key1.42m1.42mjoin/shuffle on every run
append + dedup at read1.42m2.84m storedevery reader pays forever

Trade-offs. Partition replace is exact and cheap when the retry granularity matches a partition boundary; it rewrites the whole partition, and a wrong boundary deletes good data. Merge on key survives late-arriving and updated rows but needs a genuinely stable key (event_id, or (source, entity_id, updated_at)) and a tie-break rule: a stale updated_at silently drops a correction. Deduping at read time leaves the duplicates in the table and taxes every downstream consumer, so it is a stopgap, not a fix.

Detect it. Run this after every load:

SELECT day, COUNT(*) AS rows, COUNT(DISTINCT event_id) AS uniq
FROM fact_revenue
GROUP BY day
HAVING COUNT(*) <> COUNT(DISTINCT event_id);

Any row returned is a duplicated partition. Pair it with a source-vs-target sum reconciliation per interval, and assert in CI that running the same interval twice leaves totals unchanged: that property test is the actual guarantee, not the code review.

Curated: · Written: · Reviewed:

Q&A-12A source corrects a record from last month. Does your pipeline see it?

Direct answer. It depends on what the load keys off, and the deciding question is whether the source emits a change event for the correction. A watermark load, "give me rows newer than the last timestamp I saw", sees inserts. A correction to a row whose timestamp already sits behind the watermark is invisible to it, permanently. Log-based CDC and a bounded reprocessing window both see it; a full snapshot sees it too, at a cost.

Why the watermark misses it. The load is:

-- runs daily; :last_watermark is max(updated_at) from the previous batch
SELECT id, amount, updated_at
FROM orders
WHERE updated_at > :last_watermark
ORDER BY updated_at;

The watermark only moves forward, so any write whose timestamp is not greater than the last one is skipped. Corrections are the writes most likely to violate that: a backfill job that rewrites amount without touching updated_at, a correction that sets updated_at to the original business date, or a transaction that commits late carrying an older timestamp. Deletes never appear at all: there is no row to return.

Trace. One row, one correction, daily runs:

WhenEventRow 42 updated_atWatermark after runRow 42 re-read?
Mar 1 09:00order loaded2024-03-01T09:00Z2024-03-01T09:00Zyes
Mar 5source corrects amount, leaves updated_at alone2024-03-01T09:00Z2024-03-01T09:00Zno
Mar 6 runupdated_at > watermark is false2024-03-01T09:00Z2024-03-01T09:00Zno

The warehouse keeps the March 1 value forever. Nothing errors; the two systems just disagree.

What actually catches it.

ApproachSees corrections?CostCatch
Watermark on created_atnolowestinserts only
Watermark on updated_atonly if the source bumps itlowmisses backdated and no-op updates, all deletes
Trailing reprocessing window (re-read last N days, merge on PK)yes, inside the windowmediumcorrections older than N days
Log-based CDC (WAL/binlog)yes, including deletesmedium-highneeds replication access, REPLICA IDENTITY FULL for old values, and a snapshot for history predating the connector
Full snapshot + diffyeshighestfine for small tables only

Detection. Do not trust the incremental path; reconcile closed periods against the source. A per-period checksum catches any row-level difference in one query:

-- run against source and warehouse; any mismatch means at least one row differs
SELECT period,
       COUNT(*)                                        AS rows,
       SUM(amount)                                     AS total,
       SUM(hashtext(id::text || ':' || amount::text))  AS checksum
FROM orders
WHERE period = DATE '2024-02-01'
GROUP BY period;

Alert on a checksum mismatch for a period that is already closed. Illustrative arithmetic: a 40M-row order table where the source corrects 1,200 rows a month at an average $180 each misstates $216k a month, $1.3M over six months, with no error anywhere.

How to settle it per source. Ask the owner two questions: are rows ever updated or deleted after load, and does updated_at get bumped when they are? If the answer is yes and no, a watermark cannot work: you need CDC or a reprocessing window. If the table has a monotonic commit sequence or LSN, key off that instead of a wall-clock column, and still use >= with a small overlap so late-committing transactions are not skipped.

Curated: · Written: · Reviewed:

Q&A-24A metric drops when a source starts returning nulls. What happened?

Nulls are excluded, not zeroed. So the metric dropped because values or whole rows left the calculation: nothing was counted as zero. The first check is whether COUNT(*) moved with the metric; that splits the diagnosis in two.

COUNT(*) flat, metric down. The nulls are in the measure column and the aggregate is skipping them. SUM and COUNT(col) shrink. AVG moves toward the mean of whatever is left, so it can go either way: it is not diagnostic on its own.

COUNT(*) down too. Rows are leaving before the aggregate. A null in a join key matches nothing under an inner join, and a null fails every comparison predicate (=, <>, NOT IN all evaluate to unknown, not true) so the row is filtered out and every metric built on it drops together.

Worked trace, SUM(amount) over three orders:

Source sendsSUMCOUNT(amount)AVG
100, 200, 3006003200
100, 200, null3002150
100, 200, 03003100
100, 200, null (was 0)3002150

Row 2 is nulls replacing real values: SUM halves, COUNT drops, AVG falls because the 300 left. Row 4 is nulls replacing zeros: SUM is unchanged and AVG rises. The same null, opposite direction, because the excluded value sat below the mean. That is the case that gets reported as an improvement.

So the direction of the metric does not identify the cause; the row count does. To localize it, count rows at each stage: raw source, post-join, post-filter, post-aggregate. The stage where the count falls is where the null is doing damage. A null in a GROUP BY dimension is a quieter variant: it forms its own null bucket rather than dropping the row, and the metric only disappears when that bucket is joined to a label table that has no null key.

Confirm by querying null count per column per day next to the metric. Nulls up in the measure column with COUNT(*) flat means exclusion from the aggregate. Nulls up in a join key or a filtered column with COUNT(*) down means row loss. Both are upstream data problems, the source should send a value or an explicit sentinel, and the measure should carry a null count so the next shift shows up instead of being absorbed.

Curated: · Written: · Reviewed:

Q&A-96A source delivers duplicates. How do you handle them?

What I'd say first

Two things to pin down before picking a strategy: is the source at-least-once (retries, replays) or genuinely emitting distinct events that collide on a key, and does anything downstream need exactly-once semantics. Then the answer: dedupe at ingest, on a deterministic key, and keep a duplicate-rate metric so a change in it is visible.

Where dedup happens

  • Ingest: dedupe as rows land in the raw/staging table, so every downstream consumer sees one row. Costs a window function or a merge on each load, and the raw table no longer mirrors what the source sent.
  • Query: leave the raw table intact and dedupe in the read model or view. Cheaper to change, but every consumer has to remember to do it, and one that forgets double-counts.

I default to ingest for anything feeding reported figures, and keep the raw rows in a separate append-only table so the dedupe is auditable.

Upstream prevention, if you control the producer: idempotency keys on the write, a Kafka idempotent producer with transactional writes for exactly-once, or a dedupe window keyed on (source, event_id). If you don't control it, you can only dedupe downstream.

Mechanism

WITH ranked AS (
  SELECT *,
         ROW_NUMBER() OVER (
           PARTITION BY order_id
           ORDER BY updated_at DESC, id DESC
         ) AS rn
  FROM raw_orders
)
SELECT * FROM ranked WHERE rn = 1;

Trace on order_id = 42:

idupdated_atrn
910:001
710:002
809:003

Keep id 9. The id DESC tie-break is what makes it deterministic: without it the two 10:00 rows tie and the engine may return either.

Choosing the tie-break

Tie-breakUse whenRisk
latest updated_atsource sends correctionsclock skew, ties
first insertedappend-only factslate corrections lost
field mergepartial updates per rowneeds per-field rules

Decide with the business owner; the tie-break changes the reported numbers.

Failure mode: ties on updated_at with no unique tie-break: reported status of 4,100 orders flipped between runs and got blamed on the source. Detect it by running the dedupe twice on unchanged input and diffing.

Metric: duplicate rate = 1 - count(distinct key)/count(*). Alert on a step change.

When not to dedupe, if duplicates are legitimate (two real events on the same key), deduping loses data; fix the key instead.

Curated: · Written: · Reviewed:

Q&A-59A source column is being renamed. How do you know what breaks?

What I'd say first

Before touching anything, pin down two things: is this a hard rename (the old name disappears) or an additive one (new name added, old kept and dual-populated), and can you enumerate every consumer? Assume a hard rename of a source column that feeds dbt models, BI dashboards, and scheduled extracts.

The answer: impact is transitive reachability over column-level lineage, and you derive that lineage from code and query logs, not from a wiki page. Direct dependents are the floor, not the answer.

Building the lineage

Three sources, in order of trust:

  1. dbt's manifest. dbt ls --select source:raw.orders+ lists every downstream node at model granularity. That's the skeleton.
  2. Column-level edges. Model-level lineage won't tell you whether orders.customer_id or orders.ship_id is the one that breaks. Parse compiled SQL with sqlglot, or use a column-lineage tool (OpenLineage/Marquez, or dbt's column lineage) to get column-to-column edges.
  3. Observed access. Snowflake's ACCOUNT_USAGE.ACCESS_HISTORY records which columns each query actually read, via baseObjects and directObjects. This catches consumers that never went through dbt: a BI tool with a saved query, an analyst's notebook.

Union those, then traverse transitively to the leaves: dashboards, extracts, reverse-ETL syncs.

Rename strategy: the real decision

ApproachCostWhen to use
Hard renameOne coordinated change; every consumer breaks at once if you miss oneSmall, fully-enumerated consumer set you control
Additive: add new column, dual-populate, deprecate oldTwo writes, extra storage, a migration windowConsumers you don't control or can't enumerate; anything customer-facing
View alias: old name as a view over the new columnCheap, but hides the change and accumulates debtShort bridge while consumers migrate

Deciding criteria: can you enumerate every consumer and deploy them together? If yes, hard rename. If no, and external or BI consumers usually mean no, go additive with a deprecation date. Don't hard-rename a column with an unowned customer-facing consumer; that's the case that pages someone at 2am.

Failure modes

  • Model-level lineage only: you rename customer_id, but the model that breaks reads ship_id. Column-level edges catch this.
  • Docs-derived lineage: stale the day after it's written. Derive from code.
  • BI saved queries: invisible to dbt, visible in query logs. This is the usual miss.
  • Detection: run the transitive set against a staging environment, or diff dbt build --select state:modified+ output before and after.

Artefact

# model-level downstream of the source
dbt ls --select source:raw.orders+

# column-level: parse compiled SQL, walk the AST for column refs
sqlglot.parse_one(compiled_sql)

Then classify the reachable set by consumer type (dbt models, BI dashboards, extracts) and notify each owner. The rename is safe when that set is empty or every member is migrated.

Curated: · Written: · Reviewed:

Q&A-60A source adds a column without warning. What should your pipeline do?

Assume the source is a scheduled batch read (a table, a CSV drop, a Parquet file) and that you have a declared schema for it. The default answer: land the new column in raw storage, keep it out of every contracted model, and emit a schema-change event for a human to review. Fail the load only when the source is positional.

Why accept rather than fail: an added column is backward compatible in every mainstream format. Avro resolves a reader schema against a writer schema and ignores writer fields the reader doesn't declare; adding a field with a default is a compatible change. Protobuf retains unknown fields. A Parquet reader projects only the columns it asks for. So a pipeline that selects explicit columns is unaffected: the new column is inert until someone promotes it.

The breakage comes from three places, none of which is the column itself:

  • Positional parsing. A reader that indexes fields by position shifts everything after the insertion point.
  • SELECT *. A model that projects everything silently widens its own output, and a fixed-width BI extract or a UNION ALL downstream then fails or misaligns.
  • Constraints. A new NOT NULL column with no default makes the warehouse load fail outright.

A trace. orders.csv has a header row, 14 columns, 172,000 rows a day, loaded by a job that maps header names to columns. Day 1 the header matches the registered schema and 172,000 rows land in raw.orders. Day 2 the header has 15 names: discount_code sits at index 4, between currency and total. The header-mapped reader gives discount_code its own column, the 14 contracted columns are identical to day 1, the load succeeds, and the schema-diff job writes {added: [discount_code]} to ops.schema_changes and pages the owner. Nothing downstream moves.

Now the same file through a positional reader that does row.split(',')[4] for currency. Every day-2 row has SAVE10 in currency and the total shifted one field right. The load still reports success (172,000 rows, no error) and currency now holds 172,000 bad values. That is the case where you fail: assert the header equals the registered column list before parsing, and abort on mismatch.

Detection is a diff, not a guess. Each run, read the observed schema (header names, information_schema.columns, the Parquet footer, the Avro writer schema) and compare it to the registered one, classifying each difference as added, removed or retyped. Added is the only class that should not stop the load.

Handling of an added columnLoadContracted modelsWhen right
Accept into raw, ignore in modelssucceedsunchangeddefault for header-named sources
Fail the loadabortsunchangedpositional or fixed-width sources
Auto-propagate to martssucceedschanges silentlynever without review

Promotion is a separate, versioned step: add the column to the contract with a default, backfill history, and only then let models select it. Until that happens, the column exists in raw and nowhere else.

Two failure modes worth naming. Type inference by sampling: if you infer types from the first 1,000 rows and the new column is null there, it gets typed as string, and the first numeric value on row 5,000 either fails the load or coerces to null. Catch it with a null-rate and domain check on the column, not with the sample. Case and whitespace variants: Discount_Code arriving alongside discount_code reads as an added column but is a duplicate logical field, so normalize names before the diff or you will promote the same data twice.

Curated: · Written: · Reviewed:

Q&A-76How do you stop upstream changes breaking reports?

You can't stop them. The producer has their own roadmap, and a schema change is often the right call for them. What you can do is move detection from your report to their CI, and make the change a migration rather than an outage.

The core move. Agree a contract on the columns the report actually reads (names, types, semantics, nullability) and express it as a test that runs in the producer's pipeline. A test in your repo tells you the data broke; a test in theirs stops the merge. That's the whole difference: at merge time the fix is a one-line default or a coordinated migration; three days later it's a backfill and an apology to whoever already read the number.

What the test looks like. Plain pandas, no framework, run against the last full partition:

# test_orders_contract.py — lives in the producer's repo, runs in their CI
import pandas as pd

REQUIRED = {
    "order_id":   "object",
    "created_at": "datetime64[ns, UTC]",
    "amount_usd": "float64",
    "status":     "object",
}
ALLOWED_STATUS = {"paid", "refunded", "pending"}

def test_orders_contract(path):
    df = pd.read_parquet(path)
    missing = REQUIRED.keys() - df.columns
    assert not missing, f"dropped columns: {missing}"
    for col, dtype in REQUIRED.items():
        assert str(df[col].dtype) == dtype, f"{col}: {df[col].dtype} != {dtype}"
    assert df["order_id"].is_unique
    assert df["amount_usd"].ge(0).all()
    assert set(df["status"]) <= ALLOWED_STATUS, set(df["status"]) - ALLOWED_STATUS

Trace it on a partition where the producer switched amount_usd from dollars to cents without renaming it: missing is empty, the dtype loop passes (still float64), uniqueness passes, ge(0) passes, status passes. The test goes green and the report is wrong by 100×. That is the honest limit of a type contract, and it is why you need the second layer.

Layers, and where each one catches the break:

LayerCaught byWhen
Nothinga wrong figure in a reportdays
Consumer-side testyour pipeline failsafter the producer shipped
Producer-side contract testproducer's CIbefore merge
Registry compatibility (Avro/Protobuf)producer's publish callbefore merge
Distribution assertionsyour pipelineafter, but before the report

Schema registries enforce part of this for you at the wire level: Confluent's default compatibility is BACKWARD, so a producer adding a required field with no default is rejected at publish. Iceberg and Delta give you the same for tables: adding a column is safe, and Iceberg's column IDs make a rename metadata-only, so consumers keep reading the same field.

What contracts don't catch, and what does. Semantics. Currency, units, timezone, whether a refund is a negative row or a status change: none of that is in a type. Back it with distribution assertions on the consumer side: row count within ±20% of the trailing 7-day median, amount_usd p99 inside an expected band, created_at max within 2 hours of now. Those catch the 100× unit change the type test waved through.

Trade-offs. Contract only the columns the report reads. A full-table contract makes every producer experiment a negotiation, and they will route around you with a shadow pipeline. Give a deprecation path instead of a freeze: version the interface, let the old version keep working for a stated window, and pin the report to a version so the change is a migration you schedule.

Failure modes worth naming. A contract test that lives in your repo and imports their code: it runs after they ship. A producer hotfix that skips CI. A backfill that rewrites history under a schema that still passes. And the quiet one: the contract passes, the report runs, the number is wrong, and nobody notices because nothing asserts on the output.

What I'd verify before calling it done: the contract test runs in the producer's pipeline on their schedule, a deliberately broken commit fails their build, and the report has at least one distribution assertion that would have caught the last real break.

Curated: · Written: · Reviewed:

Q&A-13Where should data quality checks live?

Put each check in the stage that owns the data it validates, and in the orchestrator that owns the downstream build, because a check can only block work that runs after it. A check that lives in a separate monitoring tool is an observation by default; it becomes a control only when its failure is wired back to stop the pipeline.

That gives four placements, each with a different owner and blast radius:

StageCheckToolCan block?
Source boundaryschema drift, partition arrivalKafka Schema Registry (default compatibility BACKWARD), dbt source freshnessyes: stops the DAG
Staging / transformunique, not_null, relationships, accepted_valuesdbt testsyes, if run via dbt build
Post-load reconciliationaggregate totals vs source systemAirflow SQLCheckOperator, Soda scan exit codeyes
Serving / BIrow-count anomaly, distribution shiftMonte Carlo, Looker alertsno: data already published

The placement that matters most is transform. In dbt, dbt run && dbt test builds every model first and tests afterwards, so a failing test annotates data that is already materialized and already being read. dbt build (dbt 1.0+) interleaves run and test in DAG order, so a failing test skips everything downstream.

Trace it. Models: stg_orders → fct_orders → rpt_revenue, with a relationships test on fct_orders.customer_id referencing dim_customers.customer_id.

dbt build executes: run stg_orders, test stg_orders, run fct_orders, test fct_orders (fails), skip rpt_revenue. The bad revenue table never materializes.

dbt run && dbt test executes: run stg_orders, run fct_orders, run rpt_revenue, then test fct_orders (fails). rpt_revenue is already built and already served.

Same test, same failure, different placement: one prevents the bad report, the other reports it after the fact.

Thresholds belong with the check, not in a wiki. dbt source freshness needs explicit warn_after and error_after; there is no default, so an unconfigured source check never fires:

sources:
  - name: raw
    freshness:
      warn_after: {count: 6, period: hour}
      error_after: {count: 12, period: hour}
    loaded_at_field: _loaded_at

Great Expectations uses mostly for partial tolerance: mostly=0.98 on a not-null check warns on 2% nulls instead of failing the run.

When not to block: reconciliation against a source system is expensive and often runs on a schedule, not in the hot path. Put it after load and let it fail the next run rather than the current one. And never put a blocking check in the BI layer; by then the data is published and the only lever left is a warning.

Verify placement by breaking a check on purpose and confirming the downstream model is skipped, not annotated. If the pipeline still produces the table, the check is a note.

Curated: · Written: · Reviewed:

Q&A-39What tests would you write for a reporting model?

This is an area where a dashboard that renders and a figure that is right are different events.

Structural tests catch a broken load and say nothing about correctness. What catches wrong numbers is testing invariants: uniqueness of the declared grain, referential integrity, accepted values, and reconciliation of key measures against a source of record or a hand-computed fixture.

Concretely, assert grain uniqueness on every fact, referential integrity on every foreign key, accepted values on every categorical column, and compare each certified measure against a fixture with deliberately unequal segments.

The reason for that specificity is a failure I have seen: A model had freshness and row-count tests only; a join change introduced fan-out and every test passed while the revenue total ran 41 percent high for 6 days.

What each test class catches.

TestBroken loadWrong number
freshnessyesno
row countyesno
grain uniquenessnoyes
measure reconciliationnoyes

I would not consider it settled without evidence: Break each invariant deliberately and confirm the corresponding test fails.

Structural tests catch broken, not wrong.

Curated: · Written: · Reviewed:

Q&A-58How do you test a model change without breaking production reports?

Assume the change is to a semantic model (a join, a measure definition, a filter) and the reports feed finance or ops daily. You cannot validate that against production traffic, because production traffic is the audience. The answer is a prod-shaped environment plus a reconciliation diff, and the deployment pattern you pick depends on how many consumers the change can reach.

Start with the diff, not the environment. Whatever pattern you use, the review artefact is the same: run the current and proposed model over the same data and diff every certified measure by report_date.

-- fan-out check: does the join multiply order rows?
select
  count(*)                   as joined_rows,
  count(distinct o.order_id) as distinct_orders
from orders o
join order_lines l on l.order_id = o.order_id;

If joined_rows > distinct_orders, the join fans out and every sum over orders is inflated. Run this before you trust any revenue number.

Three patterns, and when each is worth it.

  • Prod-shaped dev environment. Copy or generate data with the same cardinality and the same multi-line share as production, run the suite and the reconciliation there. Default when the change touches a certified measure that other teams extract from.
  • Shadow deploy. Run the new model alongside the old against the production warehouse, write both outputs to a comparison table, expose neither. Use when you cannot copy the data (PII, size, cost) but can still query it.
  • Canary dashboard. Publish the new model to one low-traffic report or one team first. Cheapest, and it only catches what that audience actually opens.

A prod-shaped environment is not worth the cost when the model has one consumer, is rebuilt from source on every run, or the change is presentational. Then a canary plus a reconciliation on that one report is enough.

Failure modes and how you see them.

  • Fan-out. Header-to-line joins multiply the header; sums inflate. Detected by the distinct-count check above.
  • Definitional drift. Gross vs net revenue, active vs licensed seats. Detected by diffing the measure definition text, not just the value.
  • Date truncation and time zones. Grouping by day moves rows across boundaries. Detected by diffing per report_date, not on the total.
  • Late-arriving data. A backfill rewrites history. Detected by a per-date diff that shows old dates moving.

The failure this comes from. A model change was tested on a 1,000-row sample where no order had multiple lines, so the fan-out never fired. In production, 41% of orders are multi-line, and the change inflated revenue 41% for 6 days before anyone caught it.

EnvironmentRowsMulti-line ordersFan-out visible
sample1,0000no
production-shaped172,00041%yes

The sample was too small to fan out, so it proved nothing about fan-out. A diff on production-shaped data would have shown the inflation on day one.

Curated: · Written: · Reviewed:

Q&A-57How do you make a transformation model incremental safely?

The 90-second answer

An incremental model is safe when three things hold: the predicate that selects rows to reprocess covers every row that could have changed, the write is idempotent on a stable key, and any change to the model's logic is followed by a full refresh. Miss the third and you get a table where two definitions of the same metric coexist, with nothing in the run log to flag it.

Assumptions: a dbt-style transformation tool, a warehouse table that already exists, is_incremental() gating the filter, and a unique_key with a merge strategy.

Why the default is unsafe

is_incremental() returns true only when the target table exists and the run is not a full refresh. So the model's WHERE clause decides everything: rows inside the window get rewritten, rows outside keep whatever they had. That is the trade: you stop paying to reprocess history, and in exchange history is frozen at whatever logic wrote it.

Three failure modes follow.

  1. Late arrivals outside the lookback. A row with event_time three days ago lands after the window closed. It is never picked up. Detect it by comparing rows per day in the target against the source for the last N days; the gap shows as a flat line where the source has a bump.

  2. Non-idempotent merge. If the unique key is not stable (it includes a mutable column, or the strategy is append-only) rerunning the same window duplicates rows. Detect with count(*) versus count(distinct key) on the target.

  3. Logic change without a full refresh. This one survives longest, because nothing errors.

The failure that costs you the interview

A fix to the revenue definition shipped, and the incremental run applied it only to new rows. The corrected and uncorrected definitions sat in one table for seven months, and a year-on-year comparison spanned both. Illustrative numbers:

PeriodRowsLogic applied
Before fix4.1mold
After fix, incremental only0.9mnew
After full refresh5.0mnew

The middle row is the trap: the run succeeded, the row count went up, and 4.1m rows still carried the old definition. The full refresh is what collapses the table back to a single definition.

What to do

  • Set the predicate's lookback to cover your late-arrival tail, not just "yesterday". If p99 arrival lag is 6 hours, a one-day window is fine; if it is 3 days, it is not.
  • Merge on an immutable key so a rerun of the same window is a no-op.
  • Treat the full refresh as part of the logic change, not a follow-up ticket.
  • Reconcile a historical period against the new logic before calling the fix done.

When not to use incremental

Small tables, or tables where most rows change every run: a full refresh is cheaper and has no correctness cliff. Schema changes are the other case: adding a column to an incremental model needs the tool's schema-change handling (dbt's on_schema_change) or a full refresh, otherwise old rows carry nulls for the new column.

If you cannot state the lookback and the key, you cannot make the model incremental safely.

Curated: · Written: · Reviewed:

Q&A-14How do you know the warehouse figure is right?

"Right" splits into two claims, and they need different evidence. Internally consistent, the dashboard agrees with itself and with yesterday, is cheap and proves almost nothing. Right against a source of record is the claim that matters, and it is established by reconciliation at the grain, not by comparing two reports.

Start there: pick the system that owns the number (the ledger, the billing system, the order service), reconcile the certified measure to it at the grain the measure is defined on, and require either an exact tie or a difference you can name and bound. An unexplained difference is a blocking defect, not a rounding note.

Full tie-out is the strongest evidence and the most expensive, so the real question is when you can afford less.

MethodWhat it provesCostUse when
Full tie-out to source of recordCompleteness and value correctness at grainHighest: needs a joinable source at the same grainThe measure is financial, low-volume, or feeds a filing
Stratified samplingSystematic defects in the sampled strataLowHigh-volume, low-value rows, or source not joinable at grain
Statistical drift monitorThat the relationship changedLow, continuousBetween tie-outs, as a guard
Contract testsSchema, nullability, referential integrity, sum-of-partsLow, every loadAlways; catches structural breakage, not value drift

Full tie-out is too expensive when the source of record isn't available at the same grain (a monthly ledger against a daily fact), when the join costs more than the defect it would catch, or when no upstream system owns the measure. Then tie at an aggregate grain, monthly totals by channel, and sample at the detail grain.

Sampling, Postgres 16:

WITH ranked AS (
  SELECT order_id, amount, channel,
         ROW_NUMBER() OVER (PARTITION BY channel ORDER BY md5(order_id::text)) AS rn
  FROM warehouse.fct_sales
  WHERE ds = DATE '2024-06-01'
)
SELECT r.channel,
       SUM(r.amount) AS warehouse_total,
       SUM(l.amount)  AS ledger_total
FROM ranked r
JOIN ledger.entries l ON l.order_id = r.order_id
WHERE r.rn <= 5000
GROUP BY r.channel;

Each channel's two columns should tie; the channel that doesn't is where the defect is. Sampling catches a systematic filter or join defect and misses a small one: that is the trade you are accepting, and it is why the drift monitor runs alongside it.

The failure this guards against is real. Hypothetical figures:

ItemWarehouseLedgerExplained
gross sales100.8m102.0mno
channel excluded—1.2mcause
after fix102.0m102.0mtie

Two dashboards agreed for a year and both understated revenue by 1.2% because they shared a filter excluding a channel. Nothing was reconciled to the ledger, so nothing caught it. Agreement between two reports is not evidence either is right.

Failure modes to name: late-arriving data producing a difference that looks like a defect (bound it with a restatement window), timezone and currency boundaries at the grain, and ledger restatements that move the target. Detect them with a tie-out job that fails loudly, a ratio-drift alert, and a threshold above which a difference blocks the release.

Curated: · Written: · Reviewed:

Q&A-83A logic error means two years of data is wrong. How do you fix it?

Core answer. Two things decide the approach: is the raw source data still intact, and how far downstream does the wrong number reach? If raw events are retained (an immutable event log, or a CDC stream) this is a recompute problem, not a data-loss problem, and the fix is deterministic. If they are not, I cannot recompute and the conversation becomes reconstruction or annotation.

Assuming raw is intact, the sequence is: fix the transform and ship a regression test so new data is correct; recompute the two years into a shadow table partitioned by day; reconcile shadow against the published table per period; quantify the delta per consumer; cut over atomically; keep the old table for a retention window; and tell the people whose numbers moved before they see them.

Why shadow, not in place. An in-place backfill mutates rows readers are querying. Mid-run a dashboard shows a mix of corrected and uncorrected days, and if the job dies at day 400 of 730 there is no clean rollback. A shadow table keeps the published version queryable and gives you a diff to reconcile against. Cut over with a partition swap or by repointing a view, which is atomic.

Mechanism: the recompute. The error is in the transform, so the recompute re-runs the corrected transform over the same raw input. Make it idempotent and partition-scoped: each run overwrites exactly one day, so a retry cannot double-count.

-- Spark SQL 3.5, Hive-style; one partition per run, idempotent
INSERT OVERWRITE TABLE metrics_shadow PARTITION (dt = '2023-04-01')
SELECT user_id, dt, SUM(amount_cents) AS revenue_cents
FROM raw_events
WHERE dt = '2023-04-01'
GROUP BY user_id, dt;

Reconciliation. Join old and new on the business key plus period and compare both row counts and aggregates. Row-count equality catches dropped or duplicated keys; the aggregate delta is what you report.

SELECT COALESCE(o.dt, n.dt) AS dt,
       SUM(CASE WHEN o.user_id IS NULL THEN 1 ELSE 0 END) AS new_only,
       SUM(CASE WHEN n.user_id IS NULL THEN 1 ELSE 0 END) AS old_only,
       SUM(n.revenue_cents - o.revenue_cents) AS delta_cents
FROM metrics_published o
FULL OUTER JOIN metrics_shadow n
  ON o.user_id = n.user_id AND o.dt = n.dt
GROUP BY 1 ORDER BY 1;

Cost arithmetic. Two years is ~730 daily partitions. At ~4 minutes per partition, serial is ~49 hours. Run with bounded concurrency, say 8 workers on a cluster that is not serving production, and it is ~6 hours. Cap concurrency so the backfill does not starve live queries.

Trade-offs. Full vs incremental: if the bug is in a shared transform, every period is suspect, so recompute all of it; if it is bounded to a window (a bad deploy, a timezone change), recompute only affected partitions. Cheaper and lower risk. Shadow vs in-place: shadow costs roughly 2x storage for the retention window plus a cutover step; in-place is cheaper but has no rollback and exposes partial state. For anything people have already reported on, shadow wins.

When this does not apply. If raw is not retained, or the error is in ingestion so the data was never captured correctly, recompute cannot recover it. You reconstruct from upstream systems, or annotate the affected periods and stop publishing them as authoritative.

Failure modes. A non-idempotent backfill that appends instead of overwriting double-counts revenue. Downstream materialized views and BI extracts that are not invalidated keep serving cached old numbers after cutover. A feature store keeps serving stale features to models trained on the old values. A partition-boundary or timezone mismatch produces an off-by-one day at period edges.

Communication. Circulate the per-period delta table before cutover, and keep the old table queryable for a defined window, 90 days is a common choice, so anyone can reproduce a published figure.

Curated: · Written: · Reviewed:

Q&A-97A refresh fails at 4am. What should users see at 9?

Clarify first. Two things decide the answer: how stale the data may be before it is wrong, and what the consumer does with it. A daily revenue report read by finance at 9am has a different tolerance than an on-call dashboard. Assume a daily refresh that normally lands by 05:00, consumers are analysts and a finance team, and the tolerance is 26 hours. One cycle plus two hours of grace.

What users see at 9. The last successful run's data, with the as-at time rendered next to the title, not buried in a tooltip: Revenue — data as of 04:11 UTC (refresh failed). A banner above the tiles names the failed run, the expected time, and links to the incident. Once the age crosses the tolerance, the tiles stop rendering numbers and show the failure state instead. A stale number that looks current is worse than a blank one: the blank one gets escalated, the stale one gets acted on.

Making that automatic. Freshness has to be data, not a human noticing. Write one row per dataset per run into a freshness table (dataset, last_success_at, expected_by, row_count, status) and have every dashboard read its as-at from that table rather than from the query. In dbt, source freshness does most of this: warn_after/error_after per source produce a sources.json carrying max_loaded_at and a pass/warn/error status, and the dashboard's as-at can be driven from the same watermark. In Airflow, a task sla plus the DAG's sla_miss_callback gives you the missed-by signal, and the callback is where the banner and the page go out. Looker's datagroup with max_cache_age and a sql_trigger does the same job for cached explores: when the trigger's watermark stops advancing, cache age climbs and you surface it.

Alert contents. "Refresh failed" is not actionable. Include the dataset, expected completion time, last successful run time, rows or partitions written by the failed run (often zero, sometimes partial), the downstream dashboards and their owners, and a link to the run log. Route it to the dataset owner, not a channel everyone mutes.

Failure modes.

  • Partial refresh. Some partitions land, some do not. The as-at must be per-partition, or the whole table marked suspect; one global timestamp hides this.
  • Backfill overwriting. A late backfill rewrites history and the as-at moves backwards. Guard with monotonic watermarks.
  • Cache outliving the data. Looker persist_for and Tableau extract schedules must be shorter than the tolerance, or the dashboard serves stale rows with a fresh-looking timestamp.
  • Alert fatigue. If the 04:00 failure pages every night, the page gets ignored. Warn below tolerance, page only on breach.

Worked trace (hypothetical). Daily refresh scheduled 04:00, normally done 04:12. At 04:00 the extract job fails on a source timeout. Airflow marks the task failed; sla_miss_callback fires at 06:00 on the 2h SLA. The freshness table still reads last_success_at = yesterday 04:11. At 09:00 the dashboard reads as-at = yesterday 04:11, age 28.8h, which is past the 26h tolerance, so the tiles render the failure state with the banner. Finance sees "data unavailable, refresh failed 04:00, owner paged" and does not act on yesterday's numbers.

The default I would defend: tolerance = one refresh cycle + 2h grace; warn at one cycle, hard-fail the display at tolerance; as-at rendered from the freshness table, never from the query; page the owner only on breach.

Curated: · Written: · Reviewed:

Q&A-21A dataset takes 4 hours to refresh and the business wants hourly figures. What do you change?

First 90 seconds

Ask what "hourly figures" means before touching the job. Two different asks hide behind it: hourly granularity (the business wants to slice by hour) and hourly freshness (the numbers must be no more than an hour old). If the source only emits a daily file, no pipeline work produces hourly freshness, and the honest answer is to say so and renegotiate. Assume the source does produce hourly data and the ask is freshness.

Then: the 4 hours is not a tuning problem. It is a full refresh reprocessing history that did not change. Fix the refresh strategy, not the cluster.

Why it takes 4 hours

A full refresh reads and rewrites every partition, so cost scales with the archive rather than with the day's activity. Six years is 2,190 daily partitions. If the job takes 4 h, that is roughly 6.6 s per partition plus about 45 s of fixed overhead (warehouse spin-up, connection setup). The marginal cost of one day is 6.6 s; you are paying it 2,190 times. Figures below are from a representative job: substitute your own and check the arithmetic holds.

StrategyPartitions processedDuration
full refresh2,1904 h
30-day window30~4 min
3-day window3~1 min

Arithmetic: 45 s + 30 × 6.6 s ≈ 4.0 min; 45 s + 3 × 6.6 s ≈ 65 s. Fixed overhead dominates at small windows, which is why you do not chase the window below what late data actually requires.

What you change

  1. Partition the table on the date the business reports by, not the ingestion timestamp, so late data lands in the partition it belongs to.
  2. Replace the full refresh with an incremental merge over a bounded recent window (3–7 days) that absorbs late arrivals, plus a watermark table recording the last successful run.
  3. Keep a separate full-rebuild path for backfills and schema changes; it runs on demand, not hourly.
  4. Make the merge idempotent, MERGE on partition key plus business key, so a retried run does not double-count.
  5. If the hourly ask is only for a dashboard, build a small hourly aggregate table and refresh that hourly, leaving the detail table on a nightly cadence. Two SLAs, two jobs.

Getting the change key right

Incremental needs a reliable way to know what changed. In order of preference: CDC from the source's log (Debezium/binlog), exact and ordered; a monotonic updated_at, cheap but breaks on clock skew and backdated corrections; a hash of the row compared per partition. No source change needed, but you read the whole partition anyway, so it only helps if partitions are small. If the source is a full snapshot with no change column and no CDC, you cannot do incremental safely; either add CDC or accept a nightly full rebuild and tell the business hourly freshness is not available.

Failure modes and how you would catch them

  • Late arrivals past the window are silently dropped. Detect: nightly reconciliation of row counts and checksums per partition against the source.
  • updated_at is not monotonic (clock skew, backdated fixes). Detect: alert when max(updated_at) runs more than a few minutes ahead of wall clock, and when a partition older than the window changes.
  • Incremental drifts from the full rebuild. Detect: weekly full rebuild into a shadow table and diff it; a non-empty diff is a bug, not noise.
  • Overlapping runs. Detect: the watermark table takes a lock, and a second run exits rather than racing.

The thing that makes hourly safe is not the smaller window: it is the reconciliation that proves the smaller window did not lose anything.

Curated: · Written: · Reviewed:

Q&A-65A query over the full dataset is too slow. Is sampling acceptable?

The 90-second answer

First question back: what does the number feed? A dashboard someone explores, or a figure that goes into a filing, a payout, or a contract? That decides it.

If it feeds a certified figure (revenue, a count of affected users, anything that has to reconcile to a ledger) sampling is not acceptable, and the fix is to make the full query fast rather than approximate it. If it feeds exploration or a soft metric, sampling is fine as long as the output carries the error bound. The distinction is sharp because sampling error is invisible in the output: a sampled total looks exactly like a computed total.

Why totals break and medians don't

Under simple random sampling with fraction f, the Horvitz–Thompson estimate of a total is (1/f)·Σ yᵢ over the sample. It is unbiased, but its variance is driven by the largest units, and a unit that is rare in row count but large in value gets multiplied by 1/f.

Worked trace. 12,000,000 orders, true quarterly revenue $48,000,000. One order is $3,840,000: 8% of revenue, and one row out of 12M. Sample f = 1%, n = 120,000.

  • Big order not drawn (p ≈ 0.99): sample sum ≈ 0.01 × $44,160,000 = $441,600. Estimate = $441,600 / 0.01 = $44,160,000. 8% low.
  • Big order drawn (p ≈ 0.01): sample sum ≈ $441,600 + $3,840,000 = $4,281,600. Estimate = $428,160,000. 8.9× the truth.

So the estimator returns either $44.2M or $428.2M against a true $48.0M. Neither is usable, and nothing in the output says which one you got.

The median is a different story: it depends on the middle of the distribution, so the same 1% sample puts it within a fraction of a percent. Rare-event counts are the worst case: a flag on 0.004% of orders is 480 rows; a 1% sample expects 4.8 of them, so the estimate swings ±46% at one standard deviation, and at 0.0004% (48 rows) the sample expects 0.48 and returns 0 in 62% of draws.

Figures below are a worked illustration, not a benchmark.

MeasureFull computation1% sampleError
median order value$42.10$42.40+0.7%
total revenue$48.0M$44.2M−8%
rare-event count (480 true)480260–700±46%

What actually fixes the slow query

Ordered by how often it is the right answer:

  1. Pre-aggregate. A nightly rollup keyed by (day, region, product) turns 12M rows into ~90 × 40 × 200 = 720,000, and usually far fewer because you only roll up the dimensions people group by. Tens of seconds becomes a few hundred milliseconds.
  2. Keep the rollup current with CDC or a streaming job so the query never touches raw rows.
  3. Partition and prune. If the query is "last quarter", partition by month; with Parquet/ORC and predicate pushdown you read one column, not the wide row. 12M int64 values is ~96 MB: a sub-second scan on local SSD, so a 40 s query is usually reading far more than it needs.
  4. Sketches with published bounds, when the metric tolerates approximation: HyperLogLog for distinct counts (Redis's default 2^14 registers, ~0.81% standard error), t-digest for quantiles, count-min sketch for frequencies. Fixed size, stated error: a different tool from sampling, and usually the better one.

If you must sample

Stratify by whatever carries the variance (customer size decile, region, plan tier) and sample the top stratum at 100%. That puts the $3.84M order in every sample and removes the failure above. Then state the bound next to the number.

Sampling mechanics matter too. Postgres TABLESAMPLE SYSTEM (1) reads 1% of blocks and is cheap, but rows within a block are correlated, so it is a cluster sample, not a row sample; TABLESAMPLE BERNOULLI (1) scans the whole table and filters, which is more accurate and no faster than the original query.

Failure modes and how you'd catch them

  • Silent estimate: the number ships without a bound. Catch it with an is_estimate flag and a CI column on every sampled metric, plus a test that fails if a certified metric is flagged as sampled.
  • Non-random sampling: WHERE id % 100 = 0 on an auto-increment id samples insertion time, not the population. Catch it by comparing the sample's distribution on a known dimension against the full table.
  • Stale fraction: the sample is drawn once and reused as the table grows, so f drifts. Record f and the row count at draw time.
  • No reconciliation: run the full computation on a closed historical period monthly and alert when the sampled figure falls outside its stated bound.

Curated: · Written: · Reviewed:

Q&A-64The current month is half over. How do you show it?

Show it as month-to-date, labelled partial, compared against the same elapsed window of the prior period. Not against the prior full month, and not annualised. Three things belong on the page: the MTD value, the elapsed fraction (day 15 of 31), and the like-for-like comparison.

The comparison, with the arithmetic

Hypothetical figures: prior month 30 days, total 1,000,000; current MTD through day 15 is 480,000; prior MTD through day 15 was 462,000.

RowArithmeticResult
MTD vs full prior month480,000 / 1,000,000 − 1−52%
MTD vs prior MTD, days 1–15480,000 / 462,000 − 1+3.9%
Run-rate to full month480,000 × 31/15 = 992,000 vs 1,000,000−0.8%, and only if the back half looks like the front half

The first row is the one that reaches a board deck. It shows a decline every month until the final days, because you are dividing 15 days of revenue by 30. The third row is a projection wearing a measurement's clothes: month-end close, payroll runs and promo calendars all sit in the back half, so the run-rate is wrong in a direction you cannot predict from the front half alone.

The query

Postgres 16. :as_of is the last complete day.

WITH b AS (
  SELECT :as_of AS as_of,
         date_trunc('month', :as_of)::date AS cur_start,
         (date_trunc('month', :as_of) - interval '1 month')::date AS prev_start
)
SELECT
  sum(e.amount) FILTER (
    WHERE e.d >= b.cur_start AND e.d <= b.as_of) AS mtd,
  sum(e.amount) FILTER (
    WHERE e.d >= b.prev_start
      AND e.d <  LEAST(b.prev_start + (b.as_of - b.cur_start + 1),
                       (b.prev_start + interval '1 month')::date)) AS prev_mtd,
  (b.as_of - b.cur_start + 1) AS elapsed_days
FROM b CROSS JOIN events e
GROUP BY b.as_of, b.cur_start, b.prev_start;

Trace it at as_of = 2025-06-15: cur_start = 2025-06-01, prev_start = 2025-05-01, elapsed_days = 15. The prior window is d >= 2025-05-01 AND d < LEAST(2025-05-16, 2025-06-01) = 2025-05-16, so May 1–15. Correct.

Now the case that breaks the naive version, as_of = 2025-03-31: elapsed_days = 31, and prev_start + 31 = 2025-03-04. Without the LEAST, the prior window runs into March and double-counts three days of the current month. LEAST(2025-03-04, 2025-03-01) = 2025-03-01, so the window is all of February: 28 days against 31. That asymmetry is real and should be visible in the caption, not hidden.

Showing it

Dashed segment for the partial days, open marker at the last complete day, solid line before it. Caption reads "through 15 Jun 2025 (15 of 30 days), vs 1–15 May 2025". Do not drop the partial period from the chart: a series that stops at the last complete month hides the current state, which is usually the thing being asked about.

Failure modes

  • MTD against full prior month. Systematic false decline, every month, until the last few days. Detect by asserting in a test that the prior window's end is <= the prior month's end.
  • Run-rate or annualised partial. Amplifies whatever the partial happens to contain. Detect by refusing to publish a point projection without a stated assumption and a band.
  • Unequal month lengths. Prior window spills into the current month without the clamp. Detect with the same boundary assertion.
  • Timezone. Cutting the as-of day in UTC while the business runs US/Pacific undercounts the last day by up to 8 hours of orders. Name the timezone in the caption.
  • Unlabelled partial. The right-edge cliff reads as a drop. Detect by requiring the elapsed fraction in the caption; alert when MTD / prior-full-month falls below 0.9 × the elapsed fraction.

Curated: · Written: · Reviewed:

Q&A-62Which exchange rate do you use for a multi-currency revenue report?

The 60-second answer

Default to the transaction-date rate, the spot rate on the day the revenue was booked, because it is the only basis that does not change when you re-run the report. For a period total, the amount-weighted average of those daily rates produces the same number, and IAS 21 permits an average rate for a period as a practical expedient for income and expenses. Use a budget rate only when the question is plan-versus-actual. Use a current or closing rate for balance-sheet items, never for a revenue total you intend to compare across periods.

Assumptions I would state before answering: what "revenue" means here (booked or recognized, not cash collected), the reporting currency, and whether the general ledger already stores a functional-currency amount. If it does, use the ledger's number: that is the audited rate, and re-deriving it invites a reconciliation break.

Why the basis matters

The rate basis is part of the metric definition, not a formatting choice. Transaction-date rates answer "what did we earn." A fixed budget rate answers "how did operations do, holding FX still." A current rate answers "what is this worth today," which is the wrong question for revenue.

Worked example: a GBP entity reporting in USD, Q1:

BasisRate appliedReported (USD)Moves when re-run
Transaction date (spot at booking)1.27 / 1.25 / 1.24503,000no
Period average (amount-weighted)1.2575503,000no
Budget / plan rate1.20480,000no
Current / closing rate1.15460,000yes

Transactions: £200,000 at 1.27, £100,000 at 1.25, £100,000 at 1.24. The transaction-date total is $503,000. The amount-weighted average rate is (200,000×1.27 + 100,000×1.25 + 100,000×1.24) / 400,000 = 1.2575, which reproduces $503,000 exactly, so "average rate" and "transaction-date rate" agree on the total, and you can use whichever your ledger produces. A simple unweighted average (1.2533) would give $501,333 and quietly disagree with the ledger by $1,667. The current rate gives $460,000 today and a different number tomorrow.

Implementation

-- Postgres 16; fx_rates holds one row per (base, quote, rate_date) on business days
SELECT t.txn_id,
       t.amount_gbp,
       t.amount_gbp * r.rate AS amount_usd
FROM transactions t
JOIN LATERAL (
  SELECT rate
  FROM fx_rates
  WHERE base = t.currency AND quote = 'USD' AND rate_date <= t.txn_date
  ORDER BY rate_date DESC
  LIMIT 1
) r ON TRUE;

The rate_date <= txn_date ... ORDER BY DESC LIMIT 1 is the load-bearing part: FX is not published on weekends or holidays, so an equi-join on the date silently drops those rows.

Failure modes

  • Restated history: a current-rate report changes prior periods daily. Detect by re-running a closed period on consecutive days and diffing; the figures must be identical.
  • Mixed bases in one total: summing a transaction-date row and a current-rate row. Tag every row with its rate_basis and assert a total has exactly one.
  • Rate gaps: weekend and holiday joins drop revenue or propagate NULL. Check row count before and after the join, and NULL-check the converted amount.
  • Double conversion: applying a rate to an amount already in the reporting currency. Assert source currency ≠ target currency first.
  • Rounding: converting per row then summing differs from summing then converting. Pick one grain and keep it, or reconciliation drifts.

The basis is a documented property of the metric. A revenue figure without one is not defensible.

Curated: · Written: · Reviewed:

Q&A-63A sale is refunded next month. How does that appear?

This is an area where a dashboard that renders and a figure that is right are different events.

Restating the original month keeps the lifetime figures clean and changes a period that has already been reported; recording the reversal in the current month keeps history stable and makes the current month look worse than trading was. Finance usually requires the second.

Concretely, record the reversal as its own row at its own date, keep the link to the original transaction, and offer a lifetime-value view that nets them where that question is asked, labelled distinctly from the period view.

The reason for that specificity is a failure I have seen: Refunds restated the original month; three previously published monthly figures changed after the fact and the finance team stopped using the report.

A March sale refunded in April.

TreatmentMarchApril
restate original4.09m4.20m
reversal in period4.10m4.19m
as published4.10m—

I would not consider it settled without evidence: Re-run a closed period after a refund and confirm the published figure is unchanged.

A published period should not move.

Curated: · Written: · Reviewed:

Q&A-61The org chart is reorganised. What happens to last quarter's team report?

Last quarter's report does not change. It is a statement about a closed period, and the org chart in force during that period is part of the fact being reported. The reorg is a new fact that applies from its effective date forward. So the as-was report stays frozen and reproducible; if someone wants the new lens on old numbers, that is a second, separately labelled as-is report: never an overwrite.

How you make that true. Model the hierarchy as a Type 2 slowly changing dimension: every product (or person, or cost centre) maps to a team with effective_from / effective_to, and every fact row carries its own date. The join is then a point-in-time join:

SELECT m.team, SUM(f.amount)
FROM revenue f
JOIN product_team_map m
  ON f.product = m.product
 AND f.date BETWEEN m.effective_from AND m.effective_to
GROUP BY m.team;

As-was uses f.date to pick the mapping. As-is uses a fixed as-of date for the mapping interval instead. Same facts, different join predicate, which is exactly why the two views can disagree without either being wrong.

Worked trace. Q3 facts: Checkout 1.30m, Search 2.80m, Ads 3.20m. The map says Checkout→Alpha until 2024-09-30, then Checkout→Beta from 2024-10-01; Search stays Alpha; Ads stays Beta.

TeamAs reported (as-was)Under new structure (as-is)
Alpha1.30 + 2.80 = 4.10m2.80m
Beta3.20m1.30 + 3.20 = 4.50m
Total7.30m7.30m

The total is invariant; only attribution moves. That invariance is your cheapest correctness check.

Trade-offs. As-was is the right default for closed periods: it matches what managers were told and what targets were set against. As-is is right for forward planning and current accountability. A hybrid (as-was for closed periods, as-is for the open one) is usually what finance and planning actually want. What you should not do is restate a closed period in place.

Failure modes.

  • Restating in place. A reorg restated four quarters of team performance under the new structure; managers were compared against figures they had never seen and the review was abandoned. Detect: published reports are immutable; a restatement is a new artefact with its own label.
  • Overlapping intervals. If two map rows cover the same product and date, the join fans out and Checkout's 1.30m counts twice: Alpha reads 5.40m and the total 8.60m. Detect: assert SUM(attributed) = SUM(fact amount).
  • Gaps in the map. If the new interval starts 2024-10-15 but the reorg took effect 2024-10-01, facts in the gap match nothing and an inner join silently drops them. Detect: left-join and count unmatched facts; it should be zero.
  • Mid-period reorgs. A reorg effective mid-quarter splits the quarter across two versions. If you store one version per quarter you pick one silently. Detect: the map's intervals must partition time with no overlap and no gap.

Verification. Re-run a previously published period after the hierarchy change and confirm it matches what was published, byte for byte. If it does not, the join is wrong, not the report.

As-was and as-is are different reports.

Curated: · Written: · Reviewed:

Q&A-85A customer asks to be erased. What happens to the warehouse?

What I'd say first

An erasure request is a lineage problem, not a source-system problem. Deleting the customer row in the OLTP database does nothing to the warehouse, the extracts built from it, or the backups: those are separate copies with separate lifecycles. So the answer is: every copy that can be tied back to that person has to be addressed, and the mechanism differs per copy because the constraints differ.

Assumptions I'd state before answering: (1) what counts as personal data here. A row keyed by customer_id is clearly in scope; a daily revenue aggregate is in scope only if the group is small enough to re-identify, so I'd set a threshold (suppress any cell with fewer than k distinct customers) rather than argue case by case; (2) whether a legal retention obligation overrides erasure for some tables (invoices, tax records), if so, those rows stay and the erasure is scoped around them.

Mechanisms, and when each is right

CopyMechanismWhy not the alternative
Warehouse fact/dim rowsHard delete by customer key, plus a tombstone row (customer_id, deleted_at)Tombstone alone leaves the data readable; hard delete alone loses the ability to replay deletions into a restore
AggregatesRecompute, or suppress cells below the k thresholdDeleting whole aggregate rows destroys non-personal history
Extracts / downstream martsRebuild from the warehouse after the delete, or propagate the tombstone; or delay extract builds until the erasure queue drainsRebuilding is correct but expensive; tombstone propagation is cheap but every consumer must honour it; delaying extracts only works if the queue is short
BackupsExpire on the normal retention clock, or restore-then-replay the deletion ledgerSurgical restore of one customer from a backup is usually impossible; crypto-shredding works only if the data was encrypted per customer

The deciding criteria: how long backups live, whether the data is encrypted per subject, and whether consumers can be trusted to filter tombstones. If backups expire in 35 days and the regulator accepts "erased from live systems, aged out of backups," that's the cheapest correct answer. If not, you need the deletion ledger and a restore procedure that replays it.

Failure mode

I've seen the source-only version: erasure applied in the OLTP database, customer rows still present in the warehouse, in 3 extracts, and in backups. Eight months later a restore brought the deleted rows back into the source, and nobody noticed until the next audit. Detection is a reconciliation job: for each erasure, check every copy for the key and alert on any hit, plus a restore test that replays the ledger and asserts zero hits.

Trace one erasure

  1. Request arrives, key = customer_id.
  2. Delete from warehouse fact and dim tables; write tombstone.
  3. Recompute affected aggregate partitions; suppress cells under k.
  4. Rebuild extracts from the warehouse.
  5. Record the deletion in the ledger with timestamp.
  6. On any restore, replay ledger entries newer than the backup before serving traffic.

The one thing I'd want from the interviewer is the backup retention window, because that decides whether this is a rebuild problem or a ledger problem.

Curated: · Written: · Reviewed:

Q&A-80A customer dimension has 240 attributes. Is that a problem?

Core answer (first 90 seconds)

No: 240 columns is not a problem by itself. Column caps sit far above it (Postgres and Redshift allow 1,600 columns per table, BigQuery 10,000), and in a columnar warehouse a scan reads only the columns the query references, so a wide dimension costs almost nothing at rest. Width becomes a problem for three specific reasons, and I'd check them in this order: attributes with different change rates sharing one SCD2 row, attributes that are mostly NULL and unused, and 240 columns with no owner or PII classification.

Change rate is the one that bites

Under SCD2, a new version of any attribute writes a new row for the whole customer. So one fast-changing attribute versions all 239 stable ones with it.

Worked example: 900,000 customers, one attribute (credit-score band) changes monthly, everything else changes about once a year:

DesignRows after 1 yearVersions/customer
single SCD2 dimension10.8M12
mini-dimension split0.9M main + ~10 mini1

10.8M = 900,000 × 12. The mini-dimension holds the band values (say 10 distinct bands) keyed from the fact, and the main dimension versions once a year. That's a 12× reduction from moving one attribute.

Detect it by measuring, not guessing: rows per natural key over time, or change events per attribute per month off the CDC stream. If one or two attributes drive most of the version growth, split those.

Sparsity and usability

240 attributes usually means several source systems were unioned into one table. Check the null fraction per column: if 200 of the 240 are NULL for 95% of customers, they aren't a dimension, they're an attribute bag. Put them in a separate table or a JSON/variant column and stop paying the cognitive cost. The cost of width in a columnar store is not storage; it's that an analyst with 240 columns picks the wrong one. That's a naming and documentation problem, and it's real.

Governance

240 columns is 240 definitions, owners and PII classifications. If nobody can say who owns column 137, that's the actual problem, and it's independent of the count.

When not to split

If change rates are homogeneous and the columns are well-populated and owned, leave it wide. Splitting adds joins and a second key for no benefit. A wide dimension is fine; a wide dimension with mixed change rates, mostly-empty columns and no owner is not.

Curated: · Written: · Reviewed:

Q&A-81An organisation hierarchy has variable depth. How do you report a rollup?

What a strong candidate says first

Two designs, chosen by how often the rollup runs and whether the hierarchy changes under you.

For ad-hoc or low-frequency rollups, a recursive CTE over the self-referencing parent key is enough: every mainstream warehouse supports it (Postgres, Snowflake, BigQuery and Databricks all ship WITH RECURSIVE). For a rollup that runs constantly or feeds a dashboard, materialise a bridge (closure) table: one row per ancestor-descendant pair, so the rollup becomes a plain join.

Recursive CTE

WITH RECURSIVE subtree AS (
  SELECT id, id AS root_id, 0 AS depth FROM org
  UNION ALL
  SELECT o.id, s.root_id, s.depth + 1
  FROM org o JOIN subtree s ON o.parent_id = s.id
)
SELECT s.root_id, SUM(f.amount) AS total
FROM subtree s
JOIN fact f ON f.org_id = s.id
GROUP BY s.root_id;

The base case seeds every node as its own root at depth 0; the recursive term walks to each child and carries the root down. No maintenance, but it re-walks the tree on every query: on a deep hierarchy with a large fact table that is the cost you pay each time.

Bridge table

-- one row per (ancestor, descendant); depth = distance
CREATE TABLE org_bridge (
  ancestor_id   INT,
  descendant_id INT,
  depth         INT,
  PRIMARY KEY (ancestor_id, descendant_id)
);

SELECT b.ancestor_id, SUM(f.amount) AS total
FROM org_bridge b
JOIN fact f ON f.org_id = b.descendant_id
GROUP BY b.ancestor_id;

The join is cheap, and the depth column answers "direct reports only" (depth = 1) or "whole subtree" (depth >= 0) from the same table. The cost is maintenance: rebuild the closure whenever the hierarchy changes, and version it (effective-from / effective-to on the bridge rows) if historical rollups must stay stable after a reorg.

The hazard that actually bites: fan-out

The bridge multiplies fact rows: each fact at a leaf appears once per ancestor. That is exactly what you want for leaf-additive flows like revenue or cost. It is wrong for measures that are not additive across the subtree.

Take headcount: 1,000 employees, each a leaf row, in a 7-level org. A single root rollup returns 1,000: correct. But SELECT SUM(headcount) FROM org_bridge JOIN fact ... with no grouping returns up to 7,000, because each employee is counted once per ancestor (up to 7 times here). Same trap for distinct counts and ratios. Guard it: attach facts only at leaves, and for non-additive measures aggregate at the leaf level first, then roll up.

Failure modes

  • Fixed level columns (level_1..level_5) silently truncate. A 5-column model against a 7-level org drops the two deepest levels; their revenue lands under the grandparent and the total still looks plausible.
  • Stale bridge after a reorg: rollups keep using yesterday's ancestry until the rebuild runs. Detect by comparing the bridge's max depth and row count against the live org table.
  • Double counting when a fact exists at a non-leaf node that already includes its descendants: the bridge counts it again at every ancestor.

Choosing

ModelDepthRebuild costBest for
Recursive CTEanynonead-hoc, low frequency
Bridge tableanyon every hierarchy changefrequent rollups, dashboards
Fixed level columnsfixednoneonly if depth is contractually fixed

Check the actual maximum depth against the model's capacity before committing to fixed levels.

Curated: · Written: · Reviewed:

Q&A-84A report needs customer-level detail. What do you consider?

The first thing I'd settle is what "customer-level" is actually for. A report that answers "how many accounts churned in EMEA last quarter" needs granularity, not identity: aggregate it and the question is still answered. A report that answers "which accounts are at risk" needs identity, and that makes it a controlled-access surface rather than something you mail to 400 people. Those are different products, and I'd name which one I'm building before touching data.

The assumption I'd push back on hardest is that aggregation anonymises. It doesn't. A cell containing two customers is two customers, and anyone who knows one of them can read the other's number off the report. So the design question isn't "is this aggregated" but "what is the smallest group any cell in this report can resolve to, across every breakdown the report allows".

I've been burned by exactly that. A regional revenue breakdown shipped with a region that had two customers in it. Their individual revenue was readable from the table, and the report went to 400 recipients. The default view looked fine; the problem sat in a dimension combination nobody had checked.

BreakdownSmallest cellIdentifiable
region2 customersyes
region × segment1 customeryes
region, cells under 5 suppressedsuppressedno

What I'd actually do, in order:

  1. Set a minimum cell size and enforce it everywhere. Five is a common floor, ten where the attribute is sensitive. Suppress or band any cell below it, and suppress the complementary cells too, if a row total and all but one column are published, the last column is recoverable by subtraction. That differencing attack is the one people miss.
  2. Check every dimension combination, not the default view. The report's own query surface defines the attack surface. If the tool lets an analyst pivot by region × segment × month, the minimum cell size has to hold for the sparsest combination, not the headline one.
  3. Separate granularity from identity. Where the question genuinely needs row-level detail, put it behind row-level access control with per-user logging, not in a shared extract. Rounding, banding, and top-N-with-remainder are the usual utility-preserving alternatives to outright suppression.
  4. Record why each personal field is in the report. A field with no stated purpose is the one that leaks. Retention and the distribution list belong in the same record.

The failure mode to watch for is silent: suppression that holds in the shipped view but not in the self-serve pivot, or two reports that are individually safe and jointly identifying. Detect it by running the minimum-cell check as a test over the report's query surface, and by diffing successive releases for cells that moved by exactly one customer.

The trade-off is real. Suppression costs utility, and over-suppressing makes the report useless enough that someone exports the raw data instead, which is the failure I'd rather avoid than the one I'd rather detect.

Curated: · Written: · Reviewed:

Q&A-23Managers should see only their own region. Where do you enforce that?

Enforce it in the semantic model or the warehouse, never in the visual. A report filter is a rendering instruction; every path that does not render the report: Export data, Analyze in Excel, the REST API, XMLA/DAX, a scheduled subscription, a downstream tool: reads the table directly. One predicate, defined once, evaluated against an identity the platform supplies, applied to every query path.

Per stack:

  • Power BI: a model role whose filter is Region[UPN] = USERPRINCIPALNAME(), with Region related to the fact table. RLS is evaluated for report viewing, Export data, Analyze in Excel and XMLA reads.
  • Looker: access_filter (or sql_always_where) on the Explore, keyed to a user attribute. It covers Explores only: SQL Runner and any other path to the warehouse sit outside it.
  • Postgres: CREATE POLICY plus FORCE ROW LEVEL SECURITY.
  • Snowflake: a row access policy attached to the table, keyed on CURRENT_USER() against a mapping table.

Postgres 16:

ALTER TABLE sales ENABLE ROW LEVEL SECURITY;
ALTER TABLE sales FORCE ROW LEVEL SECURITY;  -- table owner is not exempt

CREATE POLICY region_isolation ON sales
  FOR SELECT
  USING (region = current_setting('app.region', true));

Three sessions against the same table:

SessionStatementsRows
ABEGIN; SET LOCAL app.region='emea'; SELECT count(*) FROM sales; COMMIT;1,200
BBEGIN; SET LOCAL app.region='apac'; SELECT count(*) FROM sales; COMMIT;3,400
CSELECT count(*) FROM sales;0

Session C returns 0 because current_setting('app.region', true) yields NULL when unset, and region = NULL is NULL, not true. That is the fail-closed property you want; writing ... OR current_setting('app.region', true) IS NULL turns it into a fail-open.

Failure modes:

  • Additive roles. Power BI unions a user's roles. A manager in both EMEA and APAC sees both regions. Detect by building a user × region matrix and asserting one region per user.
  • Workspace role bypass. Workspace Admin, Member and Contributor bypass RLS on the dataset. The manager must be Viewer on the workspace, or get Read/Build through an app. Detect by listing workspace role assignments.
  • Service-principal embedding. App-owns-data embedding evaluates RLS as the service principal unless you pass an effective identity, so every viewer sees every region. Detect by embedding with a restricted user's token.
  • Pooled connections. SET app.region is session-scoped; behind a transaction-mode pooler it leaks to the next request. Use SET LOCAL inside the transaction.
  • Copies. A row access policy or RLS policy protects the table it is attached to. A materialized copy, a CSV pulled from the warehouse, or a second table without the policy is unfiltered.

Verify by authenticating as a restricted user and hitting the export and the API, not by reading the configuration. Assert the row count, not the presence of a filter.

Curated: · Written: · Reviewed:

Q&A-104A regional manager must see only their region's rows in a report everyone shares. How do you drive that from a user-to-region mapping table instead of one role per region?

Two things to pin down first: is the boundary a single region or a subtree (a country manager who should see all their districts), and does anyone read this data outside the semantic model. Direct SQL, an export, another tool? Then: I enforce it in the semantic model with one dynamic role driven by a user-to-region mapping table, and treat warehouse policies as a second control for people who query the warehouse directly.

The build. A mapping table UserRegion(UserPrincipalName, RegionCode), loaded from the IdP or HR feed and refreshed with the model. Grain is one row per user per region they may see, so a country manager gets four rows. Relationship UserRegion[RegionCode] → DimRegion[RegionCode], many-to-one, single direction. Note the direction: filtering the many side does not flow to the one side, so the role filter goes on DimRegion, not on UserRegion.

-- Role: Regional Manager, table filter on DimRegion
DimRegion[RegionCode] IN
CALCULATETABLE (
    VALUES ( UserRegion[RegionCode] ),
    UserRegion[UserPrincipalName] = USERPRINCIPALNAME ()
)

USERPRINCIPALNAME() returns the Entra ID UPN in the service; in Desktop it is the signed-in account. The fact then filters through the existing DimRegion relationship, so every visual, every measure and every XMLA query is scoped. Adding a region is a row in a table, not a model change and not a deployment. If the boundary is a subtree, store the manager's node and test ancestry with PATHCONTAINS against a parent-child or closure table rather than expanding the mapping by hand.

Why the model and not the warehouse first. A warehouse policy protects SQL access, but the report's numbers come from the model, and propagating identity into the warehouse session is something most BI gateways do not do. Do both when there is direct SQL access (Snowflake row access policies, BigQuery row access policies, Postgres CREATE POLICY ... USING (region = current_setting('app.region'))) and keep the mapping table as the single source for both.

WhereProtectsBypassed by
Dynamic model rolevisuals, measures, XMLA queries, model exportsdownloaded .pbix/CSV already on a laptop; service-principal embedding without an effective identity
Warehouse row access policydirect SQL, other toolsnothing, if identity is propagated, but the gateway often does not set it

Failure modes worth naming.

  • UPN mismatch. B2B guests arrive as ana@contoso.com#EXT#@fabrikam.onmicrosoft.com while the mapping row says ana@contoso.com; the manager gets an empty report and files a bug. Detect with a nightly check that every mapping UPN resolves to a real account.
  • Service principals bypass RLS. Embedded reports using app-owns-data do not apply RLS unless you pass an effective identity. Test that path explicitly.
  • Measures that remove filters leak. DIVIDE ( [Sales], CALCULATE ( [Sales], ALL ( DimRegion ) ) ) shows a regional manager the global total. Every ALL, REMOVEFILTERS and ALLEXCEPT in a measure is a place to check.
  • A user with no mapping row must see nothing. That is the safe default, and it flips to "sees everything" if the filter is written with NOT IN or a left join.

Testing. "View as role" in Desktop with an arbitrary UPN catches the obvious break, but it does not scale. Automate it: for each UPN in the mapping, query the model over the XMLA endpoint and assert the visible fact row count equals the expected count derived from the mapping. A four-region example: Ana, one region, 12,400 rows; Ben, three regions, 41,900; an unmapped user, 0; a service principal through the embedding path, 0 or full, which is exactly the case you want to catch before go-live.

Curated: · Written: · Reviewed: —

Q&A-37Should the report import data or query the source live?

Import or query live is a freshness-versus-performance decision, and what settles it is what the number on the report is used for. If the decision tolerates data that is a day old, import it. If it genuinely doesn't, query the source. Most reports that claim they need live data don't: the requester usually means "I don't want to wait for tomorrow's refresh," not "I need the last five seconds."

Two questions decide it: how stale can this number be before someone makes a wrong call, and how much read load can the source absorb?

Importing runs an ETL or ELT job into a warehouse or model on a schedule. The report reads a local table, so latency is flat and predictable, and the source pays the query cost once per refresh instead of once per view. Staleness is bounded by the refresh interval and nothing else.

Querying live issues SQL against the source on each interaction. Freshness is seconds, but latency tracks whatever else the source is doing, and every dashboard refresh is read load on a system that may be serving transactions. That's the trade.

ModeData ageReport latencySource load
Import, nightlyup to 24 h~0.3 sone job per night
Direct queryseconds~4 s, worse under contentionevery view
Hybridseconds to 24 h~0.6 srecent partitions only

(Figures illustrative, from a warehouse-backed BI setup.)

Hybrid is often the honest answer: today's partition queried live, everything older served from the imported model. It costs you two code paths and a boundary where the two can disagree, so it's worth it only when the recent slice is what people actually act on.

The failure I've seen: an operations dashboard refreshed nightly, used for intraday decisions for months. The figures never moved during the day and nobody noticed, because the report never said when its data was from. Detect it by putting the as-at timestamp on the report and alerting when refresh lag exceeds the interval.

Curated: · Written: · Reviewed:

Q&A-87When should a question be answered by the operational system rather than the warehouse?

What a strong candidate says first. Route by the consumer's staleness budget, not by which system happens to hold the data. A question about the current state of one entity (this order's status, this account's balance, this device's last heartbeat) goes to the operational system, because every write changes the answer and the consumer acts on it now. A question that aggregates many entities over time (orders shipped last month, year-on-year trend) goes to the warehouse, where an hour of staleness moves no decision. That default only becomes wrong when you can name the pipeline's real lag and show it fits inside the budget.

Where the lag actually comes from

Three hops, each with its own floor:

  • Capture. A nightly extract with an 02:00 window leaves a row written at 02:05 up to about 24 hours stale. CDC narrows that: Debezium on Postgres logical replication polls the WAL every poll.interval.ms (default 500 ms) in batches of max.batch.size (default 2048), so end-to-end is typically around a second and grows with WAL backlog.
  • Transform. A dbt or Spark layer is only as fresh as its slowest upstream. A model scheduled after a 03:00 feed inherits that timestamp no matter how fast the warehouse answers queries.
  • Storage semantics. A materialized view refreshes on a schedule, not on write. Snowpipe Streaming and the BigQuery Storage Write API land rows in seconds, not milliseconds, and neither makes a lookup by primary key cheap.

Why the two workloads conflict

Aggregation wants columnar storage, full scans, few concurrent queries, and tolerates staleness. A point lookup wants a B-tree index on the key, thousands of small concurrent reads, and breaks on staleness. Tuning for one makes the other expensive. A columnar store has no index for a single key, so the lookup scans: BigQuery on-demand bills $6.25 per TB scanned (US multi-region, 2024 pricing), and Snowflake bills a 60-second minimum each time a warehouse resumes. A row store asked to run analytical scans contends with the OLTP traffic it exists to serve.

QuestionStaleness budgetServe fromWhy
this order's statusunder 1 sOLTP system of recordthe consumer acts immediately; the warehouse copy is ~1 s behind via CDC, up to 24 h behind via batch
orders shipped last monthhourswarehouseaggregate over history; nothing changes within the lag
per-minute fraud counterseconds, aggregatedwarehouse with streaming ingestneeds a moving-window aggregate, not one row

Options, and when the rule flips

  • Streaming CDC into the warehouse (Snowpipe Streaming, Storage Write API). Buys seconds and keeps the lookup expensive. This flips the rule for aggregate-over-recent-data, a per-minute counter, not for single-record reads.
  • Read replicas of the OLTP database. Cheap indexed lookups, but replica lag is not a constant. Watch pg_stat_replication.replay_lag: it spikes when a long query on the standby forces the primary to retain dead tuples (hot_standby_feedback), and can reach minutes under write bursts. Fine for internal read-heavy dashboards, wrong for anything that decides a customer-visible action.
  • A cache in front of the API with a short TTL. Correct for hot keys where the last few seconds of writes are irrelevant. Wrong the moment the question is "did this payment post".

The rule flips toward the warehouse only when the question is aggregate in nature and the ingestion path is streaming, or when the operational read is served from a replica whose lag you have measured rather than assumed.

Failure mode, and how you would see it

A support dashboard refreshed by an 02:00 dbt run, used to answer "where is my order" during an 08:00 shift, serves answers up to 23 hours old, and the agent cannot tell a rendered dashboard from a correct one. Detect it with a freshness check on the table rather than on the render: alert when now() - max(updated_at) exceeds the model's SLA.

Curated: · Written: · Reviewed:

Q&A-36A dashboard visual takes 40 seconds. Where do you look first?

First 60 seconds

I'd open the generated query and look at rows scanned, not the chart. The visual shows 20 rows; the query may be scanning hundreds of millions to produce them. Cost here is driven by rows scanned and the cardinality of the grouping, not by how complex the expression looks.

Assumptions I'd say out loud: the visual plots a small number of points, the data sits in a columnar warehouse or a partitioned table, and the 40 s is query time, not rendering or network. If it's rendering, none of this applies.

Then the answer: check what the visual actually requests. A chart that plots 20 points does not need 1.4 million rows.

Why it behaves this way

Two multipliers. Rows scanned sets the floor: you pay for every row the engine reads, whether or not it survives the filter. Grouping cardinality sets the output size and the sort/aggregate cost on top. A visual grouped by a high-cardinality attribute asks for a result the user cannot read anyway, and pays to build it.

A failure I've seen

A visual grouped by customer identifier across 3 years returned 1.4 million rows for a chart that rendered the top 20. The query scanned 780 million rows to draw a picture of 20.

GroupingRows returnedRows scannedDuration
customer id1,400,000780m40 s
customer tier5780m22 s
tier, from aggregate51,8000.4 s

The middle row is the tell: same scan, fewer rows out, 18 s saved. The grouping was doing real work. The bottom row is the fix: pre-aggregate, and the scan drops from 780m to 1,800.

What I'd change

Group at a level a person can read. Push filters early so they prune before the scan, not after. Use partition pruning for wide date ranges. Where the same visual is hit repeatedly, materialize an aggregate.

How I'd know it worked

Compare rows scanned before and after, not wall-clock alone: wall-clock moves with cache and concurrency. If rows scanned stays at 780m and the time drops, you fixed the sort, not the scan, and it comes back under load.

Curated: · Written: · Reviewed:

Q&A-55A query filters to one month and still scans three years. Why?

Core answer (first 90 seconds)

The filter is on the wrong side of a function, or on a column that isn't the partition key. Partition pruning is a plan-time decision: the planner looks at the predicate, asks which partitions can possibly contain a matching row, and skips the rest. It can only do that when the predicate is a sargable range on the partition key itself. Wrap the key in a function (DATE_FORMAT(created_at, '%Y-%m') = '2024-03', YEAR(created_at) = 2024, CAST(created_at AS DATE)) and the planner sees an opaque expression, not a range, so it scans every partition.

The fix is to move the function to the literal side: created_at >= '2024-03-01' AND created_at < '2024-04-01'. Same result, and now the planner can prune.

Why it behaves that way

Pruning is a static analysis over the predicate tree. The planner needs a constant bound on the partition key. Anything that breaks that: a function on the column, an implicit cast of the column (type mismatch between column and literal), a predicate on a derived column that isn't the partition key, a non-constant comparison (a value from a subquery or a parameter the planner can't see at plan time), or an OR that mixes the partition key with a non-partition predicate: turns the predicate into a filter applied after the scan, not a partition selector.

Alternatives, and when the rewrite doesn't apply

  • Sargable rewrite (above). Works when the partition key is the raw column and the predicate is a monotonic range.
  • Generated partition key. If the app genuinely filters on a derived value (a local date, a normalized tenant key) add a stored generated column and partition on that, then filter on it. Postgres and MySQL both spell it GENERATED ALWAYS AS (...) STORED. This is the right move when you can't change the query shape.
  • Runtime pruning. Some engines (Postgres, for parameterized nested-loop joins) can prune at execution time even when the plan-time predicate is a parameter. A plan that shows all partitions isn't always the final answer: check the actual execution.
  • When none of this applies: if the predicate is genuinely non-sargable and not monotonic, WHERE abs(created_at - now()) < interval '1 month', no rewrite prunes it. You need a different access path: an expression index, a materialized rollup table, or accept the scan. And if the filter is on a column that isn't the partition key at all, no rewrite of that predicate will prune; you must partition on the column you filter by.

Failure modes and detection

  • Silent full scan: results are correct, latency is 30x. Detected only by reading the plan, not by testing correctness.
  • Implicit cast: partition column is VARCHAR, literal is a date; the engine casts the column, not the literal. Check the column type and the plan's filter expression.
  • Plan-time vs execution-time: a prepared statement may show all partitions in the generic plan but prune at runtime, or the reverse. Compare EXPLAIN with EXPLAIN ANALYZE.

Worked example (illustrative figures)

Daily partitions over three years ≈ 1,096 partitions; one month = 31.

PredicatePartitions scannedDuration
DATE_FORMAT(created_at,'%Y-%m') = '2024-03'1,09648 s
created_at >= '2024-03-01' AND created_at < '2024-04-01'311.4 s

Verify by reading the partitions-scanned count in the plan: Postgres shows it as subplans removed in the Append node, MySQL 8 as the partitions column in EXPLAIN, and comparing it to the number the predicate should select.

Curated: · Written: · Reviewed:

Q&A-56Why does selecting fewer columns matter so much in a warehouse?

The 60-second answer. In a columnar store, a scan touches only the column chunks a query references. Parquet, ORC, and Iceberg tables store values grouped by column inside each row group, so a query projecting 3 of 84 columns reads 3 column chunks per row group and skips the other 81. Bytes scanned scale with the width of the projection, not with the width of the table. SELECT * gives back exactly the property the format was chosen for: every query over a view or model that reads wide pays for the full row.

A worked example, hypothetical but shaped like what shows up in a warehouse billing bytes scanned:

Projection over the same 84-column, 4 TB factBytes scannedWall time
3 columns1.2 GB0.9 s
all 84 columns34 GB25 s

The 28x factor is not a special case. It is roughly the ratio of the two projections' total column size, which is what the reader decompresses, decodes, shuffles, and moves over the network.

Why it behaves that way. Each column chunk is stored and compressed independently, so reading one costs one seek plus decode of that chunk. The file footer carries the schema and per-chunk min/max statistics; the reader consults those before touching data. A wide projection multiplies the number of chunks read per row group and pushes the extra columns through every downstream operator, including the shuffle. Single-column delta encodings and dictionary encodings also compress far better than the mixed columns in a whole-row blob, so wide reads cost more per byte moved as well as moving more bytes.

When column pruning is not the lever.

  • Partition and cluster pruning already did the work. A query pinned to one of 730 daily partitions scans a slice; 28x of a small slice may sit inside the budget, and the real cost driver is scan frequency, not width.
  • Row-group skipping fires. If a highly selective predicate prunes every row group via min/max stats, no column chunks are read at all, whatever the projection lists.
  • The table is small. Per-query fixed overhead (planning, metadata, listing) dominates, and its real lever is result caching.
  • Ad-hoc exploration. Enumerating 84 column names to save a few seconds of scan is usually the wrong trade; read wide once, then land the narrow table you actually reuse.
  • A deliberately wide mart. A table built once and read narrow by many consumers is fine. The mistake is wide reads at query time, not wide storage.

Failure modes and how you catch them. A view defined as SELECT * bakes in the full width; every downstream query inherits it and you see it as flat bytes-scanned per query across unrelated reports. BI tools and ORMs that default to SELECT * produce the same signature from the client side. For nested fields, Parquet stores each leaf as its own column chunk, so projecting one field of a struct can read only that leaf, but engines differ in whether they materialize the whole struct, so read the plan rather than assuming. Detection is the same in all three cases: per-query bytes scanned from the warehouse's audit views or the query plan, plus a bytes-scanned threshold asserted in CI on the models you own. If a narrow and a wide selection over the same rows show the same bytes scanned, projection was not the cost driver and something else is.

Curated: · Written: · Reviewed:

Q&A-79How do you speed up a filter that always uses the same column?

Assumptions first: Postgres 16 or MySQL 8.0 InnoDB, a table large enough that the current plan is a sequential scan, and a predicate that is equality or a narrow range on one column. If the column is already indexed, the question is why the planner isn't using it: see the failure modes below.

The default answer is a B-tree index on that column. CREATE INDEX ON events (event_date); A B-tree keeps keys sorted, so an equality probe descends ~3–4 pages to the leaf and then walks the leaf chain for matches: cost is O(log n + k) page reads plus k heap lookups, against O(n) pages for a seq scan. That is the whole mechanism, and it is the fix for the common case.

The index only wins when the predicate is selective. The planner knows this and will ignore your index above a crossover point, correctly. Illustrative numbers on a 100M-row, 100-byte table (10 GB heap, seq scan at ~1 GB/s ≈ 10 s):

SelectivityRows matchedPlanWhy
0.01%10kindex scan~10k scattered heap fetches at ~0.1 ms ≈ 1 s
1%1Mseq scan1M random fetches ≈ 100 s, worse than 10 s
1%, covering index1Mindex-only scanno heap fetches, ~2 s

So if the filter is not selective, the index is not the answer: physical ordering is. Cluster the table on that column (CLUSTER events USING events_event_date_idx in Postgres, or a clustered index in InnoDB/SQL Server) so the matching rows sit in adjacent pages and the scan reads only that range. Or partition by the column so pruning skips whole partitions before any page is read. Both reduce bytes read rather than work per byte.

When not to index. A low-cardinality column, status with five values, is a poor B-tree candidate: the index matches a fifth of the table and the planner will seq-scan anyway. Use a partial index (WHERE status = 'failed') if one value is the hot one. On a write-heavy table, every index adds write amplification and space; measure before adding a fourth one.

Failure modes, and how to see them.

  • Index exists, unused. Run EXPLAIN (ANALYZE, BUFFERS) and compare estimated vs actual rows. Stale statistics after a bulk load are the usual cause: ANALYZE events; fixes it.
  • Predicate defeats the index. WHERE date_trunc('day', ts) = '2024-05-01' cannot use an index on ts; rewrite as a range or add an expression index. An implicit cast or collation mismatch does the same thing silently.
  • Clustering decays. Postgres CLUSTER is a one-time rewrite; new rows land out of order, so the benefit erodes with insert volume. Re-cluster on a schedule, or use partitioning where new partitions are naturally ordered.

Measure bytes scanned before and after for the real query mix, pg_stat_statements plus BUFFERS output, not for a chosen example.

Curated: · Written: · Reviewed:

Q&A-77A dashboard's cost tripled after a redesign. What changed?

What a strong candidate says first

Clarify two things: which warehouse and which pricing model. Scan-priced (BigQuery, Athena) or runtime/credit-priced (Snowflake, Redshift, Databricks), and what "tripled" is measured against, the whole bill or the dashboard's share of it. Then answer directly: a redesign moves cost through exactly two levers, bytes scanned per execution and executions per day. A 3× jump almost always means one of them moved by roughly 3×, and the two usual culprits are a shortened refresh interval and a filter that stopped pruning partitions.

The cost equation

Scan-priced: cost = executions × bytes scanned × price per byte. Runtime-priced: cost = executions × (warehouse size × runtime), where bytes scanned still drives runtime, so it shows up indirectly. Either way, attribute before optimizing: pull the query log and group by dashboard.

-- Snowflake; ACCOUNT_USAGE lags up to ~45 min, INFORMATION_SCHEMA.QUERY_HISTORY is fresher but keeps 7 days
select query_tag,
       count(*) as executions,
       sum(bytes_scanned) as bytes_scanned,
       sum(bytes_scanned) / count(*) as bytes_per_exec
from snowflake.account_usage.query_history
where start_time >= dateadd('day', -7, current_timestamp())
  and query_tag like 'dashboard:%'
group by 1
order by bytes_scanned desc;

BigQuery's equivalent is INFORMATION_SCHEMA.JOBS_BY_PROJECT with total_bytes_billed. If bytes_per_exec is flat and executions tripled, the redesign changed cadence or caching. If executions are flat and bytes_per_exec tripled, it changed the query.

What redesigns actually change

ChangeLeverWhy cost moves
Extract/materialized table → live queryexecutionsevery viewer refresh now hits the warehouse
Auto-refresh 1 h → 1 minexecutions60× more runs for the same data
Default date filter removed or widenedbytes/execpartition pruning lost, full scan
New visual joins a high-cardinality dimensionbytes/execfan-out inflates rows before aggregation
Row-level security addedexecutionsone shared result becomes one query per user
Warehouse resized up to fix a slow tileruntime2× size ≈ 2× credits per second

Worked example (hypothetical figures)

A 1-minute auto-refresh, 90 viewers, BI tool caching one result per interval, 9.7 GB scanned per execution:

IntervalExecutions/dayBytes scannedRefreshes returning new data
1 minute1,44014 TB24
1 hour240.23 TB24

The source table updates hourly, so 1,416 of the 1,440 daily executions return identical results. Aligning refresh to the update cadence cuts 14 TB/day to 0.23 TB/day: 60×. If the tool does not share cache across viewers, executions become 90 × 1,440 = 129,600/day and the scan is ~1.26 PB/day; check that before trusting any per-dashboard number.

Failure modes and how to detect them

  • Cache keyed per user: executions ≈ viewers × intervals. Detect by comparing executions/day against intervals/day.
  • Fan-out join: bytes_per_exec rises while result row count rises faster. Detect by comparing rows returned to the grain of the visual.
  • Partition pruning silently lost: a date filter moved into a calculated column or a timezone conversion. Detect by comparing bytes_per_exec before and after the change on the same date range.
  • Refresh storm at 09:00: every viewer's dashboard refreshes on open. Detect by plotting executions by minute.

Trade-offs

Caching and longer intervals trade freshness for cost, and that is the right trade only when the decision cadence is slower than the data cadence. When the dashboard drives an operational decision (an incident view, a live queue) pay for the freshness and cut cost elsewhere: pre-aggregate to the visual's grain, or move the heavy join into a scheduled model.

Curated: · Written: · Reviewed:

Q&A-78When is a materialised view the right tool?

The 90-second answer. A materialised view is right when one expensive query is read far more often than its underlying data changes, and the readers can tolerate a bounded staleness window. The deciding test is arithmetic, not taste: refresh cost amortised over read volume has to land well below the cost of running the query live, and the aggregate has to be one the engine can maintain incrementally, or the full recompute has to stay cheap as the source grows. It is the wrong tool when consumers will put its output beside live data and compare the two, and wrong when the aggregate is non-invertible (median, count-distinct, percentile), because every refresh is then a full scan whose cost grows with the table while the staleness window stays fixed.

Mechanism: what a refresh actually costs. A refresh is either a full recompute or a delta application, and that choice sets the staleness window you can afford. Postgres core has no incremental maintenance: REFRESH MATERIALIZED VIEW re-runs the defining query over the whole source. CONCURRENTLY avoids the ACCESS EXCLUSIVE lock that otherwise blocks readers for the duration, but it requires a unique index and is slower, because it diffs the new result against the old one. Snowflake dynamic tables, BigQuery materialized views and Oracle fast-refresh materialized views (with materialized view logs) apply deltas, so refresh cost tracks changed rows rather than table size: that is what lets you set a short TARGET_LAG without paying a full scan each cycle.

-- Postgres 16
CREATE MATERIALIZED VIEW mv_daily_revenue AS
SELECT account_id, date_trunc('day', created_at) AS day, sum(amount) AS revenue
FROM orders
WHERE status = 'paid'
GROUP BY 1, 2
WITH DATA;

CREATE UNIQUE INDEX ON mv_daily_revenue (account_id, day);

REFRESH MATERIALIZED VIEW CONCURRENTLY mv_daily_revenue;

Trace: the unique index is what makes CONCURRENTLY legal. Without it Postgres raises cannot refresh materialized view "mv_daily_revenue" concurrently. With it, the refresh builds the new result alongside the old and swaps at commit, so readers keep hitting the previous snapshot until the swap lands. That gap is the staleness window, and it is the number you publish.

-- Snowflake: incremental by default where the query supports it
CREATE DYNAMIC TABLE dt_daily_revenue
  TARGET_LAG = '10 minutes'
  WAREHOUSE = transform_wh
AS SELECT account_id, date_trunc('day', created_at) AS day, sum(amount) AS revenue
   FROM orders WHERE status = 'paid' GROUP BY 1, 2;

sum is invertible, so Snowflake applies the delta from change tracking; swap it for median(amount) and the refresh degrades to a full recompute.

The arithmetic that decides it (illustrative figures). An aggregate scans 200M rows in 6 s and the dashboard runs it 5,000 times a day: 8.3 hours of compute. Refreshing the same aggregate every 10 minutes is 144 × 6 s ≈ 14 minutes: roughly a 35× reduction, bought with up to 10 minutes of staleness. If the live query instead took 200 ms, the materialised view saves nothing and adds a moving part.

Alternatives and when each wins.

OptionFreshnessRefresh costPick it when
Application cache (Redis)TTL, secondsper-key, no scanresult is small and keyed; no SQL aggregation needed
Pre-aggregated table via dbt/ETLschedulefull rebuild or incremental modelyou already run a scheduler and want lineage and tests
Materialised viewengine-managedrecompute or deltathe query is SQL, read-heavy, staleness is bounded and displayable
Streaming rollup (Flink, Kafka Streams)sub-secondcontinuousfreshness must be seconds and the source is a stream

Failure modes and how they show up.

  • Stale-versus-live comparison. A matview refreshed hourly sitting next to a live detail view on one dashboard gets escalated as a data-quality incident. Detect it by rendering the as-of timestamp next to every figure the view produces, and alerting when refresh age exceeds twice the interval.
  • Refresh lock. A non-concurrent refresh blocks reads; on a busy dashboard this appears as a p99 latency spike that lines up with the refresh schedule.
  • Non-invertible aggregate. Refresh duration trends upward with source size until it exceeds the interval and refreshes start overlapping. Watch the refresh-duration trend, not just success/failure.
  • Silent refresh failure. The view keeps serving old data indefinitely. Alert on time since last successful refresh, not on job exit code alone.

Curated: · Written: · Reviewed:

Q&A-15A dashboard shows revenue of 4.2 million. What is missing?

A bare "4.2 million" is missing its unit, its window, its definition, its scope and its freshness. Each of those can move the number by more than the trend anyone is arguing about, so name them before interpreting anything.

MissingThe question that resolves itHow much it moves the number
Unit and currencyUSD thousands or EUR millions?1000x, plus FX
WindowPoint-in-time or a period? Which period, fiscal or calendar?A quarter against a year is 4x
DefinitionBookings, billings, or revenue recognized under ASC 606 / IFRS 15? Gross or net of refunds, discounts and tax?At a 15% marketplace take rate, gross is ~6.7x net
ScopeWhich entities, products and regions are summed?Consolidation adds or drops whole businesses
FreshnessWhen was the tile last loaded, and from which close?A nightly batch is up to 24h stale; a monthly close is up to 30 days behind

Once those are pinned, the number still is not a finding, because a finding is a comparison. The comparison has to come from the decision being made (against plan, against the same period last year, against a control) and it has to be labelled on the visual so someone who was not in the design meeting knows what they are looking at. Defaulting to the previous period reports a crisis every January in a seasonal business.

Worked example, a subscription business, January, three comparisons of the same 4.2M:

ComparisonChangeReads as
month on month-34%crisis
year on year+8%growth
against plan-2%on track

The -34% is the seasonal renewal cycle, not a business event. Three quarters of the executive attention in that meeting went to a pattern that repeats every year. The fix is not to hide the month-on-month number; it is to make the year-on-year and the plan comparison the headline and keep the baseline stable so the trend is about the business rather than about the baseline.

Concrete failure modes and how to catch them:

  • Currency mixed in a consolidation. A tile that sums EUR and USD entity figures without conversion produces a total that is not a currency. Detect it by checking whether the tile equals the sum of the converted entity figures; if it does not, the semantic layer is summing raw columns.
  • Definition drift between teams. Sales reports bookings, finance reports recognized revenue, and the two differ by the deferred portion of annual contracts. Detect it by reconciling the dashboard to the finance close for the same period; a persistent gap that scales with contract length is the tell.
  • Restatement after refunds. Revenue recognized in-period and refunded later leaves the tile showing the pre-refund figure until the next close. Detect it by comparing the tile to the ledger after close, not before.
  • Stale load. A failed nightly job leaves yesterday's number on screen with no visual cue. Detect it by surfacing the last-refresh timestamp on the tile and alerting when it exceeds one load interval.

Before treating any comparison as settled, check it against a full year of history to confirm it does not encode a seasonal artefact as a finding, and confirm the tile's definition matches the one in the semantic layer rather than a copy in a notebook.

Curated: · Written: · Reviewed:

Q&A-16When is it acceptable to start a bar chart's axis above zero?

My answer to truncated axes and misleading charts begins with the definition, agreed before anything is built.

A bar encodes magnitude by length, so truncating the axis multiplies the apparent difference by an arbitrary factor. Lines encode change rather than magnitude, so a non-zero baseline is legitimate there and often necessary to see the variation.

Concretely, keep bars anchored at zero, use a line or a dot plot where the interesting variation is small relative to the level, and label the axis clearly in either case so the reader can calibrate.

The reason for that specificity is a failure I have seen: A bar chart from 94 to 100 percent made a 1.2-point difference look like a threefold gap; the decision taken from it was reversed when the same data was shown on a full axis.

Same 1.2 points, three axes.

AxisBar ABar BApparent ratio
0 to 10097.498.61.01x
94 to 1003.44.61.35x
97 to 990.41.64.0x

I would not consider it settled without evidence: Ask whether a reader could reach the wrong conclusion by reading the chart correctly, and change the encoding if they could.

A bar's length is the claim; do not scale it arbitrarily.

Curated: · Written: · Reviewed:

Q&A-17Status is shown by red, amber, and green. What is wrong with that?

A red/amber/green status is a colour-only encoding, and that fails WCAG 2.2 success criterion 1.4.1 (Use of Color, Level A): colour must not be the only visual means of conveying information. Two populations lose the signal. Red-green colour vision deficiency (deuteranomaly and deuteranopia most commonly, protanopia less so) affects roughly 8% of men and 0.5% of women of European descent. And greyscale rendering (a printout, a screenshot pasted into a slide, a projector) collapses red, amber and green toward the same grey, because they differ mainly in hue, not in luminance.

The fix is redundant encoding: keep colour, add a second channel carrying the same information. Shape or icon, a text label, or position in a sorted list all work, and each costs something different.

EncodingDistinguishable with red-green CVDSurvives greyscaleRenderings readable of 2
colour onlynono0
colour + iconyesyes2
colour + labelyesyes2

A label is the most robust and the most expensive: it costs horizontal space in every row and clutters a dense table. An icon costs less space but needs a legend, and a legend is a second thing to read and to keep in sync. Position works only when the list is already sorted by status; it breaks the moment status is one column among many. So the choice is driven by the surface, not by taste.

Colour-only is defensible in exactly one case: when the same information is already present as text elsewhere in the same view, or when the surface is a single-user terminal dashboard that is never printed or shared. Even then it is a bet on the audience, and the bet is cheap to avoid.

I have seen the failure this prevents. A red/amber/green dashboard was printed for a board meeting; every status rendered as the same grey, and the escalation the design intended to trigger did not happen. The status was readable to the designer and to no one in the room.

To detect it before that: render the dashboard in greyscale and under a colour-deficiency simulation (Coblis, or the vision-deficiency emulation in Chrome DevTools) and confirm the status is still readable. If you use a colourblind-safe palette, Okabe-Ito is the common default for categorical status. Check the redundant channel's contrast too; a grey icon on a grey row is no better than a grey dot.

An encoding that fails in greyscale fails in the board pack.

Curated: · Written: · Reviewed:

Q&A-20Two series on a dual axis move together. What can you say?

The short answer

Two series moving together on a dual axis is a question, not a finding. The chart is consistent with at least four stories: A causes B, B causes A, a third variable drives both, or the two are unrelated and the axes were scaled to make them look coupled, and the chart itself cannot separate them. What you can legitimately say is that the two series are correlated over the window you plotted, and then state which of those four explanations you have ruled out and how.

Why the dual axis makes it worse

A dual axis gives each series its own scale, and you choose both. Rescale either one and the lines can be made to cross, diverge, or track each other almost perfectly. So the visual coupling is partly an artefact of your axis limits, not a property of the data. Even on a shared axis, co-movement is not evidence of a mechanism: two series that both grow with population, headcount, or time will correlate near r = 1 with no causal link at all.

What actually distinguishes the explanations

  • Reverse causation: does B move first? Check the lag structure, but a lag alone does not establish direction.
  • Common cause: name the confounder, control for it, or find variation in A that is independent of it.
  • Coincidence: how many series did you look at? With enough pairs, some will track by chance.
  • Aggregation: the relationship can flip sign between groups and disappear or reverse in the pooled data (Simpson's paradox).

The tools that settle it are a randomised holdout, a natural experiment or difference-in-differences, or an instrument. Observational correlation, however clean the chart, does not.

A worked example (hypothetical)

Marketing spend and revenue both rose over eight quarters, plotted on a dual axis, and the chart was used to justify a bigger budget. Both had also grown with headcount. A later geo holdout (spend lifted in half the regions, held flat in the other half) found incremental revenue about 0.3x what the chart implied.

ExplanationConsistent with the chartRuled outHoldout result
spend drives revenueyesno~0.3x the chart's implied effect
revenue drives spendyesnonot tested
headcount drives bothyesnonot tested

What to say in the room

State the alternative explanations, name the confounder you would need to rule out, and say whether you have. Reserve causal language for a design that supports it. If you must show two series, use a shared axis or index both to a common base, and label the chart as descriptive.

Curated: · Written: · Reviewed:

Q&A-18A conversion rate is reported as 4.7 percent. Is that defensible?

Verdict. Not as stated. "4.7%" is a claim about precision, and precision is a function of the denominator, which the report omits. If the denominator is at least 1,000 and you show an interval, 4.7% is defensible. If it is in the tens or low hundreds, the decimal is noise and the honest report is "about 5%" or a range.

Assumptions. Sessions are independent Bernoulli trials, one conversion per session, no clustering (no per-account or per-campaign correlation). If sessions cluster, the effective n is smaller than the raw count and every interval below widens.

Why the decimal is the issue. For a proportion p over n trials the standard error is sqrt(p(1-p)/n). At p=0.047 that is 0.212/sqrt(n):

  • n=100: SE = 2.1 points, 95% CI ≈ [0.6%, 8.8%]
  • n=1,000: SE = 0.67 points, 95% CI ≈ [3.4%, 6.0%]
  • n=10,000: SE = 0.21 points, 95% CI ≈ [4.3%, 5.1%]

At n=100 the interval is eight points wide; the tenths digit is meaningless. At n=1,000 the interval is ±1.3 points, so the tenths digit is still noise but the integer is stable. Only past roughly 10,000 does the decimal start to mean something.

There is also an arithmetic tell. 4.7% = 47/1000 in lowest terms (47 is prime). An exact 4.7% therefore requires a denominator that is a multiple of 1,000. At n=100 the achievable rates near it are 4/100 = 4.0% and 5/100 = 5.0%: a 4.7% from n=100 must be a rounded value, and the rounding step is a full point.

One extra conversion.

SessionsConversionsRateRate at +1Δ
1,000474.7%4.8%0.1 pt
2,000944.7%4.75%0.05 pt
10,0004704.7%4.71%0.01 pt

At n=1,000 a single conversion moves the reported figure by a tenth of a point: the same magnitude as the decimal you are quoting.

Options, and when each fits.

  • Rate + denominator + interval. Default for any decision. Use Wilson, not the Wald interval, below ~n=200: Wald gives [0.7%, 9.3%] for 5/100 and can produce a negative lower bound at small counts; Wilson gives [2.2%, 11.2%].
  • Suppress decimals below a stated n. Fine for dashboards where the audience only needs magnitude; state the threshold (e.g. no decimals below n=1,000) so it is a policy, not an accident.
  • Report a bucket. "4–6%" when the interval is wider than the decision threshold. Best when the reader will compare against another figure.

Failure modes.

  • Comparing two segments by their decimals. Two 1,000-session segments at 4.7% and 5.1% differ by 0.4 points, but each interval is ±1.3 points: the gap is noise.
  • Aggregating unequal segments and reporting the pooled rate: Simpson's paradox can flip the sign of the comparison.
  • Quoting a point estimate with no n on a slide that drives a budget decision.
import math

def wilson(k, n, z=1.96):
    p = k / n
    d = 1 + z*z/n
    c = p + z*z/(2*n)
    h = z * math.sqrt(p*(1-p)/n + z*z/(4*n*n))
    return (c - h)/d, (c + h)/d

print(wilson(47, 1000))  # (0.0355, 0.0619)
print(wilson(5, 100))    # (0.0215, 0.1118)

So: 4.7% is defensible only with the denominator attached and an interval beside it. Without the denominator, the answer is "not yet."

Curated: · Written: · Reviewed:

Q&A-44What makes a dashboard usable by everyone who receives it?

A dashboard is usable by everyone who receives it when its meaning survives every rendering path and assistive technology the audience actually uses. Assume the dashboard is a web page or PDF that may be viewed on a 27-inch monitor, projected in a bright room, printed in grayscale, or read by NVDA or VoiceOver. The baseline I use is WCAG 2.2 AA; it is not sufficient for every projected room, but it is the floor.

The decisions that decide usability are encoding, contrast, text size, and fallback. Encode series with at least two channels: color plus shape, pattern, or direct label. A color-only line chart fails for deuteranopia and in grayscale print. WCAG 1.4.1 requires color not be the only visual means. For text, WCAG 2.2 AA requires 4.5:1 for normal text and 3:1 for large text (18pt or 14pt bold). Non-text graphics and UI components need 3:1 against adjacent colors under WCAG 1.4.11. Those numbers are checkable with a contrast calculator; #767676 on white is 4.54:1 and passes normal text; #949494 on white is 3.03:1 and passes only large text or non-text. Projected 9pt labels are below any comfortable reading size; 12pt is marginal, 14pt+ is safer. But font size alone doesn't fix contrast.

Alternatives: a dense single-screen dashboard vs progressive disclosure. Dense can work for expert on-call engineers who know the metrics, but it fails for occasional viewers and screen readers because the reading order becomes arbitrary. Progressive disclosure with a summary and drill-down works better for mixed audiences, at the cost of more clicks. Interactive-only tooltips vs always-visible labels: tooltips reduce clutter but are inaccessible to keyboard and touch users unless they are focusable and dismissible; always-visible labels cost space but survive print and screen readers. Canvas/SVG chart vs HTML table fallback: canvas gives precise layout but hides data from assistive tech unless you add a table or ARIA. The deciding criteria are the audience's assistive tech, the worst rendering condition, and whether the data must be extracted or just glanced at.

Concrete failure modes: a dashboard with a red/green status column that becomes identical gray in print; detected by printing to PDF in grayscale. A chart with 9pt axis labels projected in a bright room; detected by projecting at the actual distance and asking someone to read the lowest series. A canvas chart with no table; detected by running NVDA or VoiceOver and tabbing through. A hover-only tooltip; detected by keyboard-only navigation. A dashboard that relies on 4.5:1 contrast but uses #949494 for 10pt labels; detected by axe-core or Lighthouse.

A screen reader needs the table, not the chart. This is the minimum fallback:

<table>
  <caption>Requests per second by service, 2025-06-01</caption>
  <thead>
    <tr><th scope="col">Service</th><th scope="col">p50</th><th scope="col">p99</th></tr>
  </thead>
  <tbody>
    <tr><th scope="row">checkout</th><td>120</td><td>480</td></tr>
  </tbody>
</table>
EncodingProjectedPrinted grayscaleScreen readerCost
Color-only linemarginalnonolow
Color + shape + direct labelyesyespartial (needs table)medium
Color + pattern + table fallbackyesyesyeshigh

The trade-off is real: the last row costs more implementation and visual space, but it is the only one that survives every condition above. If the dashboard is only ever viewed by a known expert on a calibrated monitor, the first row can be acceptable; for a general audience, it is not.

Curated: · Written: · Reviewed:

Q&A-42A stakeholder asks for a dashboard with every metric they can think of. What do you do?

What I'd say in the room

"I'll build it, but give me ten minutes first, because a dashboard with everything on it is a dashboard nobody reads."

Then three questions, in this order:

  1. What decision does this page change? Not "what do you want to know": what do you do differently when a number moves?
  2. Who makes that decision, and how often? Daily standup, weekly review, incident response?
  3. What's the action when the number is bad? If there's no action, it's a report, not a dashboard.

Assumption I state out loud: the stakeholder is one consumer among several, and the page has two finite budgets. Load time and attention.

The answer

Ship a first version with the panels tied to the named decision, usually 4–8 of them, and put the rest behind a detail view or a linked report. Give the stakeholder a self-serve path to add a panel, so "no" becomes "not on the front page" rather than a refusal.

Why "everything" fails

Every panel is a query, and a dashboard is read in a glance, so signal-to-noise is the product rather than a side effect.

Worked example, figures illustrative: a team ships a 34-panel dashboard. The page fans out 34 queries; serialized behind a small connection pool at roughly 0.65 s each, that's about 22 s to full paint. Post-launch interaction data looks like this:

PanelsShare of interactions
top 491%
next 108%
remaining 201%

Read it as: 20 panels cost load time and attention and returned 1% of use. The 22 s is what the stakeholder feels; the 1% is what the pruning argument rests on. Both come from the same measurement, which is why I instrument interaction per panel from day one instead of arguing about it later.

Failure modes

  • The stakeholder feels overruled. Fix: the detail view plus a single named owner for the front page. One owner is what makes pruning possible at all.
  • Panels are cheap to add and expensive to remove. They accrete because adding is a one-line change and removing needs a conversation. Fix: every panel carries an owner and a review date.
  • Alerting rides on a dashboard panel. A panel that pages someone is a different artifact with a different budget; don't let it share the dashboard's.

What would change my mind

If the decision genuinely needs all 34 (a compliance audit, a monthly close, a one-off investigation) then it's a report, and I'd build a report: paginated, exportable, no glance budget. The mistake is building a report and calling it a dashboard.

How I'd know it worked

Instrument per-panel interaction. After two weeks, panels with zero interactions get removed or moved. A dashboard you can prune is a dashboard that was designed.

Curated: · Written: · Reviewed:

Q&A-43When should a report send an alert rather than being visited?

I would settle alerting from a reporting layer against a hand calculation on a small fixture before trusting the model.

An alert is right where the condition is rare, actionable, and time-sensitive; a scheduled report is right where the review is routine. An alert on a condition that occurs regularly becomes a filter rule in someone's mailbox within a fortnight.

Concretely, set the threshold from the measured distribution rather than a round number, estimate the expected fire rate before enabling, and route to a person who can act rather than to a distribution list.

The reason for that specificity is a failure I have seen: A threshold set at a round number fired on 40 percent of days; recipients filtered it within two weeks, and the genuinely unusual day was filtered with the rest.

Backtested fire rate.

ThresholdDays fired per yearActioned
round number1463
95th percentile189
99th percentile44

I would not consider it settled without evidence: Backtest the threshold against a year of history and publish the expected fire rate before enabling.

An alert that fires often is a mail filter.

Curated: · Written: · Reviewed:

Q&A-48You have one number on the front page. How do you choose it?

I'd pick the number closest to the outcome the team is accountable for, and put its guardrail right next to it. The front page is a steering wheel, not a scoreboard: whatever sits there is what gets optimised, so it has to be the thing you actually want moved.

Two assumptions I'd state out loud before choosing. First, who reads it and what decision it drives: an exec steering the quarter needs a different number than an on-call engineer watching a status page. Second, the time window: a number without a period and a comparison is a level, and a level is not a finding.

Given that, the real choice is between a rate and a level, and between a leading and a lagging indicator.

  • Lagging (revenue, qualified opportunities, churn) is closest to the outcome but slow and hard to attribute to this week's work.
  • Leading (signups, sessions, deploys) moves fast and is actionable but easy to move without moving the business.
  • A level ("4,100 leads") is uninterpretable alone; a rate ("14.9% of leads qualify") normalises and survives volume swings.

My default: the lagging outcome as the headline, expressed as a rate where a denominator exists, with one leading indicator and one guardrail beside it. I'd only lead with a leading indicator when the outcome is too lagging or too noisy to steer (early-stage, or a metric that only settles quarterly) and then the guardrail is non-negotiable.

The failure this prevents is Goodhart's law. Here's the shape of it:

MetricQ1Q2Change
Leads4,1005,740+40%
Qualified opportunities610520−15%
Lead→qualified rate14.9%9.1%−39%

A form change cut a required field. Leads jumped 40% (5,740/4,100) and if leads were the front-page number, that reads as a win. Qualified opportunities fell 15%, and the conversion rate fell from 14.9% to 9.1%. The headline moved because the definition of a lead got cheaper, not because the business improved. The guardrail was on page four and nobody looked.

So the rule I'd apply: the headline is the outcome, the guardrail sits beside it, and the figure is stated as a comparison (against target, prior period, or cohort) so it reads as a finding. "9.1% of leads qualify, down from 14.9%" is a decision. "5,740 leads" is a decoration.

Detection, not just prevention: watch the gap between the headline and its guardrail, and look at the distribution, not the mean. A mean that holds while the median drops is the same gaming in a different costume.

When I wouldn't do this: a status page or safety number. There the front-page figure is availability or error budget, and you want a threshold and a level, not a rate: "99.9% over 30 days, budget 43 minutes", because the decision is binary and the comparison is against the SLO, not a trend.

Curated: · Written: · Reviewed:

Q&A-49A team asks for a dashboard. When is the answer no?

The 90-second answer

No when any of three things is true: the question is one-off, no decision changes with the answer, or the cost of keeping the number fresh is more than anyone would pay for it. Before I say no I ask who acts on it, how often the question comes back, and what they would do differently at a different value. If I can't get a name and an action, it's a no, and I offer the cheaper artefact instead.

The rule I actually apply

Question shapeRight deliverableWhy
Asked once, decision already madeAnalysis in a doc or notebookThe answer is a paragraph, not a page
Recurs weekly or more, drives an actionDashboardThe refresh cost is amortised by repeated use
Recurs, but only one threshold mattersAlert with a thresholdNobody reads a chart to learn "is it over 500"
Recurs, nobody acts on itNothing: fix the processA number nobody moves on is decoration
Data not trustworthy yetData-quality work firstA dashboard on bad data launders the badness

Arithmetic on the cost side (hypothetical figures, to show the shape)

Hourly refresh is 24 × 365 = 8,760 runs a year. At $0.05 a run that's $438 in warehouse spend, before anyone maintains the pipeline. Build is 6 engineer-days plus 2 analyst-days; at a loaded $800/day that's $6,400. Year one lands near $6,800. If the page gets 14 views in that year, each view cost about $485. The one-off analysis that answers the same question is 1 day, no refresh, and the number is in the doc where the decision is recorded.

Failure modes, and how I detect them

  • Dashboard rot. Usage spikes at launch and decays. Detect by instrumenting views per user per week; anything under one view per user per month for two consecutive months gets killed or folded into something else.
  • Refresh cost creep. The source table grows 10× and the hourly schedule stays. Detect with per-dashboard cost attribution in the warehouse; a dashboard whose refresh costs more than the team's other queries combined is a candidate for daily or on-demand.
  • Metric drift. Two dashboards define "active user" differently and the meeting argues about the definition instead of the decision. Detect by versioning metric definitions and giving each metric one owner.
  • Dashboard-as-alert. Nobody looks until it's red, which means it should have been an alert. Detect by counting actions taken per alert versus views per chart.
  • Accountability theatre. The page exists so someone can point at it. Detect by asking what happens if the number is bad; if the honest answer is "nothing", it's theatre.

How to say no without being the blocker

Refuse the artefact, not the question. Offer the one-off analysis, a scheduled email, an alert with a threshold, or a saved query the requester can run themselves. Then set a revisit condition out loud: "if this becomes a weekly question, come back and we'll build it." Instrument usage from day one so the kill decision later is data rather than opinion.

Curated: · Written: · Reviewed:

Q&A-46You have 900 reports. How do you know which matter?

Views tell you what's used. They don't tell you what matters. A monthly board pack read by three people outranks a daily ops report nobody opens, and a report with zero views can still be feeding a downstream system. So I score every report on two axes, not one.

Assumptions I'd state up front: I have per-report view telemetry with user identity, report ownership metadata, and visibility into scheduled subscriptions and downstream consumers. If the dependency data doesn't exist, I say so and treat the first pass as provisional.

Consumption: distinct users in the last 90 days, and their level. Raw view counts lie: one analyst refreshing a dashboard 40 times is one consumer, not forty. I always compare distinct users against raw views.

Dependency: scheduled subscriptions, email bursts, embedded links, and any downstream job reading the report's dataset.

High dependencyLow dependency
High consumptionCore: keep, fund itPopular: keep, but check it isn't a duplicate
Low consumptionDependency-only: keep, find the real consumerCandidate: archive

Worked pass over the 900 (hypothetical figures): 610 had zero views in 90 days. Of those, 40 had an active subscription or a downstream read, so they're dependency-only: keep and chase the subscriber. The other 570 had neither, so they're archive candidates. Of the 290 that were viewed, 90 had ten or more distinct users in the month: core. The remaining 200 sit between and need a judgement call on who reads them.

Archive vs delete. Archive is reversible and cheap; delete reclaims refresh capacity and removes the trust risk permanently. Delete is right when there's no owner, no dependency, no views in 12 months, and the underlying data is retained elsewhere. Retain-without-owner is right when an external obligation, regulatory or contractual, fixes the retention period; there the obligation is the owner, not a person's name. Requiring a named owner for everything else is what stops the estate regrowing.

Failure modes I'd watch for:

  • Telemetry counts automated refreshes as views. Detect by splitting on session and user agent and comparing distinct users to raw views. A 50:1 ratio is a robot, not adoption.
  • A zero-view report with a scheduled export. Deleting it breaks a downstream system silently. Detect with a dependency scan before any archive, not after.
  • Seasonal reports. Dead for 90 days, critical at quarter close. Detect by comparing the 90-day window against a 12-month one before archiving.
  • Owner notification with no reply. Default to archive with a re-review date; never delete on silence.

The decision rule underneath all of it: a report matters if removing it would change a decision or break something. Views are one input. Dependency and decision-criticality are the other, and they're the ones that catch the low-frequency report that actually runs the business.

Curated: · Written: · Reviewed:

Q&A-88Finance maintains a critical spreadsheet. Do you replace it?

Short answer: not until you can reproduce it to the penny. The spreadsheet is the specification; the replacement is the implementation.

Assumptions I'd state up front: it's in the reporting path, finance owns it, and it's critical because a number from it reaches a filing or a board pack. If it's a scratch pad nobody downstream reads, none of this applies: leave it alone.

The decision turns on one question: can you enumerate every rule the spreadsheet applies, including the ones nobody wrote down? If yes, replace it. If no, you're not replacing a spreadsheet, you're deleting a specification.

Criteria I'd use, in order:

CheckReplace ifDon't replace if
Rules enumeratedEvery formula, lookup and manual adjustment is written downAny rule lives only in the maintainer's head
Reproduces outputModel matches the spreadsheet to the penny across a full close cycleIt matches on a spot check
Inputs availableEvery input the spreadsheet reads is in the new systemIt reads a file someone emails monthly
RollbackParallel run for at least one full cycle, spreadsheet still authoritativeCutover on a promise

The third row is the one that kills most projects. A spreadsheet that reads a CSV a person exports by hand is not a spreadsheet problem, it's an interface problem, and replacing the spreadsheet doesn't fix it.

Alternatives, cheapest first:

  1. Wrap it. Feed the spreadsheet from the warehouse, keep it as the calculation engine, version it, back it up. Cost: days. Risk: low. Doesn't remove the key-person dependency.
  2. Extract and run in parallel. Build the model from the extracted rules, run both for a full close, reconcile to the penny, then retire the spreadsheet. Cost: weeks to a quarter. Risk: medium. This is the default for anything critical.
  3. Replace outright. Only defensible when the rules are already documented and the inputs are already in the new system. Rare for a spreadsheet that earned the word "critical".

Failure mode I've seen: a model built from the documented process came out at 12.13m against the spreadsheet's 12.50m. The 0.37m gap was a manual adjustment applied for four years that appeared in no documentation. The model was correct to its spec and wrong to the business. Detection is not a spot check: it's reconciling every line, every period, including the periods where the adjustment didn't apply.

VersionFigureDifference explained
Model from documentation12.13mno
Spreadsheet12.50m—
Model with manual adjustment rule12.50myes

So: replace it when you can reproduce it, and not before. Budget the extraction as the project, not the build.

Curated: · Written: · Reviewed:

Q&A-86A stakeholder asks for revenue "by revenue band". How do you handle it?

What I'd say first

"By revenue band" is a request to group by a measure, and SQL won't let you: you have to turn revenue into a derived dimension first. So the real question isn't the band edges, it's the grain and window the band is computed over, and whether it's computed at query time or persisted. I'd pin those down before writing anything.

Assumptions I'd state back: revenue means recognised revenue, not bookings; the entity is the customer account, not the order; the window is trailing 12 months; edges are in whole USD. Any of those changing changes the numbers.

The decision

Three ways to do it, and they are not equivalent:

ApproachBand isStable across reports?Cost
CASE WHEN in the queryrecomputed per queryno: moves with the filter windownone, but every consumer reimplements the edges
Materialised attribute on the customer dim, refreshed nightlyfixed for the dayyes, within a refresha pipeline, plus a backfill when edges change
SCD2 snapshot (dbt snapshot)fixed per periodyes, historically reproduciblea row per band change

I'd default to the materialised attribute when the band is reported regularly, and reach for SCD2 when someone needs to reproduce last quarter's segmentation. For a one-off exploration, or while the edges are still being argued about, the CASE WHEN is fine and faster to iterate: don't build a pipeline for a question that might change next week.

The SQL

-- Postgres 16; trailing-12-month recognised revenue, whole USD
SELECT
  CASE
    WHEN rev_ttm >= 1000000 THEN 'enterprise'
    WHEN rev_ttm >= 100000  THEN 'mid'
    WHEN rev_ttm >= 10000   THEN 'smb'
    ELSE 'long_tail'
  END AS revenue_band,
  count(*)     AS accounts,
  sum(rev_ttm) AS revenue
FROM customer_revenue_ttm
GROUP BY 1
ORDER BY 1;

CASE evaluates top-down and stops at the first match, so the edges must be descending: flip two of them and every enterprise account silently becomes mid.

Failure modes

The one I've actually seen: the band computed on the fly over whatever period the filter selected. Same customer, three reports:

ReportWindowRevenueBand
quarterly90 days$38ksmb
annual12 months$420kmid
lifetimeall time$1.9Menterprise

Segmentation gets abandoned the moment finance notices. Others: edges changed without a backfill, so last month's report no longer reproduces; refunds making revenue negative, which falls into ELSE and quietly inflates long_tail; and mixing currencies before banding.

How I'd verify

Pick a known account and confirm it lands in the same band in every report that claims to use the same definition, or that the reports state different windows. If they disagree, the definition isn't shared, and that's the bug, not the edges.

Curated: · Written: · Reviewed:

Q&A-66A dashboard shows the treatment group converting better. Can the team ship?

No, not on a dashboard read. A dashboard is a look, not a decision. The team can ship when the predeclared primary metric's confidence interval excludes zero at the planned analysis point (or a predeclared sequential boundary is crossed), and the guardrail metrics haven't regressed. Until then the honest answer is "not yet, and here is what would change it."

What I'd ask first: what was the predeclared sample size and analysis date? Is the metric on the dashboard the primary one? Have you checked sample ratio mismatch? If nothing was predeclared, the dashboard number cannot support a ship decision at all.

Why the dashboard number is not evidence. Every look at accumulating data is a test. Run a fixed-horizon test at α=0.05 repeatedly as data arrives and the family-wise error rate climbs: 2 looks ≈ 8%, 5 looks ≈ 14%, 10 looks ≈ 19%, 20 looks ≈ 25%, and continuous monitoring drives it toward 1. A dashboard re-renders and someone checks it, so a "significant" reading at day 4 is roughly what you'd expect under a true null if you look often enough.

The second mechanism is that early estimates are noisy and the dashboard gets read when they look good. At 340 users per arm the standard error on a difference of proportions near a 10% baseline is about 2.5pp, so a +3.2pp reading sits ~1.3 SE from zero: indistinguishable from noise. As n grows, the estimate converges on the truth.

Worked example, normal approximation on the difference of proportions, baseline ~10%:

DayUsers/armDiff95% CI
4340+3.2pp-1.6 to +8.0
144,100+0.6pp-0.7 to +1.9
30 (planned)12,000+0.1pp-0.7 to +0.9

The day-4 interval spans zero, and so does the planned-end interval. The effect didn't fade: the early number was noise.

Alternatives, and when each is right.

  • Fixed-horizon test: predeclare n and the read date, analyze once. Valid and simple; you cannot stop early or monitor.
  • Group sequential with alpha spending (O'Brien-Fleming or Pocock): predeclare K looks and spend α across them. O'Brien-Fleming demands a large effect to stop early, protecting the final analysis; Pocock spends more evenly and stops earlier at the cost of a stricter final threshold. Use when you can commit to a look schedule.
  • Always-valid inference / confidence sequences: valid at any stopping time, no schedule needed. The price is wider intervals: more data for the same power. Use when the business genuinely must watch continuously.
  • Sequential Bayes factors: monitor a posterior, but fix the prior and the decision rule up front.

Failure modes and how to catch them. Peeking without correction: compare the number of looks against the predeclared schedule. Sample ratio mismatch: test observed arm sizes against the planned ratio; an SRM invalidates the comparison. Segment peeking: slicing by device or country multiplies the looks; predeclare segments or correct for them. Novelty or primacy effects: early lift that decays; plot the effect against exposure time, not calendar time.

So: don't ship. Report the interval and the planned analysis point, and if the team needs to monitor, move to always-valid inference before the next read.

Curated: · Written: · Reviewed:

Q&A-67Retention looks worse this quarter. How would you check?

First 90 seconds

Before answering "is retention worse", I'd pin down three things: which retention definition (N-day return, rolling, or unbounded), which population (all signups, or activated users only), and which window. Then I'd split the question in two: did user behavior change, or did the measurement change? A tracking release, a new signup source, or a denominator change moves the number without anyone behaving differently.

If the metric is stable, the next move is to hold age constant. A pooled retention rate mixes cohorts of different ages and sizes, so it moves when the mix moves even if every cohort behaves identically. Cohort analysis fixes age, which is what makes the comparison mean anything.

The mechanism, with numbers

Worked example, hypothetical figures. A campaign tripled acquisition in Q3. Pooled "30-day retention" puts every acquired user in the denominator, including the ones who haven't been around for 30 days yet: they get scored as not retained.

CohortAcquiredReached day 30Retained at day 3030-day retention
Q14,1004,1002,54262%
Q24,3004,3002,62361%
Q322,00019,61712,16362%
Pooled30,400—17,32857%

Every cohort sits at 61–62% at day 30. The pooled number is 57% because 2,383 Q3 users are too recent to have reached day 30 and are counted as churned. Nothing about retention changed; the denominator did. That is a 5-point "decline" that is entirely an artifact.

What the shape tells you

Once retention is plotted by age for each cohort, the shape localizes the cause:

PatternLikely cause
Every cohort down at every ageReal behavior change: product, pricing, or an outage
Recent cohorts down at every age, older cohorts flatAcquisition quality: a new channel or campaign
All cohorts down at one specific ageA one-time event or a broken lifecycle message at that step
One segment down, rest flatLocalized: platform, geography, or plan tier

Failure modes and how to catch them

  • Right-censoring: never compare a 30-day number for a cohort younger than 30 days. Truncate the window or read a survival curve instead.
  • Small cohorts: a 200-user cohort at 60% has a 95% CI of roughly ±7 points (sqrt(0.6·0.4/200) ≈ 0.035). A 3-point move on 200 users is noise.
  • Seasonality: compare year-over-year at the same age, not just the previous quarter.
  • Denominator drift: if "active user" now excludes a platform or a plan tier, retention moves for definitional reasons.
  • Aggregation over segments: a real drop in one channel can be hidden by growth in another. Segment by acquisition channel, platform, and plan before concluding.
  • Pipeline bugs: validate the cohort query against a hand calculation on a small fixture before trusting it.

What I'd report

Cohort retention at a fixed age, with cohort sizes and a note on which cohorts are still maturing. If every cohort is flat at the same age, the answer to "retention looks worse" is that the pooled metric is misleading, and the fix is to report cohort curves rather than a single pooled rate. If recent cohorts are genuinely lower at the same age, that points at acquisition quality or onboarding, and I'd segment to find where.

Curated: · Written: · Reviewed:

Q&A-68The business wants a forecast on the dashboard. What do you attach to it?

What I attach

First I ask what decision the number feeds. A forecast on a dashboard is an input to a decision (hiring, capacity, spend, a target) and the attachment is whatever makes that decision defensible. Then I attach four things: the interval, the method and its assumptions, the back-tested error, and the refresh date with a named owner.

A point forecast alone is a claim with no error bar, and the first thing a business reader does with it is treat it as a commitment. The interval is the part that carries the information about how much the number should move a decision.

Alternatives, and when a bare point is fine

  • Show nothing on the dashboard; link to a notebook. Right when the forecast is exploratory and no decision is scheduled against it. Cost: nobody uses it.
  • Range only, no point. Right when the decision is a threshold check ("do we cross 5.0m?") and the midpoint invites anchoring. Cost: harder to compare week over week.
  • Scenario bands (base / upside / downside with stated drivers). Right when the uncertainty is dominated by a few discrete choices (a pricing change, a launch date) rather than by noise. Cost: more to maintain, and the drivers have to be real.
  • Point plus interval plus error. The default for a recurring business review.
  • Point only. Acceptable when the decision has enough headroom that the error cannot flip it, or when the figure is already contracted rather than forecast (booked revenue, signed renewals). If the decision threshold sits outside the interval, the point is enough.

Worked check

Hypothetical: forecast 4.6m, back-tested error ±18% → 3.8m to 5.4m. The decision is whether to add two support engineers, triggered at 5.0m. The interval straddles the trigger, so the point does not decide it: the answer is "wait for next month's actuals" or "stage the hire". If the trigger were 6.5m, the whole interval sits below it and the point alone would have been fine.

Failure modes

  • Point becomes target. Someone copies 4.6m into a plan, and the forecast's job changes from predicting to being hit. Detect: the number appears in a target doc without its interval.
  • Miscalibrated interval. A nominal 80% interval that covers 52% of held-out weeks is worse than no interval, because it manufactures false confidence. Detect with a coverage check on held-out data:
# Python 3.12
covered = sum(lo <= y <= hi for lo, hi, y in zip(lo_hist, hi_hist, y_hist))
print(covered / len(y_hist))  # want ~0.80 for an 80% interval
  • Stale model. A forecast fitted a year ago keeps rendering. Detect: the refresh date on the tile is older than the retrain cadence.
  • Method drift. The dashboard shows a number but nobody can say which model produced it. Detect: no method string on the tile.

Curated: · Written: · Reviewed:

Q&A-69Revenue is down 6 percent and the executive wants to know why. How do you approach it?

First 90 seconds. Before touching data, pin down what "down 6%" means. Against which baseline: prior year, prior quarter, or plan? Which revenue: bookings, billings, or recognized revenue? Which currency? A 6% decline in recognized revenue at constant currency is a different investigation from a 6% decline in bookings at actual rates. State those assumptions out loud, then commit to the approach: build a bridge whose components sum to the movement, rank them, and validate the largest against a source that doesn't share its lineage.

The mechanism. Revenue decomposes additively. For each product/customer/region cell, ΔRev = (P₁−P₀)·Q₀ + P₀·(Q₁−Q₀) + (P₁−P₀)·(Q₁−Q₀). The first term is price, the second volume, the third the interaction that mix absorbs. Sum across cells and you get the total. Two adjustments matter: restate both periods at constant currency so FX doesn't masquerade as a business driver, and confirm the revenue definition didn't change. A recognition-timing or reclassification change moves the number with no underlying change.

Two ways to run it. Top-down: start at the segment or product line that moved most, drill to customer. Fast, good for a same-day answer, but it can stop at the first plausible layer. Bottom-up: aggregate transaction-level data into the bridge and reconcile to the GL. Slower, catches mix effects top-down hides. For an executive who needs an answer today, do top-down to name the driver and its rough size, then bottom-up to confirm before anyone acts on it.

Worked example (illustrative figures, not this scenario). A bridge that sums to −6.0 pp:

ComponentContribution
one customer contract ended−5.1 pp
price−0.7 pp
volume, remaining customers+0.4 pp
mix−0.6 pp
total−6.0 pp

The point of the table is that it sums. A list of six plausible causes that doesn't reconcile is not an explanation.

Failure modes. Double counting when a customer lands in both a churn bucket and a volume bucket. An interaction term silently folded into mix. A one-off, a deal pulled forward last quarter, read as run-rate. FX left in. Each is caught by reconciling the bridge to the reported total and by checking the largest component against an independent source: the CRM for a contract, the billing system for price.

What changes the decision. If the driver is a single contract, it's a retention conversation and the run-rate is fine. If it's price across the base, it's a pricing problem. If it's mix, the total may be fine while the margin isn't. Name the driver, its size, and which of those it is.

Curated: · Written: · Reviewed:

Q&A-70A stakeholder pushes back until the number supports their position. What do you do?

First I work out which of three things is actually happening, because the response differs for each. One: the stakeholder disagrees with a definition. Legitimate, and theirs to argue. Two: they disagree with the method. Also legitimate, and I show the working. Three: they want a number the data doesn't support. That isn't a negotiation, and I don't do it. Most pushback that feels like the third is really the first, and treating it as bad faith is how you lose the room.

The test I apply is materiality: does the definitional choice change the decision? If it doesn't, I concede the definition and move on. If it does, the disagreement goes to whoever owns the decision, with both numbers labelled.

Concretely: agree the definition in writing before computing, compute under it, and publish the definition alongside the figure so the number traces to a stated rule. Where there's a genuine definitional split, show both.

DefinitionRevenueArgued byClears the 95m bonus trigger?
incl. intercompany98.1mrequesteryes
excl. intercompany93.9mfinanceno
published93.9mboth, in writingno

The gap is 4.2m, 4.3% of the larger figure, and it sits across the threshold, so this one is material and it escalates. Had both figures cleared 95m, I'd have taken finance's definition, said so, and spent none of my credibility on it.

When they still refuse, I move the disagreement from me-versus-them to the decision owner. The script is roughly: "I can give you either number. I can't give you one number under two definitions. Here are both, labelled, with the rule each rests on. You own which definition the decision uses; I own that the arithmetic under it is right. If you want to overrule finance's definition, make that call in writing and I'll publish it with the definition attributed." That's influence without either capitulating or stonewalling: it hands them the authority they're actually after and keeps the arithmetic out of scope.

Publishing both figures is not always right. Internally, while the decision is live, it's the honest move. Externally (a filing, a contract, a board pack) only one number goes out, and showing the alternative reads as hedging and invites cherry-picking downstream. There you pick one, document why, and don't circulate the other.

Failure modes I watch for. The number gets adjusted three times to absorb objections; by publication nobody can state its definition and it disagrees with the certified close by 9%. Detection: reconcile against the source of record (the ledger, finance's certified close) before anything ships. The other failure is mine: escalating a definitional point that doesn't change the decision, which spends the credibility I'll need on the one that does. Detection: if I can't write down how the decision flips, it isn't worth escalating.

Definitions are negotiable. Arithmetic isn't.

Curated: · Written: · Reviewed:

Q&A-47A stakeholder wants a metric you believe will be misused. What do you do?

First 90 seconds

I ask what decision the number feeds, and who acts on it. That answer decides everything else. If the metric genuinely can't support that decision, I say so once, plainly, and then ship the metric anyway with its definition attached, because a refusal doesn't stop the number, it just moves it somewhere I can't see it.

Assumptions I'd state out loud: the stakeholder owns the decision, I don't have veto over what they measure, and the metric is computable from data we already have. If any of those is false the conversation changes, if I do have veto, or if the metric needs a pipeline we don't have, that's a different discussion.

Why the misuse happens

Almost never malice. A number gets detached from three things: its denominator, its population, and its window. Once detached, it gets compared against a number computed differently, and the comparison is the misuse.

The common case is a ratio. Ratios don't add. Compute conversion per region, then average the regional rates, and you get a different figure than the global rate: the gap is traffic mix. Latency behaves the same way: the mean is not what users feel.

Worked example

A stakeholder wants "average latency" to decide whether service A is faster than service B.

Service A: 95 requests at 10 ms, 5 requests at 1000 ms. Mean = (95×10 + 5×1000) / 100 = (950 + 5000) / 100 = 59.5 ms.

Service B: 100 requests at 60 ms. Mean = 60 ms.

On the mean, A wins by 0.5 ms. On the tail, 5% of A's users wait a full second. If the decision is which service sits in front of checkout, the mean answers the wrong question and p99 answers it. I'd ship both, with the mean labelled as not decision-grade for tail-sensitive paths.

What I actually hand over

FieldExample
Metricp99 request latency, service A
Denominator / populationall requests, health checks excluded
Windowrolling 7 days, 1-minute buckets
Known exclusionscold-start requests in the 30 s after deploy
Counter-metricerror rate, so a fast failure doesn't read as fast
Decisions it can't supportcapacity planning; cross-service ranking without matched traffic mix

The one case where I don't ship it: if the misuse is a compliance or safety problem, a number that would be used to rank people or gate access, that goes up the chain rather than out the door with a footnote.

Failure mode and detection

I've watched a refusal go wrong. The stakeholder rebuilt the metric in a spreadsheet without the denominator, and the figure landed in a board pack wrong by a factor of three. Detection is cheap: a week after you decline or caveat a metric, search the dashboards and docs for it. If it's there without your definition, the caveat didn't travel.

Follow-up

Put the definition in the dashboard itself, not in a Slack thread, and set a date to re-check whether the metric is still being used for the decision it was built for. If it has drifted, that's the next conversation.

Curated: · Written: · Reviewed:

Q&A-71How do you estimate a reporting request?

Split the request into four components (definition, model, reconciliation, build) estimate each separately, and state the source-data-quality assumption explicitly, because that is where the variance lives. Then re-estimate after a short spike against the real source. An estimate quoted off the visual is wrong by a large factor and sets an expectation that makes the rest look like delay.

Three approaches, and when each is right:

Component breakdown. Estimate definition, model, reconciliation and build as separate line items. Best when the source is unfamiliar or the definitions are contested. Costs an hour of scoping; buys you a variance you can attribute.

Spike-then-estimate. Timebox one to two days to pull the source, run a tie-out query against the ledger, and count distinct entities. Best when source quality is unknown. The spike is not free, but it replaces a guess about source quality with a measured tie-out.

Reference-class. Reuse a prior request of the same shape. Best when you have tracked actuals against components before. Its weakness is that it inherits the last project's hidden reconciliation debt.

The "visual is cheap" assumption holds when the semantic layer already has conformed dimensions and the source ties to the ledger. It fails when the request is a custom chart with drill-through, a paginated report with forty fields, or a one-off analysis where the "build" component is the analysis itself rather than a chart. In those cases the visual is the work.

Failure modes and how to catch them:

  • Definitional drift. Two teams mean different things by "active customer". Catch it by writing the definition down and getting sign-off before modelling.
  • Source does not tie. Amounts differ from the ledger. Catch it with a reconciliation query in the spike, not after build.
  • Late-binding requirements. Scope grows during build. Catch it by freezing scope at the end of the spike.

A request estimated at 2 days took 14. Nine of them went to reconciling a source whose amounts did not tie to the ledger.

ComponentEstimatedActual
definition0.5 d2 d
model0.5 d2 d
reconciliation0 d9 d
build1 d1 d

The estimate had zero for reconciliation; the actual was nine days. The overrun was recorded as poor estimation rather than as a data-quality finding, which is the second failure: it hides the driver and guarantees the next estimate repeats it.

What I would do on the next one: run a one-day spike, tie the source to the ledger, write the definition down, then re-estimate the four components with the spike's numbers. Track actuals against the same four components so the reference-class approach has something real to draw on.

Curated: · Written: · Reviewed:

Q&A-72A stakeholder asks something the data cannot support. What do you say?

Say no to the number, say what would be needed to produce it, and offer the nearest question the data can actually answer. If the decision is expensive enough, propose the instrumentation that would make the original question answerable later.

The assumption I state out loud is that the stakeholder needs a decision, not a number. That reframes the conversation: instead of arguing about whether the data is good enough, we argue about what decision is pending and what evidence would change it.

What that sounds like in the room, in three sentences:

"We can't answer that from what we collect: the field doesn't record the thing you're asking about. What we can tell you is [nearest supportable question]. If this decision is worth a quarter of instrumentation, here's what we'd need to capture and roughly what it costs."

Before I say any of that, I write down what a wrong answer would look like. If the data can't rule that wrong answer out, I don't publish a number.

A churn post-mortem is the case that made this concrete. The question was why customers churned. The survey had a fixed dropdown, so the answer was an artefact of which reasons had an option, not of why people left. It drove a product change that did not move churn.

What the churn field could and could not distinguish:

ReasonDropdown optionShare reportedActual share
priceyes61%22%
missing featureyes31%18%
unrecorded reasonsno0%60%

The reported column is what the dashboard showed; the actual column is what the free-text follow-ups contained. The 60% that had no option is the part the stakeholder was asking about, and it was structurally invisible.

Two failure modes, both common.

The proxy gets quoted without the caveat. You hand over a defensible adjacent metric, someone puts it in a deck, and the caveat doesn't survive the copy-paste. Detect it by asking what decision the number will drive and whether that decision is sensitive to the distinction the data can't make. If it is, don't hand over the proxy at all.

The no gets routed around. You decline, and the stakeholder finds a number elsewhere: usually worse. Detect it by checking whether the question comes back from a different direction a week later. If it does, the answer isn't "no," it's "no, and here's the instrumentation proposal."

The trade-off is cost of instrumentation against cost of a wrong decision. A one-off question about a reversible experiment doesn't justify a new event pipeline; a pricing or retention decision that will be revisited quarterly does. Say which one you think it is, and let the stakeholder disagree: that disagreement is the useful part of the conversation.

The check I run before answering anything like this: name what the data cannot distinguish, then ask whether the proposed answer depends on that distinction. If it does, the answer is no.

Curated: · Written: · Reviewed:

Q&A-73What mistake do experienced BI developers make?

The mistake is treating the BI team's output as the deliverable. Experienced developers get very good at shipping (more dashboards, faster refreshes, more certified metrics) because that work is visible and countable, while whether a number changed a decision is neither. So the organisation optimises what it can measure, and the metric layer grows faster than the business changes.

Why it happens is organisational, not technical. BI usually sits as a cost centre with a request queue. The developer who says yes to a new metric is rewarded; the one who says "we already have that, at a different grain" is not. Nobody owns the definition of "revenue", so every stakeholder request becomes a new certified measure. The cost lands later in the data model: each measure adds a column and a join, the fact table's grain becomes ambiguous, and two teams compute the same word differently. That is reconciliation debt: it stays invisible until a board deck disagrees with the ledger.

-- finance: recognised revenue, net of refunds, by recognition month
SELECT date_trunc('month', recognised_at) AS month,
       sum(amount - refund_amount) AS revenue
FROM ledger_entries
GROUP BY 1;

-- growth: gross bookings, by signup month
SELECT date_trunc('month', created_at) AS month,
       sum(amount) AS revenue
FROM orders
GROUP BY 1;

Both queries are defensible. Finance means recognised revenue net of refunds; growth means gross bookings. Neither is wrong, and a dashboard that joins them fans out and double-counts. That is the failure mode: not a bug, a definition nobody owned.

When consolidating is the wrong move. Collapsing to a small set of measures is right when two measures answer the same decision. It is wrong when they answer different decisions or carry different legal definitions: bookings, recognised revenue and cash collected are genuinely distinct, and forcing them into one "revenue" number produces a confidently wrong figure. Regulated reporting and self-serve exploration also need breadth; you cannot pre-certify every question an analyst will ask. The criterion is the decision, not the count.

Failure modeWhat it looks likeHow to detect it
Metric drifttwo certified measures divergescheduled reconciliation between them, alert on any gap
Dashboard rotreports nobody opensaccess logs, last-viewed date
Refresh creepmodel window grows past its SLA as measures accumulatejob-duration trend, not a single failure

Illustrative example, hypothetical rather than a real team: a BI group ships a few hundred reports over two years; an audit of decision records finds only a handful cited, and the certified revenue measure was never reconciled to the ledger. The absolute numbers do not matter: the ratio does. Delivery scaled; decision coverage did not.

What I would actually do is instrument usefulness the same way you instrument latency. Log which reports appear in decision records, retire what nothing depends on, and put the recovered effort into reconciling the few measures that carry real decisions.

Curated: · Written: · Reviewed:

Q&A-90What should a new analyst be able to do in their first week?

Assumptions first. "Analyst" here means someone who answers questions from a warehouse with SQL and a BI tool, on a team that already has certified data models. A spreadsheet-and-deck analyst or a fresh-grad rotation shifts the list. I'll answer for the SQL case.

The core answer. By Friday of week one, a new analyst should be able to take a small, well-scoped question and answer it end to end without a colleague writing the query. That breaks into five things:

  1. Find the right table. Given "how many orders last month," they locate the certified orders model, not the three deprecated staging tables sitting next to it.
  2. Write the query. Joins, filters, a GROUP BY, correct date boundaries: the last one trips people up more than joins do.
  3. Check the number. Reconcile against a known-good figure (an existing dashboard, a finance total) and state the tolerance they accepted.
  4. Explain it. One paragraph to a stakeholder, caveat attached.
  5. Know who to ask. The owner of the model, whoever grants access, where definitions live.

What they should not be expected to do: own a production dashboard, design a data model, or answer an ambiguous strategic question. Those are month-two and month-six items.

CapabilityVerified by Friday when…
Find the certified sourceThey name the model and why the alternatives are wrong
Write the queryIt runs and returns a plausible row count
ReconcileThey show the known-good figure and the gap
ExplainA stakeholder repeats the answer correctly
EscalateThey know the model owner by name

A worked first week. Task: "How many active subscriptions did we have at the end of last month?"

  • Mon: files the access request; finds the certified subscriptions model; reads its definition of "active."
  • Tue: access still pending; reads column docs, finds status and ended_at.
  • Wed: access granted; writes the query, gets 41,200.
  • Thu: reconciles against the finance dashboard's 41,180; the 20-row gap traces to a timezone boundary on ended_at.
  • Fri: sends the answer with the caveat.

Five days, one correct answer, and the two-day wait is visible instead of hidden.

Trade-offs. Two schools. Ship something real in week one gives you a checkable signal and surfaces a broken access path immediately. Read and absorb builds context but produces nothing you can verify, and it hides the same broken access path until week three. Ship-something is the better default. It stops being right when the domain is genuinely gated (regulated data, no certified models, a schema nobody has documented) and then the honest week-one expectation is "can navigate and ask good questions," not "can answer."

Failure modes. The confident wrong number: the reconciliation step gets skipped, and it's detected only when a stakeholder acts on it. The access bottleneck: detected by tracking wait time, not by asking how onboarding "felt." The analyst who never asks: no questions in standup by Wednesday means they're stuck and hiding it.

How I'd check it. Instrument the path end to end, waits included, and report the median and the worst case. The waiting is part of the onboarding.

Curated: · Written: · Reviewed:

Q&A-91What do you check when reviewing a colleague's report?

First I'd ask what the report is for and who acts on it, because that sets how deep the review goes. Assuming it's a data report backed by a query or a model, not a prose memo, my answer is that I check the numbers before the chart. The defects that change a decision live in the grain, the joins, the filters and the metric definition. The visual is the least likely place for the error to be.

In order: grain and join fan-out, filters that silently exclude rows, the metric definition against the certified one, null handling, and totals against an independent calculation. Presentation last.

Grain first, because a fan-out corrupts every number downstream of it. If orders is one row per order and order_items is many per order, joining them multiplies each order's amount by its item count:

SELECT
  (SELECT COUNT(*) FROM orders)          AS orders_rows,
  COUNT(*)                               AS joined_rows,
  COUNT(DISTINCT o.id)                   AS distinct_orders
FROM orders o
JOIN order_items i ON i.order_id = o.id;

On a hypothetical fixture of 1,000 orders and 2,400 items, this returns 1,000 / 2,400 / 1,000. joined_rows far above distinct_orders is the fan-out signature, and any SUM(o.amount) taken after that join is inflated by the average items-per-order: here 2.4×, so a true total of $50,000 reports as $120,000. That is the check I run before reading a single figure.

Then the filters. A WHERE status = 'active' on a nullable column drops every NULL row silently, and the total looks plausible. Then the definition: "active user" as 30-day rolling versus calendar month is a different number on the same data, so I compare the report's definition against the certified one rather than against my intuition. Then nulls in the aggregation: AVG skips them, SUM treats them as zero, and neither is what the reader assumes.

How deep to go depends on what the number is for:

Review depthCatchesRough cost
Spot-check totalsarithmetic slips, obvious chart errors~10 min
Grain, join and filter auditfan-out, dropped rows, silent exclusions30–60 min
Full reconciliation to the certified measuredefinition drift, upstream pipeline bugshours, needs a trusted source

A one-off exploration that nobody will act on does not justify the third row. A number going to a customer, a regulator, or a pricing decision does, and so does any report whose model changed since the last run. Where the pipeline already has automated tests: uniqueness, not-null, relationship and accepted-values checks on the key columns, plus a row-count delta against the previous run: those replace most of the manual pass, and the review becomes reading the test output rather than re-deriving the query by hand.

What would change my answer: if the report is prose with cited figures, the same order applies to the figures, but I add a check that each citation matches its source. If the colleague owns the model and I don't, I ask them to walk me through the grain and the filters rather than reverse-engineering it, which is faster and catches intent mismatches a query read won't.

So the decision rule is: reconcile fully when the number drives a decision or leaves the building; spot-check when it doesn't; and let automated tests carry the middle case.

Curated: · Written: · Reviewed:

Q&A-92Where does Python belong in a reporting stack?

Core answer

Python belongs at the edges of the reporting stack: extraction and orchestration on the way in, and transformations the warehouse can't express on the way through, and it must not sit in the serving path. The rule that decides every case: the reporting layer reaches data over SQL, so anything Python computes is invisible until it is materialized as a table in the warehouse.

Assumptions: the warehouse is the system of record; the BI tool (Looker, Tableau, Power BI, Metabase) queries the warehouse or a semantic layer over JDBC/ODBC; batch refresh is acceptable.

Why the serving path is closed to Python

A BI tool issues SQL. It has no hook to execute a notebook, a pandas script, or a FastAPI handler. So a metric defined in Python is reachable only if a job writes its result to a table first. That is the whole mechanism behind drift: the notebook and the semantic layer both compute "active accounts", the semantic layer's definition changes, the notebook's does not, and both numbers are now in circulation. The semantic layer (dbt Semantic Layer, Cube, LookML) avoids this because a metric is defined once in SQL/YAML and compiled to every consumer, so one edit propagates.

Where Python earns its place

LayerPython's roleWhat must hold
IngestionAPI pulls, dlt/Meltano/Singer taps, Airflow/Dagster/Prefect tasksIdempotent; lands raw rows in the warehouse
TransformationML scoring, forecasting, NLP, anything SQL can't expressOutput materialized to a table with a declared grain and refresh
Semantic layernoneDefinitions live here
Servingembedded-analytics API, precomputed tablesNever compute a certified metric at query time

Trade-offs

dbt (SQL + Jinja) is the default for transformation: versioned, tested, warehouse-native, and reachable by BI. Reach for Python when the logic isn't expressible in SQL: a gradient-boosted propensity score, a Prophet forecast, entity resolution. Reach for Spark/PySpark when the data exceeds one worker's memory. The criterion is not "is Python nicer" but "can SQL express it, and does the result need to be reachable by the reporting layer".

Failure modes

Drift, as above: detect by counting metric definitions outside the semantic layer and diffing the two figures on a schedule. Memory: pandas holds the whole frame in RAM, so a 4 GB worker handles roughly 10–20M rows of a few columns (illustrative); past that you OOM, and the fix is Polars streaming, Spark, or pushing the aggregation into SQL. Non-determinism: an unseeded model or a timezone-naive timestamp gives a different number each run. Silent staleness: the Python job fails, the table keeps yesterday's rows, and the dashboard looks fine. Alert on table freshness, not just job exit code.

Materializing a Python result

# Python 3.12, pandas 2.2, snowflake-connector-python 3.12
import pandas as pd
from snowflake.connector.pandas_tools import write_pandas

df = pd.read_sql("select account_id, session_id, ts from raw.events", conn)
features = df.groupby("account_id").agg(
    sessions_28d=("session_id", "nunique"),
    last_seen=("ts", "max"),
).reset_index()
write_pandas(conn, features, "ACCOUNT_FEATURES", auto_create_table=True)

The BI tool now joins ACCOUNT_FEATURES like any other table. The Python step is a producer, not a definition.

What would change the decision

If the metric is genuinely model output that changes per request (a live recommendation), Python in the serving path is correct, but then it's an application, not a reporting stack, and the number is not certified.

Curated: · Written: · Reviewed:

Q&A-93An analysis in pandas runs out of memory on a year of data. What now?

Clarify first: does the answer need row-level detail, or a reporting-grain result? If it is a group-by, a join to a small dimension, or a filter-then-aggregate, the warehouse should do it and pandas should receive hundreds or thousands of rows, not hundreds of millions. That decision resolves most of these incidents on its own.

Why pandas runs out. A DataFrame is a column store held entirely in RAM. Numeric columns cost their width per row (int64 = 8 bytes, float64 = 8) plus block overhead; object columns cost an 8-byte pointer per row plus a separate Python object per value: a 20-character string is roughly 60-70 bytes once you count the object header and the buffer. A 180M-row frame with a couple of string columns lands in the tens of GB before any group-by allocates its output.

The decision rule. Compare rows returned by the query with rows needed for the result. If they differ by orders of magnitude, aggregate in SQL.

Worked example (illustrative figures):

ApproachRows transferredPeak memoryDuration
pull 180M rows, group in pandas180,000,000~60 GBOOM after ~20 min
GROUP BY in warehouse, pull result400~2 MB4 s

If the frame is genuinely needed (iterative modelling, per-row feature work) reduce it instead of moving it:

  • usecols to read only the columns the analysis touches.
  • Downcast: float32/int32 where range and precision allow. Check ranges first; astype from int64 to int32 wraps silently.
  • category dtype for low-cardinality strings. It is a trap on high-cardinality columns: the category index plus the dictionary can exceed the object column it replaced.
  • chunksize with an incremental aggregate rather than concatenating chunks.
import pandas as pd

totals = None
for chunk in pd.read_csv(
    "events.csv",
    usecols=["account_id", "amount"],
    dtype={"account_id": "int32", "amount": "float32"},
    chunksize=1_000_000,
):
    part = chunk.groupby("account_id", as_index=False)["amount"].sum()
    totals = part if totals is None else (
        pd.concat([totals, part])
          .groupby("account_id", as_index=False)["amount"].sum()
    )

Each chunk reduces to at most its distinct accounts before merging, so peak memory tracks group cardinality, not row count.

Failure modes to name. Chunked reads that end in pd.concat(chunks) are the same OOM with extra steps. A chunked group-by over a high-cardinality key still grows totals linearly. category on a near-unique ID column inflates memory. Downcasting without a range check corrupts sums.

Verify. df.memory_usage(deep=True).sum() before and after; log rows returned against rows needed. If the ratio is large, the aggregation belongs in the warehouse.

Curated: · Written: · Reviewed:

Q&A-94An enrichment step joins 2 million rows against a 50,000-row lookup and takes minutes. Why?

The short answer

The step is scanning the 50,000-row lookup once per input row. 2M × 50k is 1e11 comparisons. Build the lookup into a hash map once and probe it: 2.05M hash operations. Same result, seconds instead of minutes.

What the slow version actually does

The usual shape is a per-row function that filters the lookup:

# pandas 2.x
lookup = pd.read_parquet("lookup.parquet")   # 50_000 rows
df = pd.read_parquet("events.parquet")       # 2_000_000 rows

def enrich(row):
    hit = lookup[lookup["sku"] == row["sku"]]   # full scan of the lookup
    return hit["category"].iloc[0] if len(hit) else None

df["category"] = df.apply(enrich, axis=1)

Each call builds a 50,000-element boolean mask, allocates it, and indexes the frame with it. 2M calls means 1e11 byte comparisons and 2M allocations of ~50 KB: roughly 100 GB of memory traffic, before you count the Python-level per-row overhead. That is the minutes.

The fix

df = df.merge(lookup[["sku", "category"]], on="sku", how="left",
              validate="many_to_one")

or, if you want the map explicitly:

assert lookup["sku"].is_unique          # a dict keeps the last duplicate silently
mapping = dict(zip(lookup["sku"], lookup["category"]))
df["category"] = df["sku"].map(mapping)

merge builds a hash table on the smaller side and probes it once per row. Series.map with a dict is a C-level hash lookup per row. Both are O(n + m).

MethodWork at 2M × 50kNotes
per-row filter1e11 comparisons, ~100 GB allocatedwhat you have
dict / Series.map2.05e6 hash opsneeds hashable, unique keys
merge (hash join)2.05e6 hash opsfans out on duplicate keys
sort-merge~4.3e7 comparisonssurvives a build side that won't fit

When a hash map is the wrong answer

  • Lookup doesn't fit in memory. A hash join builds the whole build side before probing; at 40 GB you spill or OOM. Partition both sides by key and join partition-wise, or sort-merge with an external sort. In Spark this is the default once the lookup exceeds spark.sql.autoBroadcastJoinThreshold (10 MiB in Spark 3.x); below it, the lookup is broadcast to every partition instead.
  • Keys aren't hashable: lists, dicts, arrays. Normalize to a tuple or a string first, or sort-merge on a sortable key.
  • Duplicate keys in the lookup. A hash join fans out: 2M probe rows × 3 matches = 6M output rows. A dict keeps the last one and says nothing. Decide the semantics (fan out, first, last, or aggregate) before you pick the structure.
  • Range or as-of joins (nearest timestamp, time window). Hashing doesn't apply; use merge_asof or a sort-merge with a range predicate.
  • Skew. One key holding 1M probe rows and 500k build rows puts all the work in one partition. Check per-partition row counts; salt the key or broadcast the small side.

Failure modes and how you see them

  • Nested loop with a full scan: EXPLAIN ANALYZE shows the lookup as the inner side with loops=2000000. A nested loop with an index on the join key is fine: 2M index probes at ~1 µs is about 2 s. The full scan is the problem.
  • Build-side spill: the hash join spills to disk. Watch spill bytes and peak RSS, not just wall time.
  • Fan-out: output row count exceeds input. validate="many_to_one" raises instead of letting it through.
  • Null keys: SQL hash joins drop them, .map gives NaN. Count unmatched rows on both paths and compare.

Confirming it

Time the step at two input sizes. Doubling the row count should roughly double the time for a hash join; if it quadruples, something is still scanning per row.

Curated: · Written: · Reviewed:

Q&A-95An export needs 20 million rows sorted. Where should that happen?

Sort it in the warehouse, stream the ordered rows out, and first check whether the recipient needs 20 million rows at all. Those are three separate decisions, and the last one usually dominates the other two.

Why the warehouse. A warehouse sort is parallel and spills to disk; a client sort is one process holding the whole result in RAM. 20M rows at roughly 1.2 KB each is about 24 GB of payload: that has to cross the network before the client can compare a single pair of rows, and then it has to fit in memory alongside whatever else the process is doing. The warehouse already has the rows. It pays a shuffle and a merge, not a transfer.

Mechanism. A distributed sort range-partitions on the sort key, shuffles each partition to a worker, sorts locally with spill-to-disk when a partition exceeds its memory grant, then merges the sorted runs. Cost scales with rows and partition count, not with the client's RAM. A client sort is a comparison sort over a fully materialised array: O(n log n) comparisons, all of it in one address space, with no spill path.

When not to. If the destination will re-sort anyway (loading into an index, a downstream job with its own ORDER BY) sorting twice buys nothing; export unordered and let the consumer sort. If the warehouse bills per byte scanned and the sort key isn't in the table's sort order, you pay a full scan plus a shuffle for an ordering nobody reads.

Failure modes. Client-side: OOM kill, or swap thrash that turns 40 minutes into hours; detectable as RSS climbing to the container limit. Warehouse-side: the sort spills and the query hits a statement timeout; detectable in the query profile as spill bytes and shuffle wait. ORDER BY with no LIMIT on a 20M-row result is the common trigger for both.

Worked example. Figures illustrative, not measured:

ApproachDurationMemoryRows used
client-side sort40 min24 GB500
warehouse sort, full export3 minstreamed500
warehouse sort, top 5002 snegligible500

The recipient used 500 rows. That is the finding: the export was sized for the producer's convenience, not the consumer's need.

If the full set is genuinely needed, stream it ordered and page with keyset pagination rather than OFFSET (PostgreSQL 16):

SELECT id, created_at, payload
FROM events
WHERE (created_at, id) > (:last_created_at, :last_id)
ORDER BY created_at, id
LIMIT 10000;

OFFSET re-scans and re-sorts the skipped prefix on every page; the row comparison seeks straight to the resume point.

What I'd ask first. What does the recipient do with the file? If the answer is "look at the top 500", the fix is ORDER BY ... LIMIT 500 in the warehouse and the 20M-row export never happens.

Curated: · Written: · Reviewed:

Q&A-98How would you show the reporting function is worth its cost?

I'd put a number on both sides, fully loaded cost and the value it produces, show the ratio, and then say plainly what the ratio can't capture. Time saved almost never covers the cost on its own, so the argument has to rest on named decisions.

Cost, fully loaded. Take a four-analyst reporting team: $180k each fully loaded is $720k. Add warehouse compute attributable to reporting, say $150k, and 300 BI seats at $200, $60k. That's roughly $930k a year. The number people forget is the maintenance half: pipeline upkeep and metric reconciliation usually run 30-40% of analyst time, so it's already inside the $720k, not a separate line.

Value, two components. The measurable one is time to answer. If 200 recurring questions a month drop from a 4-hour manual assembly to 20 minutes self-serve, that's 200 × 3.67h = 734 hours a month, about 8,800 hours a year. At a $75 loaded hourly rate that's $660k: roughly 0.7× cost. The second component is decisions the reporting supported, and that's the part that closes the gap.

LineAnnualNote
Team + compute + licences$930kfully loaded
Time saved on recurring questions$660k8,800 h at $75/h
Decisions supportednot monetisedmust be named individually

How to measure the decision half: three options, different failure modes.

ApproachWhat it measuresFailure mode
Chargebackcost allocated to consuming teamsdrives shadow spreadsheets; teams stop asking
Attribution survey"what would you have done without it"flattery bias, inflates value
Decision lognamed decisions citing a reportundercounts; people don't cite

Deciding criteria: if reporting spend exceeds 1-2% of the consuming unit's budget, chargeback, because demand discipline matters more than a clean number. If you're defending the budget to a CFO, run the decision log plus time saved, and accept the undercount.

When it isn't worth the cost. If the ten most expensive reports have no named decision owner and no citation in 90 days, and time saved is below the fully loaded cost, the function isn't worth its cost as configured. The answer is to cut scope (retire the assets, keep the two or three reports that decisions actually cite) not to defend the whole portfolio.

Failure modes to avoid. Page views and automated refreshes are not value; a dashboard opened daily by a scheduled job is usage, not a decision. Attribution surveys inflate because nobody says "I'd have guessed." Chargeback, run badly, pushes teams back to spreadsheets, which costs more than the reporting did.

The defensible answer is a ratio with the assumptions stated, a decision log with names in it, and a stated threshold below which you'd cut the function rather than argue for it.

Curated: · Written: · Reviewed:

Q&A-99Your analysis says something implausible. What do you do?

Treat it as a defect until you have tried to kill it. In most pipelines an implausible jump is a duplicate load, a grain change, a timezone boundary or a partial day, not a discovery. The first hour goes into falsification, not into writing it up, and the stakeholder gets told early that the number is under verification with an ETA, so nobody builds on it in the meantime.

Assumptions I would state out loud: the figure is implausible against a known baseline, not merely surprising to me, and I can reach both the raw events and the pipeline's run history.

Order the checks by cost.

  1. Recompute from raw events through a second, independent path: different engine or different base table, no shared CTE. If the two disagree, the defect is in the transform, not the world.
  2. Check grain and join fan-out. A join that multiplies rows inflates every sum downstream:
-- Postgres 16
SELECT order_id, COUNT(*) AS n
FROM orders
WHERE order_date = DATE '2024-03-04'
GROUP BY order_id
HAVING COUNT(*) > 1;
  1. Read the run history around the date the effect starts: backfills, re-runs, schema migrations, upstream vendor changes.
  2. Check the definition: rate versus count, partial final day, timezone of the date column.

Worked example, figures illustrative. Daily orders sit at 12,000 ± 400. On 2024-03-04 the table shows 16,800, up 40%. The run history shows a backfill on 2024-03-05 that re-processed 2024-03-01 through 2024-03-07, and that batch contained 4,800 orders dated 03-04. 12,000 + 4,800 = 16,800, exactly. The excess equals the re-run batch to the row, so "duplicate load" explains the number with no free parameters. "Genuine growth" would have to explain why growth arrived as a single-day step whose size is precisely the size of a batch.

HypothesisDiscriminating testVerdict
Duplicate loadexcess equals re-run batch row count; dedupe by event id removes itconfirmed
Genuine growthsurvives dedupe and appears in the independent sourceno
Upstream or definition changeschema or definition change dated to the same dayno

Failure modes that actually bite here. Deduping on the wrong key, order_id when the duplicate is a distinct event_id for the same order, hides real duplicates and makes the defect look like growth. A timezone shift moves rows across a day boundary and reads as a spike on one day and a dip on the next. A partial final day makes the last bar look like a collapse. A denominator change turns a flat rate into a rising count. Each of these is detected by the same move: recompute the metric from raw events under the alternative definition and see whether the effect survives.

If it survives all of that, present it as a finding with the checks listed, the effect size with an interval, and the single observation that would overturn it. Say what you ruled out: that is the part a reviewer can audit, and it is what separates a finding from a number that happened to render.

Curated: · Written: · Reviewed:

Q&A-100How would you know a reporting layer is in good shape?

A reporting layer is in good shape when someone can pull a number, put it in a deck, and defend it in the meeting without a follow-up email. That's the bar. It breaks into three testable properties: the number reconciles to the system of record, exactly one definition of it is in circulation, and the consumer can see how stale it is.

Everything else (dashboard count, query volume, adoption) measures activity, not health.

The four measures I'd actually track

MeasureHow to computeBad reading
Reconciliation coveragecertified measures with a passing tie-out to source of record / all certified measuresany certified measure with no tie-out
Definitions per metricdistinct definitions in the metric registry, including shadow copies in spreadsheets and BI extractsmore than one for a metric that drives a decision
Certified consumption sharequeries against certified assets / all queries against the layerfalling while total queries rise
Orphaned assetsassets with no query in 90 days and no ownergrowing

The tie-out is the one that matters most and the one teams skip. It's a query, not a process: sum the reporting layer's revenue by day for the last 30 days and compare it to the ledger's sum for the same window. A difference means the layer's grain, filters, or late-arriving handling diverges from the source. Run it on a schedule and store the result, so "reconciled" is a timestamped fact rather than a claim.

Where this model misleads

Reconciliation can be green while the layer is useless. A tie-out at month grain passes even if the daily numbers are wrong, and a tie-out on one measure says nothing about the other eleven. Certified consumption share is gameable: de-certify the assets people actually use and the ratio improves. Definitions-per-metric is gameable the same way: rename the second definition and the count drops while the spreadsheet lives on. Orphaned asset count punishes a layer that legitimately holds slow-moving regulatory extracts.

The alternative, and when to prefer it

The other credible model is consumer-centric: ticket volume to the data team, median time from "number looks wrong" to resolution, and how often a published number is revised after publication. It's cheaper to run (no registry, no tie-out) and it catches trust problems reconciliation never will, because a layer can reconcile perfectly and still be unusable at 9am on the first of the month.

Pick by blast radius. One team, one source, internal decisions: consumer metrics are enough, and the registry is overhead. The layer feeds regulatory filings, pricing, or exec comp: reconciliation coverage is non-negotiable and consumer metrics are secondary. Most layers are both, so run reconciliation on the certified subset and consumer metrics on everything.

Worked example (hypothetical)

A team ships a warehouse for a subscription business. Year one they track delivery: 40 dashboards, 300 daily users. Year two they add the four measures and get:

MeasureYear 1Year 2
certified measures reconciled0 of 1212 of 12
definitions per key metricup to 61
consumption from certified assets41%84%
orphaned assets61040

The interesting column is the first: nothing was reconciled in year one and nobody noticed, because delivery was the only thing measured. The 610 orphaned assets are the residue of that.

Failure modes to watch

  • Tie-out passes at month grain, fails at day grain: late-arriving events land in the wrong period.
  • Freshness shown as pipeline success time rather than max event time in the table; consumers think they're current when they're a day behind.
  • A metric is certified but its owner left; no one can explain a change.
  • Two definitions both certified after a migration, and the dashboard silently picks one.

Report the four measures monthly and work the worst one. If reconciliation coverage is the worst, stop everything else.

Curated: · Written: · Reviewed:

Q&A-89Why put dashboards and models in version control?

The short answer

A dashboard is a claim about the business, and a claim you can't reproduce is one you can't defend. Version control is what lets you answer three questions on demand: what changed, who approved it, and what did this number mean on the day we published it. So model SQL, metric definitions and dashboard definitions live in Git, changes go through a pull request, and every published figure traces to a commit or tag. Edits made inside the BI tool are exceptions that get back-ported the same day, not the normal path.

What it buys, concretely

A one-line filter change becomes a diff someone can review. A bad deploy becomes git revert. A number in last quarter's board deck becomes a tag you can check out and rebuild. None of that exists when the definition lives in a calculated-field dialog.

The definition is the artefact

-- models/marts/fct_orders.sql — dbt 1.7, Postgres
select
    order_id,
    customer_id,
    date_trunc('day', created_at) as order_date,
    case when status = 'refunded' then 0 else amount_cents end as net_revenue_cents
from {{ ref('stg_orders') }}
# models/marts/schema.yml
models:
  - name: fct_orders
    columns:
      - name: net_revenue_cents
        tests:
          - not_null

That case is the definition of net revenue. In Git it's a line in a pull request. In the tool it's a click with no history.

Reproducing a published figure

git checkout v2024-Q1
dbt build --select +fct_orders
# re-run the dashboard's query against the rebuilt table

If the number comes back identical, the figure is defensible. If it doesn't, you found the drift before an exec did.

DiffReviewRollbackReproduce last quarter
edit in BI toolnonenonemanual, if rememberedno
definitions in Gitline-levelPRgit revertcheckout tag, rebuild

Trade-offs

Binary workbook formats (.twbx, .pbix) version-control but don't diff, so you get history without reviewable changes; prefer text formats (.twb XML, Power BI's .pbip/TMDL) where the tool offers them. Dashboard layout churn generates noise, so version the definitions and the queries, not every pixel move. A shared semantic model produces merge conflicts, and CI that rebuilds every model on every PR costs real minutes. And if tool-side edits aren't back-ported, the next deploy silently reverts them: the two-sources-of-truth problem.

Failure modes

Silent definitional drift: someone changes a filter in the tool, the number moves, nobody knows why. Catch it with a reconciliation test against the source of record and a volume test that fails the build when row counts move outside a band. Orphaned charts: a column rename breaks a chart no test covers. Parse the BI tool's API for field references and fail CI on missing ones. Unreproducible history: if you can't check out a tag and rebuild, you have a backup, not version control.

How I'd verify it

Take a figure published three months ago, check out the tag from that week, rebuild, compare. That's the test.

Curated: · Written: · Reviewed: