Skip to content

Support dlt's new cdc merge strategy #396

Open
Open
Support dlt's new cdc merge strategy#396

Description

@amotl
amotl
opened on Oct 7, 2026

About

dlt 1.31 starts supporting the new cdc merge strategy, and possibly a few more options around that or other topics/strategies. What is needed to make omniload also support them -- just add it to the docs? ;]

Details

An excerpt from the release notes:

  • cdc merge strategy
    • Loads a full snapshot as an upsert and deletes destination rows that are missing from it.
    • Optionally deletes only within the loaded merge_key partitions.
    • Supported on duckdb (1.4+), motherduck, ducklake, Snowflake, Postgres, BigQuery, Databricks, MSSQL, Fabric, Athena (Iceberg) and Delta.
  • Skip unchanged rows on cdc and upsert
    • skip_unchanged_rows: True updates only rows that changed, so unchanged rows keep their _dlt_load_id. Snowflake Streams and similar consumers then see only real changes.
    • row_version_column_name compares one version or hash column instead of all columns.
  • Merge conditions: source_filter and destination_scope
    • source_filter chooses which loaded rows to merge. destination_scope chooses which destination rows can be deleted or retired.
    • They work with delete-insert, scd2 and cdc (upsert takes source_filter only).
    • Together they enable:
      • partition replace (BigQuery and Snowflake can prune partitions);
      • keyless delete-insert;
      • scd2 retiring only within a scope;
      • {table} / {staging_table} placeholders.

References

