Forum Discussion
Solution for data ingestion in pipeline
Hi All,
Currently, our organization uses a metadata-driven data ingestion approach. However, since we do not have suitable incremental load candidates in our on-prem SQL Server tables, we are required to overwrite the tables during each load.
I am exploring alternative solutions to address this limitation. One option I researched is using a mirrored SQL database, but I have concerns about whether implementing CDC (Change Data Capture) could impact the production server’s performance.
Could someone help clarify the potential impact or side effects of using a mirrored SQL database, particularly in relation to CDC? Additionally, I would appreciate any suggestions on other possible approaches to handle incremental ingestion in this scenario.
Any insights or leads would be greatly appreciated.
Thank you.
Hi JibinSebastian,
If the source tables lack any natural incremental keys (like LastModified, rowversion, or surrogate IDs columns), then even with mirroring or CDC, you can't easily do a "compare and load only changes" and incrementally update data in your bronze layer and beyond.
The advantage of using database mirroring or CDC using a Copy Job item, is that the data is already landing in Fabric without needing to copy all data every day. And although mirroring sounds wonderful, it can also be brittle and break easily, stopping the replication. So a Copy Job might be a better and stable solution, especially when your data does not need to be near-realtime updated.
Both Fabric mirroring and CDC introduce extra CPU and disk I/O on the source server due to CDC log scanning, which might cause latency on you production environment application. When using Copy Job, your transaction log can growth significally when you have a lot of daily data changes, depending on how many times a day you run the job.
Another option would be to create custom triggers on the tables and insert changes into audit tables. This requires no schema changes to the source tables, but can increase latency on data manipulation processes. And it also add a bit more complexity to your database.
If it is within you budget, you could also consider setting-up new (Azure) server for a readable secondary database and use transactional replication to push changes to this database. Transaction replication is a low latency solution with less impact than CDC or triggers. Then use this database as your source for Fabric using a Copy Job.
Hope this helps. If so, please give kudos 👍 and mark as Accepted Solution ✔️ to help others. If you resolved your question, let us know what worked for you.
4 Replies
Hi JibinSebastian,
If the source tables lack any natural incremental keys (like LastModified, rowversion, or surrogate IDs columns), then even with mirroring or CDC, you can't easily do a "compare and load only changes" and incrementally update data in your bronze layer and beyond.
The advantage of using database mirroring or CDC using a Copy Job item, is that the data is already landing in Fabric without needing to copy all data every day. And although mirroring sounds wonderful, it can also be brittle and break easily, stopping the replication. So a Copy Job might be a better and stable solution, especially when your data does not need to be near-realtime updated.
Both Fabric mirroring and CDC introduce extra CPU and disk I/O on the source server due to CDC log scanning, which might cause latency on you production environment application. When using Copy Job, your transaction log can growth significally when you have a lot of daily data changes, depending on how many times a day you run the job.
Another option would be to create custom triggers on the tables and insert changes into audit tables. This requires no schema changes to the source tables, but can increase latency on data manipulation processes. And it also add a bit more complexity to your database.
If it is within you budget, you could also consider setting-up new (Azure) server for a readable secondary database and use transactional replication to push changes to this database. Transaction replication is a low latency solution with less impact than CDC or triggers. Then use this database as your source for Fabric using a Copy Job.
Hope this helps. If so, please give kudos 👍 and mark as Accepted Solution ✔️ to help others. If you resolved your question, let us know what worked for you.
also please note that this has nothing to do with incrementatl refresh. Incremental refresh only works with immutable data, and will always flush and fill entire partitions rather than upserting individual rows.
Hi JibinSebastian ,
You're raising a very relevant point — especially for teams working with metadata-driven ingestion patterns and facing limitations with incremental logic on legacy SQL environments.
Impact of Using CDC on Production or Mirrored SQL Databases:
Yes, enabling Change Data Capture (CDC) on a production SQL Server can have measurable performance impact, depending on:
DML Volume: CDC reads from the transaction log. High insert/update/delete operations can cause additional I/O overhead.
Retention Settings: Large retention windows (default 3 days) can lead to bloated CDC tables.
Indexing: Poor indexing on CDC change tables affects query performance during extraction.
Log Space & Cleanup Jobs: If cleanup isn’t tuned properly, transaction logs and CDC tables can grow rapidly.
However, if applied selectively (only on necessary tables) and monitored carefully, the performance hit is often manageable.
CDC on a Mirrored SQL Database:
This is where it gets tricky:
Traditional SQL Server Database Mirroring (now deprecated in favor of Always On Availability Groups) does not support querying the mirror — it’s not readable.
Therefore, you cannot offload CDC reads to a mirrored DB unless it’s part of an Always On Availability Group with readable secondary replicas.
If you're using Availability Groups, you can enable CDC on the primary and run extraction queries from the readable secondary — which would help minimize production load.
Alternative Approaches for Incremental Ingestion
1. Timestamp-Based Incremental Loads (If Feasible) If any of your tables have a LastModifiedDate or UpdatedAt column
Use Fabric Pipelines or Dataflows Gen2 to query only rows where LastModifiedDate > @LastWatermark.
Store and update the last watermark in a metadata table in Fabric.
2. CDC Offloaded to a Staging SQL Server
If you’re unable to use readable replicas:
Replicate production DB to a staging SQL Server using backup-restore or replication.
Enable CDC only on staging.
Use Fabric Pipelines to read from CDC change tables.
3.Use SQL Server Replication Instead of CDC
Set up transactional replication to push changes to a secondary SQL Server or Azure SQL target.
Run your Fabric ingestion from the replicated copy.
Doesn’t rely on CDC or transaction logs directly.
4. Leverage Fabric Mirroring for SQL Server
If you're on SQL Server 2022+ and eligible for Fabric's new mirroring feature:
Mirror SQL Server tables directly into OneLake delta tables.
Fabric keeps mirrored tables updated in near real-time.
Your ingestion layer becomes transformation-only — no need for raw extraction logic.
If CDC on production is too risky, and timestamps aren’t available, your best bets are:
CDC or replication on a non-prod copy
Read from an Always On replica
If this post helps, then please appreciate giving a Kudos or accepting as a Solution to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!Hi JibinSebastian , Thank you for reaching out to the Microsoft Community Forum.
We find the answer shared by nielsvdc is appropriate. Can you please confirm if the solution worked for you. It will help others with similar issues find the answer easily.
Thank you nielsvdc for your valuable response
