What Data Engineering Is: From Source Systems to Trusted Data Products

In plain terms

Every company records things. A shop records each sale; a bank records each transfer; an app records each tap. Those records are created by one system, the checkout, the payment processor, the mobile app, and needed by another: the finance team’s spreadsheet, the executive’s dashboard, the model that decides which customers get a discount. Between “created” and “needed” the data has to be moved, combined with other data, cleaned, and reshaped. And it almost never arrives in a usable shape on its own.

Data engineering designs, builds, and operates the systems that make those records usable. A data pipeline is a sequence of steps that collects and prepares data. A data platform provides shared storage, processing, access, and operating tools for multiple pipelines and their users. Part of the work is moving records; another part is establishing what they mean and detecting when the result no longer meets its intended use.

We will follow an order count from its source to a dashboard, identify what makes the result trustworthy, and connect the people and systems involved. The final lab builds a small local pipeline so you can see how successful code can still produce the wrong result.

A Tuesday morning

Consider a fictional shop. The counts below illustrate three separate failures; they are not measurements from a company.

The head of finance opens the revenue dashboard. It says 41,200 orders yesterday. The source order report says 42,610. After confirming that both reports should count the same statuses and business day, she traces the difference. She asks which number is right. That question, why do two systems disagree, and which one do we believe?, is a recurring question in data engineering, and answering it means walking the path the number travelled:

1. The shop’s database recorded 42,610 orders. Fine so far.
2. A nightly job converted the local-day boundary to UTC using a fixed offset. A daylight-saving change made that offset wrong for part of the interval, excluding 430 orders. 42,180 remain. Local time itself is not the error; the incorrect boundary conversion is.
3. A transformation joined orders to customers to add the country. An inner join keeps only orders with a matching customer. It dropped 970 orders whose customer records had been deleted, about 2.3% of the 42,180 remaining orders. 41,210 remain.
4. The dashboard’s “orders” tile counts only orders with status paid, as it always has, but a new captured status appeared in March and nobody told the dashboard. 41,200.

Every step reported success, but the final count is 1,410 below the source count: 1,410 / 42,610 is about 3.3%. This is a silent data failure: a process completes without detecting a wrong result. The lab models lost rows and missing status handling with simple query changes. It does not implement a daylight-saving conversion or reproduce these exact counts.

Where data comes from

A source system is anything that creates records as a side effect of doing its real job. Sources differ in shape, in how they can be read, and in how they break.

In the table, CDC means change data capture: reading inserts, updates, and deletes rather than only the latest rows. An API is an interface a program calls to request data; SaaS means hosted software. CSV stores tabular text, JSON stores structured values, and SFTP transfers files over an encrypted connection. A schema describes fields and their types. Backpressure means controlling incoming work when processing cannot keep up. The categories overlap: an application event can arrive in a stream or in a file.

A source is often owned by a product team or vendor with its own requirements. Agree on how changes will be communicated rather than assuming the interface will stay fixed. Also ask whether the source keeps current state, such as a customer’s present address, or events and history, such as dated address changes. An operational database can store either or both. If updates overwrite the only copy, later queries cannot recover the old values; history must have been retained in the source, a change log, a backup, or another permitted store.

The lifecycle

The following lifecycle describes the responsibilities between a source and a decision. Systems can combine stages or revisit them: validation is useful both before and after transformation, and some applications read a source directly.

generate → collect → store raw → validate → transform → serve → consume → retire
   │          │          │           │           │          │        │         │
 source    ingestion   landing    quality     modeling   warehouse  BI/ML   retention
 systems              zone       checks      (SQL/Spark)  / mart   / apps   & deletion

• Collect (ingestion). Get the data out of the source and into your platform, without losing any and without harming the source. Batch (nightly copy) or streaming (continuous).
• Store raw. Retain an unmodified copy when permitted, for a defined purpose and retention period. It gives you a stable input for investigating or rebuilding a transformation. Re-extracting from the source is another option only if the required history is still available.
• Validate. Check that what arrived is what was expected, row counts, required fields, plausible values, before anything builds on it.
• Transform (modeling). Clean, deduplicate, join, aggregate, and reshape into tables that answer business questions. This is where the meaning is decided: what counts as an order, when a customer is “active”, which currency revenue is in.
• Serve. Put the result somewhere fast enough and safe enough for the consumers: a warehouse for analysts, an API for an application, a feature table for a model.
• Consume. Dashboards, reports, models, and increasingly, data sent back into operational systems or used to answer questions in natural language.
• Retire. Apply the agreed retention and deletion policy, including its obligations and exceptions. Track derived copies as well as the original data.

