E2e Medallion Architecture

> Update Check — ONCE PER SESSION (mandatory) > The first time this skill is used in a session, run the check-updates skill before proceeding. > - GitHub Copilot CLI / VS Code: invoke the check-updates skill. > - Claude Code / Cowork / Cursor / Windsurf / Codex: compare local vs remote package.json version. > - Skip if the check was already performed earlier in this session.

> CRITICAL NOTES > 1. To find the workspace details (including its ID) from workspace name: list all workspaces and, then, use JMESPath filtering > 2. To find the item details (including its ID) from workspace ID, item type, and item name: list all items of that type in that workspace and, then, use JMESPath filtering

End-to-End Medallion Architecture

Prerequisite Knowledge

Read these companion documents — they contain the foundational context this skill depends on:

• [COMMON-CORE.md](../../common/COMMON-CORE.md) — Fabric REST API patterns, authentication, token audiences, item discovery
• [COMMON-CLI.md](../../common/COMMON-CLI.md) — az rest, az login, token acquisition, Fabric REST via CLI
• [SPARK-AUTHORING-CORE.md](../../common/SPARK-AUTHORING-CORE.md) — Notebook deployment, lakehouse creation, job execution
• [notebook-api-operations.md](../spark-authoring-cli/resources/notebook-api-operations.md) — Required for notebook creation — .ipynb structure requirements, cell format, getDefinition/updateDefinition workflow

For Spark-specific optimization details, see [data-engineering-patterns.md](../spark-authoring-cli/resources/data-engineering-patterns.md).

---

Architecture Overview

Medallion Architecture is a data lakehouse pattern with three progressive layers:

| Layer | Purpose | Optimization Profile | Use Case | |-------|---------|---------------------|----------| | Bronze (Raw) | Land raw data exactly as received | Write-optimized, append-only, partitioned by ingestion date | Audit trail, reprocessing, lineage | | Silver (Cleaned) | Deduplicated, validated, conformed data | Balanced read/write, partitioned by business date | Feature engineering, operational reporting | | Gold (Aggregated) | Pre-calculated metrics for analytics | Read-optimized (ZORDER, compaction), partitioned by month/year | Power BI reports, dashboards, ad-hoc analytics via SQL endpoint |

• Bronze: Schema-on-read — flexible schema, Delta time travel supports audit and rollback
• Silver: Schema enforcement — reject non-conforming writes; handle schema evolution with mergeSchema when sources change
• Gold: Strict schema governance — curated, business-approved datasets only

---

Must/Prefer/Avoid

MUST DO

