Data systems engineering, part 1: from raw order rows to trusted revenue

A data pipeline can produce a result without producing a trustworthy result.

That distinction became clear in the first project of my data systems engineering track. I started with three small ecommerce CSV files and a local DuckDB database. The data was intentionally small. The problems were not:

• an order referenced a customer that did not exist;
• an order contained a null amount;
• an order arrived twice with identical values; and
• an order arrived twice with contradictory values.

The project was not an exercise in writing one large SQL query. It was an experiment in deciding where trust is established, what happens to records that fail validation, and how to keep those records visible without allowing them to corrupt business metrics.

The implementation lives in a separate learning repository. This post explains the design, the observed results, the failure-mode experiment, and the lessons that carry forward to Spark, Kafka, lakehouse tables, and orchestration.

The question behind the project

The source model says that the orders table has one row per order. If that were always true, a daily revenue query could be as simple as:

SELECT order_date, SUM(amount)
FROM orders
WHERE status = 'completed'
GROUP BY order_date;

That query is only as reliable as the assumptions behind it. What happens if a customer reference is wrong? What happens if an amount is missing? What happens if the same order appears twice? What happens if the duplicate rows disagree?

There are at least two different concerns here:

1. Preserve what the source sent so the pipeline remains auditable and replayable.
2. Decide which records are safe to use for business reporting.

Treating those as the same operation creates bad choices. A pipeline that drops invalid rows at ingestion hides source problems. A pipeline that sends every raw row to reporting turns source defects into financial errors.

The design separates the concerns into four stages:

Source files
    |
    v
Bronze: preserve source-shaped rows
    |
    v
Silver validation
    |-----------------------|
    v                       v
Trusted Silver          Quarantine with reason
    |
    v
Gold: trusted business aggregates

Bronze answers: "What arrived?"

Silver answers: "Which rows satisfy the data contract?"

Quarantine answers: "Which rows were excluded, and why?"

Gold answers: "What business result can we safely serve?"

The local toolchain

The project uses a small local stack so that data movement remains visible:

• uv manages the Python environment.
• Python runs the SQL helper scripts.
• DuckDB stores and queries the local database file.
• SQL expresses the transformations.
• pytest checks the data and pipeline invariants.

DuckDB is the database engine in this setup. It is not the same thing as a GUI such as DBeaver. The database is persisted in data/poc1.duckdb, while the project scripts open that file and execute SQL against it. DBeaver can be useful for inspecting the database, but the SQL files and commands remain the reproducible workflow.

The commands in this post were run from:

01-sql-foundations/

The project-managed form is important because it makes the Python and DuckDB versions explicit:

uv run python scripts/run_sql.py \
  data/poc1.duckdb \
  sql/00_load_bronze.sql

Start with grain, not with tables

Before loading data, I wrote down the grain and key of each table:

This is deliberately an order-level model. A production ecommerce system would usually need an order_items table with one row per product line. If an order can contain several products, storing only one product_id on orders is not enough. That limitation is part of the model, not a detail to ignore.

The project also clarified the meaning of the measures:

• quantity is the number of units in the order.
• unit_price belongs to the product catalog.
• amount is the extended order amount, not the unit price.
• amount must not be multiplied by quantity again.
• only completed orders contribute to trusted revenue.
• cancelled orders remain valid Silver records but do not contribute to Gold completed revenue.

For example, order O1002 has quantity 2, product P003, and amount 150.00. The product price is 75.00, so the amount already includes the quantity.

These definitions prevented a common analytical error: multiplying a value that already represents the full order by quantity a second time.

Bronze: preserve first, judge later

The clean source files were loaded into three source-shaped tables:

bronze_customers
bronze_products
bronze_orders

The Bronze loader uses DuckDB's CSV reader:

CREATE OR REPLACE TABLE bronze_orders AS
SELECT *
FROM read_csv_auto('data/orders.csv', header = true);

The baseline input contained 6 customers, 5 products, and 12 orders. The first completed-revenue query produced these daily totals:

Those values describe the clean baseline. They do not prove that the pipeline will behave correctly when the source violates its contract.

The deliberate fixture

The bad fixture contains six physical rows:

order_id,customer_id,product_id,order_date,quantity,amount,status
O1013,C007,P001,2026-03-07,1,1200.00,completed
O1014,C002,P003,2026-03-07,2,,completed
O1015,C003,P002,2026-03-08,1,850.00,completed
O1015,C003,P002,2026-03-08,1,850.00,completed
O1016,C004,P004,2026-03-09,1,1500.00,completed
O1016,C004,P004,2026-03-09,1,1500.00,cancelled

The four cases are intentionally isolated:

• O1013 has a syntactically valid-looking customer ID, but C007 is not in customers.csv.
• O1014 has a valid customer and product but no amount.
• O1015 occurs twice with every field equal.
• O1016 occurs twice and differs only in status.

