# E2e Medallion Architecture

> >

- **Type:** Skill
- **Install:** `agentstack add skill-microsoft-skills-for-fabric-e2e-medallion-architecture`
- **Verified:** Yes — security-reviewed for prompt injection and unsafe behavior
- **Seller:** [microsoft](https://agentstack.voostack.com/s/microsoft)
- **Installs:** 0
- **Category:** [Agent Skills](https://agentstack.voostack.com/c/agent-skills)
- **Latest version:** 0.1.0
- **License:** MIT
- **Upstream author:** [microsoft](https://github.com/microsoft)
- **Source:** https://github.com/microsoft/skills-for-fabric/tree/main/plugins/fabric-skills/skills/e2e-medallion-architecture

## Install

```sh
agentstack add skill-microsoft-skills-for-fabric-e2e-medallion-architecture
```

Requires the [AgentStack CLI](https://agentstack.voostack.com/docs/cli). Works with Claude Code, Cursor, and any MCP-compatible agent.

## About

> **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
4. **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
5. **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}`
2. **Create one lakehouse per workspace**:
   - Bronze workspace → `{project}_bronze` lakehouse
   - Silver workspace → `{project}_silver` lakehouse
   - Gold workspace → `{project}_gold` lakehouse
3. **Assign RBAC per layer workspace**:
   - Bronze: ingestion/engineering write permissions
   - Silver: engineering/data quality permissions
   - Gold: analytics/BI consumer access with stricter curation controls
4. **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)
3. **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.
4. **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)
5. **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))
6. **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
2. **Read from lakehouse path**: Once data is in `Files/`, read using lakehouse-relative paths (e.g., `spark.read.format("csv").load("Files/landing/daily/")`)
3. **Add metadata and write**: Tracking columns (ingestion timestamp, source file, batch ID), Delta table with descriptive name, partition by ingestion date, append mode
4. **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](https://github.com/microsoft)
- **Source:** [microsoft/skills-for-fabric](https://github.com/microsoft/skills-for-fabric)
- **License:** MIT

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

## Pricing

- **Free** — Free

## Security capabilities

Automated source analysis of v0.1.0 — what this tool can access:

- **Network access:** yes
- **Filesystem access:** no
- **Shell / process execution:** no
- **Environment & secrets:** no
- **Dynamic code execution:** no

*"Yes" means the capability is present in the source — more access means more to trust, not that it is unsafe.*


## Versions

- **0.1.0** — security scan: passed — Imported from the upstream source.

## Links

- Listing page: https://agentstack.voostack.com/l/skill-microsoft-skills-for-fabric-e2e-medallion-architecture
- Seller: https://agentstack.voostack.com/s/microsoft
- Browse the marketplace: https://agentstack.voostack.com/browse

---
Listed on AgentStack — the marketplace for AI agent skills and MCP servers. Every listing is security-reviewed. Creators keep 70%.