Data Ingestion with Synapse Pipelines and Data Factory

Data Ingestion with Synapse Pipelines and Data Factory – Complete Guide for DP-203

Why Is Data Ingestion with Synapse Pipelines and Data Factory Important?

Data ingestion is the foundational step in any data engineering workflow. Without a reliable, scalable, and manageable way to move data from source systems into your analytical platform, downstream processing, transformation, and reporting are impossible. Azure Synapse Pipelines and Azure Data Factory (ADF) are Microsoft's primary orchestration and data movement services, and they are heavily tested on the DP-203 exam. Understanding how to design, configure, monitor, and troubleshoot ingestion pipelines is essential for both the exam and real-world data engineering.

What Are Synapse Pipelines and Azure Data Factory?

Azure Data Factory (ADF) is a fully managed, serverless cloud-based ETL/ELT and data integration service. It allows you to create data-driven workflows (called pipelines) that orchestrate and automate data movement and data transformation at scale.

Azure Synapse Pipelines is the data integration engine embedded within Azure Synapse Analytics. It shares the same underlying technology and user experience as ADF, meaning pipelines, datasets, linked services, triggers, and activities work almost identically. The key difference is that Synapse Pipelines is tightly integrated with other Synapse components (dedicated SQL pools, serverless SQL pools, Spark pools), whereas ADF is a standalone service.

Key Concepts

1. Pipelines
A pipeline is a logical grouping of activities that together perform a task. For example, a pipeline might copy data from an on-premises SQL Server to Azure Data Lake Storage Gen2, then trigger a Spark notebook to transform the data.

2. Activities
Activities are the individual steps within a pipeline. There are three major categories:
- Data movement activities: The Copy Activity is the most important. It moves data between supported source and sink data stores.
- Data transformation activities: These include Data Flow, Spark notebooks, stored procedures, HDInsight activities, and more.
- Control flow activities: ForEach, If Condition, Until, Wait, Web, Lookup, Get Metadata, Set Variable, Execute Pipeline, and Switch.

3. Datasets
A dataset is a named view of data that points to the data you want to use in your activities. It references a linked service and specifies the data structure (schema, format, location).

4. Linked Services
Linked services define the connection information needed to connect to external resources (e.g., Azure Blob Storage, Azure SQL Database, on-premises SQL Server). Think of them as connection strings.

5. Integration Runtimes (IR)
The Integration Runtime is the compute infrastructure used by pipelines. There are three types:
- Azure IR: For cloud-to-cloud data movement and transformation. Supports auto-resolve region or fixed region.
- Self-hosted IR: For connecting to on-premises or private network data sources. You install this on a machine within the network.
- Azure-SSIS IR: For running SSIS packages natively in the cloud.

6. Triggers
Triggers determine when a pipeline execution is fired:
- Schedule trigger: Fires on a wall-clock schedule (e.g., every day at 2 AM).
- Tumbling window trigger: Fires at periodic intervals from a specified start time; supports backfill, dependencies between triggers, and retry policies.
- Event-based trigger: Fires in response to blob creation or deletion events in Azure Storage (e.g., a new file landing in a container).
- Custom event trigger: Fires based on Azure Event Grid custom events.

7. Mapping Data Flows
Mapping Data Flows provide a visual, code-free environment for building data transformation logic that runs on Spark clusters. They are useful for complex transformations such as joins, aggregations, pivots, and conditional splits. Data flows are executed as activities within a pipeline.

How Data Ingestion Works – Step by Step

Step 1: Define Source and Sink
You configure linked services for both the source (e.g., an on-premises Oracle database using a Self-hosted IR) and the sink (e.g., Azure Data Lake Storage Gen2 using the Azure IR). Then you create datasets pointing to the specific tables, files, or folders.

Step 2: Create a Pipeline with Copy Activity
The Copy Activity is configured with:
- Source settings: Query type (table, query, or stored procedure), partitioning options for parallel reads, watermark columns for incremental loads.
- Sink settings: File format (Parquet, CSV, JSON, Delta, Avro, ORC), folder structure, write behavior (overwrite, append, merge).
- Performance tuning: Data Integration Units (DIUs), parallel copy degree, staging (for scenarios requiring PolyBase or COPY command).

Step 3: Handle Incremental Loads
Full loads are expensive. For incremental ingestion, common patterns include:
- Watermark pattern: Use a Lookup activity to get the last loaded timestamp, then use it in the Copy Activity's source query to fetch only new/changed rows. After successful copy, update the watermark.
- Change Data Capture (CDC): ADF and Synapse Pipelines support native CDC connectors for certain sources, allowing you to capture inserts, updates, and deletes.
- Tumbling window triggers with dependencies: Ensure ordered, reliable processing of time-sliced data.

Step 4: Orchestrate with Control Flow
Use control flow activities to build robust pipelines:
- Lookup + ForEach: Dynamically iterate over a list of tables to copy.
- If Condition: Branch logic based on metadata or previous activity output.
- Execute Pipeline: Call child pipelines for modularity.
- Get Metadata: Check if a file exists before processing.

Step 5: Parameterize and Use Expressions
Pipelines, datasets, and linked services can all be parameterized. Dynamic expressions use the expression language (similar to Azure Logic Apps) to set values at runtime. For example, you can construct file paths with @concat('data/', formatDateTime(utcnow(), 'yyyy/MM/dd')) for date-partitioned folder structures.

Step 6: Configure Triggers
Attach the appropriate trigger type. For event-driven ingestion, use a blob-created event trigger so the pipeline runs immediately when new files arrive. For batch ingestion on a schedule, use schedule or tumbling window triggers.

