Optimizing Large-Scale ETL Pipelines Using Medallion Architecture on Azure Data Lake

The medallion architecture applied to large-scale ETL pipelines and Azure Data Lake is examined. The Azure-specific aspects of medallion architecture are deciphered for improved design, operation, and governance of ETL pipelines. Bronze-layering covers data ingestion and retention, storage organization, naming conventions, schema-on-read and schema-on-write trade-offs, and accessibility by non-technical users. The design of the silver layer focuses on cleansing, standardization, deduplication, conformance, and data enrichment. Finally, gold-layering deals with the creation of data products—curated datasets that are cataloged, ready to be consumed by visual-analysis tools, and offered through published APIs. The work centers on using medallion architecture to tackle the challenges of ETL pipelines running in Azure Data Lake. Such pipelines face increasing volumes and complexities, including new data sources, customer mandates for improved product quality, stricter regulatory requirements, and the need for customer-oriented APIs. The analysis is further filtered by success criteria expressed in data quality, observability, and monitoring för the whole medallion architecture, and data-organizational strategy for the silver and gold layers with specific focus on the definition and creation of data products.

As data volumes soar and processing capabilities multiply, organizations find themselves grappling with increasingly complex data transformation workflows. This complexity can often be ameliorated through the practical implementation of a Medallion architecture on Azure Data Lake, a well-established design pattern for data pipelines with origins in Data Lake development. While for many Data Lake use cases, self-service data access is the ultimate objective, such access can be impeded by issues such as erratic data availability and poor data quality. An examination of the Medallion architecture from both the theoretical literature and practical implementation in Azure Data Lake environments can illuminate approaches for optimizing operations in the Bronze, Silver, and Gold stages [1].

The generic characteristics of the Medallion architecture within a cloud-based Data Lake establish a foundation for a more specific analysis of how Azure Data Lake service systems can be designed, built, and operated to drive continued automation, reduced costs, and improved Data Governance capabilities over time. Core areas of focus include the organization of data within the Data Lake and adherence to consistent naming conventions; augmentation of data quality, lineage, and observability capabilities to enhance usability and compliance with external regulations; and provision of suitable Metadata to support robust Data Governance [2].

1.1. Scope and Objective

Medallion-driven optimization of ETL pipelines in Azure Data Lake is examined. Success is defined in terms of increased usability and quality of persisted data products, reduced operational costs, and improved governance. Research boundary encompasses pipelines implementing the medallion pattern and executing on Azure Data Lake, including external land whose contents are either stored on ADLS Gen2 or accessible externally via Data Virtualization Integration Services. Resulting guidelines and design enhancements can be contextually adapted to ELT pipelines or other data-processing solutions [3].

Practical implementation of an ETL pattern based on the Medallion Architecture on Azure Data Lake is presented as a series of concrete objectives driving design, operation, and governance of the overall solution. Stored data pipelines are subjected to Storage Organization and Naming Conventions that align physical layout, partition schemas, and naming rules with their semantic function. Data Quality, Lineage, and Observability are focused on monitoring and alerting at both the data and processing levels. A Metadata and Governance Strategy defines the metadata model and usage policies, addressing the cataloging and governance of all resources throughout their life cycle [4].

Figure 1

Medallion Architecture and Its Role in the Azure Data Platform

1.2. Background and Significance

Enterprise data lakes primarily enable the storage of large, often unstructured datasets that many different consumers, including non-data engineers, can analyze using a variety of tools of their selection, such as Power BI, Tableau, Cognos, R, and Python. Data in these lakes is often fresh during analysis, so it enables rapid ad-hoc queries without the management overhead of a data warehouse [5]. However, this fresh, easily interrogable data and the freedom of the end-user to analyze raw data can create third-party analysis where the results are of lower quality, produce different answers from the same question, and take longer to execute than necessary.

Unlike enterprises with a data warehouse, larger data lake-consuming enterprises have established policies determining how data should be structured in the data lake by datasource owners or the governance team, specifically for standard users wanting to minimize operational risk [6]. This ensures data quality and makes it simple to consume via a catalogue. To ensure topics rarely change, introduce many new columns, change the meaning of existing columns, or are slow and expensive to execute, data lake-tiering strategies such as the medallion architecture are implemented for large data pipelines that move data from one tier to another, instead of extracting it over and over again. Data quality checks are included, and any failure at any stage is clearly visible to all teams responsible for remediation, so users of the higher tiers do not need to worry about the data shape and quality [7].

Equation 1: End-to-end pipeline latency

Where:

• $T_B$= bronze-layer processing time
• $T_S$= silver-layer processing time
• $T_G$= gold-layer processing time
• $T_O$= orchestration overhead

Step-by-step derivation

1. The ETL pipeline is executed in stages: bronze, silver, gold.
2. Total pipeline completion time is the sum of the time consumed by each stage.
3. Orchestration itself also consumes time because scheduling, retries, and dependency handling are mentioned in the paper.
4. Therefore, total latency is the sum of all stage runtimes plus orchestration overhead.

The following section presents foundational background knowledge ensuring theoretical alignment with practical design decisions.

Data lakes are centralized repositories that allow the storage of structured and unstructured data at scale. The Medallion pattern is a popular approach for structuring data within data lakes. The raw or bronze layer, as shown in Figure 1, is typically used for ingestion and storage of source data [8]. As the name implies, the silver layer hosts a cleansed version of the data, permitting truthful and comprehensive analysis. The gold layer typically hosts data products that are ready for consumption, in the sense that they require little or no further processing to generate insights [9].

