Skip to content

About

E-commerce scale-up analytics data platform with end-to-end data pipelines and a semantic layer to unify KPIs computation for different data consumming departments

Resources

Stars

1 star

Watchers

0 watching

Forks

Repository files navigation

TheLook Modern Data Stack (Snowflake, dbt, Code-First, IaC)

A hands-on modern data stack built around the public TheLook eCommerce dataset. Snowflake as the single analytic engine, Dagster + Metabase running always-on on OCI Free Tier, the whole platform declared via Terraform.

Status: Phase 1 (Infrastructure & Governance) closed: Snowflake RBAC, OCI VM, Terraform Cloud workflows, 13 ADRs. See the Phase 1 closure report. Phase 2 (data engineering) in progress: dlt ingestion, dbt Finance marts, the Cube Cloud semantic layer, Metabase self-service BI, and the KTX context layer for AI agents (MCP) are all live. The same canonical metrics are consumed by analysts (Cube Explore), end-users (Metabase), and AI agents (Claude via KTX MCP), cross-validated to the cent, with a Loom walk-through of the agent demo. Blog post is next.


Live surfaces

The following surfaces are produced by Phase 2. Phase 1 delivered the platform underneath (Snowflake RBAC, OCI VM, Terraform Cloud workflows, cost guardrails) but does not expose end-user surfaces.

Surface URL Availability
Dagster UI https://dagster.<domain>.dev Always-on (OCI), Phase 2
Metabase (self-service BI on Cube SQL API) metabase.gaelmukunde.dev 📡 Live docker-compose on OCI VM, TLS via Caddy + Let's Encrypt
dbt docs (lineage + descriptions) mukunde.github.io/thelook-project 📡 Live auto-deployed on merge to main (GitHub Pages)
Cube Cloud semantic layer (Finance) https://thelook.cubecloud.dev/ (auth-protected) 📡 Live auto-deployed on merge to main, Loom walk-through (modeling, view-only pattern, generated SQL, metric unicity) + screenshots below
KTX context layer (AI agents via MCP) local MCP server (semantic-context/) 📡 Live wired to Claude via Model Context Protocol, ADR-0013
AI agent demo (Cube semantic layer via KTX/MCP) Loom walk-through 📡 Live Claude querying the finance view through KTX MCP: metric unicity, the analytical-horizon guard, and the financial-return-rate rationale

Below: the finance view consumed across four surfaces with identical values to the cent, demonstrating single source of metric truth (ADR-0011: Strict Kimball reflection, single grain fct_order_items, view-only public surface). Same query (Net Revenue and Gross Margin Rate sliced by product category and year-month, filtered to non-returned items) rendered in Cube Cloud Explore (chart + tabular grid), in Metabase (consumed via Cube SQL API), and by AI agents through the KTX MCP tools (same values, e.g. Sweaters 2026-06 margin = 51.98% on every surface). The verification baseline, a direct SQL aggregation on ANALYTICS.MARTS.FCT_ORDER_ITEMS, returns the same values.

Cube Explore chart view: Net Revenue and Gross Margin Rate by category, 2026-05 vs 2026-06

Cube Explore tabular view: same query, formatted as currency and percent

Metabase chart of the same finance view consumed via Cube SQL API: same Net Revenue values per category and year-month

Beyond identical numbers, the KTX context layer gives AI agents the business context no schema carries. Asked about a metric for 2022, the agent does not invent a figure or report "missing data": it explains the data starts on 2023-01-01 by design (the analytical-horizon wiki page), the kind of guardrail that separates a trustable agent from a plausible-sounding one.

KTX agent declining to invent 2022 figures, citing the 2023-01-01 analytical horizon as a deliberate design choice


Finance domain status

The Finance domain is currently live in dev (Snowflake ANALYTICS_DEV.dbt_<initials>_marts). It exposes the following Kimball-style modelled tables, all contract-enforced, all tests passing:

Mart Type Grain Notes
dim_users dim one row per user Conformed. PII (first_name, last_name, email, street_address) pseudonymised SHA-256 + salt at staging
dim_products dim one row per SKU Conformed
dim_dates dim one row per calendar day Synthetic, generated via dbt_utils.date_spine, covers 2023-01-01 to current_date + 2 years
fct_order_items fact one row per (order, item) pair Exposes net_revenue, gross_margin, gross_margin_rate, is_returned, is_delivered, days_to_delivery
fct_orders fact one row per order Aggregated from fct_order_items. Exposes items_count, total_revenue_net, total_gross_margin, gross_margin_rate, is_fully_returned, is_partially_returned, average_item_price