Step 7: Monitor and Troubleshoot
Both ADF and Synapse provide a monitoring hub showing pipeline runs, activity runs, trigger runs, and Integration Runtime status. You can also:
- Send alerts to Azure Monitor.
- Write logs to Log Analytics for long-term analysis.
- Use diagnostic settings for detailed execution telemetry.

Key Differences Between ADF and Synapse Pipelines

- Synapse Pipelines are part of the unified Synapse workspace; ADF is a standalone service.
- Synapse Pipelines have native access to Synapse SQL pools, Spark pools, and the Synapse workspace without needing extra linked services.
- ADF supports managed VNET and managed private endpoints, and so does Synapse (via Managed Workspace VNET).
- ADF supports SSIS IR; Synapse Pipelines also supports SSIS IR as of recent updates.
- For exam purposes, the configuration and concepts are nearly identical.

Common Ingestion Scenarios for the Exam

1. Copying data from on-premises to Azure Data Lake: Requires Self-hosted IR, linked service to on-premises source, and Azure IR for the sink.
2. Loading data into a dedicated SQL pool: Use the COPY statement or PolyBase via staging in ADLS Gen2. Enable staging in the Copy Activity's sink settings.
3. Ingesting data from multiple tables dynamically: Use Lookup + ForEach + parameterized Copy Activity.
4. Incremental load from a relational database: Watermark column approach with a control table to track the high-water mark.
5. Processing files as they arrive: Event-based trigger on Blob Storage or ADLS Gen2.
6. Ingesting data from REST APIs: Use REST or HTTP linked services with pagination rules in the Copy Activity source.

Performance Optimization Tips

- Increase Data Integration Units (DIUs) for cloud-to-cloud copies (default is auto; max is 256).
- Use parallel copy settings to increase throughput from partitioned sources.
- Use Parquet or Delta format as the sink for optimal downstream query performance.
- Enable staging when loading into Synapse dedicated SQL pools for best performance.
- Partition source data and use dynamic partitioning in Copy Activity for large tables.
- For Mapping Data Flows, configure appropriate Spark cluster sizes and use partitioning strategies to avoid data skew.

Security Considerations

- Use Managed Identity for authentication to Azure services wherever possible.
- Store credentials in Azure Key Vault and reference them from linked services.
- Use Managed Private Endpoints and Managed VNET to ensure data does not traverse the public internet.
- Self-hosted IR communicates outbound only (port 443); no inbound firewall rules needed.

---

Exam Tips: Answering Questions on Data Ingestion with Synapse Pipelines and Data Factory

Tip 1: Know When to Use Self-hosted IR vs. Azure IR
If the question mentions on-premises data, private networks, or data behind a firewall, the answer almost always involves a Self-hosted Integration Runtime. For cloud-to-cloud movement, Azure IR is sufficient. If managed private endpoints are mentioned, look for Managed VNET IR.

Tip 2: Understand the Copy Activity Deeply
Many questions revolve around configuring the Copy Activity correctly. Pay attention to source partitioning, sink write behavior (upsert, append, overwrite), staging requirements for dedicated SQL pools, and format settings.

Tip 3: Incremental Load = Watermark Pattern
When a question asks about loading only new or changed data, think watermark column. The pattern is: Lookup (get last watermark) → Copy Activity (filter using watermark) → Stored Procedure or script (update watermark). CDC may also be mentioned for supported sources.

Tip 4: Event-based Triggers for Real-time File Arrival
If a scenario says "process files as soon as they arrive," select the event-based trigger (storage events). Do not confuse this with schedule or tumbling window triggers.

Tip 5: Tumbling Window for Backfill and Dependencies
Tumbling window triggers are the only trigger type that supports backfill (reprocessing past time windows) and trigger-level dependencies. If the question involves reprocessing historical data or chaining triggers, tumbling window is the answer.

Tip 6: Dynamic Pipelines = Lookup + ForEach + Parameters
When asked to process multiple tables or files dynamically without creating separate pipelines, the answer involves the Lookup activity to retrieve a list, ForEach to iterate, and parameterized datasets/activities inside the loop.

Tip 7: Know the Difference Between ADF and Synapse Pipelines
If the question specifies a Synapse workspace, choose Synapse Pipelines. If it's about a standalone data integration requirement or mentions ADF explicitly, choose Data Factory. Functionally they are almost identical, but context matters.

Tip 8: PolyBase/COPY Command for Dedicated SQL Pool Loading
When loading into a Synapse dedicated SQL pool, the COPY statement (or PolyBase) is the most performant method. In Copy Activity, this is configured via the staging option with an ADLS Gen2 staging area. Look for this in performance-related questions.

Tip 9: Managed Identity and Key Vault for Security
For security-focused questions, always prefer Managed Identity over connection strings or passwords. If secrets must be stored, use Azure Key Vault linked service.

Tip 10: Monitor and Retry
Know that pipelines can be monitored via the Monitor hub in Synapse/ADF, and that activities support retry policies and timeout configurations. Alerts can be configured through Azure Monitor. If a question asks about handling transient failures, look for retry settings on the activity level.

Tip 11: Mapping Data Flows vs. Copy Activity
Copy Activity is for data movement (ETL's E and L). Mapping Data Flows are for code-free transformations (the T). If the question involves complex joins, aggregations, or transformations without code, Mapping Data Flows is the answer. If it's purely about moving data, Copy Activity is correct.

Tip 12: Read the Question for Cost and Performance Clues
Questions may hint at cost optimization (use auto-resolve Azure IR, right-size DIUs) or performance (increase parallelism, use staging, choose columnar formats). Match the solution to the stated priority.