SQL vs Data Factory vs Dataflows in Azure: The Right Tool for Each ETL Step

The Three Players in Your Azure ETL Stack

Azure SQL (and Synapse / Dedicated SQL)

What it is:

• A compute engine for relational data
• Best at set‑based transformations, joins, aggregations
• Can host your curated warehouse / data mart

Strengths:

• Strong for complex joins and aggregations
• Easy to version SQL scripts in Git
• Great for reusable, documented logic (views, stored procedures)
• Predictable performance once tuned

Weaknesses:

• Not ideal for orchestrating external systems
• Limited built‑in connectors compared to Data Factory
• Scaling is more manual (tiers, DTUs, vCores)

Azure Data Factory (ADF)

What it is:

• Orchestration + data movement + some transformation (Mapping Data Flows)
• Connects to on‑prem and cloud sources

Strengths:

• Rich set of connectors (SaaS, files, databases)
• Scheduling, monitoring, retry logic, alerts
• Good for batch pipelines and dependency management

Weaknesses:

• Transformations in Mapping Data Flows are visual but can become opaque
• Harder to diff/merge complex flows than SQL code
• Debugging can be slower than running SQL locally

Power BI Dataflows

What it is:

• Power Query (M) running in the cloud
• Creates reusable, curated tables for Power BI datasets

Strengths:

• Great for self‑service modeling and lightweight ETL
• Reuse across multiple Power BI reports
• Ideal for semantic reshaping and business logic close to the report

Weaknesses:

• Not a replacement for a warehouse
• Performance can degrade with heavy transformations on large volumes
• Governance is harder at enterprise scale if every team builds their own

Think in Layers: Landing, Staging, Warehouse, Semantic

Instead of asking “SQL or Data Factory or Dataflows?”, design around layers. Then assign the right tool to each layer.

Typical layers in an Azure BI stack:

1. Landing – raw data as‑is from source systems
2. Staging – lightly cleaned, standardized, still close to source
3. Warehouse / Data Marts – conformed, integrated, historical model
4. Semantic / Reporting – business-friendly tables, measures, calculations

Recommended default mapping

• Landing: Data Factory
• Staging: Data Factory + SQL
• Warehouse / Data Marts: SQL
• Semantic / Reporting: Power BI Dataflows (and DAX in datasets)

This isn’t rigid, but it gives you a safe default. Deviate only with a reason.

Where SQL Should Own the Logic

Use SQL when you have to:

• Integrate multiple sources into conformed dimensions/facts
• Apply complex business rules that must be auditable
• Handle slowly changing dimensions (SCDs)
• Build reusable data marts for multiple reports/tools

Example: Building a Sales Fact Table in Azure SQL

Assume you’ve landed data into stgSales, stgCustomer, stgProduct. Your warehouse schema might look like this:

-- DimCustomer
CREATE OR ALTER VIEW dw.DimCustomer AS
SELECT
    c.CustomerKey,
    c.CustomerID,
    c.CustomerName,
    c.Region,
    c.Segment,
    c.IsActive,
    c.ValidFrom,
    c.ValidTo
FROM stg.CustomerSCD c;

-- FactSales
CREATE OR ALTER VIEW dw.FactSales AS
SELECT
    s.SalesKey,
    s.SalesDate,
    c.CustomerKey,
    p.ProductKey,
    s.Quantity,
    s.NetAmount,
    s.DiscountAmount,
    s.CurrencyCode
FROM stg.Sales s
JOIN dw.DimCustomer c
    ON s.CustomerID = c.CustomerID
    AND s.SalesDate BETWEEN c.ValidFrom AND c.ValidTo
JOIN dw.DimProduct p
    ON s.ProductID = p.ProductID;

Why SQL here?

• Time‑variant joins (using ValidFrom/ValidTo) are easier to reason about in SQL
• You can optimize with indexes, statistics, partitions
• BI tools beyond Power BI (SSRS, Excel, external apps) can reuse the same logic

When not to use SQL

Avoid pushing tasks to SQL that are better handled upstream:

• Copying data from 10 different SaaS APIs
• File handling (unzipping, renaming, moving)
• Triggering external processes or notifications

For those, let Data Factory do the orchestration.

Where Azure Data Factory Adds Real Value

Use Azure Data Factory as the conductor of your ETL orchestra.

Typical ADF responsibilities:

1. Data ingestion
   • Copy from on‑prem SQL / Oracle / SAP into Azure
   • Pull from REST APIs, Blob, ADLS, SFTP
2. Orchestration
   • Run SQL stored procedures in the right order
   • Branch logic (if/else) based on row counts or flags
   • Parallelize independent loads
3. Scheduling and monitoring
   • Time‑based triggers (hourly, daily)
   • Event‑based triggers (file arrived)
   • Central monitoring with retry logic

Example: A Simple ADF Pipeline Pattern

Typical daily pipeline:

1. Copy Sales from on‑prem SQL to landing.Sales in Azure SQL
2. Run a stored procedure to load stg.Sales
3. Run another stored procedure to refresh dw.FactSales
4. Call a REST endpoint to refresh Power BI dataset

The SQL pieces might look like:

-- Step 2: Landing -> Staging
CREATE OR ALTER PROCEDURE etl.LoadStgSales AS
BEGIN
    SET NOCOUNT ON;

    TRUNCATE TABLE stg.Sales;

    INSERT INTO stg.Sales (SalesID, SalesDate, CustomerID, ProductID, Quantity, NetAmount)
    SELECT
        SalesID,
        SalesDate,
        CustomerID,
        ProductID,
        Quantity,
        NetAmount
    FROM landing.Sales;
END;
GO

