Suggestions for CDC driven data ingestion

Hi PhilBrown ,

Try considering below steps for your usecase and let me know if this helps?

1. Minimize Spark Job Duration
   Long-running Spark jobs are difficult to manage, consume significant Fabric Capacity, and are prone to failure over time. Instead of streaming jobs running for hours, use micro-batch jobs that execute every 5 to 15 minutes. These short-lived jobs process smaller chunks of data, allowing better scaling and monitoring. This approach also enables retry logic and isolates failures to a specific time window.
2. Decouple Change Capture from Delta Write
   Separating the ingestion of CDC data from the logic that writes to Delta tables adds flexibility and improves fault tolerance. First, write raw Debezium change events into a Bronze Delta table as-is, without any transformation or merge logic. Then, in a separate step or job, read from the Bronze table and apply upserts or deletions into a Silver Delta table. This separation allows you to buffer, inspect, and reprocess raw data without needing to re-ingest from Event Hubs.
3. Leverage Micro-Batching with Delta Lake
   Delta Lake performs best when updates and inserts are handled in small batches. Use micro-batch processing to append raw data into Bronze tables and then periodically run merge jobs into Silver tables. This reduces memory pressure and avoids large-scale shuffles. You can also optimize performance by partitioning on client ID or date and using Z-Ordering on frequently queried fields.
4. Avoid Snapshot Reads on Large Databases
   Taking full snapshots from large SQL Server databases — especially on-prem — can result in long table locks and degraded source system performance. Instead, use Debezium in CDC-only mode to read directly from transaction logs without triggering heavy queries. If an initial load is required, perform it once offline or outside business hours, and switch to CDC for all incremental loads thereafter.
5. Use Delta Lake Only When Necessary
   While Delta Lake provides powerful features like versioning and ACID compliance, it’s not needed at every stage of the pipeline. Use it selectively in Silver and Gold layers where data needs merging, analytics, or time travel. For raw ingestion or high-velocity logs, storing as Parquet or using Fabric Lakehouse SQL may be more efficient. Avoid unnecessary overhead by choosing the right format for the right layer.

Thanks,

Prashanth Are

MS Fabric community support