The bad Bronze loader is intentionally boring:

CREATE OR REPLACE TABLE bronze_orders_bad AS
SELECT *
FROM read_csv_auto('data/orders_bad.csv', header = true);

The learner-run output was:

records_read | null_amount_records | distinct_order_ids
6            | 1                   | 4

The numbers reveal two separate facts:

• Six physical rows arrived.
• Only four order keys were distinct.

The difference is not noise. It is evidence that the source violated the declared one-row-per-order grain.

Bronze did not attempt to:

• look up C007 and discard the row;
• convert the null amount to zero;
• remove one copy of O1015; or
• choose a winner for the contradictory O1016 rows.

If Bronze performed those actions, later stages would lose the original source evidence. The raw row is valuable even when it is not trusted.

Silver: the trust boundary

Silver combines the clean and bad Bronze tables and assigns a classification to each row. It calculates three pieces of information for each order_id:

1. How many physical rows have that key.
2. How many distinct versions of the row exist.
3. Which occurrence is being processed.

The validation step also performs a left join to the customer table. A left join is important here. An inner join would silently drop O1013, which would make the source problem disappear instead of making it observable.

The classification policy is:

The order of these checks matters. A conflicting duplicate must not be reduced to an exact duplicate simply because it has a repeated key. A row with an unknown customer must not be rescued by assigning a guessed customer.

The learner-run Silver output was:

orders_conflicting_duplicate_row_count | 2
orders_exact_duplicate_rows_removed    | 1
orders_missing_customer_count          | 1
orders_null_amount_count               | 1
orders_quarantined_count               | 5
orders_records_accepted                | 13
orders_records_read                    | 18

The totals reconcile:

13 trusted Silver rows + 5 quarantined rows = 18 input rows

The five quarantined physical rows are:

O1013 → MISSING_CUSTOMER
O1014 → NULL_AMOUNT
O1015 → EXACT_DUPLICATE
O1016 → CONFLICTING_DUPLICATE
O1016 → CONFLICTING_DUPLICATE

Why O1015 is different from O1016

The two O1015 rows carry the same business information. Keeping one canonical copy does not require us to guess which value is correct. The extra copy can be counted and preserved in quarantine without changing the order's meaning.

The two O1016 rows do not carry the same information. One says completed; the other says cancelled. Choosing one would change whether the order contributes to revenue. The source has not supplied a timestamp, version, sequence number, or other authority for choosing between them, so both rows remain quarantined.

This is why "deduplicate by key" is not a complete data-quality policy. The pipeline must compare the values within each key group before deciding what a duplicate means.

Why a null amount is not zero

O1014 has a quantity and product, so it may be tempting to derive its amount as 2 * 75.00. That is not automatically safe. The current product catalog price is not necessarily the historical transaction price. Discounts, price changes, taxes, refunds, or other order-time adjustments could exist outside this small model.

A derivation rule can be valid if the business contract says that amount is always recoverable from the current catalog. This project does not have that contract. Therefore the safer policy is to preserve the null, quarantine the row, and avoid inventing a monetary value.

Quarantine is not deletion

The pipeline creates two derived outputs from validation:

silver_orders
quarantine_orders

silver_orders contains only rows with no quarantine reason. The invalid rows are not thrown away. quarantine_orders contains the original fields plus the reason that prevented the row from entering trusted Silver.

This distinction supports several operational workflows:

• an upstream team can correct the customer feed;
• a data steward can investigate the conflicting order;
• a later replay can accept a row after its missing relationship arrives;
• operators can monitor whether a quality problem is growing;
• auditors can trace a Gold number back to the decisions made about source rows.

A pipeline that reports only "five rows failed" is less useful than one that says which five rows failed and why.

Gold: calculate from trusted data

The Gold table is:

gold_daily_revenue_by_segment

It reads from silver_orders, joins to the validated customer relationship, keeps completed orders, and aggregates by order date and customer segment.

Gold does not read raw Bronze directly. That boundary prevents the business metric from bypassing the validation policy.

For the restored fixture, the verified Gold result contained twelve date-segment rows and total revenue of 10,825.00. The canonical O1015 row contributed 850.00 to the startup segment on March 8. The unresolved March 7 records and the conflicting March 9 records did not contribute to Gold.

The cancelled clean order also remained in trusted Silver but did not contribute to completed revenue. This is a useful distinction:

Silver validity and business eligibility are related, but not identical.

A cancelled order can be a valid order record. It simply has different business semantics for a completed-revenue metric.

The controlled failure experiment

The pipeline was not considered understood until one source value was changed on purpose.

The O1016 row with cancelled status was temporarily changed to completed:

O1016,C004,P004,2026-03-09,1,1500.00,completed

