Forum Discussion

fabricpribeiro's avatar
fabricpribeiro
Icon for Post Patron rankPost Patron
4 months ago
Solved

Extended capabilities in mirroring - Delta Change Data Feed

Dears,

I currently have a SQLMI being mirrored via CDC, as it runs on update policy = 2022. Soon, and being aware of the risks involved (incompatibilities with existing code, etc.), we will migrate it to update policy = Always-up-to-date.

 

The aim is to switch the mirroring from CDC to Change Feed, since Change Feed allows source tables without primary keys to be replicated into Fabric. Some of these special cases (tables without PKs) have neither a candidate key nor timestamp columns at source.

 

By enabling Change Feed, we will be able to mirror them into our Raw workspace in Fabric, fully managed by Fabric.

 

All tables land in our Raw zone, in the database created by the mirroring process. From there, we have a Bronze notebook — triggered via pipeline — that brings the data incrementally into our Bronze Lakehouse using append (adding new data only, no upserts).

 

This works fine for tables that have watermarks or candidate keys. The problem is with tables that have neither, where we cannot distinguish what is new or determine how to bring it into Bronze.

 

I was reading Extended Capabilities in Mirroring - Microsoft Fabric | Microsoft Learn. It appears that if I enable this on the mirrored database (accepting the additional cost), we will be able to see which rows were inserted, updated or deleted. However, a few things were not clear to me after reading it.

 

Questions (Delta Change Data Feed):

  1. Can I enable this while configuring the Change Feed mirror, or do I need to establish the mirror first and only then tick the option that activates the extended CDF functionality?
  2. What permissions do I need in Fabric to enable this? Anything at capacity level, or only at workspace level?
  3. How will I be able to identify which rows were inserted, updated or deleted? Will it create extra metadata fields in each table (i.e. both the tables with PKs and those without)?
  4. If the answer to the above is yes, which field can I use to check whether a record is new, updated or deleted, so that I can configure my notebook accordingly?
  5. Does it change anything from a behavioural perspective when bringing data from source into the Fabric Raw zone (e.g. shorter refresh time, or different behaviour in terms of which data is brought from source to Fabric)?

Thanks a lot,

Pedro

 

  

 

admin
workspaces
  • Dev_Dholakia's avatar
    Dev_Dholakia
    4 months ago

    Hi fabricpribeiro,

    1) When/how to enable

    • Toggle on an existing mirrored DB — no rebuild needed.
    • Path: Mirrored DB → gear → Delta table management → Enable delta change data feed.
    • Also available via Mirrored Database REST API.
    • Captures changes only after enablement (no backfill).

    2) Permissions

    • Workspace Admin/Member to toggle the setting.
    • Extended Capabilities are paid — consume CU on the host Fabric capacity (F SKU).
    • No new source-side permissions.

    3) Insert/Update/Delete exposure

    Read via Spark:

    • SQL: table_changes('schema.table', startVersion)
    • PySpark: .option("readChangeFeed","true").option("startingVersion", v)

    Updates return two rows (update_preimage + update_postimage).

    4) Metadata columns (added to CDF reads, not base table)

    ColumnTypePurpose
    _change_typestringinsert, update_preimage, update_postimage, delete
    _commit_versionlongDelta commit version where the change occurred
    _commit_timestamptimestampTime the commit was applied

    Solves your no-PK / no-watermark problem — use _commit_version as the watermark.

    5) Impact on mirroring behavior / performance

    • No source-side latency change.
    • Extra storage in hidden _change_data/ folder (bound by VACUUM/retention).
    • Extra CU consumption when enabled.
    • Enables incremental Bronze loads — much cheaper than full reloads.

    Bronze notebook pattern

    1. Maintain control.cdf_watermark (table, last_commit_version).
    2. Read CDF from last_v + 1, append rows (incl. _change_* cols) to Bronze.
    3. Update watermark only on success.
    4. Keep refresh cadence shorter than Delta retention window.

    Key limitations

    • Preview — verify GA status before prod.
    • No history backfill — take a snapshot first if needed.
    • Change files tied to VACUUM retention — tune delta.deletedFileRetentionDuration if needed.
    • Enable selectively per table to control cost.

    Microsoft Learn

    Found this helpful? A quick Kudos means a lot, it keeps the community spirit going and motivates contributors to keep sharing.

    ✅Did this resolve your query? Please mark it as the Accepted Solution so fellow members can spot the answer at a glance.


    Disclosure: My earlier response above was drafted with the assistance of an AI tool (Microsoft Copilot) and cross-checked against Microsoft Learn documentation. Sharing this note for transparency as per the Community AI Usage Policy.

3 Replies

  • Dev_Dholakia's avatar
    Dev_Dholakia
    Icon for Resolver III rankResolver III
    4 months ago

    Hi fabricpribeiro,

    1) When/how to enable

    • Toggle on an existing mirrored DB — no rebuild needed.
    • Path: Mirrored DB → gear → Delta table management → Enable delta change data feed.
    • Also available via Mirrored Database REST API.
    • Captures changes only after enablement (no backfill).

    2) Permissions

    • Workspace Admin/Member to toggle the setting.
    • Extended Capabilities are paid — consume CU on the host Fabric capacity (F SKU).
    • No new source-side permissions.

    3) Insert/Update/Delete exposure

    Read via Spark:

    • SQL: table_changes('schema.table', startVersion)
    • PySpark: .option("readChangeFeed","true").option("startingVersion", v)

    Updates return two rows (update_preimage + update_postimage).

    4) Metadata columns (added to CDF reads, not base table)

    ColumnTypePurpose
    _change_typestringinsert, update_preimage, update_postimage, delete
    _commit_versionlongDelta commit version where the change occurred
    _commit_timestamptimestampTime the commit was applied

    Solves your no-PK / no-watermark problem — use _commit_version as the watermark.

    5) Impact on mirroring behavior / performance

    • No source-side latency change.
    • Extra storage in hidden _change_data/ folder (bound by VACUUM/retention).
    • Extra CU consumption when enabled.
    • Enables incremental Bronze loads — much cheaper than full reloads.

    Bronze notebook pattern

    1. Maintain control.cdf_watermark (table, last_commit_version).
    2. Read CDF from last_v + 1, append rows (incl. _change_* cols) to Bronze.
    3. Update watermark only on success.
    4. Keep refresh cadence shorter than Delta retention window.

    Key limitations

    • Preview — verify GA status before prod.
    • No history backfill — take a snapshot first if needed.
    • Change files tied to VACUUM retention — tune delta.deletedFileRetentionDuration if needed.
    • Enable selectively per table to control cost.

    Microsoft Learn

    Found this helpful? A quick Kudos means a lot, it keeps the community spirit going and motivates contributors to keep sharing.

    ✅Did this resolve your query? Please mark it as the Accepted Solution so fellow members can spot the answer at a glance.


    Disclosure: My earlier response above was drafted with the assistance of an AI tool (Microsoft Copilot) and cross-checked against Microsoft Learn documentation. Sharing this note for transparency as per the Community AI Usage Policy.