Top 100 Business Intelligence Developer Interview Questions and Answers

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 grain	Rows	Order count reported	Correct
one per order	41,000	41,000	yes
actual: one per line	98,400	98,400	no, 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.

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.

Aggregation	Value	Correct for
sum over 12 months	14.2m	nothing
period end	1.2m	balance at year end
average of month ends	1.18m	average 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.

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

A ratio is not additive and its total is not the average of the parts. The correct overall figure divides the total numerator by the total denominator, which lands wherever the segment volumes put it rather than midway between the rates.

Concretely, define the ratio as a division of two additive measures rather than storing a ratio column and aggregating it, so the total is computed at whatever level the user views it.

The reason for that specificity is a failure I have seen: A report averaged segment conversion rates to 22.5 percent; the volume-weighted figure was 6.75 percent, and a campaign budget was set against the wrong one.

Where the weighting lands.

Segment	Sessions	Conversions	Rate
A	38,000	1,900	5.0%
B	2,000	800	40.0%
total	40,000	2,700	6.75%
average of rates	—	—	22.5%, wrong

I would not consider it settled without evidence: Compute the total from the underlying numerator and denominator on a fixture where the segments deliberately differ in size.

The average of two rates is not a rate.

My answer to distinct counts across segments begins with the definition, agreed before anything is built.

A distinct count is non-additive because an entity can appear in more than one group. Adding regional counts double-counts every customer active in both, and the error grows with how much the groups overlap.

Concretely, compute the distinct count at the level being reported rather than aggregating a stored count, and where performance forces pre-aggregation, use a sketch that supports union rather than a plain count.

The reason for that specificity is a failure I have seen: Regional distinct customer counts summed to 128,000 against an actual distinct total of 96,000; the 32,000 difference was customers who transacted in more than one region.

Overlap between regions.

Region	Distinct customers
north	71,000
south	57,000
sum	128,000
actual distinct	96,000

I would not consider it settled without evidence: Compare the summed segment counts against a distinct count over the union, and report the overlap explicitly.

Distinct counts do not add unless the groups are disjoint.

I would treat fan-out from joining facts at different grains as a claim about the business that has to reconcile to a source of record.

Joining a header-grain fact to a line-grain fact repeats the header's values once per line, so any measure summed after the join is multiplied by the line count. The result varies with an apparently unrelated dimension, which is why it is usually found by finance rather than by a test.

Concretely, keep facts at different grains in separate tables joined through conformed dimensions rather than to each other, and where a combined view is required, aggregate to a common grain before joining.

The reason for that specificity is a failure I have seen: A shipping cost of 10 recorded once per order became 30 on three-line orders; the reported total was 41 percent high and varied by product mix, which made it look like a pricing anomaly.

Shipping cost through the join.

Order	Lines	True cost	After join
A	1	10	10
B	3	10	30
C	5	10	50
total		30	90

I would not consider it settled without evidence: Compare a header-level measure computed before and after the join, and assert they are equal.

A join at the wrong grain multiplies rather than combines.

The useful question for conformed dimensions is what the total does when the segments are unequal.

Two facts can only be compared through a dimension they share. Separate customer dimensions with different keys and different definitions of an active customer mean no query can combine them honestly, however the joins are written.

Concretely, agree the key, the hierarchy, and the change behaviour once, publish it as a conformed dimension, and treat a request for a second version as a definitional disagreement to resolve rather than a table to build.

The reason for that specificity is a failure I have seen: Sales counted a customer from first order and support from first ticket; a combined retention figure differed by 14 percentage points depending on which dimension the analyst happened to join to.

Same entity, two definitions.

Definition	Customers	Retention reported
active from first order	96,000	62%
active from first ticket	74,000	76%
conformed	96,000	62%

I would not consider it settled without evidence: Reconcile the entity counts from each dimension and require the difference to be explained before either is certified.

Two customer tables mean two different companies.

I would settle slowly changing dimensions against a hand calculation on a small fixture before trusting the model.

The answer depends on whether the report means the region the customer is in now or the region they were in when the sale happened, and both are legitimate. Overwriting the value silently chooses the first and makes historical reports change retrospectively.

Concretely, decide the type per attribute before the first load: overwrite where only the current value matters, keep a versioned history with effective dates where the historical value matters, and expose both where the business genuinely asks both questions.

The reason for that specificity is a failure I have seen: An overwrite moved 3 years of a customer's sales into their new region; the prior year's regional report changed after it had been presented, and the discrepancy was attributed to a data error.

Last year's regional total after a move.

Handling	North last year	South last year
overwrite	4.1m	6.3m
versioned history	5.4m	5.0m
as originally reported	5.4m	5.0m

I would not consider it settled without evidence: Re-run a prior period's report after a dimension change and confirm the figures are unchanged where they should be.

An overwrite rewrites history without saying so.

The judgement in star schema versus normalised model for reporting is which measure is certified and who owns its definition.

A normalised model optimises for write correctness and a star schema optimises for query simplicity and predictable joins. The reporting layer is read by people writing their own queries, and every additional join is a chance to produce a wrong number.

Concretely, model facts and conformed dimensions, keep the join paths shallow and unambiguous, and accept the storage and update cost as the price of a model that non-specialists can query without producing fan-out.

The reason for that specificity is a failure I have seen: A reporting layer exposed the normalised source directly; analysts wrote 6-table joins and 3 of 8 sampled reports contained a fan-out error nobody had detected.

Error rate by model shape.

Model	Median joins per query	Sampled reports with errors
normalised source	6	3 of 8
star schema	2	0 of 8

I would not consider it settled without evidence: Sample analyst-written queries against the model and count how many produce an incorrect total.

The model is a user interface for people writing joins.

Where candidates lose the interview on metric definition ownership is explaining a wrong total as a tool quirk.

The disagreement is definitional rather than technical: one includes refunds, the other does not, and both queries are correct for their own definition. Fixing the query without agreeing the definition just moves which number is wrong.

Concretely, give each certified metric a named owner, a written definition including exclusions, and a change process, and publish it where the number is consumed rather than in a separate document.

The reason for that specificity is a failure I have seen: Two revenue figures differed by 4.5 percent for 8 months; the reconciliation meeting established that one included intercompany sales, which neither definition had stated.

Where the 4.5 percent went.

Component	Team A	Team B
gross sales	102.0m	102.0m
less refunds	-3.9m	-3.9m
less intercompany	-4.2m	included
reported	93.9m	98.1m

I would not consider it settled without evidence: Reconcile the two figures to a stated definitional difference before changing either query.

A disputed number is usually two definitions, not one bug.

I would answer certified versus exploratory assets by separating what the data measured from what the chart implied.

Uniform governance either strangles exploration or fails to protect the numbers that matter. A tiered model — certified, team-owned, and personal — lets each have the right level of control, provided the tier is visible at the point the number is used.

Concretely, label each asset with its tier so the label travels into an export or a screenshot, give certified assets an owner and tests, and measure the share of consumption that comes from certified assets.

The reason for that specificity is a failure I have seen: An uncertified exploratory chart was pasted into a board pack and treated as authoritative; nothing in the image distinguished it from a certified one.

Consumption by tier.

Tier	Assets	Share of views	Owner
certified	40	71%	named
team-owned	310	24%	team
personal	2,400	5%	none

I would not consider it settled without evidence: Check that the tier is visible on the exported artefact, not only in the tool.

The label has to survive the screenshot.

The engineering content of idempotent loads is the additivity rule and the reconciliation, not the visual.

Every reporting pipeline is re-run — after failure, after a correction, during a backfill — so a load that appends unconditionally is wrong by construction. The error is silent because row counts look plausible and only the totals are wrong.

Concretely, replace a well-defined partition or merge on a stable business key so that running an interval twice gives the same result as running it once, and test that property rather than assuming it.

The reason for that specificity is a failure I have seen: A re-run after a transient failure appended a second copy of one day; daily revenue for that date read 2x and was used in a month-on-month comparison before anyone noticed.

Totals after a re-run.

Load strategy	After 1 run	After 2 runs
append	1.42m	2.84m
partition replace	1.42m	1.42m
merge on key	1.42m	1.42m

I would not consider it settled without evidence: Run the same interval twice in a test environment and assert the resulting totals are unchanged.

Every pipeline is re-run, so design for the second run.

Before publishing late-arriving data I would write down what a wrong number would look like here.

A pipeline reading only rows newer than a watermark will never see a correction to an older row, so the reporting layer diverges from the source silently and permanently. Whether corrections are possible is a property of each source that has to be established rather than assumed.

Concretely, use change capture or a bounded reprocessing window for sources that amend history, record for each load which interval it covered, and reconcile periodically against the source rather than trusting the incremental path.

The reason for that specificity is a failure I have seen: A watermark-based load missed 4,100 corrections over 6 months; the warehouse and the source differed by 0.8 percent on revenue and neither side could explain it.

Divergence over six months.

Month	Corrections in source	Captured	Cumulative drift
1	620	0	0.1%
3	1,900	0	0.4%
6	4,100	0	0.8%

I would not consider it settled without evidence: Reconcile a closed period against the source on a schedule and investigate any drift.

An incremental load sees new rows, not corrected ones.

The first thing I would pin down about data quality gates versus observations is which decision the number is meant to support.

A check that raises a dashboard tile is an observation someone may read; a check that blocks publication is a control. Row-count and freshness checks catch failed loads, while uniqueness, referential integrity, and reconciliation checks catch the loads that succeed and are wrong — the dangerous ones.

Concretely, decide per check whether failure blocks or warns, block on the checks that indicate incorrectness rather than lateness, and make the blocked state visible to consumers rather than silently serving stale data.

The reason for that specificity is a failure I have seen: Quality checks wrote to a monitoring dashboard nobody watched; a referential integrity failure published a report with 8 percent of sales unattributed to any customer for 3 weeks.

Check outcome by design.

Check	Failure mode caught	Blocks publication
freshness	late load	yes
row count	failed load	yes
referential integrity	wrong load	should
reconciliation to source	wrong load	should

I would not consider it settled without evidence: Break a check deliberately and confirm publication is prevented rather than annotated.

A check that cannot stop publication is a note.

I would start reconciliation to a source of record from the grain, because most wrong totals resolve to one.