Every stage can lose rows, duplicate rows, or change meaning. The Tuesday-morning story had one failure in collect (the timezone), one in transform (the join), and one in consume (the dashboard’s stale filter). A platform is “good” to the extent that each stage makes its failures visible to the next.

What a data product is

For this article, a data product is a maintained dataset or interface intended for specific users and a defined use. A table name alone does not tell a consumer what its rows mean, when it updates, or whom to contact. The table below turns those questions into commitments.

For example, a daily sales table might include only paid and captured orders, group them by UTC date and currency, exclude refunds, and be ready by 06:00 UTC. Its owner must agree on those definitions with its users. A check can establish that the table matches the source under those rules; it cannot prove that the source recorded every sale correctly or that the rules fit every business use. Trust is specific to the stated purpose and the evidence available.

The problems the field actually solves

Strip away the tools and there are seven recurring problems. Every technology in the series is a particular answer to one or more of them.

• Volume. The data no longer fits on one machine, or one machine takes too long. Answer: split it across many, store it in formats built for scanning.
• Reliability. It needs predictable runs, automatic recovery for expected failures, and escalation when recovery needs a person. Answer: task scheduling and coordination, retries, and idempotent design: repeating the same input does not add duplicate results.
• Correctness. Duplicates, dropped rows, wrong time zones, definitions that drift. Answer: modeling discipline, tests and assertions.
• Latency. How fresh does it need to be: a day, an hour, a second? Shorter deadlines can require different ingestion, state management, and recovery designs; there is no fixed complexity multiplier. Answer: choose deliberately, stream only where it pays.
• Change. Sources change shape; requirements change; the team changes. Answer: raw zones, contracts, version control, documentation.
• Security and privacy. Combining data from several sources can expose sensitive relationships, so access must match purpose. Answer: access control, classification, encryption, deletion.
• Cost. Charges depend on the service: stored data, scanned data, compute time, and transfer can all contribute. Answer: file sizes, partitioning, right-sizing, and looking at the bill.

These problems also arise with a single 50 GB database. More machines may address a capacity limit, but they do not resolve inconsistent definitions or unannounced source changes. Identify the actual failure before choosing a tool.

Batch and streaming, briefly

Batch processing handles a bounded collection, such as an exported file or one day’s orders. Stream processing handles a continuing flow; it may process individual events or small groups and does not guarantee a particular delay. Hourly reporting may need only a scheduled batch. An alert that must react within seconds needs a path designed for that deadline. Both approaches must handle duplicates and late data; streaming also makes continuously maintained state and recovery important.

Choose the least complex approach that meets the agreed freshness and recovery requirements. Ask what action the user will take when new data arrives, and how much delay that action can tolerate.

Who does what

Job titles vary. The roles below describe responsibilities that need an owner. A feature is an input to a model; point-in-time-correct training data uses only information available at the prediction time. Orchestration means scheduling tasks and coordinating their dependencies.

One person may cover several roles, or responsibilities may be split across teams. Agree on who owns source changes, metric definitions, pipeline failures, and consumer support. Two technically correct pipelines can still disagree if their teams use different definitions of the same metric.

The modern platform at a glance

Here is one possible arrangement. The product names are examples, not a required shopping list. A data lake keeps files for different uses; a warehouse provides managed analytical tables and queries; a lakehouse adds table-management capabilities over lake storage. The roles can overlap. Parquet is a file format; Iceberg manages table metadata and snapshots across files. They sit at different layers and can be used together.

SOURCES            INGESTION                STORAGE & PROCESSING                    SERVING
─────────          ─────────────────        ────────────────────────────────        ──────────────────
databases    ──►   batch / CDC        ──►   ┌─ data lake ────────────────────┐      warehouse / lakehouse
                   [Airbyte, Fivetran,      │ object storage (S3/GCS/ADLS)   │ ──►  [Snowflake, BigQuery,
events       ──►    Debezium]               │ files: Parquet; tables: Iceberg│       Databricks, Redshift]
                                            │                                │              │
