Ingest and Transform Data

Dataflows Gen2 is a low-code ETL tool built on Power Query Online. Its workflow is **Connect → Transform → Land**. Use it when transformation is the main requirement and the team benefits from a visual, reusable process.

Core capabilities

• Connect to supported cloud, database, file, and Fabric sources.
• Transform data with the Power Query interface or the Advanced Editor using M.
• Land results in a Lakehouse, Warehouse, or SQL database.
• Use Duplicate or Reference queries when several outputs share one source.
• Preserve query folding where possible so supported transformations execute at the source.

Important boundary

Dataflows Gen2 prepares and moves data; it is not a replacement for long-term warehouse storage or row-level security. A Fabric capacity workspace is required.

A pipeline orchestrates a sequence of activities. It is the manager around workers such as Dataflows Gen2, notebooks, scripts, and Copy Data.

Common activities

• Copy Data: move data from a source to a destination, usually with minimal transformation.
• Dataflow Gen2: run visual Power Query transformations.
• Notebook or stored procedure: run code-heavy Spark or SQL logic.
• Get Metadata: inspect files or folders before processing them.
• Control flow: use dependencies, If Condition, For Each, parameters, variables, schedules, and event-based triggers.

Choose the tool deliberately

• Choose Copy Data when data should move as-is or transformation happens later.
• Choose Dataflow Gen2 when visual cleaning, filtering, joins, or reusable Power Query logic is needed.
• Choose a pipeline when scheduling, sequencing, retries, parameters, or monitoring are central.

Every pipeline run has a run ID and status that can be used for monitoring and troubleshooting.

Use a full load when rebuilding the target is simple and acceptable. Use an incremental load when only new or changed records should be processed; this requires reliable change detection.

Prepare data for analytics

• Land source-shaped data in a staging or Bronze area.
• Clean, deduplicate, validate, and conform data in Silver.
• Aggregate or model business-ready data in Gold.
• Handle missing, duplicate, and late-arriving records explicitly.

Streaming choices

• Use Eventstreams to transform and route events while they are in motion.
• Use Spark Structured Streaming for code-based streaming transformations.
• Use KQL when the workload is optimized for real-time event analysis and time windows.
• Choose native real-time tables or OneLake shortcuts according to whether the data should be copied or accessed in place.