The decision is about ownership, not which editor is easier
A sales analyst combines CRM opportunities with a spreadsheet of territories in Power Query. It works, so the team publishes a dataflow and connects three Power BI reports. Six months later, finance needs the same customer hierarchy, operations needs daily snapshots, and nobody can explain why the pipeline report and the board report disagree. The original transformation was useful; its location and ownership were never designed for enterprise reuse.
The question is not whether Power BI dataflows or a warehouse can clean data. Both can. The question is where a transformation should become an authoritative business rule, who should maintain it, and which other systems must be able to consume its output.
The practical answer is usually a hybrid architecture: use dataflows for approachable ingestion, shaping and controlled self-service; use warehouse or lakehouse transformations for durable, cross-domain business logic, history, testing and reusable data products; keep presentation-specific calculations in the semantic model. The dividing line should follow business criticality and reuse rather than a blanket preference for SQL or Power Query.
First, distinguish three technologies that often get called ‘dataflows’
Power BI Dataflow Gen1
Gen1 is a workspace-managed Power Query data-preparation asset that produces reusable tables for downstream Power BI and other supported consumers. It helped teams centralize repeated Power Query work outside individual reports. Microsoft now classifies Gen1 as legacy and says it will not receive new feature investment. Existing Gen1 workloads can continue to operate, and the migration decision depends on licensing and dependencies rather than an invented universal shutdown date.
Microsoft Fabric Dataflow Gen2
Dataflow Gen2 is part of Fabric Data Factory. It retains the low-code Power Query experience while supporting destinations such as Fabric Lakehouse and Warehouse, Azure SQL and other supported stores, as well as pipeline orchestration, monitoring and CI/CD integration. It is not simply Gen1 with a new name: storage, refresh behavior, consumption billing and downstream access patterns can differ. Microsoft’s Dataflow Gen2 overview documents the current destination and feature model.
Warehouse or lakehouse transformations
Warehouse transformations are usually SQL or code-based processes that turn landed source data into standardized staging tables, conformed dimensions, fact tables and business marts. They may run in Fabric Warehouse, Snowflake, BigQuery, Redshift, SQL Server or another platform. A lakehouse may use SQL, Spark or a transformation framework. The defining feature is not a particular vendor: it is a managed, persistent analytical data layer with explicit contracts and multiple consumers.
A Power BI semantic model is a fourth, distinct layer. It defines relationships, measures, security and reporting semantics. It should not become an accidental substitute for source-system normalization or enterprise history. Likewise, a dataflow writing into a warehouse does not automatically make its transformation logic warehouse-governed; governance depends on ownership, tests and change controls.