Quality and trust increase as data moves through the stages. At the same time, schemas become more rigid since the intention is for consumers to test, discover, and consume the data rather than enrich or clean it. As a result, the silver layer usually applies cleansing and conformance rules to reduce defects that require triaging in the gold layer. Enrichment, such as generating surrogate keys during the ETL process, speeds up the consumption of data products. Adding high-value context to the data also resides here. The gold layer, for its part, typically catalogs the assets in a data catalog or glossary to improve findability and enable usage analytics [10].

Azure Data Lake Storage Gen2 (ADLS Gen2) is a cloud-based native data lake that meets the needs of big data analytics workloads. Built on Azure Blob storage, ADLS Gen2 provides the massive scale and cost-effectiveness of object storage. In addition, ADLS Gen2 offers file system semantics, fine-grained access control, and can integrate seamlessly at analytical runtime with Azure Synapse, Azure Databricks, Azure HDInsight, and a wide range of ISV services [11]. Besides the above features, Gen2 includes a hierarchical namespace that supports the organization of files within directories and subdirectories, making it particularly useful for the implementation of the Medallion pattern.

ADLS Gen2 offers REST APIs for the efficient transfer of data to the cloud. For operational use cases that require high throughput, the Azure Data Box service speeds up data transfer. Gen2 uses Azure Active Directory (AAD) for identity management, which allows combined Azure role-based access control (RBAC) and access control list (ACL) protection [12]. These controls govern access to data in distributed and multitenant settings while helping enforce regulatory compliance. Other security features include data encryption, data access logging, managed private endpoints, and Service Endpoints for Azure Storage [13].

2.1. Data Lake Architectures and the Medallion Pattern

Three-layered data lake architectures organized in bronze, silver, and gold stages constitute a popular data-infrastructure pattern, known as the medallion architecture. In these designs, the quality of stored data improves from layer to layer, with the silver layer containing cleansed, conformed, and enriched data that preserves lineage information from the source systems, and the gold layer populated with ready-to-consume data products [14]. Although each medallion-layer implementation is successively more computationally expensive, the individual cost of processing stored data is often lower than that incurred by the ingestion process. Consequently, medallion pipelines are usually a mix of batch and streaming processing, optimized for storage consumption and processed in isolation at defined intervals [15].

The distinction between data quality and scale effectively frames the adoption of a medallion architecture. For each distinct source system or data-product type, spikes in the quantity of ingested data generally occur during business hours, and ETL user requirements for tools such as reports or analytics dashboards tend to be highly concentrated in specific trading-month windows [16]. Data garments undergoing enrichment, standardization, conformity, and deduplication are typically consumed by user-activated reports and dashboards, automated ML workloads, and data science OOS-flows, all of which contain their own peaks and deferred latency. As such, several store-and-purge Copernican moments exist in addition to the traditional data-native placements of Copernican constellations [17].

Figure 2

Medallion Architecture

2.2. Azure Data Lake Fundamentals

Azure Data Lake Storage (ADLS) Gen2 is an enterprise-grade service for big data analytics. Built on Azure Blob Storage, it combines the scalability and cost benefits of object storage with a hierarchical file system [18]. Complete data analytics solutions can be built using existing Azure services, including Azure Data Factory for orchestration; Azure Databricks, Azure Synapse, and Azure Machine Learning for data processing and machine learning; Power BI for data visualization; and Azure Key Vault for certificate and secret management. Security is built in at all levels, with role-based access control (RBAC), integration with Active Directory, storage account keys, shared access signatures, and Azure firewall support ensuring that data remains secure [19].

Microservices-based use cases are protected with authentication and authorization through Azure Active Directory (Azure AD) tokens. Fine-grain authorization of folder and file operations is possible via POSIX access control lists (ACLs) and access control lists support for Azure Blob Storage [20]. Data can also be grouped together according to its sensitivity by applying sensitivity labels to data in Azure Synapse, movement of sensitive data can then be governed and/or restricted through data loss prevention capabilities built into Microsoft Information Protection. The separation of storage and compute enables the scaling of processing without impacting storage and involves cost models that charge on a per-second basis for processing. Each decision regarding the location of physical data can be based on a combination of business and technical decisions and no longer needs to consider hardware restrictions or vendor imposed logical designs for a physical implementation [21].

2.3. ETL and ELT Paradigms in Large-Scale Environments

Enterprise ETL solutions have traditionally followed an extract-transform-load order of execution, as depicted in the upper part of Figure 8. Such workflows can be optimally executed in ready-made tools such as Informatica, IBM DataStage, Talend, Microsoft SSIS, and others, and are often conceived and developed without in-depth software engineering skills [22]. Given the fact that nontrivial transformations can require substantial coding effort and computation time, neglecting to store the results back into a persistent storage solution after the transformations is wasteful, as depicted in the latter part of the upper section of Figure 8. Transformation-only jobs, therefore, can benefit from using a data ingestion tool with a proper staging area and a fast, scalable engine such as Spark. ETL jobs that require materialization of their results must take this derivative into account when designing the mechanicals of the transformation job [23].

