Practical insights
- 1000published
- 25in review
- 8topic families
Results
- 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 - 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 - 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 - 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 - 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 - 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 - 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 - 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