Skip to main content

Transformation - section 6 - data flow

 



Feature from SlideExplanation
✅ Code-free data transformationsData Flows in ADF allow you to build transformations using a drag-and-drop visual interface, with no need for writing Spark or SQL code.
✅ Executed on Data Factory-managed Databricks Spark clustersInternally, ADF uses Azure Integration Runtimes backed by Apache Spark clusters, managed by ADF, not Databricks itself. While it's similar in concept, this is not the same as your own Databricks workspace.
✅ Benefits from ADF scheduling and monitoringData Flows are fully integrated into ADF pipelines, so you get all the orchestration, parameterization, logging, and alerting features of ADF natively.

⚠️ Important Clarification

Although it says "executed on Data Factory managed Databricks Spark clusters," this does not mean you're using your own Azure Databricks workspace. Rather:

  • ADF Data Flows run on ADF-managed Spark clusters.

  • Azure Databricks notebooks (which you trigger via an "Execute Notebook" activity) run on your Databricks workspace.

So, these are two different engines:

  • Mapping Data Flows → ADF-managed Spark

  • Databricks Notebook Activity → Azure Databricks (your own cluster)

✅ When to Use Data Flows vs. Databricks

Use CaseUse Data FlowsUse Databricks
No-code / low-code transformation✅ Yes❌ Less suitable
Complex ML / Custom PySpark❌ Limited✅ Ideal
Fully integrated into ADF✅ Native✅ Via notebook activity
Need reusable notebooks / jobs❌ No notebooks✅ Yes


🔷 Types of Data Flows in ADF

TypeDescriptionPurpose
Data FlowCode-free data transformation at scaleIdeal for building ETL/ELT pipelines visually. Runs on ADF-managed Spark clusters. Supports complex joins, aggregations, lookups, pivot, derived columns, etc.
Wrangling Data Flow (Preview)Code-free data preparation at scale, designed for data exploration and profilingUses Power Query engine (same as Excel/Power BI). Good for data cleaning, shaping, quick transformation in a familiar UI. Limited in complex logic. Still in preview, with fewer features and limited scalability.

🧩 When to Use Which?

ScenarioUse Data FlowUse Wrangling Data Flow
Large-scale transformations✅ Yes❌ No
Data exploration & profiling❌ Not ideal✅ Yes
Complex ETL logic (joins, filters, aggregations)✅ Yes❌ No
Familiar with Power BI/Excel style❌ Not needed✅ Yes
Production-ready pipelines✅ Yes❌ No (Preview-only)

🛠️ Backend Execution Engine

  • Data Flows use ADF’s Spark-based runtime under the hood.

  • Wrangling Data Flows use Power Query engine, which converts logic into Spark only during execution.

📝 Summary

FeatureData FlowWrangling Data Flow
InterfaceVisual, scalable builderPower Query (Excel/Power BI style)
StatusGenerally AvailablePreview (not for prod)
Ideal ForETL at scaleLightweight wrangling
BackendSpark (ADF-managed)










Power Query → Spark












Phase Mode Purpose Cost When to Use
Development Data Flow Debug Test & preview transformations Debug cluster cost While building or troubleshooting
Production Pipeline Execution Execute full pipeline logic Based on activity runtime In deployed, automated pipelines

In Each of the data flow we need souce and sink transformation but we can add as many souces we like 

creting the souce transformation first .



after that click test connection and test the connection is successful 


1. Source: CasesAndDeathsSource

  • Dataset used: ds_raw_cases_and_deaths

  • This step loads raw COVID-19 data.

🔹 Data Example (before filtering):

countrycontinentdatecasesdeathspopulation
ItalyEurope2020-02-012060M
BrazilAmericas2020-02-0131212M

2. FilterEuropeOnly

  • Applies a filter to keep only European countries.

  • Likely filter expression:

    sql
    continent == 'Europe' && isNotNull(country_code)

🔹 Purpose: Remove non-European countries, ensuring you're working with relevant regional data.

🔹 Data After Filtering:

countrycontinentdatecasesdeathscountry_code
ItalyEurope2020-02-0120IT
SpainEurope2020-02-0151ES

3. SelectOnlyRequiredFields

  • Renames and reduces the number of fields to only essential ones.

🔹 Input fields might include: continent, country, country_code, population, cases, deaths, source, date.

🔹 After transformation, fields might become:

countrycountry_codepopulationcases_countdeaths_countreported_date
ItalyIT60M202020-02-01

✅ This step standardizes column names to prepare for downstream logic like pivoting and lookups.

4. PivotCounts

  • Performs a pivot transformation on case and death counts.

🔹 For example, converts multiple rows like:

text
| metric | value |
|--------|-------|
| cases  | 2     |
| deaths | 0     |

Into:

text
| cases_count | deaths_count |
|-------------|--------------|
| 2           | 0            |

This step:

  • Groups by keys such as country_code and date.

  • Pivots metric (cases, deaths) into separate columns.

✅ Makes the data easier to analyze and visualize downstream.

5. LookupCountry

  • Performs a lookup join using the ds_country_lookup dataset.

🔹 This enriches the dataset with metadata, like:

  • _entry_code_3_digit (likely ISO alpha-3 code)

  • Region, subregion, or demographic grouping

  • Possibly economic or geographic information

🔹 Join key: country_code

🔹 Result:

countrycountry_code_entry_code_3_digitpopulationcasesdeathsdate
ItalyITITA60M20...

✅ Adds contextual intelligence to raw data, enhancing its utility.

