Core day 6 of 60 · Week 2 · Part I · application depth

Exploratory Data Analysis

A route dashboard shows IST-TZX passengers in March down 21% year on year, and the network planner wants to cut a frequency. The source table has duplicates, missing fares, impossible values and gaps. Which of those issues changes this decision, and how do you prove it before anyone fits a model?

By the end: From a profile of a messy table you name the one quality issue that changes a stated decision, show its effect on the decision metric with a query, and say which other issues can wait.

At a glance

Profile the table against the decision, find the issue that moves the decision metric, and treat errors, rare truths and regime changes differently.

Decision first
Fix the metric that drives the decision before profiling anything.
Coverage
A missing row looks exactly like zero in a sum - of passengers, orders or sensor readings.
Three surprises
An error is fixed, a rare truth is kept, a regime change is modelled.
Missingness
MCAR, MAR or MNAR decides whether deleting rows biases the answer.
Triage
Rank issues by their effect on the decision, not by row count.

Recall · 5 min

Without notes: what is the grain of flight_day in the airline sample database, and what does a sum of passengers mean at that grain? Then name one feature from Day 2 that is not available at a 30-days-out decision time.

Open due reviews

Concept · 10 min

EDA is detective work with a suspect list, not a tour of every column. A profile of flight_day_raw finds a dozen oddities in minutes; most are real and most do not matter for the question on the table. The skill is ranking: which issue moves the number that drives this decision, by how much, and in which direction?

Each profile panel finds a real issue, but only the coverage gap reverses the March verdict

Predict first

Duplicates add about 0.8% extra rows in both years. What do they do to a year-on-year percentage change?

Text description of the figure

Profile panels for a messy flight table: key uniqueness, coverage by date and route, missing rate by market, and load factor per row with impossible values highlighted. A cleaning-strategy selector recomputes the headline metrics (mean and trend) under each choice so you can see which issue moves it. The widget builds its own seeded table, so its percentages differ slightly from the query's (for example -23.0% rather than -21.4% for IST-TZX).

Three surprises that look alike in a histogram Overview

An error
Manufacturing: a sensor reads above the line's rated maximum and the shift log disagrees. Fix it under a logged rule.
A rare truth
Retail: Black Friday really did sell 8x a normal day. Keep it; it is the demand you must plan for.
A regime change
Telecom: churn jumps from the date a competitor cut prices. Model the break (Day 21).

The EDA loop, decision first Diagram

Decision and metricMarch passengers per route
Keys and grainis (date, flight_no) unique?
- to Coverage over time
Triage tableissue, rows, effect
Coverage over timeexpected vs present rows
- to Integrity rules
Distributionson rows you now trust
- back to Triage table
Integrity rulespassengers <= capacity
Missingness by segmentrates by market
- back to Distributions

Each check hands the next one a table it can trust a little more: one row per key, every expected row, valid values, known gaps by segment. Coverage comes early because it is the cheapest check with the biggest payoff: a missing row is invisible to every later step.

A coverage calendar finds the hole a SUM cannot see Illustration

IST-TZX has 43 of 62 expected flight rows in March 2023. Nothing in the rows that exist is wrong; the problem is the rows that do not.

Where rows exist, 2023 runs above 2022. The dive to zero is the feed, not the passengers.

Same rows, two divisions: the sign flips once you divide by the rows that are actually there.

Seeded synthetic table from the Day 6 widget (two daily flights on six routes, planted gap). The lab query on the sample database gives -21.4% and +13.3% for the same comparison.

Each cell is one route-day; colour shows how many of the two scheduled flights have a row. The same rows give opposite year-on-year signs depending on what you divide by.

Four shapes, four next steps Illustration

A long right tail pulls the mean 20 EUR above the median. Report the median, or model the log.

One row out of 121 moves the mean by 9.8 and the median by 0.5. Fix the row; do not hide it.

Two peaks. Only 6% of flights sit within 0.02 of the mean: split by market before you summarize.

8% of flights pile up at the limit of 190; the dashed outline is demand nobody recorded.

Seeded synthetic data (600 fares, 121 flights, 500 load factors, 500 flights). Red line: mean; dashed line: median.

