DAA-C01 Question 32
Single answerASOF JOINSA market data team stores trade events in TRADES(symbol, trade_ts, trade_price) and quote updates in QUOTES(symbol, quote_ts, bid_price, ask_price). Analysts need a result set that attaches to each trade the most recent quote for the same symbol at or before the trade timestamp so they can calculate execution slippage against the prevailing market. The team wants a solution that is simple, accurate, and uses Snowflake SQL features designed for this time-series matching pattern. Which query best meets the requirement?
- A
SELECT t.symbol, t.trade_ts, t.trade_price, q.bid_price, q.ask_price FROM TRADES t ASOF JOIN QUOTES q MATCH_CONDITION(t.trade_ts >= q.quote_ts) ON t.symbol = q.symbol;
- B
SELECT t.symbol, t.trade_ts, t.trade_price, q.bid_price, q.ask_price FROM TRADES t JOIN QUOTES q ON t.symbol = q.symbol AND t.trade_ts >= q.quote_ts QUALIFY ROW_NUMBER() OVER (PARTITION BY t.symbol, t.trade_ts ORDER BY q.quote_ts DESC) = 1;
- C
SELECT t.symbol, t.trade_ts, t.trade_price, q.bid_price, q.ask_price FROM TRADES t ASOF JOIN QUOTES q ON t.symbol = q.symbol AND t.trade_ts >= q.quote_ts;
- D
SELECT t.symbol, t.trade_ts, t.trade_price, q.bid_price, q.ask_price FROM TRADES t LEFT JOIN QUOTES q ON t.symbol = q.symbol AND t.trade_ts = q.quote_ts;
Show answer and explanation
Correct answer: A
Explanation
Snowflake ASOF JOIN is intended for time-series use cases where each row on the left should be matched to the nearest qualifying row on the right according to an ordered timestamp relationship, such as the latest quote before a trade. The correct pattern is to place the temporal comparison in MATCH_CONDITION and the business key, such as symbol, in the ON clause. This is both clearer and more aligned with Snowflake best practices than building a many-to-many join and reducing it with window functions. Option 2 is a common fallback technique and may return a correct result set, but it is not the best answer when Snowflake provides a purpose-built ASOF JOIN construct. Option 3 reflects a syntax misunderstanding, and Option 4 reflects a common data-model mistake of assuming exact timestamp equality. Refer to Snowflake SQL documentation for ASOF JOIN syntax and examples showing MATCH_CONDITION for temporal alignment and ON for grouping keys.
- A. Correct.
Correct. This uses Snowflake's ASOF JOIN syntax as intended for time-series alignment. The MATCH_CONDITION expresses the temporal relationship needed: the quote timestamp must be less than or equal to the trade timestamp. The ON clause restricts matching to the same symbol. ASOF JOIN is specifically designed to return the closest qualifying row from the right-side table based on the temporal condition, which makes it the best fit for attaching the latest quote at or before each trade.
- B. Incorrect.
Incorrect. This pattern can often produce the desired result by joining all earlier quotes and then filtering to the latest one with ROW_NUMBER and QUALIFY. However, the question asks for the solution that uses the Snowflake feature designed specifically for this problem. While workable, this approach is more verbose and can be less clear than ASOF JOIN for nearest-prior time-series matching. It tests a common misconception that a manually ranked regular join is equivalent to the best native construct.
- C. Incorrect.
Incorrect. In Snowflake, ASOF JOIN requires the temporal predicate to be specified in MATCH_CONDITION, not embedded inside the ON clause. The ON clause is used for equality-style grouping keys such as symbol. This option looks plausible because it resembles normal join syntax, but it is not the correct ASOF JOIN pattern.
- D. Incorrect.
Incorrect. An equality join on timestamps only returns rows where trade_ts and quote_ts are exactly the same. In real market data, trades and quotes rarely share identical timestamps, so this would miss most matches and would not satisfy the requirement to find the most recent quote at or before the trade time.