AI Ecosystem CourseLearn with Q
Data: From Business Records To Model Inputs · Lesson 5

ETL, ELT And Streaming Data Explained

A pipeline must preserve meaning through movement, delay, retries and corrections. Choose its sequence and processing cadence from the task, then test which effects survive failure and recovery.

Study And Save Progress

Watch The Lesson

9 min 20 sec

Read the video transcript

A return happens before midnight, reaches our fictional company’s data pipeline after midnight, and then arrives a 2nd time during a retry. Later, someone corrects the return’s status. Which day should the return belong to? How many returns should we count? Which status should the assistant use? Following that 1 case will show

why moving information quickly still requires careful decisions about meaning, identity, and recovery. Ingestion brings data from a source into a receiving system. Extraction obtains the source information. Transformation changes its representation or combines it with other information. Loading places it in the target. Our returns assistant needs an authorized,

usable view of the business facts. Moving every available field into every destination would create unnecessary copies and exposure. First decide which information each destination needs, who may use it, and how changes will reach it. Suppose the source records a handling cost in cents and the analytical destination expects dollars. For this example, the currency is

already known to be US dollars. Extraction obtains the original value and its units. Transformation converts 1,500 cents into $15 and checks the expected fields. Loading places that prepared representation in the destination. That order is called extract, transform, load, or E T L. Keep the source value and transformation

identity available under the appropriate access rules so the conversion can be investigated. An alternative loads an authorized source representation into a restricted target area first, then performs the major transformation there. That is extract, load, transform, or E L T. The cost still needs the same currency and unit interpretation. The location and sequence of the transformation have changed.

A required restriction on personal information must be enforced before that information reaches a destination where it is prohibited. Calling a route E L T gives no permission to postpone that restriction. The choice depends on the work: transformation needs, target capability, sensitive fields, recovery, cost, and who can operate the process.

Neither acronym guarantees that a bad customer identifier will be found. We still need checks and a way to explain what happened to the source information. Once the route is chosen, another question follows: how long can the service wait for new information? Batch processing handles a defined group of data at a scheduled or triggered time.

A daily return summary can collect the relevant group and process it together. Streaming handles a continuing flow, allowing work to proceed as events arrive. An assistant that needs a recent cancellation may need a much shorter update delay than a daily summary. That requirement should drive the design. Streaming also brings continuing responsibility for late arrivals,

repeated events, ordering, and recovery; speed alone cannot resolve them. Netflix's April 27, 2016 account of its operating Keystone pipeline describes 1 event infrastructure serving batch and stream processing. Its engineers explain that producers could drop an event after retries to preserve application availability, and that they measured dropped messages.

That was an explicit tradeoff for that system. A financial return needs its own acceptance rule: a low-delay path is inadequate if it silently loses a required event. Decide which losses are acceptable, how they are detected, and which events need reconciliation. Return R17 occurred at 11:55 p.m. on May 1, but this pipeline

handled its event at 12:10 a.m. on May 2. Use UTC for both times and midnight as the daily cutoff in this invented example. Event time describes when the return happened. Processing time describes when this system handled the event. A daily count organized by event date assigns it to May 1.

One organized by processing date assigns this handling of the event to May 2. Both dates can be recorded, but they answer different questions. Suppose the May 1 summary was published at 12:05 a.m., before the event arrived. What should happen at 12:10? A rule might revise the earlier summary

or send the late event for reconciliation. Simply putting it into the next day’s business count would change the report’s meaning. The team must define the time basis, cutoff, lateness treatment, and whether published results can change. Apache Flink’s version 1.20 documentation explains event time and processing time separately, and describes handling late events.

It provides a concrete system example; our company’s cutoff rule is a teaching choice. Now the connection fails after the destination has applied the return event but before the sender receives confirmation. The sender retries. If every arrival adds 1 to the return count, the same return event can be counted 2 times.

Give the event a stable identity within its source, and define how the destination recognizes that it has already applied it. A different genuine return needs a different event identity, even when it concerns the same order. Discarding everything with the same order identifier could lose real returns. An operation is idempotent when repeating it has

the same relevant effect as applying it 1 time. In our example, applying the same identified return event again should leave the business return count unchanged. That requires the identity check and the relevant update to work safely together, including through failures. A label promising deduplication is insufficient. Test a retry after the update, a restart, and a 2nd genuine event.

Define which effects the guarantee covers; an event count, a message to a customer, and a payment can have different delivery paths. The source then corrects R17 from received to canceled. A process that copies only newly inserted rows can leave the assistant’s copy showing received. Change data capture, often shortened to C D C, obtains source changes

for downstream use under a particular mechanism. The pipeline needs to handle the relevant updates and deletions, as well as new records, and apply them to the right identity. A delayed older version must not overwrite a newer correction. Keep event identity, record identity, and change order clear. PostgreSQL 18 logical replication is 1 documented mechanism. Its publications can select which change operations to send,

