1. Choose Dataflow Gen2 for the transformation, not the orchestration

A dataflow defines queries and their outputs. A pipeline coordinates when activities run, how dependencies are handled and what happens after a failure. A notebook is another transformation option when code, custom libraries or explicit processing logic is the better fit.

New Dataflow Gen2 items include CI/CD and Git integration support by default. Since April 2026, the non-CI/CD Classic creation option is unavailable; existing Classic items continue to work and can be converted using Save As. Do not migrate a working Gen1 flow without checking its connector, refresh and downstream consumption requirements.

Before the exercise: use an eligible practice workspace/capacity, permission to create the dataflow, and approved connections with read access to the source and write access to the destination. Private sources may need a supported on-premises or VNet gateway. Creating a connection is not the same as granting source access.

2. Reuse the Lakehouse lesson's five-row input

Complete the linked Lakehouse lesson first. Its dbo.uig_bronze_orders table contains five string-valued rows: three valid orders, one exact duplicate and one negative amount. This path reads that existing table; you do not need to upload another copy or overwrite the notebook outputs.

1. Create a Dataflow Gen2 named uig_orders_powerquery in your practice workspace.
2. Select the Lakehouse connector and browse to the producer Lakehouse and dbo.uig_bronze_orders. Confirm the actual schema/name exposed by the navigator. Use the existing approved connection.
3. Name the input query orders_input. Confirm five rows and the columns order_id, region and amount. Preserve the source table; do not attach an output to this query.
4. Set order_id to Whole number, trim and uppercase region, and set amount to Fixed decimal number. Use the appropriate locale for the sample’s decimal point. Inspect conversion errors rather than replacing them silently with zero.
5. Create a reference query named orders_rejected. Retain the sample’s row where amount is negative. In this known sample, it is order 1004 with -10.00. For real data, explicitly handle nulls, invalid IDs, unknown regions and conversion errors as well.
6. Create a second reference named orders_valid. Filter to positive order IDs, UK/US regions and nonnegative amounts. Select all three columns and remove exact duplicate rows. The result must contain orders 1001, 1002 and 1003.
7. Create a reference from orders_valid named sales_by_region. Group by region and calculate Sum of amount as total_amount.

Do not remove duplicates only on order ID and assume the result is correct. Two different rows for one ID require an explicit conflict-resolution rule; keeping an arbitrary row can hide a source defect. Preview profiling can be sample-based, so validate the persisted output too.

3. Configure outputs and make reruns predictable

Give only the three output queries explicit Lakehouse table destinations. Use separate demo tables so the notebook lesson remains intact:

Check the destination schema and column mappings in the wizard. For this snapshot-style exercise, use Replace, not Append. Append would add the same data again on a rerun. Disable load/staging for an intermediate query only where the editor allows it and its dependents do not require staged results; do not delete the dataflow’s managed staging items.

Automatic destination settings can manage mapping and drop/recreate a table. Manual fixed-schema settings offer different behavior and can fail publishing when the query shape changes. Understand what happens to dependent objects before choosing either. Replace is destructive to the selected destination data: use only the demo tables, never an existing business table.

1. Publish and wait for destination validation. Run a refresh, inspect refresh history, and verify all three tables after completion.
2. Run the same refresh again without changing Bronze. Counts and totals should remain unchanged.
3. In a practice copy, break a known mapping or conversion. Confirm that validation/refresh reports the error, inspect the failing query and restore it before retrying.

Multiple outputs are not one atomic publication. One table can change before another query fails. Production flows need a validation/promotion design appropriate to their consumers; a green activity status alone is not a data-quality gate.

4. Fast Copy and staging: inspect the engine that actually ran

Fast Copy uses the pipeline Copy Activity backend for eligible ingestion. Current guidance includes connectors such as ADLS Gen2, Blob, Azure SQL, Lakehouse, Warehouse, PostgreSQL, on-premises SQL Server, Oracle, Snowflake and Fabric SQL database. That does not mean every authentication, gateway, transformation and destination combination is supported.

The usual automatic thresholds are at least 100 MB for eligible CSV/Parquet files in ADLS/Blob, or five million source rows for databases. Require fast copy can bypass the volume threshold but makes refresh fail if the query cannot use that engine. The five-row lesson is not a meaningful ingestion benchmark.

