Domain 4 of the official DP-900 skills outline includes "Describe considerations for data ingestion and processing." Analytics starts with bringing data together, so the difference between ETL and ELT, the pipelines and components of Azure Data Factory, and the ways to bring data into Microsoft Fabric come up regularly. Questions that ask you to tell apart similarly named components, such as linked services, datasets, and integration runtimes, are especially common.
This post is a supplementary entry to Large-Scale Analytics Services in our DP-900 study series. Building on the ETL, ELT, and Data Factory introduction there, it covers how to choose between the two approaches, what each Data Factory component does, how Fabric and Databricks ingest data, and HDInsight, which still shows up often in older material and practice questions.
---
What Is Data Ingestion? ETL and ELT Revisited
Picture a parcel distribution center. Packages are collected from across the country (extract), sorted by size and region (transform), and placed on warehouse shelves (load). If the sorting happens before the packages go into the warehouse, that is ETL; if everything goes into the warehouse first and is sorted inside, that is ELT.
| Aspect | ETL (extract → transform → load) | ELT (extract → load → transform) | |------|------|------| | Where transformation happens | Before loading, in a separate processing engine | After loading, inside the analytical store | | Raw data | Often only the transformed result is stored | Raw data is kept as is and transformed when needed | | Store it suits | A relational warehouse with a fixed schema | Stores with strong compute, such as data lakes and lakehouses | | Advantage | Only clean data goes in | Fast for large volumes; data can be transformed differently later | | Example | Clean data with Data Factory data flows and load it into SQL Database | Copy raw files into a lakehouse, then transform with Spark or SQL |
Transformation usually runs in parallel on distributed systems made of multiple nodes. The place where intermediate results are kept during transformation is called a staging area (or staging table); it lets you validate and clean data without putting load on the source system. Ingestion covers both batch processing of collected static data and streaming processing of events as they arrive.
Exam tip: "Copy data into the analytical store first, then transform it with the store's compute" → ELT. "Clean the data and fix its format before loading" → ETL.
---
The Building Blocks of Azure Data Factory
Azure Data Factory is a cloud data integration (ETL) service for building data movement and transformation workflows and running them on a schedule. Its component names are similar enough to cause confusion on the exam, so use the diagram and table to separate their roles.
!Building blocks of an Azure Data Factory pipeline
| Component | Role | Analogy | |------|------|------| | Pipeline | A logical group of activities that together perform one unit of work | A single recipe card | | Activity | One processing step in a pipeline | Each step of the recipe | | Dataset | The structure and location of data an activity uses as input or output | The ingredient list | | Linked service | Connection information for a data store or compute service (similar to a connection string) | The grocery store's address and pass | | Integration runtime | The compute environment where activities run; the bridge between activities and linked services | The kitchen where you cook | | Trigger | The condition that starts a pipeline run | The time you start cooking | | Mapping data flow | Transformation logic designed visually without code, run automatically on Spark clusters | An illustrated recipe |
A linked service answers "where and how do I connect?" while a dataset answers "which data inside it?" For example, the connection string for a storage account belongs in a linked service, and a specific container and folder inside that account belong in a dataset.
Three Kinds of Activities
Data movement activities: the Copy activity copies data between on-premises and cloud data stores. Data transformation activities: process data with mapping data flows, Databricks notebooks, stored procedures, HDInsight Hive queries, and more. Control activities: control the flow by chaining steps in sequence, branching with If Condition, looping with ForEach, or running another pipeline.
Integration Runtimes and Triggers
| Integration runtime | Use | |------|------| | Azure | Moving data between cloud data stores and running data flows | | Self-hosted | Reaching data on premises or inside a private network | | Azure-SSIS | Running existing SSIS (SQL Server Integration Services) packages as they are |
| Trigger | When the pipeline runs | |------|------| | Schedule | At set times and intervals (for example, every day at 2 a.m.) | | Tumbling window | Fixed, non-overlapping intervals; past windows can be rerun | | Storage event | When a blob is created or deleted | | Custom event | When a custom event arrives through Event Grid |
You can also run a pipeline manually (on demand) without a trigger. Pipelines can be deployed through CI/CD with Azure DevOps or GitHub, and their successes and failures monitored with Azure Monitor.
Exam tip: "Copy data from an on-premises SQL Server with Data Factory" → self-hosted integration runtime. "The component that holds connection information" → linked service. "Run a pipeline whenever a file is uploaded" → storage event trigger.
---
Ways to Bring Data into Microsoft Fabric
Fabric offers several ways to bring data into OneLake. Whether the data is actually copied, and whether it stays in sync, is how you choose between them.
| Method | What it does | Copies data | Choose it when | |------|------|------|------| | Fabric Data Factory pipelines | Orchestrate multi-step movement and transformation in sequence or in parallel | Yes | Typical batch ETL or ELT | | Dataflow Gen2 | No-code transformation built with Power Query | Yes | Reusable transformations without code | | OneLake shortcuts | Reference ADLS Gen2, Amazon S3, Google Cloud Storage, and others in place | No | Data must stay in its original location | | Mirroring | Continuously replicate Azure SQL Database, Cosmos DB, Snowflake, and others in near real time | Yes (automatic) | Operational databases ready for analytics without pipelines | | Eventstream | Ingest events from Event Hubs, Kafka, and IoT Hub in real time | Yes | Streaming data | | Notebooks | Ingest and transform directly with PySpark, Python, or SQL code | Yes | No connector exists or the logic is complex |
Fabric Data Factory is the next generation of Azure Data Factory and uses the same pipeline model, so your skills carry over. The difference is that where Azure Data Factory uses linked services, Fabric uses connections. If the destination is a service outside Fabric such as Azure SQL Database, or you work in a hybrid environment without Fabric, use the standalone Azure Data Factory.
Exam tip: "Query data in Amazon S3 from a Fabric lakehouse without copying it" → OneLake shortcut. "Keep replicating a production Azure SQL Database into OneLake without pipelines" → mirroring.
---
Ingestion and Processing in Azure Databricks and HDInsight
Azure Databricks
In Azure Databricks, Lakeflow pipelines let you declare what the output tables should contain, and Databricks manages execution order, dependencies, and incremental processing for you. They suit production batch and streaming ingestion. For exploratory or one-off ingestion, PySpark or SQL notebooks read from REST APIs, JDBC sources, and cloud storage and write to Delta Lake tables.
Azure HDInsight
HDInsight runs open-source big data frameworks such as Apache Spark, Hive, LLAP, Kafka, HBase, and Hadoop as managed clusters in Azure. It is not named in the current skills outline, but older study material and questions often feature it in scenarios such as "move an existing Hadoop ecosystem to the cloud as is."
| Service | Characteristics | Choose it when | |------|------|------| | Azure Data Factory | No-code pipelines, many built-in connectors, hybrid connectivity | Data integration outside Fabric, on-premises sources | | Fabric Data Factory | Pipelines and Dataflow Gen2 inside Fabric | Bringing data into OneLake for analysis in Fabric | | Azure Databricks | Spark-based, Lakeflow pipelines and notebooks | Code-first large-scale processing, Delta Lake | | Azure HDInsight | Managed clusters for open-source frameworks | Moving existing Hadoop, Hive, or Kafka jobs as they are |
Exam tip: "Run existing Hadoop and Hive jobs on managed Azure clusters with minimal changes" → HDInsight. "Copy and transform data from many sources on a schedule without code" → Data Factory.
---
Practice with Scenarios
Decide on your answer first, then check the columns on the right.
| Situation | Answer | Why | |------|------|------| | Put all raw logs into the lake first and transform them with Spark when needed | ELT | Transform inside the store after loading | | Run a pipeline that copies sales data every night at 2 a.m. | Schedule trigger | A set time | | Process data in one-hour slices and rerun past slices that were missed during an outage | Tumbling window trigger | Fixed, non-overlapping intervals | | Copy Oracle data from the company's own datacenter to Azure | Self-hosted integration runtime | Access to a private network | | Run SSIS packages you already use in the cloud | Azure-SSIS integration runtime | SSIS lift and shift | | Design Spark-based transformation logic visually without code | Mapping data flow | Spark clusters managed automatically | | Query data stored in another cloud from Fabric without moving it | OneLake shortcut | A reference, no copy | | Reflect Cosmos DB operational data in Fabric in near real time without ETL | Mirroring | Automatic continuous replication |
-