6. SelectForSink

  • Final selection of 8 columns that will be written to the sink.

  • May look like:

    • country

    • country_code

    • population

    • cases_count

    • deaths_count

    • reported_date

    • _entry_code_3_digit

    • source

✅ Ensures the final schema is clean, ready for output.

7. Data Preview

This lets you preview the data at this transformation stage.

🧪 You can see:

  • Rows grouped by reported_date

  • Repeated population values

  • Cases and deaths changing per date

  • Source is consistent: "Epidemic intelligence, national..."

✅ Useful for debugging and validation before writing to the sink.

8. Sink (Not Yet Shown)

You’ll typically route this data to:

  • Azure Data Lake Storage (ADLS) for historical archive

  • SQL Database for Power BI reporting

  • Snowflake or Synapse for analytical modeling

  • Blob Storage in parquet/CSV for further consumption

💡 Best Practices & Observations:

AreaRecommendation
Filter stepUse isNull() checks to avoid losing records
PivotEnsure pivot columns are finite and known in advance
LookupEnsure country codes are standardized (2-digit vs. 3-digit)
Column namesUse consistent naming: snake_case or camelCase
Sink schemaValidate sink schema before full execution
ParameterizationUse parameters to dynamically load by date or region
Debug modeAlways test in debug mode before triggering pipeline runs

✅ Summary:

This Data Flow:

  • Transforms COVID-19 data

  • Filters for Europe

  • Pivots case/death metrics

  • Enriches with country metadata

  • Prepares clean data for downstream analytics


Create a Mapping Data Flow (the design of your data transformations)

  • Create a Pipeline

  • Inside the pipeline, add a Data Flow Activity

  • In the Data Flow Activity’s settings, select the Mapping Data Flow you designed earlier

  • When the pipeline runs, it triggers the Mapping Data Flow via the Data Flow Activity


    • ✅ Enable Data Flow Debug:

    • Click "Data Flow Debug" in the Data Flow designer (top bar).

    • This spins up a temporary Spark cluster.

    • You can preview data, test transformations, and check outputs quickly.

    • TTL (Time to Live) controls how long the debug cluster stays up to reduce spin-up time and cost.

    ✅ Avoid pipeline-level debug when not necessary:

    • Pipeline Debug runs the Data Flow on full production IR, which is more expensive.

    • Use only when you need to test integration with other activities, like triggers, lookups, or parameters.



    After creating a data flow in Azure Data Factory or Synapse Pipelines, you can manually trigger the pipeline by selecting "Trigger Now". This allows you to test the pipeline without having to create a scheduled trigger.

    When running a pipeline that includes a Data Flow activity, Spark will automatically allocate a compute cluster in the background to execute the job during the manual run. This eliminates the need to pre-provision compute resources for testing.

    This method is useful for testing and debugging purposes before setting up automated or scheduled triggers.

    Comments

    Post a Comment

    Popular posts from this blog

    Entity Relationship (ER) Diagram Model with DBMS Example

    Reference :   Entity Relationship (ER) Diagram Model with DBMS Example What is ER Diagram? ER Diagram  stands for Entity Relationship Diagram, also known as ERD is a diagram that displays the relationship of entity sets stored in a database. In other words, ER diagrams help to explain the logical structure of databases. ER diagrams are created based on three basic concepts: entities, attributes and relationships. ER Diagrams contain different symbols that use rectangles to represent entities, ovals to define attributes and diamond shapes to represent relationships. At first look, an ER diagram looks very similar to the flowchart. However, ER Diagram includes many specialized symbols, and its meanings make this model unique. The purpose of ER Diagram is to represent the entity framework infrastructure. Entity Relationship Diagram Example Table of Content: What is ER Diagram? What is ER Model? History of ER models Why use ER Diagrams? Facts about ER Diagram Model ER Diagram...
    Post a Comment
    Read more

    SQL Joins and advanced joins and Subqueries

      Refernce :  Expert Guide to Advanced SQL Joins: What You Need to Know It's helpful to visualize how these different SQL joins work. Here's a breakdown in a table-like format, along with explanations: SQL Join Types Overview Join Type Description Key Characteristics Use Cases INNER JOIN Returns rows where there is a match in both tables. - Shows only matching records. - Excludes unmatched rows from both tables. - Retrieving related data that exists in both tables. - Finding records with corresponding entries in another table. LEFT OUTER JOIN (LEFT JOIN) Returns all rows from the left table, and the matched rows from the right table. - Includes all records from the left table. - Fills in NULL values for columns from the right table where there's no match. - Retrieving all records from one table and their related data from another, even if some records don't have matches. - Finding records in one table that don't have corresponding entries in another. RIGHT OUTER JO...
    Post a Comment
    Read more

    GIT BASH

      Bash Shell: Git Bash uses the Bash (Bourne Again SHell) command-line interpreter. This means you can use many of the same commands you'd find in a Linux or macOS terminal. Git Integration: Git Bash is tightly integrated with Git, making it easy to execute Git commands Essential Commands: Navigation: pwd : Prints the current working directory. ls : Lists files and directories in the current directory. cd <directory> : Changes the current directory. cd .. : Moves to the parent directory. File Management: mkdir <directory> : Creates a new directory. touch <file> : Creates a new file. rm <file> : Removes a file. rmdir <directory> : Removes an empty directory. Git Commands: git init : Initializes a new Git repository. git clone <repository URL> : Clones an existing Git repository. git status : Displays the status of your working directory. git add <file> : Adds a file to the staging area. git commit -m "commit message" : Commits chan...
    Post a Comment
    Read more