arrow_backBack to Projects
database

E-commerce Shop Data Pipeline

Data Engineering
Oct 31, 2026visibility4

An e-commerce ELT data pipeline modeling transactional events into Kimball star schema presentation marts with automated data testing using dbt, sql, and python, in a DuckDB local warehouse.

dbtDuckDBSQLPython

A straightforward breakdown of what this project is, the problem it solves, and how it works under the hood.

TL;DR:
This project is an analytical data warehouse project that transforms raw e-commerce transaction data (customers, orders, and split tender payments) into clean, analytics-ready Kimball star schema marts using dbt, DuckDB, and SQL.


1. The Problem: Why I Built This

I built this project to move beyond theory and implement a complete, reproducible e-commerce data pipeline locally from scratch. I wanted hands-on experience designing schemas, managing table grains, and authoring automated quality tests that guarantee trustworthy metrics.


2. What It Does: The Star Schema Marts

The pipeline models raw transactional data into two clean presentation marts optimized for business intelligence and reporting:

1. fct_orders (The Order Fact Table)

  • Grain: Exactly one row per order_id.
  • Purpose: Represents individual order transactions. It stores the customer reference, order timestamp, status lifecycle, total monetary value, and additive breakdown amounts per payment method (credit card, coupon, bank transfer, gift card).

2. dim_customers (The Customer Dimension Table)

  • Grain: Exactly one row per customer_id.
  • Purpose: Represents unique customer entities enriched with accumulated profile metrics. It provides first order date, most recent order date, total order count, and lifetime value segmented by payment method, allowing analysts to filter high-value cohorts without repeatedly joining large transactional tables.

3. How It Works: The Pipeline Architecture

The pipeline follows a clean, unidirectional Directed Acyclic Graph (DAG) separating staging, intermediate transformations, and presentation marts:

[Raw Ingestion (raw_xyz)]
  (customers, orders, payments)
             |
             v
[Staging Layer (stg_)]
  (stg_customers, stg_orders, stg_payments)
             |
             v
[Intermediate Layer (int_)]
  (int_order_payments: payment pivoting & grain alignment)
             |
  +----------+----------+
  |                     |
  v                     v
[fct_orders]     [dim_customers]
(Transaction Fact) (Enriched Entity Mart)

The transformation executes in three sequential phases:

  1. Staging (models/staging/): Cleans raw ingestion tables, casts column types to appropriate SQL types, and applies boundary integrity tests (unique, not_null, accepted_values).
  2. Intermediate (models/int/): Isolates multi-payment rollups. It aggregates individual payments to the order grain and dynamically pivots method amounts, ensuring downstream fact tables join on a clean 1-to-1 relationship.
  3. Marts (models/mart/): Assembles the final presentation layer. Joins staged orders with intermediate payments to produce fct_orders, and joins staged customers with customer order rollups to generate dim_customers.

4. Enforcing Data Integrity: The 63-Test Suite

To guarantee that models remain dependable, the project implements a comprehensive 63-test automated quality suite:

Primary & Foreign Key Contracts

Every model across staging, intermediate, and marts defines unique and not_null assertions on its primary key. Foreign key relationships are tested across layer boundaries, verifying that every order references a valid customer in dim_customers.

Custom Generic Assertions (is_non_negative)

Authored a reusable generic dbt test that verifies monetary amounts and transaction counts never drop below zero across any model.

Singular Accounting & Reconciliation Tests

Standalone assertion queries catch complex relational and mathematical discrepancies:

  • Payment Method Balance: Confirms that order total_amount strictly equals the sum of its payment components (credit_card_amount + coupon_amount + bank_transfer_amount + gift_card_amount).
  • Revenue Reconciliation: Pre-aggregates fact orders to the customer grain to verify that total transactional revenue in fct_orders perfectly balances customer lifetime value in dim_customers with zero join fan-outs.
  • Chronological Validity: Ensures that every customer's first_order_date occurred on or before their most_recent_order_date.

5. The Tech Stack

  • Transformation Framework: dbt
    Coordinates modular SQL compilation, lineage modeling, schema documentation, and test execution.
  • Analytical Storage: DuckDB
    Fast, local in-process columnar OLAP database providing instant query execution and zero cloud infrastructure cost.
  • Query Language: SQL + Jinja
    Expresses dimensional modeling logic, window aggregations, and dynamic payment pivoting.
  • Synthetic Data Generation: Python
    Simulated realistic e-commerce datasets spanning multi-status order lifecycles and split payment tenders.

6. What I Learned

  1. Grain is Non-Negotiable: If you cannot define the exact grain of a model in one clear sentence, downstream joins will inevitably create fan-outs and multiply metrics.
  2. Automated Assertions Build Real Confidence: Moving beyond basic null checks custom domain tests transformed a set of SQL queries into a production-grade data engineering asset.
Quokka AIQuokka