End-to-End Data Engineering Project Using SQL

The five-layer architecture

This project uses a simulated online orders dataset (customers, products, and orders arriving as raw CSV rows) and pushes it through every layer a real analytics warehouse uses:

Layer 1 — Raw landing table

Every column is loaded as text, and nothing is cleaned or validated. This layer exists purely so the exact original data is always recoverable if a downstream bug is found later:

CREATE TABLE raw_orders (
  order_id      TEXT,
  customer_email TEXT,
  order_date    TEXT,
  amount        TEXT,
  status        TEXT,
  loaded_at     TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
-- Populated via COPY / bulk load from the source CSV, no transformation

Layer 2 — Staging (cleaning)

This is where messy reality gets handled: type casting, trimming whitespace, standardizing status values, and dropping exact duplicate rows using the pattern from removing duplicates without a unique ID:

WITH deduped AS (
  SELECT *,
    ROW_NUMBER() OVER (
      PARTITION BY order_id ORDER BY loaded_at DESC
    ) AS rn
  FROM raw_orders
)
SELECT
  TRIM(order_id)::INT                       AS order_id,
  LOWER(TRIM(customer_email))          AS customer_email,
  CAST(order_date AS DATE)              AS order_date,
  CAST(amount AS DECIMAL(10,2))         AS amount,
  CASE LOWER(TRIM(status))
    WHEN 'complete'   THEN 'completed'
    WHEN 'completd'   THEN 'completed'   -- known typo in source system
    ELSE LOWER(TRIM(status))
  END                                     AS status
FROM deduped
WHERE rn = 1
  AND amount IS NOT NULL
  AND order_date IS NOT NULL;

Every rule here should be something a reviewer can point to and understand why — a documented typo fix, an explicit null filter, a deliberate dedup key. That documentation is as much a part of the project as the SQL itself.

Layer 3 — Dimension tables

Conformed customer records, ready to be joined against by any fact table. This project uses a full SCD Type 2 pattern so customer attribute changes over time are preserved rather than overwritten:

CREATE TABLE dim_customer (
  customer_sk          INT PRIMARY KEY,
  customer_email       TEXT NOT NULL,
  signup_cohort_month  DATE,
  effective_start_date DATE NOT NULL,
  effective_end_date   DATE,
  is_current           BOOLEAN NOT NULL DEFAULT TRUE
);
-- Populated via the expire-then-insert SCD Type 2 pattern

Layer 4 — Fact table

Fact tables join to dimensions by surrogate key, not business key — the exact reasoning covered in the SCD Type 2 article applies here directly:

CREATE TABLE fct_orders AS
SELECT
  s.order_id,
  d.customer_sk,
  s.order_date,
  s.amount,
  s.status
FROM stg_orders s
JOIN dim_customer d
  ON d.customer_email = s.customer_email
 AND s.order_date BETWEEN d.effective_start_date AND COALESCE(d.effective_end_date, '9999-12-31');

The join condition is deliberately date-ranged, not just on customer_email — this is what correctly pins each order to the exact customer dimension version that was true when the order happened.

Layer 5 — Reporting mart

The final, business-ready output — pre-aggregated so a BI tool or stakeholder query doesn't need to re-derive it every time:

CREATE TABLE mart_monthly_revenue AS
SELECT
  DATE_TRUNC('month', order_date) AS month,
  COUNT(DISTINCT order_id)   AS total_orders,
  COUNT(DISTINCT customer_sk) AS unique_customers,
  SUM(amount)                     AS total_revenue
FROM fct_orders
WHERE status = 'completed'
GROUP BY DATE_TRUNC('month', order_date)
ORDER BY month;

A note on orchestration

In production, these five scripts would run in sequence via a scheduler (Airflow, Dagster) or a transformation framework (dbt) that tracks dependencies between them automatically. For a portfolio project, that infrastructure isn't the point being demonstrated — a numbered sequence of .sql files, run in order with a short README explaining the dependency chain, proves the same underlying skill without requiring a reviewer to set up an orchestration tool just to evaluate it.

Project structure for GitHub

ecommerce-data-pipeline/
├── README.md
├── sql/
│   ├── 01_raw_layer.sql
│   ├── 02_staging_layer.sql
│   ├── 03_dimension_layer.sql
│   ├── 04_fact_layer.sql
│   └── 05_mart_layer.sql
├── sample_data/
│   └── raw_orders_sample.csv
└── docs/
    └── architecture-diagram.png

Common mistakes

• Skipping the raw layer entirely. Transforming directly from the source with no untouched copy makes it impossible to recover from a downstream bug that turns out to be a bad transformation, not bad source data.
• No SCD strategy on dimensions. Overwriting dimension attributes in place quietly breaks the fact table's ability to reflect historically accurate joins.
• Undocumented cleaning rules. A reviewer should be able to see why each staging transformation exists, not just that it does.
• Joining facts to dimensions on the business key instead of the surrogate key — this defeats the entire point of the dimensional layer.
• No sample data included in the repo. A pipeline a reviewer can't actually run against real rows is much less convincing than one they can.