Skew, a typo, a mixture and a ceiling each pull the mean and the median apart differently. The shape tells you which summary to report and what to check next.

Three missingness mechanisms Comparison

Criterion	MCAR	MAR	MNAR
Missingness depends on	Nothing: completely at random	Observed columns: e.g. a missing rate that differs by segment	The missing value itself: high earners skip the income field
Deleting incomplete rows	Unbiased; you lose precision only	Biased if the groups differ	Biased, and the observed rows cannot show it
What to do	Complete cases are fine	Analyse within groups or impute conditional on them	Get more data or run a sensitivity analysis

The data can reject MCAR (rates differ by segment) but cannot prove MAR over MNAR.

Triage for the IST-TZX decision Table

Issue	Rows flagged (2022-2023)	Effect on March passengers per route
Missing flight-days	93	Blocks: flips IST-TZX from +13% to -21%
Duplicate keys	110 (55 with conflicting revenue)	Small: inflates both years by a similar share
Route not upper-case	124	Small: splits a route's total until standardised
avg_fare missing	281	None: passengers are not affected
Passengers > capacity	7	Tiny here; matters for load factor
Negative bookings	2	None for passengers; fix at source

Counts from today's lab query on flight_day_raw. Only the top row has to block the decision; the rest go to a backlog with owners.

Run a short loop, decision first: metric, keys, coverage, integrity rules, missingness by segment, then distributions.
The missingness mechanism decides what deletion does: MCAR, MAR or MNAR.
End with a triage table: issue, rows affected, effect on the decision metric. Only the top row blocks the decision.

Assumptions

You know which rows should exist (here every flight operates daily); without an expected calendar, a coverage check cannot tell a gap from a cancelled service.
The integrity rules reflect physics or business definitions, not habit; a rule like "fares under EUR 30 are errors" needs a business owner.
Missingness labels (MCAR/MAR/MNAR) are hypotheses about a process; the data can reject MCAR (missing rates differ by segment) but cannot prove MAR over MNAR.

Application and workplace decision · 10 min

Run the query. It removes exact repeats, standardises route casing, and compares March 2022 with March 2023 per route two ways: the total-passengers change the dashboard shows, and the change in passengers per flight-day that is actually present. Then answer: does IST-TZX justify cutting a frequency?

Your answer to the task (decision, method, baseline). Write it before reading the interpretation.

SQL (airline sample database)

-- March passengers per route: total change vs change per flight-day present
WITH march AS (
  SELECT DISTINCT date, flight_no, UPPER(route) AS route, passengers
  FROM flight_day_raw
  WHERE substr(date, 6, 2) = '03'
),
by_year AS (
  SELECT route,
         SUM(CASE WHEN date < '2023' THEN passengers END)  AS pax_2022,
         SUM(CASE WHEN date >= '2023' THEN passengers END) AS pax_2023,
         COUNT(CASE WHEN date < '2023' THEN 1 END)         AS flight_days_2022,
         COUNT(CASE WHEN date >= '2023' THEN 1 END)        AS flight_days_2023
  FROM march
  GROUP BY route
)
SELECT route, pax_2022, pax_2023,
       ROUND(100.0 * (pax_2023 - pax_2022) / pax_2022, 1) AS total_yoy_pct,
       flight_days_2022, flight_days_2023,
       ROUND(100.0 * (1.0 * pax_2023 / flight_days_2023 - 1.0 * pax_2022 / flight_days_2022)
             / (1.0 * pax_2022 / flight_days_2022), 1) AS per_flight_day_yoy_pct
FROM by_year
ORDER BY total_yoy_pct;

This code is executed in the repository's checks (pnpm lesson:py / lesson:sql). It is not run on this site; copy it into your notebook.

Technical depth · optional · 31 min in total

Open only what you need. Nothing here is required to complete the day; the assessment covers the core above.

Robust statistics: median, trimmed mean, MAD6 minThe estimators defined, an 8-number example worked by hand where the 3-SD rule misses its own outlier, and what a breakdown point buys.

One family of centre estimates, from mean to median Formula

\bar x_{\alpha} \;=\; \frac{1}{n-2g} \sum_{i=g+1}^{\,n-g} x_{(i)}, \qquad g = \lfloor \alpha n \rfloor

