WF-6.7 — Warehouse Engine Decision

Type: Vendor / architecture decision Value Driver: VD6 Financial Operations (data infrastructure) Version: 1.0 Date: 2026-04-29 Decision owner: Roger Thompson Status: Approved — DuckDB + MotherDuck on S3 Replaces: SQLite reference in Database-Schema-v1.md (Phase 2) and WF-6.4 §4 Phase 2

Summary

Decision: Adopt DuckDB + MotherDuck on AWS S3 as the Cedarfell warehouse engine. Reject Redshift (provisioned and serverless) and reject SQLite (the original Schema v1.2 reference).

One-line rationale: DuckDB+MotherDuck is right-sized for our scale (1 GB → 10 GB over 5 years), 5–10× cheaper at every scale we project, replicable to new CSA owners in under an hour without AWS skills, and lock-in-free because the data layer is Parquet on S3 — the same lake any future engine could read.

Three-year TCO advantage over Redshift Serverless: approximately $15K – $30K at 1 CSA; $50K – $130K at projected 5-CSA scale.

Background

Trigger. Schema v1.2 originally specified SQLite for Phase 2. WF-6.4 inherited that. On review, Roger flagged that SQLite is unsuitable (single-writer, no analytics-tuning, no remote access, no portfolio replication story), and asked the team to evaluate DuckDB + MotherDuck vs. AWS S3 + Redshift as the replacement.

Use case. Power the Monthly Driver Scorecard, then expand to the full Cedarfell warehouse covering route profitability, fleet cost, payroll allocation, and FedEx settlement reconciliation. Eventually replicate to a portfolio of CSAs as the operating playbook scales.

Data scale projection (driven by source-system row counts in Database-Schema-v1.md v1.3):

This is laptop-scale OLAP at every horizon. The architecture choice is not gated by data volume — it’s gated by operational cost, replicability, and exit-quality.

Operator profile. Sole owner-operator (Roger) with a corporate management background. One bookkeeper at Tensor (Jana), one offshore bookkeeper (Sheryl), two BCs running ops, one HR/Fleet coordinator. No internal DevOps, SRE, or cloud platform engineer — and the operating model isn’t going to support hiring one.

Strategic constraints.

• KPI Taxonomy v1.0 already commits to dbt as the transformation framework (dbt/models/kpi/ referenced in §Governance).
• Schema v1.2 commits to a three-phase build (spreadsheet → database → dashboard).
• STR.EXIT KPIs include STR.EXIT.04 Playbook replicability score and STR.EXIT.06 Data warehouse maturity. The engine choice is an exit-value input.
• Replicable CSA model is the stated expansion strategy. Each new CSA owner must be able to clone the data infrastructure cheaply and operate it without specialized skills.

Options evaluated

Option A — DuckDB + MotherDuck on AWS S3 (recommended)

Architecture: S3 holds raw source files (XLS, CSV, PDF) and cleansed Parquet files. DuckDB queries Parquet directly via the httpfs extension. MotherDuck adds managed cloud DuckDB compute, sharing, and dashboard back-end. Roger develops locally on his laptop; cleaned silver/gold tables sync to MotherDuck for BC/Jana/Roger access.

Stack:

• AWS S3 — cedarfell-raw/ (immutable source files), cedarfell-staging/ (Parquet)
• DuckDB (OSS, free) — local development, embedded analytics engine
• MotherDuck — managed DuckDB compute and sharing (cloud component)
• dbt-duckdb — transformations from bronze to silver to gold
• Python parsers — one per source system, all batch
• Metabase or similar (Phase 3) — BI/dashboards

Option B — AWS S3 + Redshift Serverless

Architecture: S3 raw + Glue Catalog for metadata + Redshift Serverless for compute + Spectrum for federated queries on S3.

Stack:

• AWS S3, Glue, IAM, Secrets Manager
• Redshift Serverless (RPU-priced)
• dbt-redshift
• Same Python parsers
• Metabase or similar

Option C — AWS S3 + Redshift Provisioned (RA3 nodes)

Architecture: Same as Option B but with always-on provisioned cluster instead of serverless. More predictable cost at sustained workloads but requires sizing the cluster.

Option D — SQLite (reject as baseline)

Architecture: Single-file local database. Schema v1.2’s original reference. Rejected: no concurrent writes, no remote access, no analytics tuning (row store, not columnar), no portfolio replication story, dbt-sqlite adapter is community-maintained and limited.

Cost analysis (Total Cost of Ownership)

All figures USD/month unless noted. Conservative estimates — mid-range vendor pricing as of 2026-04, normal usage patterns.

TCO at 1 CSA (today)

TCO at 5 CSAs (portfolio, ~2030)

TCO at 10 CSAs (~2031)

