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