A practical comparison across seven decisions
1. Who will reuse the result?
For a one-off departmental report, a dataflow may be enough. If several reports need a common, relatively stable cleaned table, a shared dataflow can reduce duplication. If Power BI, an operational application, finance exports, machine learning and a second BI platform all need the same customer or order definitions, publish a governed warehouse table or data product. A transformation that lives only in a BI workspace creates avoidable coupling for non-BI consumers.
Ask whether the consumer needs raw records, standardized attributes, curated business entities or a final KPI. A single shared customer dimension is a better warehouse candidate than a report-specific sort order. Conversely, a report’s display label or visual grouping rarely justifies a new enterprise table.
2. How consequential is a mistake?
A local campaign experiment can tolerate faster iteration and lighter review. A revenue-recognition rule, regulated field classification or executive headcount measure requires traceable approval and regression testing. The greater the consequence of a wrong number, the stronger the case for central ownership, versioned definitions and automated reconciliation.
The location alone does not guarantee control. A warehouse full of undocumented SQL is no safer than an unmanaged dataflow. What matters is a named owner, documented inputs, expected grain, tests, deployment review and a record of exceptions.
3. How much data and how often?
Small-to-moderate datasets with modest refresh frequency can be practical in Power Query. Larger fact tables, high-frequency updates, complex joins and long historical windows deserve benchmarking in the engine that will execute them. Source-side query folding can make Power Query efficient by pushing eligible transformations into the source. If folding breaks early, a seemingly simple step may force expensive data movement and local processing.
Do not assume that Dataflow Gen2 is always faster or cheaper than SQL, or that a warehouse is automatically cheaper. Compare representative production volumes, refresh frequency, source query load, staging overhead, Fabric Capacity Units, storage and downstream semantic-model refresh. A short successful pilot on ten thousand rows says little about a billion-row fact table.
4. Does the business need historical truth?
A dataflow that overwrites the latest customer state may be suitable for current-state reporting. Historical reporting is harder. If customers change regions, products change categories, orders are corrected or records are deleted, a warehouse design can explicitly maintain effective dates, change capture, snapshots and slowly changing dimensions.
History is a business decision, not merely a storage option. Define whether last quarter’s sales should be attributed to the region at the time of sale or the customer’s current region. Document how late-arriving changes and restatements affect published metrics before choosing a transformation tool.
5. Who can change the logic?
Dataflows make preparation accessible to analysts familiar with Power Query. That is a strength when teams need rapid prototyping and domain knowledge is close to the data. Warehouse transformations often suit engineering-led development with code review, automated tests and controlled promotion across environments. Fabric Dataflow Gen2 now supports CI/CD and Git integration, narrowing the tooling gap, but disciplined review and test coverage still have to be designed.
A healthy model is not ‘analysts cannot touch data’ or ‘every analyst can redefine revenue.’ Allow analysts to prototype and propose changes. Promote shared definitions into governed assets once other teams rely on them. Give each asset a technical maintainer and a business definition owner.
6. What are the security boundaries?
Transformation location affects credentials, workspace permissions, gateway access, row-level exposure and where sensitive data is copied. A dataflow may materialize columns that a report later hides; hiding them in a visual is not a security control. A warehouse may offer central policies, but downstream exports and BI caches still need scrutiny.
Map data classifications at ingestion, intermediate storage, destination tables and semantic models. Confirm whether service identities have least privilege, whether refresh credentials are personal or managed, and whether a user can retrieve sensitive columns outside the intended report. Apply row and column controls at the appropriate enforcement point and test with actual consumer identities.
7. Who pays for the workload?
Cost should be measured per usable data product, not only per transformation run. Dataflow Gen2 uses Fabric capacity consumption across engines such as standard compute, high-scale compute and fast copy. Warehouse transformations may consume query compute, scheduled capacity, storage and orchestration. Additional copies and repeated semantic-model imports can outweigh savings in one layer.
Build a cost view that includes ingestion, transformation, storage, refresh, retries, gateway infrastructure and support effort. Microsoft’s Dataflow Gen2 pricing guidance explains how to inspect Capacity Units and refresh history; use current tenant billing rather than old blog examples to estimate production costs.
An architecture pattern that keeps each layer honest
A robust analytics flow can be described as: operational sources and APIs → landing or raw data → standardized staging → governed business marts → semantic model → dashboards and downstream applications. Dataflow Gen2 may participate in ingestion or preparation and write into a lakehouse or warehouse. SQL or another managed transformation framework can then enforce durable business rules. Not every organization needs every layer, but each responsibility should have a deliberate home.
Landing: preserve enough source evidence
Capture stable identifiers, source update timestamps, extraction time, relevant status fields and delete signals where available. Keep raw or minimally transformed records when reproducibility and audit needs justify them. Avoid discarding the only source field that would later explain a reconciliation difference.
Standardization: normalize without inventing business meaning
Normalize types, time zones, source codes and keys. Apply consistent null handling and basic quality checks. A dataflow can be effective for a supported API or file connector, especially when the Power Query interface accelerates the work. For heavily reused source entities, centralize the resulting standardized tables so every report does not repeat the same preparation.
Business marts: define metrics at the right grain
A sales fact should have an explicit grain, such as one row per order line, with documented treatment of refunds, taxes and currency. A customer dimension should define uniqueness and history. The semantic model can then expose approved measures and security, while visuals focus on decisions rather than hidden data cleaning.
This is consistent with Actiknow’s BI consulting and implementation services, which cover integrating databases and APIs, solution architecture, data modeling and reporting. The service description is relevant to the design decision; it is not evidence that any particular customer architecture has been deployed.

Worked example: the customer revenue metric
Consider an illustrative company with an e-commerce platform, CRM and billing system. Marketing wants revenue by campaign, finance wants recognized revenue by legal entity, and sales wants revenue by account owner. A naive implementation builds three dataflows, each joining its preferred tables and calculating ‘revenue’ differently.
A more controlled design begins with a source-to-target map. Landing tables retain order IDs, invoice IDs, credit notes, currency, posting dates and update timestamps. Standardization resolves customer identifiers and validates currencies and statuses. A finance-approved fact table defines net revenue at the agreed grain. A customer dimension maps CRM accounts to billing entities with documented exceptions.
The Power BI semantic model references the curated tables and exposes measures such as net revenue, invoiced amount and order value with distinct names. Campaign attribution can remain a separate analytical model because it answers a different question. Department-specific filters and visuals stay near the report; canonical revenue logic does not.

