Moving Data Between Systems: Copy or Call, Transform Where, and How Often

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. The example is invented.

“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. This article works through them in that order. 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 and means scaling the target also scales the pipeline — provided the target is powerful enough to transform efficiently. 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 should be preserved for exploration or future schema changes
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 deduplication of exact repeats. Cheap to do anywhere; best done once, near arrival, so every consumer starts from the same interpretation.
• Business logic. Definitions, joins across sources, and metrics. This benefits most from living where it can be versioned, tested, and re-run over retained raw data after a fix — the ELT half.

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 main advantage of ELT, keeping raw data so logic can be re-run, is also a retention decision, with the security and cost obligations of keeping that raw 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 needs data from the previous day at best. 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; the stage with the longest wait sets the number. 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, a continuously running job needs someone to respond when it stops, at any hour. A batch job that fails at 2 a.m. can often wait until morning; a stream feeding a fraud check cannot.

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, but it 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: the tables it needs, read-only, nothing more.
• 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 applies to the source applies to every copy, including raw landing data kept for reprocessing and its backups. A platform that cannot locate every copy of a person’s data cannot meet a data deletion request.

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. A daily batch uses compute for an hour; a stream holds it continuously, whether or not changes are arriving.
• 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 paged when it breaks 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 is enough; a scheduled batch keeps compute and on-call cost low
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 someone on call
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

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 reveal the wrong choice

Question	If the answer is unclear
Does the consumer need a copy, or the owner’s current answer?	A copy re-implements a rule it does not own, and is wrong between refreshes
Is the extraction reading a published interface or internal tables?	Routine source changes break the pipeline without warning
Which restrictions must apply before data lands?	Restricted fields are exposed in a broader platform
What decision sets the freshness requirement, and what is measured end to end?	“Real time” is built where a day would do, or a day is delivered where seconds matter
Is any business definition implemented twice for different latencies?	Fast and accurate paths disagree, and nobody knows which is right
Does the source team know what is extracted, how, and what it costs them?	A disk alert or a slow checkout has no known cause
Can every copy of a person’s data be found and deleted?	Deletion obligations are met in the source and nowhere else
Who is paged when this breaks, and who is told when the source changes?	Failures are discovered by consumers

References

Sources were checked on September 16, 2026. The Azure guidance cited is the revision dated September 15, 2025, and the PostgreSQL description refers to version 18. The decision criteria in this article are not tied to a product version.

• 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
• Zhamak Dehghani, Data Mesh Principles and Logical Architecture (martinfowler.com, 2020)