Redesigning the ETL Pipeline to Meet Analytics SLAs
An e-commerce company's daily analytics batch pipeline fails its morning SLA while compute costs rise.
Context
You are a Data Engineer at an e-commerce company. The central data platform ingests transactional data, inventory records, and user clickstream events into a cloud data warehouse overnight. Analytics dashboards used by commercial and operations teams must be refreshed by 07:00 AM daily. Over the past six weeks, the pipeline has consistently completed past 09:30 AM, blocking morning trading decisions and generating executive escalations. Meanwhile, cloud warehouse spend has jumped 40% in three months. The IT Manager wants your assessment by noon today on how to resolve the SLA breach and stabilize costs.
Your Task
Deliver your assessment in writing for the IT Manager structured into three sections:
- Recommendation
- Impact
- Implementation Plan
Available Data
The daily data pipeline runs through an orchestrator managing 18 interdependent tasks across ingestion, staging, transformation, and presentation layers.
Daily Pipeline Performance by Stage
| Stage | Source Tables | Row Volume | Average Runtime | Failure Rate | Monthly Compute Cost |
|---|---|---|---|---|---|
| Ingestion (Kafka to Staging) | Transactions, App Events | 42,000,000 | 45 min | 1.2% | $1,800 |
| Staging Cleansing | 14 raw staging tables | 42,000,000 | 35 min | 0.5% | $1,200 |
| Core Transformation (dbt) | Dimension & Fact models | 120,000,000 | 165 min | 8.4% | $6,400 |
| Reporting Mart Refresh | Aggregated business marts | 8,000,000 | 50 min | 2.1% | $2,600 |
Pipeline execution begins at 02:00 AM. Over the last month, the Core Transformation stage has expanded from 65 minutes to 165 minutes, pushing the finish time from 06:15 AM to 09:55 AM on average.