Illustrative reconciliation checks
For a daily invoice fact, compare source invoice count and distinct invoice IDs against the modeled table for the same posting window. Compare net amounts by currency and legal entity before any exchange-rate conversion. Check duplicate business keys, unmapped statuses, orphan customer references and late changes. Record every unexplained material variance with an owner and resolution.
A basic SQL-style validation might be: SELECT posting_date, currency_code, COUNT(DISTINCT invoice_id) AS invoices, SUM(net_amount) AS net_amount FROM mart_invoice GROUP BY posting_date, currency_code; Run an equivalent source query against the same cutoff and business definition. This is a template, not executable code for every warehouse: adjust identifiers, null handling, tax treatment and rounding for the actual system.
For a more detailed approach to source-to-warehouse checks, Actiknow’s guide to data warehouse reconciliation covers keys, amounts, deletes, historical dimensions and signed exceptions. The important principle is that passing a refresh does not prove the metric is correct.
When dataflows are the right answer
Choose a dataflow when an analyst needs to combine a manageable number of sources quickly; the output serves a small, known set of consumers; Power Query connectors and transformations fit the job; refresh windows are forgiving; and a named owner can maintain it. It is especially useful as a prototype or a reusable preparation layer for a BI-focused team.
A dataflow can also be a production component when it is governed, tested, monitored and cost-effective. The distinction is not ‘dataflows for amateurs, SQL for professionals.’ It is whether the execution and ownership model meets the workload’s needs.
When warehouse transformations are the better answer
Prefer a governed warehouse or lakehouse transformation when multiple domains need the same entities, the output feeds systems beyond Power BI, historical correctness matters, the volume requires efficient set-based processing, or changes need formal review. A centralized layer also makes it easier to expose consistent business keys, test relationships and reconcile source-to-target control totals.
A warehouse is not a reason to move every Power Query step into SQL. Preserve low-risk presentation shaping close to the consuming report. Centralize the logic whose duplication would create material inconsistency.
The migration trap: Gen1 to Gen2 is not the same as moving logic to the warehouse
Microsoft recommends Dataflow Gen2 for new work where appropriate and identifies Gen1 as legacy, but moving from Gen1 to Gen2 does not answer where authoritative transformation rules belong. A migrated dataflow may still contain a hidden business definition duplicated across workspaces. Audit the logic before selecting a destination.
Inventory each Gen1 dataflow’s inputs, transformations, linked consumers, refresh schedules, gateway, credentials and dependent semantic models. Identify which steps are connector-specific ingestion, which are standardized source preparation, and which define business KPIs. Keep or rebuild each step in the layer that best fits its ownership and reuse.
Microsoft’s Gen1-to-Gen2 migration guidance describes feature differences and upgrade options. Review licensing, pipeline integration, destination compatibility, API dependencies, security and measured capacity consumption before cutover. Do not promise a no-risk in-place upgrade or an automatic performance improvement.
A scoring model executives can use
Score each candidate transformation from 1 (low) to 5 (high) on six dimensions: number of independent consumers, financial or operational criticality, historical complexity, volume and refresh pressure, security sensitivity, and required change-control rigor. This is a prioritization aid, not a vendor benchmark.
A total near the low end suggests keeping the work close to a dataflow or report, subject to specific constraints. A high score suggests a governed shared layer. Do not let the arithmetic override a single critical requirement: for example, a sensitive regulated dataset may require central control even when only one report consumes it.
Add two override questions. First, does the transformation need to be consumed outside Power BI? Second, would a different result create an audit, customer or financial problem? A yes to either merits an architecture review regardless of the score.