logs         ──►   streaming         ──►    │ processing: SQL, Spark, Flink  │      ┌───────┼────────┐
                   [Kafka, Kinesis,         │ transformation: dbt            │      BI      apps     ML / AI
files, APIs  ──►    Pub/Sub, Fluent Bit]    └────────────────────────────────┘   [Tableau, [reverse  [features,
                                                                                  Power BI]  ETL]     RAG]

CROSS-CUTTING:  orchestration [Airflow, Dagster] · quality [Great Expectations, dbt tests] · catalog & lineage
                [DataHub, OpenMetadata, OpenLineage] · security & privacy · infrastructure as code [Terraform] · cost

In the serving layer, BI means reports and dashboards; reverse ETL sends prepared data back into business applications; RAG retrieves documents to help a language model answer a question. A catalog helps users find datasets, while lineage records where data came from and how it was transformed. The diagram illustrates three design choices:

• Storage and compute are separate. In this design, files can remain in object storage while compatible engines process them. Compatibility, permissions, and total operating cost still need checking; not every platform uses this arrangement.
• Transformation is code. Shared definitions can live in tested, version-controlled SQL or a governed semantic model, rather than being reimplemented independently in every dashboard.
• The cross-cutting row is where trust comes from. Orchestration, quality, lineage, and access are not features added at the end; they are the difference between a pile of tables and a platform.

How data fails quietly: the catalogue

A pipeline can finish successfully while producing incomplete, outdated, or misinterpreted data. The examples below show what to inspect and which checks can reveal the problem.

Choose checks and responses that fit the use. A missing settlement file may require blocking a finance report; a late dashboard refresh may call for a visible stale-data label and an alert. Agree on the tolerance and owner rather than stopping every pipeline at an arbitrary percentage difference. Reconciliation compares results with a reference. Counts are one check; keys, amounts, and definitions may also need comparison at the relevant level. Matching counts alone can hide missing records offset by extra ones.

What the work is actually like

The balance of work varies with the team and platform. Three activities recur:

• Understanding sources and meaning. What does this column mean? Why are there three status codes for “paid”? Who decides what a refund is? Reading other people’s schemas, talking to the people who own them, and writing down what you learned.
• Building and changing pipelines. The SQL, the Python, the configuration. Mostly small changes to existing things; rarely a blank page.
• Operating and investigating. Why is it slow, why is it wrong, why did it not run. The Tuesday-morning question, in its thousand variants.

SQL, data modeling, Python, and an understanding of storage and processing systems support this work. So does explaining to a non-technical user why two numbers differ, what has been checked, and what remains uncertain. You do not need to know their syntax to follow this introduction.

Four misconceptions to drop early

• “It is about big data.” A small dataset can also have missing records, inconsistent definitions, and access problems. The hard problems are correctness, change, and ownership, at any size.
• “Real-time is better.” Real-time delivery is useful when a timely action needs it. It also brings operating requirements that should be justified by that use, rather than by the label alone.
• “The tool will fix it.” A warehouse, a catalog, or an orchestrator does nothing about a metric with no owner. Tools amplify practices; they do not replace them.
• “Just make the dashboard right.” The dashboard is the last link in a chain that starts in a system the data team does not control. Making it right means making the whole chain right, which is why the job is a platform and not a report.

Lab

The first two exercises require no code. The last two use Python’s standard library and SQLite, a database stored in a local file. If Python or SQL is new, follow the explanation of inputs, outputs, and failures now, then return to run the code once you are comfortable with basic Python and SQL. Run the two Python blocks in order in one session. The dates and records are synthetic, not historical operating data. The lab demonstrates ingestion, transformation, basic checks, and a queryable result; it does not implement scheduling, access control, or a retention service.

1. Interview a source. Pick an app you use every day. List ten things it must record about you to work (an order, a login, a message). For each, write: what system probably creates it, whether it is state or an event, and one way it could change without warning.

2. Trace a metric. Take one number you have seen on a real dashboard at work or in a public report. Write the chain from source to screen as best you can guess it, and mark every point where the Tuesday-morning failures could occur.

3. Build the smallest possible platform. This one is shown in full rather than hidden behind a solution, because the next exercise builds on it. Using generated shop exports as the “source”: (a) copy the raw CSVs unchanged into a raw/ folder with the date in the path; (b) load them into a database; (c) build one table, daily_revenue, with one row per day per currency; (d) add three checks and stop the normal run if any fails; (e) write a README saying what the table means and who would own it. This is a local demonstration of several lifecycle responsibilities, not a complete production platform.

