In this post we are going to cover ASOF JOIN in Snowflake.
ASOF JOIN answers one question. For every row on the left, find the nearest earlier row on the right. Not the row that matches, because there is nothing to match on. The row that was most recently in force. We write MATCH_CONDITION in place of ON, and that is the whole clause.
We will start with a price list whose price changes over time, which is the problem the clause exists for. Then we will add a key so two products stop reading each other's history, write the same question the old way with a correlated subquery, and see what happens to an order placed before the price history begins.
Every query below was run on a real Snowflake account, and every result you see is the output it gave back.
Episode 32 of the Snowflake SQL: Zero to Hero series on TechBrothersIT.
Let's start with a price list
Here is the sample data for this post, and there is a problem built into it. It is a price list, but the price is not a property of the product. It is a property of the product at a point in time. Each row says: from this date onwards, this is what it costs.
SELECT * FROM (
SELECT 'A-100' AS SKU, '2023-01-01'::DATE AS EFF_DATE, 280.00 AS PRICE
UNION ALL SELECT 'A-100', '2023-06-01'::DATE, 300.00
UNION ALL SELECT 'A-100', '2024-01-01'::DATE, 325.00
UNION ALL SELECT 'B-220', '2023-01-01'::DATE, 410.00
UNION ALL SELECT 'B-220', '2023-09-01'::DATE, 450.00
) ORDER BY SKU, EFF_DATE;| SKU | EFF_DATE | PRICE |
|---|---|---|
| A-100 | 2023-01-01 | 280 |
| A-100 | 2023-06-01 | 300 |
| A-100 | 2024-01-01 | 325 |
| B-220 | 2023-01-01 | 410 |
| B-220 | 2023-09-01 | 450 |
Five rows and three columns, covering two products. A-100 has three effective dates and B-220 has two. There is no end date on any row. A price is simply in force until the next one starts, which is how price histories are almost always stored.
Now here is the question. An order for A-100 was placed in March 2023. What did it cost?
We do not want the current price. We want the price that was in force on that date. And no join we have written so far can get it. There is no key to match on. The order date is not an effective date, so equality finds nothing, and a BETWEEN has no upper bound to use.
What we want is the latest row whose effective date is not in the future relative to the order.
Now let's write the ASOF JOIN
Snowflake has a join for precisely this. We write ASOF JOIN instead of JOIN, and then MATCH_CONDITION instead of ON.
SELECT O.ORDER_ID, O.ORDER_DATE, P.SKU, P.EFF_DATE, P.PRICE
FROM (SELECT ORDER_ID, ORDER_DATE FROM ORDERS WHERE ORDER_ID BETWEEN 1001 AND 1012) O
ASOF JOIN (
SELECT 'A-100' AS SKU, '2023-01-01'::DATE AS EFF_DATE, 280.00 AS PRICE
UNION ALL SELECT 'A-100', '2023-06-01'::DATE, 300.00
UNION ALL SELECT 'A-100', '2024-01-01'::DATE, 325.00
) P
MATCH_CONDITION (O.ORDER_DATE >= P.EFF_DATE)
ORDER BY O.ORDER_ID;| ORDER_ID | ORDER_DATE | SKU | EFF_DATE | PRICE |
|---|---|---|---|---|
| 1001 | 2023-01-12 | A-100 | 2023-01-01 | 280 |
| 1002 | 2023-01-28 | A-100 | 2023-01-01 | 280 |
| 1003 | 2023-02-14 | A-100 | 2023-01-01 | 280 |
| 1004 | 2023-02-27 | A-100 | 2023-01-01 | 280 |
| 1005 | 2023-03-09 | A-100 | 2023-01-01 | 280 |
| 1006 | 2023-03-22 | A-100 | 2023-01-01 | 280 |
| 1007 | 2023-04-05 | A-100 | 2023-01-01 | 280 |
| 1008 | 2023-04-19 | A-100 | 2023-01-01 | 280 |
| 1009 | 2023-05-02 | A-100 | 2023-01-01 | 280 |
| 1010 | 2023-05-21 | A-100 | 2023-01-01 | 280 |
| 1011 | 2023-06-08 | A-100 | 2023-06-01 | 300 |
| 1012 | 2023-06-25 | A-100 | 2023-06-01 | 300 |
Twelve rows and five columns. Each order is next to the price row that was in force when it was placed.
Read down the PRICE column and we can watch the price change. The first ten rows all carry 280, taken from the 2023-01-01 price row, because every one of those orders predates the next change.
Then order 1011, dated 2023-06-08, picks up 300 from the 2023-06-01 row, and so does 1012. The price changed and the join followed it. No row ever looked forward. Each one took the latest effective date that was not after its own order date.
That query deliberately narrows its right-hand side to A-100's three price rows and leaves out B-220 entirely. Compare the inline list in the SQL against the five-row grid above it and you will see. With one product's history on the right there is no key to get wrong, which keeps this first example about the time comparison alone. We put both products back once we have the ON clause.
So what is MATCH_CONDITION doing?
MATCH_CONDITION is not an ON clause with a different name, and that distinction is the whole clause. It is its own keyword and it is mandatory. An ASOF JOIN without one is not a valid statement.
It takes exactly one comparison, between one column on each side. And that one comparison does two jobs at once.
- It says which rows are eligible.
O.ORDER_DATE >= P.EFF_DATEmakes every price row dated on or before the order a candidate. - It says which one of them wins. Out of all those candidates the join keeps the closest, which is the greatest
EFF_DATEthat still satisfies the comparison. That is the part a plainONclause could never express, because an ordinary join keeps every row that matches, not the nearest one.
So the direction of the operator is the meaning of the query, not a detail. >= reads backwards in time. Give me what was already in force.
The documentation also allows the other comparison operators, so the mirror-image question, the nearest later row, is a matter of turning the comparison round. We captured no result for that, so nothing is shown for it here.
An ordinary non-equi join with the same condition is a different query, not a slower spelling of this one. JOIN ... ON O.ORDER_DATE >= P.EFF_DATE would keep every earlier price row for every order, so an order placed after two price changes would come back more than once. MATCH_CONDITION is what reduces that to one row per left row.
Now let's put both products back with ON
Two products with two separate price histories need one more thing, and it is the ON clause we already know. An ASOF JOIN accepts it as an optional extra alongside MATCH_CONDITION.
SELECT S.ORDER_ID, S.SKU, S.ORDER_DATE, P.EFF_DATE, P.PRICE
FROM (
SELECT 1001 AS ORDER_ID, 'A-100' AS SKU, '2023-03-10'::DATE AS ORDER_DATE
UNION ALL SELECT 1002, 'A-100', '2023-08-15'::DATE
UNION ALL SELECT 1003, 'B-220', '2023-03-10'::DATE
UNION ALL SELECT 1004, 'B-220', '2023-11-20'::DATE
UNION ALL SELECT 1005, 'A-100', '2024-05-01'::DATE
) S
ASOF JOIN (
SELECT 'A-100' AS SKU, '2023-01-01'::DATE AS EFF_DATE, 280.00 AS PRICE
UNION ALL SELECT 'A-100', '2023-06-01'::DATE, 300.00
UNION ALL SELECT 'A-100', '2024-01-01'::DATE, 325.00
UNION ALL SELECT 'B-220', '2023-01-01'::DATE, 410.00
UNION ALL SELECT 'B-220', '2023-09-01'::DATE, 450.00
) P
MATCH_CONDITION (S.ORDER_DATE >= P.EFF_DATE)
ON S.SKU = P.SKU
ORDER BY S.ORDER_ID;| ORDER_ID | SKU | ORDER_DATE | EFF_DATE | PRICE |
|---|---|---|---|---|
| 1001 | A-100 | 2023-03-10 | 2023-01-01 | 280 |
| 1002 | A-100 | 2023-08-15 | 2023-06-01 | 300 |
| 1003 | B-220 | 2023-03-10 | 2023-01-01 | 410 |
| 1004 | B-220 | 2023-11-20 | 2023-09-01 | 450 |
| 1005 | A-100 | 2024-05-01 | 2024-01-01 | 325 |
Five rows and five columns. A-100 in March takes 280, in August takes 300, and in May 2024 takes 325. B-220 in March takes 410 and in November takes 450.
Now look at the two orders both dated 2023-03-10, one per product. They are the same date and they get different prices, because each one searched only its own product's history. Neither product can pick up the other one's price.
That division of labour is the thing to remember about the clause.
| Clause | Required? | What it does |
|---|---|---|
MATCH_CONDITION | Mandatory | The time comparison. Decides which rows are eligible and which single one wins. |
ON | Optional | Ordinary equality on the key. Keeps each key searching only its own history. |
Leaving the ON clause out when the right-hand table holds more than one key's history is the mistake to watch for, and it does not raise an error. The join is perfectly valid with MATCH_CONDITION alone. It simply has no reason to care which product a price row belongs to.
How did we do this before ASOF JOIN existed?
With a correlated subquery. For each row, go and find the maximum effective date that is not later than this order's date.
SELECT S.ORDER_ID, S.SKU, S.ORDER_DATE,
(SELECT MAX(P.EFF_DATE) FROM (
SELECT 'A-100' AS SKU, '2023-01-01'::DATE AS EFF_DATE
UNION ALL SELECT 'A-100', '2023-06-01'::DATE
UNION ALL SELECT 'A-100', '2024-01-01'::DATE
UNION ALL SELECT 'B-220', '2023-01-01'::DATE
UNION ALL SELECT 'B-220', '2023-09-01'::DATE
) P WHERE P.SKU = S.SKU AND P.EFF_DATE <= S.ORDER_DATE) AS EFF_DATE
FROM (
SELECT 1001 AS ORDER_ID, 'A-100' AS SKU, '2023-03-10'::DATE AS ORDER_DATE
UNION ALL SELECT 1002, 'A-100', '2023-08-15'::DATE
UNION ALL SELECT 1003, 'B-220', '2023-03-10'::DATE
UNION ALL SELECT 1004, 'B-220', '2023-11-20'::DATE
UNION ALL SELECT 1005, 'A-100', '2024-05-01'::DATE
) S
ORDER BY S.ORDER_ID;| ORDER_ID | SKU | ORDER_DATE | EFF_DATE |
|---|---|---|---|
| 1001 | A-100 | 2023-03-10 | 2023-01-01 |
| 1002 | A-100 | 2023-08-15 | 2023-06-01 |
| 1003 | B-220 | 2023-03-10 | 2023-01-01 |
| 1004 | B-220 | 2023-11-20 | 2023-09-01 |
| 1005 | A-100 | 2024-05-01 | 2024-01-01 |
Five rows and four columns, and the EFF_DATE column is the same one the ASOF JOIN with the ON clause produced, row for row.
Let's be precise about what that shows. These two queries agreed on this data, on one run. That is a demonstration, not a proof that they agree in general. Edge cases such as duplicate effective dates for the same key are not exercised by this data at all.
And the case for the ASOF JOIN version here is readability, not speed. Nothing on this page was timed, so nothing is claimed about which runs faster. What is fair to say is which one states its intent. One of them names the thing it is doing. The other leaves the reader to reconstruct “nearest earlier row” out of a MAX and two correlated predicates.
The subquery form also fixes us to one output column at a time. It returns the effective date. Fetching the price alongside it means either a second correlated subquery or joining back to the price list on the date we just worked out. The ASOF JOIN hands us every column of the matched row at once, which is why its grid has a PRICE column and this one does not.
What if there is nothing earlier to match?
This is the edge case that decides whether we can trust the clause in a pipeline. What happens to a left row that has nothing earlier to match, such as an order placed before the price history begins?
The documentation answers it: “the result is null-padded for the selected columns from the right table. (ASOF joins are similar to left outer joins in this respect.)” So before running it, we expect both orders to come back, not one.
SELECT S.ORDER_ID, S.ORDER_DATE, P.EFF_DATE, P.PRICE
FROM (
SELECT 9001 AS ORDER_ID, '2022-01-01'::DATE AS ORDER_DATE
UNION ALL SELECT 9002, '2023-07-01'::DATE
) S
ASOF JOIN (
SELECT '2023-01-01'::DATE AS EFF_DATE, 280.00 AS PRICE
UNION ALL SELECT '2023-06-01'::DATE, 300.00
) P
MATCH_CONDITION (S.ORDER_DATE >= P.EFF_DATE)
ORDER BY S.ORDER_ID;| ORDER_ID | ORDER_DATE | EFF_DATE | PRICE |
|---|---|---|---|
| 9001 | 2022-01-01 | NULL | NULL |
| 9002 | 2023-07-01 | 2023-06-01 | 300 |
Two rows and four columns. Order 9001, dated 2022-01-01, is kept with NULL in both of the columns that came from the right-hand side. Order 9002 matched normally. Documented, predicted, and confirmed.
An ASOF JOIN does not drop our left rows. That is the right default for a pipeline, because an order with no price is a data problem we want to see rather than a row we want to lose.
It does mean something, though. Every column we take from the right-hand side is nullable in the result, even when it is declared NOT NULL at source.
Anything computed from those columns inherits the NULL. Multiply order 9001's price by a quantity and the answer is NULL, not zero, and a SUM over the result will skip it silently. If a missing match matters, test for it. Filter on the right-hand side being NULL and you have an exception report of every row your reference data does not cover.
The shape worth remembering
Strip the example away and the join itself is three lines, and one of them is optional.
SELECT ...
FROM <left> L
ASOF JOIN <right> R
MATCH_CONDITION (L.<time> >= R.<time>) -- mandatory: the time
ON (L.<key> = R.<key>) -- optional: the keyIt answers one question and it answers it well. What was true at that moment? Any time a fact in one table is stamped with a date and a fact in another is valid from a date, this is the join. The list of places that happens is longer than it first looks.
- A price, a tax rate or an exchange rate in force when a transaction was recorded.
- The version of a slowly changing dimension row that was current when a fact arrived.
- A sensor reading or a market quote nearest before an event, where the timestamps will never line up exactly.
- Whichever configuration or entitlement was active on the date something was processed.
The limits are worth knowing before we build on it. MATCH_CONDITION takes one comparison between one column on each side, so it is not a place to put a second condition. That is what the ON clause is for. Left rows are kept and null-padded rather than dropped, so downstream arithmetic has to expect NULL. And if two right-hand rows share the same key and the same effective date, nothing on this page tells us which one we get, because this data never produced that case.
Key points
ASOF JOINanswers one question: for each left row, find the nearest row on the right in one direction of time.MATCH_CONDITIONis mandatory and is not anONclause. It takes one comparison, and it decides both which rows are eligible and which single one wins.- The direction of the operator is the meaning of the query.
>=asks for what was already in force. - An ordinary
JOIN ... ON L.date >= R.datereturns every earlier row, not the nearest one. That is a different result, not a slower spelling of the same one. - The
ONclause is optional and does the key: it is what stops one product reading another product's price history. Leaving it out raises no error. - A left row with nothing earlier to match is kept and null-padded, like a left outer join. Snowflake's documentation says so and the captured grid confirms it.
- So every column taken from the right-hand side is nullable in the result, and anything computed from one inherits the
NULL. - It replaces a correlated
MAXsubquery and returns every column of the matched row at once instead of one value per subquery. - The two forms returned the same five effective dates here, on this data, once. That is a demonstration, not a proof of general equivalence, and nothing was timed.
In the next post we will cover LATERAL. It is a subquery in the FROM clause that can see the row it is running for, which is the general version of the trick ASOF JOIN packages up.
Every query and every result grid on this page was captured from a live Snowflake account (version 10.34.101), including the null-padded row. The price history is written inline in the queries that use it and is shown in full as the first grid; the order rows come either from this course's invented ORDERS demo data or from an inline list in the query itself. This episode captures no SELECT * of ORDERS, so no row count is claimed for it — every count on this page is of a grid printed beside it. The equivalence between the two forms is a single observed agreement on this data, not a general claim, and no performance claims are made anywhere in this series: nothing here was timed. Clause behaviour, including the quoted sentence on null padding, is taken from the Snowflake ASOF JOIN documentation.
No comments:
Post a Comment
Note: Only a member of this blog may post a comment.