Azure Data Factory · Metadata-Driven Ingestion
Metadata-Driven Multi-Source Ingestion with Azure Data Factory
A reusable ingestion pattern that reads source configuration from metadata, selects the appropriate extraction path and lands data into a consistent ADLS Gen2 folder structure.
The project focuses on replacing source-specific hardcoded pipelines with configuration, parameters, dynamic iteration and watermark-based incremental processing.
Project Overview
- Project type
- Hands-on data engineering implementation
- Primary platform
- Azure Data Factory
- Source types
- SQL Server, HTTP or REST endpoints and file-based sources
- Configuration source
- Metadata stored in ADLS Gen2
- Orchestration approach
- Lookup, ForEach, conditional routing and parameterised pipeline activities
- Incremental processing
- Watermark-based loading
- Primary target
- ADLS Gen2
- Target organisation
- Source-based and date-based folder structure
- Operational controls
- Execution logging, retry handling and failure notifications
- Notification integration
- Azure Logic Apps
- Primary focus
- Reusable ingestion, source onboarding and incremental data movement
Problem to Solve
Engineering problem
Building a separate pipeline for every source leads to duplicated activities, repeated configuration and increasing maintenance effort. A reusable ingestion design was needed so that source-specific behaviour could be controlled through metadata rather than repeated pipeline logic.
- SQL, HTTP or REST and file-based sources require different extraction behaviour.
- Static pipelines repeat connection, path and activity configuration.
- New source onboarding should require configuration changes rather than a complete pipeline rebuild.
- Incremental SQL ingestion requires reliable watermark tracking.
- Target folder naming should remain consistent across sources.
- Failures and execution results need to be visible for troubleshooting.
- Transient failures should not always require immediate manual reruns.
- Source-specific details should remain separate from reusable orchestration logic.
Constraints
- Different source types do not use identical extraction methods.
- Incremental loading applies only where a reliable watermark column is available.
- Metadata must contain enough information to configure the relevant source path.
- Watermarks should advance only after successful ingestion.
- Invalid or incomplete metadata should not trigger uncontrolled pipeline execution.
- Credentials and secrets should not be stored directly in metadata files.
Architecture Overview
The solution separates source configuration from pipeline orchestration. Azure Data Factory reads metadata from ADLS Gen2, iterates through configured source definitions and selects an extraction path based on source type. Data is landed in a consistent ADLS Gen2 structure, while watermark and execution details support incremental processing and operational review.
- 01
Source Systems
Configured source systems provide relational, API or file-based input data.
- 02
Metadata Configuration
A metadata file in ADLS Gen2 stores the source-level information needed to control pipeline behaviour.
- 03
Pipeline Orchestration
Azure Data Factory reads configurations and iterates through the configured sources.
- 04
Source-Type Routing
Conditional logic selects the appropriate extraction path based on the configured source type.
- 05
Incremental Control
For supported relational sources, the pipeline retrieves the previous successful watermark and extracts records beyond that value.
- 06
ADLS Landing
Extracted data is written into a consistent folder layout organised by source and processing date.
- 07
Logging and Notification
Execution details are captured for monitoring, while failures can invoke a notification flow.
Key principle: Metadata controls source-specific ingestion behaviour while reusable Azure Data Factory orchestration manages iteration, routing, incremental loading and target organisation.
Metadata-Driven Multi-Source ADF Ingestion
End-to-End Data Flow
Read Source Configuration
The pipeline reads metadata stored in ADLS Gen2 to identify configured sources and their ingestion settings.
- Output
- Configured source definitions
Iterate Through Configured Sources
A ForEach activity processes each metadata record and passes source-specific values into reusable pipeline components.
Determine the Source Type
Conditional routing selects the appropriate extraction logic for SQL, HTTP or REST, or file-based ingestion.
- Decision
- SQL source → Query-based extraction; HTTP or REST source → API extraction; file source → File copy path.
Retrieve the Previous Watermark
For incremental sources, the pipeline retrieves the last successful watermark value before constructing the extraction query.
- Input
- Source identifier and configured watermark column
- Output
- Previous successful watermark
Extract Source Data
The pipeline uses metadata values and reusable datasets to extract SQL, HTTP or REST, or file-based source data.
Land Data in ADLS Gen2
Extracted data is written to a consistent target structure organised by source and processing date.
- Output
- Conceptual folder convention: /source_name/yyyy/mm/dd/data
Capture Execution Information
The pipeline records source context, run status and error information required for monitoring and troubleshooting.
Update the Watermark
After a successful incremental load, the stored watermark is updated to the latest successfully processed value.
- Operational purpose
- Do not advance the watermark when the corresponding ingestion fails.
Send Failure Notification
When a configured failure path is triggered, Azure Logic Apps sends an operational notification containing relevant pipeline information.
Technical Implementation
Metadata Configuration
Source-specific behaviour is stored in configuration rather than repeated inside separate pipelines.
- Metadata is stored in ADLS Gen2.
- Source values are read dynamically at runtime.
- The configuration identifies how each source should be processed.
- New source definitions can reuse the same orchestration pattern.
- Sensitive credentials are not stored directly in the metadata.
Parameterised Orchestration
Reusable pipelines and datasets receive source, target and load settings through parameters.
- Supported linked-service, dataset or activity properties can be configured dynamically.
- Source paths and object names are supplied from metadata.
- Target paths are constructed consistently.
- Repeated hardcoded activity definitions are reduced.
Source Iteration
A ForEach activity processes the configured source definitions.
- Each iteration receives one source configuration.
- Dynamic expressions reference the current metadata item.
- The same orchestration pattern supports different configured sources.
- Source-specific failures can be captured with contextual information.
Conditional Source Routing
The pipeline chooses extraction behaviour based on the source type.
- SQL sources use query-based extraction.
- HTTP or REST sources use the configured API path.
- File sources use the relevant source dataset and file path.
- Unsupported source types should not be processed silently.
Watermark-Based Incremental Loading
Incremental SQL ingestion uses the last successful watermark to restrict the next extraction.
- The previous watermark is retrieved before extraction.
- The source query selects records beyond the stored value.
- The current successful maximum becomes the next watermark.
- The watermark should update only after the load succeeds.
- Full-load sources do not require watermark filtering.
Consistent Target Organisation
Data is landed in ADLS Gen2 using a predictable source and date-based structure.
- Source-specific folders improve discoverability.
- Processing dates provide basic run organisation.
- Parameterisation avoids hardcoded target paths.
- Folder naming remains controlled through metadata and expressions.
Logging and Operational Visibility
Pipeline execution details are recorded to support monitoring and troubleshooting.
- Run identifiers can provide traceability.
- Source context helps identify the failed configuration.
- Status and error details support investigation.
- Activity output can provide row information where the configured activity exposes it.
Retry and Notification Handling
Transient activity failures may use configured retry behaviour, while failure paths trigger operational notification through Azure Logic Apps.
- Retry settings apply to appropriate activities.
- Permanent failures remain visible after retry exhaustion.
- Logic Apps receives pipeline or activity context.
- Notifications support manual review and corrective action.
Metadata Design
The metadata layer separates configurable source behaviour from reusable pipeline logic. Each configuration record represents a source or ingestion object and supplies the values required by the orchestration.
Source identification
Identifies the source, source type and object or endpoint required by the relevant extraction branch.
Connection and path information
Supplies approved connection references and source or target path values without storing credentials directly.
Load behaviour
Describes whether the configured path uses full or incremental loading and, where applicable, which watermark behaviour is required.
Operational behaviour
Provides the processing context required for reusable orchestration and source-level troubleshooting.
These groups describe metadata responsibilities rather than claiming an exact field schema. Passwords, access keys, tokens and connection secrets are not stored inside the metadata file.
Data Quality and Validation
Supported Source Type
- Purpose
- Prevent an unknown source type from being processed through an incorrect branch.
- Implementation
- Compare the configured source type with the supported SQL, HTTP or REST and file values.
- Failure handling
- Route unsupported configurations to a controlled failure path.
Watermark Validity
- Purpose
- Ensure incremental extraction uses a valid previous successful value.
- Implementation
- Retrieve the stored watermark before building the incremental source query.
- Failure handling
- Do not update the watermark when extraction or target writing fails.
Target Path Consistency
- Purpose
- Maintain predictable landing locations across sources.
- Implementation
- Build the ADLS folder path through parameterised expressions and controlled metadata values.
- Failure handling
- Fail the affected load when the target path cannot be constructed safely.
Security and Access Considerations
Secret Separation
Keep credentials outside pipeline expressions and metadata configuration.
Behaviour: Connections use secure Azure Data Factory linked-service mechanisms rather than embedding secrets inside metadata.
The implementation separates source configuration from credential handling. Additional environment-specific identity and network controls would be applied during deployment.
Secure Linked Services
Control connectivity to SQL Server, ADLS Gen2, HTTP endpoints and other configured sources.
Behaviour: Linked services contain or reference the required connection settings.
Monitoring and Failure Handling
Execution Logging
Record which source configuration ran and whether it completed.
Behaviour: Capture source context, run status and relevant timestamps or activity outputs.
Error Context
Make failures easier to associate with the relevant source.
Behaviour: Include the source name or metadata identifier with captured error information.
Retry Handling
Reduce manual intervention for transient failures.
Behaviour: Apply activity retry configuration where appropriate. Retry does not resolve invalid metadata, authentication failures or permanent source errors.
Failure Notification
Provide visibility when a pipeline or source iteration fails.
Behaviour: Invoke the configured Azure Logic Apps notification flow with relevant pipeline context.
Unsupported Configuration Handling
Prevent unknown metadata values from following an incorrect processing path.
Behaviour: Fail the affected source configuration with an understandable error rather than silently defaulting to another source type.
Watermark Protection
Prevent records from being skipped after a failed incremental load.
Behaviour: Update the watermark only after the corresponding extraction and landing operation succeeds.
Rerun and Recovery Considerations
Watermark-Based Restart
The last successful watermark provides the lower boundary for the next incremental extraction.
Watermark Update After Success
The watermark should advance only after the target write completes successfully.
Source-Level Context
Logging the source name or metadata identifier helps identify which configured item requires rerun or correction.
Stable Target Organisation
Consistent target paths make it easier to locate output created by a specific source and processing date.
Retry for Transient Failures
Configured retries can reattempt temporary connectivity or service issues.
Full-Load Rerun Considerations
A full-load rerun may require target overwrite, cleanup or run-specific folder handling depending on the configured sink behaviour.
Duplicate-Risk Consideration
Watermark loading reduces repeated extraction, but duplicate prevention also depends on target-writing behaviour and business-key handling.
The design supports controlled rerun behaviour, but complete idempotency depends on the sink mode, watermark transaction handling and treatment of partial output.
Key Design Decisions
- 01
Separate Configuration from Orchestration
This reduces duplicated orchestration and makes source behaviour easier to change centrally.
Trade-off: Metadata quality becomes important because invalid configuration can affect runtime behaviour.
- 02
Use One Reusable Iteration Pattern
The same orchestration can be reused for multiple source definitions.
Trade-off: Source-specific exceptions must be handled through configuration or controlled branches.
- 03
Route by Source Type
SQL, HTTP or REST and file sources require different connection and extraction behaviour.
- 04
Use Watermarks for Supported Incremental Loads
This avoids re-extracting the complete source for every scheduled run.
Trade-off: The selected watermark column must be reliable and late-arriving records need deliberate handling.
- 05
Update Watermarks Only After Success
Advancing early could cause records to be skipped after a failed load.
- 06
Apply a Consistent ADLS Folder Pattern
Predictable storage improves discoverability and simplifies downstream processing.
- 07
Keep Secrets Outside Metadata
Metadata should control behaviour without exposing connection secrets.
- 08
Add Logging and Notifications
Dynamic pipelines are difficult to troubleshoot when the failed metadata item is not visible.
Challenges and Implementation Considerations
Supporting Different Source Structures
SQL sources, APIs and files do not share the same extraction properties.
Approach: Use common orchestration with source-type-specific branches.
Designing Useful Metadata
The metadata needed enough information to control runtime behaviour without becoming difficult to maintain.
Approach: Separate source identity, connection references, load settings and target values into clear configuration responsibilities.
Constructing Dynamic Expressions
Dataset properties, queries and target paths needed to change for each metadata item.
Approach: Use pipeline parameters, dataset parameters and current-item expressions inside the ForEach activity.
Managing Watermarks Safely
An incorrectly updated watermark could either duplicate data or skip records.
Approach: Read the previous successful value before extraction and update it only after successful landing.
Handling Heterogeneous Failures
Authentication, network, metadata and source-query failures require different corrective actions.
Approach: Capture source context, retain error details, apply retries only where appropriate and send failure notifications.
Avoiding Hardcoded Target Paths
Static folder paths reduce reuse and can produce inconsistent storage layouts.
Approach: Construct target paths from approved metadata responsibilities and date values.
Balancing Reuse and Source-Specific Logic
Too much generic behaviour can make exceptional source requirements difficult to support.
Approach: Keep common orchestration reusable while isolating necessary source-type differences in controlled branches.
Late-Arriving or Updated Source Records
A simple timestamp watermark may not capture all late-arriving or backdated changes.
Approach: Document this as a design consideration and avoid claiming that the basic watermark pattern solves every incremental-load scenario.
Outcome and Demonstrated Value
The completed implementation demonstrates how Azure Data Factory can use metadata, parameters and dynamic iteration to process different configured sources without building a separate orchestration pipeline for each one.
- Separates source configuration from reusable pipeline logic.
- Supports SQL, HTTP or REST and file-based extraction patterns.
- Uses metadata-driven iteration with parameterised pipelines and datasets.
- Applies watermark-based incremental loading for supported relational sources.
- Organises landed data through a consistent ADLS Gen2 folder convention.
- Captures execution context with retry and notification paths for monitoring.
Potential Extension
Potential Improvements
- Add formal metadata-schema validation before source execution.
- Move configuration to governed SQL control tables when transactional updates or richer operational management are required.
- Add source-to-target row-count reconciliation.
- Add checksum or key-level validation for selected datasets.
- Introduce explicit audit tables for pipeline and object-level runs.
- Add environment-specific deployment parameters.
- Add dead-letter or rejected-configuration handling.
- Support configurable full-load overwrite and append behaviour.
- Improve late-arriving data handling for timestamp watermarks.
- Add alert severity and notification routing based on failure type.
- Add automated tests for metadata records and dynamic expressions.