Data Lake Architectures in Azure

The medallion architecture

The medallion architecture organises data lake storage into three progressive quality zones:

┌──────────────────────────────────────────────────────────────────┐
│                     ADLS Gen2 Storage Account                    │
│                                                                  │
│  ┌─────────────────────────────────────────────────────────┐     │
│  │  BRONZE (raw)                                           │     │
│  │  Exact copy of source data. Never modified.             │     │
│  │  Formats: JSON, CSV, Avro, Parquet (as received)        │     │
│  │  /bronze/salesforce/accounts/2025/03/19/                │     │
│  │  /bronze/sqlserver/orders/year=2025/month=03/           │     │
│  └─────────────────────────────────────────────────────────┘     │
│                           ↓  Spark / ADF                         │
│  ┌─────────────────────────────────────────────────────────┐     │
│  │  SILVER (conformed)                                     │     │
│  │  Validated, cleaned, typed, joined across sources.      │     │
│  │  Format: Delta Lake (always)                            │     │
│  │  /silver/orders/                                        │     │
│  │  /silver/customers/                                     │     │
│  │  /silver/products/                                      │     │
│  └─────────────────────────────────────────────────────────┘     │
│                           ↓  Spark / SQL                         │
│  ┌─────────────────────────────────────────────────────────┐     │
│  │  GOLD (curated)                                         │     │
│  │  Business-ready aggregations and domain models.         │     │
│  │  Format: Delta Lake / Parquet                           │     │
│  │  /gold/customer_lifetime_value/                         │     │
│  │  /gold/monthly_revenue_by_region/                       │     │
│  │  /gold/inventory_forecast/                              │     │
│  └─────────────────────────────────────────────────────────┘     │
└──────────────────────────────────────────────────────────────────┘

Bronze is the immutable archive. Every ingested file lands here exactly as received. If something goes wrong in downstream processing, you can always reprocess from bronze. Never delete or modify bronze data — just let it expire according to your retention policy.

Silver is the clean, conformed layer. Spark jobs read from bronze and write to silver after: validating column types, removing duplicates, standardising date formats, filtering invalid rows, and joining entities from different source systems (e.g., matching customer records between Salesforce and ERP). Silver data is stored in Delta format for ACID guarantees and easy incremental updates.

Gold is the business layer. Aggregations, KPIs, and domain-specific datasets are computed from silver and stored in gold. Power BI reports, data science feature tables, and executive dashboards read from gold. Gold data should be structured and named in terms that business users understand, not in terms of source system table names.

Folder structure and naming conventions

Consistent naming is essential for a data lake used by multiple teams. Here is a naming convention that works well for most organisations:

/bronze/
  {source-system}/
    {entity}/
      year={YYYY}/
        month={MM}/
          day={DD}/
            {entity}_{YYYYMMDD_HHMMSS}.{format}

/silver/
  {domain}/
    {entity}/
      (Delta table files managed by Spark)

/gold/
  {domain}/
    {dataset-name}/
      (Delta table files managed by Spark)

Examples:
/bronze/salesforce/accounts/year=2025/month=03/day=19/accounts_20250319_020000.json
/bronze/pos-system/transactions/year=2025/month=03/day=19/transactions_20250319_000000.parquet
/silver/sales/orders/         ← Delta table: all orders, all sources, conformed
/silver/customers/profile/    ← Delta table: unified customer profile
/gold/reporting/monthly_sales_by_region/  ← Delta table: pre-aggregated for BI

Using Hive-style partitioning (year=2025/month=03/) in the bronze layer allows Spark and serverless SQL to use folder-level partition pruning when querying bronze data directly. This is particularly useful for backfill jobs that process a specific date range.

Access control: RBAC and ACLs

ADLS Gen2 supports two access control models that you use together:

Azure RBAC controls who can access the storage account as a whole. Common roles:

• Storage Blob Data Reader — read files; cannot write
• Storage Blob Data Contributor — read and write files; cannot manage account settings
• Storage Blob Data Owner — full access including setting ACLs

POSIX ACLs allow fine-grained permissions at the container, folder, and file level. With ACLs, you can grant read access to the silver layer to all analysts while restricting write access to only the ETL service principal. ACLs must be explicitly enabled in the storage account settings.

# Grant the Synapse workspace managed identity access to the bronze container
SYNAPSE_MI=$(az synapse workspace show \
  --resource-group rg-data \
  --name ws-synapse-demo \
  --query identity.principalId \
  --output tsv)

