Databases & Data Platforms

Data modeling, SQL, and data platforms: a medallion lakehouse on Databricks with a scheduled pipeline, a PostgreSQL tuning lab where every index is a measured decision and a CI job fails the plan regressions, GA4 site tags taken through BigQuery, PySpark and Airflow to monitored KPIs on Google Cloud, plus mini data marts, KPI marts, ETL/ELT, integrity checks, and pragmatic SQLite/DuckDB workflows.

Integration Java .NET Python AI/ML Data React Angular

Back to Projects

Tagline (GA4 Tags to KPIs on Google Cloud)

A React storefront tagged with GA4's ecommerce events against a machine-checked contract, and the pipeline behind it: BigQuery over the site's own GA4 export and Google's public GA4 sample, multi-touch attribution in PySpark on Dataproc, one Airflow DAG, measured cost cuts, tag QA and KPI alerts. The site's traffic is simulated, and labelled as such everywhere.

Stage 1: Tag the site — 14 events validated against a JSON Schema contract as they fire, shown in a Tag Inspector; user_id is an opaque account id, never the email. 67 unit tests and a Playwright funnel test.
Stage 2: Stitch and enrich in BigQuery — 4.3 million sample events into 360,129 sessions and 4,918 orders that count as conversions; without the transaction_id dedupe, revenue reads 6.5% high. On the site's export, 8 people stitched across 17 devices.
Stage 3: Attribution in PySpark on Dataproc, run by Airflow — every order credited under six models, reconciling to the cent; the store's self-referral falls from 17.0% of last-click revenue to 4.0% of first-click (orders with a complete 30-day lookback).
Stage 4: Cost, measured — nine experiments, each kept or reverted on its numbers: a daily DAG run from 480 s and $0.081 to 249 s and $0.019, BigQuery billing 75% less, outputs unchanged but for two last-bit sums.
Stage 5: Guard the data — a 46-test tag QA suite in CI catches 12 of 12 deliberate tag breaks; anomaly alerts backtested on 92 days raised 114 alerts in 12 incidents, all but one traced to six real problems in Google's own data.
Stage 6: Run it like a product — a RICE-scored roadmap of 26 items traced to Stages 1–5, three GitHub issue forms for intake, a decision log, and a static dashboard over a 71 KB snapshot of the marts.

GA4 BigQuery SQL PySpark Dataproc Airflow React TypeScript Playwright GitHub Actions

Live / Demo

Tagging: dataLayer pushes in GTM's ecommerce format from typed builders, each validated with Ajv against events.schema.json before it is pushed; Consent Mode v2 through the standard gtag shim; purchase claimed once per transaction_id before it fires.
Warehouse: BigQuery, one SQL file per table, with a table layout measured in Stage 4; every job capped with maximum_bytes_billed and labelled; 11 data checks that fail the build, one of them for email-shaped strings.
Identity: anonymous sessions stitched to signed-in people on an opaque user_id; purchases sent with consent denied stay as orders with no session, counted but never guessed into a journey.
Attribution: PySpark DataFrame functions unit-tested on hand-built journeys, run as a Dataproc Serverless batch; an independent BigQuery rebuild matches all 73,962 rows, the sample's 73,890 and the site's 72 (largest weight difference 2.2e-16).
Orchestration: one Airflow 3 DAG in Docker, locally rather than on Cloud Composer, which bills for every hour an environment exists; since Stage 4 a daily incremental build in one BigQuery transaction.
Monitoring: each KPI and tag-health rate against the same weekday's median over 21 days with a leave-one-out MAD band, plus rules for missing days and vanished events; thresholds in one TOML file; delivery to a webhook, tested against a local sink.
Dashboard: a static page with hand-built SVG charts over a committed JSON snapshot: aggregates only, validated against a JSON Schema and a privacy scan, with no request beyond its own files.
Data: Google's public GA4 sample (real store traffic, obfuscated by Google) and the site's own GA4 export of simulated shoppers. Campaign spend and unit costs are synthetic and flagged in their tables.

Review Radar (Databricks Lakehouse)

A daily pipeline that ingests Apple's public App Store reviews feed for my own app and five competitors into a bronze → silver → gold medallion lakehouse on Delta Lake, then publishes the gold layer as a public dashboard. Runs on Databricks Free Edition at $0.

Stage 1: Bronze ingest — raw JSON appended as fetched, plus a one-time mosthelpful backfill for version history (the feed caps at ~500 recent reviews).
Stage 2: Silver MERGE upsert keyed on review id (App Store reviews are editable), then the first gold aggregates.
Stage 3: Two-task Lakeflow job on serverless compute, scheduled daily with retries and failure alerts.
Stage 4: AI/BI dashboard — rating by version, weekly velocity, competitor comparison.
Stage 5: Databricks Asset Bundles (job deployed from code) and a GitHub Action that publishes a nightly JSON snapshot to a Vercel dashboard.
Stage 6: Terraform an Azure Databricks workspace and deploy the same bundle to it — the pipeline was never tied to one platform. Provisioned, verified, screenshotted, and destroyed the same day, with the teardown checked against Azure rather than trusted.

Databricks PySpark Delta Lake Spark SQL Medallion Asset Bundles Terraform Azure GitHub Actions Angular

Live / Demo

