Microsoft Certified: Fabric Analytics Engineer AssociatePlan and implement data analytics solutions (10-15%)Medium
A company is ingesting real-time sensor data into a Microsoft Fabric Lakehouse. The sensor data arrives as small, frequent messages, and needs to be stored in a Delta table. Due to the high volume and velocity, the data engineer is concerned about the 'small file problem' impacting query performance later. Which ingestion pattern should be used to mitigate this issue effectively?
- ABatching sensor messages into larger micro-batches before writing to the Delta table.
- BWriting sensor data to a non-Delta format first, then converting it to Delta daily.
- CDirectly writing each sensor message as a separate small Delta file.
- DUsing a separate process to `VACUUM` the Delta table frequently after each write.
Show answer & explanationAnswer & explanation
Correct answer: A. Batching sensor messages into larger micro-batches before writing to the Delta table.
Batching small, frequent messages into larger micro-batches before writing them to a Delta table is the most effective strategy to mitigate the small file problem for streaming data. This reduces the number of files and metadata overhead, significantly improving subsequent query performance.
Why the other options are wrong
- B. Converting formats daily adds unnecessary complexity and latency, and doesn't solve the real-time ingestion challenge effectively; it also shifts the small file problem to the conversion step.
- C. Directly writing small files exacerbates the small file problem, leading to poor query performance.
- D. `VACUUM` removes old, unreferenced files for retention, but it does not consolidate existing small files into larger ones, nor does it prevent the creation of new small files during ingestion.
Micro-batching for Streaming Ingestion
Micro-batching is an ingestion pattern for streaming data where small, continuous data streams are collected into larger, time-based batches before being written to storage, mitigating the 'small file problem' and improving write/read efficiency.
- Reduces the number of files generated.
- Optimizes for distributed file systems.
- Balances latency with throughput efficiency.
Memory trick: Batch your streams, keep files big, and queries fast.