Business metrics are defined once at the finest grain (fct_order_items) and propagated upwards by aggregation (fct_orders), so net_revenue has a single canonical definition (sale_price when item status is not 'Returned', else 0). See ADR-0010 for the narrative source mapping (Segment / Akeneo / Reflex / NetSuite / Shopify Plus / Snowplow + GA4) layered on top of the public TheLook dataset.

The full consumption stack is live: Cube Cloud semantic layer (ADR-0007, ADR-0011), Metabase self-service (ADR-0012), and the KTX context layer exposing the same metrics to AI agents over MCP (ADR-0013). The 4 canonical Finance measures (net_revenue, gross_margin_rate, financial_return_rate, avg_order_value) are exposed via a single finance view and consumed identically from Cube Cloud Explore (analysts), Metabase (end-user self-service), and Claude through KTX MCP tools (agents), cross-validated to the cent against direct SQL on ANALYTICS.MARTS.FCT_ORDER_ITEMS. KTX adds the business context no schema can carry: versioned wiki pages (never average rate columns, the 2023-01-01 analytical horizon, value-weighted vs volume return rates) that agents read before querying. A Loom walk-through shows Claude answering all three live. Next: the first blog post.


Problem

A simulated scale-up e-commerce operates three departments — Finance, Marketing, Operations — that consume the same raw data but compute divergent KPIs. Without a single source of truth, net_revenue reported by Finance differs from what Marketing infers, and only Operations factors partial returns into the calculation.

This project implements a semantic layer (Cube) as the arbiter: each critical metric has a single definition, and each department layers its own dimensions on top without touching the underlying formula.

Architecture

BigQuery (TheLook)
       │
       │  dlt  (Python, incremental on created_at, cut-off 2023-01-01)
       ▼
Snowflake  ─ RAW ─ ANALYTICS ─ ANALYTICS_DEV
       │
       │  dbt Core  (Kimball star schema, contracts enforced on staging)
       ▼
   dim_* / fct_*  ──►  Cube Cloud (semantic layer, single execution path)
                           │
                           ├─► Cube Explore   (analysts)
                           ├─► SQL API ──► Metabase (end-users, live, OCI VM)
                           └─► SQL API ──► KTX context layer (semantic YAML + wiki)
                                               │
                                               └─► MCP ──► Claude / AI agents

Orchestration: Dagster OSS on OCI VM (always-on)
IaC: Terraform — snowflakedb/snowflake + oracle/oci providers
CI/CD: GitHub Actions (lint + state-based dbt build + Terraform plan/apply)

Design principles

  • Code-first, versionable everywhere — every component (ingestion, orchestration, transformations, infrastructure, dashboards) is declared as code under Git, regardless of language (Python, SQL, HCL, YAML), not clicked in a UI.
  • Infrastructure-as-Code for everything provisionable — Snowflake warehouses and RBAC are declared with the same discipline as the OCI VM.
  • Single analytic engine — Snowflake only, with no DuckDB mirror and no Oracle Autonomous Database (ADB) fallback. See ADR-0006.
  • Always-on platform, demo-on-demand warehouse — Dagster + Metabase run permanently on OCI Free Tier at €0; Snowflake is terraform apply-ed for active periods and terraform destroy-ed afterwards.
  • Documented decisions — fifteen ADRs capture the structuring choices with their trade-offs, plus a Phase 1 closure report consolidating the infrastructure and governance work.

Stack at a glance

Function Tool Hosting Cost
Source BigQuery (TheLook) GCP €0
Ingestion dlt Dagster on OCI €0
Warehouse Snowflake AWS, demo-on-demand €0 during 30-day trial, capped at 10 credits/month if upgraded (resource monitor)
Transformation dbt Core Dagster on OCI + CI €0
Semantic layer Cube Cloud (dev) SaaS €0
Orchestration Dagster OSS OCI VM, always-on €0
BI (static) Evidence.dev Vercel €0
BI (live) Metabase OCI VM, always-on €0
Reverse proxy + TLS Caddy OCI VM €0
IaC Terraform (dual provider) Terraform Cloud €0
CI/CD GitHub Actions GitHub €0
Domain (optional) Any registrar — ~€10/year

