Azure Data Factory · Mapping Data Flow and Conditional Routing
Movie Analytics Transformation Pipeline with Azure Data Factory
A parameterised Mapping Data Flow pipeline that validates movie data, derives analytical fields, applies grouping and ranking logic and routes outputs according to release-year requirements.
The project focuses on reusable transformation design, data-quality checks, conditional destination routing and source-file lifecycle management.
Project Overview
- Project type
- Hands-on data engineering implementation
- Primary platform
- Azure Data Factory
- Source
- Movie ratings CSV files
- Source storage
- Azure Blob Storage
- Transformation engine
- Azure Data Factory Mapping Data Flow
- Key transformations
- Derived Column, Window, Aggregate, Assert and Conditional Split
- Destinations
- Azure SQL Database and ADLS Gen2
- Routing rule
- Release-year-based destination routing
- Invalid-data handling
- Assert validation using the configured Azure Data Factory assertion behaviour
- Source-file handling
- Archive after successful processing
- Primary focus
- Data transformation, validation and conditional routing
Problem to Solve
Engineering problem
The source movie dataset required validation and transformation before it could be used for analytical reporting. Genre values needed preparation, rating statistics needed to be calculated and different release-year groups required different storage handling.
- Genre values were supplied in a comma-separated field.
- Release-year and rating values required data-quality validation.
- Analytical output required grouping by genre and release year.
- Rating-based ranking was required within defined groups.
- Older and newer movie records required different destinations.
- Invalid records needed a controlled validation outcome rather than silent acceptance.
- Sink configuration needed to remain reusable across environments or target schemas.
- Processed source files needed to be retained for traceability.
Constraints
- Source data is provided in CSV format.
- Transformation is implemented with Azure Data Factory Mapping Data Flow.
- Routing decisions depend on release year.
- Validation occurs within the transformation flow.
- The implementation should not hardcode every target property.
- Source-file archival should occur only after successful processing.
Architecture Overview
Azure Data Factory reads the source movie CSV from Azure Blob Storage and processes it through a Mapping Data Flow. The flow derives the required genre field, applies analytical transformations, validates required values and uses conditional routing to send outputs to Azure SQL Database or ADLS Gen2. After successful processing, the source file is moved to an archive location.
- 01
Source Storage
Movie-rating CSV files are placed in an Azure Blob Storage staging location.
- 02
Pipeline Orchestration
Azure Data Factory coordinates source reading, Mapping Data Flow execution and source-file archival.
- 03
Data Preparation
The Mapping Data Flow derives the required genre field and prepares source columns for downstream transformation.
- 04
Analytical Transformation
A Window transformation applies row-level ranking before an Aggregate transformation creates grouped rating output at the configured genre-and-release-year grain.
- 05
Data-Quality Validation
Assert rules check required release-year and rating conditions before approved outputs continue.
- 06
Conditional Routing
A Conditional Split sends data to different targets according to the configured release-year rule.
- 07
Target Storage
Configured outputs are written to Azure SQL Database or ADLS Gen2 according to the routing result.
- 08
Source Archive
After successful processing, the source CSV is moved from staging to an archive location.
Key principle: Apply transformation and validation before routing approved outputs to their required destinations, while retaining processed source files through a controlled archive step.
Movie Analytics Mapping Data Flow
End-to-End Data Flow
Receive Movie Data in Blob Storage
The source movie-ratings CSV is placed in the configured Azure Blob Storage staging location.
- Input
- Movie ratings CSV
Start the Parameterised Pipeline
Azure Data Factory starts the pipeline using configured source and target values. The portfolio does not infer unverified parameter names.
Read the Source CSV
The Mapping Data Flow source reads the movie dataset from Azure Blob Storage using the configured dataset.
Derive the Primary Genre
A Derived Column transformation splits the comma-separated genre value and extracts the required primary genre using a Mapping Data Flow expression.
Apply Ranking Logic
A Window transformation ranks row-level movie records within the configured grouping based on rating. Exact partition columns, sort expression and rank retention are not inferred without the Mapping Data Flow export.
- Output
- Row-level records with ranking in the ranking stage
Calculate Grouped Rating Statistics
An Aggregate transformation groups the required records and calculates the implemented rating statistics.
- Output
- One record per configured genre-and-release-year grouping; individual movie rows are no longer represented at this grain
Apply Data-Quality Assertions
Assert rules validate required release-year and rating conditions. The case study does not claim a persisted error-row sink because that configuration has not been verified.
- Decision
- Failing values follow the configured Azure Data Factory assertion behaviour.
Route Data by Release Year
A Conditional Split applies the configured year-based routing rule. The exact boundary is not stated because the implementation export is unavailable.
- Decision
- Configured release-year groups follow separate Azure SQL Database and ADLS Gen2 paths.
Write the Azure SQL Output
The configured analytical output is written to Azure SQL Database. No insert, update, upsert or overwrite behaviour is inferred.
Write the ADLS Gen2 Output
The configured alternate release-year output is written to ADLS Gen2. Partitioning and overwrite behaviour are not claimed.
Handle Invalid Records
Records failing Assert validation remain outside approved output according to the configured assertion failure behaviour. A stored rejected-data path is not claimed.
Archive the Processed Source File
After successful processing, Azure Data Factory moves the source file from staging into the archive location.
- Operational purpose
- Preserve the processed input and prevent repeated staging-folder processing.
Technical Implementation
Parameterised Pipeline Design
Pipeline and dataset parameters allow source and target values to be configured without duplicating the complete pipeline.
- Source location can be supplied dynamically where implemented.
- Target schema or table can be parameterised.
- The Mapping Data Flow can be reused for compatible datasets.
- Environment-specific values remain outside core transformation logic.
Source Ingestion
Azure Data Factory reads the source CSV from Azure Blob Storage into the Mapping Data Flow.
- CSV format is configured through the source dataset.
- Column projection or schema settings follow the implemented source.
- The source file remains available until downstream processing succeeds.
- Data movement and transformation are coordinated through Azure Data Factory.
Genre Derivation
A Derived Column transformation prepares the primary genre used by downstream analytical logic.
- The source genre field contains comma-separated values.
- The transformation extracts the required genre element.
- Derived values are created within Mapping Data Flow.
- The original field may remain available depending on projection.
Window Ranking
A Window transformation assigns ranking within the configured partition while row-level records are still present.
- Partition columns follow the implemented grouping.
- Ordering follows the implemented rating field.
- Ranking occurs before aggregation changes the row grain.
- The rank field is not presented as part of the grouped aggregate output.
Aggregation
An Aggregate transformation creates grouped analytical measures.
- Records are grouped by the configured analytical keys.
- Rating statistics are calculated where implemented.
- The output grain changes from individual movies to grouped records.
- The grouped output is not described as retaining every original movie row.
Assert Validation
Assert rules enforce required data-quality conditions within the Mapping Data Flow.
- Release-year and rating conditions can identify unusable values.
- Assertion results use the configured failure behaviour.
- Validation occurs at the implemented stage of the transformation flow.
- A rejected-row sink is not claimed without its configuration.
Conditional Routing
A Conditional Split directs records to different outputs based on release year.
- Routing conditions are evaluated within Mapping Data Flow.
- Older and newer groups use separate sink paths.
- Routing rules remain explicit and reviewable.
- The project does not claim overlapping output paths.
Parameterised Sink
Target schema or table values can be supplied through parameters where supported by the implementation.
- Sink configuration can vary without rewriting the complete flow.
- SQL targets remain controlled through approved parameters.
- Invalid identifiers should not be accepted silently.
- The portfolio does not infer the target write mode.
Source-File Archival
The pipeline preserves processed source files in an archive location after successful transformation.
- Archival occurs after required processing succeeds.
- The archive supports traceability and controlled reruns.
- Failure dependencies prevent premature archival.
- The description does not claim source deletion.
Data Quality and Validation
Required Release Year
- Purpose
- Prevent records without a usable release-year value from entering year-based routing.
- Implementation
- Use an Assert condition to validate the required release-year value; the exact expression is not reproduced without the flow export.
- Failure handling
- Use the configured assertion failure behaviour without claiming a persisted rejected-row sink.
Positive Rating Validation
- Purpose
- Prevent invalid or unusable rating values from entering analytical output.
- Implementation
- Use an Assert condition to validate the required positive rating rule.
- Failure handling
- Apply the configured Azure Data Factory assertion failure behaviour.
Source Schema Handling
- Purpose
- Ensure source fields required by the Mapping Data Flow are available.
- Implementation
- Use the configured source projection or schema mapping.
- Failure handling
- Explicit schema-drift handling is not claimed.
Genre Derivation Check
- Purpose
- Ensure the derived primary genre can be used by downstream grouping logic.
- Implementation
- Extract the required genre value from the source field.
- Failure handling
- Null or empty genre handling is not inferred without the expression configuration.
Routing Completeness
- Purpose
- Ensure records follow the correct destination based on release year.
- Implementation
- Use explicit Conditional Split expressions.
- Failure handling
- Unmatched-row behaviour is not claimed without the split configuration.
Aggregation Integrity
- Purpose
- Calculate measures at the intended genre-and-release-year grain.
- Implementation
- Group using the configured keys and calculate the implemented rating statistics.
- Failure handling
- No reconciliation against another system is claimed.
Security and Access Considerations
Linked-Service Separation
Keep source and target connection configuration separate from Mapping Data Flow expressions.
Behaviour: Azure Data Factory linked services manage connectivity to Azure Blob Storage, ADLS Gen2 and Azure SQL Database.
The project separates transformation logic from connection configuration. Environment-specific authentication and network controls would be applied through the linked services used during deployment.
Monitoring and Failure Handling
Mapping Data Flow Activity Status
Make transformation success or failure visible in Azure Data Factory monitoring.
Behaviour: Pipeline and activity-run output can be used to review Mapping Data Flow execution.
Assert Failure Visibility
Identify records or runs that violate configured data-quality rules.
Behaviour: Use the Assert configuration and its configured failure behaviour.
Conditional Path Visibility
Confirm which output path receives each release-year group.
Behaviour: Review available sink-level metrics or activity output without inferring custom row-count logging.
Sink Failure Propagation
Prevent a failed target write from being treated as successful processing.
Behaviour: Sink or Mapping Data Flow failures propagate through the pipeline dependency chain.
Archive Dependency
Avoid archiving a source file before required processing completes.
Behaviour: The archive activity runs only after successful data processing.
Rerun and Recovery Considerations
Source Archive
Processed source files are preserved in an archive location for traceability and controlled rerun.
Archive Dependency
The archive action runs only after the required transformation and sink operations succeed.
SQL Sink Rerun Behaviour
A rerun may duplicate or replace target rows depending on the configured SQL write mode; the current mode is not inferred.
ADLS Output Rerun Behaviour
Repeated processing may create duplicate files or overwrite prior output depending on sink path and file settings.
Invalid-Record Reprocessing
Records that fail validation may require source correction before they can be processed again; no automated correction workflow is claimed.
Parameter Consistency
Reruns must use the correct source file, destination schema and destination table parameters.
Partial-Failure Consideration
If one sink succeeds and another fails, recovery may require sink-specific cleanup or rerun controls.
Idempotency Limitation
Rerun safety depends on the configured sink keys, write modes, output paths and partial-failure handling.
The pipeline demonstrates validation, transformation and conditional routing. Complete idempotency depends on sink keys, write modes, output paths and handling of partial failures.
Key Design Decisions
- 01
Use Mapping Data Flow for Transformation
The project combines orchestration and visual data transformation within Azure Data Factory.
- 02
Derive a Primary Genre
A consistent genre value is required for grouping and ranking.
Trade-off: Selecting one genre simplifies analysis but does not preserve full multi-genre membership in that analytical field.
- 03
Use Window Ranking
Ranking records within the configured genre-and-release-year partition demonstrates analytical ordering within groups.
Trade-off: Ranking remains meaningful only while row-level records are preserved.
- 04
Create Grouped Rating Measures
Grouped rating statistics provide analytics-ready summaries.
Trade-off: Aggregation changes the dataset grain and no longer represents individual movie rows.
- 05
Validate After Required Transformations
Assert rules can identify invalid values after required source and derived fields are available.
Trade-off: The exact validation position should follow the Mapping Data Flow export when that asset is added.
- 06
Route by Release Year
The project requires different storage handling for the configured release-year groups.
- 07
Parameterise the SQL Target
Schema or table parameterisation improves reuse across compatible targets or environments where implemented.
- 08
Archive the Source File
The archive supports traceability and reduces accidental repeat processing from the staging location.
Challenges and Implementation Considerations
Parsing Multi-Value Genres
The source genre field contains multiple values in one column.
Approach: Use a Derived Column expression to extract the required primary genre.
Remaining limitation: The simplified analytical field does not represent every genre associated with the movie.
Preserving the Correct Data Grain
Window ranking operates on row-level records, while aggregation produces group-level output.
Approach: Describe ranking before aggregation and state that the grouped output no longer represents individual movie rows.
Remaining limitation: The available implementation evidence does not confirm a separate row-level output branch.
Applying Validation at the Correct Stage
Some validation depends on fields created or transformed within the flow.
Approach: Place Assert rules at the stage supported by the implementation and avoid reproducing unverified expressions.
Routing Records Without Overlap
Incorrect split conditions could send records to the wrong destination or multiple destinations.
Approach: Use explicit release-year conditions and review the Conditional Split behaviour.
Handling Invalid Ratings and Years
Null years prevent year-based routing, while invalid ratings affect analytical measures.
Approach: Use Assert validation before approved sink writing without claiming an unverified rejected-row sink.
Managing Different Target Types
Azure SQL Database and ADLS Gen2 have different sink configuration and rerun behaviour.
Approach: Use dedicated sink paths with controlled mappings and parameters.
Maintaining Accurate Archive Dependencies
Archiving too early could make troubleshooting or rerun handling more difficult.
Approach: Run the archive activity only after required processing completes.
Avoiding Unsupported Technical Claims
The project uses Mapping Data Flow rather than a separate code-based processing engine.
Approach: Use accurate Azure Data Factory transformation terminology throughout the case study.
Outcome and Demonstrated Value
The completed workflow demonstrates how Azure Data Factory Mapping Data Flow can validate, transform and route movie data according to defined analytical and storage requirements.
- Transforms batch movie data through Azure Data Factory Mapping Data Flow.
- Derives genre information and applies ranking and grouped statistics.
- Applies Assert-based validation for required data-quality rules.
- Uses Conditional Split for release-year routing.
- Writes outputs to Azure SQL Database and ADLS Gen2.
- Preserves processed source files through an archive flow.
Potential Extension
Potential Improvements
- Add formal source-schema validation before transformation.
- Add null and format checks for title and genre fields.
- Preserve all genres through a normalised genre bridge rather than only extracting one value.
- Separate row-level ranking and grouped aggregation into explicit output branches when both outputs are required.
- Add structured rejected-record output with rule name and failure reason.
- Add source-to-target row-count reconciliation.
- Add audit fields such as pipeline_run_id and source_file_name.
- Add business-key-based upsert logic for safe SQL reruns.
- Add run-specific or partitioned output handling for ADLS reruns.
- Add pipeline notifications for failed transformation or sink activities.
- Add environment-specific configuration and deployment parameters.
- Add automated tests for Mapping Data Flow expressions.
- Add data profiling to identify rating and release-year anomalies.