DAA-C01 Question 31
Single answerASOF JOINSA financial analytics team stores trade events in TRADE_EVENTS(symbol, trade_ts, trade_id, qty) and quote updates in QUOTE_UPDATES(symbol, quote_ts, bid_price, ask_price). For each trade, the team needs the most recent quote for the same symbol at or before the trade timestamp to compute slippage. Some symbols have many quote updates per second, and there is not always an exact timestamp match between trades and quotes. Which SQL approach best meets this requirement in Snowflake?
- A
Use an ASOF JOIN between TRADE_EVENTS and QUOTE_UPDATES, matching on symbol and quote_ts <= trade_ts so each trade is paired with the nearest preceding quote for that symbol.
- B
Use an INNER JOIN on symbol and trade_ts = quote_ts, then aggregate with MAX(quote_ts) to recover the latest quote before each trade.
- C
Use a CROSS JOIN between trades and quotes for each symbol, then rely on QUALIFY ROW_NUMBER() without a timestamp comparison because ASOF semantics are implicit in window functions.
- D
Use a LEFT OUTER JOIN on symbol only, then select any quote row per symbol because Snowflake automatically chooses the temporally closest row when timestamps are present.
Show answer and explanation
Correct answer: A
Explanation
The key requirement is a point-in-time lookup: for each trade, return the most recent quote for the same symbol whose timestamp is less than or equal to the trade timestamp. In Snowflake, ASOF JOIN is the appropriate feature for this pattern because it is designed for time-series joins where the nearest preceding or following row is needed rather than an exact timestamp match. This is especially useful in financial, IoT, and event-stream analytics. Exact-match joins fail when timestamps differ slightly, and generic joins or cross joins can produce incorrect results or excessive intermediate data. Best practice is to define the business key equality condition, such as symbol, together with the ASOF temporal condition so the join returns the proper point-in-time row. This aligns with Snowflake documentation on ASOF JOIN for time-series analysis and nearest-match temporal joins.
- A. Correct.
Correct. ASOF JOIN is designed for time-series alignment when exact timestamp matches may not exist. In this scenario, the requirement is to match each trade with the latest quote at or before the trade time, partitioned by symbol. ASOF JOIN supports equality match conditions such as symbol plus a temporal match condition that finds the closest qualifying row based on timestamp ordering. This is the intended pattern for point-in-time enrichment of event data with the nearest prior observation.
- B. Incorrect.
Incorrect. An INNER JOIN on exact timestamp equality would miss most rows when trades and quotes rarely share identical timestamps. Adding MAX(quote_ts) afterward does not fix the core issue, because the exact-match join has already eliminated candidate quote rows that occurred before the trade but at different timestamps. This reflects a common misconception that aggregation can recover rows that were filtered out by an overly restrictive join condition.
- C. Incorrect.
Incorrect. A CROSS JOIN with window functions can sometimes be engineered to simulate nearest-prior matching, but without an explicit timestamp comparison it does not satisfy the business requirement. It is also far less efficient and more error-prone because it creates a large intermediate result set. ASOF semantics are not implicit in window functions; the logic must be explicitly defined. Snowflake provides ASOF JOIN specifically to avoid this complexity.
- D. Incorrect.
Incorrect. A regular LEFT OUTER JOIN on symbol only would create many quote matches per trade and does not automatically select the temporally closest or preceding row. Snowflake does not infer point-in-time matching from the presence of timestamp columns. The developer must specify temporal logic, and ASOF JOIN is the feature intended for this use case.