Both O1016 rows then became identical. After rerunning Bronze and Silver, the group was classified as an exact duplicate rather than a conflicting duplicate. The original value was restored and the pipeline was rerun again.

The expected metric transition for the temporary change was:

conflicting duplicate rows: 2 → 0
exact duplicate rows removed: 1 → 2
accepted Silver rows: 13 → 14
quarantined rows: 5 → 4

The interesting part is not the arithmetic. It is the scope of the effect. One field changed, but the classification of the entire order_id group changed. That group-level decision then changes what Silver considers trusted and what Gold may consider eligible.

This is a practical reason to test data-quality rules with controlled mutations. A rule can look obvious when viewed row by row and behave differently when keys, versions, and downstream aggregates are involved.

Idempotency and reruns

The derived tables use CREATE OR REPLACE TABLE. For this local experiment, a rerun rebuilds the derived state from the input rather than appending a second copy of the output.

That matters because data jobs are retried. A job can be interrupted after writing some tables, a scheduler can run it again, or an operator can rerun the command after correcting a source file. If the job appends blindly, revenue and quality counts can grow each time the job is retried.

Two sorted persistent snapshots of Silver, quarantine, metrics, and Gold were compared during verification. They were byte-identical across the two runs for this fixture.

This is a small local form of idempotency. Production systems need more machinery, including stable run IDs, partition overwrite or merge semantics, transaction boundaries, checkpoints, and concurrency controls. The principle is the same: repeating a job for the same input should not create a different business result.

Tests as executable data contracts

The project began with a clean-input test and added pipeline tests for the four-case fixture. The full learner-run suite finished with:

4 passed

The checks cover more than whether the SQL file executes. They assert that:

• the fixture contains the intended rows;
• the four cases remain isolated;
• the missing customer remains missing;
• the null remains null;
• exact duplicates are identical;
• conflicting duplicates differ in status;
• one canonical O1015 row enters Silver;
• the five expected physical rows enter quarantine;
• invalid dates do not appear in Gold;
• the canonical duplicate does not double-count revenue.

These tests are small, but they represent the beginning of a data contract. They make a future change visible if someone changes the fixture, rewrites the SQL, or alters the definition of trusted data.

What this local experiment hides

DuckDB makes the exercise easy to run on a laptop, which is the point of starting here. It also hides several problems that appear at scale:

• CSV files are small enough to scan repeatedly.
• There is no partition pruning problem.
• There are no large shuffles or skewed keys.
• There is no late or out-of-order event stream.
• There is no schema registry or catalog governance.
• There is no concurrent writer.
• A single local database avoids distributed commit coordination.
• The fixture has no source sequence number for resolving updates.

The local implementation should not be mistaken for a production architecture. It is a compact way to make the contracts visible before adding distributed components.

Mapping the experiment to a larger data platform

Spark will introduce distributed execution for the same kind of transformation. Kafka will introduce an event log and ordering questions. Streaming work will add windows, watermarks, state, and late data. Lakehouse work will add table metadata, snapshots, and catalogs. The technology changes, but the questions stay familiar:

• What is the grain?
• What is the key?
• What state does the job keep?
• What happens when it runs twice?
• Which rows are trusted?
• Where can an operator find rejected data?
• What evidence proves the result is correct?

The series roadmap

This post is the first entry in a broader data systems engineering series. The planned progression is:

1. SQL analytics, data modeling, and Bronze/Silver/Gold foundations.
2. Spark batch and distributed transformations.
3. Spark SQL execution plans and transformation workbenches.
4. Kafka event logs and message delivery semantics.
5. Streaming time, windows, watermarks, and state.
6. Lakehouse tables, metadata, snapshots, and catalogs.
7. CDC and current-state tables.
8. Orchestration, retries, and job control.
9. Catalogs, lineage, governance, and impact analysis.
10. Feature pipelines and an integrated capstone.

The order is intentional. It is easier to reason about Spark, streaming, and lakehouse behavior after the data contract and failure modes are clear in a single-process environment.

Revisit checklist

When I return to this project, I want to be able to answer these questions without looking at the implementation:

• What is the grain of each source and output table?
• Why does Bronze preserve invalid rows?
• Why is a missing customer different from a null amount?
• Why is a null not automatically zero?
• When is an exact duplicate safe to reduce?
• Why can a conflicting duplicate not be resolved from this source alone?
• Why does Gold read trusted Silver instead of raw Bronze?
• How do the accepted and quarantined counts reconcile?
• What changes when the same job runs twice?
• Which concerns appear only after moving to Spark or streaming?

The practical lesson from this first POC is simple: data quality is not a final filter placed after the reporting query. It is a set of explicit decisions about preservation, trust, evidence, and repeatability that must shape the pipeline from the first table onward.