• Check the query’s Fast Copy indicators and the refresh-history Engine value. A completed flow does not prove Fast Copy ran.
• For files, the supported ingestion transformations include combining files, column selection/removal/renaming and type changes. Grouping and richer transformations can require a separate referencing query.
• Direct Fast Copy output is currently a Lakehouse destination. For another destination, stage the ingestion and reference it in a later query.
• Do not repeat the old blanket statement that on-premises sources are unsupported: on-premises SQL Server is in the current connector list. Check the exact gateway scenario instead.

Staging can enable scalable SQL compute and reuse. Same-region placement alone does not make disabling staging safe or beneficial. Compare execution plans, duration, capacity consumption and correctness for the actual query graph. There is no verified universal 8x speedup, 90% saving or additive cost waterfall for your workload.

5. Incremental refresh replaces buckets—not business-key rows

The five-row exercise deliberately uses a full snapshot replacement. For a larger date-based source, Dataflow Gen2 incremental refresh partitions the configured range into time buckets and evaluates a change-detection maximum. A changed bucket is retrieved and replaced; an unchanged bucket can be skipped.

This is not an automatic upsert on your chosen business keys. It does not by itself solve duplicate IDs, SCD history, arbitrary deletes or changes outside the selected window.

Current limits include 50 buckets per query and 150 across a dataflow. Configure the destination before the first incremental refresh. Test a new row, an update, a delete and an out-of-window change against your intended rules; do not infer correctness just from a faster run. For small inputs, a full refresh can be simpler and cheaper.

6. Use supported variables without confusing them with connections

Use Fabric’s variable picker or supported Power Query functions such as Variable.Value to reference a same-workspace Variable Library. An expression such as @{variables('SQL_SOURCE_SERVER')} inside a quoted M string is not a valid general-purpose bridge to Fabric variables.

Dataflow variables are resolved at the start of a run and remain fixed during that operation. They support basic values for logic such as filters; they cannot change the authored connection information or destination schema mappings. A deployment value set does not automatically rebind every source to production.

1. Use CI/CD-enabled dataflows and supported Git/deployment integration.
2. Track query definitions, parameter contracts, output schema and approved connection mappings together.
3. Deploy to Test first; verify connection binding, permissions and destination mappings in that workspace.
4. Run with nonproduction data, compare expected outputs, and check that no development run wrote to a production table.
5. Only then enable the intended schedule or pipeline activity. Do not schedule both independently unless duplicate execution is intentional.

Public parameters and Variable Library values are separate configuration surfaces. Neither is a secret store. Keep credentials in supported connections, limit who can change operational values and make missing configuration fail clearly rather than silently selecting the wrong environment.

7. Monitor correctness before optimizing cost

Record the run ID, source watermark/snapshot, query-level status, execution engine, rows written and validation totals. When a pipeline triggers the dataflow, inspect both the activity output and the dataflow refresh details.

For the exercise, the acceptance test is specific: five Bronze rows remain intact, three valid orders total 245.00, one rejected row is retained, regional totals are 170.00/75.00, and a rerun does not double them. Source conversion errors must remain visible.

Measure actual capacity consumption and source requests for representative refreshes. F-SKU pricing alone cannot derive an exact per-run cost from elapsed time. Gateway, storage, source-system and network costs can also matter; do not advertise a fixed penny-per-run or a made-up benchmark.

Continue with the pipeline lesson if you need orchestration, the notebook lesson for explicit custom validation, or Mirroring if the real need is continuous replication rather than Power Query transformations. These are complementary paths, not a requirement to rebuild the same solution three times.

Common questions

Official references and review scope

Checked against the following official vendor documentation on 2026-10-30. This is an independent learning guide, not a Microsoft certification, benchmark or customer deployment report. These workflows have not been executed in your Fabric tenant. Expected values are teaching acceptance checks; validate feature availability, identity, network, schema and recovery requirements before production use.

• Dataflow Gen2 architecture and current destinations
• Destination mappings, Replace/Append and schema behavior
• Fast Copy connectors, thresholds and limitations
• Incremental refresh bucket replacement and limits
• Variable Library integration and fixed connections
• Dataflow Gen2 CI/CD and Git integration

Take the next practical step

Practice Fabric interview scenarios