and updates and deletes require an appropriate replica identity to locate the affected records. That specific behavior does not establish that every connector captures every kind of change. Check the selected tool, configuration, source retention, and destination behavior. Removing a record from a current downstream view also leaves a separate question

about controlled history, backups, and other copies. Here is the checkpoint. Why can real-time data still be wrong? Consider the late return, its repeated delivery, and its corrected status before you answer. Low delay does not establish correct source meaning, complete events, duplicate handling, compatible structure, or appropriate permissions.

A pipeline can deliver the wrong status almost immediately. Freshness is 1 part of quality. Test what arrives, what is applied, what is held, and what remains unresolved. Try a 2nd check. The same identified return event arrives 2 times, followed by a later correction for the same return. Should the pipeline discard everything after the 1st arrival?

Explain what must stay the same and what must change. The duplicate delivery should leave the relevant business effect unchanged. The genuine later correction must still update the identified return under the change-order rule. Treating the return identifier as a reason to discard every later change would freeze an obsolete state. The checks must distinguish repeated delivery

from new information about the same case. For practice, trace a return that arrives after a daily cutoff, repeats after a lost confirmation, and is then corrected. Choose a batch or continuing route from the service’s delay requirement. State the event date, the arrival date, what the retry may change, and how

the correction reaches the assistant’s current view. The worksheet supplies a new case and a separate answer key. A useful pipeline preserves the meaning of the return while controlling its movement. Next we will trace the sources and transformations that explain a downstream result.

Main Point

A pipeline must preserve meaning through movement, delay, retries and corrections. Choose its sequence and processing cadence from the task, then test which effects survive failure and recovery.

Key Points

1. Move the representation deliberately

Continue the fictional order-and-returns assistant. A source handling cost of 1,500 cents, already identified as USD, becomes $15.00. That is a unit conversion, with its source meaning retained. A missing or wrong currency remains a problem; dividing by 100 cannot repair it.

In an ETL route the example proceeds through extraction, conversion/checking, then loading into the analytical target. In an ELT route the authorized source representation first enters a restricted target area, where the major conversion occurs. A required pre-transfer restriction remains a pre-transfer restriction in either design. Preserve only permitted source evidence, with controlled access and retention.

Microsoft’s Azure Architecture Center describes transformation before target loading in ETL and transformation within the target in ELT. The article is vendor guidance about these patterns; its product suggestions and performance descriptions are not acceptance evidence for this fictional business. The public article has no edition-specific URL; its linked metadata records 15 September 2025, and this package records retrieval on 29 October 2026. [MS-ETL]

Select from requirements: destination capability, source formats, sensitive fields, operating skill, recovery and cost. Record the transformation and source identity so that a downstream discrepancy can be investigated. Exact lineage mechanisms are developed in the next lesson.

2. Choose an appropriate cadence

A daily summary can process a bounded set at a scheduled or triggered time. A cancellation needed by an active assistant may justify a shorter, continuing update path. Define a usable maximum delay rather than assuming every task needs streaming.

Apache Flink 1.20.3 documentation distinguishes bounded and unbounded inputs. Its BATCH execution mode requires bounded input, while its STREAMING mode can process either. This implementation distinction prevents a misleading rule that “bounded always means it must use batch.” The lesson’s general batch/streaming distinction concerns how work is grouped and continued, rather than prescribing a particular engine. [FL-MODE]

3. Keep two times

Use UTC and midnight calendar boundaries for the following fictional case:

An event-date report attributes the return to May 1. A processing-date report attributes this handling of the event to May 2. Preserve both timestamps; do not rename the arrival time as the business event time.

For an event-date report, the late arrival might trigger an explicitly versioned revision or a reconciliation path. A rule that closes a reporting period permanently needs a visible treatment for excluded late data. Flink’s time documentation discusses out-of-order events, watermarks and allowed lateness. A watermark is a progress mechanism under the engine’s assumptions; it cannot prove that every real-world event has arrived. Exact late-data rules vary by pipeline. [FL-TIME]

A business day could use a different zone or cutoff. State those choices and convert consistently, including any daylight-saving rules. The arithmetic above has no daylight-saving ambiguity because the example uses UTC.

4. Retry an event without duplicating its effect

Imagine source event E17 describing R17. Its first delivery updates the destination, but confirmation is lost. An unguarded retry that increments a counter again produces two applications of one event. Keep a stable source-scoped event identity and a defined rule for whether it has already been applied.

The idempotent requirement in this example is one relevant business effect after repeated application of the same E17. Identity alone cannot guarantee this: the check and update must be coordinated safely, with persistent state appropriate to recovery. Separate effects such as notifications and payment requests need their own end-to-end design. No exactly-once service guarantee is asserted here.