Comparing one report against another only establishes that they agree, which they will if they share a defect. Correctness is established against a source of record — the ledger, the billing system — through a reconciliation that is expected to tie exactly or to a stated difference.

Concretely, reconcile the certified measures to the source of record on a schedule, state the permitted differences and their causes, and treat an unexplained difference as a blocking defect rather than as a rounding note.

The reason for that specificity is a failure I have seen: Two dashboards agreed for a year and both understated revenue by 1.2 percent because they shared a filter excluding a channel; nothing was reconciled to the ledger.

Reconciliation to the ledger.

Item	Warehouse	Ledger	Explained
gross sales	100.8m	102.0m	no
channel excluded	—	1.2m	cause
after fix	102.0m	102.0m	tie

I would not consider it settled without evidence: Tie the certified measure to the source of record with every difference explained, monthly.

Two reports agreeing is not evidence either is right.

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

A number alone is not interpretable; a comparison makes it a finding. The right comparison comes from the decision — against plan, against the same period last year, against a control — and defaulting to the previous period reports a crisis every January in a seasonal business.

Concretely, choose the comparison from the decision, label it on the visual so a reader who was not in the design meeting knows what they are seeing, and keep it stable so the trend is about the business rather than about the baseline.

The reason for that specificity is a failure I have seen: A month-on-month comparison showed a 34 percent fall every January; the year-on-year figure for the same month was up 8 percent, and three quarters of executive attention went to a seasonal pattern.

January, three comparisons.

Comparison	Change	Reads as
month on month	-34%	crisis
year on year	+8%	growth
against plan	-2%	on track

I would not consider it settled without evidence: Check the comparison against a full year of history to confirm it does not encode a seasonal artefact as a finding.

The comparison is the finding, so choose it deliberately.

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.

Axis	Bar A	Bar B	Apparent ratio
0 to 100	97.4	98.6	1.01x
94 to 100	3.4	4.6	1.35x
97 to 99	0.4	1.6	4.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.

I would treat colour as the only encoding as a claim about the business that has to reconcile to a source of record.

Colour alone excludes readers with colour vision deficiency, which is roughly 8 percent of men, and it stops working entirely when the dashboard is printed or screenshotted in greyscale — which is how most dashboards actually travel.

Concretely, pair colour with a shape, a label, or a position so the encoding survives both, choose palettes that remain distinguishable under common deficiencies, and test by viewing the dashboard in greyscale.

The reason for that specificity is a failure I have seen: A red-amber-green dashboard was printed for a board meeting; every status looked identical in greyscale and the escalation the design intended did not happen.

Status readable by rendering.

Encoding	Colour vision deficiency	Greyscale print	Renderings readable of 2
colour only	no	no	0
colour + icon	yes	yes	2
colour + label	yes	yes	2

I would not consider it settled without evidence: View the dashboard in greyscale and under a colour-deficiency simulation and confirm the status is still readable.

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

The useful question for precision that the data does not support is what the total does when the segments are unequal.

The number of digits shown implies a precision, and on a small sample that precision does not exist. A rate computed from 40 sessions moves by 2.5 points on a single extra conversion, so a decimal place is a claim the data cannot support.

Concretely, round to the precision the sample supports, show the denominator alongside the rate, and show an interval where the audience will compare it against another figure.

The reason for that specificity is a failure I have seen: Segment conversion rates were reported to one decimal place on samples between 30 and 40,000; a 0.4-point difference on a 34-session segment was treated as a finding and drove a campaign change.

One extra conversion.

Sessions	Conversions	Rate	Rate at +1
34	2	5.9%	8.8%
3,400	200	5.9%	5.9%
34,000	2,000	5.9%	5.9%

I would not consider it settled without evidence: Show the denominator with every rate and suppress decimal places below a stated sample size.

Digits shown are a claim about precision.

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.

