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.
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 | 📡 |
| dbt docs (lineage + descriptions) | mukunde.github.io/thelook-project | 📡 |
| Cube Cloud semantic layer (Finance) | https://thelook.cubecloud.dev/ (auth-protected) |
📡 |
| KTX context layer (AI agents via MCP) | local MCP server (semantic-context/) | 📡 |
| AI agent demo (Cube semantic layer via KTX/MCP) | Loom walk-through | 📡 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.
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.
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.
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.
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)
- 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 andterraform 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.
| 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 |
.
├── 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/.
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)
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 |
| 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.
These cannot be expressed in Terraform. Order matters: do them top to bottom.
- 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 asadmin_bootstrap. - Snowflake — enroll MFA on
admin_bootstrap(Snowsight → bottom-left user menu → My profile → Multi-factor authentication → Enroll). See ADR-0008. - Snowflake — generate
admin_bootstrapRSA key pair locally and register the public key (the Snowflake provider authenticates via JWT only, not password):In Snowsight, run as ACCOUNTADMIN (replacemkdir -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
<KEY_CONTENT>with the public key without the-----BEGIN/END-----lines and without newlines):ALTER USER admin_bootstrap SET RSA_PUBLIC_KEY = '<KEY_CONTENT>';
- Snowflake — generate the 5 service user RSA key pairs (one each for USER_TERRAFORM, USER_DLT, USER_DBT, USER_DAGSTER, USER_CUBE):
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.
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
- OCI — sign up at https://www.oracle.com/cloud/free. Complete identity verification. Note the tenancy OCID and the home region (immutable).
- OCI — upgrade the tenancy to Pay-As-You-Go (Console → Billing & Cost Management → Subscriptions → Upgrade). Required to subscribe additional regions.
- 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. - OCI — enroll MFA on the human admin user (Console → Identity & Security → Domains → Default → Users → your user → Authentication → Enable MFA).
- OCI — create a dedicated compartment named
thelook-prodfor the project resources (Console → Identity & Security → Compartments → Create). Note its OCID. - OCI — generate the API key pair locally and register the public key in OCI Console (Console → User profile → API Keys → Add API Key):
Upload
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
thelook_api_key_public.pemcontent to OCI Console; note the fingerprint shown after upload. - OCI — generate the SSH key pair for the VM (used by the Bastion sessions to authenticate, and as the
ubuntuuser's authorized key via cloud-init):ssh-keygen -t rsa -b 4096 -C "thelook-vm" -f ~/.ssh/thelook/oci_vm_rsa
- Terraform Cloud — sign up at https://app.terraform.io. Create an organization (e.g.
thelook-project). - Terraform Cloud — connect GitHub (Settings → Providers → VCS Providers → Add → GitHub.com). Required for VCS-driven runs.
- Terraform Cloud — create two workspaces, both VCS-driven, pointing to your fork of this repo:
thelook-snowflakewith working directoryinfra/terraform/snowflakethelook-ociwith working directoryinfra/terraform/oci
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>.pubthelook-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 |
# 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@localhostHonest 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 toUSER_TERRAFORMis a one-time TFC variable update, documented above and verified by a clean0/0/0plan. - 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 installon first clone).
After this one-time bootstrap (~1-2h), every subsequent change to infrastructure or RBAC flows through:
- Edit code, commit, push.
- TFC auto-triggers a plan.
- Review plan, click Confirm & Apply.
- Done.
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.
- 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 dedicatedANALYTICS_DEVschema. - The always-on platform (Dagster + Metabase + Postgres × 2 + Caddy) runs on OCI Free Tier at €0 in perpetuity.
- A
snowflake_usagedbt model exposes warehouse credit consumption for monitoring.
- PII columns are pseudonymized via a deterministic SHA-256 + salt macro at the staging layer.
- A
meta.data_classificationtag (public/internal/pii/sensitive_pii) is exposed in dbt docs and drives the Snowflake 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.
| 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 | Report |
|---|---|
| Phase 1 — Infrastructure & Governance | Phase 1 closure report |
MIT — see LICENSE.