import csv, random, shutil, sqlite3, tempfile
from pathlib import Path

root = Path(tempfile.mkdtemp())
# the source: exports from the shop's database, one customers file and one orders file per day
source = root / "source"
source.mkdir()
rng = random.Random(0)
with (source / "customers.csv").open("w", newline="") as f:
    writer = csv.writer(f)
    writer.writerow(["customer_id", "country"])
    for i in range(1, 201):
        writer.writerow([f"C{i}", rng.choice(["KR", "JP", "US"])])
days = ["2026-03-12", "2026-03-13", "2026-03-14"]
for day in days:
    with (source / f"orders_{day}.csv").open("w", newline="") as f:
        writer = csv.writer(f)
        writer.writerow(["order_id", "customer_id", "ordered_at", "status", "amount", "currency"])
        for i in range(1, 301):
            # customer ids above 200 do not exist: deleted accounts, like the Tuesday story
            customer = f"C{rng.randint(1, 210)}"
            status = rng.choices(["paid", "captured", "refunded"], weights=[80, 10, 10])[0]
            writer.writerow([f"O{day[-2:]}{i:03d}", customer,
                             f"{day}T{rng.randint(0, 23):02d}:{rng.randint(0, 59):02d}:00Z",
                             status, f"{rng.uniform(5, 100):.2f}", rng.choice(["USD", "KRW"])])

# (a) collect and store raw: byte-for-byte copies, the date in the path, never modified
raw = root / "raw"
for day in days:
    dest = raw / "orders" / f"date={day}"
    dest.mkdir(parents=True)
    shutil.copy(source / f"orders_{day}.csv", dest / "part-0.csv")
(raw / "customers" / "date=2026-03-14").mkdir(parents=True)
shutil.copy(source / "customers.csv", raw / "customers" / "date=2026-03-14" / "customers.csv")
print(sorted(str(p.relative_to(root)) for p in raw.rglob("*.csv")))
# ['raw/customers/date=2026-03-14/customers.csv', 'raw/orders/date=2026-03-12/part-0.csv', 'raw/orders/date=2026-03-13/part-0.csv', 'raw/orders/date=2026-03-14/part-0.csv']

raw_customers = raw / "customers" / "date=2026-03-14" / "customers.csv"
# (b) load into a database (SQLite here; DuckDB reads the same files with read_csv)
con = sqlite3.connect(root / "platform.sqlite")
con.execute("CREATE TABLE customers (customer_id TEXT, country TEXT)")
con.execute("CREATE TABLE orders (order_id TEXT, customer_id TEXT, ordered_at TEXT, status TEXT, amount REAL, currency TEXT)")
with raw_customers.open(newline="") as f:
    con.executemany("INSERT INTO customers VALUES (?, ?)", (tuple(r.values()) for r in csv.DictReader(f)))
for path in sorted(raw.glob("orders/date=*/part-0.csv")):
    with path.open(newline="") as f:
        con.executemany("INSERT INTO orders VALUES (?, ?, ?, ?, ?, ?)", (tuple(r.values()) for r in csv.DictReader(f)))
print(con.execute("SELECT count(*) FROM orders").fetchone(), con.execute("SELECT count(*) FROM customers").fetchone())
# (900,) (200,)

DAILY_REVENUE = """
CREATE TABLE daily_revenue AS
SELECT substr(ordered_at, 1, 10) AS day, currency,
       count(*) AS orders, round(sum(amount), 2) AS revenue
FROM   orders
WHERE  status IN ('paid', 'captured')
GROUP BY 1, 2
"""
# (c) transform: one row per UTC day per currency, paid or captured orders only
con.execute(DAILY_REVENUE)
for row in con.execute("SELECT * FROM daily_revenue ORDER BY day, currency"):
    print(row)
# ('2026-03-12', 'KRW', 140, 6913.55)
# ('2026-03-12', 'USD', 129, 6789.17)
# ('2026-03-13', 'KRW', 139, 6792.05)
# ('2026-03-13', 'USD', 136, 7133.85)
# ('2026-03-14', 'KRW', 130, 6866.6)
# ('2026-03-14', 'USD', 147, 7577.94)

