01Introduction
Replacing Fragile Nightly Batch Jobs with Real-Time Azure Pipelines
02Problem Statement
- Financial services firm: 40 nightly batch ETL jobs in legacy SSIS packages
- Jobs failing 2-3 times/week; data engineering team spending 40% of time on fixes
- Business requirement: real-time data for risk dashboards and regulatory reporting
- Challenge: modernize without breaking downstream BI tools and data consumers
- Timeline: 6-month phased migration; critical regulatory report cannot be delayed
03Proposed Solution
Phase 1
Azure-Native Streaming + Batch Architecture
- Azure Data Factory for orchestration; replaced SSIS packages with ADF pipelines
- Azure Databricks (Spark) for transformation; Delta Lake for ACID-compliant storage
- Azure Event Hubs for real-time streaming from source systems
- Azure Synapse Analytics as serving layer for Power BI and regulatory reporting
- Python (PySpark) for all transformation logic; unit-tested via pytest
Phase 2
Migration Execution & Quality Gates
- Migrated 40 SSIS packages to ADF + Databricks notebooks over 6 sprints
- Data quality framework: Great Expectations checks on every pipeline stage
- Parallel run period: old and new pipelines ran simultaneously for 4 weeks
- Row count and checksum reconciliation automated; zero-discrepancy gate before cutover
- CI/CD: GitHub Actions deploys Databricks notebooks and ADF ARM templates
04Technologies
- Azure
- Data FactoryDatabricksSynapse AnalyticsEvent HubsADLS Gen2
- Languages
- Python (PySpark)SQLARM templates
- Data Quality
- Great ExpectationsCustom reconciliation scripts
- Orchestration
- ADF trigger chainsDependency management
- Monitoring
- Azure MonitorLog AnalyticsCustom pipeline health dashboards