Activity

  1. hampsterx commented on Oct 16, 2026

    @hampsterx
    hampsterx
    on Oct 16, 2026
    Contributor

    Not docs-only. --incremental-strategy is a closed enum (IncrementalStrategy in src/omniload/model.py), so cdc is refused as an invalid choice before dlt ever sees it. What it takes:

    • An enum member and a mapping. cdc becomes {"disposition": "merge", "strategy": "cdc"} in run_ingest, next to the structured form scd2 already uses. upsert isn't exposed either and falls out of the same mapping: merge today hands dlt a bare "merge", which resolves to the destination's default strategy, delete-insert on the SQL destinations.
    • Refusing partial reads. cdc deletes every destination row the load does not carry, so anything that reads less than the whole table deletes rows the source still has. That is --incremental-key, --sql-limit and --yield-limit, which scd2 already refuses, and two cases scd2 misses today as well: --interval-start/--interval-end bound into a query: table's placeholders without an incremental key, and sources that keep their own cursor while handles_incrementality() returns False (Google Ads windows on segments_date, Kafka reads from tracked offsets). The rest of scd2's refusals do not apply: cdc matches rows on the primary key rather than a row hash, so the Arrow backends and mmap:// are fine. Checked on dlt 1.31 with duckdb, including sql_table(backend="pyarrow").
    • An emptied source deletes nothing. A cdc run against a source table emptied to zero rows leaves the destination as it was, with both the sqlalchemy and pyarrow backends, because dlt runs no merge for a load without rows. That reads as a docs line rather than something omniload should work around.
    • A primary key. dlt refuses cdc and upsert without one, but only at the load step and as a traceback, so omniload should check it up front. A SQL table with a declared primary key supplies it by reflection; anything else needs --primary-key.
    • Filesystem sources reject it along with the other key-based strategies.
    • Destinations. dlt checks its own capabilities, so an unsupported destination fails with dlt's error. One gap: dlt-cratedb subclasses dlt's postgres destination without overriding the merge strategies, so on 1.31 CrateDB advertises cdc and dlt does not refuse it up front. The same already holds for merge and delete+insert, which the CrateDB page documents as unsupported.
    • Docs. The incremental loading page, the ingest options, and the ClickHouse/CrateDB lists of unsupported strategies.

    skip_unchanged_rows, row_version_column_name and the merge conditions have no CLI surface at all. source_filter and destination_scope take raw SQL predicates and overlap with how delete+insert uses the incremental key as its merge key, so they read as an issue of their own rather than part of this one.

    Release ordering is the catch. omniload allows dlt<1.32 since #394, but dlt-filesystem 0.19.0 on PyPI still declares dlt<1.31, so an install resolves dlt 1.30 and CI cannot exercise cdc. The 1.31 compatibility fix is on dlt-filesystem's main, unreleased.

    @amotl would you rather cut a dlt-filesystem release so omniload can raise its floor to dlt>=1.31, or keep >=1.22 and gate cdc at runtime, with its tests skipped until that release exists?

  2. amotl commented on Oct 16, 2026

    @amotl
    amotl
    on Oct 16, 2026
    MemberAuthor

    Thank you for making a plan, this sounds good. dlt-filesystem v0.20.0 has been released.

  3. hampsterx commented on Oct 17, 2026

    @hampsterx
    hampsterx
    on Oct 17, 2026
    Contributor

    Raising the floor runs into a break that is already live on main. dlt 1.31 removed ensure_pendulum_datetime_utc from dlt.common.time, and 57 modules under src/omniload/source/ import it (192 call sites, plus a few ensure_datetime_utc and ensure_pendulum_datetime_non_utc). Now that dlt-filesystem 0.20.0 allows dlt 1.31, a fresh install resolves it, and because run_ingest imports the MongoDB source module on every run, every ingest fails with an ImportError. That is the red main run after the release, where ty reports unresolved-import. dlt-filesystem's #9 already did the same port with ensure_pendulum_dt and a shared UTC helper. Note that 1.31's ensure_pendulum_datetime now defaults to the context timezone rather than UTC, so it is not a drop-in rename. The cdc work sits on top of that port.

    Two corrections to the plan above, from building it:

    • An emptied source deletes nothing only when it yields no rows at all, as a SQL table does. A source that yields an empty Arrow table, which mmap:// does, still produces a load job, and cdc then deletes every destination row.
    • More sources keep a cursor or a window while handles_incrementality() returns False: Asana (tasks, and stories through it), Mailchimp, Monday, and the Allium and Dune saved queries, which bind a two-days-ago-to-yesterday window into the query parameters when no interval is given. dlt only materializes a resource's incremental when extraction starts, so none of them can be detected from the resource before the run.

    @amotl is the dlt 1.31 port already in progress on your side?

  4. amotl commented on Oct 17, 2026

    @amotl
    amotl
    on Oct 17, 2026 · Hidden as resolved
    Author

  5. amotl commented on Oct 18, 2026

    @amotl
    amotl
    on Oct 18, 2026 · Hidden as resolved
    Author

  6. coderabbitai commented on Oct 18, 2026

    @coderabbitai
    coderabbitaiBot
    on Oct 18, 2026 – with coderabbitai · Hidden as resolved

  7. amotl commented on Oct 18, 2026

    @amotl
    amotl
    on Oct 18, 2026 · Hidden as resolved
    Author

  8. coderabbitai commented on Oct 18, 2026

    @coderabbitai
    coderabbitaiBot
    on Oct 18, 2026 – with coderabbitai · Hidden as resolved

  9. amotl commented on Oct 19, 2026

    @amotl
    amotl
    on Oct 19, 2026 · Hidden as resolved
    Author

  10. coderabbitai commented on Oct 19, 2026

    @coderabbitai
    coderabbitaiBot
    on Oct 19, 2026 – with coderabbitai · Hidden as resolved

  11. amotl commented on Oct 19, 2026

    @amotl
    amotl
    on Oct 19, 2026 · Hidden as resolved
    Author

  12. coderabbitai commented on Oct 19, 2026

    @coderabbitai
    coderabbitaiBot
    on Oct 19, 2026 – with coderabbitai · Hidden as resolved

  13. coderabbitai commented on Oct 22, 2026

    @coderabbitai
    coderabbitaiBot
    on Oct 22, 2026 – with coderabbitai · Hidden as resolved

  14. amotl commented on Oct 22, 2026

    @amotl
    amotl
    on Oct 22, 2026
    MemberAuthor

    Hi again. All the mentioned obstacles should have been resolved, so proceeding on the named feature should be possible now.

  15. amotl commented on Oct 23, 2026

    @amotl
    amotl
    on Oct 23, 2026
    MemberAuthor

    omniload v0.21.0 has been released, including GH-422. Thank you again for your excellent contributions!

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment

Metadata

Metadata

Assignees

No one assigned

    Labels

    No labels
    No labels

    Type

    No type

    Projects

    No projects

      Milestone

      No milestone

      Relationships

      None yet

      Development

      No branches or pull requests

      Issue actions