az role assignment create \
  --assignee $SYNAPSE_MI \
  --role "Storage Blob Data Contributor" \
  --scope "/subscriptions/{sub}/resourceGroups/rg-data/providers/Microsoft.Storage/storageAccounts/mydatalake/blobServices/default/containers/bronze"

# Grant the Databricks service principal read access to silver
az role assignment create \
  --assignee {databricks-sp-object-id} \
  --role "Storage Blob Data Reader" \
  --scope "/subscriptions/{sub}/resourceGroups/rg-data/providers/Microsoft.Storage/storageAccounts/mydatalake/blobServices/default/containers/silver"

# Grant the ETL service principal write access to silver (for writing transformed data)
az role assignment create \
  --assignee {etl-sp-object-id} \
  --role "Storage Blob Data Contributor" \
  --scope "/subscriptions/{sub}/resourceGroups/rg-data/providers/Microsoft.Storage/storageAccounts/mydatalake/blobServices/default/containers/silver"

Bronze to silver transformation pipeline

A typical bronze-to-silver PySpark job reads raw files from the bronze layer, applies quality and conformance rules, and writes to a Delta table in silver. This job runs nightly (or in micro-batches for near-real-time requirements):

from pyspark.sql import SparkSession
from pyspark.sql.functions import (
    col, to_date, to_timestamp, trim, upper, when,
    current_timestamp, lit, count, isnan, isnull
)
from pyspark.sql.types import DecimalType
from delta.tables import DeltaTable

spark = SparkSession.builder.getOrCreate()

# Read yesterday's raw order files from bronze
# Using partition pruning: read only yesterday's partition
from datetime import date, timedelta
yesterday = (date.today() - timedelta(days=1)).strftime("%Y/%m/%d")
bronze_path = f"abfss://bronze@mydatalake.dfs.core.windows.net/orders/year={yesterday[:4]}/month={yesterday[5:7]}/day={yesterday[8:10]}/"

raw_orders = spark.read.json(bronze_path)

# Data quality checks
total_rows = raw_orders.count()
invalid_total = raw_orders.filter(
    isnull(col("order_id")) |
    isnull(col("customer_id")) |
    (col("order_total") < 0)
).count()

print(f"Bronze rows: {total_rows}, Invalid: {invalid_total}")
if invalid_total / total_rows > 0.05:
    raise Exception(f"Data quality failure: {invalid_total/total_rows:.1%} invalid rows exceeds 5% threshold")

# Transform bronze → silver
silver_orders = (
    raw_orders
    .filter(
        col("order_id").isNotNull() &
        col("customer_id").isNotNull() &
        (col("order_total") >= 0)
    )
    .withColumn("order_date",   to_date(col("order_date"), "yyyy-MM-dd"))
    .withColumn("order_total",  col("order_total").cast(DecimalType(18, 2)))
    .withColumn("status",       upper(trim(col("status"))))
    .withColumn("load_date",    current_timestamp())
    .withColumn("source",       lit("pos-system"))
    .dropDuplicates(["order_id"])
    .select(
        "order_id", "customer_id", "order_date",
        "order_total", "status", "load_date", "source"
    )
)

# Merge into Delta table (upsert: update existing, insert new)
silver_path = "abfss://silver@mydatalake.dfs.core.windows.net/orders/"

if DeltaTable.isDeltaTable(spark, silver_path):
    delta_table = DeltaTable.forPath(spark, silver_path)
    delta_table.alias("existing").merge(
        silver_orders.alias("incoming"),
        "existing.order_id = incoming.order_id"
    ).whenMatchedUpdateAll() \
     .whenNotMatchedInsertAll() \
     .execute()
else:
    # First run: create the Delta table
    silver_orders.write.format("delta") \
        .partitionBy("order_date") \
        .save(silver_path)

print(f"Silver orders updated: {silver_orders.count()} rows merged")

Data governance with Microsoft Purview

As a data lake grows, knowing what data exists, where it came from, and who is responsible for it becomes critical. Microsoft Purview integrates with ADLS Gen2 to provide:

• Automated scanning — Purview scans your ADLS containers and registers all Parquet and Delta tables in its data catalog, inferring schemas and column statistics
• Data lineage — tracks the flow from bronze through silver to gold, showing which files and tables were derived from which sources
• Sensitivity classification — automatically identifies PII and sensitive data (email addresses, SSNs, credit card numbers) and labels them
• Business glossary — maps technical column names (e.g., cust_ltv_usd) to business terms (e.g., “Customer Lifetime Value in USD”)