Data Integration in Practice: Full Load, Incremental Sync, CDC, and Job Orchestration
You have a production database that grows by millions of rows every day. Every morning, the analytics team asks for yesterday’s data. And every morning, someone runs a script that dumps the entire database and loads it into the data warehouse.
The script takes four hours. It locks tables. It hammers the source. And it keeps doing the same work over and over — copying billions of rows that didn’t change.
There’s a better way. But most teams never move past the “full load” stage. They don’t know what comes next. Or they’re afraid to touch it.
Let’s talk about the four levels of data integration. Where you are now. Where you could be. And why the jump from level two to level three is where most people get stuck.
Level 1: Full Load — The Brute Force Approach
Full load is exactly what it sounds like. Every time the pipeline runs, it reads the entire source dataset and writes it to the destination. Truncate the target table. Select star from the source. Insert everything again.
Why teams start here:
It’s easy. One query. No state to track.
It works when data is small. For tiny datasets, it’s fine.
It’s forgiving. No need to worry about missing updates.
The breaking point: Somewhere between 10 million and 100 million rows, full load stops being fine and starts being painful. The job runtime grows linearly with the data size. At a certain point, it outgrows your maintenance window.
I’ve seen teams schedule full loads every four hours because the data set kept growing, and they didn’t know any other way. The database team kept complaining about lock contention. The pipeline kept failing because it couldn’t finish before the next run started.
Full load has its place. Small dimensions, reference tables, static data. But for fact tables? For high-velocity sources? It’s technical debt accruing interest every day.
Level 2: Incremental Sync — Only What Changed
Incremental sync is the first real upgrade. Instead of copying everything, you copy only the rows that have changed since the last run.
You need a way to detect changes. The most common method is a timestamp column — updated_at, last_modified, something that changes whenever the row changes.
sql
SELECT * FROM orders WHERE updated_at > LAST_RUN_TIME
What’s better:
Runtime scales with changes, not total size. A table with 100 million rows that gets 100,000 new rows daily will copy 0.1% of the data.
Lower load on the source system.
Faster pipeline = fresher data.
What’s still tricky:
Deletes don’t show up in timestamp-based syncs. A row that disappears won’t be reflected in the destination unless you have a separate mechanism (soft deletes, change tracking, or periodic full reconciliation).
Timestamps rely on application discipline. If something updates a row without bumping the timestamp, you miss it.
Incremental sync works for append-mostly patterns. For tables with frequent updates, you’re still copying changed rows, but you need to handle upserts (update if exists, insert otherwise).
This is where most teams stop. They add a WHERE updated_at > :last_run to their query and call it a day. It’s a significant improvement over full load. But it’s not the end.
Level 3: CDC (Change Data Capture) — Read the Transaction Log
CDC doesn’t poll for changes. It listens to them. Instead of querying the source table over and over, CDC reads the database transaction log — the same log the database uses for replication and crash recovery.
Every insert, update, and delete gets captured, usually in the order they happened.
Why CDC is different:
No polling. Zero impact on source queries. CDC reads the log file, not the table.
Captures deletes natively. You’ll know a row disappeared.
Near real-time. Latency can be seconds, not hours.
The trade-offs:
Operational complexity. CDC requires log access, permissions, often a separate connector service (Debezium, Maxwell, AWS DMS).
Log retention. If your pipeline falls behind and the log gets rotated, you lose data and must backfill.
Schema changes. CDC streams are brittle when the source schema changes without coordination.
A counterintuitive observation: Teams that move to CDC often end up with more data issues initially. Not because CDC is bad. Because they weren’t aware of how messy their change stream actually was. Duplicate events. Out-of-order events. Updates that reference rows that don’t exist yet.
CDC forces you to deal with the real complexity of your data. That’s uncomfortable. It’s also necessary for real reliability.
Level 4: Job Orchestration — Making It All Work Together
Full load, incremental sync, and CDC are about how data moves. Orchestration is about when and in what order.
A simple pipeline has one source and one destination. A real pipeline has many:
Extract from production
Load into staging
Run transformations
Update dimension tables
Append to fact tables
Trigger downstream reports
Orchestration tools (Airflow, Dagster, Prefect) do a few things that matter:
Dependencies: Don’t run fact load until dimensions are ready.
Retries: A temporary network blip shouldn’t kill the whole pipeline.
Backfills: When you add a new transformation, you can rerun historical data.
Observability: You know where the pipeline is, how long each step takes, and where it’s stuck.
The overlooked cost of poor orchestration: Without explicit dependencies, teams build implicit ones. “Run script A, wait five minutes, then run script B.” That five-minute buffer is either too short (race conditions) or too long (wasted time). Explicit orchestration removes the guesswork.
The Migration Path
Going from level one to level four doesn’t happen overnight. Here’s a realistic path:
Phase 1: Full load for everything. Works while data is small.
Phase 2: Identify the largest tables — the ones where full load is already painful. Convert them to timestamp-based incremental sync. Leave small tables alone.
Phase 3: For critical tables where you need delete capture or very low latency, add CDC as a parallel path. Run both CDC and incremental sync for a while. Compare outputs. Build trust.
Phase 4: Add orchestration when you have 5+ pipelines and manual sequencing starts breaking. This is usually earlier than people think.
Phase 5: Retire legacy pipelines one by one.
Data You Might Not Expect
A fintech company I worked with was running full loads on a 2-billion-row transaction table. The job took 12 hours. It overlapped with the next run. Their data warehouse was perpetually 24 hours behind.
They moved to incremental sync first. Runtime dropped to 45 minutes. Then they added CDC for the high-velocity payment table. Payment events showed up in the warehouse within 5 seconds. Fraud detection queries that used to run on stale data now run on fresh data.
The surprising part wasn’t the performance gain. It was the cultural shift. The data team stopped being afraid to touch the pipeline. When something broke, they could trace it, fix it, rerun it. The pipeline became maintainable.
When to Stay Simple
CDC and orchestration are powerful. They’re also overkill for many use cases.
You don’t need CDC if:
Your data is append-only
You can tolerate 5-15 minute latency
You don’t need delete capture
You don’t need orchestration if:
You have one pipeline
That pipeline runs once a day
Failures are rare and recovery is manual
Add complexity when the pain of not having it exceeds the cost of building it. Not before.
Realistic Expectations
Here’s what most people get wrong: They think the hard part is the tooling. It’s not. The hard part is understanding your data.
Do you know which columns actually indicate a change?
Do you know how to handle late-arriving data?
Do you know what happens when a row gets updated twice between pipeline runs?
Do you know how to recover from a pipeline failure without restarting from zero?
CDC tools can capture changes. Orchestration tools can sequence tasks. Neither can tell you what “latest” means in your business context. That’s your job.
Wrapping Up
Data integration isn’t a tooling problem. It’s a thinking problem. Full load is simple but doesn’t scale. Incremental sync scales but misses deletes. CDC captures everything but requires operational discipline. Orchestration ties it together but adds complexity of its own.
Move up the stack as the pain demands. Not before. And when you do, don’t throw away the old patterns. Run them side by side. Compare. Build confidence.
The best data pipeline is one you’re not afraid to change. That starts with understanding the trade-offs at each level.
