ETL vs ELT, Batch vs Streaming

In an illustrative scenario, three requests arrive in the same week, all about the same customer data held in the CRM. The analytics team wants it in the warehouse to report churn by segment. The fraud team wants to know within seconds when a customer’s contact details change, because a changed email followed by a large order is a pattern they watch. And the marketing application wants to check, at the moment a customer clicks, whether that customer has opted out of offers. All three are written up as “we need a pipeline from the CRM.” They need three different things, and one of them should not be a pipeline at all.

“Build a pipeline” compresses several decisions into one word: whether to copy data out of its owning system or ask that system when needed, where the data gets transformed, how current it has to be, what extraction does to the source, what the copy obliges you to protect, where the bill lands, and who is responsible for each part. The labels people argue about — ETL or ELT, batch or streaming — answer only some of these, and not the first one. The mechanics of individual load patterns and processing modes are covered in ETL and ELT pipeline patterns and batch, micro-batch, and streaming; here the concern is choosing among them.

The first question: copy the data, or call its owner?

Moving data between systems is one of several ways to integrate them. The Enterprise Integration Patterns catalog lists four broad styles — file transfer, a shared database, remote procedure invocation, and messaging — and frames the choice with criteria that apply directly here: how coupled the systems become, how data formats are agreed and evolve, how timely the shared data must be, whether the consumer needs data or functionality, and whether the interaction can be asynchronous.

Data pipelines are replication: the consumer gets its own copy. Calling an API the owning system publishes is service integration: the consumer asks the owner each time. They solve different problems.

	Replicate the data	Call the owning service
What the consumer gets	A copy it controls: history, joins with other data, large scans, its own availability	The owner’s current answer, with the owner’s rules applied
What it costs	Staleness, a second place to protect, and the risk of reinterpreting meaning differently from the owner	A runtime dependency on the owner’s availability, latency, and rate limits
Fits when	The need is analysis over time or volume, or the consumer must work when the source is down	A decision needs the authoritative current state, or a business rule the owner enforces

The marketing application in the example needs the authoritative current opt-out status at the moment of a decision, and the rule that governs it belongs to the CRM. A copy refreshed hourly would be wrong after every opt-out for the wait until the next run, plus the time that run takes, plus any retry or missed run — a lag that has to be measured rather than read off the schedule. The application would also be re-implementing a consent rule it does not own. That request is service integration. The analytics request is clearly replication. The fraud request sits between them: it needs to react to changes, which is what events are for.

When replication quietly becomes a shared database

A common way to replicate operational data is also a tightly coupled one: reading the source application’s own tables, by query or by capturing changes from its database log. The consumer is then built on the source’s internal schema — column names, status codes, how a soft delete is represented — none of which the source team promised to keep stable. This is not shared database integration — in that style the applications keep their data in one common database, whereas here the consumer gets its own store and its own compute. What replication separates is storage and execution; what it does not separate is the consumer’s dependence on the source’s internal schema. So the coupling the catalog describes for a shared database survives the copy: its account of that style names the difficulty of finding a schema that serves several applications, one that often ends up hard to work with and politically contested, and a consumer reading internal tables inherits exactly that problem without ever having agreed to a schema with anyone.

The coupling can be loosened without giving up replication. The source team can publish a deliberate interface — a view, an export, or events written through a transactional outbox — and agree on it through a data contract. Capturing changes from internal tables with change data capture remains a valid choice, particularly for systems whose owners cannot change them, but it should be recognized as depending on internals rather than on a promise.

The direction can also run the other way. When modeled analytical data needs to reach an operational tool, that is reverse ETL, and the same question applies: is the tool receiving a copy it will act on later, or does it need the current value when it acts?

Where transformation runs

Once replication is the answer, the ETL-or-ELT question arrives. Microsoft’s Azure Architecture Center states the difference crisply: ELT differs from ETL “solely in where the transformation takes place.” In ELT the target store’s own processing does the work, which removes a separate transformation engine; the guidance adds that scaling the target then scales the transformation, provided the target is powerful enough to transform efficiently. That is the transform-and-load capacity, not the whole pipeline: an extraction API limited to a hundred records a second caps throughput at a hundred records a second however large the warehouse grows, so whether scaling the target helps depends on where the actual bottleneck is. Its guidance on choosing is worth reading as a list of conditions rather than a verdict.

