Memory hook: Ingest, store, transform, model and visualise are separate stages.
Must remember
- ETL transforms before loading; ELT loads first and transforms in the target platform. Data Factory supports orchestration/integration; Spark-based processing supports distributed transformations; Synapse and Fabric provide analytical capabilities with different resource/operating models.
- A warehouse stores curated analytical models; a lake stores diverse source data; a lakehouse combines lake storage with supported table/management capabilities. A star schema connects fact measurements to dimensions; analytical models may denormalise for efficient reporting.
- Event Hubs ingests event streams; Stream Analytics evaluates supported streaming queries; batch processing handles finite datasets. Event time and processing time differ, so late/out-of-order events need a policy.
- Power BI semantic models describe relationships and measures; reports contain interactive pages/visuals; dashboards present selected monitoring views. Import and DirectQuery modes differ in freshness, performance and source dependency. Refreshing a report cannot fix bad source data.
- Select charts by the comparison: trends over time, categories, distributions or relationships. Avoid misleading scales and aggregate-only conclusions. Apply access controls and row-level security where required; exporting data can create another governed copy.
Choose under exam pressure
| Requirement | Choice and reason |
|---|---|
| Orchestrate movement and transformation stages | Data Factory or an appropriate integration pipeline. |
| Analyse an unbounded event stream | A streaming ingestion/processing design. |
| Present business metrics interactively | Power BI with a suitable semantic model. |
Traps
- A dashboard is not the database of record.
- Streaming arrival time may differ from event time.
- A fast visual can still display stale or incorrect data.
Active recall
1. What distinguishes ETL from ELT?
Whether transformation occurs before or after loading into the destination environment.
2. What does a fact table normally contain?
Measurements/events and keys linking to descriptive dimensions.
3. Why does DirectQuery depend on source performance?
Queries are sent to the source rather than answered solely from an imported dataset.
4. Which service category collects high-volume events?
Streaming ingestion, such as Event Hubs.
5. Why define lineage for a dashboard?
To trace values back through models, transformations and sources.