$x_{(i)}$: the values sorted ascending (order statistics); the estimator works on ranks, not raw positions
$\alpha$: the trim share per side: alpha = 0 is the mean; alpha near 0.5 leaves only the middle value(s), the median
$g$: how many values are discarded at each end before averaging the rest

The trimmed mean interpolates between mean and median. Choosing alpha is choosing how many corrupted rows you want to survive.

A robust yardstick for "unusual" Formula

\begin{gathered}\text{MAD} = \operatorname{median}_i\,\lvert x_i - \tilde x \rvert \\ \hat\sigma = 1.4826 \times \text{MAD} \\ z_i = \frac{x_i - \tilde x}{\hat\sigma}\end{gathered}

$\tilde x$: the median: half the rows can be wrong before it breaks; one bad row can move the mean anywhere
$\text{MAD}$: median absolute deviation from the median; the robust cousin of the standard deviation
$1.4826$: 1 / (0.75 quantile of the standard normal): makes MAD estimate sigma for normal data
$z_i$: a robust z-score; extreme rows cannot inflate the yardstick that judges them

A 3-standard-deviation rule uses a mean and SD that the outlier itself inflates. Flagging is only step one; the row still needs a diagnosis.

Worked by hand: 8 claim payouts, one with an extra digit Table

Quantity	By hand	Value
Data (EUR)	210, 225, 230, 235, 240, 245, 250, 2600 - the last is 260 mis-keyed	n = 8
Mean	4,235 / 8	529.4
Median	(235 + 240) / 2	237.5
25% trimmed mean	mean of the middle four: 230, 235, 240, 245	237.5
MAD	median of \|x - 237.5\| = 2.5, 2.5, 7.5, 7.5, 12.5, 12.5, 27.5, 2362.5	10.0
Robust sigma-hat	1.4826 x 10.0	14.8
Robust z of 2600	(2600 - 237.5) / 14.8	159
Classic z of 2600	SD = 836.8, so (2600 - 529.4) / 836.8	2.47
Mean after the fix	2600 corrected to 260: 1,895 / 8	236.9

Hypothetical insurance payouts, verified in Python. One keystroke moves the mean by nearly 300; the classic z of the bad row is below 3, so the 3-SD rule never flags it - the robust z is 159.

Below 11 rows, a 3-SD rule cannot fire at all Chart

n = 41.5
n = 62.04
n = 8the claim payouts above2.47
n = 102.85
n = 11first size where z = 3 is reachable3.02
n = 204.25

Bars: the largest |z| any row can reach in a group of n. With the sample SD no value in n rows can have |z| above (n - 1) / sqrt(n), however wrong it is: the outlier hides behind the spread it created. A 3-SD rule run per small group (per store-day, per sensor-hour) never fires below 11 rows. Computed in Python.

Breakdown points, and what robustness costs Table

Estimator	Breakdown point	Price on clean normal data
Mean	0%: one row can move it anywhere	none - it is the most efficient there
25% trimmed mean	25%: holds until a quarter of rows are bad	small efficiency loss
Median	50%: the best any estimator can do	about 64% efficiency vs the mean (2 / pi)
SD	0%: inflated by the outliers it should measure	none
1.4826 x MAD	50%	noisier than the SD when the data are clean

Breakdown point: the smallest share of corrupted rows that can drag the estimate arbitrarily far. The constant 1.4826 = 1 / Phi^-1(0.75) makes MAD estimate sigma for normal data.

Breakdown points, drawn Illustration

The mean moves from the first bad row (breakdown point 0). The 25% trimmed mean holds until a quarter of the rows are bad; the median until half.

The SD is inflated by the very rows it should help flag (14 clean, 656 at 10% bad). The MAD yardstick stays under 73 up to 48% bad and only breaks at 50% (1429).

Classic z of a 10x row falls below 3 once about 12% of rows are bad: the errors hide behind the spread they created. The robust z of the same row stays above 29 until the bad rows reach half the sample.

Seeded synthetic payment amounts: 200 values near 240 with SD 15; a growing, fixed set of rows is multiplied by 10 (a unit or extra-digit error). All estimates are computed from the contaminated sample shown.

