Skip to content
All projects

Project 02 // Data engineering · Jul–Aug 2025

Grocery Transaction Data Warehouse

A Medallion warehouse over 131M+ rows, in plain SQL Server

Row counts flow from Bronze (10 million transactions, 131 million lines) to Silver (3.3 million completed orders, 1.58 million failing the header-total check) to Gold (43.8 million fact rows).
Transaction lines loaded
131M+
Automated quality checks
21
Completed sales kept
3.3M

In short

The question

Retail transaction data typically arrives as messy, disconnected extracts. The goal: make it trustworthy and fast to query by landing it raw, cleaning and standardizing it, then modeling it for analytics on sales, customers, and product performance.

What I built

  1. 01
    CSV sources6 files, about 12 GB
  2. 02
    Bronze (BULK INSERT)10M transactions, 131M lines, kept as they arrived
  3. 03
    Silver (cleaned)3.3M completed orders, cleaned, 21 checks run
    • T-SQL stored procedures
    • Data-quality testing
  4. 04
    Gold (star schema)43.8M rows in a star schema
    • Dimensional modeling (star schema)
  5. 05
    AnalyticsSales, customer and product questions

What it found

Header total vs line items
  • Completed orders3.3M
  • Totals did not match1.58M · 47.5%

Revenue is rebuilt from line prices.

Share of revenue (synthetic data)
  • Top 20% of customers44.7%
  • A layered warehouse where each step can be re-run and audited independently.
  • Keeping completed orders cut 10M raw transactions to 3.3M; 21 automated checks run after each layer.
  • 1.58M orders flagged because their header totals did not match their line items (47.5% of completed orders), so revenue is rebuilt from line prices.
  • SQL Server
  • T-SQL
  • Medallion
  • ETL
  • Star schema

Full case study // tap a section to open it

01Problem & context

Retail transaction data typically arrives as messy, disconnected extracts. The goal: make it trustworthy and fast to query by landing it raw, cleaning and standardizing it, then modeling it for analytics on sales, customers, and product performance.

Retail transaction data usually arrives as messy, disconnected CSV extracts that nobody fully trusts, so analysts end up cleaning the same data again for every question. A layered warehouse lands the raw data once, cleans it once, and gives the business one tested, documented model for sales, customer, and product questions.

Built on the public Supermarket Transactional Data dataset by Nadin Ramadan on Kaggle. The warehouse design, ETL, tests, and documentation are my own work.

An end-to-end data warehouse in SQL Server that turns raw grocery-transaction CSV extracts into a business-ready star schema. It is built on the public Supermarket Transactional Data dataset on Kaggle; the warehouse design, ETL, tests, and documentation are my own work.

It follows the Medallion pattern: Bronze keeps an untouched copy of the six source files, Silver cleans and standardizes them, and Gold exposes a star schema of views that BI tools and analysts can query directly.

The whole load is automated with stored procedures that log their timing and catch errors, and 21 automated data-quality checks run after each layer: keeping completed orders cut 10M raw transactions to 3.3M and flagged 1.58M orders whose header totals did not match their line items.

02My role & approach

Solo project: architecture, stored-procedure ETL, dimensional modeling, data-quality tests, and documentation.

  1. 01

    Bronze: a raw copy of six source CSVs (brands, categories, customers, products, transactions, transaction lines), loaded by a truncate + BULK INSERT procedure with load timing and TRY/CATCH error handling.

  2. 02

    Silver: cleaned and standardized data (type casting, value normalization, text cleanup) loaded by its own procedure with per-table timing.

  3. 03

    Gold: a business-ready star schema exposed as views.

  4. 04

    Documented a column-level data catalog and naming conventions for schemas, tables, columns, and procedures.

Problems I hit and how I handled them

  • Loading 131M+ rows reliably: a truncate + BULK INSERT procedure with load timing and TRY/CATCH, so a failed load is logged instead of silently leaving partial data.
  • Inconsistent source values (whitespace, gender, loyalty tier, nationality codes) are standardized in Silver and then verified by automated tests.
  • Keeping Gold trustworthy: tests for surrogate-key uniqueness and fact-to-dimension referential integrity.
  • 21 automated data-quality checks filtered 10M raw transactions to 3.3M completed sales and flagged 1.58M orders whose header totals did not match their line items.
