Topic 1 of 6
Data ingestion and processing
Before analyzing, you have to bring data from transactional databases, files, and real-time streams into an analytical store. This article explains the pieces of a large-scale analytics solution and Microsoft's tools for moving and transforming data. The exam asks you to tell ETL from ELT and to pick the right tool.
Contents
Large-scale analytics
Large-scale analytics solutions combine conventional data warehousing, used for business intelligence (BI), with big data techniques.
Conventional data warehousing
Copies data from transactional stores into a relational database with a schema optimized for querying and multidimensional models.
Big data
Large volumes in multiple formats, batch loaded or captured in real-time streams, stored in a data lake and processed with distributed engines like Apache Spark.
Combining a data lake's flexible storage with a warehouse's SQL analytics led to the design called a lakehouse.
Elements of the architecture
Ingestion and processing: Data from transactional stores, files, real-time streams, or other sources is loaded into a data lake or a relational data warehouse, being cleaned, filtered, and restructured. It's usually done by distributed systems that process in parallel on multi-node clusters, in batches and in streams.
Analytical data store: Relational data warehouses, file-based data lakes, and hybrid architectures (lakehouses).
Analytical data model: Today, a semantic model (the tabular model behind Power BI and Fabric) with tables, relationships, hierarchies, and measures written in DAX. Preaggregated cubes were used before.
Visualization: Reports, dashboards, and visual exploration, including self-service for non-technical users.
AI-assisted analytics: Copilot in Fabric and Power BI, and Genie in Azure Databricks, let you ask in natural language for DAX or SQL queries, summaries, and answers.
ETL vs. ELT
ETL · Extract, Transform, Load
Data is transformed before it's loaded into the analytical store.
ELT · Extract, Load, Transform
Data is copied to the store and transformed afterward. It's a common pattern in modern lakehouses.
Either way, the result is a structure optimized for analytical queries.
Fabric Data Factory
The recommended starting point for pipeline-based ingestion in Microsoft Fabric. It brings the simplicity of Power Query and more than 200 native connectors to on-premises and cloud sources. It offers two complementary tools:
Pipelines
Orchestrate multi-step movement and transformation workflows, chaining activities in sequence or in parallel. They use connections to reach sources and destinations: load data, run stored procedures, invoke notebooks.
Dataflows Gen2
A visual, low-code experience for building reusable transformation logic with Power Query, without writing code.
The output can be saved to a Fabric Lakehouse or Warehouse, or any other supported destination. It's the next generation of Azure Data Factory.
Shortcuts and mirroring
OneLake shortcuts
A live reference to external storage: ADLS Gen2, Amazon S3, Google Cloud Storage, or another OneLake location. External data appears as if it were already in the Lakehouse: no pipelines, no movement, no duplication. Useful when data must stay where it is for compliance or cost.
Mirroring
Continuously replicates an external database (Azure SQL Database, Snowflake, Azure Cosmos DB, and others) into OneLake in near real time. You configure the connection once and Fabric handles change tracking, with no pipeline authoring. The data is queried through a SQL analytics endpoint.
Eventstream and notebooks
Eventstream
Real-time streaming ingestion from Azure Event Hubs, Apache Kafka, Azure IoT Hub, and custom endpoints. It routes, filters, and transforms events before landing them in a Lakehouse or an Eventhouse (KQL databases optimized for time series).
Fabric notebooks
A code-first option (Apache Spark) when no connector exists or custom logic is needed. You write PySpark, Python, Scala, R, or SQL to read from REST APIs, databases, or files and write to Delta tables in the Lakehouse or Warehouse. They run interactively or scheduled in a pipeline.
Azure Data Factory
The standalone Azure service for building integration pipelines outside Fabric: for example, when the destination is Azure SQL Database or an external service, or when connecting to on-premises sources in a hybrid environment.
It uses the same pipeline model as Fabric Data Factory (with linked services instead of connections), so skills transfer between the two. Data engineers use it for ETL solutions that move data from transactional systems into analytical stores.
Ingestion in Azure Databricks
Lakeflow pipelines
A declarative framework for reliable, incrementally updated pipelines on Delta Lake. You define what the tables should contain and Databricks handles ordering, dependencies, and incremental processing. For production, batch and streaming.
Databricks notebooks
PySpark, Python, Scala, SQL, and R for exploratory or ad-hoc ingestion. They can be scheduled as jobs or embedded in a Lakeflow pipeline.
Auto Loader
Incrementally and idempotently loads data from cloud object storage and data lakes into the lakehouse.
Which tool for each case
| Tool | Approach | Does it copy data? |
|---|---|---|
| Pipelines (Fabric or ADF) | Activity orchestration | Yes |
| Dataflows Gen2 | Visual transformation with Power Query | Yes |
| Shortcut | Live reference | No |
| Mirroring | Continuous database replica | Yes, automatically |
| Eventstream | Real-time events | Yes, as a stream |
| Notebooks | Code with Spark | Depends on the code |
Exam scenarios
| Situation | Answer | Why |
|---|---|---|
| Orchestrate several steps: copy data, run a procedure, then a notebook | Fabric Data Factory pipeline | It chains activities in sequence or in parallel. |
| Transform data without writing code, using Power Query | Dataflows Gen2 | A visual, low-code experience. |
| Query Amazon S3 data from Fabric without copying it | OneLake shortcut | A live reference, no data movement. |
| Continuously replicate Azure SQL Database into OneLake without building pipelines | Mirroring | Fabric handles change tracking. |
| Ingest events from Event Hubs or Kafka in real time | Eventstream | It routes and transforms event streams. |
| Read a REST API with no connector and apply custom logic | Fabric notebook | Code in PySpark, Python, Scala, R, or SQL. |
| A pipeline to Azure SQL Database with on-premises sources, outside Fabric | Azure Data Factory | A standalone integration service. |
| Declarative, incremental pipelines on Delta Lake in Databricks | Lakeflow pipelines | Databricks handles ordering, dependencies, and increments. |
Common exam traps
❓ In ETL, is data loaded before it's transformed?
No. In ETL it's transformed before loading; in ELT it's loaded first and transformed in the store.
❓ Does a OneLake shortcut copy the data?
No. It's a live reference: the data stays at its source.
❓ Does mirroring require building pipelines?
No. You configure the connection once and Fabric handles replication and change tracking.
❓ Which Fabric tool lets you transform data without code?
Dataflows Gen2, with Power Query.
❓ Do Azure Data Factory and Fabric Data Factory use different models?
No. Both are pipeline-based; ADF uses linked services instead of connections.
❓ What does Lakeflow pipelines offer in Databricks?
Declarative, incremental pipelines on Delta Lake; Databricks handles ordering and dependencies.
Did you get this topic?
Test what you just learned
For compliance reasons, some files must stay in an Amazon S3 bucket, but the team wants to query them from a Fabric Lakehouse. What do they use?
Ready to test "Data ingestion and processing"?
Original questions, each with a full explanation.
Start a mock examSign in to save your progress.