Data Engineering / Basic: contracts and repeatable batches / Lesson 5

ETL, ELT, staging and publication

Choose transformation placement from governance and system constraints while maintaining validation, raw-input provenance and an explicit publication boundary.

About 4 min readContent reviewed 2026-10-26

On this page

Lesson goals

Distinguish ETL and ELT without ranking them universally.
Separate staged, rejected and accepted dataset versions.

Transformation location does not remove responsibility

ETL transforms before loading the chosen destination, while ELT loads an identified representation and transforms within the destination environment. The names depend on the stated system boundary. Neither is automatically more secure, cheaper or correct. Decide from source access, destination capability, privacy, data volume and replay needs. Loading raw sensitive fields into a broadly accessible area is not harmless merely because filtering happens later. The flow still needs validation, scoped retention, ownership and a definition of when consumer-visible data is accepted.

Record the accepted input and planned output version

The planning record identifies one synthetic source interval, schema and transformation version. It is not an orchestrator configuration or a claim that a task already succeeded. A retry should use the same declared input and transformation when reproducibility is required, rather than whichever files happen to be newest. Keep raw input immutable under its retention and privacy policy, or retain a trustworthy reference when full retention is inappropriate. A source file path alone is weak identity if the file can be overwritten without versioning.

json Nonexecuting batch-publication plan

{
  "run": "lab-batch-2026-10-01",
  "inputVersion": "synthetic-source-v1",
  "interval": "2026-10-01",
  "schemaVersion": 1,
  "transformVersion": "normalize-v1",
  "publicationState": "staged-not-yet-accepted"
}

The record describes provenance and state only. It provisions no warehouse, storage path or task.

Keep rejects visible and define publication failure

A flow can reject individual malformed rows, fail the whole batch or publish a labeled partial result, but those are different contracts. Define the permitted policy before seeing an inconvenient result. Preserve accepted and rejected counts with source identity and scoped reasons. Do not expose private payloads in general logs or copy every rejection indefinitely without a retention decision. Downstream readers should know whether the published version is complete for the promised interval and whether late corrections will replace or append data. A task’s completed state is not that explanation.

Publish only an accepted synthetic batch

This local model validates a deliberately small domain, stages accepted rows and refuses publication when any rejection exists. Another dataset may permit quarantined rejects under an explicit partial-data policy; the rule here is whole-batch acceptance. The function does not validate every production field or implement durable atomic storage. It illustrates why an invalid row must not quietly disappear before a green publish result. Test the negative path along with the valid batch and retain the reasons needed to correct the source or transformation.

python Offline staging and whole-batch acceptance

def stage(rows):
    good = [r for r in rows if type(r.get('qty')) is int and r['qty'] >= 0]
    rejected = len(rows) - len(good)
    return good, rejected

good, rejected = stage([{'qty': 2}, {'qty': -1}])
assert good == [{'qty': 2}]
assert rejected == 1
assert not (rejected == 0)  # this contract forbids publishing the partial batch
assert stage([{'qty': 2}])[1] == 0

One negative quantity is rejected and blocks this model’s publication condition. No actual storage commit occurs.

Treat publication as a consistency mechanism

For files, a complete manifest or table snapshot can identify the accepted file set. For a database, a transaction can coordinate target rows and relevant state when both live within its supported boundary. Distributed task completion does not automatically create an atomic commit across arbitrary systems. Consumers should select the declared accepted version, and a failed attempt should not masquerade as a complete new version. Model restart and cleanup before deleting staged artifacts. Tool-specific guarantees need their own versioned documentation rather than assuming every object store, warehouse or table format has identical commit semantics.

Common errors and fixes

Raw loading is called safe because transformation happens later

Why it happens: Access and privacy exposure before transformation were ignored.
How to fix it: Scope raw access and retention from the actual data contract.
Prevent it next time: Review every staging area and copy.

A partial staged batch appears as the new complete dataset

Why it happens: Consumer visibility was not tied to an accepted publication marker.
How to fix it: Use a supported complete-version publication mechanism.
Prevent it next time: Test failure between writing parts and publishing state.

A retry reads a different latest source

Why it happens: Input identity was a moving location rather than a declared version.
How to fix it: Pin the intended input version and interval.
Prevent it next time: Keep source and transformation provenance per run.

Check your understanding

1. Is ELT permission to load every private source field into a shared warehouse area?

Show worked solution

No. Loading precedes transformation under that boundary, so raw access and retention can create exposure before later filters run. Minimize or restrict the appropriate representation and document the accepted uses. Transformation placement changes architecture, not the obligation to protect every copy.

2. A batch has ninety-nine accepted rows and one rejected row. May it publish as complete?