Picking the right incremental strategy for a dataset
dbt supports multiple incremental strategies—append, merge, delete+insert, and insert_overwrite—that control how new and updated records are reconciled with an existing table on incremental runs. Choosing the right strategy depends on adapter support, whether the dataset has updates (not just new rows), the presence of a reliable unique_key, and warehouse-specific cost/performance characteristics like partitioning. Picking the wrong strategy can cause duplicate rows, unnecessary full-table scans, or failure to update existing records.
Must-know
- The 'append' strategy simply inserts all new rows without checking for duplicates or updates, so it's only appropriate for pure event/log data where rows are never updated and a
unique_keyis not required. - The 'merge' strategy uses a MERGE/upsert statement to insert new rows and update existing ones matched on
unique_key, making it the standard choice for Snowflake, BigQuery, Databricks, and other adapters that support native MERGE. - The 'delete+insert' strategy deletes matching rows based on
unique_keythen inserts the new batch, useful on adapters lacking native MERGE support (e.g., Postgres, Redshift) but can be less efficient at scale. - The '
insert_overwrite' strategy (primarily for BigQuery and Spark/Databricks) replaces entire partitions rather than row-by-row matching, which is highly efficient for large, partitioned datasets where whole partitions of data are reprocessed. - A
unique_keyconfig is required for merge and delete+insert strategies to identify which rows to update or replace; omitting it or choosing a non-unique key can silently produce duplicate records. - The optimal strategy choice depends on data characteristics: use append for immutable event streams, merge for datasets with occasional row-level updates, and
insert_overwritefor large partitioned datasets where full-partition reprocessing is cheaper than row-level matching.
You maintain an incremental model on a warehouse adapter whose dbt adapter does not implement the merge incremental strategy. Late-arriving corrections occasionally update existing rows identified by customer_id, and new rows are also added on every run. Which configuration correctly produces upsert behavior (update matched rows, insert new ones) on this adapter?
What you have tried across dbt Analytics Engineering's objectives, not a readiness score.
Developing and optimizing dbt models
- Tracing and confirming a model's upstream raw sources
- How dbt's core materialization types differ
- Keeping models modular and avoiding repeated logic
- What each core dbt command actually does
- Shaping a model's dependency graph so it stays readable
- Setting project-wide configuration in dbt_project.yml
- Pulling in and using a dbt package
- Writing a model in Python instead of SQL
- Granting model access through the grants config
- Configuring a snapshot to track slowly changing data
- Picking the right incremental strategy for a dataset
- Dry-running a model to check its logic and schema before a real build
- Sampling a model run before a full build
- What microbatch and other advanced materializations are for
Managing dbt models governance
Debugging data modeling errors
Troubleshooting and optimizing dbt pipelines
Implementing dbt tests
Implementing and maintaining external dependencies
Coverage checked against the published exam guide on Aug 4, 2026.
These are independent practice questions, written against this certification's published exam guide. They are not the certification vendor's own questions, and not the real exam.