-- Step 3: Staging -> Warehouse
CREATE OR ALTER PROCEDURE etl.LoadFactSales AS
BEGIN
    SET NOCOUNT ON;

    -- Upsert logic, simplified
    MERGE dw.FactSalesBase AS tgt
    USING (
        SELECT * FROM stg.Sales
    ) AS src
    ON tgt.SalesID = src.SalesID
    WHEN MATCHED THEN
        UPDATE SET
            tgt.Quantity = src.Quantity,
            tgt.NetAmount = src.NetAmount
    WHEN NOT MATCHED BY TARGET THEN
        INSERT (SalesID, SalesDate, CustomerID, ProductID, Quantity, NetAmount)
        VALUES (src.SalesID, src.SalesDate, src.CustomerID, src.ProductID, src.Quantity, src.NetAmount);
END;
GO

ADF just orchestrates:

• Copy activity: Source = on‑prem SQL, Sink = landing.Sales
• Stored procedure activity: etl.LoadStgSales
• Stored procedure activity: etl.LoadFactSales
• Web activity: Call Power BI REST API to refresh dataset

When not to use ADF

• For small, report‑specific transformations that only one dataset needs
• For heavy business logic that must be transparent and versioned as code
• For row‑by‑row logic that’s easier in SQL or Power Query

ADF is best as the glue, not the place where every business rule lives.

Where Power BI Dataflows Make Sense

Power BI Dataflows are excellent for last‑mile shaping and self‑service scenarios.

Use Dataflows when:

• A team needs to build and iterate quickly on their own logic
• You want to centralize a few curated tables for multiple reports, but don’t yet have a full warehouse
• You need to reshape warehouse tables into report‑friendly entities (e.g., flattening, splitting, grouping)

Example: Turning a Warehouse Fact into a Reporting Table

Assume you already have dw.FactSales and dw.DimDate in Azure SQL. A Dataflow could:

1. Connect to Azure SQL
2. Merge FactSales with DimDate
3. Add fiscal year/month columns
4. Output a SalesSummary entity for multiple datasets

Power Query (M) might look like this:

let
    Source = Sql.Database("azuresqlserver.database.windows.net", "SalesDW"),
    FactSales = Source{[Schema="dw",Item="FactSales"]}[Data],
    DimDate = Source{[Schema="dw",Item="DimDate"]}[Data],
    Merged = Table.NestedJoin(
        FactSales,
        {"SalesDateKey"},
        DimDate,
        {"DateKey"},
        "DateTable",
        JoinKind.Inner
    ),
    Expanded = Table.ExpandTableColumn(
        Merged,
        "DateTable",
        {"CalendarDate", "FiscalYear", "FiscalMonth"},
        {"CalendarDate", "FiscalYear", "FiscalMonth"}
    ),
    Grouped = Table.Group(
        Expanded,
        {"FiscalYear", "FiscalMonth"},
        {{"TotalSales", each List.Sum([NetAmount]), type number}}
    )
in
    Grouped

Why Dataflows here?

• Business users can tweak the logic without touching the warehouse schema
• Same SalesSummary can feed multiple datasets
• Governance is simpler than embedding all logic inside each PBIX

When not to use Dataflows

• As your only ETL layer for large volumes (you’ll hit performance and governance issues)
• For complex SCD logic or multi‑source integration
• For data that other tools (outside Power BI) must consume

A Practical Decision Matrix

When you’re unsure where a transformation belongs, ask these questions.

1. Who needs to reuse this logic?

• Multiple tools (Power BI, Excel, external apps):
  • Prefer SQL (views, tables)
• Only Power BI, multiple reports:
  • Prefer Dataflows
• Single dataset/report only:
  • Consider doing it inside the dataset (Power Query) or SQL, not ADF

2. What’s the data volume and complexity?

• High volume, complex joins, SCD:
  • SQL (with ADF orchestrating)
• Medium volume, mostly reshaping/merging:
  • Dataflows are fine
• Low volume, many sources, lots of file/API handling:
  • ADF for movement, SQL or Dataflows for transformation

3. Who owns and maintains the logic?

• Central data team:
  • More logic in SQL + ADF
• Business/reporting teams:
  • More logic in Dataflows (but keep core definitions in SQL)

4. How critical is auditability and change control?

• Very high (finance, regulatory):
  • SQL with stored procedures and views, version‑controlled
• Medium:
  • ADF pipelines + SQL
• Lower (exploratory, team‑specific):
  • Dataflows and dataset‑level Power Query

A Minimal, Clean Reference Architecture

For most teams, a simple and robust pattern looks like this:

1. Ingest with Data Factory
   • Copy from source systems to Landing (Blob/ADLS or landing schema in SQL)
2. Standardize in SQL Staging
   • Clean types, basic deduplication, simple derivations
3. Model in SQL Warehouse/Data Marts
   • Dimensions, facts, SCDs, conformed business logic
4. Shape in Dataflows
   • Report‑friendly entities; lightweight transformations
5. Model in Power BI Datasets
   • DAX measures, relationships, row‑level security

This keeps heavy lifting and shared logic in SQL, orchestration in ADF, and last‑mile business shaping in Dataflows.

One Concrete Takeaway: Draw the Line Once, Then Stick to It

Pick one real pipeline you own today and do this:

1. Draw its current flow: sources → landing → staging → warehouse → reports.
2. For each transformation, label where it runs now: SQL, ADF, Dataflow, dataset.
3. Move one clearly misplaced transformation to a better layer (e.g., a heavy join currently in a Dataflow into SQL, orchestrated by ADF).

Repeat this cleanup a few times and you’ll gradually move from an accidental mix of SQL, Data Factory, and Dataflows to a deliberate, maintainable ETL design.