Forty Terabytes of Telemetry and a Monthly Bill Nobody Wants to Repeat
Contoso Logistics lands 40 TB of raw JSON and CSV telemetry from 8,000 trucks into Azure Data Lake Storage Gen2, growing at 3 TB per month. Two data engineers must clean the data and convert it to partitioned Parquet. Six analysts occasionally explore slices of the raw data - roughly 5% of the files, with unpredictable columns - about twice a week. A Power BI dataset must refresh hourly with pre-aggregated trip metrics for 300 dashboard users. A data science team of four wants PySpark notebooks for route optimisation. Last year's cluster bill came in at three times forecast, and leadership has mandated the minimum number of always-on services.
1. Choose the compute for the analysts' occasional ad hoc exploration and justify it against the alternative. What would you change about the data itself to make that choice pay off?
Read the worked answer
Choose the serverless SQL pool in Azure Synapse Analytics. The profile is twelve queries a week, unpredictable columns, and a small slice of the files. Serverless charges per terabyte of data scanned with no provisioning and nothing running when idle - that matches the workload exactly.
Reject the dedicated SQL pool for this workload. A dedicated pool is provisioned capacity sized for predictable concurrency. For twelve queries a week it is either always-on and mostly idle (the exact bill leadership is complaining about) or paused and slow to resume every time an analyst is curious. Both are wrong.
Make the choice pay off by changing the data, not the compute. Serverless bills on bytes scanned, so the cheapest query is the one that touches the fewest bytes:
- Convert raw CSV/JSON to Parquet. Columnar storage plus compression means a query selecting four columns reads four columns' worth of bytes rather than whole files.
- Partition the lake by date and ideally by truck or region, so a date-filtered query prunes files instead of scanning 40 TB.
- Expose views and external tables over the curated layer so analysts write
SELECT * FROM tripsagainst a governed shape instead of hand-writtenOPENROWSETpaths that scan the raw zone. - Put a per-query data-processed limit in place so one careless
SELECT *cannot cost more than the annual analyst budget.
The same advice applies to the nightly conversion: the two engineers should be writing Parquet once into a curated zone, and the analysts should never point at the raw CSV.
2. The Power BI dataset must refresh hourly for 300 users. Would you point Power BI at raw lake files, at the serverless pool, or at a dedicated SQL pool? Explain the reasoning and the mode you would use.
Read the worked answer
Never point Power BI at raw files. Every refresh, and with DirectQuery every user interaction, would re-run parsing and aggregation over the raw zone. The cost is paid repeatedly and the latency is exposed to 300 people.
The decisive distinction is Import mode versus DirectQuery. With Import mode, Power BI pulls a small pre-aggregated dataset into its in-memory model on a schedule; the 300 users are then served by Power BI itself and generate zero queries against Synapse. With DirectQuery, all 300 users' queries hit the source and force it to stay permanently sized and warm - which contradicts the minimum-always-on mandate and re-creates last year's bill.
So: build a gold layer of pre-aggregated trip metrics (hourly or daily grain, a few GB at most) and have Power BI import it on an hourly schedule. The aggregate can be produced by the serverless pool writing Parquet, with Power BI refreshing against that small, already-computed table. Refresh cost scales with the aggregate size, not the 40 TB.
When would a dedicated SQL pool be right? Only if the serving layer genuinely needs it: complex joins across many conformed dimensions, high query concurrency at sub-second latency, or DirectQuery as a hard requirement. Even then, size it small and pause it outside the refresh window. The default answer for 300 users on an hourly cadence is a small import model over a pre-built gold table.
3. Sketch the end-to-end pipeline that satisfies the engineers, analysts, data scientists, and BI users while keeping always-on services at a minimum. Name the service for each hop and say why the alternative was rejected.
Read the worked answer
Layered lake plus per-consumer compute - one storage layer, no permanently running cluster.
- Ingestion and orchestration - Synapse pipelines (or Azure Data Factory). Scheduled triggers, parameterised activities, dependency chaining, retries, and alerting on failure. Rejected alternative: cron plus scripts, which gives no lineage, no retry semantics, and no way to know a run failed at 02:00.
- Raw zone (bronze) - Data Lake Storage Gen2, immutable, partitioned by arrival date. Never overwritten; this is the replay source when transformation logic changes.
- Transformation - Synapse Spark pool, created per job and torn down after. PySpark flattens the JSON, applies quality rules, and writes partitioned Parquet to the curated zone (silver). Rejected alternative: keeping a Spark cluster always-on, which is exactly the idle-cost failure that produced last year's bill. Accept a one-to-three minute cluster start as the price of that saving, or keep one small warm pool only if the hourly SLA demands it.
- Analyst exploration - serverless SQL pool over the silver zone, pay-per-TB-scanned, nothing provisioned.
- Gold aggregates - written by the pipeline as a small partitioned Parquet dataset containing the trip metrics the dashboard needs.
- Serving - Power BI in Import mode refreshing hourly against the gold layer, so 300 users never touch Synapse.
- Data science - the four-person team runs notebooks against the silver Parquet, either on an auto-terminating Spark pool or a Spark cluster created on demand.
The pattern is uniform: storage is shared and cheap, compute is per-consumer and short-lived, and the only thing that runs hourly is a pipeline that touches a small gold table.