• Choose lakehouse architecture based on schema-enabled availability (see [infrastructure-orchestration.md](../spark-authoring-cli/resources/infrastructure-orchestration.md)):
• Preferred: Schema-enabled lakehouse → create ONE workspace + ONE lakehouse with bronze, silver, gold schemas
• Legacy: Non-schema-enabled → create separate workspaces per layer (Bronze, Silver, Gold) for governance and access control
• Use Livy API for schema and table creation — to create schemas and tables in a schema-enabled lakehouse, submit Spark SQL statements via Livy sessions (POST /livyApi/versions/2023-12-01/sessions → POST .../statements). This is the only programmatic REST path for DDL operations (CREATE SCHEMA, CREATE TABLE) in Fabric lakehouses.
• Add metadata columns in Bronze: ingestion timestamp, source file, batch ID
• Apply data quality rules in the Bronze-to-Silver transformation (deduplication, null handling, range validation)
• Use Delta Lake format for all medallion layer tables
• Use partition-aware overwrite in Silver/Gold writes to avoid reprocessing unchanged data
• Include validation steps after each layer (row counts, schema checks, anomaly detection)
• Follow the .ipynb validation + Fabric nuances in [notebook-api-operations.md](../spark-authoring-cli/resources/notebook-api-operations.md#ipynb-validation--fabric-nuances) when creating notebooks via REST API — every code cell must include "outputs": [] and "execution_count": null
• Complete the full end-to-end flow — do not stop after creating notebooks; always bind lakehouses, execute notebooks sequentially (Bronze → Silver → Gold), verify results, and connect Power BI to the Gold layer unless the user explicitly requests a partial setup

PREFER

• Incremental processing (watermark pattern) over full refresh
• Separate notebooks per layer for independent testing and debugging
• ZORDER on frequently filtered columns in Gold tables
• Running OPTIMIZE after writes in Silver and Gold layers
• Environment-specific Spark configs (write-heavy for Bronze, balanced for Silver, read-heavy for Gold)
• OneLake shortcuts to expose Gold data to consumer workspaces without duplication
• Clear layer ownership: engineers own Bronze/Silver, analysts own Gold
• Fabric Variable Libraries to centralize paths and configuration across layers
• Multi-workspace deployment patterns for medium/high governance requirements (Bronze/Silver/Gold in separate workspaces)
• Use Materialized Lake Views (MLVs) for Silver/Gold tables when the transformation is expressible in Spark SQL and benefits from declarative refresh semantics. See [spark-authoring-cli — Materialized Lake View patterns](../spark-authoring-cli/resources/materialized-lake-view-patterns.md) and [MLV incremental refresh patterns](../spark-authoring-cli/resources/mlv-incremental-refresh-patterns.md).
• For MLV refresh scheduling and monitoring (create/delete schedules, trigger on-demand refresh, check job status), use [mlv-operations-cli](../mlv-operations-cli/SKILL.md). Note: "materialized view", "spark materialized view", and "MLV" all refer to the same Fabric feature.

AVOID

• Storing all layers in a single lakehouse WITHOUT schemas — non-schema lakehouses require notebook init cells or Environment configuration to enable OneLake Spark Catalog for RLS/CLS and MLVs. Use separate lakehouses for isolation if schemas aren't available.
• Creating 3 separate lakehouses when schema-enabled lakehouse is available — use schemas within one lakehouse instead (cleaner, no boilerplate init cells, more efficient for MLV cross-schema transformations)
• Skipping the Silver layer and going directly from Bronze to Gold
• Hardcoded workspace IDs, lakehouse IDs, or FQDNs — discover via REST API
• SELECT * without LIMIT on Bronze tables (they grow unboundedly)
• Running VACUUM without checking downstream dependencies
• Chaining OneLake shortcuts between medallion layers (Bronze→Silver→Gold) — each layer must be physically materialized for lineage and governance
• Copying complete implementation code into skills — guide the LLM to generate instead
• Reading from external HTTP/HTTPS URLs directly in Spark — Fabric Spark cannot access arbitrary external URLs; land data in lakehouse Files/ first (via curl, OneLake API, or Fabric pipeline Copy activity), then read from the lakehouse path
• Creating notebooks via REST API without validating .ipynb structure — missing execution_count: null or outputs: [] on code cells causes silent failures or "Job instance failed without detail error"

---

Workspace Setup Guidance

When setting up a medallion workspace, choose your architecture pattern first (see [infrastructure-orchestration.md](../spark-authoring-cli/resources/infrastructure-orchestration.md) for detailed guidance):

Option A: Schema-Enabled Lakehouse (Preferred)

1. Create single workspace: {project}-{env}
2. Create one lakehouse with schemas: {project}_lakehouse
3. Create schemas within the lakehouse:

• bronze schema for raw ingestion
• silver schema for cleaned/validated data
• gold schema for aggregated analytics

1. Choose transformation approach:

• Option 4a: Use notebooks for each layer (PySpark or Spark SQL transformations)
• Option 4b: Use Materialized Lake Views (Spark SQL) for declarative transformations with incremental refresh (when query is IR-eligible) — see [materialized-lake-view-patterns.md](../spark-authoring-cli/resources/materialized-lake-view-patterns.md) and [mlv-incremental-refresh-patterns.md](../spark-authoring-cli/resources/mlv-incremental-refresh-patterns.md)
• Note: PySpark MLVs exist but use full refresh only (no incremental) — use when you need UDFs/complex Python logic
• MLV benefit: OneLake Spark Catalog is automatically enabled for schema-enabled lakehouses — MLVs work out-of-box with no notebook init cells or Environment configuration required

1. RBAC (optional): Use row-level security and column masking within schemas for fine-grained access control (also requires OneLake Spark Catalog)

Option B: Separate Lakehouses (Legacy)

1. Create three workspaces:

• {project}-bronze-{env}
• {project}-silver-{env}
• {project}-gold-{env}

1. Create one lakehouse per workspace:

• Bronze workspace → {project}_bronze lakehouse
• Silver workspace → {project}_silver lakehouse
• Gold workspace → {project}_gold lakehouse

1. Assign RBAC per layer workspace:

• Bronze: ingestion/engineering write permissions
• Silver: engineering/data quality permissions
• Gold: analytics/BI consumer access with stricter curation controls

1. Enable OneLake Spark Catalog for non-schema lakehouses (required for RLS/CLS and catalog-backed access patterns):

• Primary: Set spark.sql.fabric.catalog.enable-schemaless-lakehouses=true in an Environment and attach it to notebooks.
• Alternative: Omit default lakehouse binding from notebooks. Use four-part fully-qualified references (workspace.lakehouse.schema.table). OneLake Spark Catalog auto-enables when no default lakehouse is set.
• Alternative (internal/unsupported): Add this as the first cell in every notebook:

``python %%pyspark !echo "spark.sql.fabric.catalog.enable-schemaless-lakehouses=true" >> /home/trusted-service-user/.trident-context ``

• ⚠️ Note: This workaround uses an internal runtime configuration path that may change in future Fabric releases. Prefer schema-enabled lakehouses for stable, documented OneLake Spark Catalog support.
• With this configuration, non-schema lakehouses support:
• ✅ Row-level security (RLS) and column-level security (CLS)
• Note: MLVs require schema-enabled lakehouses (Option A). For non-schema lakehouses, use notebooks with Delta tables.

Common Steps (Both Options)

After completing Option A or Option B above, perform these steps:

1. Create notebooks for each layer (one per transformation stage) — follow .ipynb validation + Fabric nuances
2. Bind each notebook to its lakehouse — set metadata.dependencies.lakehouse with the correct lakehouse ID (see [notebook-api-operations.md § Default Lakehouse Binding](../spark-authoring-cli/resources/notebook-api-operations.md#default-lakehouse-binding)):

• Option A: All notebooks → same lakehouse, use schema prefixes (bronze.table, silver.table)
• Option B:
• Bronze notebook → Bronze workspace/lakehouse
• Silver notebook → Silver workspace/lakehouse (reads Bronze via cross-workspace OneLake access / fully qualified references)
• Gold notebook → Gold workspace/lakehouse (reads Silver via cross-workspace access)

1. Confirm notebook deployment — check that updateDefinition returned Succeeded; this is sufficient confirmation that content and lakehouse binding persisted. Do NOT call getDefinition to re-verify — it is an async LRO and adds unnecessary latency.
2. Execute notebooks sequentially — Bronze first, then Silver, then Gold — using POST .../jobs/instances?jobType=RunNotebook with the correct defaultLakehouse in execution config (both id and name required)
3. Connect Power BI to Gold layer — discover the Gold lakehouse SQL endpoint, create a Direct Lake semantic model, create a report with visuals on the Gold summary table (see [Gold Layer → Power BI Consumption](#gold-layer--power-bi-consumption))
4. Create pipeline to orchestrate the Bronze → Silver → Gold flow for recurring execution

Explicit Override: Single Workspace

If the user explicitly asks for a single workspace deployment (for example, POC/small team/monolithic pattern), keep the current approach:

• One workspace with separate Bronze/Silver/Gold lakehouses
• Preserve layer separation logically even when workspace is shared
• Call out governance trade-offs versus multi-workspace design

Parameterize by environment: workspace name suffix (-dev, -prod), data volume (sample vs full), capacity SKU, and Bronze retention period.

---

Bronze Layer — Ingestion Patterns

When a user requests data ingestion into the Bronze layer, guide LLM to:

1. Land data in lakehouse first: External data must be staged into the lakehouse Files/ folder before Spark can read it — use one of:

• Fabric Pipeline Copy activity (preferred for recurring loads) — connects to external sources (HTTP, FTP, databases, cloud storage) and writes to OneLake
• OneLake API / curl — upload files via REST API using storage.azure.com token (see COMMON-CLI.md § OneLake Data Access)
• OneLake Shortcut — for data already in Azure ADLS Gen2, S3, or another OneLake location
• notebookutils.fs — copy from mounted storage paths within a notebook
• ⚠️ Fabric Spark cannot read from arbitrary HTTP/HTTPS URLs — spark.read.format("csv").load("https://...") will fail

1. Read from lakehouse path: Once data is in Files/, read using lakehouse-relative paths (e.g., spark.read.format("csv").load("Files/landing/daily/"))
2. Add metadata and write: Tracking columns (ingestion timestamp, source file, batch ID), Delta table with descriptive name, partition by ingestion date, append mode
3. Validate: Log row counts, validate schema structure, flag anomalies vs historical patterns

---

Silver Layer — Transformation Patterns

When a user requests Bronze-to-Silver transformation, guide LLM to:

• Quality rules: Deduplicate on natural/composite key, filter invalid ranges, handle nulls (drop required, fill optional), validate logical constraints
• Schema conformance: snake_case column names, standardized data types, derived columns (durations, percentages, categories)
• Schema evolution: Use mergeSchema option when source schemas change; coordinate downstream updates to Gold tables and Power BI datasets
• Write strategy: Partition by business date, partition-aware overwrite, run OPTIMIZE after write, log before/after metrics

---

Gold Layer — Aggregation Patterns

When a user requests Gold analytics tables, guide LLM to generate:

• Common aggregates: Daily/weekly/monthly summaries, dimensional analysis (by location, category, type), trend breakdowns over time, demand patterns (hour-of-day, day-of-week)
• Spark session config — set these properties in the Gold notebook before any write operations:

``python spark.conf.set("spark.sql.parquet.vorder.default", "true") spark.conf.set("spark.databricks.delta.optimizeWrite.enabled", "true") spark.conf.set("spark.databricks.delta.optimizeWrite.binSize", "1g") ``

• V-Order (vorder.default) — applies Fabric's columnar sort optimization to all Parquet files, dramatically improving Direct Lake and SQL endpoint read performance
• Optimize Write (optimizeWrite.enabled) — coalesces small partitions into optimally-sized files (target ~1 GB per binSize), reducing file count and improving scan efficiency
• Optimization: ZORDER on filter columns, run OPTIMIZE after writes, pre-aggregate metrics to avoid runtime computation

---

End-to-End Execution Flow

When setting up medallion architecture end-to-end, the LLM must not stop after c

…

Source & license

This open-source skill is cataloged on AgentStack and links to its original source — we do not rehost the code.

• Author: microsoft
• Source: microsoft/skills-for-fabric
• License: MIT

Install and usage instructions live in the source repository linked above.