Favors transforming before load (ETL)	Favors transforming in the target (ELT)
Heavy transformation has to be kept off a constrained target	The target is a warehouse or lakehouse with elastic compute
Complex rules need a specialized engine	Raw data has to land in the target first, so that its own tools can explore and reshape it — keeping raw data somewhere is a separate retention decision that either pattern can satisfy
Regulation requires curated, audited staging before data is loaded	The logic benefits from the target’s native capabilities

In practice the more useful move is to stop treating “the transformation” as one step. Three different kinds of work are usually bundled together, and each has its own right place.

• Restrictions that must apply before data crosses a boundary. Dropping fields the destination is not permitted to hold, or masking them, belongs before load. Once a restricted field lands in a broadly accessible platform, removing it later does not undo the exposure. This is the ETL half, whatever the platform is otherwise called, and it is where data minimization is applied.
• Standardization close to landing. Types, encodings, time zones, and removal of redelivered events — the same event delivered twice, recognized by an identifier the producer assigned or by the delivery contract, not two rows that merely look alike, since identical values can be two real actions. Best done once, near arrival, so every consumer starts from the same interpretation; the cost is small for type and encoding fixes and grows with the state that deduplication has to keep.
• Business logic. Definitions, joins across sources, and metrics. This benefits most from living where it can be versioned, tested, and re-run after a fix. Whether it can be re-run depends on what was retained — the inputs as they were, the reference data, and the code — which is a retention decision separate from where the transformation runs: an ETL pipeline can keep a permitted raw copy and reprocess from it, and an ELT platform that has expired its raw layer cannot.

Most real platforms are therefore both, as the glossary entry on ETL and ELT notes. The label matters less than being explicit about which work happens where, and why. One consequence deserves attention: the ability to re-run logic over raw data, often credited to ELT, comes from retaining that raw data rather than from where the logic runs — and retention is a decision of its own, with the security and cost obligations of keeping the data.

How fresh, and what freshness costs

Freshness requirements are usually stated as a technology preference — “we need real time” — when they should be derived from a decision. Ask what action the data supports, how quickly that action has to happen, and what it costs when the data is late. Churn reporting read in a weekly meeting may need nothing fresher than the previous day — or it may need this morning’s cancellations if the meeting decides on retention offers, and how often a report is read says nothing about which; the question is what cutoff the decision needs and what a later one would cost. A fraud check that blocks an order needs changes within seconds, because the order is placed within seconds. Examined this way, a requirement that began as “real time” can turn out to be hours or a day; the ones that genuinely are seconds are the ones that justify the cost.

Two points make the requirement measurable. First, freshness is end to end. What a consumer experiences is the sum of when the source makes a change available, extraction, transport, transformation, and publication. A streaming ingest feeding a model rebuilt once a night delivers overnight freshness at best: the wait for the nightly rebuild dominates the sum, but the earlier stages still add to it, and where stages run in parallel it is the longest path through them that counts. How to measure it is covered under data freshness.

Second, lower latency is not the same work done faster; it adds obligations. Microsoft’s guidance on streaming reliability lists some of them: checkpointing for at-least-once processing and recovery, idempotent transformations because duplicates are possible, watermarks for late and out-of-order data, and dead-letter handling for records that cannot be processed. Beyond those, someone has to be able to respond when the job stops — and how fast is set by what the consumer can tolerate rather than by the processing mode. A nightly batch whose readers arrive in the morning can often wait until then; a stream feeding a fraud check cannot, and neither can a batch whose output is due before its readers wake.

Serving batch and streaming consumers from one logic

When some consumers need seconds and others need a daily, fully reconciled view of the same data, the tempting design is two paths: a fast one and an accurate one. That is the Lambda architecture, and Jay Kreps’s 2014 critique identified its central problem — that maintaining code that must produce the same result in two complex distributed systems “is exactly as painful as it seems.” His alternative, now called the Kappa architecture, keeps one stream processing path, retains the input log long enough, and handles a logic change by running a second copy of the job over the retained history into a new output, switching consumers once it has caught up.