Four-week implementation plan
Week 1: map what already exists
List dataflows, warehouse jobs, Power Query steps in semantic models, refresh schedules, owners and downstream reports. Identify duplicated joins, repeated mapping tables and competing KPI definitions. Capture a baseline for refresh time, failure frequency, query cost and reconciliation exceptions.
Week 2: classify and design
Classify transformations as ingestion, standardization, business rule, semantic measure or presentation shaping. Select one high-value shared entity, such as customer or invoice, and define its grain, keys, freshness, history and access policy. Document which tool owns each step and how failures will surface to consumers.
Week 3: build and compare
Implement the proposed flow in a nonproduction environment. Run existing and new outputs in parallel using the same source cutoff. Test row counts, distinct keys, numeric control totals, deletes, late updates, access permissions and representative refresh loads. Benchmark Dataflow Gen2 Capacity Units against warehouse compute and storage where both are viable.
Week 4: cut over with controls
Promote the validated data product, update dependent semantic models and monitor the first scheduled runs. Keep a rollback path and a record of accepted differences. Assign ownership for changes and incidents, publish a data dictionary, and retire duplicated logic only after consumers have confirmed parity.
Production acceptance criteria
Before declaring success, require: documented grain and KPI definitions; an inventory of downstream consumers; reproducible source-to-target checks; verified handling of inserts, updates and deletes; approved security for sensitive fields; freshness thresholds with alerting; a measured capacity and cost baseline; versioned change approval; and a rollback procedure. For critical data, business owners should sign off on metric behavior, not just the technical team.
Monitor the complete path, including source arrival, transformation completion, semantic-model refresh and dashboard availability. Actiknow’s data pipeline observability article explains why job success alone is an incomplete measure of data trust.

Frequently asked questions
Are Power BI dataflows a replacement for a data warehouse?
Not generally. Dataflows prepare and distribute data, and Gen2 can load warehouse destinations, but they do not automatically provide enterprise modeling, historical strategy, cross-system contracts or governance. A small BI-only environment may need no separate warehouse; a multi-domain enterprise often benefits from one.
Should every Power Query transformation be moved into SQL?
No. Keep local presentation logic and low-risk shaping near the report when that is simpler. Move shared, consequential and historically sensitive transformations into governed data products. Decide by ownership and reuse, not language preference.
Is Dataflow Gen1 being discontinued immediately?
Microsoft labels Gen1 legacy and is not adding new features. Existing workloads remain supported under current guidance. Check Microsoft’s current licensing and migration notices rather than assuming a universal retirement date.
Does Dataflow Gen2 require Fabric capacity?
Microsoft documents Fabric capacity, trial capacity or Power BI Premium capacity as supported capacity options. Eligibility, workspace configuration and licensing can differ by tenant, so validate your environment before planning a migration.
Is Dataflow Gen2 faster than warehouse SQL?
There is no universal winner. Query folding, connector behavior, staging, data volume, transformation type, concurrency and destination all matter. Compare equivalent outputs on production-like data and measure both elapsed time and resource consumption.
Where should KPI calculations live?
Put reusable row-level business transformations and conformed facts in the governed data layer. Put measures that respond to filter context in the semantic model when appropriate. Document the definition and prevent competing versions from emerging in separate reports.
Can dataflows be governed and tested?
Yes. Use controlled workspaces, named owners, Git and deployment practices where supported, validation queries, refresh monitoring and reconciliation. The key is to implement those controls rather than assuming the tool supplies them automatically.
How do we prevent refreshes from overloading source systems?
Inventory concurrent refreshes, benchmark source queries, preserve query folding where practical, consider incremental processing and centralize shared extraction when it reduces repeated scans. Coordinate refresh windows with source-system owners and monitor upstream load.
What should be migrated first?
Start with a transformation that is both duplicated and business-critical, but small enough to validate within a pilot. A shared customer mapping or revenue fact usually offers a clearer test of governance than a cosmetic report-specific step.
How do we know the new architecture is better?
Measure fewer conflicting definitions, fewer duplicate transformations, faster issue diagnosis, reliable refresh against a stated SLA, reconciled business totals and acceptable total operating cost. A prettier architecture diagram is not an outcome.
The bottom line
Power BI dataflows are valuable because they make preparation accessible. Warehouses are valuable because they make shared analytical data durable, testable and broadly reusable. The best design uses each for the work it is suited to, with explicit responsibility for business definitions and production controls.
If your team is deciding whether to keep transformations in Power Query, move them into Fabric or a cloud warehouse, or simplify overlapping BI pipelines, Actiknow can help assess the architecture and delivery tradeoffs through its BI consulting services. Contact Actiknow to discuss the specific sources, consumers and reporting risks involved.
Sources and product documentation
- Microsoft: Dataflow Gen2 overview and feature comparison.
- Microsoft: Migration guidance for Dataflow Gen1 to Gen2.
- Microsoft: Dataflow Gen2 pricing and capacity consumption.