The ETL approach can also be put in the other order, ELT, where a staging hot storage also acts as a temporary data lake from which transformations are executed into downstream data mart solutions [24]. The data movement pattern of a pure ELT solution, with data landing by each data flow into its respective transformation solutions, resembles more that of the Medallion Architecture without the Silver/Hygiene layer. In particularly large-scale ELT environments it can make sense to decouple the staging area completely from the other data products and have the transformations dictate where the lakes go. However, it’s also important to keep in mind that transforming at the same time as loading is not scalability-friendly, since when the transformation pipeline keeps pace with the ingestion the same data is touched several times for no added benefit [25].

Equation 2: Incremental-processing workload reduction

Where:

• $N_{\mathrm{\Delta }}$= number of changed records processed incrementally
• $N_{\text{full}}$= number of records in a full reload
• $R$= processing ratio

Step-by-step derivation

1. In a full load, every record is processed.
2. In CDC/incremental mode, only changed rows since the last watermark are processed.
3. The fraction of work done is therefore changed-record count divided by full-record count.
4. If $R\ll 1$, incremental processing is much cheaper than full reload.

A related savings equation is:

The study of scalable enterprise ETL (or ELT) pipelines on Azure Data Lake provides valuable insights into design and operational optimizations for Medallion Data Lake architecture. The primary secondary findings at the bronze, silver, and gold levels address pipeline production quality monitoring and declarative definition of data quality [26].

The bronze level is where all data enters the data lake. Data products accept either non-persistent (directly from the source) or persistent (staging) data sources in various formats, although parquet is highly preferred due to its efficiency. Data are often transformed into parquet format before national stage storage [27]. A “schema-on-read” approach is generally adopted for all bronze level products, where data structure is not enforced on ingest. However, where feasible – particularly for dedicated persistent bronze sources – “schema-on-write” staging on consumable file formats is strongly recommended to improve data quality for enriched products. Products at the bronze level are typically subject to a tiering policy where data younger than a specified timeframe is retained [28].

A Medallion Data Lake approach fundamentally allows for a series of incremental stages leading to the production of cleansed, conformed, and enriched data. The major data quality and business rule sets defined for this study are applied at the silver level to individual cleansed data products [29]. The key quality rule categories include cleansing rules, standardization rules, deduplication rules, conformance rules, and enrichment rules. Cleansing rules address syntax-related issues such as strange characters or invalid datatypes in key dimensions. Standardization rules enforce consistency of codes within key dimensions [30]. Deduplication rules ensure that only the best entry for each key value in a dimension is retained. Conformance rules combine entries from multiple sources into a single conformed dimension table. Finally, enrichment rules augment dimensionally-conformed data products with relevant attributes from other sources [31].

3.1. Bronze Layer: Ingestion and Raw Data Management

The bronze layer of an ETL pipeline is responsible for ingesting data from heterogeneous source systems into a Azure Data Lake Storage (ADLS). Ideally, the process is applied to a specific set of data that needs to be staged in its original source format. Data can be ingested in both structured and unstructured formats, including text, images and video, although it is common to stage data only in structured formats. These unstructured data lakes are only relevant for machine learning workloads and testing, so ADLS policies can be used to control the additional cost incurred [32].

Once ingestion is complete, the data is made available through a schema-on-read approach that does not require a pre-defined schema on write. Having a schema defined on read rather than following a schema-on-write principle supports the flow of the Bronze layer and avoids writing data in a structured manner unless needed. These methods can only be applied when no data cleansing is performed as data verification at the ADLS landing zones is complicated or non-existent, with validation left to downstream consumers and future processes. Hence, any cleansing, deduplication or standardization logic should only take place in the Silver layer or beyond [33].

Ingestion frequency and retention policies are critical for the Bronze layer as Azure Data Lakes are often used to stage data and then deliver to destinations in structured formats. Secondary zones outside of the business-as-usual (BAU) ingestion process provide a sandbox for analysts and help reduce the footprint of the Bronze layer, dataframe on ADLS platform. Such retention policies must be managed from a federal perspective when working across multiple Azure Data Lakes globally [34].

Figure 3

Bronze Layer: Ingestion and Raw Data Management

3.2. Silver Layer: Cleansed, Conformed, and Enriched Data

The processes in the Silver layer change the internal quality of the data, making it fit for analytical purposes. It is assumed that the data is cleansed for data defects that are easy to detect and correct. The cleansing operations should be documented, as known errors will re-occur, and correcting those errors will only add extra costs for normally error-free data [35]. Data cleansing procedures can either be defined as rules for automated detection and correction, or be performed manually using business knowledge. Deduplication involves detecting and removing duplicate records. Conformance ensures that data from different sources behave in a common way, so that reliable analysis and reporting can be done across the different dimensions. Data from multiple sources can be enriched by linking it with related data through common identifiers or by matching similar records using unsupervised machine learning techniques. Enrichment is the process of combining data from multiple sources to add value [36]. The data have been examined and cleansed for unfit content and all cumulative rules for data characteristics have been enforced.

Closely related are data fusion techniques, which, given a number of records that refer to the same real-world entity, produce a single high-quality representative record. These processes can either be implemented using a rule-based approach or be performed by adopting machine-learning methods. The Silver layer is where these tasks are undertaken.

Equation 3: Data-quality score

Where:

• $q_i$= normalized score for each rule category
• $w_i$= weight for that category
• ${\sum }_{i=1}^5{w}_i=1$

Expanded:

Step-by-step derivation

1. The paper lists 5 quality rule categories.
2. Each category can be scored between 0 and 1.
3. Not all categories must have equal importance, so weights are assigned.
4. A weighted sum yields one overall quality score.
5. This score can be used at the silver validation gate before data enters gold.

