DEA-C02 Data Transformation Practice Question
A data engineer needs to join a large fact table to a small dimension table that changes slowly. The dimension has a `valid_from` and `valid_to` timestamp, and the fact rows have an `event_ts`. The engineer wants to avoid scanning the entire dimension for every fact row and must ensure the join uses the correct validity window. Which approach is most appropriate?
⚠ Common exam trap
The trap here is believing that a non-equi join or a simple NULL filter can efficiently handle SCD validity windows, when ASOF JOIN is the optimized construct for this pattern.
Answer choices
Why each option matters
Answer the question above first, then reveal the full breakdown to understand why each option is right or wrong.
Correct answer & explanation
✓
Use an ASOF JOIN with `MATCH_CONDITION (event_ts >= valid_from)` and equality on the dimension key.
ASOF JOIN is purpose-built for joining time-series data to slowly changing dimensions by matching the closest preceding record. It uses MATCH_CONDITION to compare timestamps and equality predicates for the key, avoiding full scans and Cartesian products. The other options either ignore historical validity or rely on inefficient join types that do not scale for large fact tables.
Answer analysis
Option-by-option breakdown
For each option: why learners choose it and why it is or isn't the right answer here.
- ✗
Use a LEFT OUTER JOIN on the dimension key and filter with `valid_to IS NULL` to get the current record.
Why it's wrong here
Filtering on `valid_to IS NULL` returns only the current dimension record, ignoring historical validity. The fact rows may belong to older versions, so this approach produces incorrect attributions for historical events and does not respect the `valid_from`/`valid_to` window required by the scenario.
- ✓
Use an ASOF JOIN with `MATCH_CONDITION (event_ts >= valid_from)` and equality on the dimension key.
Why this is correct
ASOF JOIN is designed for time-series lookups where the fact timestamp must fall on or after a dimension timestamp. By matching on the dimension key and using MATCH_CONDITION, it efficiently finds the most recent valid dimension row without scanning all validity windows, which is exactly the SCD lookup pattern needed here.
- ✗
Use a non-equi join with `event_ts BETWEEN valid_from AND valid_to` and rely on the optimizer to prune partitions.
Why it's wrong here
A non-equi join on BETWEEN prevents the optimizer from using hash join efficiently and often results in a nested loop or full scan of the dimension. While it may work for very small dimensions, it does not scale and does not guarantee partition pruning, making it a poor choice for a large fact table.
- ✗
Use a CROSS JOIN with a WHERE clause that filters on the dimension key and timestamp range.
Why it's wrong here
A CROSS JOIN generates a Cartesian product before filtering, which is extremely expensive and can explode intermediate row counts. Even with a WHERE clause, the optimizer may not push the filter effectively, and this approach is not suitable for large fact tables or for correctly handling overlapping validity windows.
About these practice questions
This DEA-C02 question is part of Courseiva's 229-question bank — original exam-style content with full explanations and wrong-answer analysis, never real exam questions or exam dumps. Learn why practice questions differ from exam dumps →
JA
Written and reviewed by Johnson Ajibi, MSc IT Security
Senior Network & Security Engineer · founder of Courseiva
Last reviewed September 2026 · checked against the official Snowflake exam blueprint
This DEA-C02 practice question is part of Courseiva's free Snowflake certification practice question bank. Courseiva provides original exam-style practice questions with explanations, topic-based practice, mock exams, readiness tracking, and study analytics to help learners prepare for the DEA-C02 exam.