dbt tests
0/71
Staging, marts, and dashboard views — all passing on real BigQuery
Loading...
Project Context
Role
Sole Designer, Builder & Operator
Team
Solo
Duration
Started July 2026. Phases P0–P3 completed ahead of schedule; two articles (due September) remain
Tools
BigQueryTerraformdbt CoreCloud Run
A personal, Terraform-managed BigQuery data platform that turns my own operating history — 188 commits across 5 personal repositories, 20 published articles, and locally-aggregated lesson and session records (sources limited to personal repositories and personal logs by an explicit allowlist) — into queryable evidence, plus a Cloud Run RAG API for asking questions over the published corpus. Built and run entirely on GCP's free tier by design: 71 dbt tests passing, 144 automated tests passing, and 5/5 known-question retrieval accuracy. (Commit/article counts as of 2026-08-13.)
Challenge
My personal operating history — 188 commits, 20 published articles, and locally-kept lesson and session records — proved capability, but only existed as scattered local files, not something a stranger could verify. A technical audience discounts claims it can't check: 'trust me' loses to 'here's the query.' The real requirement wasn't a bigger app — it was a way to turn my own operating history into something a stranger could run, query, and check for themselves, without spending money to do it.
Solution
A Terraform-managed BigQuery warehouse in three layers — raw (a faithful copy of the source of truth: local git history and Markdown files), staging, and marts (both built and tested with dbt Core) — feeding a private Looker Studio evidence dashboard and a token-gated Cloud Run RAG API (Bearer auth; endpoint not published). The API chunks and embeds the published-article corpus with Gemini, then answers questions with BigQuery's brute-force VECTOR_SEARCH behind hard cost ceilings (per-query byte limits, a single Cloud Run instance, a daily request cap). Every free-tier constraint — a forbidden-operations list, a ¥1,000 budget alert, pinned regions — was a design input from the start, not an afterthought.
Challenge & Solution
Challenge
My personal operating history — 188 commits, 20 published articles, and locally-kept lesson and session records — proved capability, but only existed as scattered local files, not something a stranger could verify. A technical audience discounts claims it can't check: 'trust me' loses to 'here's the query.' The real requirement wasn't a bigger app — it was a way to turn my own operating history into something a stranger could run, query, and check for themselves, without spending money to do it.
Solution
A Terraform-managed BigQuery warehouse in three layers — raw (a faithful copy of the source of truth: local git history and Markdown files), staging, and marts (both built and tested with dbt Core) — feeding a private Looker Studio evidence dashboard and a token-gated Cloud Run RAG API (Bearer auth; endpoint not published). The API chunks and embeds the published-article corpus with Gemini, then answers questions with BigQuery's brute-force VECTOR_SEARCH behind hard cost ceilings (per-query byte limits, a single Cloud Run instance, a daily request cap). Every free-tier constraint — a forbidden-operations list, a ¥1,000 budget alert, pinned regions — was a design input from the start, not an afterthought.
Design Philosophy
Free-tier constraints are the spec, not a limitation to work around. And honesty beats a better-looking demo: the corpus is too small for BigQuery's ANN vector index (it needs 5,000+ rows and 10MB minimum), so the README says so and ships brute-force search instead of hiding the gap. Catching a wrong assumption before writing code is cheaper than catching it after: a pre-build review flagged that the free sandbox's 60-day table expiration and DML restriction would silently corrupt any import-based migration plan — so the design changed to 'rebuild from source with Terraform,' not 'import and hope.'
Key Features
Zero-cost by design, not by luck
A documented forbidden-operations list (streaming inserts, GKE, always-on VMs), a ¥1,000 budget alert, and pinned regions (US multi-region for BigQuery, us-central1 for GCS/Cloud Run) keep every component inside GCP's Always Free tier — verified line-by-line in the README's cost table.
A sandbox trap caught before it cost a rebuild
Pre-implementation review flagged that the billing-free sandbox's 60-day table expiration, combined with its DML restriction, would corrupt a naive 'import the sandbox data' migration. The fix: treat local git/Markdown as the true source of record and have Terraform recreate everything from scratch — a design decision, not a patch.
A documented list of what wasn't built
Airflow/Dagster, GKE, Kafka, a dedicated vector database, and Dataform were all evaluated and rejected — each with a stated reason (a weekly batch load needs no orchestrator; BigQuery already does vector search; dbt is the industry-standard transform layer). The rejections are as visible as the adoptions.
A RAG API with a hard cost ceiling, not a soft one
FastAPI on Cloud Run answers questions over the published-article corpus (20 articles → 290 chunks, 286 successfully embedded and searchable as of 2026-08-13 — free-tier rate limits drop a small number of chunks per run, reproduced across multiple runs and logged to a load-run ledger rather than hidden — Gemini embeddings, BigQuery VECTOR_SEARCH). Because brute-force search scans the full table on every call, the real cost guard isn't request throttling — it's a per-query byte cap (maximum_bytes_billed), a single-instance ceiling, and a daily request limit, all verified against the free tier's numeric limits.
Technology Stack
Frontend
Looker Studio (evidence dashboard)
Backend
FastAPI (Python)Cloud Run
Database
BigQuery (raw / staging / marts via dbt Core)
Tools
Terraform (GCS state backend)dbt CoreGemini API (embeddings)
Development Process
Built in verifiable phases, each gated by physical evidence rather than 'should work': a free BigQuery sandbox proved the data model before any billing was enabled; a Terraform migration then rebuilt everything from scratch and made the repository public; dbt Core added typed, tested transformations (71 dbt tests passing) plus a Looker Studio dashboard; and a Cloud Run RAG API closed the loop with Gemini embeddings and BigQuery VECTOR_SEARCH. The repo going public went through an independent audit before shipping. The production-deploy audit was missed at launch time, caught during a post-launch review, and run retroactively — where it caught and fixed a Critical-severity issue. The gate miss itself is part of the record, not smoothed over.
Results & Metrics
Automated tests
0/144
Loader, dbt, and RAG API — built test-driven, all passing
Retrieval accuracy
0/5
Known-question benchmark, top-1 hit (similarity 0.71–0.84)
Monthly cost
¥0 (design ceiling)¥0 (design ceiling)
All components fit inside GCP's Always Free tier by design (forbidden-operations list, ¥1,000 budget alert, per-query byte cap); actual billing after the trial credit expires (2026-10-27) is an ongoing check, not yet confirmed