dbt-core: Duplicate Rows detected during snapshot

@ddppi first of all, I loved the recursive structure of your troubleshooting steps. Here’s a time lapse of me walking through them making sure I understand everything correctly:

Secondly, some good news!

We have a timely discussion going on in https://github.com/dbt-labs/dbt-core/issues/6089 that contains a proposal to proactively search for duplicates before merging into the final snapshot table. The summary of the current proposal is here. Would welcome you to join that discussion and contribute any feedback you have.

we recently had the same error, and eventually we found it is due to duplications in the underlying table in RAW DB and it is tricky to identify initially.

some of our experience while we trouble shot our error

1. the actual SQL query to create snapshot table can be found in .<my-local-project-folder>\logs\dbt.log. We can copy the query out and run in Snowflake, which helped tremendously in our case to quickly pin-point the issue.
2. the DBT model creates table in CONFORMED from RAW and snapshot creates snapshot of this DBT model. When we started investigating the duplicates, we first looked at the source table in CONFORMED instead of RAW, and we couldn’t find any duplicate records there. The tricky part is our pipeline runs DBT run and snapshot commands in a temporary database first before building everything in CONFORMED. So long story short is we should have looked at the source table in RAW, even though snapshot “appears” to create snapshots from the tables in CONFORMED.
3. if you don’t understand what I was saying in point 2, refer back to point 1 😃

I have the same issue on exasol with error message: “Unable to get a stable set of rows in the source tables” and there are duplicate lines in the temp table before merge, even though the soures are clean. I figured out, that a single quote within a varchar column caused the problem, after excluding all rows with single quotes in the string, the duplicates where gone

@muscovitebob @urwa we’re looking into this again. One hypothesis is that using multiple threads could cause this. Are you also using more than 1?

I think in my case the issue may have been caused by running two instances of dbt concurrently. We have been migrating Airflow instances and had a dbt dag running on both instances at one point. I suspect that the snapshot command ran at the same time on both by accident and this is the root cause on my case.

Hey @atvaccaro, it is technically possible to force dbt snapshot to fully copy the table each time. You’d do this by setting the unique_key to something globally unique, e.g. a surrogate key that hashes together your actual primary key with the current_date.

Such an approach doesn’t use a snapshot strategy to detect changes; it’s just append-only. That means the snapshot is:

• Much less efficient, since you’re storing duplicate data in the table many times over.
• No longer idempotent: given the same raw data in the table, running dbt snapshot once or many times returns different results. (Rather than recording only the rows that are different, dbt will append another entire copy of the table.)

It’s not something I’d ever recommend, but you’re welcome to implement it in your own project.

This issue sounds like the crux of the matter:

there are indeed multiple rows with identical dbt_scd_ids

Which snapshot strategy are you using? If it’s the timestamp strategy, dbt creates a hash of the configured unique_key and updated_at. If it’s the check strategy, dbt creates a hash of the configured unique_key and the current timestamp.

Therefore, the way to end up with duplicate dbt_scd_id is:

• timestamp strategy: there exist multiple rows in your underlying table with the same values of unique key + updated timestamp
• check strategy: multiple dbt snapshot invocations at exactly the same time (unlikely)

If the underlying table is liable to having exact duplicate records, you can add logic to the snapshot query or an intermediate model to remove those duplicates.