Executive BI Datamart Pipeline: Redshift Serverless & Glue ETL
Automating dimensional data warehouse transforms from transactional OLTP databases into Redshift Serverless and Amazon QuickSight.
1. Business Problem & Context
An international retail brand ran complex analytical SQL queries directly against their production PostgreSQL databases. Nightly sales report generation locked production tables, causing order checkout slowdowns. Furthermore, queries scanning multi-year sales trends took 45 minutes to execute in standard databases.
2. Requirements & Constraints
- Zero Impact on Production OLTP: Analytical queries must never query production transactional databases.
- Sub-2s Executive Dashboards: C-level dashboards in Amazon QuickSight must load in under 2 seconds.
- Serverless Cost Model: Automatically shut down warehouse compute when no queries are running.
3. Architecture Overview & Data Flow
Interactive Architecture Diagram (Use controls to zoom & pan)
4. AWS Services Used & Rationales
AWS Services Architecture Rationale
Concrete reasons why these specific services were chosen over alternatives
| Service | Category | Architectural Rationale ("Why this service?") |
|---|---|---|
| Amazon Redshift Serverless | Analytics | Columnar data warehouse that scales Redshift Processing Units (RPUs) dynamically and pauses when idle. |
| AWS Glue (Apache Spark) | Analytics | Transforms raw normalized transactional tables into a star schema (Fact Sales, Dim Customers, Dim Stores). |
| Amazon QuickSight | Analytics | Provides paginated executive reports and ML-powered automated anomaly insights. |
5. Key Design Trade-offs
Architecture Decision & Trade-Off Matrix
Evaluating alternative approaches under real-world constraints
Dedicated Provisioned Redshift Cluster (dc2.8xlarge)
- + Fixed monthly predictable cost
- − Billed 24/7 even when idle ($3,500/mo)
- − Manual resizing requires cluster downtime
Redshift Serverless (Chosen)
✓ Chosen Design- + Charges only for query runtime seconds
- + Automatically handles complex aggregations
- + $0.00 spend at night
- − Requires base RPU and max RPU ceiling limits
6. Implementation Highlights
Redshift DDL Star Schema Table Design with Distribution Keys
CREATE TABLE fact_sales (
sale_id BIGINT IDENTITY(1,1),
customer_id INT NOT NULL,
store_id INT NOT NULL,
sale_timestamp TIMESTAMP NOT NULL,
total_amount DECIMAL(12,2) NOT NULL
)
DISTKEY(customer_id)
SORTKEY(sale_timestamp);7. Results & Key Metrics
- Report Query Performance: Dropped from 45 minutes to 1.4 seconds.
- Production DB Health: 0% CPU impact on transactional checkout systems.
8. Key Architectural Takeaways
OLTP vs OLAP Rule: Never run heavy aggregate business intelligence queries against production transactional databases. Always ETL into a dedicated columnar warehouse like Redshift Serverless.
