Unique key for merge behavior in dbt - Cheat Sheet & Quick Revision

Recall & Review

beginner

What is a unique key in the context of merge behavior in dbt?

A unique key is a column or set of columns that uniquely identify each row in a table. It helps dbt know which rows to update or insert during a merge operation.

beginner

Why is specifying a unique key important for merge operations in dbt?

Specifying a unique key ensures that dbt can correctly match existing rows to update them and avoid duplicate rows when merging new data.

intermediate

What happens if you do not specify a unique key in a dbt merge operation?

Without a unique key, dbt cannot reliably match rows, which may cause errors or duplicate data during the merge.

intermediate

How do you define a unique key in a dbt model for merge behavior?

You define the unique key in the model's config block using the unique_key parameter, listing the column(s) that uniquely identify rows.

beginner

Give an example of a unique key configuration in a dbt model.

Example: {{ config(materialized='incremental', unique_key='id') }} means the 'id' column uniquely identifies each row for merge.

What does the unique key do in a dbt merge operation?

A Deletes duplicate rows automatically

B Sorts the data alphabetically

C Identifies rows to update or insert

D Creates new columns in the table

The unique key helps dbt match rows to update or insert during a merge.

What might happen if you omit the unique key in a dbt incremental model?

A The model runs faster

B Nothing changes

C The data is automatically cleaned

D Duplicate rows or errors may occur

Without a unique key, dbt cannot match rows properly, causing duplicates or errors.

How do you specify a unique key in a dbt model?

A In the SQL SELECT statement

B In the model's config block with 'unique_key'

C By naming the model file uniquely

D By creating an index in the database

You specify the unique key in the config block using the 'unique_key' parameter.

Which of these is a good choice for a unique key?

A A column that uniquely identifies each row

B A column with many repeated values

C A column with null values

D A column with random text

A unique key must uniquely identify each row to work correctly.

In dbt, what type of model typically uses a unique key for merge behavior?

A Incremental models

B Snapshot models

C Ephemeral models

D View models

Incremental models use unique keys to merge new data with existing data.

Explain why a unique key is necessary for merge behavior in dbt incremental models.

Think about how dbt knows which rows to change or add.

Describe how to set a unique key in a dbt model and what happens if it is missing.

Focus on the config syntax and consequences.