DP-700: Implement ingestion pipelines with Dataflows and Synapse

I will teach how to implement data ingestion pipelines in the context of Fabric (Dataflows / Synapse pipelines), a core skill in DP-700. Knowing this helps on the exam and, in practice, ensures data arrives reliably and efficiently to your analytics environment. Here you will find concepts, concrete steps, examples with plausible numbers and production best practices.

What you need to know

Data ingestion is the process of bringing data from sources (files, databases, services) into your storage/processing layer (OneLake / data lake or tabular storage). In Fabric you can use Dataflows (Power Query) for transformations in the ingest path or Synapse pipelines for orchestration and data copy. The essential concepts are:

• Connectors and authentication: know how to configure credentials (Managed Identity, service principal, key) to orchestrate secure copies. For example, for an Azure Blob storage prefer the workspace Managed Identity; for on‑premises sources use a gateway together with a service principal or secure credentials.
• Ingestion modes: full load vs incremental; when to use incremental copy with watermark or CDC (Change Data Capture). For a table of 100M rows, a daily full load is costly — an incremental that copies 1–5% of the data reduces costs and processing time.
• Light vs heavy transformation: use Dataflows/Power Query for cleaning and mapping close to the source (ideal for CSV/Excel files and quality rules), use compute (Spark, Synapse SQL Pools, Mapping Data Flows) for heavy transformations on datasets of tens to hundreds of GB or more.
• Idempotency and reprocessing: design pipelines that can be re-run without duplicating data. Common techniques: use natural keys for deduplication, upsert/merge in the sink, or keep execution logs with watermarks. In production, establish a retention period (e.g., 90 days) to allow reprocessing without accumulating costs.

Simple example: copy a 2 GB CSV file stored in an Azure Blob to a table in Fabric using a copy pipeline. The pipeline needs the Blob connector, column mapping and a failure/retry policy (for example, 3 attempts with exponential backoff). A well-configured parallel copy can reach 100–200 MB/s, reducing ingestion time to tens of seconds/minutes, depending on network and the number of files.

How it works — practical step by step

Below are practical steps to create a simple ingestion pipeline that copies data from a storage to a table in Fabric and performs light transforms with a Dataflow. I include operational suggestions and typical values you can adjust according to your environment.

1. Prepare credentials and linked services: configure a Linked Service for Azure Blob/ADLS using the workspace Managed Identity or a service principal. This avoids placing secrets in pipelines. In production environments, establish credential rotation and audit accesses. Typically, assign least privilege (RBAC) to the identity, for example, read/write only on the required container.

2. Create the Dataflow (Power Query): in Data Factory / Fabric Dataflows create a flow that reads the file, detects the delimiter, sets types and applies cleaning rules (trim, replace nulls, normalize dates). This reduces errors from inconsistent schemas. E.g.: convert a text column to date with a fallback to NULL when the format fails, or apply a deduplication rule by CustomerID keeping the row with the highest timestamp.

   // Exemplo conceptual de transformações Power Query
   Table.ReplaceValue(Source, null, "", Replacer.ReplaceValue, {"CustomerName"})
   Table.TransformColumnTypes(PrevStep, {{"OrderDate", type date}})
   

3. Create the copy pipeline: add a Copy Data activity that uses the Dataflow as source or link the file directly as source and the Fabric table as sink. Configure column mapping, parallelism (Degree of Copy Parallelism) and the pre-copy policy (e.g.: truncate vs append). For regular loads, prefer append + upsert/merge in the sink to avoid downtime windows.

4. Implement incremental ingestion: when possible, use a watermark or modification column to copy only new/changed rows. Configure the source query to filter by timestamp > @pipelineVariable('lastWatermark') and update the variable at the end of execution. For example, a watermark stored in a JSON file or metadata table updated at the end of each run. This reduces the transferred volume for large terabyte-scale loads.

   // Padrão conceptual de filtro incremental
   SELECT * FROM SourceTable WHERE ModifiedAt > @pipeline().parameters.lastWatermark
   

5. Scheduling and monitoring: schedule the pipeline (time trigger — e.g.: every 15 minutes, daily — or event trigger when a file arrives). Enable retry (for example, 3 attempts with 30s, 60s, 120s) and notifications (email/Teams) on failure. Monitor metrics: number of rows, bytes transferred, duration and error rate. Establish SLAs — e.g.: daily ingestion completed within 2 hours for 500 GB datasets.

6. Test idempotency: run the pipeline multiple times with the same input to ensure it does not duplicate data — use natural keys or upsert logic in the sink (MERGE). In OLTP scenarios, a common approach is to apply MERGE by batch_id or ModifiedAt to ensure consistency.

Common mistakes

• Poorly configured credentials: using keys/strings instead of Managed Identity increases risk and causes failures when secrets expire. Always prefer managed identities when possible and audit authentication errors in the log for early detection.
• Carelessness with schemas: assuming all files have the same schema causes failures or corrupts data. Validate schema and handle missing/extra columns is essential. In environments with hundreds of sources, create automatic validation rules that reject files outside expectations to reduce operational risk.
• Full loads when it should be incremental: running full loads indiscriminately increases cost and time; defining watermark/CDC is often the best option for large data. For example, replacing a daily 1 TB full load with an incremental of 20 GB drastically reduces transfer cost and processing time.

How to practice

Practicing with exercises in your Fabric environment is essential. Create labs that simulate files from 100 MB to 5 GB, implement event-based triggers, and test reruns and simulated failures. Use the OFFICIAL Microsoft Practice Assessment (free) to evaluate areas where you need to strengthen knowledge and consult the official Microsoft study guide (free) for the measured topics. Do not use dumps or unofficial exam questions — practice with labs and official resources.

In summary

• Ingestion combines connectors, authentication, modes (full vs incremental) and light/heavy transformation.
• Use Managed Identity and Linked Services for pipeline security and reliability.
• Implement watermark/CDC to reduce cost and time in incremental ingestions; consider retention policies and retention windows for reprocessing.
• Test idempotency and schema validation to avoid duplication and data corruption; monitor metrics and set clear SLAs.