RFC 9110 §9.2.2 defines idempotence for the intended server effect of repeated identical HTTP requests. That is a precise example of a scoped guarantee, published June 2022. It does not establish that a pipeline’s arbitrary operation, POST endpoint, side effect or payment is idempotent. Our pipeline retry requirement applies the underlying principle to an explicitly defined business effect. [RFC-IDEM]

Do not deduplicate solely by order_id: one order can have several legitimate returns. Also distinguish event delivery identity from the identity of a record whose state can later change.

5. Apply corrections and deletions

A later source change E19 updates return R17 from received to canceled. A new-rows-only copy can leave its destination stale even if new rows arrive rapidly. CDC can supply relevant source changes; the selected connector must actually capture and the destination must actually apply them. Preserve record identity and a usable change-order rule so that a replay of older state cannot overwrite the correction.

PostgreSQL 18 publications can select INSERT, UPDATE, DELETE and TRUNCATE operations. Updates and deletes require an appropriate replica identity for locating the affected row. These statements describe that mechanism and configuration, not every CDC product. Schema changes, connector retention, filters, restart positions and destination effects require separate checks. [PG-CDC]

Removing R17 from a current view does not establish deletion from every archive, log, backup or derived copy. Record what was changed and which controlled history remains under the retention policy.

6. Diagnose before claiming readiness

For each test, record the source case, arrival, applied effect, current result and unresolved condition:

  1. A late event: can it reach the correct event period or a visible reconciliation route?
  2. A duplicate delivery: does the relevant count stay unchanged through retry and restart?
  3. A correction: does the current view advance without an older replay undoing it?
  4. A deletion: which downstream representations respond, and what history remains?
  5. Invalid structure or permissions: is the affected path held without exposing prohibited data?

Use the separate worksheet and key to apply these decisions. The proposed outcomes are test expectations, not claims that a running system has passed.

7. A dated example

Netflix's April 27, 2016 account of its operating Keystone pipeline describes one event infrastructure serving batch and stream processing. Its engineers explain that producers could drop an event after retries to preserve application availability, and that they measured dropped messages. That was an explicit tradeoff for that system. A financial return needs its own acceptance rule: a low-delay path is inadequate if it silently loses a required event. Decide which losses are acceptable, how they are detected, and which events need reconciliation.

8. Bringing the pipeline decision together

The return event needs its business time, stable delivery identity and later correction preserved along the path. Choose a cadence from the service delay it can tolerate, then test late arrivals, retries, corrections and scoped deletion. A low-delay stream becomes useful when its effects remain accurate and traceable through failure and recovery.

Learning Checks

Attempt each answer before expanding the reasoning.

M13-L05-Q01

Why can real-time data still be wrong? Consider the late return, its repeated delivery, and its corrected status before you answer.

Show reasoning

Low delay does not establish correct source meaning, complete events, duplicate handling, compatible structure, or appropriate permissions. A pipeline can deliver the wrong status almost immediately. Freshness is one part of quality. Test what arrives, what is applied, what is held, and what remains unresolved.

M13-L05-Q02

The same identified return event arrives twice, followed by a later correction for the same return. Should the pipeline discard everything after the first arrival? Explain what must stay the same and what must change.

Show reasoning

The duplicate delivery should leave the relevant business effect unchanged. The genuine later correction must still update the identified return under the change-order rule. Treating the return identifier as a reason to discard every later change would freeze an obsolete state. The checks must distinguish repeated delivery from new information about the same case.

Sources

Learner Materials

Download Lesson 5 Learner Materials

Use the supplied fictional cases and templates for practice.

Practice

Write your answer, compare it with the model answer and decide what to revisit. These written checkpoints are self-assessed.

Here is the checkpoint. Why can real-time data still be wrong? Consider the late return, its repeated delivery, and its corrected status before you answer.

Show The Model Answer

Low delay does not establish correct source meaning, complete events, duplicate handling, compatible structure, or appropriate permissions. A pipeline can deliver the wrong status almost immediately. Freshness is one part of quality. Test what arrives, what is applied, what is held, and what remains unresolved.

Compare your reasoning with the model answer. These written checks are self-assessed.

Try a second check. The same identified return event arrives twice, followed by a later correction for the same return. Should the pipeline discard everything after the first arrival? Explain what must stay the same and what must change.

Show The Model Answer

The duplicate delivery should leave the relevant business effect unchanged. The genuine later correction must still update the identified return under the change-order rule. Treating the return identifier as a reason to discard every later change would freeze an obsolete state. The checks must distinguish repeated delivery from new information about the same case.

Compare your reasoning with the model answer. These written checks are self-assessed.

Open Practice And Save Your Work