# Snapshots are built with duplicate rows

**URL:** <https://discourse.getdbt.com/t/snapshots-are-built-with-duplicate-rows/9941>\
**Category:** Help\
**Tags:** snapshots, bigquery\
**Created:** [September 14, 2023, 7:20am UTC](https://discourse.getdbt.com/t/snapshots-are-built-with-duplicate-rows/9941 "2023-09-14T07:20:22Z")\
**Posts on this page:** 5\
**Page:** 1

<div class="post-metadata">

**Author:** ![patryk](https://avatars.discourse-cdn.com/v4/letter/p/838e76/32.png) [@patryk](https://discourse.getdbt.com/u/patryk)\
**Post date:** [September 14, 2023, 7:20am UTC](https://discourse.getdbt.com/t/snapshots-are-built-with-duplicate-rows/9941/1 "2023-09-14T07:20:22Z")

</div>

I’m building snapshot models for reverse ETL scripts, so they pickup only recently changed rows. I don’t have any timestamp column available for this and I’m using “check all” strategy.

The problem is, two of my snapshot keep breaking with **UPDATE/MERGE must match at most one source row for each target row** error. When starting from scratch, they might work for 2-3 days and then break.

There are no duplicate rows in source data as I’m using QUALIFY to make sure only one row per unique key appears. This is the snapshot code:

```auto
{{
    config(
      materialized='snapshot',
      target_schema='dbt_analytics',
      unique_key='account_id',
      strategy='check',
      check_cols='all'
    )
}}

select * from {{ ref("stg_accounts_standard_metrics_before_snapshot") }}
qualify row_number() over (partition by account_id order by account_id) = 1

```

(referenced model is also using qualify to make sure it outputs unique account\_id)

I’ve noticed that for snapshot which don’t work, dbt leaves a table with `__dbt_tmp` suffix which contains history of changes with following dbt values:

 ![Screenshot 2023-09-14 at 09.16.56](https://us1.discourse-cdn.com/flex020/uploads/getdbt/original/2X/6/6854927d0d83ab55ce228ef235a0511acc08d3ea.png)

Not sure if these are correct.

I’m super confused because I have in total 4 snapshot tables created the same way (qualify clauses to ensure uniqueness), but only 2 are breaking.  
The pipeline runs once a day, there is no option for race conditions and dbt running twice at the same time.

I’m fighting this for weeks now and I’m out of ideas. Anyone got some tips?

---

<div class="post-metadata">

**Author:** ![stevepisani](https://sea2.discourse-cdn.com/flex020/user_avatar/discourse.getdbt.com/stevepisani/32/3712_2.png) [@stevepisani](https://discourse.getdbt.com/u/stevepisani)\
**Post date:** [February 26, 2024, 11:21pm UTC](https://discourse.getdbt.com/t/snapshots-are-built-with-duplicate-rows/9941/2 "2024-02-26T23:21:10Z")

</div>

@patryk were you able to find a solution to this? I am running into the same problem now.

---

<div class="post-metadata">

**Author:** ![patryk](https://avatars.discourse-cdn.com/v4/letter/p/838e76/32.png) [@patryk](https://discourse.getdbt.com/u/patryk)\
**Post date:** [February 26, 2024, 11:44pm UTC](https://discourse.getdbt.com/t/snapshots-are-built-with-duplicate-rows/9941/3 "2024-02-26T23:44:22Z")

</div>

Nope. I created a workaround by building my own snapshot logic. I generate surrogate key from all columns and if the key changes, I update these rows in my table.

---

<div class="post-metadata">

**Author:** ![stevepisani](https://sea2.discourse-cdn.com/flex020/user_avatar/discourse.getdbt.com/stevepisani/32/3712_2.png) [@stevepisani](https://discourse.getdbt.com/u/stevepisani)\
**Post date:** [February 26, 2024, 11:45pm UTC](https://discourse.getdbt.com/t/snapshots-are-built-with-duplicate-rows/9941/4 "2024-02-26T23:45:23Z")

</div>

it seems i spoke too soon and just figured my problem out 😅. my issue was due to a race condition between my prod and staging environments.

thanks for the reply!

---

<div class="post-metadata">

**Author:** ![bpruss](https://sea2.discourse-cdn.com/flex020/user_avatar/discourse.getdbt.com/bpruss/32/4290_2.png) [@bpruss](https://discourse.getdbt.com/u/bpruss)\
**Post date:** [February 4, 2025, 10:44pm UTC](https://discourse.getdbt.com/t/snapshots-are-built-with-duplicate-rows/9941/5 "2025-02-04T22:44:34Z")

</div>

Can you say more about how you solved the race condition? I’m having a similar issue where rows with no changes are getting new dbt\_scd\_id and timestamps, but there are NO DIFFERENCE between the records.