Batch, Streaming, and On-Premises Ingestion for Logistics
Tailspin Logistics ingests 3 TB per day from 1,200 CSV files dropped every 10 minutes by 14 warehouse systems, plus 4 TB of on-premises SQL Server order history that changes continuously. Operations dashboards need 15-minute latency; finance accepts four hours. Files occasionally gain new columns, and late files arrive up to six hours after their expected window. The company has Fabric F128, one lakehouse, and three analysts who know PySpark but not pipelines. They currently use a single Dataflow Gen2 that fails on large files and has no retry. A data gateway is installed for on-premises access.
1. Map each source to Fabric ingestion items and justify the choice. Address why the current Dataflow Gen2 approach is failing.
Read the worked answer
Use different tools for different shapes and latency:
- Warehouse CSV files: a Data Factory pipeline with a scheduled trigger every 10 minutes copies new files to a Bronze lakehouse folder, then a PySpark notebook performs cleansing and writes Delta Silver tables. Dataflow Gen2 is poor here because it is not designed for multi-terabyte high-volume file processing and the team already has PySpark skills.
- On-premises SQL Server order history: a pipeline using the on-premises data gateway performs incremental loads into Bronze, then a notebook merges into Silver. Continuous change can be handled by a watermark column and short intervals; the four-hour finance SLA does not require streaming.
- Operations events that truly need 15 minutes: if the warehouse systems can emit events, use Eventstream into the lakehouse; if they only drop files, a 10-minute scheduled pipeline can meet the SLA only after measuring file arrival jitter.
The single Dataflow Gen2 fails because it concentrates all volume and has no retry or quarantine path. Split ingestion by source and put orchestration in pipelines with retry and dependencies.
2. How would you handle schema drift and late-arriving files without breaking downstream reports?
Read the worked answer
Treat Bronze as append-only and tolerant, Silver as conformed and validated. In the Bronze notebook, read CSV with explicit options to capture malformed records, and write to Delta with schema evolution enabled so new columns are added rather than dropping the file. Do not let Silver consumers see raw drifted columns; maintain a mapping layer that maps known source columns to canonical names and routes unknown columns to a quarantine table for review.
For late files, partition Bronze by source and ingestion date, and make Silver merges idempotent using a natural key plus file metadata. A late file should upsert its rows without duplicating earlier loads. Publish a data-quality table that records file name, arrival time, row counts, rejected rows, and schema version. Downstream reports read only Silver/Gold, so a schema change produces a quarantine record and an alert, not a broken dashboard.
3. Design orchestration and monitoring so the three PySpark analysts can operate the solution.
Read the worked answer
Keep orchestration in pipelines and transformation logic in notebooks. Each domain gets one pipeline with activities: copy to Bronze, run Bronze-to-Silver notebook, run Silver-to-Gold notebook, then run data-quality checks. Set retries and dependencies so a failed copy does not trigger transformation. Use parameters for source paths and dates rather than editing notebook code per run.
Monitoring should be layered: pipeline run history for failures and retries, a Delta audit table written by each notebook, and alerting on freshness SLA breaches. The analysts own notebook logic because that matches their skills; the platform team owns pipeline templates and gateway connections. Do not ask the analysts to build custom orchestration in PySpark, and do not use a single monolithic notebook, because both make retries and partial recovery harder.