Month	Rows loaded	Revenue in fact	Row LAG saw	Growth shown	If February had loaded (147)
Jan	1	100	—	—	—
Feb	0	—	—	—	—
Mar	1	138	Jan (100)	+38.0%	−6.1%
Apr	1	130	Mar (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.

The judgement in correlation presented as cause is which measure is certified and who owns its definition.

A dual axis invites a causal reading by placing two arbitrary scales side by side, and any two growing series can be made to look coupled by choosing the scales. Co-movement is consistent with cause, with reverse cause, with a common driver, and with coincidence.

Concretely, avoid dual axes where the audience will read causation, state the alternative explanations you have and have not ruled out, and reserve causal language for cases with an experiment or a credible design behind them.

The reason for that specificity is a failure I have seen: A dual-axis chart of marketing spend and revenue was used to justify a budget increase; both had grown with headcount, and a later holdout test found the incremental effect was a fraction of what the chart implied.

What the chart could not distinguish.

Explanation	Consistent with chart	Ruled out	Effect found in later holdout
spend drives revenue	yes	no	0.3x the chart implied
revenue drives spend	yes	no	not tested
headcount drives both	yes	no	not tested

I would not consider it settled without evidence: Name the confounder you would need to rule out, and say whether you have.

Two lines moving together is a question, not an answer.

Where candidates lose the interview on incremental refresh and partitioning is explaining a wrong total as a tool quirk.

A full refresh reprocesses history that has not changed, so its cost grows with the archive rather than with the day's activity. Partitioning by time and refreshing only the open partitions makes the cost proportional to what actually moved.

Concretely, partition on the date the business reports by, refresh a bounded recent window to absorb late arrivals, and refresh history only on a deliberate backfill rather than on every run.

The reason for that specificity is a failure I have seen: A 4-hour full refresh over 6 years of history reprocessed 2,190 days to pick up changes in 3; hourly reporting was declared impossible until the model was partitioned.

Refresh cost by strategy.

Strategy	Days processed	Duration
full refresh	2,190	4 h
30-day window	30	4 min
3-day window	3	40 s

I would not consider it settled without evidence: Measure refresh time against the changed window rather than the archive size, and confirm it is proportional.

Refresh what changed, not what exists.

I would answer aggregate tables and their staleness by separating what the data measured from what the chart implied.

An aggregate is a cache of a query result, so it can disagree with the detail it summarises whenever the underlying data changes and the aggregate has not been rebuilt. A user comparing the two sees two different numbers with no indication which is current.

Concretely, rebuild aggregates in the same transaction or the same run as the detail, expose the aggregate's own freshness, and prefer query-time aggregation with a fast engine where the performance gap does not justify the divergence risk.

The reason for that specificity is a failure I have seen: A nightly aggregate and a live detail view differed all day; users learned to compare the two and escalate whichever supported their argument.

Divergence through the day.

Time	Aggregate	Live detail	Difference
06:00	1.42m	1.42m	0
12:00	1.42m	1.71m	20%
18:00	1.42m	1.98m	39%

I would not consider it settled without evidence: Compare the aggregate against the detail on a schedule and alarm on any difference beyond a stated tolerance.

An aggregate is a cache and can be wrong.

The engineering content of row-level security is the additivity rule and the reconciliation, not the visual.

Filtering in the visual is a presentation choice that any user with query access can bypass, and an export or an API call is exactly such access. Enforcement belongs in the semantic layer or the database, where the filter applies to every path to the data.

Concretely, define the security predicate once in the model against an identity the platform supplies, apply it to every query path including exports and scheduled sends, and test by authenticating as a restricted user rather than by reviewing the configuration.

The reason for that specificity is a failure I have seen: A report filtered by region in the visual; a manager exported the underlying dataset and received every region, and the export had been available for 14 months.

Rows returned to a restricted user.

Path	Visual filter	Model-level security
dashboard	own region	own region
export	all regions	own region
API query	all regions	own region

I would not consider it settled without evidence: Authenticate as a restricted user and attempt an export and an API query, not just a dashboard view.

A filter in the visual is a suggestion to the visual.

Before publishing handling nulls in measures I would write down what a wrong number would look like here.

Aggregate functions generally ignore nulls, so a null is excluded from an average rather than counted as zero, and the two give very different answers. Which is correct depends on whether the null means unknown or means none.

Concretely, decide per column what a null means, coalesce where it means zero and leave it where it means unknown, and report the null count alongside the measure so a change in it is visible rather than absorbed.

The reason for that specificity is a failure I have seen: A source began sending nulls instead of zeros for one product line; the average order value rose 12 percent because those orders left the denominator, and the improvement was reported as a win.

Same data, two null meanings.

Values	Treated as unknown	Treated as zero
10, 20, null	avg 15.0	avg 10.0
count	2	3

I would not consider it settled without evidence: Report the null count per measure alongside the value, and alarm on a change in it.

Unknown and zero are different, and the aggregate treats them differently.

The first thing I would pin down about time zones in reporting is which decision the number is meant to support.

A daily total depends on where the day boundary is drawn, and a timestamp stored in one zone and reported in another shifts transactions between days. The difference is largest at the boundary and invisible in the middle of the day.

Concretely, store timestamps in a single zone, state the reporting zone in the model rather than leaving it to the client, and define the business day explicitly where it does not align with midnight.

The reason for that specificity is a failure I have seen: Two reports on the same table differed by up to 4 percent on daily revenue; one applied the viewer's local zone and the other the warehouse's, and month totals agreed which is why nobody looked.

One day, two zones.

Zone	Day total	Difference
warehouse UTC	142,000	—
viewer local	136,400	-3.9%
month total	identical	0%

I would not consider it settled without evidence: Compare daily totals across the two zones and confirm the difference is zero, rather than checking the monthly total.

A day is a boundary decision, so make it in one place.

I would start filter context in a calculated measure from the grain, because most wrong totals resolve to one.

A measure is evaluated against whatever filters apply where it appears, so a total row legitimately computes over a different set than the rows above it. For non-additive measures that is the correct behaviour and for additive ones it usually indicates a filter the author did not intend.

Concretely, establish what the total should mean before deciding it is wrong, express any context override explicitly in the measure rather than by arranging the report a particular way, and test totals separately from detail rows.

The reason for that specificity is a failure I have seen: A margin percentage total was overwritten with a sum of row percentages to make it "add up"; the reported total margin was 119 percent for a product mix with several small high-margin lines.

Margin total, two ways.

Product	Revenue	Margin	Margin %
A	900,000	90,000	10%
B	10,000	5,400	54%
C	4,000	2,200	55%
computed total	914,000	97,600	10.7%
sum of row %			119%, wrong

I would not consider it settled without evidence: Compute the total from the underlying numerator and denominator and compare it against what the visual shows.

A total is a separate calculation, not a sum of what is above it.

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

The comparison has to align on a business-meaningful boundary rather than a date offset. Subtracting 365 days moves a Monday to a Sunday, and comparing a 31-day month against a 28-day one reports a difference that is entirely calendar.

Concretely, use a date dimension that carries fiscal period, day of week, and holiday flags, align on the business boundary the comparison intends, and normalise by trading days where volume depends on them.

The reason for that specificity is a failure I have seen: A year-on-year retail comparison offset by 364 days aligned weekdays but shifted the Easter trading week between periods; the reported 11 percent decline was entirely the holiday moving.

Same month, three alignments.

Alignment	Change reported
minus 365 days	-11%
minus 364 days	-11%
fiscal period, holiday-adjusted	+3%

I would not consider it settled without evidence: Check the comparison across a full year including moveable holidays before publishing it as a trend.

Aligning dates is not aligning periods.

My answer to running totals and window functions begins with the definition, agreed before anything is built.

A running total accumulates over an ordered set, and the set it accumulates over is determined by the filters in force. A total computed in the source and stored is correct for one filter state and wrong for every other.

Concretely, compute the accumulation at query time over the filtered set, define the ordering explicitly since ties otherwise resolve arbitrarily, and state whether the accumulation restarts at a period boundary.

The reason for that specificity is a failure I have seen: A stored running total was correct for the full year and wrong for every filtered view; a regional filter showed the national accumulation, and the report was used for a regional target review.

Running total under a regional filter.

Month	Region north	Stored national total	Correct filtered
Jan	120k	400k	120k
Feb	140k	850k	260k
Mar	130k	1,310k	390k

I would not consider it settled without evidence: Filter to a subset and confirm the running total restarts and accumulates within it.

An accumulation depends on what is being accumulated over.

I would treat date dimension design as a claim about the business that has to reconcile to a source of record.

Fiscal periods, trading days, holidays, and week numbering are business facts that differ per organisation and cannot be derived from a calendar. A date dimension puts them in one reviewed place instead of in every query that needs them.

Concretely, build a table with one row per date carrying calendar and fiscal attributes, holiday and trading-day flags, and relative offsets, and join every fact to it rather than computing periods in measures.

The reason for that specificity is a failure I have seen: 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.

Where the definition lives.

Approach	Definitions	Updated on change
per-report logic	22	14
date dimension	1	1

I would not consider it settled without evidence: Count the places that independently define a fiscal period, and drive it to one.

A fiscal calendar is data, not a formula.

The useful question for many-to-many relationships is what the total does when the segments are unequal.

A direct many-to-many relationship makes totals ambiguous, because a customer's revenue is counted once per segment they belong to. There is no correct single total; the model has to say what a segment total means.

Concretely, introduce a bridge table with an explicit allocation rule where the business needs a summable total, or accept the overlap and label segment totals as non-additive so the grand total is not presented as a sum.

The reason for that specificity is a failure I have seen: Customer revenue was joined to a segment bridge with no allocation; segment totals summed to 1.7 times company revenue and the discrepancy was reported as a data quality issue for two quarters.

Segment totals against company revenue.

Segment	Revenue attributed
enterprise	62m
high growth	48m
strategic	61m
sum	171m
company	100m

I would not consider it settled without evidence: Sum the segment totals and compare against the company total; the difference is the overlap and must be explained.

Overlapping groups cannot both be exclusive and additive.

I would settle role-playing dimensions against a hand calculation on a small fixture before trusting the model.

One physical date dimension joined three times under different roles, because the attributes are identical and only the meaning of the join differs. Building three tables triples the maintenance and guarantees they will diverge.

Concretely, join the single date dimension multiple times with explicit role names in the model so a user picking "date" knows which one they have, and make the default role the one the business means by an unqualified date.

The reason for that specificity is a failure I have seen: A report joined on ship date while the business meant order date; revenue moved between months by up to 9 percent and the difference was attributed to timing noise for three months.

Monthly revenue by date role.

Month	By order date	By ship date	Difference
Mar	4.10m	3.73m	-9.0%
Apr	3.90m	4.21m	+7.9%

I would not consider it settled without evidence: Confirm the join role is visible in the field name a user selects, not only in the model diagram.

Three roles, one table, three explicit names.

The judgement in degenerate dimensions and junk dimensions is which measure is certified and who owns its definition.

An order number has no attributes of its own, so it belongs on the fact table as a degenerate dimension rather than in a table with a single column. Low-cardinality flags similarly do not deserve individual tables and are better combined.

Concretely, keep attribute-free identifiers on the fact, combine unrelated low-cardinality flags into one junk dimension to avoid a proliferation of two-row tables, and reserve real dimensions for things with attributes and hierarchies.

The reason for that specificity is a failure I have seen: A model had 14 dimension tables each holding a single yes/no flag; the diagram was unreadable and analysts joined the wrong flag table on 3 of 8 sampled reports.

Model shape.

Design	Dimension tables	Joins in a typical query
flag per table	14	7
junk dimension	1	2

I would not consider it settled without evidence: Count dimensions with fewer than three attributes and consolidate them.

A table with one column is not a dimension.

Where candidates lose the interview on handling deletes from a source system is explaining a wrong total as a tool quirk.

An incremental load that reads inserts and updates will never see a delete, so the warehouse retains rows the source no longer has. Totals then exceed the source permanently and the difference grows.

Concretely, establish whether the source hard-deletes, use change capture or a periodic full reconciliation to detect absences, and decide whether a deleted row should disappear or be soft-marked so historical reports remain stable.

The reason for that specificity is a failure I have seen: Cancelled orders were hard-deleted in the source; the warehouse kept them and reported revenue 2.3 percent above the ledger, which was reconciled manually every month for a year.

Drift from undetected deletes.

Month	Deleted in source	Detected	Cumulative overstatement
1	410	0	0.2%
6	2,600	0	1.3%
12	5,100	0	2.3%

I would not consider it settled without evidence: Reconcile row counts and totals against the source on a schedule rather than trusting the incremental path.

An incremental load cannot see what is no longer there.

I would answer surrogate keys by separating what the data measured from what the chart implied.

A natural key belongs to the source and can be reused, changed, or collide across systems after a merger. A surrogate key is owned by the warehouse and is also what makes a versioned dimension possible, since the same natural key must map to several rows.

Concretely, assign a surrogate key per dimension row, keep the natural key as an attribute for traceability, and resolve the fact's foreign key to the version that was current at the event time where the dimension is versioned.

The reason for that specificity is a failure I have seen: An acquisition brought a second source whose customer identifiers collided with 4,100 existing ones; facts joined to the wrong customers and the error was found by a customer receiving another company's report.

Collisions after the merger.

Key strategy	Collisions	Facts misattributed
natural key	4,100	61,000
surrogate + source qualifier	0	0

I would not consider it settled without evidence: Test that two sources' natural keys can coexist without collision before loading the second.

A key you do not own can change without telling you.

The engineering content of sparse and dense fact tables is the additivity rule and the reconciliation, not the visual.

A transaction fact stores only what happened, so an absent row means no activity and the report must supply the zero. A snapshot fact stores every combination and is far larger but makes absence explicit, which matters for measures like inventory where zero is a real value.

Concretely, use transaction grain for events and periodic snapshot grain for levels, and where a report must show zeros over a transaction fact, generate them from the dimension cross join at query time rather than storing them.

The reason for that specificity is a failure I have seen: A report over a transaction fact silently omitted products with no sales; a product that stopped selling disappeared from the trend rather than showing zero, and the decline went unnoticed for 4 months.

A product that stopped selling.

Month	Units	Row present	Shown in trend
Jan	400	yes	yes
Feb	0	no	no
Mar	0	no	no

I would not consider it settled without evidence: Check that a product with zero activity appears in the report with a zero rather than not appearing.

Absence and zero are different, and only one of them shows up.

Before publishing query performance and cardinality I would write down what a wrong number would look like here.

Reporting query cost is usually driven by the number of rows scanned and the cardinality of the grouping rather than by the complexity of the expression. A visual grouping by a high-cardinality attribute is asking for a large result the user cannot read anyway.

Concretely, check what the visual actually requests, reduce cardinality by grouping at a level a person can read, push filters as early as possible, and use partition pruning and aggregates for the wide-date-range cases.

The reason for that specificity is a failure I have 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 produce a picture of 20.

Rows scanned against rows shown.

Grouping	Rows returned	Rows scanned	Duration
customer id	1,400,000	780m	40 s
customer tier	5	780m	22 s
tier, from aggregate	5	1,800	0.4 s

I would not consider it settled without evidence: Inspect the generated query and the rows scanned rather than reasoning from the visual's appearance.

The visual shows 20 rows; the query may not.

The first thing I would pin down about import versus direct query is which decision the number is meant to support.

Importing gives fast, predictable performance against a snapshot whose age is the refresh interval; querying live gives current data at the cost of source load and latency that varies with what the source is doing. The choice is a freshness-against-performance trade, not a technical preference.

Concretely, import where the business decision tolerates the refresh interval, query live where it genuinely does not, consider a hybrid with recent partitions live and history imported, and state the data's age on the report either way.

The reason for that specificity is a failure I have seen: An operational dashboard used an imported model refreshed nightly; the operations team used it for intraday decisions for months before noticing the figures never changed during the day.

Freshness against responsiveness.

Mode	Data age	Visual load	Source load
import, nightly	up to 24 h	0.3 s	none in hours
direct query	seconds	4 s	continuous
hybrid	seconds to 24 h	0.6 s	recent only

I would not consider it settled without evidence: Show the data's as-at time on the report itself, so staleness is visible rather than assumed.

Show the age of the data on the page.

I would start the semantic layer as a contract from the grain, because most wrong totals resolve to one.

Without it, every report re-implements joins, filters, and metric logic, so the definition of revenue is whatever each report author wrote. The layer makes the definition a single reviewed object that reports consume rather than reinvent.

Concretely, define entities, joins, and metrics once with tests, expose them to every consumer including notebooks and extracts, and version the layer so a definitional change is a reviewed release rather than an edit.

The reason for that specificity is a failure I have seen: Eleven reports each defined active customer; six definitions existed, and the executive summary happened to use the most generous one.

Definitions in circulation.

Metric	Implementations	Values reported
active customer	6	74k to 96k
revenue	3	93.9m to 98.1m
after semantic layer	1	one each

I would not consider it settled without evidence: Count distinct implementations of each key metric across consumers, and drive it to one.

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

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.

Test	Broken load	Wrong number
freshness	yes	no
row count	yes	no
grain uniqueness	no	yes
measure reconciliation	no	yes

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

Structural tests catch broken, not wrong.

My answer to documenting a metric where it is used begins with the definition, agreed before anything is built.

A definition in a separate wiki is read by the people who go looking, which is not the people reading the number in a meeting. The definition has to travel with the metric to the point of consumption.

Concretely, attach the definition, owner, and last-reviewed date to the metric in the semantic layer so every consumer surfaces it, and include it in exports where the platform allows.

The reason for that specificity is a failure I have seen: A definition existed in a wiki nobody had opened in 8 months; a director presented the metric with an incorrect interpretation, and the correction happened after the decision.

Definition reachability.

Location	Views/month	Consumers reached
separate wiki	4	few
tooltip on the metric	1,900	most

I would not consider it settled without evidence: Check whether the definition is visible from the report without leaving it.

A definition nobody reaches is not published.

I would treat change management for a metric definition as a claim about the business that has to reconcile to a source of record.

Changing a definition changes history, so every published figure moves. Doing it silently destroys trust in the number more effectively than the original imprecision did.

Concretely, publish the change with the reason, show both definitions in parallel for a period, restate history explicitly, and record the change date on the metric so a comparison spanning it is visible as such.

The reason for that specificity is a failure I have seen: A definitional change was applied silently; the quarterly figure fell 6 percent, the business investigated a performance problem for two weeks, and the cause was the definition.

The 6 percent, explained.

Series	Q1	Q2
old definition	4.10m	4.16m
new definition	3.85m	3.91m
as published, silently	4.10m	3.91m

I would not consider it settled without evidence: Publish the before and after series together and require the difference to be explained before the change is applied.

A silent definitional change looks exactly like a business decline.

The useful question for designing for the decision is what the total does when the segments are unequal.

A request for everything is a symptom of an unstated decision. Without one, the layout is driven by available data and produces a page nobody acts on, which is also a page nobody can prune.

Concretely, establish the decision, its owner, and its frequency; derive the metrics from what that decision needs; and put the rest in a detail view. Being able to remove a metric is the test of whether the dashboard was designed.

The reason for that specificity is a failure I have seen: A 34-metric dashboard was built to specification; usage analytics showed 4 metrics accounted for 91 percent of interactions and the page took 22 seconds to load.

Interaction concentration.

Metrics	Share of interactions	Load time
top 4	91%	—
next 10	8%	—
remaining 20	1%	—
all 34	100%	22 s

I would not consider it settled without evidence: Measure interaction per metric after launch and remove what nobody uses.

Every metric costs load time and attention.

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.

Threshold	Days fired per year	Actioned
round number	146	3
95th percentile	18	9
99th percentile	4	4

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.

The judgement in accessibility in reports is which measure is certified and who owns its definition.

A dashboard is read in conditions the designer does not control — printed, projected, at low contrast, by people using assistive technology. Design choices that depend on a large high-contrast screen simply fail for part of the audience.

Concretely, meet contrast requirements, avoid encoding meaning in colour alone, keep font sizes readable when projected, provide the underlying table for anything a screen reader must convey, and test in the conditions the report is actually consumed in.

The reason for that specificity is a failure I have seen: A dashboard designed on a large monitor was projected in a bright room; the 9-point axis labels and low-contrast series were unreadable, and the meeting discussed the wrong series.

Readability by rendering.

Setting	Designer monitor	Projected	Printed
9pt labels	readable	no	marginal
12pt labels	readable	yes	yes
colour-only series	readable	marginal	no

I would not consider it settled without evidence: Test the report projected and printed, not only on the machine it was built on.

Design for the room it will be read in.

Where candidates lose the interview on self-service without duplicate definitions is explaining a wrong total as a tool quirk.

People build their own when the certified one is missing a dimension, is too slow, or is hard to find. Restricting the tool addresses none of those and pushes the work into spreadsheets, where it is invisible.

Concretely, find out why the certified asset was not used before adding governance, close the gap that drove the divergence, and make the certified path the fastest one so building a variant is a deliberate choice.

The reason for that specificity is a failure I have seen: A restriction on custom measures moved the work into spreadsheets; 14 versions of the same metric now existed outside the platform entirely, and none were discoverable.

Where the duplicates went.

Policy	Duplicates in platform	Duplicates in spreadsheets
open	22	3
restricted	2	14
gap closed	3	2

I would not consider it settled without evidence: Ask the authors of the top duplicated measures why they built them, and fix the reason.

Restriction moves the work somewhere you cannot see it.

I would answer usage analytics on the reporting estate by separating what the data measured from what the chart implied.

An estate accumulates because nothing removes reports, and every unused report still costs refresh capacity, review attention, and the risk that someone finds it and trusts it. Usage data is what makes pruning possible.

Concretely, instrument views by report and by user, identify the long tail with no views in a stated period, archive rather than delete after notifying owners, and require an owner for anything retained.

The reason for that specificity is a failure I have seen: Of 900 reports, 610 had no views in 6 months and 40 were still refreshing hourly against production; one of the unused ones was found by a new starter and used for a decision.

Estate by usage.

Category	Reports	Refresh cost
viewed weekly	90	high value
viewed monthly	200	keep
no views in 6 months	610	wasted

I would not consider it settled without evidence: Report views per asset over a stated period and archive the tail with owner notification.

An unused report still costs and can still mislead.

The engineering content of handling a request for a number you distrust is the additivity rule and the reconciliation, not the visual.

Refusing outright loses the conversation and the metric gets built elsewhere without the caveats. The better move is to supply it with the context that prevents the misuse, and to say plainly what it cannot support.

Concretely, provide the metric with its denominator, its uncertainty, and a stated list of decisions it cannot support, and offer the measure that does answer the underlying question.

The reason for that specificity is a failure I have seen: A refusal led the stakeholder to compute the metric in a spreadsheet without the denominator; the resulting figure was used in a board pack and was wrong by a factor of three.

Outcome by response.

Response	Metric used	Caveats present	Error
refuse	yes, in a spreadsheet	no	3x
supply with context	yes, in the model	yes	none

I would not consider it settled without evidence: Check whether the metric appears elsewhere without your caveats after a refusal.

A refused number gets built without the caveats.

Before publishing the executive summary figure I would write down what a wrong number would look like here.

The front-page figure sets what the organisation pays attention to, so it should be the one closest to the outcome rather than the one easiest to move. A number that can be improved without improving the business will be.

Concretely, choose the measure closest to the outcome, pair it with the counter-metric that would move if it were being gamed, and state the comparison so the figure is a finding rather than a level.

The reason for that specificity is a failure I have seen: A front-page lead count rose 40 percent after a form change while qualified opportunities fell; the counter-metric existed on page four and nobody looked at it.

The quarter, both metrics.

Metric	Q1	Q2	Position
leads	4,100	5,740	front page
qualified opportunities	610	520	page four

I would not consider it settled without evidence: Put the counter-metric beside the headline rather than deeper in the report.

Whatever is on the front page is what gets optimised.

The first thing I would pin down about when not to build a dashboard is which decision the number is meant to support.

A dashboard is right for a recurring question against changing data. A one-off question is answered better by an analysis, and a question whose answer will not change any decision is better not answered at all.

Concretely, establish whether the question recurs and whether the answer changes a decision; deliver an analysis for one-off questions, and where nothing would change, say so rather than building the page.

The reason for that specificity is a failure I have seen: A dashboard built for a one-off board question was viewed 12 times in its first week and twice in the following year, while continuing to refresh hourly.

Cost against use.

Deliverable	Build cost	Views in year 1	Ongoing refresh
dashboard	6 days	14	hourly
one-off analysis	1 day	n/a	none

I would not consider it settled without evidence: Ask how often the question will be asked and what the answer changes, before building.

A recurring question deserves a dashboard; a single one deserves an answer.

I would start window functions for ranking from the grain, because most wrong totals resolve to one.

A window function partitions the result and ranks within each partition in one pass, which is both simpler and faster than a correlated subquery per group. The choice among rank, dense rank, and row number determines what happens on ties, and that is a business decision.

Concretely, partition by the grouping column, order by the measure, and pick the ranking function deliberately: row number forces exactly five, rank leaves gaps after a tie, dense rank does not. State the tie-break so results are reproducible.

The reason for that specificity is a failure I have seen: Row number with no tie-break returned a different fifth product on each refresh when two products tied exactly; a supplier queried why they appeared one week and not the next.

Two products tied at 400 units.

Function	Ranks assigned	Rows returned for top 5
row_number	1,2,3,4,5	5, arbitrary choice
rank	1,2,3,4,4,6	5, rank 6 excluded
dense_rank	1,2,3,4,4,5	6

I would not consider it settled without evidence: Run the query twice on unchanged data and confirm the result is identical.

A ranking without a tie-break is not reproducible.

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

Named intermediate steps make a query reviewable, which matters because reporting queries encode business rules that other people must check. The cost is that a CTE referenced several times may be recomputed, depending on the engine.

Concretely, use CTEs to name business concepts rather than to break the query arbitrarily, check the plan where a CTE is referenced more than once, and materialise explicitly where the engine re-evaluates it.

The reason for that specificity is a failure I have seen: A CTE computing an expensive aggregate was referenced 4 times; the engine re-evaluated it each time and the query took 4 times longer than the equivalent with a materialised intermediate.

Cost by reference count.

References	Re-evaluated	Duration
1	once	4 s
4, inline CTE	4 times	16 s
4, materialised	once	5 s

I would not consider it settled without evidence: Read the execution plan for repeated references rather than assuming the CTE is evaluated once.

A name is not always a materialisation.

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.

Join	Fact rows in	Fact rows out	Revenue
inner	172,000	168,900	1.394m
left with unknown member	172,000	172,000	1.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.

I would treat unknown members in dimensions as a claim about the business that has to reconcile to a source of record.

A fact with a missing dimension key has to go somewhere, and the alternatives are dropping it, which understates totals, or leaving a null, which excludes it from grouped views. An unknown member keeps the total correct and makes the gap visible.

Concretely, create a reserved key for unknown in each dimension, route unmatched facts to it at load time, and monitor its share so a growing unknown is visible as a data quality signal rather than hidden in a total.

The reason for that specificity is a failure I have seen: Nulls were left in place; a grouped report excluded 4 percent of revenue and the totals across two reports differed by exactly that amount for months.

Unknown share as a signal.

Month	Unknown share	Cause
Jan	0.1%	normal
Feb	0.2%	normal
Mar	4.1%	new source unmapped

I would not consider it settled without evidence: Report the unknown member's share per dimension and alarm on a rise.

Unmatched facts should be visible, not absent.

The useful question for grouping sets and subtotals is what the total does when the segments are unequal.

Grouping sets compute several aggregation levels in a single pass, which is both faster and guaranteed consistent, whereas unioning separate queries can produce subtotals that disagree with the detail if a filter differs between them.

Concretely, use grouping sets or rollup for hierarchical totals, use the grouping indicator to distinguish a subtotal row from a detail row with a null, and never compute subtotals in a separate query that could drift.

The reason for that specificity is a failure I have seen: Subtotals were computed by a second query with a slightly different date filter; category subtotals exceeded the sum of their products by 0.4 percent and the discrepancy was investigated as a modelling error.

Consistency by method.

Method	Passes	Subtotal matches detail
union of queries	3	not guaranteed
grouping sets	1	always

I would not consider it settled without evidence: Assert that subtotals equal the sum of their details in the same result set.

Subtotals from a second query can disagree with the first.

I would settle partition pruning against a hand calculation on a small fixture before trusting the model.

Pruning only happens when the engine can tell from the predicate which partitions are needed. Wrapping the partition column in a function, or filtering on a derived column, hides that from the planner and forces a full scan.

Concretely, filter on the raw partition column with a literal range, avoid applying functions to it in the predicate, and verify pruning from the plan's partitions-scanned figure rather than from the query's appearance.

The reason for that specificity is a failure I have seen: A filter applied a date-formatting function to the partition column; the planner could not prune and every query scanned 1,096 partitions instead of 31.

Partitions scanned.

Predicate	Partitions scanned	Duration
function on column	1,096	48 s
raw column range	31	1.4 s

I would not consider it settled without evidence: Read the partitions-scanned count in the plan and compare it against the number the filter should select.

A function on the partition column hides it from the planner.

The judgement in columnar storage and projection is which measure is certified and who owns its definition.

Columnar storage reads only the columns a query references, so the cost is roughly proportional to the columns selected rather than to the row width. Selecting everything defeats the property the storage format exists to provide.

Concretely, select only the columns needed, avoid wildcard selection in models and views that others build on, and be aware that a wide select in a view propagates to every query over it.

The reason for that specificity is a failure I have seen: A view selected all 84 columns of a fact; every report over it read the full row width, and a query needing 3 columns cost 28 times what it should.

Bytes scanned by projection.

Columns selected	Bytes scanned	Duration
3 of 84	1.2 GB	0.9 s
all 84	34 GB	25 s

I would not consider it settled without evidence: Compare bytes scanned for a narrow and a wide selection over the same rows.

Columnar storage rewards asking for less.

Where candidates lose the interview on incremental models in a transformation tool is explaining a wrong total as a tool quirk.

An incremental model trades correctness guarantees for speed: it only reprocesses what its predicate selects, so anything outside that window keeps whatever it had, including errors introduced before the logic was fixed.

Concretely, define the incremental predicate with a lookback that covers late arrivals, make the merge idempotent on a stable key, and run a full refresh after any logic change since incremental runs will not revisit history.

The reason for that specificity is a failure I have seen: A logic fix was deployed and only applied to new rows; the corrected and uncorrected definitions coexisted in one table for 7 months and a year-on-year comparison spanned both.

Rows under each definition.

Period	Rows	Logic applied
before fix	4.1m	old
after fix	0.9m	new
after full refresh	5.0m	new

I would not consider it settled without evidence: Full-refresh after any logic change and reconcile a historical period against the new logic.

Incremental means history keeps the old logic.

I would answer environments for a reporting stack by separating what the data measured from what the chart implied.

Reporting changes are deployed into a system people read continuously, so a change tested only in production is tested on the audience. The environment needs to be realistic enough that a fan-out or a definitional change is visible before release.

Concretely, keep a development environment with production-shaped data, run the test suite and a reconciliation against key measures on every change, and compare the before and after values of certified measures as part of the review.

The reason for that specificity is a failure I have seen: A model change was tested on a 1,000-row sample where the fan-out did not appear; in production the same change inflated revenue 41 percent for 6 days.

Where the defect appeared.

Environment	Rows	Multi-line orders	Fan-out visible
sample	1,000	0	no
production shape	172,000	41%	yes

I would not consider it settled without evidence: Diff the certified measures between the current and proposed model on production-shaped data as a review artefact.

A sample too small to fan out proves nothing about fan-out.

The engineering content of lineage and impact analysis is the additivity rule and the reconciliation, not the visual.

Impact is a reachability question over the transformation graph, and stopping at direct dependents understates it. A column can be consumed by a model, which feeds a metric, which appears in reports nobody has listed.

Concretely, derive lineage from the code rather than from documentation, traverse transitively to reports and downstream extracts, and notify the owners of everything reachable rather than the immediate consumers.

The reason for that specificity is a failure I have seen: A rename was checked against 4 direct dependents; 38 reports and 2 scheduled extracts broke, including a customer-facing one that had no listed owner.

Impact by traversal depth.

Depth	Assets affected	Broke
direct	4	4
2 levels	19	19
transitive	40	40

I would not consider it settled without evidence: Produce the transitive impact set from lineage before approving a source change.

Impact is reachability, not adjacency.

Before publishing handling schema drift from a source I would write down what a wrong number would look like here.

Silently ignoring new columns loses data you may need and silently accepting them can break downstream contracts. The right behaviour depends on whether the consumer has a schema contract, and it should be a decision rather than a default.

Concretely, detect drift explicitly, fail or quarantine on a removed or retyped column since those break consumers, and admit added columns into a staging area without propagating them to a contracted model until reviewed.

The reason for that specificity is a failure I have seen: A source retyped an amount column from decimal to string; the pipeline coerced silently and 40 percent of values became null, which read as a genuine decline in a low-volume segment.

Behaviour on a retyped column.

Handling	Rows loaded	Values lost	Detected
silent coercion	172,000	68,800	no
schema assertion	0	0	yes, at load

I would not consider it settled without evidence: Assert the source schema on each load and fail on a type change rather than coercing.

A silent coercion is a data loss you will not see.

The first thing I would pin down about reporting on a slowly changing hierarchy is which decision the number is meant to support.

A hierarchy is a dimension that changes, and the report must state whether it uses the structure as it was or as it is now. Both are legitimate and they give different numbers, so the choice has to be explicit and visible.

Concretely, version the hierarchy with effective dates, offer both views where the business needs them, label which is in force on the report, and default to the historical view for periods that have already been reported.

The reason for that specificity is a failure I have seen: A reorganisation restated 4 quarters of team performance under the new structure; managers were compared against figures they had never seen and the review was abandoned.

Last quarter under two structures.

Team	As reported	Under new structure
A	4.10m	2.80m
B	3.20m	4.50m
total	7.30m	7.30m

I would not consider it settled without evidence: Re-run a previously published period after a hierarchy change and confirm it matches what was published.

As-was and as-is are different reports.

I would start currency conversion in reporting from the grain, because most wrong totals resolve to one.

The rate choice changes the answer and encodes a business meaning: transaction-date rates show what was actually earned, a fixed budget rate isolates operational performance from currency movement, and a current rate restates history every day.

Concretely, store the transaction amount in its original currency with the rate applied, offer the alternative rate bases as separate measures, and label which basis a figure uses on the report.

The reason for that specificity is a failure I have seen: A report restated at the current rate changed prior-period figures daily; a year-on-year comparison moved by up to 6 percent with no underlying change, and the pattern was attributed to reporting instability.

Same quarter, three bases.

Basis	Reported	Changes daily
transaction date	12.40m	no
budget rate	12.10m	no
current rate	11.70m	yes

I would not consider it settled without evidence: Re-run a closed period on consecutive days and confirm the figures do not move.

The rate basis is part of the metric definition.

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.

Treatment	March	April
restate original	4.09m	4.20m
reversal in period	4.10m	4.19m
as published	4.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.

My answer to reporting on incomplete periods begins with the definition, agreed before anything is built.

Comparing a partial month against a complete one reports a decline every time, and annualising a partial period exaggerates whatever the period happens to contain. The comparison has to be like for like.

Concretely, compare month-to-date against the same day of the prior period, label the period as partial and state the day, and avoid extrapolating to a full-month figure unless the seasonality within the month is understood and stated.

The reason for that specificity is a failure I have seen: A month-to-date figure was compared against the full prior month; every month showed a decline until the final days, and the pattern was presented to the board twice before it was questioned.

Day 15 of the month.

Comparison	Change reported
MTD against full prior month	-48%
MTD against prior MTD, day 15	+4%
annualised from MTD	+9%, unreliable

I would not consider it settled without evidence: Confirm the comparison uses the same elapsed portion of both periods.

Half a month against a whole one is always a decline.

I would treat sampling in a reporting context as a claim about the business that has to reconcile to a source of record.

Sampling is fine for exploration and dangerous for a reported figure, because the sampling error is invisible in the output. It is particularly unsafe for totals, rare events, and any measure driven by a small number of large values.

Concretely, sample for exploration and never for a certified figure, and where volume genuinely forces approximation, use a method with a stated error bound and present the bound alongside the number.

The reason for that specificity is a failure I have seen: A 1 percent sample was used for a revenue estimate; a single large customer fell outside the sample and the figure was 11 percent low, with nothing in the report indicating it was an estimate.

Sampling error by measure type.

Measure	Full	1% sample	Error
median order value	42.10	42.40	0.7%
total revenue	4.10m	3.65m	-11%
rare event count	14	0	-100%

I would not consider it settled without evidence: Compare the sampled figure against the full computation on a historical period and report the error.

A sampled total is an estimate, so it must say so.

The useful question for reporting on experiments is what the total does when the segments are unequal.

A dashboard showing a difference says nothing about whether the difference is distinguishable from noise, and a metric watched continuously will cross any threshold eventually. Reporting an experiment needs the design, not just the numbers.

Concretely, show sample size, the interval, and the predeclared analysis point, avoid presenting a live-updating experiment as a result, and label anything read before the planned end as provisional.

The reason for that specificity is a failure I have seen: A team shipped on a 3.2-point difference at day 4 with 340 users per arm; the interval spanned zero, and the effect was 0.1 points at the planned end with 12,000 per arm.

The same experiment over time.

Day	Users per arm	Difference	Interval
4	340	+3.2 pp	-1.9 to +8.3
14	4,100	+0.6 pp	-0.7 to +1.9
30, planned end	12,000	+0.1 pp	-0.6 to +0.8

I would not consider it settled without evidence: Report the interval and the planned analysis point alongside the point estimate.

A difference watched continuously will eventually look significant.

I would settle cohort analysis against a hand calculation on a small fixture before trusting the model.

A pooled retention figure mixes cohorts of different ages and sizes, so it moves when the mix moves even if every cohort behaves identically. Cohort analysis holds age constant, which is what makes the comparison meaningful.

Concretely, group by acquisition period and compare the same age across cohorts, show cohort sizes so a small recent cohort is not read as a trend, and be explicit that recent cohorts have less history rather than worse retention.

The reason for that specificity is a failure I have seen: A pooled retention figure fell 5 points after a large acquisition campaign; each cohort's retention at 30 days was unchanged, and the fall was entirely the new cohort's youth.

Retention at 30 days by cohort.

Cohort	Size	30-day retention
Q1	4,100	62%
Q2	4,300	61%
Q3	22,000	62%
pooled, all ages	30,400	57%

I would not consider it settled without evidence: Compare retention at a fixed age across cohorts before concluding anything from a pooled figure.

A pooled rate moves when the mix moves.

The judgement in forecasting in a BI context is which measure is certified and who owns its definition.

A forecast without an interval is presented as a fact and will be treated as a commitment. The interval is the part that carries the information about how much the forecast should influence a decision.

Concretely, show the interval, state the method and the assumptions, back-test against held-out history and publish the error, and refresh the model rather than letting a forecast fitted a year ago continue to render.

The reason for that specificity is a failure I have seen: A point forecast on a dashboard was adopted as a target; the model's back-tested error was plus or minus 18 percent, which had never been shown alongside it.

Forecast with its error.

Presentation	Figure	Back-tested error
point only	4.6m	not shown
with interval	3.8m to 5.4m	±18%

I would not consider it settled without evidence: Publish back-tested error alongside every forecast and refuse to show a point estimate alone.

A point forecast becomes a target.

Where candidates lose the interview on explaining a number that moved is explaining a wrong total as a tool quirk.

A movement decomposes into contributions — price, volume, mix, and new versus existing — and the decomposition is what turns a number into an explanation. Listing possible causes without quantifying them is not an answer.

Concretely, decompose the change into additive components that sum to the total movement, rank them, and check the largest against an independent source before presenting it.

The reason for that specificity is a failure I have seen: An analysis listed six plausible causes without quantifying any; the actual driver was a single customer's contract ending, which was 5.1 of the 6 points and had not been mentioned.

The 6 points, decomposed.

Component	Contribution
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

I would not consider it settled without evidence: Produce a decomposition whose components sum to the observed change, and name the largest.

A list of causes is not an explanation.

I would answer working with a stakeholder who wants a specific answer by separating what the data measured from what the chart implied.

The professional position is to separate the definitional question, which is legitimately theirs, from the computation, which is not. A definition can be argued; a figure computed under an agreed definition cannot be negotiated.

Concretely, agree the definition explicitly and in writing before computing, show the figure under both definitions where there is a genuine disagreement, and record which was used on the published figure.

The reason for that specificity is a failure I have seen: A figure was adjusted three times to accommodate objections; by the time it was published nobody could state its definition and it disagreed with the certified measure by 9 percent.

The same quarter under both definitions.

Definition	Revenue	Agreed by
including intercompany	98.1m	requester
excluding intercompany	93.9m	finance
published	93.9m	both, in writing

I would not consider it settled without evidence: Publish the definition alongside the figure so the number is traceable to a stated rule.

Definitions are negotiable; arithmetic is not.

The engineering content of estimating a BI request is the additivity rule and the reconciliation, not the visual.

The visual is usually the smallest part; the cost is in agreeing definitions, understanding the source, and reconciling. An estimate based on the visual is wrong by a large factor and sets an expectation that makes the rest look like delay.

Concretely, estimate the definitional, modelling, reconciliation, and build components separately, state the assumption about source data quality since that is the biggest variance, and re-estimate after the source is understood.

The reason for that specificity is a failure I have seen: A request estimated at 2 days took 14; 9 of them went to reconciling a source whose amounts did not tie to the ledger, and the overrun was recorded as poor estimation rather than as a data quality finding.

Where the 14 days went.

Component	Estimated	Actual
definition	0.5 d	2 d
model	0.5 d	2 d
reconciliation	0 d	9 d
build	1 d	1 d

I would not consider it settled without evidence: Break the estimate into components and track actuals against them to improve the next one.

The chart is the cheap part.

Before publishing when the data cannot answer the question I would write down what a wrong number would look like here.

Producing a number that looks like an answer is worse than saying no, because it will be used. The useful response names what would be needed to answer it and offers the nearest question the data can support.

Concretely, state plainly what is not measurable and why, offer the closest supportable question, and where the answer matters enough, propose the instrumentation that would make it answerable.

The reason for that specificity is a failure I have seen: A question about why customers churned was answered from the fields available; the resulting attribution was an artefact of which reasons had a dropdown option, and it drove a product change that did not affect churn.

What the field could distinguish.

Reason	Dropdown option	Share reported	Actual share
price	yes	61%	22%
missing feature	yes	31%	18%
unrecorded reasons	no	0%	60%

I would not consider it settled without evidence: Say what the data cannot distinguish, and check whether the proposed answer depends on that distinction.

A number that looks like an answer will be used as one.

The first thing I would pin down about the BI developer's own failure mode is which decision the number is meant to support.

The recurring one is optimising the delivery rather than the decision — more dashboards, faster refreshes, more metrics — because those are visible and measurable, while whether anything changed as a result is not.

Concretely, follow up on what decisions the reports actually changed, retire what nothing depends on, and spend the recovered effort on the small number of figures that carry real decisions.

The reason for that specificity is a failure I have seen: A team delivered 340 reports in two years; a review found 4 that were cited in any decision record, and the certified revenue measure had never been reconciled to the ledger.

Two years of output.

Measure	Value
reports delivered	340
cited in a decision	4
certified measures reconciled	0

I would not consider it settled without evidence: Track which reports appear in decision records and reallocate effort toward those.

Delivery is measurable; usefulness has to be checked.

I would start warehouse layering from the grain, because most wrong totals resolve to one.

Layers exist to separate concerns that change at different rates: raw landing preserves what arrived, a cleaned layer applies typing and deduplication, and a modelled layer applies business meaning. Collapsing them means a business rule change forces a reload from source.

Concretely, keep raw immutable so any downstream logic can be recomputed without re-extracting, put typing and quality in the middle, and confine business definitions to the modelled layer where they can be changed and replayed.

The reason for that specificity is a failure I have seen: Business logic was applied during extraction; a definitional change required re-extracting 3 years from a source that only retained 90 days, and the history could not be restated.

Recoverability by layering.

Design	Logic change requires	History restatable
logic in extraction	re-extract from source	90 days only
raw retained	rebuild from raw	3 years

I would not consider it settled without evidence: Confirm the modelled layer can be rebuilt entirely from retained raw data without touching the source.

Raw is what lets you change your mind later.

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

Mirroring is convenient for loading and wrong for querying, because the source is shaped for transactional writes. The warehouse exists to serve a different access pattern and should be shaped for it, with the mirror kept only as the raw layer.

Concretely, land the mirror as raw, model facts and conformed dimensions above it, and resist exposing the raw mirror to analysts since that is how the source's shape becomes the reporting layer's shape permanently.

The reason for that specificity is a failure I have seen: Analysts were given access to the raw mirror while the model was being built; two years later 90 reports depended on raw tables and the modelled layer had 6 consumers.

Consumption by layer after two years.

Layer	Reports depending	Intended
raw mirror	90	0
modelled	6	96

I would not consider it settled without evidence: Report consumption by layer, and treat significant consumption from raw as a modelling gap rather than a governance one.

Whatever you expose first becomes the interface.

My answer to data contracts with source teams begins with the definition, agreed before anything is built.

Without an agreement, the source team owes you nothing and will change a schema for good reasons of their own. A contract makes the expectation explicit and, more usefully, gives them a test that fails in their pipeline rather than in yours.

Concretely, agree the fields, types, and semantics you depend on, express the contract as a test the producer runs, version it, and give the producer a deprecation path rather than freezing their schema.

The reason for that specificity is a failure I have seen: A source retyped a column with no notice; the reporting team discovered it 3 days later from a wrong figure, and the producer had no way to know anyone depended on the type.

Where the break is caught.

Arrangement	Caught by	Delay
none	wrong figure	3 days
consumer-side test	failed load	4 h
producer-side contract test	producer's CI	before merge

I would not consider it settled without evidence: Confirm the contract test runs in the producer's pipeline, not only in yours.

A contract the producer cannot see is a hope.

I would treat the cost of a query in a consumption-priced warehouse as a claim about the business that has to reconcile to a source of record.

In consumption pricing the bill is driven by bytes scanned and by how often queries run, so a change that adds a visual, removes a filter, or shortens the auto-refresh multiplies cost without changing anything visible.

Concretely, attribute cost per report and per user, cache where the data's freshness allows, set auto-refresh from the decision's real cadence rather than the fastest available, and review the top spenders monthly.

The reason for that specificity is a failure I have seen: An auto-refresh set to one minute on a dashboard viewed by 90 people scanned 14 TB a day; the underlying data updated hourly, so 59 of every 60 refreshes returned identical results.

Cost against refresh interval.

Interval	Refreshes/day	Bytes scanned	Useful refreshes
1 minute	1,440	14 TB	24
1 hour	24	0.23 TB	24

I would not consider it settled without evidence: Compare refresh frequency against the data's actual update frequency and align them.

Refreshing faster than the data updates buys nothing.

The useful question for materialised views and their invalidation is what the total does when the segments are unequal.

It is right where the query is expensive, run often, and tolerates the staleness its refresh interval implies. It is wrong where consumers will compare it against live data, because they will find a difference and not know which is current.

Concretely, use it for expensive repeated aggregations, expose its refresh time to consumers, and prefer incremental maintenance where supported so the staleness window is small enough not to be noticed.

The reason for that specificity is a failure I have seen: A materialised view refreshed hourly sat beside a live detail view on the same dashboard; users compared the two and escalated the difference as a data quality incident four times before it was explained.

Divergence within the refresh window.

Minutes since refresh	Materialised	Live	Difference
5	1.42m	1.43m	0.7%
55	1.42m	1.61m	12%

I would not consider it settled without evidence: Show the as-at time of any materialised source next to the figures it produces.

A stale figure beside a live one produces an incident.

I would settle indexing and clustering for reporting queries against a hand calculation on a small fixture before trusting the model.

Clustering or sorting the data on the column the queries filter by lets the engine skip blocks that cannot match, which reduces the bytes read rather than the work done per byte. It helps where the filter is selective and does nothing where it is not.

Concretely, cluster on the columns most queries filter or join by, measure the pruning effect rather than assuming it, and re-cluster as the data grows since the benefit degrades with insertion order over time.

The reason for that specificity is a failure I have seen: A table was clustered on a column used by 4 percent of queries while 80 percent filtered on date, which was unclustered; the clustering cost maintenance and delivered no measurable improvement.

Pruning by clustering key.

Clustering key	Queries benefiting	Bytes scanned
product id	4%	34 GB
event date	80%	2.1 GB

I would not consider it settled without evidence: Measure bytes scanned before and after clustering for the actual query mix, not for a chosen example.

Cluster on what the queries actually filter by.

The judgement in handling very wide dimensions is which measure is certified and who owns its definition.

Width itself is not the problem; the problem is that attributes with different change rates and different owners are in one table, so a frequently-changing attribute forces a new version of every rarely-changing one under a versioned design.

Concretely, split rapidly-changing attributes into a separate mini-dimension keyed from the fact, keep stable descriptive attributes together, and choose the split from measured change rates rather than from intuition.

The reason for that specificity is a failure I have seen: A credit-score band changing monthly sat in a versioned customer dimension; the table grew to 41 million rows for 900,000 customers and dimension queries became the slowest part of every report.

Dimension growth by design.

Design	Customers	Rows	Versions per customer
single wide dimension	900,000	41m	45
mini-dimension split	900,000	1.1m	1.2

I would not consider it settled without evidence: Measure the change rate per attribute and split the ones driving version growth.

One fast-changing attribute versions the whole row.

Where candidates lose the interview on bridge tables for hierarchies is explaining a wrong total as a tool quirk.

A fixed set of level columns only works when the depth is fixed, and a self-referencing parent key requires recursion that most reporting tools handle poorly. A bridge table flattens every ancestor-descendant pair so a rollup becomes a simple join.

Concretely, materialise the closure with a depth attribute, rebuild it when the hierarchy changes, and version it where historical rollups must remain stable.

The reason for that specificity is a failure I have seen: A five-column level model truncated a seven-level hierarchy; two levels of the organisation were absent from every rollup and their revenue appeared under their grandparent.

Coverage by hierarchy model.

Model	Max depth supported	Actual max	Nodes represented
5 level columns	5	7	61%
bridge closure	any	7	100%

I would not consider it settled without evidence: Check the maximum actual depth against the model's capacity before choosing a fixed-level design.

Fixed levels assume a depth the business has not agreed to.

I would answer audit columns on every table by separating what the data measured from what the chart implied.

When a figure is disputed the first question is where the row came from and when, and without load metadata the answer requires reconstructing a pipeline run from logs. The columns are cheap and the absence is expensive exactly when it matters.

Concretely, carry the source system, the extract timestamp, the load run identifier, and the row's effective dates where versioned, and expose the load identifier so a figure can be tied to a specific run.

The reason for that specificity is a failure I have seen: A disputed figure could not be traced to a load; establishing which run had produced it took 2 days of log correlation and the answer arrived after the meeting it was needed for.

Time to trace a disputed row.

Metadata present	Time to answer
none	2 days
load run id	10 min
+ source and extract time	2 min

I would not consider it settled without evidence: Take a disputed row and confirm you can name its source and load run from the row itself.

Provenance is cheap to carry and expensive to reconstruct.

The engineering content of reprocessing history safely is the additivity rule and the reconciliation, not the visual.

A backfill rewrites figures people have already seen and acted on, so it is a communication event as much as a technical one. Doing it silently is how a correct fix destroys trust in the reporting layer.

Concretely, reprocess into a parallel location, reconcile old against new and quantify the change per period, communicate before switching, and keep the previous version available for a period so a comparison against a published figure is possible.

The reason for that specificity is a failure I have seen: A backfill was applied in place overnight; a director's board figures changed between the draft and the meeting, and the reporting team learned about it from the director.

Effect of the correction by period.

Period	Published	Corrected	Change
FY1	41.2m	40.1m	-2.7%
FY2	46.8m	46.6m	-0.4%

I would not consider it settled without evidence: Produce a before-and-after comparison per period and circulate it before the switch.

Correcting history changes what people already reported.

Before publishing handling personally identifying data in reports I would write down what a wrong number would look like here.

A reporting layer distributes data widely by design, so personal data in it reaches an audience far larger than the source system's. Aggregation is not automatically anonymisation, since a small group can identify an individual.

Concretely, report at an aggregate level where the question allows, suppress cells below a minimum count so small groups cannot be resolved, restrict row-level detail by security, and record the basis for including any personal field.

The reason for that specificity is a failure I have seen: A regional breakdown included a region with two customers; their revenue was individually identifiable, and the report was distributed to 400 people.

Smallest cell by breakdown.

Breakdown	Smallest cell	Identifiable
region	2 customers	yes
region and segment	1 customer	yes
region, suppressed below 5	suppressed	no

I would not consider it settled without evidence: Check the minimum cell count across every dimension combination the report permits, not just the default view.

A small group is an individual with extra steps.

The first thing I would pin down about retention and the right to erasure is which decision the number is meant to support.

The warehouse is a copy, so erasure in the source does not propagate, and downstream extracts and backups are further copies again. The obligation applies to all of them and the pipeline usually has no mechanism for a deletion.

Concretely, maintain a deletion path that reaches every copy including aggregates and extracts, distinguish aggregate figures that need not change from row-level data that must go, and record the deletion so a later restore does not reintroduce it.

The reason for that specificity is a failure I have seen: Erasure was applied in the source only; the customer's rows persisted in the warehouse, in 3 extracts, and in backups, and were reintroduced to the source by a restore 8 months later.

Copies reached by the erasure.

Copy	Reached	Reintroduced by restore
source	yes	yes
warehouse	no	—
extracts	no	—
backups	no	—

I would not consider it settled without evidence: Trace one erasure through every copy and confirm each is addressed, including the restore path.

Deletion has to reach every copy you made.

I would start the difference between a metric and a dimension from the grain, because most wrong totals resolve to one.

A measure aggregates and a dimension groups, and a request to group by a measure is a request to bucket it into a derived attribute. That bucketing is a modelling decision — at what level, over what period — and it changes the answer.

Concretely, establish the level and period at which the banding is computed, materialise the band as a dimension attribute so it is stable and reportable, and state the definition since a customer's band depends on when it was calculated.

The reason for that specificity is a failure I have seen: Revenue band was computed on the fly over whatever period the filter selected; the same customer appeared in three bands across three reports and the segmentation was abandoned.

One customer, three reports.

Report	Period banded over	Band
quarterly	3 months	mid
annual	12 months	high
trailing 30 days	1 month	low

I would not consider it settled without evidence: Confirm a given customer falls in the same band across every report, or state why not.

Banding a measure creates a dimension, so define it once.

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

The warehouse is optimised for aggregation across history and is a copy with a lag. An operational question about the current state of one record is answered better and more correctly by the system of record.

Concretely, route current-state single-record questions to the operational system, keep the warehouse for aggregation and history, and resist building operational lookups into a reporting layer whose freshness cannot support them.

The reason for that specificity is a failure I have seen: A support team used a nightly-refreshed dashboard to check order status; customers were told incorrect statuses for up to 24 hours until the practice was noticed.

Question against the right system.

Question	Freshness needed	Right system
this order's status	seconds	operational
orders shipped last month	daily	warehouse
year-on-year trend	daily	warehouse

I would not consider it settled without evidence: Check what freshness each consumer's question actually needs before serving it from the warehouse.

A copy with a lag cannot answer a current-state question.

My answer to spreadsheets as part of the reporting estate begins with the definition, agreed before anything is built.

A spreadsheet in use is a working system encoding rules that exist nowhere else, and replacing it without extracting those rules loses them. It is a requirements document as much as a tool.

Concretely, extract the rules and reconcile the modelled output against the spreadsheet until they agree exactly, then migrate, and keep the spreadsheet available in parallel for a period rather than switching on a promise.

The reason for that specificity is a failure I have seen: A model was built from the documented process and differed from the spreadsheet by 3 percent; the difference was a manual adjustment applied for 4 years that appeared in no documentation.

Reconciling to the spreadsheet.

Version	Figure	Difference explained
model, from documentation	12.13m	no
spreadsheet	12.50m	—
model, with manual adjustment rule	12.50m	yes

I would not consider it settled without evidence: Reconcile to the penny against the spreadsheet before proposing to retire it.

The spreadsheet is where the undocumented rules live.

I would treat version control for reporting logic as a claim about the business that has to reconcile to a source of record.

Reporting logic is code that produces numbers people act on, so it needs the same review, history, and rollback as any other code. Changes made directly in a tool have no diff, no reviewer, and no way to answer what a figure was computed from last quarter.

Concretely, keep model and metric definitions as code with review, treat tool-side edits as exceptions requiring back-porting, and tag releases so a published figure can be tied to the logic that produced it.

The reason for that specificity is a failure I have seen: A definitional change was made directly in the tool; six weeks later nobody could say what the measure had been before, and the previously published figures could not be reproduced.

Reproducibility by practice.

Practice	Diff available	Prior figure reproducible	Weeks to answer "what was it"
edits in tool	no	no	6, unresolved
definitions as code	yes	yes	0
+ release tags	yes	yes, by version	0

I would not consider it settled without evidence: Take a published figure from three months ago and reproduce it from the versioned logic.

A figure you cannot reproduce is a figure you cannot defend.

The useful question for onboarding a new analyst is what the total does when the segments are unequal.

Time to first correct answer measures the whole system — model clarity, documentation, access, and support — better than any adoption count, because it is the path every new person walks and it exposes whichever part is worst.

Concretely, measure it with a real question, instrument each step including access approvals, and attack the largest step rather than the most visible one.

The reason for that specificity is a failure I have seen: A new analyst took 11 days to produce a correct figure; 7 were waiting for access approvals nobody had counted as part of onboarding.

Where the 11 days went.

Step	Days
access approvals	7
finding the certified model	2
writing the query	1
reconciling the answer	1

I would not consider it settled without evidence: Instrument the journey end to end including waits, and report median and worst case.

The waiting is part of the onboarding.

I would settle reviewing another analyst's work against a hand calculation on a small fixture before trusting the model.

The defects that matter are rarely in the visual. They are in the grain, the join, the filter, and the definition, and a review that looks at the chart checks the least likely place for the error to be.

Concretely, check the grain and the joins for fan-out, the filters for silent exclusions, the definition against the certified one, the totals against a hand calculation, and the null handling, before looking at the presentation.

The reason for that specificity is a failure I have seen: A review approved a report on its presentation; the underlying join dropped 3 percent of facts through an inner join and the error was found by a customer three weeks later.

Where review findings came from.

Area	Findings in 40 reviews
grain and joins	17
filters and exclusions	11
definition mismatch	8
presentation	4

I would not consider it settled without evidence: Reconcile the report's total against the certified measure as part of every review.

The error is upstream of the chart.

The judgement in Python in a BI workflow is which measure is certified and who owns its definition.

Python is well suited to ad-hoc analysis, statistical work the warehouse cannot express, and orchestration. It is a poor place for a business definition, because logic in a notebook is not reachable by the reporting layer and diverges from the certified model immediately.

Concretely, keep definitions in the semantic layer where every consumer reaches them, use Python for analysis and for transformations the warehouse genuinely cannot do, and push any recurring notebook logic down into the model.

The reason for that specificity is a failure I have seen: A key metric was computed in a scheduled notebook; when the certified definition changed, the notebook kept the old one for 5 months and the two figures were both in circulation.

Where definitions lived.

Location	Definitions	Updated on change
semantic layer	40	40
scheduled notebooks	6	1

I would not consider it settled without evidence: Count metric definitions living outside the semantic layer and migrate them.

A definition in a notebook is invisible to the model.

Where candidates lose the interview on pandas for reporting-scale data is explaining a wrong total as a tool quirk.

Pandas holds the whole frame in memory with substantial per-column overhead, so a dataset comfortably handled by the warehouse can exceed a workstation's memory by a wide margin. Pushing the aggregation into the warehouse usually removes the problem entirely.

Concretely, aggregate in SQL and bring back the result rather than the detail, select only needed columns, use appropriate dtypes and categoricals where a frame is genuinely needed, and process in chunks where the operation permits.

The reason for that specificity is a failure I have seen: An analyst pulled 180 million rows to compute a group-by that the warehouse would have done in 4 seconds; the notebook consumed 60 GB and failed after 20 minutes.

Same result, two approaches.

Approach	Rows transferred	Memory	Duration
pull then group in pandas	180m	60 GB	failed
group in SQL, pull result	400	2 MB	4 s

I would not consider it settled without evidence: Compare rows returned against rows needed for the result, and push the aggregation down where they differ.

Aggregate where the data is.

I would answer dictionaries and lookups in analysis code by separating what the data measured from what the chart implied.

Scanning the lookup for each row is quadratic in the product of the two sizes; building a hash map once makes each lookup constant, turning the work into a single pass. The difference is minutes against seconds at this scale.

Concretely, build the map once outside the loop, or use the library's own merge which does this internally, and be alert to an apparently innocent lookup inside a per-row function since that is where the pattern hides.

The reason for that specificity is a failure I have seen: An enrichment applied a per-row function that filtered the lookup frame each time; 2 million rows against 50,000 took 14 minutes, and the same work as a merge took 3 seconds.

Enrichment cost by method.

Method	Comparisons	Duration
filter per row	1e11	14 min
hash map	2e6	3 s
library merge	2e6	2 s

I would not consider it settled without evidence: Measure runtime against input size at two points; a fourfold rise for doubled input indicates the quadratic pattern.

Build the lookup once, not once per row.

The engineering content of sorting large result sets is the additivity rule and the reconciliation, not the visual.

Sorting is one of the operations a warehouse is built for, with parallelism and spill-to-disk that a single-process client does not have. Pulling the rows out to sort them locally moves the work to the least capable part of the system.

Concretely, sort in the warehouse and stream the ordered result, page rather than materialising everything client-side, and question whether a 20-million-row sorted export is answering a question that a summary would answer better.

The reason for that specificity is a failure I have seen: An export pulled 20 million rows and sorted in the client; it took 40 minutes and 24 GB, and the recipient used only the top 500 rows.

Export cost by approach.

Approach	Duration	Memory	Rows used
client-side sort	40 min	24 GB	500
warehouse sort, full export	3 min	streamed	500
warehouse sort, top 500	2 s	negligible	500

I would not consider it settled without evidence: Ask what the recipient does with the export before optimising how it is produced.

Sort where the engine is, and ask why 20 million rows.

Before publishing deduplication strategies I would write down what a wrong number would look like here.

Which row to keep is a business decision — latest by update time, first by insert, or a merge of fields — and the choice changes the reported figures. Deduplicating arbitrarily produces a result nobody can reproduce.

Concretely, define the key and the tie-break explicitly, deduplicate deterministically with a window function ordered on a stable column, and report the duplicate rate so a change in it is visible.

The reason for that specificity is a failure I have seen: Deduplication kept an arbitrary row where update times tied; the reported status of 4,100 orders changed between runs and the instability was attributed to the source.

Stability by tie-break.

Tie-break	Rows changing between runs
none	4,100
update time, then id	0

I would not consider it settled without evidence: Run the deduplication twice on unchanged input and confirm the result is identical.

A non-deterministic dedupe produces a different report each run.

The first thing I would pin down about reporting availability and refresh failures is which decision the number is meant to support.

Serving yesterday's data silently is the worst option, because it looks current and will be acted on. The choice is between showing stale data clearly labelled and showing nothing, and either is better than an unlabelled stale figure.

Concretely, show the as-at time prominently, alert the owner on failure with enough detail to act, and where the data's age exceeds a stated tolerance, replace the figures with the failure state rather than continuing to render.

The reason for that specificity is a failure I have seen: A failed refresh went unnoticed for 3 days; the dashboard rendered normally throughout and two decisions were taken on figures that were three days old.

What consumers saw.

Design	Visibly stale	Decisions on stale data
render silently	no	2
as-at label	yes	0
replace past tolerance	yes	0

I would not consider it settled without evidence: Fail a refresh deliberately and confirm consumers can tell from the report itself.

Stale and unlabelled is worse than absent.

I would start measuring the reporting team's value from the grain, because most wrong totals resolve to one.

Output measures — reports delivered, tickets closed — describe activity and are easy to improve without improving anything. What indicates value is decisions the reporting supported and time saved for the people who would otherwise have assembled the numbers.

Concretely, track which reports are cited in decisions, measure time to answer for recurring questions, and report the retirement of unused assets as an outcome rather than a loss.

The reason for that specificity is a failure I have seen: A team reported 340 dashboards delivered as its annual achievement; a usage review found 610 of 900 assets unused, and the certified measures had never been reconciled.

Two views of the year.

Measure	Value	Reads as
dashboards delivered	340	productive
assets unused	610 of 900	waste
median time to answer	4 h to 20 min	improvement

I would not consider it settled without evidence: Report decisions supported and time to answer rather than assets delivered.

Delivering more is not the same as helping more.

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

An implausible result is more often a data or modelling defect than a discovery, and the professional move is to try hard to break it before presenting it. Publishing an artefact as a finding costs credibility that a delayed correct answer does not.

Concretely, reconcile against an independent source, check the grain and the joins, look for a change in the pipeline around the date the effect starts, and only present the finding once the obvious defects are ruled out.

The reason for that specificity is a failure I have seen: A 40 percent jump in orders was presented as a growth finding; it was a duplicate load, and the correction was circulated after the number had reached a board pack.

What the investigation found.

Hypothesis	Consistent with data	Confirmed
genuine growth	yes	no
duplicate load	yes	yes
source change	yes	no

I would not consider it settled without evidence: Check whether the effect starts on a date that coincides with a pipeline change, before treating it as a business event.

A surprising number is usually a defect.

My answer to what good looks like for a reporting layer begins with the definition, agreed before anything is built.

The indicators are that figures reconcile to the source of record, that one definition exists per metric, that consumers can tell how fresh a number is, and that unused assets are removed. Those are properties of the system rather than of its output volume.

Concretely, track reconciliation status per certified measure, definitions per metric, share of consumption from certified assets, and unused asset count, and treat each as a maintained number rather than a project outcome.

The reason for that specificity is a failure I have seen: A team measured only delivery; two years in, no certified measure had been reconciled to the ledger and six definitions of active customer were in circulation.

Reporting layer health.

Measure	Year 1	Year 2
certified measures reconciled	0 of 12	12 of 12
definitions per key metric	up to 6	1
consumption from certified	41%	84%
unused assets	610	40

I would not consider it settled without evidence: Report the four health measures monthly and act on the one that is worst.

Health is reconciliation and singularity, not volume.