silver-layer-setup

# Silver Layer Setup - Orchestrator Skill End-to-end workflow for creating production-grade Silver layer pipelines using Spark Declarative Pipelines (SDP, formerly Delta Live Tables/DLT) with Delta table-based data quality rules, quarantine patterns, streaming ingestion, and monitoring views. > **Naming:** Databricks rebranded DLT to **Spark Declarative Pipelines (SDP)** / **Lakeflow Declarative Pipelines (LDP)**. The modern Python API is `from pyspark import pipelines as dp`, and Databricks now **recommends `dp`** over `import dlt`. Expectations **are available** in `dp` (e.g. `dp.expect_all_or_drop(...)`, `dp.expect_all(...)`) — the [Lakeflow pipelines Python reference](https://docs.databricks.com/aws/en/ldp/developer/python-ref) maps each `@dlt.expect*` decorator to a `dp` equivalent. This skill still standardizes on `import dlt` for the existing DQ-rules framework (`dlt-expectations-patterns`) because that framework's decorators are written against `dlt`; the two APIs are behaviorally equivalent, so a `dp` migration is straightforward (swap the import and decorator prefix). New projects may use `databricks pipelines init` to scaffold an SDP Asset Bundle project. **Time Estimate:** 3-4 hours for initial setup, 1 hour per additional table **What You'll Create:** 1. `dq_rules` Delta table - Centralized rules repository in Unity Catalog 2. `dq_rules_loader.py` - Pure Python module to load rules at runtime 3. `silver_*.py` - SDP/DLT notebooks with expectations loaded from Delta table 4. `silver_pipeline.yml` - Serverless SDP pipeline configuration 5. DQ monitoring views - Per-table metrics and referential integrity checks --- ## Decision Tree | Question | Action | |----------|--------| | Creating a Silver layer from scratch? | **Use this skill** - it orchestrates everything | | Only need DLT expectations patterns? | Read `silver/01-dlt-expectations-patterns/SKILL.md` directly | | Need advanced DQX validation? | Read `silver/02-dqx-patterns/SKILL.md` directly | | Need Asset Bundle configuration? | Read `skills/databricks-asset-bundles/SKILL.md` directly | | Need table properties reference? | Read `common/databricks-table-properties/SKILL.md` directly | | Need pure Python import patterns? | Read `common/databricks-python-imports/SKILL.md` directly | --- ## Mandatory Skill Dependencies **CRITICAL: Before generating ANY code for the Silver layer, you MUST read and follow the patterns in these common skills. Do NOT generate these patterns from memory.** | Phase | MUST Read Skill (use Read tool on SKILL.md) | What It Provides | |-------|---------------------------------------------|------------------| | All phases | `skills/databricks-expert-agent` | Core extraction principle: extract names from source, never hardcode | | Schema setup | `common/schema-management-patterns` | CREATE SCHEMA DDL with governance metadata | | DQ rules table | `common/databricks-table-properties` | TBLPROPERTIES for the dq_rules metadata table | | DQ rules table | `common/unity-catalog-constraints` | PRIMARY KEY constraint syntax | | Rules loader | `common/databricks-python-imports` | Pure Python module patterns (NO notebook header) | | DLT notebooks | `common/databricks-table-properties` | Silver-layer TBLPROPERTIES (CDF, row tracking, auto-optimize) | | Pipeline config | `skills/databricks-asset-bundles` | DLT pipeline YAML, job YAML, serverless config, multi-user `user_prefix` pattern | | Pipeline config | `common/naming-tagging-standards` | Enterprise naming, COMMENTs, tags, PII classifications for every DDL and resource | | Deployment (if user-triggered) | `common/databricks-autonomous-operations` | Deploy → Poll → Diagnose → Fix → Redeploy loop when jobs/pipelines fail | **NEVER do these without FIRST reading the corresponding skill:** - NEVER write `table_properties={...}` without reading `databricks-table-properties` - NEVER write Python import patterns without reading `databricks-python-imports` - NEVER write Asset Bundle YAML without reading `databricks-asset-bundles` - NEVER write `CREATE SCHEMA` without reading `schema-management-patterns` - NEVER define PK/FK constraints without reading `unity-catalog-constraints` - NEVER skip a `references/*.md` file when the SKILL.md links to it with `See: references/...` - NEVER skip the requirements template in Phase 1 — it forces structured design decisions > **🔴 CRITICAL — Follow `See: references/...` links.** > When a SKILL.md (or this orchestrator) says `See: references/xxx.md for complete pattern`, > you MUST Read that reference file. The SKILL.md is a **summary**; the `references/*.md` > file contains the complete, production-ready pattern. Skipping references means generating > from memory rather than from the specification. > > For Silver specifically, these are the references most often skipped — read ALL of them > during their relevant phase: > > | Reference | Phase | Why | > |-----------|-------|-----| > | `silver/00-silver-layer-setup/assets/templates/requirements-template.md` | Phase 1 | Forces Bronze→Silver mapping, DQ strategy, quarantine decisions before any code | > | `silver/01-dlt-expectations-patterns/references/expectation-patterns.md` | Phase 2/4 | Complete DQ rules table DDL, loader implementation, population examples | > | `silver/01-dlt-expectations-patterns/references/quarantine-patterns.md` | Phase 4 | Complete quarantine table implementation with diagnostic columns | > | `silver/00-silver-layer-setup/references/silver-table-patterns.md` | Phase 4 | DLT table templates, `get_bronze_table()` helper, derived-field patterns | > | `silver/00-silver-layer-setup/references/monitoring-patterns.md` | Phase 5 | DQ monitoring views, referential integrity, data freshness | > | `silver/00-silver-layer-setup/references/pipeline-configuration.md` | Phase 6 | Silver-specific DLT pipeline YAML + DQ setup job YAML | ### 🔴 Non-Negotiable Defaults (Applied to EVERY Silver Table and Pipeline) These defaults are ALWAYS applied. There are NO exceptions, NO overrides, NO alternative options. | Default | Value | Applied Where | NEVER Do This Instead | |---------|-------|---------------|----------------------| | **Serverless** | `serverless: true` | Pipeline YAML | ❌ NEVER set `serverless: false` or define `clusters:` | | **Auto Liquid Clustering** | `cluster_by_auto=True` | Every `@dlt.table()` | ❌ NEVER use `cluster_by=["col1", "col2"]` or `partition_cols=` | | **Edition** | `edition: ADVANCED` | Pipeline YAML | ❌ NEVER use `CORE` or `PRO` (expectations require ADVANCED) | | **Photon** | `photon: true` | Pipeline YAML | ❌ NEVER set `photon: false` | | **Row Tracking** | `"delta.enableRowTracking": "true"` | Every table's `table_properties` | ❌ NEVER omit (breaks downstream MV refresh) | | **Change Data Feed** | `"delta.enableChangeDataFeed": "true"` | Every table's `table_properties` | ❌ NEVER omit (required for incremental propagation) | ```python # ✅ CORRECT: Every @dlt.table() MUST include these @dlt.table( name="silver_anything", table_properties={ "delta.enableChangeDataFeed": "true", "delta.enableRowTracking": "true", # ... other properties from databricks-table-properties }, cluster_by_auto=True # 🔴 MANDATORY on every table, including monitoring views ) ``` ```yaml # ✅ CORRECT: Pipeline YAML MUST include these serverless: true # 🔴 MANDATORY - no classic clusters photon: true # 🔴 MANDATORY - vectorized execution edition: ADVANCED # 🔴 MANDATORY - required for expectations ``` ### Silver-Domain Dependencies | Skill | Requirement | What It Provides | |-------|-------------|------------------| | `silver/01-dlt-expectations-patterns` | **MUST read** | DQ rules table DDL, rules loader cache pattern, DLT decorators, quarantine generation, runtime rule updates | | `silver/02-dqx-patterns` | **Optional** | Read only when user needs richer diagnostics than DLT expectations, or pre-merge validation | --- ## Core Philosophy: Schema Cloning **The Silver layer should essentially clone the source Bronze schema with minimal transformations:** - **Same column names** as Bronze (no complex renaming) - **Same data types** (minimal type conversions) - **Same grain** (no aggregation - that's for Gold) - **Add data quality rules** (the main value-add) - **Add derived flags** (business indicators like `is_return`, `is_out_of_stock`) - **Add business keys** (SHA256 hashes for tracking) - **Add timestamps** (`processed_timestamp`) **What NOT to do in Silver:** - No major schema restructuring - No aggregations (save for Gold) - No complex business logic (simple flags only) - No joining across tables (dimension lookups in Gold) **Why:** Silver is the validated copy of source data. Gold handles complex transformations. This keeps Silver focused on data quality and makes troubleshooting easier (column names match source). ### Python API: this skill standardizes on `import dlt` (`dp` is the recommended forward path) Databricks **recommends the modern `dp` API** (`from pyspark import pipelines as dp`) and it **fully supports expectations**. This skill and its DQ-rules framework (`dlt-expectations-patterns`) are currently written against `import dlt`, so **use `import dlt` here to stay consistent with the framework's decorators.** The `dp` equivalents are drop-in — migrating is a mechanical swap of the import and decorator prefix. ```python # ✅ CURRENT STANDARD for this skill: legacy `dlt` API (matches the DQ-rules framework) import dlt from dq_rules_loader import get_critical_rules_for_table @dlt.table(name="silver_transactions", cluster_by_auto=True) @dlt.expect_all_or_drop(get_critical_rules_for_table("silver_transactions")) def silver_transactions(): return dlt.read_stream(get_bronze_table("bronze_transactions")) ``` ```python # ✅ RECOMMENDED forward path: modern `dp` API (expectations supported — dp.expect_all_or_drop) from pyspark import pipelines as dp from dq_rules_loader import get_critical_rules_for_table @dp.table(name="silver_transactions", cluster_by_auto=True) @dp.expect_all_or_drop(get_critical_rules_for_table("silver_transactions")) def silver_transactions(): return spark.readStream.table("bronze_transactions") ``` **Which to use?** Databricks documents `dp` as the recommended API and both `@dp.expect_all_or_drop()` and `@dp.expect_all()` exist. We keep `import dlt` as this skill's standard only so the rules loader and decorators stay uniform across the framework — **not** because `dp` lacks expectations. To migrate: change `import dlt` → `from pyspark import pipelines as dp`, `@dlt.` → `@dp.`, and `dlt.read_stream(x)` → `spark.readStream.table(x)`. See the [Lakeflow pipelines Python reference](https://docs.databricks.com/aws/en/ldp/developer/python-ref) for the full decorator mapping. --- ## File Structure ``` src/{project}_silver/ ├── setup_dq_rules_table.py # Databricks notebook: Create and populate DQ rules Delta table ├── dq_rules_loader.py # Pure Python (NO notebook header): Load rules from Delta table ├── silver_dimensions.py # DLT notebook: Dimension tables (stores, products, etc.) ├── silver_transactions.py # DLT notebook: Fact table with quarantine ├── silver_inventory.py # DLT notebook: Additional fact tables (if applicable) └── data_quality_monitoring.py # DLT notebook: DQ monitoring views ``` **Critical Files:** - `dq_rules_loader.py` must be **pure Python** (NO `# Databricks notebook source` header) - Run `silver_dq_setup_job` BEFORE deploying DLT pipeline --- ## Working Memory Management This orchestrator spans 6 phases (deployment and Phase 7 are user-triggered). To maintain coherence without context pollution: **After each phase, persist a brief summary note** capturing: - **Phase 1 output:** Schema names (catalog, silver_schema), table list, DQ rules strategy decision - **Phase 2 output:** DQ rules table path, count of rules defined, rule severity distribution - **Phase 3 output:** `dq_rules_loader.py` path, confirmation it is pure Python (no notebook header) - **Phase 4 output:** DLT notebook paths per table, expectation counts, SCD handling decisions - **Phase 5 output:** Monitoring view paths, metric definitions - **Phase 6 output:** Pipeline YAML path, job YAML path, `databricks.yml` sync status - **Phase 7 output (if user-triggered):** Anomaly detection config, schema monitoring status **What to keep in working memory:** Only the current phase's context, the table list from Phase 1, and the previous phase's summary note. Discard intermediate outputs (full DDL strings, DQ rule DataFrames, raw DLT notebook contents) — they are on disk and reproducible. **Critical file note:** `dq_rules_loader.py` must be **pure Python** (NO `# Databricks notebook source` header). Carry this constraint through all phases. --- ## Phased Implementation Workflow ### Phase 1: Requirements & Schema Setup (30 min) **Pre-Condition - MUST read these skills first:** 1. Read `skills/databricks-expert-agent/SKILL.md` - Apply extraction principle throughout 2. Read `common/schema-management-patterns/SKILL.md` - Use for Silver schema DDL **Steps:** 1. **MUST Read AND fill** `assets/templates/requirements-template.md` before writing any code: - Map Bronze tables to Silver tables - Define DQ rules per entity (critical vs warning) - Identify quarantine candidates - Skipping this step consistently leads to incomplete DQ coverage and ad-hoc quarantine patterns 2. Create Silver schema using pattern from `schema-management-patterns` 3. Verify Bronze tables exist AND pin each table's column inventory — run SQL, do NOT infer from local files: ```bash databricks api post /api/2.0/sql/statements -p $PROFILE --json '{ "warehouse_id": "<WAREHOUSE_ID>", "catalog": "<CATALOG>", "statement": "SHOW TABLES IN <CATALOG>.<BRONZE_SCHEMA>", "wait_timeout": "30s" }' ``` If the Bronze schema doesn't exist or has zero tables, STOP and report — the Bronze layer must be completed first. Reading a clone script or inferring from the schema CSV is NOT runtime verification.