3.3. Gold Layer: Curated, Ready-to-Consumer Data Products

The Gold layer contains data products created to satisfy the needs of specific stakeholders while observing external governance constraints. These products are defined in a data catalog, which helps consumers discover and understand them. Operations on the Gold layer may encompass simple publishing to distribution channels, CRUD API exposure for single-record consumption, interactive access for small-scale exploration, or even machine-learning model serving, among others [37].

The focus is on making data accessible and usable, thereby enhancing comprehension and productivity within the organization. Specific stakeholders may require semantically enriched versions of core datasets for focused domains, or data marts consolidated or summarized for performance-sensitive reporting. Various layers can be merged into a single API to optimize consumption. For complex Gold-layer definitions, a detailed analysis of the data product is recommended, considering the final consumers' use cases and consumption characteristics. Gold-layer creation may leverage caching and materialized view capabilities in the consumption layer, based on broader architectural decisions [38].

The Medallion Layering Pattern and Related Techniques Represent a Specialization of Data Engineering Best Practices, Enabling a Consistent Improvement in Both Quality and Governance on Azure Data Lake Solutions. Concrete Improvements for Design, Operation, and Governance of Medallion Pipelines [39].

The design and operation of medallion architectures on Azure Data Lake can benefit from established data engineering best practices commonly applied to data warehouses. The applicability and impact of these techniques can be assessed for the bronze, silver, and gold layers of large pipelines. During the design phase of any enterprise ETL or ELT process, one tries to address these questions that affect the success of the entire solution: how should the data be organized within the storage service? What naming conventions should be applied to all the objects created? What data quality checks need to be applied to ensure the reliability of data products? Which observability capabilities must be included to allow effortless monitoring and alerting? How should governance be implemented to ensure the data is easy to discoverand usable by the entire organization?

The decision around storage organization and naming conventions is key to guaranteeing an efficient and comprehensible solution. An improper layout (such as any flat or directory-less structure) can lead to performance issues during data consumption, whereas the absence of a clear, defined ambition for storage and naming can cause chaos within the environment over time. Data quality checks become critical to ensuring the usability of data products, especially if end consumers are not directly involved in the silver-layer processing stages. Consequently, automation becomes crucial to validating the data continuously and preventing faulty products from being released. Finally, observability capabilities enable proactive rather than reactive monitoring, empowering engineers to solve issues before end consumers are affected [40].

4.1. Storage Organization and Naming Conventions

Applying the medallion architecture to ETL processes in Azure Data Lake suggests a storage organization designed to group data by subject area or use case. Inside each area, a hierarchical folder structure helps manage large volumes and enable fine-grained access control when required. Partitions segregate data naturally according to ETL patterns or data use, while consistent naming schemes simplify understanding, increase discoverability, and support automation. Optimizing the structure improves efficiency and usability throughout the pipeline [41].

Each silver and gold table can be managed in its own folder within the medallion architecture. Deltas and other data staged for intake remain in staging folders, where Azure Data Lake Store supports role-based access control down to the storage account level, controlling user access to sensitive source data. For extreme volumes, a further layer may be introduced to provide quick access to tabular metadata and partition details, supporting additional access and reprocessing options. Snowflake’s pseudo-columns SYS$PARTITION_VALUES and SYS$PARTITION_PATH offer lightweight alternatives to creating small, frequently scanned DDL views listing valid partitions [42].

Figure 4

Storage Organization and Naming Conventions

4.2. Data Quality, Lineage, and Observability

Granting access to meaningful data is a long-term goal shared by all data providers. Quality assurance, traceability, and observability processes are also essential components of a successful Medallion Pipeline, and these features can be categorized into three main areas: data quality requirements, provenance and impact analysis, and observability and monitoring capabilities [43].

Every data product must meet an explicit set of quality rules that cover the standard requirements for the specific data asset, which can be distilled from the known internal and external business-use cases. The rule sets validation gates define how the rules are checked and the procedures for triaging defects. Many of the gates will operate in an automatically trigger/notify/fix mode, while other defects will require human intervention to assess their severity and either solve the problem or reject the incoming data product. In addition to monitoring the data products, the underlying ETL processes also require quality validation rules that focus on the state and behaviour of the processes themselves rather than the resulting data. These typically look for unexpected changes in the nature or volume of the ingested data. Whenever possible, any alerts raised should be actionable, prompting workflow restarts or pointing to relevant incident response documentation or playbooks [44].

Data products need to provide traceability of their provenance, that is, details of their production and transformation history. This information is an invaluable aid to consumers as it helps them understand the context of the data producers. It also aids in analysing unexpected changes in the data and their effect on consumption; by tracing backward, consumers can determine whether the changes originated from the data itself or the productizing processes. Provenance information should be captured at the finest level possible, linking the data product back to its raw data, associated quality codes, and all transformation steps leading up to the output [45].

Equation 4: Completeness metric

Where:

• $N_{\text{observed}}$= records actually ingested
• $N_{\text{expected}}$= records expected for that batch/window
• $C$= completeness

Step-by-step derivation

1. Completeness measures whether the expected data arrived.
2. If all expected records arrived, then $N_{\text{observed}}=N_{\text{expected}}$, so $C=1$.
3. If fewer records arrive, completeness falls below 1.
4. A threshold can be used for alerting, for example reject if $C<0.98$.

4.3. Metadata and Governance Strategy

