Azure Databricks and Azure Data Factory · PySpark and Data Quality
Retail Data Validation Pipeline with Azure Databricks
A retail ingestion and validation workflow that moves order data from Amazon S3 into ADLS Gen2, validates records through Databricks PySpark and separates rejected records before reporting-layer loading.
The project focuses on duplicate detection, order-status validation, secure credential handling and preserving rejected records for review and possible reprocessing.
Project Overview
- Project type
- Hands-on data engineering implementation
- Primary source
- Amazon S3
- Source datasets
- Orders CSV and order-items JSON
- Orchestration
- Azure Data Factory
- Staging storage
- ADLS Gen2
- Validation engine
- Azure Databricks with PySpark
- Validation stages
- Duplicate order detection and order-status reference validation
- Reference-data source
- Azure SQL Database
- Approved target
- sales_reporting table in Azure SQL Database
- Rejected-data target
- Discarded-data folder in ADLS Gen2
- Credential management
- Azure Key Vault
- Primary focus
- Data validation, secure connectivity and controlled rejected-record handling
Problem to Solve
Engineering problem
Retail order data can produce unreliable reporting when duplicate order identifiers or unsupported order statuses are allowed into the reporting dataset. The pipeline needed to validate records before promotion while preserving rejected data for investigation.
- Duplicate order_id values could cause the same order to be represented more than once.
- order_status values needed to be checked against approved reference data.
- Order and order-item datasets used different file formats.
- Credentials were required across Amazon S3, Databricks and Azure SQL Database.
- Invalid records needed controlled handling rather than silent removal.
- Rejected records needed enough context to explain why validation failed.
- Validation failures should not require discarding the complete source dataset.
- Valid and invalid records needed to follow separate processing paths.
Constraints
- Source files are received from Amazon S3.
- Orders are supplied in CSV format.
- Order-item data is supplied in JSON format.
- Validation is performed through a Databricks notebook.
- Approved order-status values are maintained outside the notebook.
- Credentials should not be hardcoded in pipelines or notebooks.
- Rejected records should remain available for investigation.
- Valid records are loaded only after completing the required checks.
Architecture Overview
Azure Data Factory retrieves the required connection information securely, copies retail source files from Amazon S3 into ADLS Gen2 and invokes an Azure Databricks notebook. The notebook applies duplicate and reference-data validation, loads approved records into Azure SQL Database and writes rejected records to a controlled ADLS Gen2 location with validation context.
- 01
Amazon S3 Source
The source contains retail order and order-item datasets in CSV and JSON formats.
- 02
Secure Credential Retrieval
Azure Data Factory obtains the required Amazon S3 connection values from Azure Key Vault rather than embedding them directly in pipeline content.
- 03
Ingestion Orchestration
Azure Data Factory copies the configured source files from Amazon S3 into the ADLS Gen2 staging area.
- 04
ADLS Gen2 Staging
Raw order and order-item files are retained in a staging location before validation processing.
- 05
Databricks Validation
An Azure Databricks notebook uses PySpark to apply duplicate detection and order-status validation.
- 06
Reference Data
Approved order-status values are read from an order-status reference table in Azure SQL Database.
- 07
Approved Data Target
Records passing both validation stages are loaded into the sales_reporting table.
- 08
Rejected Data Target
Records failing duplicate or status validation are written to a discarded location in ADLS Gen2 with information describing the validation failure.
Key principle: Apply validation before reporting-layer promotion, while preserving rejected records and separating credential management from pipeline and notebook code.
Retail Data Validation with ADF and Databricks
End-to-End Data Flow
Receive Retail Data in Amazon S3
The source location contains the retail order and order-item datasets.
- Input
- Orders CSV and order-items JSON
Retrieve Amazon S3 Credentials
Azure Data Factory retrieves the required Amazon S3 access values from Azure Key Vault.
- Operational purpose
- Avoid embedding source credentials directly in the pipeline definition.
Copy Source Data to ADLS Gen2
Azure Data Factory copies the source datasets from Amazon S3 into the configured ADLS Gen2 staging location.
Retain Raw Files in Staging
The orders CSV and order-items JSON remain available in ADLS Gen2 for validation processing and troubleshooting.
- Output
- Staged retail source data
Invoke the Databricks Validation Notebook
Azure Data Factory triggers the configured Azure Databricks notebook after the required source files are available. The required Databricks connection value is retrieved through the configured Azure Key Vault integration.
Detect Duplicate Orders
PySpark checks order_id values and identifies records that fail the duplicate-order rule.
- Output
- Stage 1: candidate valid records and duplicate records
Retrieve Approved Status Values
The notebook connects to Azure SQL Database and reads the approved order-status reference data. SQL connection information is retrieved securely rather than hardcoded in the notebook.
Validate Order Status
Records passing the duplicate check are compared with the approved status reference data.
- Output
- Stage 2: valid order-status records and invalid order-status records
Load Approved Records
Records passing both validation stages are loaded into the sales_reporting table in Azure SQL Database.
Write Rejected Records
Duplicate and invalid-status records are written to the discarded-data location in ADLS Gen2 with their validation stage and rejection reason.
Preserve Data for Investigation
Rejected records remain outside the approved reporting path and can be reviewed to understand the validation failure.
Technical Implementation
Amazon S3 Ingestion
Azure Data Factory coordinates movement of the retail source datasets from Amazon S3 into Azure storage.
- Orders are received in CSV format.
- Order-item data is received in JSON format.
- Source connection values are separated from pipeline logic.
- Data is staged in ADLS Gen2 before validation.
- The pipeline invokes downstream processing after ingestion succeeds.
Secure Credential Handling
Azure Key Vault is used to keep sensitive connection values outside the pipeline and notebook code.
- Amazon S3 access credentials, Azure SQL Database connection information and the Databricks authentication value are handled as secure credential categories.
- Secrets are retrieved at runtime.
- Credentials are not written directly into the portfolio.
- Connection details remain separate from transformation logic.
Databricks Notebook Orchestration
Azure Data Factory invokes a Databricks notebook after the required source data is staged.
- Pipeline dependencies control notebook execution.
- The notebook reads source data from ADLS Gen2.
- Validation is implemented with PySpark.
- Validation output is divided into approved and rejected records.
- Connection values are supplied through secure configuration.
Duplicate Detection
The first validation stage identifies repeated order_id values before records proceed to reporting-layer loading.
- order_id is used as the duplicate-checking field.
- Duplicate records are separated from candidate valid records.
- Rejected duplicate records are preserved for review.
- The retained-record rule follows the notebook implementation and is not inferred here.
Reference-Data Validation
The second validation stage checks order_status against approved values stored in Azure SQL Database.
- Approved values are external to the notebook code.
- Reference data can be maintained without rewriting validation logic.
- Unmatched statuses are classified as invalid.
- Invalid-status records follow the rejected-data path.
Valid-Record Loading
Records passing duplicate and status validation are written to the sales_reporting table.
- Only records passing both stages proceed.
- Azure SQL Database is used as the reporting target.
- The portfolio does not infer an append, overwrite or upsert write mode.
- No uniqueness constraint is claimed.
Rejected-Record Handling
Failed records are separated instead of being silently ignored or mixed with the approved reporting dataset.
- Duplicate records are preserved.
- Invalid-status records are preserved.
- The rejected-data location is stored in ADLS Gen2.
- Validation context is retained to support investigation and controlled correction.
Separation of Responsibilities
Each platform performs a focused role in the workflow.
- Azure Data Factory: orchestration and data movement.
- ADLS Gen2: raw staging and rejected-data storage.
- Azure Databricks: PySpark validation.
- Azure SQL Database: reference data and approved reporting target.
- Azure Key Vault: credential storage.
Data Quality and Validation
Source Availability
- Purpose
- Confirm that required source datasets are available before validation processing.
- Implementation
- Use pipeline activity dependencies and source-copy results.
- Failure handling
- Do not invoke the validation notebook when a required ingestion activity fails.
Duplicate order_id Detection
- Purpose
- Prevent repeated order identifiers from entering the approved reporting dataset.
- Implementation
- Use PySpark to identify repeated order_id values.
- Failure handling
- Write affected records to the discarded-data location with a duplicate reason; the rule does not infer fraud or a source-system error.
Order-Status Reference Validation
- Purpose
- Allow only recognised order-status values into the approved dataset.
- Implementation
- Compare order_status with approved values read from Azure SQL Database.
- Failure handling
- Write unmatched records to the discarded-data location.
Valid-Record Promotion
- Purpose
- Ensure that only records passing both validation stages reach the reporting target.
- Implementation
- Construct the valid output from records that pass duplicate and status checks.
- Failure handling
- Do not load failed records into sales_reporting.
Rejection-Reason Preservation
- Purpose
- Make it possible to understand why a record did not pass validation.
- Implementation
- Retain the validation stage and reason before writing rejected records.
- Failure handling
- Keep rejected records outside the approved reporting path with their implemented validation context.
Source-Format Handling
- Purpose
- Read order and order-item datasets according to their expected formats.
- Implementation
- Use the appropriate CSV and JSON readers and schemas.
- Failure handling
- Malformed-file handling is not claimed beyond the implemented readers.
Security and Credential Handling
Amazon S3 Credential Protection
Keep AWS access values outside the Azure Data Factory pipeline definition.
Behaviour: Retrieve the configured Amazon S3 credentials from Azure Key Vault.
Azure SQL Credential Protection
Avoid embedding database credentials in the Databricks notebook.
Behaviour: Retrieve SQL connection information through the approved secure configuration.
Databricks Connection Protection
Keep the Databricks authentication value separate from pipeline code.
Behaviour: Use Azure Key Vault-backed configuration for the Databricks connection.
Secret Separation from Code
Keep transformation and validation logic free from hardcoded secrets.
Behaviour: Access credentials at runtime through secure connection configuration.
This project demonstrates credential separation using Azure Key Vault; it is not presented as a complete enterprise security, networking or governance implementation.
Monitoring and Failure Handling
Activity Dependency Control
Prevent downstream validation from running when source ingestion fails.
Behaviour: The Databricks activity depends on successful completion of the required ingestion activities.
Validation-Stage Separation
Identify whether a record failed duplicate or status validation.
Behaviour: Records are classified according to the validation rule they fail.
Rejected-Record Preservation
Avoid losing invalid records during validation.
Behaviour: Rejected records are written to the discarded-data folder for review.
Pipeline Failure Visibility
Make ingestion or notebook execution failures visible through Azure Data Factory monitoring.
Behaviour: Activity and pipeline run details identify the failed stage.
Credential Failure Visibility
Identify failures caused by unavailable or invalid connection values.
Behaviour: Key Vault, source connection or database connection errors remain visible in the relevant activity output.
Rerun and Recovery Considerations
Raw-Data Retention
The staged source files remain available in ADLS Gen2 for investigation and controlled rerun.
Rejected-Record Retention
Duplicate and invalid-status records remain in the discarded-data location instead of being silently removed.
Validation Reprocessing
Rejected records can be reviewed and reprocessed after source correction or reference-data correction; this is a controlled consideration rather than an automated workflow.
Reference-Data Correction
An order-status rejection may require updating the source record or the approved reference data, depending on the cause.
Duplicate-Rerun Risk
A rerun could create repeated reporting records if the sink write mode does not enforce idempotent behaviour.
Target Write Behaviour
The current portfolio does not infer whether the target uses append, overwrite or upsert behaviour.
Source-File Identification
Retaining source-file information can improve rejected-record traceability when that metadata is implemented.
Partial-Failure Handling
A failure after some valid records are written may require target cleanup, transaction handling or business-key-based loading.
The project demonstrates controlled validation and rejected-data handling. Complete idempotency depends on the reporting-table write mode, business keys and partial-failure behaviour.
Key Design Decisions
- 01
Stage Source Data Before Validation
Staging in ADLS Gen2 separates ingestion from validation and keeps the original input available for troubleshooting.
- 02
Use Azure Data Factory for Orchestration
ADF manages data movement and dependency-based activity flow, while Databricks performs the PySpark processing.
- 03
Use PySpark for Validation
PySpark supports reusable DataFrame-based validation and separation of valid and rejected records.
- 04
Validate in Two Stages
Applying duplicate detection before status validation makes rejection reasons clearer and prevents repeated records from unnecessarily continuing through later validation.
Trade-off: The stage order must be considered when a record fails more than one rule.
- 05
Maintain Status Values Outside the Notebook
Approved order statuses can be maintained separately from notebook code in Azure SQL Database.
- 06
Preserve Rejected Records
Writing invalid records to a discarded-data location keeps them available for investigation and possible correction.
- 07
Store Credentials in Azure Key Vault
Keeping AWS, Databricks and database connection values outside pipeline and notebook code reduces their direct exposure in implementation assets.
- 08
Load Only Approved Records
The sales_reporting table should not mix validated and rejected records.
Challenges and Implementation Considerations
Defining Duplicate Behaviour
Detecting repeated order_id values is straightforward, but deciding which record should be retained requires a clear rule.
Approach: Follow the notebook logic without inferring a first, latest or highest-priority survivorship rule.
Validating Against External Reference Data
Hardcoding status values inside the notebook would make maintenance less flexible.
Approach: Read approved statuses from an Azure SQL order-status reference table.
Handling Different Source Formats
Orders and order items arrive in CSV and JSON formats.
Approach: Use format-specific readers and maintain clear schemas for each dataset.
Preserving Invalid Data
Dropping invalid records would remove evidence needed for troubleshooting.
Approach: Write rejected records to an ADLS Gen2 discarded-data location.
Securing Multiple Connections
The workflow requires access to Amazon S3, Azure Databricks and Azure SQL Database.
Approach: Store sensitive connection values in Azure Key Vault and retrieve them through approved runtime configuration.
Distinguishing Validation Failures
Duplicate records and invalid-status records require different corrective actions.
Approach: Keep validation stages and rejection reasons distinct.
Managing Reporting-Table Reruns
Repeating a successful or partially successful load can produce duplicate target records depending on the sink mode.
Approach: Avoid inferring the current write behaviour and identify business-key or merge handling as a potential improvement.
Reference-Data Accuracy
Valid records may be rejected when the approved-status table is incomplete or outdated.
Approach: Treat reference-data maintenance as part of the validation dependency.
Outcome and Demonstrated Value
The completed workflow creates a controlled path from Amazon S3 ingestion to reporting-layer loading. Order records must pass duplicate and order-status validation before reaching the sales_reporting table, while rejected records remain available in ADLS Gen2 for investigation.
- Moves Amazon S3 order data into Azure through Azure Data Factory.
- Uses ADLS Gen2 as a staging and rejected-data location.
- Applies PySpark duplicate and order-status validation in Azure Databricks.
- Keeps credentials separate from pipeline and notebook code.
- Preserves approved and rejected paths for reporting and investigation.
- Documents target-rerun limitations without inferring an unverified sink mode.
Potential Extension
Potential Improvements
- Define an explicit duplicate-survivorship rule.
- Add schema validation before notebook processing.
- Add null and data-type validation for required order fields.
- Add source-to-target row-count reconciliation.
- Add audit columns such as pipeline_run_id and source_file_name.
- Add a structured rejection schema containing rule name and rejection timestamp.
- Add business-key-based merge or upsert logic for safe target reruns.
- Add formal reprocessing of corrected rejected records.
- Add monitoring and notification for rejected-record thresholds.
- Add automated PySpark tests for validation functions.
- Add environment-specific configuration for development, testing and production.
- Add Delta Lake staging when versioning and merge behaviour are required.
- Replace token-based Databricks authentication with an appropriate identity-based method where supported by the deployment environment.