How to build ETL Method for CDC Data Load for Multiple tables

Question

Hi, I am trying to build an ETL Pipeline using data flows, so that multiple table data loads can be initiated (as a sequential activity), So far I have tried to achive this using an etl table and parameterizing pipeline and dataflows so that I can call those objects and can be loaded as per CDC from dataflows. First object loads successfully, problem comes in second object, and I dont see my datasets get loaded for the second object and CDC throws that there is no change available, how can I build this correctly so that I can load multiple tables in single click as per CDC.

Also if I add some more objects in my control table then how can I let CDC Method know that its a new object and it should be full loaded before capturing the Change for the table?

Is something like this possible using ADF?

Answer 1

Hi Anmol Ganju,

Thanks for reaching out to Microsoft Q&A.

--Change Data Capture (CDC) in ADF:

• CDC allows you to capture only the changed data since the last run, improving efficiency by avoiding unnecessary queries on entire datasets.
• ADF provides two primary methods for CDC:
  • CDC Factory Resource: This resource simplifies CDC setup at the factory level. It automatically detects changes and captures data without designing pipelines or data flow activities.
  • Native CDC in Mapping Data Flow: This method uses native CDC technology from source databases. It’s ideal for incremental data synchronization between tables.

--Sequential Loading of Multiple Tables:

To load multiple tables sequentially, consider creating a master pipeline that orchestrates the individual table loads.

Each table load can be a separate activity within the master pipeline.

Ensure that the dependencies are correctly defined (e.g., the second table load depends on the successful completion of the first).

--Handling New Objects:

• If you add new objects (tables) to your control table, follow these steps:
  • Update your control table with the new object details.
  • Modify your master pipeline to include the new table load activity.
  • Ensure that the CDC method (either factory resource or native CDC) is aware of the new table.
  • For full loading of the new table, consider an initial load before capturing changes.

--Possible Approach:

• Create a control table that lists all the tables you want to load.
• In your master pipeline:
  • Read the control table to determine the tables to process.
  • For each table:
    • Trigger the CDC process (either factory resource or native CDC).
    • If it’s a new table, perform a full load initially.
    • Otherwise, capture changes since the last run.

--Considerations:

• Latency: Set a preferred latency for CDC to wake up and check for changes.
• Billing: You’re billed only during processing time, not continuously.
• Monitoring: Monitor pipeline runs and CDC activity to ensure correctness.

Please 'Upvote'(Thumbs-up) and 'Accept' as an answer if the reply was helpful. This will benefit other community members who face the same issue.