A metadata model aligned with Azure Data Lake best practices comes into view at the gold layer. A Data Catalog hosted in Azure Purview provides essential lineage and other relevant attributes to facilitate usability and discovery. Purview’s scanning capability is complemented with custom metadata stores for specialized needs. A coherent logic model aids design of tagging schemes that signal sensitivity, governance level, and other exploitable traits. The roles and responsibilities for the Azure data estate shape the governance strategy. Policy-based data access rules materialize formal requirements, while audit capabilities drill deeper into use patterns. Compliance with regulations such as GDPR, HIPAA, and CCPA drives further considerations [46].

A data estate does not govern itself. Effective governance frameworks define the persons, processes, and technologies needed to efficiently manage information assets. The governance strategy supporting Azure Data Lake’s medallion ETL pattern evolves from a clear understanding of these key concepts, focused on an Azure Data Lake—data estate—Medallion architecture centered on Datalake ingestion—storage—processing governance in relation to data producers and consumers [47]. Within this context, a model describing the requirements, business functions, operations, services, data assets, information, metadata, knowledge, and technology supporting Governance best practices serves to identify the tooling and written artifacts required to maximize the benefits gained from Azure Datavault within the Data Lake [48].

The strategy’s focus on metadata and provenance helps build enough trust in the Data Lake for it to become the single source of truth for broad use across the enterprise by both business and data consumers, facilitating self-service BI from the Gold layer. Data Freud’s Metadata-Based Data Governance Framework supplies the formal drivers of Metadata management, Data Governance, and Data Cataloging for the Medallion pattern operating within the Azure Data Lake environment. The model consolidates its essential activities around metadata management, data catalogue functions, data governance, data privacy, and data security. Compliance drivers, ad-hoc project metadata, custom Golang protection, and purpose custom catalog tagging support complete the strategy [49].

Several optimizations spanning orchestration, scaling, and cost management have been proposed for Azure Data Lake medallion ETL pipelines. The underlying technical approach and corresponding evaluation framework are presented in this section [50].

Orchestration and Scheduling: Dependencies, retries, and SLA enforcement are managed by Azure Data Factory Data Flows and Azure Data Factory Pipelines, with the main flow orchestrated by Apache Airflow to enable parallelism across different source pipelines. Airflow triggers two Data Factory Pipelines, the first to handle the ingestion of transaction data from Amadeus into staging and the second for ingestions from the YouGov survey API and third-party FIFA data sources, while also recreating the testing databases in Azure Cosmos DB. DAG parameters allow re-ingestion of raw data into staging after validation failures or for playbook testing. Paths for processed transaction data can be copied alongside testing paths using a path suffix. Control tables define runs for BiQ and update the Azure Data Explorer materialized view. Incremental loading of new destination records for three data products is also supported by Airflow [51].

Parallelism, Partitioning, and Performance Tuning: To achieve low-latency runtimes, monkey-patching NumPy’s default .dot() and .matmul() methods with Numba-compiled binary functions of the same signature and decorated with Numba’s @njit routine is explored, achieving a speedup of over four times for a covariance matrix computation on a 1282 matrix. Partitioning transactional data by throughput balances small changes during months with low football activity against longer compute times for bursts of publications, while partitioning by bucket improves materialization runtimes for data products aggregating samples associated with operators [52].

Incremental Processing and Change Data Capture: Change-data capture design for monitoring and applying new records to data products using area-specific Azure Blob Storage archives and Azure SignalR Service APIs provides a footprint smaller than instrumentation-based alternatives, as the back-end query directly against Delta tables without modifying the SQL. Incremental updates also restore the release cadence for Another Level, a constant release project that consistently tests the same magical formula [53].

Equation 5: Timeliness / freshness metric

Where:

• $t_{\text{now}}$= current timestamp
• $t_{\text{last\_update}}$= timestamp of latest successful update
• = freshness lag

Step-by-step derivation

1. Timeliness depends on how old the most recent data is.
2. The age of data is current time minus the most recent successful update time.
3. Smaller $F$means fresher data.
4. SLA can be enforced as:

5.1. Orchestration and Scheduling

Azure Data Factory (ADF) serves as the orchestrator for the medallion pipelines. Managed and serverless, it abstracts infrastructure and scaling concerns, dynamically provisioning both Control and Data Integration Units as required. ADF provides a user-friendly interface for building ETL workflows, orchestrating the dispatch of tasks on different runtimes, tracking job status, and facilitating error handling, retries, and alerts. Each ADF pipeline defines a directed acyclic graph (DAG) of activities—the tasks executed—and captures dependencies between them. When executed, ADF inspects the DAG for active activities, passes necessary parameters, and assigns the workload to a linked Integration Runtime, which can run on Azure or on-premises [54].

Each activity can be retried automatically in the case of failure. Yet, despite its robust features, ADF lacks the ability to manage dependencies among calls to multiple ADF pipelines. This functionality is crucial when pipelining different steps of multiple data-loading routines. To address this limitation, DataOps tools were created, implementing a scheduling mechanism powered by Microsoft Azure Service Bus facilitating the sequential dispatch of DAGs and monitoring activity success and failure across the data-loading orchestrations [55].

DataOps uses ADF to deploy each pipeline individually, with monitoring, start, and stop controls throughout all steps and sections of the Data Lake ingest layer. Logs are systematically generated and persistently stored in a datalake-logging container. Dashboards provide customizable views of data and error checks, including the active status of the ingestion process. ADF monitors the data population in real time and can follow each dataset, identifying issues at a deeper level. Safety limits on the amount of ingested data from each source can be defined and controlled whenever needed [56].