TCO at 25 CSAs (MBO 2035 exit scenario)

Cumulative 2026 → 2035 (delta retained as margin)

Mid-case projection across the 10-year horizon, weighted by likely CSA-count trajectory:

• Option A cumulative: ~$30K – $80K
• Option B cumulative: ~$350K – $900K
• Margin retained by going Option A: ~$300K – $800K over ten years

That is not a rounding error and shows up directly in net margin %, the Apex 1 KPI.

Risk assessment

Aggregate risk: Option A presents materially lower aggregate risk than Options B/C across every dimension except compliance certification depth — and that gap is recoverable in weeks if it ever becomes a binding constraint.

Performance considerations

Query performance. At Cedarfell’s scale, both engines return results in milliseconds. DuckDB benchmarks competitively with Redshift on TPC-H at single-node scale and outperforms Redshift on many small-data queries because there’s no network hop.

Ingestion. Both engines ingest Parquet from S3 at IO-bound speed. DuckDB has the edge for ad-hoc XLS/CSV ingestion (one-line read_csv_auto), Redshift requires Glue or COPY commands.

Concurrent users. MotherDuck Pro tier supports concurrent reads from ~20 analysts comfortably. Redshift can handle hundreds. Cedarfell needs neither — even at 25-CSA scale, daylight concurrent users likely stay under 30.

Tooling fit. dbt-duckdb is mature (1.0+, broad community). dbt-redshift is canonical. Metabase, Tableau, Power BI, Sigma, and the BI ecosystem support both.

Strengths and concerns per option

Option A — DuckDB + MotherDuck on S3

Strengths

• Right-sized for the data scale Cedarfell will see in this decade
• Cost compounds in our favor as CSAs come online
• Replicable per-CSA in under an hour without AWS skills — directly serves STR.EXIT.04 (playbook replicability) and the multi-market expansion thesis
• Local-first development; no cloud cost during dev cycles
• Open-format storage (Parquet on S3) preserves complete optionality on engine swap
• Modern columnar engine purpose-built for analytics workloads we run

Concerns

• MotherDuck is a younger company; vendor maturity and roadmap less proven than AWS
• Smaller talent pool of operators with prior DuckDB experience (mitigated by SQL similarity to standard dialects)
• Compliance certification depth lighter than AWS (acceptable today; revisit if a contract requires it)

Option B — Redshift Serverless

Strengths

• AWS shared-responsibility model brings deepest compliance certification lineage (SOC 2 Type II, HIPAA-eligible, FedRAMP-eligible)
• Automatic scaling up and down with load
• Mature ecosystem with thousands of operators
• Native federation to other AWS services

Concerns

• Minimum spend tied to RPU-hours; even idle dashboards accrue cost
• Operational complexity (IAM, VPC, security groups, monitoring, snapshots) requires DevOps capacity Cedarfell does not have
• Cost compounds against us at portfolio scale — every new CSA is incremental Redshift spend that DuckDB does for free
• Time-to-first-parser is 1–2 weeks vs. 1 day on DuckDB
• Engine over-built for our scale by 2–3 orders of magnitude

Option C — Redshift Provisioned (RA3)

Strengths

• More predictable pricing than serverless for sustained workloads
• Same compliance and ecosystem strengths as B

Concerns

• Always-on cost regardless of usage
• Cluster sizing decisions add operational overhead
• Same DevOps burden as Option B

Option D — SQLite

Strengths

• Free, embedded, simplest of all
• Familiar to most developers

Concerns

• Single-writer concurrency model (real blocker for parallel parser jobs)
• Row-store design ill-suited to OLAP scans across millions of rows
• No remote/multi-user access (BCs and Jana would need their own copies)
• No portfolio replication path; one DB per CSA without sharing primitive
• dbt-sqlite community-maintained and feature-light
• Designed for embedded application data, not analytics warehousing

Recommendation

Proceed with Option A — DuckDB + MotherDuck on AWS S3.

This decision optimizes for what actually matters at Cedarfell’s scale and trajectory: cost discipline, operational simplicity that one owner-operator can maintain, replicability to new CSA owners without specialized skills, and engine choice that compounds in our favor as the portfolio grows. Lock-in risk is essentially zero because the storage layer is Parquet on S3 — the same lake any future engine reads.

Reject Options B and C (Redshift) on cost and operational grounds. The compliance edge that Redshift carries is real but not currently binding, and is recoverable in weeks if a contract ever requires it.

Reject Option D (SQLite) on technical grounds. Single-writer concurrency, row-store performance, and lack of remote access make it unsuitable for the workload.

Negotiation points (MotherDuck specifically)

If signed up via MotherDuck for the cloud component, the leverage points are:

