Practical insights
- 1000published
- 25in review
- 8topic families
Results
- Write down the grain of every table and assert it before the first aggregate
You start a KPI query on an unfamiliar table, or a table whose definition changed recently.
easydocumented fact - Compare row counts before and after every join to catch join explosions early
A query joins a flight-level table to a table with several rows per flight, and the resulting passenger total looks plausible but higher than finance reports.
easypractitioner heuristic - Aggregate each fact table to a shared grain before joining two facts together
You need bookings and ancillary revenue per flight, and both live in tables with many rows per flight.
mediumpractitioner heuristic - Put filters on the optional table in the ON clause, or your LEFT JOIN becomes an inner join
A query LEFT JOINs flights to promotions to keep all flights, then adds WHERE promo.channel = 'web'.
easydocumented fact - Decide whether NULL means zero before trusting AVG or COUNT(column)
An average fare or average delay is computed with AVG over a column that contains NULLs.
easydocumented fact - Use NOT EXISTS instead of NOT IN when the subquery column can contain NULL
A query finds customers who never booked with WHERE customerid NOT IN (SELECT customerid FROM bookings), and returns zero rows.
mediumdocumented fact - Specify ROWS and a unique ORDER BY in running totals, because the default frame groups ties
A running total of bookings uses SUM(...) OVER (ORDER BY bookingdate) and several rows share the same date.
mediumdocumented fact - Densify dates before a ROWS-based rolling window, because "7 rows" is not "7 days" when days are missing
A 7-day rolling average of passengers uses AVG(...) OVER (PARTITION BY route ORDER BY date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) on a table with gaps.
mediumdocumented fact - Partition lag features by entity and shift them by the forecast horizon, not by one row
A training table adds LAG(passengers) OVER (ORDER BY date) as a feature for a model that forecasts 14 days ahead.
mediumpractitioner heuristic - Report business metrics by event time and pipeline health by processing time
A dashboard counts bookings by the timestamp at which records landed in the warehouse.
mediumdocumented fact - Deduplicate on a business key and keep the latest version by update time, not with DISTINCT
A raw table contains repeated rows for the same flight and date, some identical and some with a revised revenue value.
easypractitioner heuristic - Join facts to the dimension version that was valid at event time, not to current attributes
A route was reclassified from domestic to intl-short last year, and a revenue-by-market report joins all history to the current route table.
mediumdocumented fact - Build training rows with as-of joins so each feature uses only values known at the cutoff
A training table joins each departure to the latest customer and route features in the warehouse.
mediumdocumented fact - Never sum across snapshots of a cumulative measure; pick one snapshot per entity
A booking-curve table stores cumulative bookings at several days before departure, and a query sums cumulativebookings per flight.
easypractitioner heuristic - Store instants in UTC and assign the business date with the local zone of where it happened
Bookings from several countries are aggregated to daily totals using the server's clock or a UTC date.
mediumdocumented fact - Expect 23-hour and 25-hour days in hourly data around daylight-saving changes
An hourly demand or call-volume series in local time shows a missing hour in spring and a doubled hour in autumn.
mediumdocumented fact - Normalize categorical keys and compare their cardinality with a reference list before grouping
A GROUP BY route returns more distinct routes than the network has, including near-identical codes.
easypractitioner heuristic - Check whether missingness depends on other columns before dropping incomplete rows
An analysis drops rows with missing fare or revenue because "only 3% are missing".
mediumdocumented fact - Distinguish "zero", "unknown" and "not applicable" instead of letting one NULL mean all three
A cancellations column is NULL for some flights, and analysts disagree on whether that means no cancellations, missing data or a flight that never operated.
easypractitioner heuristic - Classify an outlier as error, rare truth or regime change before deciding what to do with it
One flight shows ten times its usual passengers, or one day's sales are far above every previous day.
mediumdocumented fact - Encode impossible values as explicit integrity rules that quarantine rows and count them
EDA finds load factors above 1 and negative bookings in a raw table that feeds a forecast.
easypractitioner heuristic - Track distinct-value counts per column as an early warning for upstream changes
A model or dashboard depends on categorical columns fed by systems you do not own.
easypractitioner heuristic - Agree a data contract at the producer boundary before building on a shared table
Your forecast or KPI starts consuming a table owned by another team, with no written agreement about it.
mediumpractitioner heuristic - Check row volume per entity and period to find gaps that schema tests cannot see
A table passes all type and null checks, yet a route's history has a hole of several days.
easypractitioner heuristic - Reconcile a new analytical table to an independent trusted total before anyone uses it
You have built a new revenue or passengers table for analysis or model training, and it will feed decisions.
easypractitioner heuristic - Compute rate KPIs as a ratio of sums unless you deliberately want the average of unit rates
Network load factor is reported as AVG(loadfactor) across flights, while finance computes passengers divided by seats.
easypractitioner heuristic - Cast to a decimal type before dividing integer counts in SQL
A conversion-rate query divides COUNT of bookings by COUNT of searches and returns 0 for every route.
easydocumented fact - Choose the chart from the comparison the reader must make, not from the data type
You need to show revenue share by fare level across three markets and the default tool suggests three pie charts.
easypractitioner heuristic - Start bar axes at zero, and switch to dots or lines when you need to show small differences
A bar chart of route load factors starts its axis at 75% so the differences look clearer.
easydocumented fact - Replace a spaghetti line chart with small multiples that share the same axes
A chart shows weekly bookings for twelve routes as twelve overlapping coloured lines.
easypractitioner heuristic - Write chart titles that state the finding and its scope, not the variables plotted
A slide is titled "Bookings by month, 2023-2024" and the presenter explains the point verbally.
easypractitioner heuristic - Index series to a common base period instead of using a dual-axis chart
You want to compare growth of revenue (in millions) with passengers (in thousands) on one chart.
easypractitioner heuristic - Use a log scale for ratios and multiplicative growth, and label the original values
A chart compares route revenue that spans from thousands to millions, or shows growth multiples above and below 1.
easypractitioner heuristic - Flag capacity-constrained observations before treating sales as demand
A forecast or price analysis uses passengers or units sold as the target, and some flights sold out or some stores ran out of stock.
mediumempirical finding - Decompose a revenue change into volume, price and mix before explaining it
Quarterly revenue rose 8% and leadership asks why, while passengers, average fares and the route mix all changed.
mediumpractitioner heuristic - Spell out the window frame when you use LAST_VALUE or NTH_VALUE
A query takes the "latest fare in the booking window" with LASTVALUE(fare) OVER (PARTITION BY flightno ORDER BY snapshotday) and returns the current row's own fare on every row.
easydocumented fact - Choose ROW_NUMBER, RANK or DENSE_RANK by deciding what a tie should do
A report asks for the top 3 routes per month by revenue, and two routes tie for third place in some months.
easydocumented fact - Filter on a window function result in an outer query, because WHERE runs before windows
You want only each flight's most recent snapshot and write WHERE ROWNUMBER() OVER (...) = 1 in the same SELECT, or you add a WHERE condition and expect the window to still see all rows.
mediumdocumented fact - Compute a median or percentile over the underlying rows, not as an average of group medians
A dashboard shows median fare per route and a stakeholder asks for the network median, so someone averages the eleven route medians.
mediumdocumented fact - Allocate header-level amounts to the line grain with a written rule before summing by line attributes
An order-like header carries a booking fee or baggage charge once, and a line table has one row per flight segment; the report sums revenue by route from the joined result.
mediumdocumented fact - Model a many-to-many relationship with a bridge table and state how weights prevent double counting
A booking can carry several promotion codes, and an analyst joins bookings to a promo column to report revenue by promotion.
harddocumented fact - Use a coverage table and an anti-join to count what did not happen
Someone asks which scheduled flights had no bookings in a week, or which customers received no offer, and the only table you have lists events that occurred.
mediumdocumented fact - Use IS NOT DISTINCT FROM when NULL keys should match in a join or a comparison
You reconcile two extracts by joining on route and fareclass, and rows where fareclass is NULL in both tables vanish from the matched set.
mediumdocumented fact - Count a right-hand key, not COUNT(*), after a LEFT JOIN
A query LEFT JOINs flights to ancillary sales and reports the number of sales per flight with COUNT(), and flights with no sales show 1.
easydocumented fact - A negated filter drops NULL rows, so write the NULL branch explicitly
A query keeps "flights not affected by an event" with WHERE event < 'strike', and the analyst expects every other flight to remain.
easydocumented fact - Do not rely on a UNIQUE constraint to stop duplicates when a key column can be NULL
A table has UNIQUE(customerid, promocode), promocode is nullable, and the nightly load keeps inserting "the same" customer row with no promo.
mediumdocumented fact - In SQLite, treat dates as text with a fixed format and never mix representations in one column
You query the airline sample database, where the date column is TEXT, and a join or filter mixes ISO strings with strings such as 3/4/2023 or with Unix timestamps from another source.
easydocumented fact - Do money arithmetic in an exact numeric type and compare floats with a tolerance
A reconciliation sums millions of fare amounts stored as double precision, and a total differs from the ledger by a few cents that move when the query plan changes.
easydocumented fact - Pick the slowly-changing-dimension type per attribute by asking whether history may change past answers
A product team asks for the route dimension to be made "type 2", and every attribute, including a typo-corrected name and a reorganised sales region, starts creating new versions.
mediumdocumented fact - Store validity intervals half-open, and join with start <= t < end
A type 2 dimension stores validfrom and validto as dates, and one version ends on 2024-03-01 while the next begins on 2024-03-01.
mediumpractitioner heuristic - Move fast-changing attributes into a mini-dimension instead of versioning the whole dimension
A customer dimension with millions of rows has a loyalty tier, risk band and credit-limit band that change monthly, and every change makes a type 2 copy of the whole row.
harddocumented fact - Schedule a snapshot of a mutable source at least as often as it changes, and handle deleted rows explicitly
An operational table holds a booking's current status and is overwritten in place, and a weekly job snapshots it to give "status history".
mediumdocumented fact - Truncate timestamps in an explicit time zone, because date_trunc on a timestamptz uses the session zone
A daily booking count is built with datetrunc('day', createdat), and the same query returns different daily totals when run from a notebook and from a scheduled job.
mediumdocumented fact - Add calendar days and elapsed hours with different interval types
A feature computes "same time next day" with + interval '24 hours', and on the day clocks change the result is off by an hour from the local departure schedule.
mediumdocumented fact - Store the zone name for a future local event, not the offset you know today
A schedule table stores departure times for next year as timestamps with a fixed offset computed at load time, and a government changes its daylight-saving rule.
harddocumented fact - Disambiguate repeated local times with an offset or fold before parsing wall-clock timestamps
A gate-system export has local timestamps without offsets, and on the night clocks go back the hour 01:00-02:00 appears twice.
harddocumented fact - Pair the ISO week number with the ISO week-year, never with the calendar year
A weekly report groups by EXTRACT(year ...) and EXTRACT(week ...), and the last days of December show up as week 1 next to January's data.
easydocumented fact - Keep two clocks for corrected facts, when it was true and when you learned it
Finance restates last month's revenue after a refund batch is posted, and the dashboard owner overwrites the monthly figures; a model trained on last month's numbers can no longer be reproduced.
hardpractitioner heuristic - Set the late-data allowance from the measured arrival-delay distribution
A streaming job aggregates bookings by event time with an arbitrary 10-minute allowance, and the daily total disagrees with the nightly batch by a stable fraction of a percent.
hardpractitioner heuristic - Parameterize each run by its data interval and never read "now" inside a transformation
A daily aggregation filters on CURRENTDATE - 1, and a rerun two days later after a failure silently computes a different day.
mediumdocumented fact - Deduplicate the incoming batch before an upsert, because one statement cannot update a row twice
A load runs INSERT ... ON CONFLICT DO UPDATE from a staging table that contains two versions of the same booking, and the job fails or, in another engine, keeps an arbitrary version.
mediumdocumented fact - Attach an idempotency key to events emitted by retried producers and deduplicate on it downstream
A booking service retries a payment event when a request times out, and some payments are recorded twice in the event table.
mediumdocumented fact - Build hashed surrogate keys from delimited, null-aware fields
A model joins on a hash built by concatenating flightno, date and fareclass, and two different rows produce the same key or a NULL field turns the whole key NULL.
mediumdocumented fact - Break ties explicitly when choosing the latest row per key
A customer table receives two updates in the same second, and the "latest" row chosen with DISTINCT ON or ROWNUMBER ORDER BY updatedat differs between runs.
mediumdocumented fact - Match near-duplicate entities with scored comparisons and a clerical-review band
Two passenger files should be merged, names and birth dates are keyed by hand, and exact-match deduplication leaves obvious duplicates while a loose rule merges different people.
harddocumented fact - Sample by a hash of the entity key so a sample stays consistent across tables and reruns
You need a 5% development sample of bookings and their payments, and sampling each table with its own random draw leaves most bookings without their payments.
mediumpractitioner heuristic - Do not treat LIMIT or TABLESAMPLE SYSTEM as a random sample, and use reservoir sampling for streams
An analyst profiles a billion-row table with SELECT LIMIT 100000 or TABLESAMPLE SYSTEM (1) and reports distributions from it.
mediumdocumented fact - Plot the rows before trusting a summary table, because identical statistics can hide different shapes
A segment comparison table shows near-identical mean, standard deviation and correlation for two route groups, and the summary is about to be filed as "no difference".
easypractitioner heuristic - Try several histogram bin widths and check density plots for impossible values
A fare distribution is shown as one histogram with software-default bins, and a "second peak" is discussed in a meeting.
easydocumented fact - Replace rainbow palettes and many-color legends with ordered scales and direct labels
A route-by-month heat map uses a rainbow scale, and a line chart of eleven routes needs a legend to read.
easydocumented fact - Check downstream consumers through lineage before renaming or retyping a column
A source team renames fareamount to basefare in the booking table and says "only the mart uses it".
mediumpractitioner heuristic - Set data-test severity by blast radius and keep the failing rows
A pipeline has forty dbt-style tests that all fail the build, and a harmless null in an optional column blocks the daily revenue refresh.
mediumdocumented fact - Page on actionable conditions only, and keep everything else on the dashboard
A data-platform team routes every failed check, slow query and late table to a chat alert, and the channel is muted within a month.
easydocumented fact - Show freshness and metric definitions on the dashboard itself
An executive dashboard shows "bookings today", the upstream load is two hours late, and viewers compare it to a figure from another team that defines a booking differently.
easypractitioner heuristic - Record run-level lineage so an incident can be traced to the job run that produced a bad table
A revenue table shows a spike, and the team cannot tell which of three overnight jobs, running against which input versions, wrote it.
mediumdocumented fact