5.2. Incremental Processing and Change Data Capture

Change Data Capture (CDC) minimizes processing workloads and enables real-time data transformation and consumption. Incremental processing captures updates since the last run using timestamps or hashes. Watermarking stores the last record in a separate table and retrieves it during the next run. All these techniques reduce processing cost and time and provide confidence in the correctness of the result. Data mutations made by data sources (e.g., insert/update/delete) are reflected in the ETL by filtering the fact tables and processing only incremental changes. Source mutations are recorded in a CDC system that maintains all historic states of the data to enable audit [57].

Separate staging containers are adopted: one for the ETL process and another for the CDC use case. The latter processes INSERT and DELETE changes in a single run; UPDATE and MERGE changes are handled in another staging container that applies a watermark mechanism. A watermark value is calculated for the fact, bridge, and main dimension tables for which updates may exist. Records from the CDC staging area greater than the watermark value are processed in the bronze stage of the ETL, and the watermark value is updated with the maximum watermark value present in the CDC data store. An additional trade-off exists in this case regarding the ETL design, where the ETL can be executed at a lower frequency but takes more time [58].

Complete pipelines yield ancillary data benefits, informed by demand monitoring, with architecture reliability established through a framework of quality gates and observability layers [59].

Data preparation pipelines like those operating in Azure Data Lake serve multiple audiences. Data scientists and business analysts consume these data products, while data engineers and operations teams wish to monitor their quality and reliability. These stakeholders will request information about data integrity, determining whether the data are complete and conforming to defined rules. They may also wish to flag data defects to the appropriate internal teams, enabling them to conduct root-cause analyses and monitor data quality fixes and issues with the ETL pipeline. Such requirements are often best met through a data quality framework [60].

The validation rules proposed for the silver layer help guarantee the technical and business requirements of a data quality framework. Five categories of quality checks have been defined:

1. consistency rule set: business and technical rules defined by the equivalent of user acceptance testing (UAT) on the cleansed data;
2. completeness rule set: key figures monitored through recurrence and trend analysis to check for missing content;
3. integrity rule set: elementary checks on referential integrity and duplicates;
4. standardization rule set: outlier detection, format, and missing value checks;
5. timeliness rule set: frequency checks for recency and staleness [60].

All five rule sets should be integrated into the validation framework. A validation gate should be implemented at the end of the silver pipeline, preventing any failures from entering the gold layer. Alerts can also be triggered automatically via an incident management platform, notifying the appropriate team about data quality defects according to a pre-agreed triage process.

The health of pipelines is a common concern in data engineering teams. To ensure reliable provenance data and monitoring of the silver layer, core observability concepts should be applied. Three key themes can be identified:

1. Collect observability metrics: data volume, recency, and other relevant pipeline parameters;
2. Expose tracing information to enable root cause analysis;
3. Build a visualization dashboard integrating the above metrics with other Azure observability sources, such as Store Analytics, Application Insights, and Azure Monitor [61].

Pipeline observability should be designed to meet one of three use cases:

1. Collection of observed volumes and recency for each pipeline write operation, allowing rapid identification of recency and staleness breaches;
2. End-to-end data flows monitor tracing, designed for failure debugging;
3. Comprehensive metrics integration, combining pipeline observability with other Azure services [62].

Recent ETL failures within the silver pipelines have positively impacted operational monitoring. Despite observations indicating a typical daily ingestion average of around 2 TB, the true daily data volume fluctuates significantly due to the irregular activity of upstream data sources across the different RDSZ databases. Consequently, this monitoring layer will be adapted to visualize Azure Monitor information alongside additional volume metrics, including recent trends and comparatives with past normal activity.

Streamlining how data provenance information is captured and made available within the data products will increase usability and adoption within the data consumer community. Data lineage support is a well-established practice in the world of data engineering, and appropriate mechanisms are already in place to store and visualize this information. However, native functionality within the medallion implementation remains limited to computational performance traces. Further integration is needed to expose that provenance information in the conformed data products [63].

User-friendly data products can also benefit from features that indicate how they can be used within other data models or analytical products. Such user stories convey how they can support other analytical processes, alongside considerations about data freshness. Analysis of the data products serving as source tables or views for other layers is therefore an important requirement of any data modeling in conformity with Kimball methodologies [64].

Figure 5

Derived fit of governance control models by scenario

6.1. Data Quality Frameworks and Validation Rules

Data quality is critical for enabling business data to deliver trusted insights at every level. To meet this challenge, organizations define data quality frameworks documenting data quality requirements supported by corresponding validation rules enabling measurement and monitoring. Data validation gates can then be established–including for the medallion architecture–to act as quality checkpoints for signals traversing the architecture across various stages. Affected signals can be enforced by such gates within data-qualified environments and automatically quarantined for further investigation anywhere else. These gates can also be instrumented, monitored, reported, and monitored, serving less as a punitive mechanism and more as an early warning system [64].

A comprehensive data quality framework with specific validation rules can be defined for the common pattern of ingesting data from an external cloud ecosystem. Data are acquired in their source schema or structure and stored as-is or transformed into a canonical schema. They are often pulled from an untrusted zone, staged, transmitted, and loaded into the medallion architecture. Sets of validation rules can be established to cover system performance, replication, change detection, data integrity, and industry best practices. Potential data defects such as schema drift, excess delays, record loss, and content anomalies are detected, identified, and triaged, with affected signals profiled for longer-term corrective action. These rules can be grouped and jointly defined for different communities, unless certain parties desire to introduce deviation detection and define bespoke rules [65].