ASSERTIONS = {
    "one_row_per_day_currency": "SELECT count(*) = count(DISTINCT day || currency) FROM daily_revenue",
    "fresh":                    "SELECT max(day) = '2026-03-14' FROM daily_revenue",
    "count_reconciles":         """SELECT (SELECT sum(orders) FROM daily_revenue)
                                    = (SELECT count(*) FROM orders WHERE status IN ('paid', 'captured'))""",
}
# (d) validate: three checks that must all be true before anyone reads the table
def run_assertions(con):
    return [name for name, sql in ASSERTIONS.items() if con.execute(sql).fetchone()[0] != 1]

failures = run_assertions(con)
print(failures)
# []
if failures:
    raise RuntimeError(f"Validation failed: {failures}")
con.commit()
readme = root / "README.md"
# (e) document
readme.write_text("""# daily_revenue
One row per UTC day per currency. Orders with status paid or captured; refunds excluded.
Source: raw/orders/date=*/part-0.csv (shop exports). Owner: data platform team.
Demo: each script run creates a new temporary directory. Exercise 4 rebuilds from loaded orders.
""")
print(readme.read_text().splitlines()[1])
# One row per UTC day per currency. Orders with status paid or captured; refunds excluded.

The six output rows are one row per UTC date and currency. For example, the first row contains 140 qualifying KRW orders and a synthetic total of 6,913.55. The statuses paid and captured are both counted only because this example defines them that way; real payment systems may distinguish capture from settlement. Refunds are excluded, so this is not a net-revenue or accounting model. The raw path contains the order date, not an independently recorded arrival time. The code leaves these copies unchanged but does not enforce immutability.

The checks test unique day/currency rows, the latest expected date, and the total count against the loaded orders. The normal run now raises an error when a check fails. An empty list means only that these checks passed: it does not prove all source files arrived, that every earlier day is present, or that amounts are correct. The next exercise exposes one such gap. This small demo uses SQLite REAL values and two-decimal rounding for every currency; those are simplifications, not a currency-specific money policy. For exact monetary arithmetic, choose an appropriate fixed-point or decimal representation. SQLite explains why binary floating-point values are approximate.

4. Break it quietly. Introduce each of three related query faults: omit the last hour, drop orders with no matching customer through an inner join, and omit captured amounts while retaining the order count, one at a time. Confirm that your three assertions catch at least two. Add an assertion for the one they missed.

def rebuild(sql):
    con.execute("DROP TABLE IF EXISTS daily_revenue")
    con.execute(sql)
    return run_assertions(con)

timezone_cut = DAILY_REVENUE.replace("WHERE  status IN ('paid', 'captured')",
    "WHERE  status IN ('paid', 'captured') AND substr(ordered_at, 12, 2) <> '23'")
# 1. model a missing-hour effect in the query; the loaded source stays intact
print("timezone cut:  ", rebuild(timezone_cut))
# timezone cut:   ['count_reconciles']
inner_join = DAILY_REVENUE.replace("FROM   orders", "FROM   orders JOIN customers USING (customer_id)")
# 2. the transform joins to customers to add a country, and the deleted customers' orders vanish
print("inner join:    ", rebuild(inner_join))
# inner join:     ['count_reconciles']
stale_filter = DAILY_REVENUE.replace("count(*) AS orders, round(sum(amount), 2) AS revenue",
    "count(*) AS orders, round(sum(CASE WHEN status = 'paid' THEN amount ELSE 0 END), 2) AS revenue")
# 3. the revenue column still counts only 'paid', as it did before 'captured' existed
print("stale filter:  ", rebuild(stale_filter))
# stale filter:   []

ASSERTIONS["revenue_reconciles"] = """WITH expected AS (
    SELECT substr(ordered_at, 1, 10) AS day, currency,
           round(sum(amount), 2) AS revenue
    FROM orders WHERE status IN ('paid', 'captured') GROUP BY 1, 2
)
SELECT NOT EXISTS (
    SELECT day, currency, revenue FROM expected
    EXCEPT SELECT day, currency, revenue FROM daily_revenue
) AND NOT EXISTS (
    SELECT day, currency, revenue FROM daily_revenue
    EXCEPT SELECT day, currency, revenue FROM expected
)"""
print("stale filter:  ", rebuild(stale_filter))
# stale filter:   ['revenue_reconciles']
print("clean:         ", rebuild(DAILY_REVENUE))
# clean:          []
con.commit()

Keep the temporary folder if you want to inspect its CSV files, SQLite database, and README. After closing the database connection, you can delete that folder when you no longer need the exercise data.