FMCG Sales Analytics — dbt + DuckDB

An end-to-end analytics engineering project: raw sales data → cleaned and tested with dbt → modeled into a dimensional star schema → queried by a Streamlit dashboard. Runs entirely locally on DuckDB — free, open-source, no cloud account or API key required.

Built as a hands-on portfolio project to demonstrate the core of the Analytics Engineer role: turning raw, messy data into a tested, documented, business-ready model.

Why this project

I've spent 5+ years building production SQL/ETL pipelines (RFM segmentation, fraud detection, cloud migration to GCP BigQuery, Tableau/Power BI reporting). This project applies the same pipeline-engineering discipline using the modern analytics-engineering stack (dbt) end to end, including automated testing and CI.

Architecture

seeds/*.csv (raw_orders, raw_customers, raw_products)
        │
        ▼
┌───────────────────────────────────────────────┐
│ STAGING (models/staging/)                      │
│   stg_customers   — dedupe on customer_id      │
│   stg_products    — margin calc                │
│   stg_orders      — drops bad rows:            │
│     missing customer_id · non-positive qty ·   │
│     orphan product_id · duplicate order_id     │
│   stg_orders__rejected — every dropped row,    │
│     with the reason (nothing vanishes silently)│
└───────────────────────────────────────────────┘
        │
        ▼
┌───────────────────────────────────────────────┐
│ MARTS (models/marts/) — star schema            │
│   dim_customers · dim_products                 │
│   fact_sales (order-line grain, cost + margin) │
│   mart_sales_by_category  (monthly, by category)│
│   mart_customer_value     (lifetime value)     │
└───────────────────────────────────────────────┘
        │
        ▼
  dashboard/app.py (Streamlit, reads the marts directly)

30 dbt tests run across every layer (not_null, unique, relationships, accepted_values, plus one custom singular test asserting margin never exceeds revenue) — all passing (dbt build: 42/42 PASS, 0 errors).

The raw seed data has four intentional data-quality issues (a missing customer_id, a negative quantity, an orphan product reference, and a duplicate order_id) so the staging layer and tests have something real to catch — see stg_orders__rejected for where they end up.

Quickstart

git clone https://github.com/<your-username>/fmcg-sales-analytics.git
cd fmcg-sales-analytics
pip install -r requirements.txt

# Load seeds, build all models, run all tests
dbt build --profiles-dir .

# (Optional) generate & browse dbt docs / lineage graph
dbt docs generate --profiles-dir .
dbt docs serve --profiles-dir .

# Launch the dashboard
streamlit run dashboard/app.py

No database server or credentials needed — profiles.yml points at a local fmcg_analytics.duckdb file that dbt build creates.

Project structure

├── seeds/                    # raw CSVs (orders, customers, products)
├── models/
│   ├── staging/               # cleaning + data-quality logic
│   └── marts/                 # star schema + business-facing marts
├── tests/                    # custom singular dbt test
├── dashboard/app.py           # Streamlit BI dashboard on the marts
├── dbt_project.yml
├── profiles.yml                # local DuckDB target, no secrets
├── .github/workflows/ci.yml   # dbt build + test on every push
└── requirements.txt

Possible extensions

• Swap the DuckDB target for BigQuery/Snowflake/Postgres — only profiles.yml changes, none of the models.
• Add incremental models for the fact table instead of full rebuilds.
• Add dbt-expectations for richer statistical tests (e.g. revenue within an expected range).
• Schedule dbt build via Airflow/cron and have the dashboard read from a warehouse instead of a local file.

License

MIT — see LICENSE.