The durable lesson is narrower than either name: define the logic once, and choose the execution schedule per consumer. Whether that is achieved with one streaming engine, or with the same definitions run on different schedules, matters less than never having two independently maintained implementations of what “an active customer” means.

What extraction does to the source

Every extraction method places some cost on the system being read, and the owner of that system experiences it even if nobody told them the pipeline exists.

• Queries against the primary database compete for the CPU, memory, and I/O that transactions need. A large extraction during business hours is an incident waiting for a busy day.
• Queries against a replica move that load off the primary, but a read replica can be behind, so an extraction can miss changes the primary has already committed.
• Log-based change capture reads committed changes rather than querying tables once it is running, but two costs remain. The first is the initial load, and any resynchronization after a gap: the existing rows still have to be copied, which reads the tables and holds a snapshot while it does, and that copy has to be sized, scheduled, and rate-limited like any other extraction. The second is that the steady state moves the cost somewhere else rather than removing it. In PostgreSQL, for example, a logical replication slot keeps the write-ahead log and catalog rows it still needs, and the documentation warns that slots “persist across crashes and know nothing about the state of their consumer(s)” — so a stalled consumer causes the source to retain log data and consume storage until someone notices.

The organizational consequence follows directly. The source team is carrying part of the pipeline’s risk, so they need to know it exists, what it reads, and whom to call. An extraction the source owner has never heard of is the one most likely to be broken by a routine schema change, or to be the unexplained cause of a disk alert.

Every copy is a new place to protect

Replication creates a second home for data, and that home has its own access model, its own administrators, its own backups, and usually a broader audience than the source. Four obligations come with it.

• Fields. Copy what the purpose needs, and apply restrictions before load, as discussed above.
• Credentials. Extraction runs with an identity that can read the source, often broadly. That identity is a high-value target and deserves least privilege, which means different things for different methods. For query-based extraction it is read access to the tables it needs and nothing more. For log-based capture it is whatever the database requires for replication — in PostgreSQL, a role with the replication attribute and connection rights, plus read access to the tables for the initial copy — which is broader than table reads, and since the documentation notes that a publication carries no access control of its own, the connection can reach whatever it is able to subscribe to. In neither case does the identity get write access to the source, and the actual exposure has to be reviewed for the method in use.
• Access in the destination. The source’s row-level rules — which sales region sees which customers — usually do not travel with the data. They have to be re-created or deliberately waived.
• Retention and deletion. A deletion obligation that arises at the source has to be evaluated for every copy — raw landing data kept for reprocessing, derived tables, and backups — and the answer is not always the same, because a copy may sit under a different jurisdiction, purpose, or legal retention requirement. What the platform owes is the ability to find every holding place, decide for each whether the obligation applies, act where it does, and keep evidence of it. Backups that cannot be edited need controls of their own — restricted access, a defined expiry, and re-application of the deletion after any restore — as the data deletion entry describes. A platform that cannot locate every copy of a person’s data cannot make that decision, let alone carry it out.

None of this argues against replication. It argues that the cost of a copy includes these controls, and that “just call the service” is sometimes the cheaper option once they are counted.

Where the bill lands

The same data movement costs different amounts, charged to different budgets, depending on the choices above. It is worth tracing before deciding rather than discovering afterwards.

• Transformation compute sits in a separate engine under ETL and in the target platform under ELT. ELT often looks cheaper because the engine disappears from the diagram, while its cost reappears in warehouse usage.
• Latency changes the cost shape, through the provisioning model as much as the processing mode. A daily batch that runs for an hour pays for an hour when compute is allocated only for the run, and for the idle hours too when it runs on always-provisioned capacity; a stream on always-provisioned capacity pays around the clock whether or not changes arrive, while a stream consumed by per-invocation functions pays per batch of records processed plus the broker’s standing cost, and a batch that runs often or long pays continuously too. Read the pricing of the actual design rather than the label.
• Storage accumulates for raw data retained for reprocessing and for retained logs that make replay possible.
• Transfer across regions or clouds is charged by volume and is easy to miss, as discussed in enterprise-scale platform design.
• People. Every pipeline needs an owner who is called when it breaks — how quickly depends on its consumers’ tolerance — and who adapts it when the source changes. That cost does not appear on a cloud bill and rarely appears in the proposal.

