Azure Data Factory and Azure Databricks · Metadata-Driven Quarterly Processing
Quarterly Policy Data Delivery and Lakehouse Processing Platform
A completed quarterly data pipeline that uses metadata-driven Azure Data Factory orchestration and Azure Databricks processing to deliver validated Raw Delta tables for downstream Data Science workloads.
The implementation combines configurable ingestion, PySpark validation, Delta Lake processing, Unity Catalog integration, structured stage validation, framework logging and protected watermark movement.
Project Overview
- Project status
- Completed
- Processing frequency
- Quarterly
- Source
- SQL-based source systems
- Source scope
- 14 configured quarterly tables
- Orchestration
- Azure Data Factory master and child pipelines
- Landing format
- Parquet
- Landing storage
- ADLS Gen2
- Raw processing
- Azure Databricks and PySpark
- Raw storage
- Delta Lake tables
- Data registration
- Unity Catalog
- Downstream consumers
- Data Science and curation notebooks
- Incremental control
- Framework watermark
- Operational controls
- Structured result validation, stage-level logging and rerun-safe updates
- Historical scope
- 8 periods × 14 tables = 112 table-period operations
- Historical validation
- Completed guarded dry-run validation
- Primary focus
- Quarterly delivery reliability, source-to-Raw processing and downstream Data Science readiness
Implementation Status
Completed
Quarterly Source-to-Raw Pipeline
Metadata-driven Landing ingestion, Databricks Raw processing, structured result validation, logging and protected watermark movement were implemented and tested.
Integrated
Data Science Readiness
Raw Unity Catalog tables and standardised quarter_code metadata were integrated for downstream notebook consumption.
Completed
Historical Migration Framework
The framework was completed and validated through a guarded dry run covering 8 periods, 14 tables and 112 table-period operations.
Problem to Solve
Engineering problem
The quarterly delivery process needed a reliable path from configured SQL source tables to validated Raw Delta tables. Landing ingestion, Databricks processing, framework logging, watermark movement and downstream quarter-based consumption had to operate as one controlled workflow rather than as disconnected stages.
- Landing copy behaviour needed to be driven from framework metadata.
- Target storage values should not remain hardcoded in pipeline activities.
- Raw Databricks processing needed to be integrated into the ADF master orchestration.
- ADF needed a reliable way to understand whether the notebook completed successfully.
- Raw-stage execution required its own framework logging and failure visibility.
- The watermark must not advance when Raw processing fails or returns an invalid result.
- Reruns needed to avoid framework-log key conflicts.
- Downstream Data Science logic required a consistent quarter identifier.
- Historical periods required a separate controlled migration process.
- Quarterly and historical processing needed separate operational boundaries.
Constraints
- The quarterly run processes 14 configured tables.
- The solution supports one-table and all-table execution modes.
- Landing data is read as Parquet.
- Raw data is written as Delta.
- Quarter partitions require validation.
- Watermark movement depends on successful Raw validation.
- Data Science notebooks consume Raw Unity Catalog tables.
- Historical migration remains separate from the regular quarterly pipeline.
Architecture Overview
Configured quarterly SQL datasets move through metadata-driven Azure Data Factory orchestration into an ADLS Gen2 Parquet Landing area. Azure Databricks validates and writes Raw Delta tables, registers them through Unity Catalog and returns a structured result that ADF validates before final success logging or watermark movement.
- 01
SQL Source Systems
Configured quarterly SQL tables provide the source datasets.
- 02
Metadata-Driven ADF Orchestration
Azure Data Factory reads framework configuration and coordinates the quarterly source-to-Landing process.
- 03
ADLS Gen2 Landing
Source datasets are written to a dynamically configured Landing location in Parquet format.
- 04
Databricks Raw Processing
The Landing-to-Raw child pipeline invokes the Databricks notebook to validate source data, standardise metadata and create Raw Delta output.
- 05
Raw Delta Layer
Validated quarterly datasets are stored as Delta tables using partition-aware overwrite behaviour.
- 06
Unity Catalog Integration
Raw Delta tables are created or refreshed through Unity Catalog for controlled discovery and downstream consumption.
- 07
ADF Result Validation
ADF parses the structured notebook result and confirms successful processing before continuing.
- 08
Framework Logging and Watermark
Separate master and Raw-stage logs capture execution status, while the watermark updates only after Raw validation succeeds.
- 09
Data Science Consumption
Downstream Data Science and curation notebooks read Raw Unity Catalog tables using standardised quarter_code metadata.
Key principle: The quarterly pipeline is considered successful only after Landing processing, Raw Databricks execution and ADF-side result validation complete successfully.
Quarterly Source-to-Raw Lakehouse Architecture
Quarterly End-to-End Data Flow
Run Quarterly Prechecks
Verify required runtime configuration and processing prerequisites.
Read Framework Metadata
Retrieve source, target, table, execution and watermark configuration.
Copy Source Tables to Landing
Dynamically copy selected SQL tables into ADLS Gen2 Landing in Parquet format.
- Output
- One-table or all-table execution
Invoke Raw Processing
Execute the Landing-to-Raw child pipeline after Landing processing succeeds.
Start Raw-Stage Logging
Create or update the Raw Processing Log before notebook execution.
Validate Runtime Parameters
Validate required notebook input parameters and execution mode.
Validate Landing Path and Files
Confirm the Landing path exists and discover applicable Parquet files.
Validate Table Selection
Confirm that the requested one-table or all-table execution is valid.
Read Landing Data
Read the configured Parquet files using PySpark.
Validate Input Data
Check for readable data, empty datasets and a usable schema.
Standardise Quarter Metadata
Convert the source period into the trusted quarter_code.
- Input
- YYYY03, YYYY06, YYYY09 or YYYY12
- Output
- YYYY01, YYYY02, YYYY03 or YYYY04
Enrich Raw Metadata
Add approved processing metadata required by downstream consumers while preserving required source columns.
Write Raw Delta Tables
Write the applicable quarter partition using partition-aware overwrite behaviour.
Create or Refresh Unity Catalog Tables
Make the Raw Delta outputs available through Unity Catalog.
Validate Raw Output
Validate row counts, quarter partitions, Delta output and version information.
Return Structured Notebook Result
Return structured success JSON or structured failure output.
- Output
- status and failed_count
Validate the Result in ADF
ADF evaluates the structured result before pipeline continuation.
- Decision
- Continue only when status = SUCCESS and failed_count = 0
Update Raw-Stage Log
Record Raw success, result-validation failure or notebook failure.
Update the Watermark
Advance the watermark only after successful Raw result validation.
Complete Master Logging
Record final quarterly pipeline success after the watermark update.
Make Raw Tables Available to Data Science
Expose validated Unity Catalog Raw tables for downstream notebook processing.
Quarterly ADF and Databricks Execution Sequence
Databricks Landing-to-Raw Processing
Runtime Validation
Validate required notebook parameters and one-table or all-table execution modes.
- Stops controlled processing for invalid input.
Landing Validation
Validate the Landing path and discover applicable source files.
- Confirms the selected table filter.
Table Classification
Apply required processing behaviour for configured table groups.
Parquet Validation
Confirm files can be read and contain usable records and schema.
- Checks empty datasets.
- Confirms a usable schema exists.
Quarter Standardisation
Derive quarter_code from the source period and use it as the trusted downstream period key.
Metadata Enrichment
Add standardised quarter_code and approved processing metadata while preserving required source columns.
Delta Writing
Write Raw Delta output using partition-aware overwrite for the applicable quarter.
- Preserves unrelated quarter partitions.
- Does not use MERGE or upsert terminology.
Unity Catalog Handling
Create or refresh the Raw Unity Catalog table and validate its Delta output reference.
Output Validation
Validate output row counts, expected quarter partition, Delta version information and Raw table availability.
Structured Completion
Return structured success JSON and controlled failure information.
- Includes failed_count for ADF validation.
My Contributions
Contributed to and completed the end-to-end implementation across Azure Data Factory and Azure Databricks.
Completed
- Understood and enhanced the existing ADF and Databricks implementation.
- Improved the metadata-driven Landing copy process.
- Replaced hardcoded target handling with metadata-controlled values.
- Integrated Databricks Raw processing into the ADF master pipeline.
- Built and hardened the Landing-to-Raw PySpark notebook.
- Added runtime, path, source-file, schema, partition and row-count validation.
- Implemented structured notebook success and failure output.
- Added ADF-side result validation.
- Added separate Raw-stage framework logging.
- Resolved framework-log schema and duplicate-key issues.
- Implemented rerun-safe log-update behaviour.
- Protected watermark movement behind successful Raw validation.
- Standardised quarter_code for downstream Data Science processing.
- Supported Data Science integration with the Raw Unity Catalog layer.
- Built the historical migration framework.
- Completed guarded dry-run validation for 112 historical operations.
- Prepared technical documentation and implementation handoff material.
Metadata-Driven Landing Processing
Landing target behaviour comes from framework configuration instead of hardcoded pipeline values.
Implemented
- Target location information is supplied dynamically.
- The master pipeline passes target values into reusable child activities.
- Sink and cleanup datasets use controlled parameterised target configuration.
- One-table and all-table modes are supported.
- Controlled parameters support forced-copy or rerun scenarios.
- Environment-specific values can change without rewriting core orchestration.
The Landing process became more reusable and easier to configure across different environments.
ADF and Databricks Result Contract
Notebook activity completion alone is not treated as proof that all Raw table processing succeeded. ADF evaluates a structured result before allowing the pipeline to advance.
Validated
Success Conditions
All conditions must pass.
- Notebook activity completes.
- Result payload is available.
- status equals SUCCESS.
- failed_count equals 0.
Failure Conditions
Any condition blocks continuation.
- Notebook execution fails.
- Result payload is missing or cannot be interpreted.
- status is not SUCCESS.
- failed_count is greater than 0.
- A required validation result is absent.
Pipeline Behaviour
Invalid Raw completion is handled as a controlled failure.
- Stop the Raw flow.
- Record the appropriate failure status.
- Do not update the watermark.
- Do not write final master success.
Stage-Level Framework Logging
The quarterly orchestration maintains separate framework-log records for the overall master pipeline and Raw processing stage.
Implemented
- Removed unsupported log-column usage.
- Separated Raw-stage logging from master-stage logging.
- Corrected framework-identifier resolution.
- Resolved unique-key conflicts.
- Added rerun-safe update behaviour.
- Captured the structured Databricks result where appropriate.
Quarterly Master Log
Tracks overall run start, applicable master-stage failures and final success.
Raw Processing Log
Tracks Raw started, Raw succeeded, Raw result validation failed and Raw notebook failed.
Watermark Protection
The watermark represents data that successfully reached and passed validation in the Raw layer.
Implemented
- Landing Copy Success
- Raw Databricks Success
- Raw Result Validation Success
- Watermark Update
- Master Success Logging
- Watermark does not update after notebook failure.
- Watermark does not update after invalid notebook output.
- Watermark does not update when failed_count is greater than zero.
- Final success logging occurs after the watermark step.
- Reruns use the last successfully committed watermark.
Transactional atomicity across ADF, Databricks and the framework database is not claimed.
Result Validation and Watermark Protection
Quarter Metadata Standardisation
Month-based quarterly markers are converted into a stable year-and-quarter quarter_code used as the trusted key for period-based joins and calculations.
Implemented
Quarter Mapping
March maps to Quarter 1, June to Quarter 2, September to Quarter 3 and December to Quarter 4.
- Stored examples: YYYY01, YYYY02, YYYY03, YYYY04.
- Raw tables are enriched with quarter_code.
Downstream Consistency
An inconsistent business period label was replaced by standardised quarter_code for period-based processing.
Data Science Enablement
The implemented Raw layer provides standardised, validated and Unity Catalog-registered datasets for downstream Data Science and curation notebooks.
Validated
- Stable Raw Delta table access.
- Consistent quarter_code metadata.
- Quarter-based joins and period filtering.
- Required source-column preservation.
- Row-count and partition validation.
- Historical and quarterly data compatibility.
- Review of downstream notebook dependencies.
- Resolution of the inconsistent quarter-label dependency.
The contribution focused on preparing and validating the Raw data contract and supporting downstream notebook integration, not developing the complete Data Science model.
Historical Migration Framework
A separate historical migration framework was completed to support controlled one-time backfill processing without affecting the regular quarterly pipeline.
Completed
Validated Scope
8 historical periods × 14 tables = 112 validated table-period operations.
Completed Framework
Manifest-driven processing, source-file discovery, period and table validation, historical Landing and Raw processing, metadata handling, Delta validation, Unity Catalog validation, dry-run planning and guarded execution controls.
The historical migration framework was completed and validated through a guarded dry run covering all 112 operations. This does not state that all historical business data was loaded.
Data Quality and Validation
Runtime Parameter Validation
- Purpose
- Prevent incomplete notebook execution requests.
- Implementation
- Validate required runtime values before source processing.
- Failure handling
- Return controlled failure without processing data.
Landing Path Validation
- Purpose
- Confirm the configured Landing location is available.
- Implementation
- Validate the Landing path before reading data.
- Failure handling
- Stop Raw processing when the path is unavailable.
Source File Discovery
- Purpose
- Confirm expected Parquet inputs are available.
- Implementation
- Discover files according to table filter and run mode.
- Failure handling
- Return controlled missing-input failure.
Table Filter Validation
- Purpose
- Prevent an unsupported table selection from running.
- Implementation
- Validate one-table or all-table execution input.
- Failure handling
- Reject unsupported selections.
Parquet Read Validation
- Purpose
- Detect unreadable Landing input.
- Implementation
- Attempt the configured Parquet read.
- Failure handling
- Return controlled failure output.
Empty Data Validation
- Purpose
- Prevent empty input from being treated as a successful Raw load.
- Implementation
- Check that the DataFrame contains usable records.
- Failure handling
- Stop the applicable table operation.
Schema Validation
- Purpose
- Confirm a usable schema before Delta writing.
- Implementation
- Validate the DataFrame schema after reading Parquet.
- Failure handling
- Stop invalid processing without claiming strict contract comparison.
Quarter Partition Validation
- Purpose
- Confirm output contains the expected standardised quarter.
- Implementation
- Validate the quarter_code partition after writing.
- Failure handling
- Report the table operation as failed.
Row-Count Validation
- Purpose
- Confirm Raw output contains expected processed records.
- Implementation
- Capture and validate counts according to notebook logic.
- Failure handling
- Report validation failure without claiming complete reconciliation.
Delta Output Validation
- Purpose
- Confirm a valid Delta output was written.
- Implementation
- Validate Delta table or version state after processing.
- Failure handling
- Return a failed table result.
Structured Result Validation
- Purpose
- Prevent ADF from advancing when table processing fails.
- Implementation
- Check status and failed_count in the returned result.
- Failure handling
- Stop the flow and retain the prior watermark.
Monitoring and Failure Handling
Master-Stage Logging
Track the overall quarterly run.
Behaviour: Record start, final success and applicable master-stage failures.
Raw-Stage Logging
Identify Raw processing failures separately.
Behaviour: Record Raw start, success, result-validation failure and notebook failure.
Structured Notebook Failure
Return controlled error information to ADF.
Behaviour: Generate structured failure output for notebook error scenarios.
ADF Result Validation
Prevent false success after failed table operations.
Behaviour: Validate status and failed_count.
Invalid Output Handling
Stop processing when notebook results cannot be interpreted.
Behaviour: Record failure and prevent watermark movement.
Rerun-Safe Logging
Avoid duplicate-key failure during repeated execution.
Behaviour: Use corrected update behaviour for existing run or stage records.
Watermark Protection
Prevent data being marked processed before Raw completion.
Behaviour: Update the watermark only after successful ADF-side result validation.
Rerun and Recovery
One-Table Rerun Support
Allows controlled processing or testing of one configured table.
All-Table Quarterly Execution
Supports the complete quarterly set of 14 configured tables.
Forced-Copy Handling
Supports controlled re-copy behaviour where configured.
Rerun-Safe Framework Logging
Prevents log unique-key conflicts from blocking a repeated run.
Partition-Aware Raw Overwrite
Reprocesses the applicable quarter partition while preserving unrelated partitions.
Watermark After Raw Success
Updates the watermark only after Raw processing and validation succeed.
Structured Failure Context
Helps ADF distinguish successful and failed Raw processing.
Separate Historical Migration
Keeps one-time backfill execution separate from regular quarterly processing.
Controlled Reprocessing
Supports controlled table or quarter reprocessing based on notebook and framework state.
The implementation provides rerun-safe logging, protected watermark movement and partition-aware Raw processing. Complete transactional atomicity across ADF, Databricks and the framework database is not claimed.
Key Design Decisions
- 01
Integrate Raw Processing into the Master Flow
Landing completion alone does not mean the quarter is ready for downstream consumption.
- 02
Use Metadata-Driven Landing Targets
Configuration-driven target values improve environment reuse and maintenance.
- 03
Maintain Separate Master and Raw Logs
Stage-level logging makes failure location and recovery status clearer.
- 04
Require Structured Notebook Output
ADF needs an explicit processing contract rather than activity completion alone.
- 05
Validate Results in ADF
A notebook may complete technically while reporting failed table operations.
- 06
Update Watermark After Raw Validation
This prevents unsuccessful Raw processing from being treated as completed.
- 07
Standardise quarter_code in Raw
Downstream joins and period calculations require a stable period key.
- 08
Use Partition-Aware Delta Overwrite
Quarter reruns should avoid uncontrolled replacement of unrelated partitions.
- 09
Separate Historical Migration
One-time backfill requirements differ from the continuing quarterly schedule.
- 10
Use Guarded Dry-Run Validation
Every configured period-table combination can be validated before controlled migration execution.
Technical Challenges Solved
Integrating Existing ADF and Databricks Components
Landing and Raw processing needed to behave as one controlled quarterly workflow.
Approach: Add the Raw child pipeline and validation gates to master orchestration.
Making Notebook Completion Machine-Readable
Activity completion alone did not describe table-level outcomes.
Approach: Return structured success JSON and structured failure output.
Parsing Databricks Output in ADF
ADF expressions needed to handle notebook results reliably.
Approach: Standardise and validate status and failed_count.
Avoiding Premature Watermark Movement
Advancing after Landing but before Raw success could skip failed data on the next run.
Approach: Place watermark update after Raw output validation.
Resolving Framework-Log Conflicts
Repeated execution produced key or update conflicts.
Approach: Separate stage logging and implement rerun-safe update behaviour.
Standardising Quarter Semantics
Business period labels were inconsistent around year-end processing.
Approach: Introduce quarter_code as trusted Raw metadata.
Validating Multiple Tables Consistently
The notebook supports selected-table and complete-quarter processing.
Approach: Add table-filter validation and structured per-table results.
Protecting Historical and Quarterly Workflows
Backfill execution should not interfere with recurring runs.
Approach: Use a separate manifest-driven historical framework with guarded dry-run controls.
Outcome and Demonstrated Value
The completed implementation provides a controlled quarterly path from metadata-driven SQL ingestion to validated Raw Delta tables. Azure Data Factory advances the watermark and records final success only after Databricks processing completes and its structured result passes validation.
- Controls quarterly source-to-Raw orchestration through metadata-driven ADF processing.
- Validates structured Databricks results before ADF records success.
- Delivers partition-aware Raw Delta tables registered through Unity Catalog.
- Separates master and Raw-stage logging while protecting watermark movement and reruns.
- Standardises quarter_code for downstream processing.
- Supports one-table and all-table execution for recurring quarter loads.
- Validates 112 historical operations through an 8-period by 14-table guarded dry run.