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 - 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 - Mark recent periods as provisional until late-arriving data has had time to land
Yesterday's bookings look 15% low on the morning dashboard, and someone escalates a demand drop.
mediumdocumented fact - Make every load and backfill idempotent so a rerun cannot double-count
A failed nightly job is rerun manually, or a month of history is backfilled after a logic fix.
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 - Keep facts whose dimension record has not arrived yet, using an explicit unknown member
Bookings for a newly opened route arrive before the route appears in the route dimension, and the reporting query uses an inner join.
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 - 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 - 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 - 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 source freshness against an explicit SLA, more often than the SLA itself
A morning revenue dashboard occasionally shows yesterday's numbers unchanged because an upstream load failed silently.
easydocumented fact - 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 - 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 - 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 - 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 - 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 - 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 - 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 - 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 - 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 - 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 - 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 - 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 - 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