A growing share of rows gets a 10x unit error. The mean leaves at once, the trimmed mean at 25%, the median at 50% - and the classic z stops flagging the bad rows long before that, because they inflate their own yardstick.

Detect with robust statistics, report what the question needs: sums, totals and models still see every raw value, so the diagnosis (error, rare truth, regime change) still has to happen.

Missingness mechanisms, formalizedadvanced5 minMCAR, MAR and MNAR as statements about P(M | Y), and what each one does to a complete-case estimate.

The three mechanisms as statements about one probability Formula

\begin{gathered}\text{MCAR: } P(M \mid Y_{\text{obs}}, Y_{\text{mis}}) = P(M) \\ \text{MAR: } P(M \mid \cdot) = P(M \mid Y_{\text{obs}}) \\ \text{MNAR: it depends on } Y_{\text{mis}}\end{gathered}

$M$: the missingness indicator: 1 where a value is absent, 0 where it is recorded
$Y_{\text{obs}}$: the values you can see: other columns, and the recorded part of this one
$Y_{\text{mis}}$: the values that are absent - under MNAR the chance of being missing depends on them
$P(M \mid \cdot)$: each mechanism is a claim about what this probability may depend on, not a property you can read off a table

Under MCAR the complete cases are a random subsample: unbiased, only n shrinks and intervals widen. Under MAR (given market here, product line in a bank) pooled rows are biased but each group is not, so conditioning on Y_obs removes it. Under MNAR selection depends on what you cannot see; no function of the observed rows removes it (van Buuren, section 1.2).

What a complete-case mean actually averages Formula

\begin{gathered}\bar y_{\text{cc}} \;=\; \frac{\sum_g n_g\, r_g\, \mu_g}{\sum_g n_g\, r_g} \\ \text{vs} \\ \mu \;=\; \frac{\sum_g n_g\, \mu_g}{\sum_g n_g}\end{gathered}

$g$: a segment defined by an observed column: product line, store, market
$n_g$: rows that should exist in segment g (recorded or not)
$r_g$: the share of segment g whose value is recorded (1 minus its missing rate)
$\mu_g$: the true mean in segment g; under MAR given g, the recorded rows estimate it without bias
$\bar y_{\text{cc}} - \mu$: zero when every r_g is equal (MCAR across segments) or every mu_g is equal; otherwise segments with high r_g are over-weighted

The fix under MAR follows from the formula: estimate each mu_g from its recorded rows, then weight by n_g, not by n_g x r_g. That is post-stratification, the simplest correct use of an observed column.

Worked by hand: a bank's average balance with MAR missingness Table

Segment	Rows n_g	Recorded r_g	True mean mu_g	Recorded rows
Retail cards	600	95%	EUR 2,000	570
SME loans	400	60%	EUR 12,000	240
Complete-case mean	-	-	(570 x 2,000 + 240 x 12,000) / 810	EUR 4,963
Re-weighted by n_g	-	-	(600 x 2,000 + 400 x 12,000) / 1,000	EUR 6,000
If MNAR inside SME	-	-	large SME balances missing more often: recorded SME mean < 12,000	still too low

Hypothetical portfolio, verified in Python. Dropping NULLs understates the average by 17% because SME loans, the high-balance segment, are recorded least. Re-weighting removes the MAR part only; MNAR inside a segment survives it.

Coverage checks3 minHow to find a gap that looks like a drop, and how close a per-row correction gets.

A coverage check in four steps Sequence

Expected calendar
Which (route, flight, date) rows should exist? From outside the table: schedule, last period's calendar or data contract.
Count present rows
Per route and month, after removing exact repeats. Count flight-days, not dates: one of two flights missing is a 50% gap.
Compare
Expected minus present = gap; 43 vs 62 flight-days on IST-TZX in March 2023.
Assert in the pipeline
Fail loudly when the gap is above a threshold, before the dashboard refreshes.

IST-TZX, March 2023 vs March 2022 Chart

Dashboard total43 flight-days against 62-21.4 %
Per flight-day present13.3 %
True total (complete table)from flight_day10 %