Who owns which part

A pipeline crosses at least two team boundaries, and many recurring pipeline failures are, underneath, responsibilities nobody accepted. A workable split looks like this.

Part	Natural owner	What they owe others
What the source data means, and how it changes	The source system’s team	A stated interface and advance notice of changes to it
Extraction and transport infrastructure	A platform team	Reliable delivery, freshness measurement, and a way to see what is extracted from where
Transformations and definitions	The team that owns the analytical use, or the domain the data describes	Tested, documented logic and a named contact for its outputs
Whether the result is fit for a decision	The data owner for that use	Acceptance criteria, and a decision when quality and timeliness conflict

The failure this table is meant to prevent is the central data team that runs every pipeline but owns no source and no meaning, and so learns about every schema change from a failed job. Data mesh is one prominent response: Zhamak Dehghani describes it as founded in decentralization and the distribution of responsibility “to people who are closest to the data,” with domain teams owning their analytical data. It is a stance about organization rather than a technique, and it asks a great deal of domain teams. But its diagnosis — that meaning is best maintained by whoever produces the data — applies whether or not the rest is adopted.

Back to the three requests

Request	Integration style	Transformation	Freshness and cost
Analytics: churn by segment	Replication, ideally from an interface the CRM team publishes rather than its internal tables	Remove fields the warehouse must not hold before load; business definitions in the platform	Daily, on the assumption stated in the example that the meeting works from the previous day’s cutoff; a scheduled batch then keeps compute low if it is allocated only for the run. Recovery must still fit the result’s deadline.
Fraud: react to changed contact details	Events — published by the CRM, or captured from its log if it cannot publish them	Minimal, close to the consumer; logic shared with the analytical definitions where they overlap	Seconds, so a continuously running path with idempotent processing and a response commitment matched to the fraud team’s tolerance for outages
Marketing app: has this customer opted out?	Call the CRM’s service at decision time	None — the owner applies its own rule	No copy to keep fresh; the cost is a runtime dependency to design for, with a safe default when the CRM is unavailable

For the analytics request, a failure can wait for the morning only when the result’s deadline minus the time to rerun still falls after it. A run due at 09:00 that fails at 06:00 and takes four hours cannot wait for the morning.

None of these answers is “batch” or “streaming” or “ETL” as a whole. Each is a set of decisions made for one consumer, which is why a platform ends up with several patterns — and why the patterns should share definitions even when they do not share execution.

Questions that test the choice

Question	If the answer is unclear, check whether
Does the consumer need a copy, or the owner’s current answer?	the consumer’s decision tolerates the copy’s measured lag, and whether a rule the owner enforces is being re-implemented
Is the extraction reading a published interface or internal tables?	the source team announces changes and the pipeline is tested against them, or whether it depends on internals that may change silently
Which restrictions must apply before data lands?	any restricted field could reach the platform before it is removed or masked
What decision sets the freshness requirement, and what is measured end to end?	the requirement traces to a decision and a cost of lateness, and whether the measured end-to-end figure meets it
Is any business definition implemented twice for different latencies?	the two implementations share a definition and test fixtures, and their outputs are compared regularly
Does the source team know what is extracted, how, and what it costs them?	the source team could trace a disk alert or a slow checkout to this extraction
Can every copy of a person’s data be found, and is it known for each copy whether a deletion applies?	there is a record of what was deleted where, which copies were exempt and why, and how backups are controlled
Who is paged when this breaks, and who is told when the source changes?	an alert reaches a named person before consumers notice, and change notices reach that person before the change lands

References

The PostgreSQL examples and descriptions use version 18.

• Enterprise Integration Patterns, Introduction to Integration Styles
• Enterprise Integration Patterns, Shared Database
• Azure Architecture Center, Extract, transform, load (ETL)
• Jay Kreps, Questioning the Lambda Architecture (O’Reilly Radar, 2014)
• PostgreSQL Documentation, Logical Decoding Concepts
• PostgreSQL Documentation, Logical Replication — Architecture (initial snapshot)
• PostgreSQL Documentation, Logical Replication — Security
• UK Information Commissioner’s Office, Right to erasure
• Zhamak Dehghani, Data Mesh Principles and Logical Architecture (martinfowler.com, 2020)