plan

1. A data ingestion pipeline is experiencing severe performance issues, taking 23 minutes to process just 113 XML files
2. Business requirement is to process up to 42,000 files per vendor (6 vendors total) within a 15-minute window during peak times. 

### The current implementation is built on:

- **Azure Data Factory (ADF)** – orchestrates copy / transform pipelines (`MDMF1`, `MDMF2`).
- **Azure Functions** – parses XML → Parquet.
- **Snowflake** – final landing for analytics tables.
- **MDMS framework** – metadata‑driven ADF wrapper that resolves dependencies via Azure SQL.

### **Detailed Problem Breakdown**

**1. Performance Gap**

- **Current State**: 113 files = 23 minutes (12.2 seconds per file)
- **Required State**: 42,000 files × 6 vendors = 252,000 files in 15 minutes (0.0036 seconds per file)
- **Performance Gap**: Current processing is approximately **3,400x slower** than required

**2. Pipeline Architecture Bottlenecks**

The end-to-end pipeline consists of four main stages:

| **Stage** | **Component** | **Duration (113 files)** | **Primary Function** |
| --- | --- | --- | --- |
| 1 | MDMF1 | 4m 41s | Copy XML files from landing to raw location |
| 2 | Azure Function | 2m 25s | Parse XML and convert to Parquet |
| 3 | MDMF2 | 8m 14s | Convert to Iceberg format |
| 4 | Snowflake SP | 7m 57s | Load to final table |

**3. Technical Constraints**

- **Azure Function Timeout**: Hard limit of 230 seconds for HTTP-triggered functions due to Azure Load Balancer constraints
- **Scaling Issues**: Single instance causes timeouts; requires 4+ instances for stability
- **Sequential Processing**: MDMF2 requires sequential file processing for Iceberg metadata generation
- **Configuration Overhead**: Significant time spent reading configuration from Azure SQL Database

**4. Attempted Solutions & Results**

- **Premium Plan Upgrade**: Implemented but didn't resolve timeout issues
- **Durable Functions**: Reduced processing time by 50% but still insufficient
- **Data Flow Optimization**: Reduced MDMF2 from 90 minutes to 8 minutes by file consolidation

## Questions

1. Can SQL logging be decoupled or batched to avoid Azure SQL concurrency limits?
2. Which MDMS lookup & proc steps in `MDMF1` contribute most latency – can they be cached or pruned?
3. Can Snowflake loads be parallelised or micro‑batched to reduce the 8‑minute wall‑clock time?

## Action Plan

### Phase 1:

1. Get Access and Current Performance Metrics
    1. Obtain access to all systems (including Github, azure and anything else)
    2. Run the pipeline with different file volumes (100, 500, 1000, 5000 files)
    3. Document exact timings for each component at each volume
    4. Identify if processing time scales linearly or exponentially
2. Review Durable Function Implementation
3. **Architecture Documentation**
    1. Map the complete MDMF framework flow
    2. Document all configuration dependencies
    3. Identify database calls and their frequency
4. Look into  Each Components
    1. **MDMF1 (4m 41s)**: Analyze why simple file copy takes so long
    2. **Azure Function (2m 25s)**: Profile the XML parsing logic
    3. **MDMF2 (8m 14s)**: Understand the data flow merging process
    4. **Snowflake SP (7m 57s)**: Analyze the stored procedure execution
5. Optimize
    1. Configuration caching (reduce SQL database lookups)
    2. Batch size optimization (currently at 50)
    3. Remove unnecessary pipeline layers
    4. Configure proper instance scaling policies
        1. Increase minimum Azure Function instances to 4
    5. Review and Optimize MDMF1 copy operations
    6. Implement parallel XML processing
    7. Review Snowflake stored procedure performance

**Expected Outcome**: 50% reduction in Azure Function processing time, elimination of timeout errors

### Phase 2: Further Optimization Analysis

## Key Metrics to Track:

- Files processed per minute
- Time per component at different volumes
- Instance scaling behavior
- Memory and CPU utilization
- Failure rates and timeout occurrences