Bar length shows size; the value shows the sign. A 9-day gap in the feed turns growth into an apparent 21% fall. Per flight-day is a stopgap, not a fix: it assumes the missing days look like the present ones, so it lands near the truth, not on it.

Reading a distribution3 minWhich summary to report for each shape the profile can show.

What each shape asks you to do Table

Shape in the profile	Report	Next check
Right skew (claims, order values, fares)	median and quantiles; mean of the log	is the tail real or a unit error?
Isolated extreme value	nothing yet	other columns that must agree: capacity, quantity x price = revenue
Two or more peaks	summaries per group	which column separates the peaks?
Pile-up at a limit	share of rows at the limit	the measure is censored: sales are not demand
Heaping on round values	the raw counts	manual entry or a default value?
Spike at zero	zeros separately	true zero, missing coded as 0, or absent row?

Practitioner checklist. The histogram is the cheapest test of whether a single average describes the column at all.

Outliers, diagnosed3 minError, rare truth or regime change, decided by evidence from other columns and time.

Diagnose a surprising value before treating it Diagram

Surprising valueflagged by a robust z or a rule
Contradicted by other columns?
- yes: to Error
no
Level shift since a date?
- yes: to Regime change
- no: to Rare truth
Errorcorrect or null it with a logged rule
- to Log the decision
Regime changemodel the break (Day 21)
- to Log the decision
Rare truthkeep it; plan for it
Log the decisionrule, owner, rows affected

Deleting by threshold skips the diagnosis and removes the rare truths you most need to plan for.

Integrity rules and who owns them Table

Rule	Basis	Rows breaking it
passengers <= capacity	physics of the aircraft	7
load_factor = passengers / capacity	definition	1
bookings_final >= 0	definition	2
route in upper case, from a known list	data standard	124
fare above a floor	needs a business owner; not a habit	not checked

Rules from physics and definitions can be enforced automatically. Business thresholds need someone accountable for them.

Duplicates and versions2 minWhen DISTINCT is enough and when you must choose a version.

Exact repeats or conflicting versions? Diagram

Key repeated?
- yes: to Same values?
no
Grain is safe
Same values?
- no: to Choose a version
yes
Keep one copyDISTINCT is enough
Choose a versionlatest processing time, logged

Half the duplicated keys in flight_day_raw carry a late revenue update. Picking a version is a rule with an owner, not a DISTINCT, and it needs a load or processing timestamp; a table without one is a data-contract finding (Day 2).

Missingness mechanisms, testedadvanced4 minWhat the segment-rate test can and cannot tell you, and what each mechanism does to an average.

Same fares, three missingness mechanisms Illustration

Unequal rates reject MCAR. Equal rates do not prove it: the MNAR row looks just like MCAR here.

Conditioning on market repairs MAR. Nothing computed from the observed rows repairs MNAR.

Seeded synthetic fares: 6,000 flights in three markets. MCAR: 15% missing at random. MAR: rate set by market. MNAR: cheap fares withheld in every market. Errors in EUR.

Left, the only test the observed data allow. Right, how far two estimates of the mean fare land from the truth under each mechanism.

avg_fare missing, by market Chart

domestic1.5 %
intl-short1.4 %
intl-longrates differ: not MCAR6.7 %

flight_day_raw, 2022-2023. Averaging fares over complete rows under-weights intl-long, the highest-fare market.

One test, and what each outcome lets you conclude Hierarchy

Rates differ by segment?the only test available
- yesMCAR rejectedMAR or MNAR: data cannot say which
  - argue MARCondition on itanalyse within, impute from, re-weight by n_g
  - MNAR plausibleTest the decisionor recover records: imputing cannot fix MNAR
- noMCAR not rejectednot proven: MNAR can give equal rates
  - if truly MCARComplete cases OKunbiased; you lose only precision

MAR versus MNAR is an argument about the recording process (who skips the field, which feed drops rows), never a property you can read off the table. The demo above shows an MNAR mechanism with equal rates in every market.

When the mechanism might be MNAR, test the decision, not the data Diagram

Assume a shiftmissing fares 10% or 20% below their market's
Re-run the metriconce per assumed shift
Decision changes?
- no: to Report the decision
yes
Recover the recordsthey are worth the cost
Report the decisionwith the range of results

