You want to combine is_incremental() with a unique key check to avoid duplicate inserts. Which snippet correctly implements this?
hard📝 Application Q9 of 15
dbt - Incremental Models
You want to combine is_incremental() with a unique key check to avoid duplicate inserts. Which snippet correctly implements this?
A{% if is_incremental() %}
DELETE FROM {{ this }} WHERE id IN (SELECT id FROM source_table);
SELECT * FROM source_table
{% else %}
SELECT * FROM source_table
{% endif %}
B{% if is_incremental() %}
SELECT * FROM source_table WHERE id IN (SELECT id FROM {{ this }})
{% else %}
SELECT * FROM source_table
{% endif %}
CSELECT * FROM source_table WHERE is_incremental() AND id NOT IN (SELECT id FROM {{ this }})
D{% if is_incremental() %}
SELECT * FROM source_table WHERE id NOT IN (SELECT id FROM {{ this }})
{% else %}
SELECT * FROM source_table
{% endif %}
Step-by-Step Solution
Solution:
Step 1: Understand duplicate prevention logic
{% if is_incremental() %}
SELECT * FROM source_table WHERE id NOT IN (SELECT id FROM {{ this }})
{% else %}
SELECT * FROM source_table
{% endif %} selects rows whose ids are not already in the target table, avoiding duplicates.
Step 2: Evaluate other options
The option selecting id IN (SELECT id FROM {{ this }}) selects existing ids (duplicates); the option using is_incremental() in SQL misuses it; the option trying DELETE FROM {{ this }} is not allowed in select models.
Final Answer:
Filters for ids NOT IN target table during incremental runs to avoid duplicates -> Option D
Quick Check:
Filter new ids with NOT IN for duplicates [OK]
Quick Trick:Use NOT IN with {{ this }} to exclude existing keys [OK]
Common Mistakes:
MISTAKES
Selecting existing ids instead of new
Using is_incremental() inside SQL WHERE
Trying to delete rows in select model
Master "Incremental Models" in dbt
9 interactive learning modes - each teaches the same concept differently
