How to create a transformation Data Flow in Synapse Analytics: step by step

This tutorial shows how to create a Data Flow in Azure Synapse Analytics to transform data (cleansing, enrichment and aggregation) and load the result to a Data Lake. You will learn why to use Data Flows for ETL/ELT without code and how to avoid common performance and data type errors.

Prerequisites

• Azure account with permissions to create resources in a subscription.
• Azure Synapse Analytics workspace created.
• Linked service for Azure Data Lake Storage Gen2 configured in Synapse.
• Sample files in CSV format in the Data Lake (e.g.: /raw/sales/).
• Basic knowledge of data transformation and SQL.

Step 1: Create an Integration Runtime and Linked Service

The Data Flow uses a managed Integration Runtime to execute transformations. Ensure the Workspace has an Azure IR or create a Self-hosted IR if you need to access private networks. Then, define a Linked Service for the ADLS Gen2 where the source files are located and where you will write the output.

// Exemplo conceptual de JSON do Linked Service (abrir em Synapse Studio -> Manage -> Linked services)
{
  "name": "LS_ADLS_gen2",
  "properties": {
    "type": "AzureBlobFS",
    "typeProperties": {
      "url": "https://minhaaccount.dfs.core.windows.net/",
      "servicePrincipalId": "",
      "servicePrincipalKey": "",
      "tenant": ""
    }
  }
}

Step 2: Create a Data Flow in Synapse Studio

In Synapse Studio go to Integrate -> Data flows -> New data flow. Choose a Mapping Data Flow (allows mapping columns and applying transformations). This provides a visual interface for common operations: Select, Filter, Derived Column, Aggregate, Join, Sort and Sink.

Step 3: Define the Source and schema

Add a Source and point to the Linked Service and the CSV file path. Configure the format (delimiter, header) and the schema: it is recommended to explicitly define column types to avoid unexpected conversions.

// Exemplo de configurações no Source (conceptual)
Source:
  dataset: /raw/sales/sales_2026.csv
  format: csv
  firstRowAsHeader: true
  schema:
    SaleId: long
    Date: string
    Amount: decimal
    Region: string

Step 4: Cleansing and normalization with Derived Column and Filter

Use Derived Column to normalize fields (e.g.: convert dates, remove spaces) and Filter to remove invalid rows (e.g.: Amount null or negative). In Derived Column create simple expressions; e.g.: toDate(Date, 'yyyy-MM-dd') to normalize the date.

// Exemplo de expressões no Derived Column
cleanDate = toDate(Date, 'yyyy-MM-dd')
cleanRegion = trim(Region)
validAmount = iif(isNull(Amount) || Amount <= 0, null(), Amount)

Step 5: Aggregation and enrichment with Aggregate and Join

If you want to calculate metrics (e.g.: total sales by region and month) use Aggregate: group by Region and month(cleanDate). To enrich, add a second Source (e.g.: regions table) and perform a Join by key.

// Exemplo conceptual de Aggregate
groupBy: [Region, year(cleanDate), month(cleanDate)]
aggregates:
  totalAmount: sum(Amount)
  countSales: count(SaleId)

Step 6: Configure the Sink and output format

Add a Sink to write the result to the Data Lake. Choose the output format: Parquet is recommended for performance and compression. Configure logical partitions (e.g.: by year/month) to optimize subsequent queries.

Sink:
  linkedService: LS_ADLS_gen2
  path: /processed/sales/
  format: parquet
  partitionBy: [year, month]
  fileNameOption: PerPartition

Step 7: Test locally and optimize performance

Run the Data Flow in Debug mode in Synapse Studio with a sample of the data. Check runtimes, memory and join cardinality. Adjust the Data Flow performance options (clusterSize, cores) if necessary and avoid unnecessary shuffles: prefer pre-aggregations and reduced columns.

Verify the result

Confirm the Parquet file was generated at the path /processed/sales/ and that the partitions (e.g.: year=2026/month=9) exist. You can use Synapse Studio -> Data -> Linked -> open the path and preview. Alternatively, run a simple query in SQL Serverless to validate schema and rows:

SELECT TOP 10 *
FROM OPENROWSET(
  BULK 'https://minhaaccount.dfs.core.windows.net/processed/sales/*/*.parquet',
  DATA_SOURCE = 'LS_ADLS_gen2',
  FORMAT = 'PARQUET'
) AS rows;

Conclusion

Creating a Mapping Data Flow in Azure Synapse Analytics is a visual and powerful way to build ETL/ELT pipelines without complex code, enabling cleansing, aggregation and writing to Parquet for better performance. Next steps: automate the Data Flow inside a Pipeline with scheduling and monitoring, or try Sink to Azure SQL/Dedicated SQL Pool. Tip: start with small samples in Debug and validate schemas to avoid type errors in production.