From scattered spreadsheets and two disconnected systems to one governed pipeline

The problem

The client, a small events-industry business running recurring ticketed events, had two systems of record that never talked to each other: a transactional PostgreSQL database holding bookings, attendees, and event details, and Klaviyo handling email marketing and campaign sends. Answering even simple questions (“which campaigns actually drove bookings?”, “who are our repeat customers?”, “how full were last quarter’s events?”) meant someone manually exporting CSVs from two different admin panels and reconciling them by hand in a spreadsheet. There was no shared definition of “active customer” or “repeat customer,” so every report risked contradicting the last one.

What I built

A single pipeline that pulls from both systems, applies one consistent set of business rules, and lands the result in a dashboard the whole team trusts. It’s the kind of dashboard where “which campaigns actually drove bookings?” finally has one answer instead of two conflicting exports:

• Ingestion from PostgreSQL (incremental, watermark-based) and the Klaviyo API (campaigns, email engagement, and profile data), landing as schema-enforced, type-safe Parquet.
• A Bronze → Silver → Gold medallion model. Bronze is raw but typed. Silver applies SCD Type 2 versioning across every dimension table (8 dimensions, 8 fact tables), so historical state is never lost: you can still answer “what did this attendee’s segment look like at the time of the campaign,” not just its value today. Gold holds business-ready aggregates built for the questions the team actually asks.
• Data quality checks at every layer boundary. Schema and null checks and primary-key uniqueness at Bronze, referential integrity and SCD-rule checks at Silver, non-negative and cross-layer row-count checks at Gold. Referential-integrity violations get quarantined to their own files instead of silently dropped, so a bad upstream record stays auditable and recoverable instead of just gone.
• Orchestration via Airflow, running on a daily schedule with a separately-triggered full-refresh DAG for historical backfills.
• Export to SQLite feeding a 10-tab, 90+ card Metabase dashboard, with sub-second query performance for analysts because Gold is already the aggregate the dashboard needs, not a live join across raw tables.
• Deployment via Terraform to AWS EC2, with CI/CD, HTTPS, and automated off-box backups. It’s the same delivery pattern this site itself runs on.

Why it matters

The pipeline turned “someone reconciles two exports by hand” into “the dashboard is always current and everyone is looking at the same numbers.” SCD2 versioning means a campaign-attribution question asked six months from now still gets the correct historical answer, not one silently corrupted by today’s current state. A 65-plus-test suite and layer-boundary data quality checks mean a bad upstream record surfaces as a loud, specific failure instead of a quietly wrong number three dashboards downstream.

This is the same pipeline architecture (medallion layers, SCD2, DQ gates, Airflow orchestration) used for the public Olist e-commerce demo, just applied to this client’s real sources.

Details here are deliberately anonymized (no client name, domain, or revenue figures) pending sign-off. Architecture, tooling, and the metrics above are real.