03Tech stack & data model
  • Microsoft SQL Server

    Database engine for the whole warehouse: bronze, silver, and gold schemas in one database.

  • SQL Server Management Studio

    Writing, testing, and debugging every script and stored procedure.

  • T-SQL stored procedures

    bronze.load_bronze and silver.load_silver, with per-table load timing and TRY/CATCH error handling.

  • BULK INSERT

    Fast loading of large CSV extracts into Bronze with a truncate-and-insert full load.

  • Medallion architecture

    Separates raw ingestion, cleaning, and business modeling so each step is auditable.

  • draw.io and Markdown

    Architecture, data-flow, integration, and star-schema diagrams, plus a data catalog and naming conventions.

Data model

The Gold layer is a classic star schema: one fact table at line-item grain with two dimensions joined on surrogate keys.

  • Fact table

    gold.fact_sales

    One row per product per transaction: transaction_line_id, transaction_id, customer_key, product_key, transaction date, price, and total amount.

  • Dimension

    gold.dim_customers

    Surrogate customer_key plus customer ID, name, gender, date of birth, city, nationality, joining date, and loyalty tier.

  • Dimension

    gold.dim_products

    Surrogate product_key plus product name, category, brand, and price, denormalized from three Silver tables.

Skills shown

  • Data warehouse design
  • Medallion architecture
  • Dimensional modeling (star schema)
  • T-SQL stored procedures
  • Data-quality testing
  • Technical documentation
04More results
  • In this synthetic data, the top 20% of customers generate 44.7% of $3.4B in revenue.
  • Data: a synthetic Kaggle retail dataset (about 12 GB of CSV). Results were re-checked by running the same SQL logic in DuckDB.
05What I learned & would improve

Lessons

  • Header totals cannot be trusted blindly: 47.5% of completed orders did not match their line items, so quality tests belong inside the pipeline, not after it.
  • Layered Bronze, Silver, and Gold loads make every step re-runnable and easy to audit.

What I would do next

  • Load new data incrementally instead of truncating and reloading every table.
  • Record timings for the full load on SQL Server, and alert when a quality check fails.

Walkthrough

What each dashboard and diagram shows, and why it matters. Click an image to enlarge it.

  1. 1

    High-level architecture

    Six source CSVs flow left to right through three layers of increasing refinement before BI tools consume them.

    • Bronze: raw tables, batch full load (truncate and insert), no transformation.
    • Silver: the same tables cleaned, standardized, and normalized, with derived columns.
    • Gold: views that integrate and model the data as a star schema for analytics.
  2. 2

    Table-level data flow

    Each source maps one-to-one into Bronze and Silver, then converges into three Gold tables.

    • transactions + transaction_lines become gold.fact_sales.
    • products + brands + categories become gold.dim_products.
    • customers becomes gold.dim_customers.
  3. 3

    Integration model

    How the six source tables relate before modeling: sales, product, and customer domains linked by foreign keys.

    • transaction_lines is the grain: each row links to a transaction and a product.
    • Join paths: lines → transactions → customers, and lines → products → brands and categories.
    • These paths are flattened in Gold so analysts never have to write them again.
  4. 4

    Bronze load procedure

    Truncate and BULK INSERT each table, time every step, and catch failures.

    • Load timing is printed per table, so slow steps are easy to spot.
    • TRY/CATCH reports the error instead of leaving a half-loaded layer behind silently.
  5. 5

    Silver data-quality checks

    Automated tests run after the Silver load.

    • Null or duplicate keys, unwanted whitespace, and broken references between tables.
    • Unexpected categorical values such as gender, loyalty tier, and nationality.
    • Gold tests then check surrogate-key uniqueness and fact-to-dimension integrity.
Next projectSales Planning Analysis
Esc

↑ ↓ to moveEnter to open