Getting data from where it is created to where it is analyzed is the unglamorous core of any data platform. The vocabulary can be confusing, because ETL, ELT, change data capture and managed connectors are sometimes presented as rivals when they are really different answers to different questions. This guide sorts them out and offers a way to choose without chasing whichever approach is currently fashionable.

The Core Decision: Where Does Transformation Happen?

ETL (extract, transform, load) reshapes data before it reaches the destination. This made sense when warehouses had limited compute and storage, and it remains useful when data must be filtered, masked or standardized before it is allowed to land anywhere.

ELT (extract, load, transform) loads raw data first and transforms it inside the destination, using the warehouse or lake engine. It keeps the raw record available, makes reprocessing easier and lets analysts work in SQL. It generally suits modern cloud platforms where compute is elastic.

Neither is universally better. Sensitive data that must not be stored in raw form pushes you toward transforming earlier. Teams that value flexibility and auditability tend to prefer loading raw data and transforming afterward.

How Often and How Much: Batch, Micro-Batch and Streaming

Separate from where transformation happens is how data moves.

• Batch moves data on a schedule, such as nightly or hourly. It is simple, predictable and adequate for much reporting.
• Micro-batch runs frequently in small increments, a middle ground when near-real-time is nice to have.
• Streaming processes events continuously, appropriate when decisions depend on seconds-old data.

A frequent mistake is demanding streaming when nobody makes decisions that fast. Ask what action the fresher data would change, and let the answer set the requirement.

Change Data Capture

Change data capture (CDC) reads a source database’s change log and replicates inserts, updates and deletes downstream, rather than repeatedly copying whole tables. It reduces load on the source, captures deletes that simple timestamp-based extracts miss, and keeps destinations closely in sync.

The trade-offs are setup complexity and source-side requirements: the source database must expose its change log, and operators must manage permissions and log retention. Check your database vendor’s current documentation for how this works on your version.

Managed Connectors Versus Custom Pipelines

Managed integration services provide prebuilt connectors for common sources such as SaaS applications and databases. They handle authentication, schema changes and retries, which saves time on commodity integrations. Custom pipelines give full control and fit unusual sources, but you own the maintenance forever.

A pragmatic split: use managed connectors for standard sources where the integration is not a differentiator, and write custom code where your logic is distinctive or no connector exists. Evaluate managed options for how they handle schema drift, failures, volume-based pricing and data residency, and verify current details with the vendor.

Comparison at a Glance

Approach	Best for	Main trade-off
ETL	Pre-load masking, filtering, strict shaping	Harder to reprocess; logic lives outside the warehouse
ELT	Flexible analytics, raw data retention	Raw data in the destination needs access controls
CDC	Near-current replicas, capturing deletes	Source setup and log management
Batch	Reporting with relaxed freshness	Data is only as fresh as the last run
Streaming	Time-sensitive decisions	Higher operational complexity
Managed connectors	Common SaaS and database sources	Cost scales with usage; less control

Design for Failure From the Start

Whichever approach you pick, pipelines fail: networks drop, schemas change, APIs rate-limit. Build in a few durable habits.

• Idempotency. Rerunning a load should not create duplicates.
• Retries with limits. Retry transient errors, but alert on persistent ones.
• Schema change handling. Decide whether new columns flow through automatically or require review.
• Monitoring of freshness and volume. A pipeline that silently loads nothing is worse than one that fails loudly.

Think About Ownership and Contracts

Integration problems are frequently organizational. When a source team renames a column without warning, downstream reports break. Agree with source owners on what changes require notice, and keep a simple record of each integration: source, destination, schedule, owner and contact. This costs little and prevents long investigations.

A Realistic Example

Suppose a hypothetical company needs to combine data from an order database, a support tool and a marketing application. For the support and marketing tools they use managed connectors, loading raw data into the warehouse on an hourly schedule and transforming it with SQL. For the order database, which is large and frequently updated, they use change data capture so the warehouse sees updates and deletions without heavy queries on the production system. One source contains personal data that policy says must be masked before storage, so that pipeline transforms before loading. Different sources, different approaches, one coherent platform with documented owners.

Frequently Asked Questions

Is ELT replacing ETL? ELT is common on cloud platforms, but ETL remains appropriate where data must be altered, masked or validated before landing. Many teams use both.

Do I need streaming for real-time dashboards? Only if the business truly acts on data that fresh. Frequent micro-batch loads often satisfy the need with less complexity.

What is the main advantage of CDC over scheduled extracts? It captures every change, including deletions, with less load on the source, and keeps the destination closer to current.

Are managed connectors worth the cost? For common sources they often save substantial engineering time, but pricing models vary, so model your expected volume and compare against the cost of building and maintaining your own.

How do I handle sources that change their schema? Agree on a change process with the source owner, choose whether new fields flow through automatically, and monitor for failures so changes are caught quickly.

Next Step

Inventory your current integrations in a simple table: source, method, schedule, owner and freshness requirement. Highlight any where the method does not match the requirement, such as a nightly full copy of a table that people expect to be current, and plan to improve that one first.