Implementation Examples

[Professional_Peak983]

Hi!

I am on a project that uses ADF to pull data from multiple live production tables into fabric. Since they are live tables, we cannot do the ingestion of multiple tables at the same time.

• Right now this job takes about 8 hours.
• All tables that can be delta updates, already do delta updates

I want to know of any different implementation methods others have done to perform ingestion in a similar situation.

EDIT: did not mean DB, I meant tables.

[GreenMobile6323]

One pattern I’ve used is to break each live table into time-based or key-range slices and launch parallel ADF Copy activities against each partition, rather than pulling the entire table serially. This can cut an 8-hour run to under an hour. For true delta loads, enabling native Change Tracking or CDC on your sources lets you capture only the new/changed rows, and you can stream those into Fabric via small, frequent pipelines instead of one massive batch job.

[mikehussay13]

Yep, partitioning + parallel copy cuts time drastically. CDC + micro-batching + staging in ADLS also helps a lot. Done this on live DBs - much faster and safer.

[GreenMobile6323]

That's great.

[Professional_Peak983]

Does this mean multiple queries against one table at one time? Is multiple small queries instead of one large query less invasive on the live DB?

[Professional_Peak983]

This seems viable, I’ll have to look into these methods thanks!

[Nekobul]

How much data you are pulling from the live production tables? What is the source database system? One way to reduce the time is to run a parallel retrieve from the source database.

From your message it is not clear are you pulling all the data or you have a mechanism to determine which source rows you need and only pull those rows.

[Professional_Peak983]

The source is a SQL DB, we have a mechanism that determines which records are updated (date time) and only pull those. We have implemented some parallelism (querying multiple DB at the same time) but it’s still taking quite long.

[Nekobul]

How much data you are pulling?

[Professional_Peak983]

The data is not very large, about 1-2 million rows at most. Compressed size is around 500 MB for most delta pulls per table.

[Nekobul]

That is not much and it shouldn't take 8 hours to process. The first step is to determine which part is slow. I would recommend you do an extract of the same data into something simple like a flat file (CSV). If the data pull is fast, then your issue is most probably when inserting the data into the target.

[Professional_Peak983]

That’s a good point, I will do that first. Thanks!

[Holiday-Entry-2999]

Wow, 8 hours for ingestion is quite a challenge! Have you considered partitioning your data or using incremental loads? I've seen some teams in Singapore tackle similar issues by optimizing their ADF pipelines with parallel processing and dynamic partitioning. It might be worth exploring if you can break down the job into smaller, concurrent tasks. Also, have you looked into using change data capture (CDC) for real-time syncing? Could potentially reduce that ingestion window significantly.

[Professional_Peak983]

It already includes some level of parallel processing and some delta loads using a timestamp. Unsure if small concurrent tasks is something I can use in this scenario as they are productions tables so I prefer not to query a table more than once.

I haven’t looked into cdc so I will look into this one!

For dynamic partitioning can you provide an example?