Data Career Hub
Menu
SQLPythonPySparkApache SparkKafkaAirflow
ProjectsProject 3 of 8

Intermediate project · Project 3 of 8

E-commerce Analytics Data Platform

An online store has orders, customers, products and web sessions in separate systems. Build an ELT platform that models them into trusted marts for revenue, retention and product performance.

  • Intermediate
  • SQL warehouse (DuckDB or PostgreSQL locally, or a cloud warehouse) · dbt Core or plain SQL scripts · Python for data generation · A BI tool or notebook for charts
  • 2 min read
  • Updated Oct 2026

Requirements

  • Load four source datasets into raw tables
  • Build staging models that type, rename and deduplicate
  • Build a star schema with order-line facts and customer, product and date dimensions
  • Define revenue, orders and repeat-customer rate once and reuse them
  • Test keys, nulls and relationships on every model

Technology stack

SQL warehouse (DuckDB or PostgreSQL locally, or a cloud warehouse), dbt Core or plain SQL scripts, Python for data generation, A BI tool or notebook for charts

Dataset

Generate synthetic orders, customers, products and sessions with a script, or use a public e-commerce sample dataset whose licence permits reuse.

Business context

Teams at the store disagree about revenue because each pulls numbers differently. A single modelled layer with tested, documented metrics is how real analytics teams resolve that, and it is the core skill set of analytics engineering.

Architecture

  1. Raw schema holds orders, customers, products and sessions as loaded.
  2. Staging models clean and type one source table each.
  3. Intermediate models join and apply business rules.
  4. Marts: fct_order_lines, dim_customer, dim_product, dim_date and daily aggregates.
  5. Certified metrics feed a dashboard.
Layered SQL models from raw to marts, each with tests.

Write down metric definitions before writing SQL, for example whether revenue includes tax, shipping and refunds. Most real disagreements are about definitions, not code.

Build steps

  1. Write down the business questions and the grain of each fact table
  2. Load raw data into a raw schema unchanged
  3. Create one staging model per source table
  4. Build dimensions, including a date dimension
  5. Build fct_order_lines and a daily sales aggregate
  6. Add tests for uniqueness, not-null, accepted values and relationships
  7. Define certified metrics in one place and build a small dashboard
  8. Document lineage and model descriptions

Testing

  • Tests on every model's primary key
  • Reconciliation: total revenue in the mart equals total in raw orders minus cancelled orders
  • A rerun of the whole project produces identical results

Data quality

  • Orders reference existing customers and products
  • No negative quantities
  • Order dates within the expected range

Monitoring

  • Model run times and test results stored per run
  • Freshness of each source

Cost considerations

  • Runs free locally with DuckDB or PostgreSQL
  • On a cloud warehouse, use the smallest warehouse and incremental models for the largest facts

Interview questions this project prepares you for

  • What is the grain of your fact table?
  • How did you define a repeat customer, and why?
  • How do you know your revenue number is correct?
  • Why did you choose incremental or full rebuilds for each model?

Resume bullets (add only what you measured)

Use these as structure. Replace the wording with results you actually produced and can show; never claim numbers you did not measure.

  • Designed and built a layered ELT analytics platform (staging, star-schema marts, certified metrics) for an e-commerce dataset with automated tests on every model
  • Implemented reconciliation checks between source and mart revenue and documented lineage for all models

Extensions

  • Add SCD Type 2 history for customer segments
  • Add sessions and a funnel model
  • Schedule with an orchestrator and add alerting

By Data Career Hub Editorial · Last reviewed Oct 2026

Progress is saved in this browser only. No account needed.

Search
Filter by type