Design Reliable BI Data Refresh

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.

Context

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.

Problem

Context

Scale Requirements

Requirements

Constraints

Design Reliable BI Data Refresh

Problem

Context

Scale Requirements

Requirements

Constraints