dbt project overview

The wolt_analytics dbt project transforms three raw CSV sources into a star-schema dimensional model that powers this platform. Task 1 focused on modeling — the 8 business questions in the brief are design guides, not questions to answer directly. The model is built so the Analytics Team can answer each with a simple SELECT … GROUP BY against mart tables, without JSON parsing or heavy joins at query time.

Layer architecture & reasoning

1. Staging — parse, clean, and standardise raw data

• stg_item_logs — Parses nested JSON payloads, deduplicates conflicting log_item_id rows (keeping valid positive prices), forward-fills null prices, and prepares SCD Type 2 inputs.
  Why: source item logs contain corrections and null prices that would break point-in-time revenue if used raw.
• stg_purchase_items — Explodes purchase baskets to one row per item per order.
  Why: category and product analysis requires line-item grain.
• stg_purchase_logs / stg_promos — Cleans types, keys, and promo windows.
  Why: downstream joins and date logic need consistent, typed columns.

2. Intermediate — business logic & historisation

• int_items_scd — Builds SCD Type 2 validity windows (valid_from / valid_to) for every item attribute change.
  Why: item prices and names change over time; orders must join to the price that was active when the order was placed.
• int_purchase_items_enriched — Point-in-time item join + promo matching at order timestamp.
  Why: centralises the hardest join logic once, so marts stay simple.

3. Marts — analytics-ready star schema

• fct_purchases (order grain) — Pre-computes fee ratios, distance buckets, promo flags, and first-purchase indicators.
  Why: delivery, fee, and promo-order questions should not require re-aggregation from line items.
• fct_purchase_items (line-item grain) — Carries point-in-time price, discount, and net value.
  Why: category/product revenue and promo volume analysis.
• dim_customers — Derives lifetime value, tenure, and behavioural segments (one-time → power user).
  Why: retention analysis should not recompute customer stats in every query.
• dim_items / dim_items_history / dim_promotions / dim_dates — Conformed dimensions for flexible slicing by product, promo, and time period.

Key design decisions

How to run locally

The downloadable ZIP is self-contained for evaluators: dbt project + DuckDB + raw CSVs + a short README. After unzipping the package:

# optional but recommended
python3 -m venv .venv && source .venv/bin/activate

pip install -r requirements-dbt.txt
cd wolt_analytics
dbt run
dbt test

Optional: rebuild DuckDB from the included CSVs with python3 load_raw_data.py at the package root. Validation queries in analyses/business_questions.sql show that all 8 Task 1 business questions are answerable from the marts.