The assumptions are explicit and few. An insurer would assume unreported claim amounts run 10% or 20% above observed ones in the same policy class; here, missing fares sitting 10-20% below their market's.

Which issues move a change metricadvanced3 minA one-line derivation of why some issues cancel in a ratio and others are added in full.

When does a data issue move a change metric? Formula

\widehat{\text{YoY}} = \frac{(1+e_{1})\,T_{1}}{(1+e_{0})\,T_{0}} - 1 \;\approx\; \text{YoY} + (e_{1} - e_{0})

$T_0, T_1$: true totals in the base and current period
$e_0, e_1$: relative error of each recorded total: +0.008 for 0.8% duplicates, -0.30 for a 30% gap
$e_1 - e_0$: what reaches the change metric: the difference between periods, not the size of either

Issues that hit both periods alike cancel in a ratio; issues that hit one period are added almost one for one. Absolute totals see every error in full.

The ratio formula, run on two of today's issues Table

Issue	e_0 (March 2022)	e_1 (March 2023)	What reaches the change metric
9-day gap on IST-TZX	0	5,370 / 7,518 - 1 = -0.29	1.100 x 0.714 - 1 = -0.214: +10.0% reads as -21.4%
Duplicates, 0.8% in both years	+0.008	+0.008	e_1 - e_0 = 0: the ratio barely moves; totals are 0.8% high

7,518 is the true March 2023 total from the complete table. Run this arithmetic before cleaning anything: it ranks issues for the decision in minutes.

Ranking what matters2 minEffect on the decision, not row count, sets the order.

Ranking issues Map

Fix first

Few rows, big effect: the IST-TZX coverage gap.

Fix first

Many rows, big effect: a broken join or unit.

Backlog

Few rows, small effect: 7 impossible passenger counts.

Backlog with owner

Many rows, small effect here: missing fares.

Row count is the wrong ranking. The coverage gap touches 19 flight-days and reverses the decision.

Memory anchor

Rank issues by what they do to the decision metric, not by how many rows they touch - and count the rows that are not there.

Useful for

Deciding whether a surprising KPI movement (a store's weekly sales, a region's churn) is real before it reaches a decision meeting.
Writing data-quality assertions (coverage, keys, integrity) that run before every dashboard or model refresh - meter readings, claims feeds, order tables.
Choosing a missing-data strategy from the mechanism instead of from habit, for example income fields in a credit application.

Not suitable for

Proving a mechanism - EDA can reject MCAR, but MAR vs MNAR is an assumption you state, argue and stress-test.
Replacing a data contract - EDA finds today's issues; ownership and schema agreements (Day 2) prevent tomorrow's.

Traps

Absent rows read as zero

