Three sources, three latencies, one F64 capacity
Fabrikam Instruments runs 1,200 line sensors across four plants. Each sensor posts a 2 KB JSON message about once per second, and the plant operations dashboard must show per-line throughput within five seconds. The ERP runs on SQL Server 2016 inside the corporate network; about 2,000 rows change per minute and finance needs an updated position within two minutes. Every night a partner drops a 1.4 TB CSV extract on an SFTP server reachable only through the corporate firewall, and it must be queryable by 06:00.
The team has an F64 capacity, strong Spark skills, almost no Power Query skills, and a hard rule that no report user may query a source system directly.
1. Assign each of the three sources to a Fabric ingestion mechanism and justify the choice on latency, volume and operational risk.
Read the worked answer
Sensors → Eventstream → Eventhouse (KQL database). ~2,400 messages/second with a five-second visibility target. A pipeline or notebook cannot hit that scheduling granularity; Eventstream ingests continuously and Eventhouse stores in a columnar, time-indexed store with retention and caching policies you control. This is the only source where the latency requirement forces the mechanism, so it is decided first and everything else fits around it.
ERP → Mirroring over an on-premises data gateway, with a watermark pipeline as the fallback. Mirroring replicates continuously with no authoring and no query load on the ERP beyond the change feed. The risk to test before committing: the tables must expose a reliable change indicator, and physical deletes must be represented. If they are not (common on older SQL Server schemas), a pipeline reading a last-modified watermark and MERGEing into a lakehouse every minute is the better-traded option - more authoring, but you control delete detection and late updates.
SFTP CSV → data pipeline Copy to bronze, then Spark to Delta. Do not parse in a dataflow (see next task). Copy lands the file as Parquet/Delta in bronze through the gateway, and a notebook does the validation, cast and merge. Keeps the heavy transform on Spark where the team is strong.
The hard rule is satisfied because every report path terminates in OneLake or Eventhouse, never in the source.
2. Where do you physically land the sensor data so that both the five-second dashboard and a twelve-month SQL analysis of join-with-ERP work? Explain the layout and the policies involved.
Read the worked answer
Keep the streaming detail in the Eventhouse and make it readable from OneLake rather than copying it eagerly.
- Eventhouse as the system of record for streaming detail. Set a caching policy that covers the working set the dashboard touches (recent hours) and a retention policy for the long tail. Materialised views pre-aggregate per-line throughput per 10-second bucket so the five-second dashboard reads a small pre-computed table instead of scanning raw rows - this is what keeps the dashboard responsive when the sensor count grows.
- Enable OneLake availability on the KQL database so the same tables can be read as Delta through OneLake from a lakehouse. No second pipeline, no drift between the streaming copy and the analytical copy.
- Analysts join in the lakehouse. Expose the Eventhouse table through a OneLake shortcut alongside the ERP-mirrored tables, and let Spark or the SQL endpoint do the twelve-month aggregation. Alternatively query the shortcut as an external table from KQL if the analysis stays time-series shaped.
What to avoid: writing every sensor message to lakehouse files as small JSON, then compacting later. That path costs capacity twice and still cannot serve a five-second dashboard.
3. The team proposes a Dataflow Gen2 for the 1.4 TB nightly file. What breaks first, and what is the alternative?
Read the worked answer
The failure is not correctness, it is capacity and time. A Dataflow Gen2 is a good fit for connector-heavy, modest-volume transformation, but a 1.4 TB CSV means parsing, staging and writing that volume inside one compute-bound operation. The first symptoms will be an execution that exceeds the practical run window, CU consumption that dwarfs everything else on an F64 and pushes the evening into throttling, and a single opaque failure with no partial restart - you re-read 1.4 TB from scratch.
Softer but still relevant: the team has almost no Power Query skills, so every schema change on the partner file becomes a support incident.
Alternative:
- Copy activity lands the raw file into bronze as Parquet/Delta (fast copy, gateway-resident read).
- A Spark notebook validates row counts and schema, deduplicates, and MERGEs into silver partitioned by business date.
- The pipeline is parameterised by file name so a failed night restarts from bronze rather than re-reading SFTP.
Use the dataflow only for the small, connector-facing tail of the estate where low-code is genuinely the cheaper option.