Lakehouse: Unity Catalog tables on Delta Lake — bronze_app_reviews (append-only raw JSON), silver_reviews (one typed row per review), three gold aggregates.
Idempotency: the silver MERGE pre-deduplicates its source with ROW_NUMBER() and only rewrites a row when the content actually changed, so a re-run reports zero inserts and zero updates.
Honest aggregates: version averages require n ≥ 20; weekly velocity is limited to each app's continuously-observed window and flags partial edge weeks instead of drawing a false ramp.
Orchestration: a two-task Lakeflow job (ingest → transform) on serverless compute; job parameters override notebook widgets so a scheduled run can never pick up a manual setting.
Deployment: databricks.yml Asset Bundle with a development target that auto-pauses its schedule, so deploying from a laptop can never collide with the live job.
Publishing: a scheduled GitHub Action calls the SQL Statement Execution API, commits gold_snapshot.json, and Vercel redeploys — aggregate numbers only, no review text or author names.
Data source: Apple's public RSS reviews feed (unofficial and unsupported; polled at 1 request/second). Not affiliated with Apple or any listed app.

Query Plan Forensics (PostgreSQL)

My own inventory schema scaled to five million rows with a fixed seed and deliberate skew, then tuned one change at a time and measured — including the three optimizations I reverted. A GitHub Action fails a pull request when a query plan changes shape.

Stage 0–1: Postgres 17 pinned in Docker with every planner GUC in git and checksums on at initdb; a generator with setseed, a power-law over SKUs, and physical ordering by created_at — the load-time decision that determines which index types exist two stages later.
Stage 2: Benchmark harness reading Execution Time out of the EXPLAIN JSON rather than timing the client, round-robin iterations to spread thermal drift, and a coefficient-of-variation warning when the numbers stop being defensible.
Stage 3: Indexing one change at a time — composite vs. covering, the partial index that works and the one Postgres refuses to build, BRIN vs. B-tree. Q04 17×, Q09 12.8×, Q01 3.6×.
Stage 4: The invoice — write amplification in time and WAL bytes, extended statistics that fixed a 6× estimate error and changed nothing, and a 34 MB index Stage 3 had silently made redundant.
Stage 5: Monthly range partitioning, measured and reverted: two queries ~40% slower against a 7.6% win on the one it was built for.
Stage 6: Materializing the valuation — 5,118× faster reads, a break-even of 0.84 reads, and the conclusion that compute cost is a rounding error so the whole decision is staleness.
Stage 7: A CI gate that compares plan shape and estimated cost — never wall-clock — and fails the PR when a “cleaner” query rewrite kills an index.

PostgreSQL 17 EXPLAIN / plans Indexing Partitioning DataGrip Docker pytest GitHub Actions

Reproducibility: setseed(0.42) with parallel workers disabled during generation, so two full loads hash identically; a destroyed volume rebuilds to plans that still pass baselines captured on the old one.
Skew on purpose: power(random(), 5) puts ~55% of movements on the top 5% of SKUs, and each product ships from a home warehouse 85% of the time — uniform data gives partial indexes and extended statistics nothing to bite on.
Measurement: timings read from the EXPLAIN JSON, not the client; round-robin iterations; every A/B alternates configurations because a single before/after pair credits the second run with the first one’s cache warm-up.
The gate: asserts plan fingerprint, root Total Cost within ±15%, Plan Rows within 2×, and that required indexes still appear. Never wall-clock — a GitHub runner’s IO is nothing like a laptop’s.
Reverted after measurement: BRIN (1,523× smaller and the planner never chose it), monthly partitioning (Q10 +40.9%, Q04 +39.9%), and a 34 MB index the covering index had silently superseded.
Kept: the covering index, the partial index, the denormalized reorder_point (at 3.2× write amplification), and the materialized valuation refreshed CONCURRENTLY.
Self-audit: three real defects in my own committed schema, reproduced before being fixed — a GREATEST(v_delta, 0) clamp that silently writes zero, a CHECK that makes negative stock adjustments unrecordable, and balances that were never derived from the ledger.
DataGrip: visual plan trees, baseline and tuned databases as two data sources in one project, and schema compare as the migration diff.
Dataset: synthetic, generated from a fixed seed. No real customer or production data.

Mini Data Mart (DuckDB)

A staged DuckDB + Python mini data mart built around a star schema (dimensions + fact table), with analytics queries and export utilities.

Stage 1: Build the mart with hardcoded seed data (clean, repeatable setup).
Stage 2: Load facts (and optionally dimensions) from CSV so new data updates the mart.
Stage 3: Refactor into a Python package with a CLI (python -m mart) — build, explore, quality checks (nulls + referential integrity), and export.
Stage 4: Multi-CSV inputs (separate dimension files) plus an optional Streamlit dashboard and Tkinter desktop UI over the mart.

DuckDB SQL Pandas ETL Data Modeling

Live / Demo

Modeling: star schema with dim_customer, dim_product, dim_date, and fact_sales.
Stage 1: repeatable rebuild using seed data in Python (drops + recreates tables).
Stage 2: CSV-driven loads so editing fact_sales.csv reflects in DuckDB after rebuild.
Exports: helper scripts write tables to data/CSV-export and data/JSON-export.
Dataset: synthetic / generated data (no real customer or production data).

Support Ticket Analytics (Ops KPIs + NLP)

End-to-end analysis of support tickets: data prep, backlog/SLA proxy KPIs, lightweight text signals, and trend dashboards. Includes a synthetic generator + ETL build script.

Pandas Streamlit Dash/Plotly DuckDB NLP Analytics