A gap in the feed (a store's POS upload, a smart meter offline for a week) produces a lower monthly sum that looks like a demand drop; lags and rolling windows built on the series later shift silently (Day 24).

How to avoid it

Compare present vs expected rows per entity and period before reporting any total or trend.

Cleaning without a decision

Hours go into fixing every oddity, the issue that reverses the conclusion is found last, and undocumented fixes make the numbers impossible to reproduce.

How to avoid it

Triage by effect on the decision metric; fix with written, re-runnable rules and log each one.

Senior reasoning

A senior does not present a list of twelve issues. They state the decision, show the one issue that changes it with a before-and-after number, name its mechanism and the residual uncertainty, and put the rest in a backlog with owners. They prefer recovering missing records over clever adjustment, and they turn every diagnosis into an automated check so the same issue cannot return unnoticed.

Interview question

"You get a new dataset and a week to build a model. What do you do on day one?" How would you structure the answer?

Outline your answer first.

Recall cards scheduled after completion

Define MCAR, MAR and MNAR, and say what complete-case analysis does under each.
Name the three kinds of outlier and the treatment for each; then name the cheapest check against fake trends.

Reviewed on a 1 · 3 · 7 · 14 · 30-day schedule; a miss returns the card to 1 day.

Prerequisites and related concepts

References

Flexible Imputation of Missing Data (2nd edition, online) — Stef van Buurencore
Definitions of MCAR, MAR and MNAR, and the point that simple fixes such as complete-case analysis are only generally valid under MCAR. (Section 1.2 (Concepts of MCAR, MAR and MNAR); section 1.3.1 (Listwise deletion))
Tidy Data — Hadley Wickham, 2014optional
One observation per row and one observational unit per table - the structural rules that duplicate keys and mixed grains violate.

Optional sections

Deep dive · 10 min

Cleaning choices change the mean and the trend. On flight_day_raw, three choices give three different stories:

The 10x entry error raises IST-FRA's December 2023 mean passengers per flight from 137.4 to 149.6 (+9%), while the median barely moves. A report that uses means needs the fix; a report that uses medians hides the error but does not remove it from sums or models.
Missing revenue (281 rows) understates the 2023 revenue SUM by about 6.4% on intl-long but only 1-2% on the other markets, so a market-mix chart shifts share away from intl-long. The average fare within each market is almost unchanged (602.9 vs 602.0 on intl-long), which is what MAR-given-market predicts.
The March 2023 gap turns a +10% route trend into -21%.

Duplicates with conflicting values. Of the 110 duplicate keys, 55 are exact copies and 55 carry a revenue about 2% higher - a late-arriving update. DISTINCT removes only the first kind. For the second you need a rule (latest load timestamp wins, or the finance-system value wins). Without a load timestamp you cannot apply the right rule, which is itself a finding for the data contract (Day 2) and for event vs processing time (Day 7).

Cardinality and casing. 124 rows with lower-case route codes create phantom routes in any GROUP BY route. A cardinality check (distinct values per categorical column, compared with the reference list) catches this before it splits a route's totals.

Hypothesis-driven, not exhaustive. Automated profilers are useful for the first pass, but they rank issues by count. Your ranking must come from the decision: write the question, the metric and what result would change your recommendation, then look.

Hands-on lab: Turn the profile into data-quality rules · 15 min

Run the rule query below. Each row is an assertion that should return 0 on clean data. Then:

For each non-zero check, classify it as error, rare truth or regime change, and write the fix rule.
Decide which checks should fail a pipeline run and which should only warn, given the March decision.
The "missing flight-days" check assumes every flight operates daily. Write down what you would need to know to make it correct for seasonal or cancelled services.

-- Each row is an assertion that should be 0 on clean data
SELECT 'duplicate (date, flight_no) keys' AS check_name, COUNT(*) AS rows_flagged
FROM (SELECT date, flight_no FROM flight_day_raw GROUP BY date, flight_no HAVING COUNT(*) > 1)
UNION ALL
SELECT 'duplicate keys with conflicting revenue', COUNT(*)
FROM (SELECT date, flight_no FROM flight_day_raw GROUP BY date, flight_no HAVING COUNT(DISTINCT revenue) > 1)
UNION ALL
SELECT 'missing flight-days (assumes every flight operates daily)',
       (SELECT COUNT(DISTINCT date) FROM flight_day_raw) * (SELECT COUNT(DISTINCT flight_no) FROM flight_day_raw)
       - (SELECT COUNT(*) FROM (SELECT DISTINCT date, flight_no FROM flight_day_raw))
UNION ALL
SELECT 'avg_fare missing', SUM(avg_fare IS NULL) FROM flight_day_raw
UNION ALL
SELECT 'passengers > capacity', SUM(passengers > capacity) FROM flight_day_raw
UNION ALL
SELECT 'load_factor <> passengers / capacity', SUM(ABS(load_factor - 1.0 * passengers / capacity) > 0.001) FROM flight_day_raw
UNION ALL
SELECT 'negative bookings_final', SUM(bookings_final < 0) FROM flight_day_raw
UNION ALL
SELECT 'route not upper-case', SUM(route <> UPPER(route)) FROM flight_day_raw;

Interview practice · 10 min

An e-commerce dashboard shows weekly orders in one region down 21%. Your manager wants the explanation in the leadership deck in an hour. What do you check, in what order, and what do you send?

Answer as you would in the interview.

Content version 6 · last verified 2026-10-11 · depends on sqlite

1 Recall · 5 min

2 Concept · 10 min