1. Annual prepay vs. month-to-month — MotherDuck offers 10–15% discount on annual commits. Don’t commit annually until 2 monthly cycles validate the architecture.
2. Free tier headroom — Free tier covers ~10 GB and limited compute; Cedarfell stays inside it through Phase 2 of WF-6.4. Negotiate when usage trends past free tier.
3. Multi-CSA discount — At 5+ CSAs, ask for a portfolio multi-database discount or volume tier.
4. SOC 2 attestation report — Request the latest report (it’s available under NDA) before any future buyer or contract review.
5. Data export guarantees — Make sure the contract terms include explicit data export at no charge in any common format (Parquet specifically). MotherDuck’s terms already cover this; verify in writing.
6. Service credits SLA — Pro tier ships with stated uptime SLA; confirm credit terms for any breach.
7. Acquisition / shutdown clause — Include a clause that data export remains free in the event of acquisition or shutdown for at least 12 months post-event.

Implementation impact on existing artifacts

The following artifacts must be updated to reflect this decision:

Decision and sign-off

Cross-references

• WF-6.4 Scorecard Data Acquisition Plan — /VD6 - Financial Operations/Workflows/
• Database-Schema-v1.md (v1.3) — /VD6 - Financial Operations/Data/
• Scorecard-Data-Sources.xlsx — /VD6 - Financial Operations/Data/
• KPI Taxonomy v1.0 — /Strategic/
• Decisions-Log.md — entry dated 2026-04-29 for this decision

Change log

Appendix A — Reference architecture (target state)

                ┌─────────────────────────────────────────────────┐
                │           SOURCE SYSTEMS (external)             │
                │  FedEx MBA  ·  GroundCloud  ·  RYDE  ·  Gusto   │
                │  Tensor  ·  Snyder/Pacific Pride  ·  Chase CC   │
                └────────────────────┬────────────────────────────┘
                                     │ manual or API pull
                                     ▼
                ┌─────────────────────────────────────────────────┐
                │   AWS S3 — cedarfell-raw/                       │
                │   immutable source files, partitioned           │
                │   by /<source>/<YYYY-MM>/ ...                   │
                │   ── source of truth for the entire warehouse ──│
                └────────────────────┬────────────────────────────┘
                                     │
                                     ▼
                ┌─────────────────────────────────────────────────┐
                │   PYTHON PARSERS  (one per source)              │
                │   read raw → emit Parquet                       │
                │   (run on Roger's laptop or scheduled cron)     │
                └────────────────────┬────────────────────────────┘
                                     │ writes
                                     ▼
                ┌─────────────────────────────────────────────────┐
                │   AWS S3 — cedarfell-staging/                   │
                │   Parquet, columnar, ready for query            │
                └────────────────────┬────────────────────────────┘
                                     │ httpfs
                                     ▼
                ┌─────────────────────────────────────────────────┐
                │   DUCKDB / MOTHERDUCK                           │
                │   ┌────────────┬────────────┬────────────────┐  │
                │   │  Bronze    │   Silver   │      Gold      │  │
                │   │  (source)  │ (cleansed) │ (reporting)    │  │
                │   │            │            │                │  │
                │   │  wsw_raw   │ wsw_daily_ │ monthly_driver │  │
                │   │  ryde_raw  │ detail     │ _scorecard     │  │
                │   │  vedr_raw  │ ryde_      │ route_         │  │
                │   │  ...       │ scores     │ profitability  │  │
                │   │            │ ...        │ ...            │  │
                │   └────────────┴────────────┴────────────────┘  │
                │           transformations via dbt-duckdb        │
                └────────────────────┬────────────────────────────┘
                                     │
                                     ▼
                ┌─────────────────────────────────────────────────┐
                │           CONSUMERS                             │
                │  Monthly Driver Scorecard workbook              │
                │  Metabase dashboards                            │
                │  Roger's quarterly review notebooks             │
                │  CSV exports for ad-hoc analysis                │
                └─────────────────────────────────────────────────┘

Per-CSA replication pattern:

Each new CSA gets:

• Its own S3 prefix: cedarfell-raw/<csa_id>/..., cedarfell-staging/<csa_id>/...
• Its own MotherDuck database (or shared database with csa_id filter — choose at portfolio onboarding)
• Same parser scripts, parameterized by csa_id and S3 prefix
• Same dbt project, parameterized by environment

A new owner buying into the playbook installs Python, clones the parser repo, runs dbt deps && dbt build, points at their MotherDuck workspace. Total setup time: under an hour.

Appendix B — Vendor financial stability snapshot (informational)

Concentration risk note. S3 is used in all options. AWS dependency is unavoidable for the lake layer. The MotherDuck choice doesn’t compound AWS dependency — it sits on S3, not next to it.