Repository layout

.
├── README.md                                      ← this file
├── CLAUDE.md                                      ← context for AI assistants in this repo
├── LICENSE
├── pyproject.toml                                 ← root Python tooling (ruff, mypy, pytest) + uv workspace
├── uv.lock                                        ← reproducible Python deps
├── .gitignore
├── ingestion/                                     ← dlt pipelines (uv workspace member), Finance scope live
├── transformation/                                ← dbt project (uv workspace member), Finance staging + marts live
├── semantic/                                      ← Cube semantic layer (cubes + finance view), auto-deployed to Cube Cloud
├── semantic-context/                              ← KTX context layer for AI agents (semantic overlay + wiki + MCP)
├── docs/
│   ├── ADR/                                       ← Architecture Decision Records (ADR-0000 to ADR-0014)
│   ├── screenshots/                               ← metric-unicity demo captures (Cube Explore, Metabase)
│   └── infrastructure-and-governance-phase-report.md   ← Phase 1 closure report
├── infra/
│   ├── terraform/
│   │   ├── snowflake/                             ← databases, warehouses, roles, grants, users
│   │   └── oci/                                   ← VCN, VM Ampere A1, Bastion, quotas, budget
│   └── docker/                                    ← Caddy + Metabase compose stack deployed on the OCI VM
└── .github/
    └── workflows/                                 ← Python CI, Terraform CI, dbt docs deploy

Future Phase 2 additions (not yet present): orchestration/ (Dagster) and notebooks/.

Getting Started

The Terraform code in infra/terraform/snowflake/ and infra/terraform/oci/ is fully reproducible: applied to fresh Snowflake and OCI accounts, it produces the same infrastructure footprint every time. However, the path from "fresh accounts" to "first successful terraform apply" requires a one-time manual bootstrap that cannot be expressed in Terraform itself: account creation, MFA enrollment, OCI Pay-As-You-Go upgrade, region subscription, RSA key-pair generation, and Terraform Cloud workspace + sensitive variables setup. Total wall-clock time for the bootstrap: ~1 to 2 hours. Subsequent changes flow through git push → TFC apply with zero manual intervention.

Click to expand the full bootstrap procedure (~1-2h, one-time)

Local tools

Install before you begin.

Tool Why Install
git Source control OS package manager
Terraform CLI ≥ 1.6 Local fmt / validate; remote applies run in TFC hashicorp.com/install
openssl Generate RSA key pairs for Snowflake users OS package manager
ssh-keygen Generate the SSH key pair used by the OCI VM usually pre-installed
uv Python toolchain (used by Phase 2 ingestion) astral.sh/uv
pre-commit Run repo's pre-commit hooks locally before push pip install pre-commit
oci CLI (optional) Diagnostics; not required for terraform apply oracle.com/cli

Accounts to create

Account Tier required Why Approx. setup time
GitHub Free Source of truth, VCS-driven TFC runs 5 min
Terraform Cloud Free Remote state, sensitive variables, workspace runs 10 min
Snowflake 30-day Trial ($400 credits) Data warehouse 5 min
OCI Free Trial → upgraded to Pay-As-You-Go Always-on platform; PayG required to subscribe additional regions per ADR-0009 30 min
GCP Free Source dataset on BigQuery (Phase 2 ingestion). Setup deferred: no GCP credentials are required for Phase 1 (infrastructure and governance). Phase 2 will document service account creation, BigQuery API enablement, and the corresponding TFC variable. (Phase 2)

The PayG upgrade exposes a credit card. The €0 TCO commitment is preserved by the four-layer cost defense in infra/terraform/oci/quotas.tf and budget.tf (Compartment Quotas, Budget canary, MFA, IaC-only). See ADR-0009 for the rationale.

One-time manual console steps

