Pipeline to Dashboardnihalsulaiman.com

Week 7 · Run it in production › Day 46

Day 46. The low-cost open-source stack for medium companies

⏱ 4h · learn 90m · lab 115m · ship 20m · review 15m🏷 retail, QSR, hospitality, marketingBefore: Day 45

By the end of today you can

  • Design a reference architecture from open-source parts that a 1–3 person data team can run
  • Size it and estimate a monthly cost, with a Microsoft-based comparison
  • Plan security, backups, upgrades and support for it
  • Write a one-page business case a managing director can approve

1. Why this matters

This is your stated mission: make professional data platforms affordable for medium-sized chains (say 20–500 locations, data arriving daily, a small team). Today you design that platform, cost it, and write the case for it.

2. Reference architecture

 Sources                     Platform (1–2 VMs or a small Kubernetes cluster)                    Users
 ───────                     ──────────────────────────────────────────────────                  ─────
 Email (IMAP/Graph) ┐
 SFTP / shared drive├─► dlt + Python landers ─► MinIO / Blob (raw, Parquet) ─┐
 POS / PMS APIs     ┘          (Dagster assets, sensors, partitions)          │
                                                                             ▼
                                         Postgres (warehouse; marts)  ◄── dbt Core (models, tests)
                                         DuckDB (heavy scans on Parquet, dev, CI)
                                                       │
                        ┌──────────────────────────────┼──────────────────────────┐
                        ▼                              ▼                          ▼
                 Metabase / Superset          Daily email/PDF reports      Ollama (local LLM): invoice
                 (dashboards, subscriptions)  (Python + Jinja)             extraction, review tagging
 Ops: Dagster UI, Grafana health board, Postgres backups → object storage, GitHub Actions CI

Every component is open source and swappable: Postgres ↔ Azure SQL, Metabase ↔ Power BI, MinIO ↔ Azure Blob/S3. That's the upgrade path when the company grows or joins a Microsoft group.

3. Sizing

For a 150-restaurant QSR chain: ~600k order lines/day ≈ 220M/year ≈ 26 GB raw/year at 120 B/row, a few GB in Parquet. Typical first build:

Component Size
Platform VM 4–8 vCPU, 16–32 GB RAM, 200–500 GB SSD
Postgres Same VM to start, or a managed Postgres instance
Object storage 100s of GB, cents per GB-month
LLM A small model on CPU for low volume, or a modest GPU box/hosted API for bulk

4. Cost model

Maths and principle

Principle: total monthly cost = infrastructure + licences + people time; compare stacks at the same service level.

Equation: \(C = C_{\text{infra}} + \sum_{\text{tools}} p_t u_t + h_{\text{ops}} \cdot w\). For each stack compute \(C\) at today's users and at 3× growth; also compute cost per location per month \(= C / L\), the number a chain's finance team relates to.

Analogy: comparing engines by fuel per km, not by tank size. Where it breaks: migration and training costs are one-off; amortise them over 24–36 months and add them in.

Try it: open stack: VM $120 + storage $10 + 8 h/month ops at $60 = $610/month for 150 locations. Per location? (≈ $4.07/location/month.) Compare with your own Microsoft estimate using current list prices.

Make a table for open stack vs Microsoft stack (Azure SQL + ADF or Dagster + Power BI Pro × users) vs managed modern stack (Fivetran + Snowflake/BigQuery + a BI tool) at 20, 150 and 500 locations. Look up current prices; write the date you looked them up.

5. Running it responsibly

  • Security: SSO in front of Metabase/Dagster (an identity-aware proxy such as Authelia or oauth2-proxy, or a cloud provider's), least-privilege DB roles, secrets in a vault or the platform's secret store, disk encryption, firewall to known IPs.
  • Backups: nightly pg_dump (or WAL archiving) to object storage, raw zone is already immutable; test a restore monthly.
  • Upgrades: pin versions; upgrade monthly in a staging copy first; CI runs against the new versions.
  • Support: a runbook and an on-call rota (even if it's one person and a backup), plus a paid support option for the most critical component if the business wants it.

The 5-year lens

Medium companies don't buy architectures; they buy outcomes and risk reduction: "the area managers get yesterday's numbers by 08:00, we stop paying for X, and if the data person leaves, the next one can run it from the runbook." Lead the business case with the outcome, then cost per location, then risks and mitigations. And be honest about when not to self-host: if there's no one to own it, a managed stack at 3× the cost is the cheaper option.

Lab

  1. Draw the reference architecture for the capstone (Excalidraw or diagrams-as-code like Mermaid/D2) and commit it.
  2. Write docs/cost-model.xlsx or docs/cost-model.md: three stacks × three sizes, with sources and the date of each price.
  3. Write docs/security-and-ops.md: identities, secrets, backups and restore drill, upgrade policy, support.
  4. Write a one-page business case for a fictional 150-restaurant QSR chain: problem, outcome, options, recommendation, cost per location, risks, 90-day plan.
  5. Ask Claude Code to play the chain's sceptical CFO and challenge the case. Revise.

Ship

docs: reference architecture, three-stack cost model, ops plan, business case.

Check yourself

1. Which number does a chain's finance team usually relate to best?

2. When is a managed stack at higher cost the better choice?

3. Why keep the raw zone in Parquet on object storage in the open stack?

My notes