Solution for data ingestion in pipeline

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.