These cannot be expressed in Terraform. Order matters: do them top to bottom.

  1. Snowflake — sign up at https://signup.snowflake.com. Note the account identifier (e.g. xy12345.eu-west-1.aws) and the bootstrap user credentials. We refer to this user as admin_bootstrap.
  2. Snowflake — enroll MFA on admin_bootstrap (Snowsight → bottom-left user menu → My profile → Multi-factor authentication → Enroll). See ADR-0008.
  3. Snowflake — generate admin_bootstrap RSA key pair locally and register the public key (the Snowflake provider authenticates via JWT only, not password):
    mkdir -p ~/.ssh/thelook && cd ~/.ssh/thelook
    openssl genrsa 2048 | openssl pkcs8 -topk8 -inform PEM -nocrypt \
      -out admin_bootstrap_rsa_key.p8
    openssl rsa -in admin_bootstrap_rsa_key.p8 -pubout \
      -out admin_bootstrap_rsa_key.pub
    In Snowsight, run as ACCOUNTADMIN (replace <KEY_CONTENT> with the public key without the -----BEGIN/END----- lines and without newlines):
    ALTER USER admin_bootstrap SET RSA_PUBLIC_KEY = '<KEY_CONTENT>';
  4. Snowflake — generate the 5 service user RSA key pairs (one each for USER_TERRAFORM, USER_DLT, USER_DBT, USER_DAGSTER, USER_CUBE):
    for u in terraform dlt dbt dagster cube; do
      openssl genrsa 2048 | openssl pkcs8 -topk8 -inform PEM -nocrypt \
        -out ${u}_rsa_key.p8
      openssl rsa -in ${u}_rsa_key.p8 -pubout -out ${u}_rsa_key.pub
    done
    These 5 public keys feed Terraform Cloud variables (next section). The private keys stay on disk for the runtime services that need them (Phase 2). Never commit any of these to git.
  5. OCI — sign up at https://www.oracle.com/cloud/free. Complete identity verification. Note the tenancy OCID and the home region (immutable).
  6. OCI — upgrade the tenancy to Pay-As-You-Go (Console → Billing & Cost Management → Subscriptions → Upgrade). Required to subscribe additional regions.
  7. OCI — subscribe to a region with reliable A1 capacity, typically eu-frankfurt-1 (Console → top-right region menu → Manage Regions → Subscribe). The home region often has saturated A1 capacity; the subscribed region is the one used by Terraform.
  8. OCI — enroll MFA on the human admin user (Console → Identity & Security → Domains → Default → Users → your user → Authentication → Enable MFA).
  9. OCI — create a dedicated compartment named thelook-prod for the project resources (Console → Identity & Security → Compartments → Create). Note its OCID.
  10. OCI — generate the API key pair locally and register the public key in OCI Console (Console → User profile → API Keys → Add API Key):
    mkdir -p ~/.oci && chmod 700 ~/.oci
    openssl genrsa -out ~/.oci/thelook_api_key.pem 2048
    chmod 600 ~/.oci/thelook_api_key.pem
    openssl rsa -pubout -in ~/.oci/thelook_api_key.pem \
      -out ~/.oci/thelook_api_key_public.pem
    Upload thelook_api_key_public.pem content to OCI Console; note the fingerprint shown after upload.
  11. OCI — generate the SSH key pair for the VM (used by the Bastion sessions to authenticate, and as the ubuntu user's authorized key via cloud-init):
    ssh-keygen -t rsa -b 4096 -C "thelook-vm" -f ~/.ssh/thelook/oci_vm_rsa
  12. Terraform Cloud — sign up at https://app.terraform.io. Create an organization (e.g. thelook-project).
  13. Terraform Cloud — connect GitHub (Settings → Providers → VCS Providers → Add → GitHub.com). Required for VCS-driven runs.
  14. Terraform Cloud — create two workspaces, both VCS-driven, pointing to your fork of this repo:
    • thelook-snowflake with working directory infra/terraform/snowflake
    • thelook-oci with working directory infra/terraform/oci

Configure Terraform Cloud workspace variables

Set these on the corresponding workspace (TFC → Workspace → Variables → Add variable). Mark sensitive variables as such — they will be encrypted at rest by HashiCorp and never re-displayed in plain text.

thelook-snowflake workspace:

Variable Initial value Sensitive
snowflake_organization_name your Snowflake org (visible in the account URL) No
snowflake_account_name your Snowflake account No
snowflake_user admin_bootstrap No
snowflake_role ACCOUNTADMIN No
snowflake_private_key content of admin_bootstrap_rsa_key.p8 (PEM, full file) Yes
user_terraform_public_key content of terraform_rsa_key.pub (no headers, no newlines) Yes
user_dlt_public_key content of dlt_rsa_key.pub (no headers, no newlines) Yes
user_dbt_public_key content of dbt_rsa_key.pub (no headers, no newlines) Yes
user_dagster_public_key content of dagster_rsa_key.pub (no headers, no newlines) Yes
user_cube_public_key content of cube_rsa_key.pub (no headers, no newlines) Yes

To strip a public key for the *_public_key variables:

awk '!/-----/ {printf "%s", $0}' <key>.pub

thelook-oci workspace:

Variable Value Sensitive
tenancy_ocid from OCI Console No
user_ocid from OCI Console (your user, not a service user) No
fingerprint from API key registration step No
private_key content of ~/.oci/thelook_api_key.pem (PEM, full file) Yes
region eu-frankfurt-1 (or your subscribed region) No
home_region eu-paris-1 (or your tenancy's home region; required for tenancy-level operations like quotas and budgets) No
compartment_ocid OCID of the thelook-prod compartment No
ssh_public_key content of ~/.ssh/thelook/oci_vm_rsa.pub No
cost_alert_email recipient(s) for budget alerts (comma-separated, no spaces) No

Reproduce the infrastructure

# 1. Clone the repo
git clone https://github.com/<you>/thelook-project.git
cd thelook-project

# 2. (Optional) Install pre-commit hooks for local validation
pre-commit install

# 3. Snowflake — first apply via TFC (as admin_bootstrap)
# In TFC UI: thelook-snowflake workspace → Actions → Start new run.
# Expected plan: ~25 resources to add (3 databases, 6 schemas, 3 warehouses,
# 5 RBAC roles, 5 service users, 1 resource monitor, ownership transfers).
# Confirm & Apply. Duration: ~3-5 min.

# 4. Snowflake — rotate from admin_bootstrap to USER_TERRAFORM (one-time)
# In TFC UI: thelook-snowflake workspace → Variables → Edit:
#   snowflake_user        = USER_TERRAFORM
#   snowflake_role        = ROLE_TERRAFORM
#   snowflake_private_key = content of terraform_rsa_key.p8
# Save, then trigger a new run.
# Expected plan: "0 to add, 0 to change, 0 to destroy" — confirms the
# rotation is clean and the new identity has the same effective rights.

# 5. OCI — apply via TFC
# In TFC UI: thelook-oci workspace → Actions → Start new run.
# Expected plan: ~14 resources to add (VCN, subnet, IGW, route table,
# security list, NSG, bastion, VM, Reserved IP, quota, budget, 5 alert
# rules). Confirm & Apply. Duration: ~5-10 min.

# 6. OCI VM — wait for cloud-init (~8-12 min after VM "Running" state)
# Cloud-init runs apt update, package upgrade, Docker install, fail2ban,
# unattended-upgrades, deploy user creation, /opt/thelook workspace setup.
# Verify completion via OCI Console → Compute → "thelook-vm" → Metrics
# (CPU usage drops below ~5% when cloud-init finishes).

# 7. OCI VM — verify SSH access via Bastion port-forwarding session
# OCI Console → Identity & Security → Bastion → "thelook-bastion"
# → Create session:
#   Type: SSH port forwarding session
#   IP: <VM private IPv4 from the OCI Console>
#   Port: 22
#   SSH key: ~/.ssh/thelook/oci_vm_rsa.pub
# Then locally, in two terminals:
ssh -i ~/.ssh/thelook/oci_vm_rsa -o ServerAliveInterval=60 \
    -N -L 22000:<vm-private-ip>:22 -p 22 \
    <session-ocid>@host.bastion.<region>.oci.oraclecloud.com
# (other terminal)
ssh -i ~/.ssh/thelook/oci_vm_rsa -p 22000 ubuntu@localhost

What is NOT reproducible from git clone + terraform apply alone

Honest list of what cannot be eliminated:

  • Account creation for Snowflake, OCI, Terraform Cloud, GitHub.
  • MFA enrollment on the Snowflake bootstrap user and the OCI human admin user (provider self-service flows, no API).
  • OCI tenancy upgrade to Pay-As-You-Go (credit card required, console flow with 3DS).
  • OCI region subscription post-PayG upgrade (console action).
  • Local RSA key-pair generation (5 Snowflake service users + 1 OCI API key + 1 SSH key for the VM). The private keys must never be committed.
  • The first Snowflake apply must run as admin_bootstrap (ACCOUNTADMIN); the rotation to USER_TERRAFORM is a one-time TFC variable update, documented above and verified by a clean 0/0/0 plan.
  • Terraform Cloud workspace creation and sensitive variables setup (chicken-and-egg with Terraform-managing-Terraform-Cloud).
  • OCI Bastion sessions for SSH access (interactive, max 3h TTL, must be re-created when the VM private IP changes).
  • Pre-commit hook installation locally (pre-commit install on first clone).

After this one-time bootstrap (~1-2h), every subsequent change to infrastructure or RBAC flows through:

  1. Edit code, commit, push.
  2. TFC auto-triggers a plan.
  3. Review plan, click Confirm & Apply.
  4. Done.

Demo-on-demand policy

Snowflake is not running 24/7. Between active periods the warehouse is destroyed to keep cost at strictly €0. When a walkthrough is needed, I run terraform apply on the Snowflake module (~5 min) and re-materialize the assets from the Dagster UI (~15 min).

Metabase dashboards keep their last-successful-query results cached, so the live surface stays visually coherent when Snowflake is down. Evidence.dev dashboards are static and carry a "Data snapshot: [date]" banner.

FinOps

  • Warehouses auto-suspend at 60s.
  • Ingestion is filtered to data from 2023-01-01 onwards at the source (dlt-level cut-off).
  • CI rebuilds only modified dbt models via dbt build --select state:modified+ --defer, against a dedicated ANALYTICS_DEV schema.
  • The always-on platform (Dagster + Metabase + Postgres × 2 + Caddy) runs on OCI Free Tier at €0 in perpetuity.
  • A snowflake_usage dbt model exposes warehouse credit consumption for monitoring.

GDPR and Governance

  • PII columns are pseudonymized via a deterministic SHA-256 + salt macro at the staging layer.
  • A meta.data_classification tag (public / internal / pii / sensitive_pii) is exposed in dbt docs and drives the Snowflake RBAC.

RBAC

  • Five Snowflake roles (ROLE_INGESTION, ROLE_TRANSFORM, ROLE_ANALYST_FINANCE, ROLE_ANALYST_MARKETING, ROLE_ANALYST_OPS) are declared in Terraform with least-privilege grants.

Decision records

ADR Decision
ADR-0000 Snowflake on AWS as the data warehouse (foundation)
ADR-0001 dlt over Airbyte for ingestion tooling
ADR-0002 Dagster OSS over Airflow for orchestration
ADR-0003 Evidence.dev + Metabase over Power BI / Tableau for the BI layer
ADR-0004 Terraform over SQL scripts and Snowsight UI for Snowflake IaC
ADR-0005 OCI Free Tier as the always-on platform compute
ADR-0006 Snowflake as the single analytic engine
ADR-0007 Cube Cloud as the semantic layer
ADR-0008 admin_bootstrap retained as break-glass with compensating controls
ADR-0009 OCI Pay-As-You-Go with €0 cost guardrails
ADR-0010 Simulated source mapping for portfolio narrative (TheLook → 6 simulated French upstream systems)
ADR-0011 Cube modeling conventions for the Finance domain (Strict Kimball reflection, single grain, view-only)
ADR-0012 Replace Evidence with Metabase OSS, refine Code-First scope for self-service BI
ADR-0013 KTX context layer wrapping Cube for AI agent consumption (MCP)
ADR-0014 Keep the semantic layer in Cube, not in Snowflake Semantic Views / Cortex Analyst (placement decision extending ADR-0007)

Phase reports

Phase Report
Phase 1 — Infrastructure & Governance Phase 1 closure report

License

MIT — see LICENSE.

About

E-commerce scale-up analytics data platform with end-to-end data pipelines and a semantic layer to unify KPIs computation for different data consumming departments

Resources

Stars

1 star

Watchers

0 watching

Forks

Releases

Packages

Contributors

Languages