Merge Strategy Configuration Inconsistency Between Code and Schema

[kang8]

dlt version

dlt 1.8.0

Describe the problem

When changing a resource's merge strategy from "upsert" to "delete-insert" in code, the change is not reflected in the "dlt_version" table's schema. The pipeline continues to use the previously defined "upsert" strategy instead of the newly specified "delete-insert" strategy in the code. This leads to unexpected behavior where duplicates are not handled as intended by the new merge strategy.

Expected behavior

When there's a mismatch between the merge strategy specified in code and the one stored in the "dlt_version" schema, dlt should either:

1. Update the schema automatically to reflect the new merge strategy, or preferably
2. Raise a clear error indicating the mismatch and suggesting a full refresh to update the schema (e.g., "The merge strategy in your code (delete-insert) doesn't match the one in the schema (upsert). Consider using full_refresh to update your schema.")

I suggest implementing solution #2 (raising an error when there's a mismatch) rather than automatically updating the schema, as this gives users more explicit control and understanding of what's happening with their pipeline.

Steps to reproduce

1. Create a dlt pipeline with a resource using "upsert" merge strategy
2. Run the pipeline to load some data
3. Change the merge strategy in your code from "upsert" to "delete-insert"
4. Run the pipeline again
5. Observe that despite the code change, the "upsert" strategy is still being used
6. Verify by checking that duplicate data isn't being handled according to "delete-insert" logic
7. Run with full refresh (pipeline.run(full_refresh=True)) and confirm that now the correct "delete-insert" behavior is observed

Operating system

macOS, Linux

Runtime environment

Kubernetes

Python version

3.11

dlt data source

intercom contacts https://developers.intercom.com/docs/references/rest-api/api.intercom.io/contacts/searchcontacts

dlt destination

Snowflake

Other deployment details

No response

Additional information

This issue creates a confusing experience for users changing merge strategies. When changing from "upsert" to "delete-insert" to handle duplicates better, users expect the change to take effect immediately. Instead, the old behavior persists without any clear indication of why.

[sh-rp]

@kang8 could you provide a snippet of code to reproduce this? Ideally with a python source with a couple of items that loads to duckdb so we are able to debug this.

[kang8]

I was using dlt to load Intercom data into Snowflake in a Dagster ETL pipeline that runs hourly. For simplicity in this example, I'm using PostgreSQL instead (note that DuckDB doesn't support the upsert strategy).

Initially, I used this configuration with upsert strategy:

import dlt

pipeline = dlt.pipeline(
    pipeline_name="intercom",
    destination=dlt.destinations.postgres("postgresql://"), # replace with you pgsql credentials
    dataset_name="dlt_intercom",
)

@dlt.resource(
    primary_key="id",
    write_disposition={"disposition": "merge", "strategy": "upsert"},
)
def contacts():
    yield [
        {"id": 1, "c1": "foo", "c2": 1},
        {"id": 2, "c1": "foo", "c2": 2},
        {"id": 3, "c1": "foo", "c2": 3},
    ]

pipeline.run(contacts)

I discovered my Intercom data sometimes contains duplicate IDs in a single load, which upsert can't handle. I tried changing to just write_disposition="merge", but this still used upsert strategy under the hood:

@dlt.resource(
    primary_key="id",
    write_disposition="merge",
)
def contacts():
    yield [
        {"id": 1, "c1": "foo", "c2": 1},
        {"id": 1, "c1": "foo", "c2": 2},
        {"id": 1, "c1": "foo", "c2": 3},
    ]

Job for contacts.fe0d9730c2.insert_values failed terminally in load 1742368021.08086 with message duplicate key value violates unique constraint "contacts__dlt_id_key"

PS: While creating this example, I found that explicitly setting write_disposition={"disposition": "merge", "strategy": "delete-insert"} resolves the issue by effectively managing the user's delete-insert strategy to handle duplicate IDs.

[sh-rp]

@kang8 I'll close this since what you describe is the correct behavior. If you want to change the merge strategy, you need to set it explicitely