6.2. Observability, Monitoring, and alerting

Business-critical ETL pipelines demand continuous observability and health monitoring. A signal detection model uses KPIs, traces, and metrics from all processing layers to identify deviations from expected patterns. Such deviations can trigger notifications or alerts for operational teams or developers, enabling them to take timely corrective action. Tools like Microsoft Azure Monitor and Azure Data Explorer support observability scenarios like these through integrations with other Azure services [66].

A test suite for the validation framework exposes data quality gates in all layers. Tests systematically generate false negatives and negatives, providing a controlled environment for verifying end-to-end quality alerts. The triage process begins with product teams who apply business knowledge to defects reported within their domains. Affected data is usually quarantined until an investigation restores data fidelity [67].

Technical discipline and a monitoring ecosystem improve confidence in system observability and performance. Pipeline textures capture materialization details. Critical pipelines are represented in Azure Data Factory with traces that automate telemetry collection in Azure Data Explorer. Overview dashboards for observability consolidate information from across the data analytics platform [68].

6.3. Provenance and impact analysis

Provenance describes the lineage of the data as it moves from raw ingestion through processing and into publication. Questions central to the area include What processes ran on the dataset? and What transformations were applied? Capturing the transformations and algorithms used to sanitize, cleanse, enrich, or analyze data. Provenance details can deliver invaluable context, with users relying on clear indications of who processed what—and with which algorithms and parameters—to more efficiently evaluate results. Reporting the person responsible for the transformation is considered best practice when possible.

Designed as a diagrammatic visualization of the data lineage, these systems allow an intuitive assessment of process dependencies so users can analyse the risk of using the dataset, based on the success criteria of the processes affecting it. With the implementation of proper provenance capture, these resources can also isolate error occurrences and trigger follow-on debugging. Moreover, by inspecting the impacted datasets, it is possible to assess the risk of using those outputs and address potential consequences. Linking Quality Analysis and Impact Analysis together defines true reproducibility in Data Engineering processes [69].

Conversely, Impact Analysis assesses how a specific dataset can affect all downstream data products. It answers questions such as “Which analyses will be affected if this dataset is modified?” and serves as an excellent complement to Provenance Analysis, closing the loop and enabling checks on which analyses will be impacted by a dataset. Once those impacted analyses are known, QA teams can prompt Data Owners to control these processes and prevent obsolete or wrong datasets from being published based on outdated or erroneous analyses.

Mature Azure Data Lake ecosystems scale to thousands of users, entities, and applications. Expanding size and usage can degrade performance and introduce errors in consumption if changes are poorly managed. Optimization of data quality and observability helps, but downtime, limits on hardware capacity, and volume-and-velocity challenges can still contribute to bad experiences. Fortunately, Azure Data Lake provides facilities to manage these issues—if they are correctly exploited [70].

Separation of storage and compute is fundamental in Azure Data Lake to reduce costs and allow elastic scaling of resource allocation. Azure services and third-party tools can autoscale depending on demand. Expensive compute-hungry steps can be allocated less frequently, to reduce cost with little impact on usability. Indeed, materialization must consider the quality of the cached data: sufficient frequency for popular queries, lower frequency when freshness is less relevant or applied constantly. Trading off cost and freshness can be fine-tuned at any scale.

7.1. Storage and Compute Separation

Storage and compute separation strategies decouple the storage and compute resources in a service's logical architecture, enabling independent scaling and deployment of these two types of assets. Having the storage and compute resources provisioned for two different tasks opens up more options when assigning them. For example, the storage resources can be located closer to users to minimize access latencies, while the compute resources can be provisioned in places with lower associated costs. It is also possible to apply autoscaling mechanisms to compute resources based on changes in usage patterns and costs in a short-term are reduced by applying a lower number of compute resources during the off-work hours. Cloud providers also allow users to take advantage of pre-provisioned resources offering a significant reduction in operating costs [71].

Separate storage and compute resources can then be assigned to specific tasks. Batches processing can take advantage of services where it is cheaper to run ETL jobs. BI queries can be directed to services optimized for relaying BI workloads and data science workloads can leverage dedicated resources. Although different resources are utilized for different functions, the same physical pool of infrastructure can be used by either processing type. This also avoids a lot of complexity transferring data around. Avoiding additional copies of the same data also reduces data egress costs, possible charges for additional external data transfer, and latency. However, the longer the distance between the storage and compute resources, the higher the associated access latency and the cost of that access.

Besides providing flexibility in storage and compute segmentation, this separation allows utilizing the specific features of storage and compute engines to reduce costs. For storage engines, it is possible to pay for the actual volume of data stored. In a compute-centric model, users pay for the amount of consumed processing time. Theoretically, the ideal scenario for any big data architecture is to have as many storage and compute resources as possible decoupled and provisioned separately to minimize costs. However, in practice, there are limitations in fully decoupling and separating storage and compute resources. Deploying to external cloud providers where a low egress cost is guaranteed is common. However, proper analysis of these costs must be addressed before fully using these types of architectures.

Figure 6

Stewardship roles mentioned in the article

7.2. caching, materialized views, and data summaries

