Context
InsightMart, a mid-sized retail marketplace, uses Tableau dashboards for finance and operations reporting. Today, analysts connect Tableau directly to a PostgreSQL OLTP database, causing slow queries, inconsistent numbers during business hours, and frequent refresh failures.
You need to design a production-ready pipeline that connects the visualization layer to an analytics data source and ensures scheduled refreshes are reliable, observable, and easy to recover.
Scale Requirements
- Source system: PostgreSQL 14, ~250 tables, 1.2 TB total data
- Change volume: ~15M row updates/inserts per day
- Dashboard usage: 1,500 daily viewers, 120 published dashboards
- Freshness target: core sales dashboards updated every 15 minutes; finance dashboards every 4 hours
- Latency SLA: source change to dashboard availability < 20 minutes for priority datasets
- Retention: 2 years in warehouse, 7 years for finance aggregates
Requirements
- Design a pipeline that extracts data from PostgreSQL and loads it into an analytics warehouse without impacting OLTP performance.
- Model analytics-ready tables for Tableau consumption, including incremental refresh support.
- Define orchestration for ingestion, transformation, dependency management, and dashboard refresh scheduling.
- Ensure data quality through schema validation, row-count reconciliation, freshness checks, and duplicate detection.
- Support failed refresh recovery, backfills for missed windows, and idempotent reruns.
- Provide a secure connection pattern between Tableau and the warehouse, including role-based access.
- Describe how you would monitor pipeline health, refresh success, and data freshness SLAs.
Constraints
- Existing stack is AWS-first; prefer managed services where possible.
- Team size is 3 data engineers and 1 analytics engineer.
- Incremental monthly budget increase must stay under $18K.
- PII exists in customer tables; dashboards must expose only masked or aggregated fields.
- No direct Tableau queries against the transactional database are allowed in the final design.
