{"course":{"slug":"data-and-sql","title":"Data, SQL and Getting to the Answer","url":"https://addaly.com/learn/data-and-sql","level":"beginner","track":"build"},"lesson":{"slug":"as-of-joins","title":"As-of joins: the value that was true at the time","url":"https://addaly.com/learn/data-and-sql/as-of-joins","position":43,"of":66,"minutes":10,"answer":"A fact that changes must be joined as of the moment the other row happened, through an effective-dated table with half-open periods that you have checked for overlaps and gaps, or through a latest-before lookup.","keyIdea":"A fact that changes must be joined as of the moment the other row happened, through an effective-dated table with half-open periods that you have checked for overlaps and gaps, or through a latest-before lookup."},"body":{"format":"markdown","text":"## The report that rewrites history\n\nA product cost ₹400 in January, ₹450 from March, and ₹500 from July. A report of margin by month joins each order to the `products` table, reads `cost`, and every January order is now shown with a cost of ₹500. Run the same report in December and it disagrees with the version run in June. Nothing was wrong with the join. The join was to the *current* value of a fact that changes, and the question was about the value at the time of the order.\n\nThe lesson on keeping each fact in one place showed one answer: copy the value onto the order row at the moment it happens, the way an invoice carries its own tax rate. That is right when the row is a legal record. It is not available when the history lives in a separate table of changes, which is the usual case for prices, exchange rates, tax rules, staff assignments and customer segments. Then the join itself has to be time-aware.\n\n> Figure: A cost that changed twice, and a report that rewrote history. Timeline: 1 January, The product's cost is recorded as 400 rupees; 14 February, An order is placed. Its margin should be computed against 400; 1 March, The cost rises to 450. The February order did not change; 1 July, The cost rises to 500; December, A join to products reports the February order at a cost of 500, and disagrees with the same report run in June. Nothing was wrong with the join. It was a join to the current value of a fact that changes, asked about a moment in the past. Either copy the cost onto the order when it happens, or join to an effective-dated table through the period that contains the order's date.\n\n## An effective-dated table\n\nThe clean shape stores each version of the fact with the period it was true for:\n\n```sql\nCREATE TABLE product_costs (\n  product_id  bigint  NOT NULL,\n  valid_from  date    NOT NULL,\n  valid_to    date,             -- NULL means \"still current\"\n  cost        numeric NOT NULL\n);\n```\n\nHalf-open periods, as with every date range in this course: a row is in force from `valid_from` up to but not including `valid_to`. The join then asks for the version whose period contains the order's date:\n\n```sql\nSELECT o.order_id, o.placed_at, pc.cost\nFROM orders o\nJOIN product_costs pc\n  ON  pc.product_id = o.product_id\n  AND o.placed_at >= pc.valid_from\n  AND (o.placed_at < pc.valid_to OR pc.valid_to IS NULL);\n```\n\nEach order matches exactly one cost row, provided the periods for a product neither overlap nor leave gaps. That proviso is the whole risk, and it gets its own check below.\n\n## When there is only a start date\n\nOften the history table records only when each value began: a `price_changes` table with `changed_at` and the new price. There is no `valid_to` to test against. The version in force at a moment is then \"the latest change at or before that moment\", which is a top-1-per-group question with a date condition:\n\n```sql\nSELECT o.order_id, o.placed_at, pc.price\nFROM orders o\nJOIN LATERAL (\n  SELECT price FROM price_changes p\n  WHERE p.product_id = o.product_id AND p.changed_at <= o.placed_at\n  ORDER BY p.changed_at DESC\n  LIMIT 1\n) pc ON true;\n```\n\n`LATERAL` lets the subquery refer to the order it is being run for. It is correct and it runs once per order, which is fine for a hundred thousand and worth measuring for a hundred million. The window alternative is to compute `valid_to` once with `lead(changed_at) OVER (PARTITION BY product_id ORDER BY changed_at)`, turning the start-only table into an effective-dated one, and then use the range join above.\n\nDuckDB has this join as a first-class construct: `FROM orders o ASOF JOIN price_changes p ON o.product_id = p.product_id AND o.placed_at >= p.changed_at` picks the latest matching row per order without the subquery, and it is one of the strongest reasons to reach for DuckDB on a laptop. Pandas offers `merge_asof` for the same operation in Python.\n\n## Validating the history table\n\nAn effective-dated table that overlaps gives an order two cost rows, and the join multiplies; one with gaps gives an order none, and an inner join drops it. Both failures produce a plausible total. Check the table before trusting any join to it:\n\n```sql\n-- overlaps: a version starts before the previous one ends\nSELECT product_id, valid_from\nFROM (SELECT *, lag(valid_to) OVER (PARTITION BY product_id ORDER BY valid_from) AS prev_to\n      FROM product_costs) t\nWHERE valid_from < prev_to;\n\n-- gaps: a version starts after the previous one ended\n... WHERE valid_from > prev_to;\n```\n\nBoth should return no rows. Run them whenever the history table changes, because the person adding a new price does not think of themselves as editing a join.\n\n## Which is right: copy the value, or join as of?\n\nCopying at write time makes the row self-contained, survives the history table being wrong later, and is what auditors expect on a document. Joining as of read time keeps one source of truth, lets a mistaken price be corrected everywhere at once, and is what analysis usually wants. Many systems do both: the invoice carries the rate it was issued with, and the analytics query joins as of the date to ask what the rate *should* have been. The disagreement between the two is itself a report worth running."},"check":{"question":"A margin report joins each order to `products.cost` and is re-run every month. The January margin figure it shows in December is lower than the figure it showed in February for the same January orders. What is the most likely cause?","options":[{"text":"The join is fanning because `products` has gained duplicate rows over the year, inflating costs in proportion.","correct":false,"tempting":"Duplicate product rows would change the row count and the revenue as well as the cost; the symptom here is a shifted cost on the same rows."},{"text":"`cost` in `products` holds only the current value, so every re-run prices January's orders at whatever the cost is now.","correct":true},{"text":"The December run used a different session time zone, moving some January orders into February and out of the calculation.","correct":false,"tempting":"A zone shift changes which orders are in January by a few hours' worth, not the cost applied to the ones that stay."},{"text":"Postgres inlines the CTE differently after ANALYZE has run, and the two execution plans apply the cost column at different stages.","correct":false,"tempting":"Execution plans change speed, never results; the same join on the same data yields the same rows and values."}]},"prev":{"slug":"date-arithmetic-and-time-zones","title":"Dates, intervals and the two kinds of timestamp","url":"https://addaly.com/learn/data-and-sql/date-arithmetic-and-time-zones"},"next":{"slug":"messy-data","title":"Cleaning real data without destroying it","url":"https://addaly.com/learn/data-and-sql/messy-data"},"authorship":"Founder and series editor: Dr Mustafa Mun. Written by AI models to a written standard. Exam keys checked blind by a second model: 3,665 of 3,666 agreed. Academic review invited.","review":{"status":"Draft, open for academic review"},"cite":"Addaly (2026) 'As-of joins: the value that was true at the time', in Data, SQL and Getting to the Answer. Edited by M. Mun. Addaly. Available at: https://addaly.com/learn/data-and-sql/as-of-joins (Accessed: 13 October 2026).","licence":{"name":"CC BY-NC-SA 4.0","url":"https://creativecommons.org/licenses/by-nc-sa/4.0/"},"reader":{"schema":1,"title":"As-of joins: the value that was true at the time","courseTitle":"Data, SQL and Getting to the Answer","module":{"index":4,"slug":"windows-sequences-and-time","title":"Windows, sequences and time"},"answer":null,"keyIdea":"A fact that changes must be joined as of the moment the other row happened, through an effective-dated table with half-open periods that you have checked for overlaps and gaps, or through a latest-before lookup.","hero":{"kind":"figure","figure":{"t":"figure","label":"Figure","figure":{"type":"timeline","title":"A cost that changed twice, and a report that rewrote history","events":[{"when":"1 January","what":"The product’s cost is recorded as 400 rupees"},{"when":"14 February","what":"An order is placed. Its margin should be computed against 400"},{"when":"1 March","what":"The cost rises to 450. The February order did not change"},{"when":"1 July","what":"The cost rises to 500"},{"when":"December","what":"A join to products reports the February order at a cost of 500, and disagrees with the same report run in June"}],"caption":"Nothing was wrong with the join. It was a join to the current value of a fact that changes, asked about a moment in the past. Either copy the cost onto the order when it happens, or join to an effective-dated table through the period that contains the order’s date."},"alt":"Figure: A cost that changed twice, and a report that rewrote history. Timeline: 1 January, The product’s cost is recorded as 400 rupees; 14 February, An order is placed. Its margin should be computed against 400; 1 March, The cost rises to 450. The February order did not change; 1 July, The cost rises to 500; December, A join to products reports the February order at a cost of 500, and disagrees with the same report run in June. Nothing was wrong with the join. It was a join to the current value of a fact that changes, asked about a moment in the past. Either copy the cost onto the order when it happens, or join to an effective-dated table through the period that contains the order’s date."}},"blocks":[{"t":"h2","id":"the-report-that-rewrites-history","runs":[{"text":"The report that rewrites history"}]},{"t":"p","runs":[{"text":"A product cost ₹400 in January, ₹450 from March, and ₹500 from July. A report of margin by month joins each order to the "},{"text":"products","code":true},{"text":" table, reads "},{"text":"cost","code":true},{"text":", and every January order is now shown with a cost of ₹500. Run the same report in December and it disagrees with the version run in June. Nothing was wrong with the join. The join was to the "},{"text":"current","i":true},{"text":" value of a fact that changes, and the question was about the value at the time of the order."}]},{"t":"p","runs":[{"text":"The lesson on keeping each fact in one place showed one answer: copy the value onto the order row at the moment it happens, the way an invoice carries its own tax rate. That is right when the row is a legal record. It is not available when the history lives in a separate table of changes, which is the usual case for prices, exchange rates, tax rules, staff assignments and customer segments. Then the join itself has to be time-aware."}]},{"t":"h2","id":"an-effective-dated-table","runs":[{"text":"An effective-dated table"}]},{"t":"p","runs":[{"text":"The clean shape stores each version of the fact with the period it was true for:"}]},{"t":"code","lang":"sql","label":"SQL","text":"CREATE TABLE product_costs (\n  product_id  bigint  NOT NULL,\n  valid_from  date    NOT NULL,\n  valid_to    date,             -- NULL means \"still current\"\n  cost        numeric NOT NULL\n);"},{"t":"p","runs":[{"text":"Half-open periods, as with every date range in this course: a row is in force from "},{"text":"valid_from","code":true},{"text":" up to but not including "},{"text":"valid_to","code":true},{"text":". The join then asks for the version whose period contains the order’s date:"}]},{"t":"code","lang":"sql","label":"SQL","text":"SELECT o.order_id, o.placed_at, pc.cost\nFROM orders o\nJOIN product_costs pc\n  ON  pc.product_id = o.product_id\n  AND o.placed_at >= pc.valid_from\n  AND (o.placed_at < pc.valid_to OR pc.valid_to IS NULL);"},{"t":"p","runs":[{"text":"Each order matches exactly one cost row, provided the periods for a product neither overlap nor leave gaps. That proviso is the whole risk, and it gets its own check below."}]},{"t":"h2","id":"when-there-is-only-a-start-date","runs":[{"text":"When there is only a start date"}]},{"t":"p","runs":[{"text":"Often the history table records only when each value began: a "},{"text":"price_changes","code":true},{"text":" table with "},{"text":"changed_at","code":true},{"text":" and the new price. There is no "},{"text":"valid_to","code":true},{"text":" to test against. The version in force at a moment is then “the latest change at or before that moment”, which is a top-1-per-group question with a date condition:"}]},{"t":"code","lang":"sql","label":"SQL","text":"SELECT o.order_id, o.placed_at, pc.price\nFROM orders o\nJOIN LATERAL (\n  SELECT price FROM price_changes p\n  WHERE p.product_id = o.product_id AND p.changed_at <= o.placed_at\n  ORDER BY p.changed_at DESC\n  LIMIT 1\n) pc ON true;"},{"t":"p","runs":[{"text":"LATERAL","code":true},{"text":" lets the subquery refer to the order it is being run for. It is correct and it runs once per order, which is fine for a hundred thousand and worth measuring for a hundred million. The window alternative is to compute "},{"text":"valid_to","code":true},{"text":" once with "},{"text":"lead(changed_at) OVER (PARTITION BY product_id ORDER BY changed_at)","code":true},{"text":", turning the start-only table into an effective-dated one, and then use the range join above."}]},{"t":"p","runs":[{"text":"DuckDB has this join as a first-class construct: "},{"text":"FROM orders o ASOF JOIN price_changes p ON o.product_id = p.product_id AND o.placed_at >= p.changed_at","code":true},{"text":" picks the latest matching row per order without the subquery, and it is one of the strongest reasons to reach for DuckDB on a laptop. Pandas offers "},{"text":"merge_asof","code":true},{"text":" for the same operation in Python."}]},{"t":"h2","id":"validating-the-history-table","runs":[{"text":"Validating the history table"}]},{"t":"p","runs":[{"text":"An effective-dated table that overlaps gives an order two cost rows, and the join multiplies; one with gaps gives an order none, and an inner join drops it. Both failures produce a plausible total. Check the table before trusting any join to it:"}]},{"t":"code","lang":"sql","label":"SQL","text":"-- overlaps: a version starts before the previous one ends\nSELECT product_id, valid_from\nFROM (SELECT *, lag(valid_to) OVER (PARTITION BY product_id ORDER BY valid_from) AS prev_to\n      FROM product_costs) t\nWHERE valid_from < prev_to;\n\n-- gaps: a version starts after the previous one ended\n... WHERE valid_from > prev_to;"},{"t":"p","runs":[{"text":"Both should return no rows. Run them whenever the history table changes, because the person adding a new price does not think of themselves as editing a join."}]},{"t":"h2","id":"which-is-right-copy-the-value-or-join-as-of","runs":[{"text":"Which is right: copy the value, or join as of?"}]},{"t":"p","runs":[{"text":"Copying at write time makes the row self-contained, survives the history table being wrong later, and is what auditors expect on a document. Joining as of read time keeps one source of truth, lets a mistaken price be corrected everywhere at once, and is what analysis usually wants. Many systems do both: the invoice carries the rate it was issued with, and the analytics query joins as of the date to ask what the rate "},{"text":"should","i":true},{"text":" have been. The disagreement between the two is itself a report worth running."}]}],"check":{"question":[{"text":"A margin report joins each order to "},{"text":"products.cost","code":true},{"text":" and is re-run every month. The January margin figure it shows in December is lower than the figure it showed in February for the same January orders. What is the most likely cause?"}],"options":[{"text":[{"text":"The join is fanning because "},{"text":"products","code":true},{"text":" has gained duplicate rows over the year, inflating costs in proportion."}],"correct":false,"tempting":[{"text":"Duplicate product rows would change the row count and the revenue as well as the cost; the symptom here is a shifted cost on the same rows."}]},{"text":[{"text":"cost","code":true},{"text":" in "},{"text":"products","code":true},{"text":" holds only the current value, so every re-run prices January’s orders at whatever the cost is now."}],"correct":true,"tempting":null},{"text":[{"text":"The December run used a different session time zone, moving some January orders into February and out of the calculation."}],"correct":false,"tempting":[{"text":"A zone shift changes which orders are in January by a few hours’ worth, not the cost applied to the ones that stay."}]},{"text":[{"text":"Postgres inlines the CTE differently after ANALYZE has run, and the two execution plans apply the cost column at different stages."}],"correct":false,"tempting":[{"text":"Execution plans change speed, never results; the same join on the same data yields the same rows and values."}]}],"order":[0,2,1,3],"why":null},"prev":{"slug":"date-arithmetic-and-time-zones","title":"Dates, intervals and the two kinds of timestamp","minutes":9},"next":{"slug":"messy-data","title":"Cleaning real data without destroying it","minutes":9}}}