Cost-optimization strategies for large pipelines rely on a variety of levers across the data management landscape: rightsizing, data retention policies, data-tiering, and maintaining an optimal ratio of compute to storage resources. Redundant storage is removed by setting well-founded data-retention policies and monitoring the use of cold storage. Finely grained partitions minimize storage overheads and boost read performance. Data-access patterns are modeled to inform appropriate caching and summarization strategies. Autoscaling helps match compute capacity to workload requirements. work_title: Optimizing Large-Scale ETL Pipelines Using Medallion Architecture on Azure Data Lake [72].

The Medallion Architecture outlines useful design patterns and best practices for operating a data lake. However, the effectiveness of individual design decisions should be validated through empirical testing. Scaling large data pipelines can expose bottlenecks and vulnerabilities. Monitoring their health and performance can help identify and quantify operational defects. Nevertheless, measures to improve data quality, lineage information, metadata, governance, and observability need to strike a balance with the costs of implementation and maintenance, lest they detract from data usability.

7.3. Cost-Optimization Strategies for Large Pipelines

Large ETL pipelines typically consume considerable compute and storage resources, resulting in higher operating expenses. A few cost-optimization techniques that apply to such environments are identified.

Other principles addressed at different layers mitigate expenses by fine-tuning execution footprints. At the Bronze layer, data retention policies discard stale and unclaimed data. In the Silver layer, partitioning rules prevent unnecessary volume bloat during data preparation. Additional layout design defines the volume count to control the underlying file system structure. When used with efficient execution and other general ETL best practices, all these elements combine to deliver value while ensuring low costs.

**Rightsizing Resources**. Assigning the right scale to data loads is essential to delivering them quickly while keeping expenses low. Under-sizing requires more time and leads to missed SLAs, while over-sizing wastes resources and incurs unnecessary charges. Workload management tools assist in keeping the cluster allocated to predefined usage ranges by launching so-called preemptible VMs. Alerts help spotting jobs that perform longer than expected and may benefit from additional allocation.

**Data Retention Policies**. Shortening the retention periods of data on the Bronze layer helps cut storage costs. Most Bronze data in a data lake does not require long-term preservation; its short life is due either to the applied quality filtering or to its association with analytics or development activities. A few Azure products automating the purge of obsolete data based on preconfigured rules can simplify monitoring and speed up handling.

**Tiered Storage**. Beyond controlling storage growth, some data sources lend themselves to tiered storage, separating hot and cold data. A logical separation into two distinct volumes—one for recent snapshots, on premium storage for rapid access, and the other for older snapshots, on cooled storage for lower fees—reduces costs in the long term by leveraging Azure's lower-priced storage specifics.

**Optimization of Compute-to-Storage Ratios**. Large cloud providers—e.g., Amazon and Azure—use a completely different storage model than on-premises data warehouses, where storage and compute are separated yet tightly coupled. In these cloud environments, low-cost storage should be paired with the least expensive compute clusters. Unnecessarily large compute clusters incur costs but proportionally larger storage reserves reduce efficiency. Ensuring that storage volume is at least an order of magnitude greater than compute memory allows smaller, cheaper, and more scalable clusters to suffice [73].

Optimizing large-scale ETL pipelines using the Medallion architecture on Azure Data Lake enhances design, operation, and governance, offering practical benefits. The Azure Data Lake Medallion pattern addresses traditional ETL pipelines' design fragmentation and operational burden. Recommendations encompass storage organization, naming conventions, a data quality framework, quality observability, effective metadata management, and governance. The analysis focuses on the cleaning and preparation of historical data sources and Azure Data Lake for end-user consumption. Supported by literature and data-analytical knowledge, the medallion-driven approach is applied in the batch-processing context of Azure Data Lake.

Recent publications have addressed the Bronze layer of the Medallion architecture on Azure Data Lake, focusing on the minimal ingestion of data for Data Warehouse purposes and the need for data retention only for investigation or forensic purposes. These points have also been considered as part of designing the Data Quality Framework and then validated through a validation gate within the Silver Layer, taking action only when the data defect rate reaches a certain threshold. Ingestion performance has been ranked as LOW in terms of priority; at the same time, traps and tools have been created to facilitate speed improvement.

• Ajani, T. (2021). Data Engineering with Azure and Databricks. Packt Publishing. • Al-Zubaidi, S., Gillon, D., & Thomas, D. (2020). How to Build a Medallion Architecture on Azure. Microsoft TechCommunity. • Albahari, C. (2021). C# in a Nutshell: the Definitive Reference. O’Reilly Media. • Gharbi, S., & Agrebi, S. (2021). Data Quality in the Cloud: A Cloud-Prone Approach. International Journal of Cloud Computing and Services Science (IJCCSS), 10(3). • Microsoft. Medallion Architecture for Data Lakehouse on Azure Databricks. Microsoft Learn. • Microsoft. Azure Data Lake Storage Gen2 documentation. Microsoft Learn. • Rao, C. S., Manohar, D. D., Girisha, H. D., Venkata, V. K., Mallard, J. C., Gupta, S., & Jain, P. S. H. (2021). Big Data Pipeline: Data Management using Apache Spark and Microsoft Azure. Journal of Computer Science & Cybernetics, 37(3), 237–258. • Wang, Y. Exam Ref DP-203 Data Engineering on Microsoft Azure. Microsoft Press. • Yeldandi, S. (2020). Design and Development of Smart Health Assist. International Journal of Computer Applications, 975, 8887. • Zhang, J., & Xu, F. (2021). An Overview of Data Management Platform for Azure Cloud. IEEE Access, 9, 77–102.