gold-layer-setup

# Gold Layer Implementation Orchestrator This skill orchestrates the complete Gold layer implementation process, transforming YAML schema designs into production-ready Delta tables with merge scripts, FK constraints, and Asset Bundle jobs. It is the natural successor to the `gold/00-gold-layer-design` skill. **Predecessor:** `gold/00-gold-layer-design` skill (YAML files must exist before using this skill) **Core Philosophy:** YAML as Single Source of Truth — Python reads YAML at runtime, DDL is generated dynamically, schema changes require YAML edits only. ## When to Use This Skill - Implementing Gold layer tables from completed YAML designs - Creating generic YAML-driven table setup scripts - Writing Silver-to-Gold MERGE scripts (dimensions and facts) - Applying FK constraints after table creation - Configuring Asset Bundle jobs for Gold layer deployment - Troubleshooting Gold layer implementation errors (duplicate keys, schema mismatches, grain violations) ## Prerequisites **MANDATORY:** Complete `gold/00-gold-layer-design` skill first. The following must exist: - [ ] YAML schema files in `gold_layer_design/yaml/{domain}/*.yaml` - [ ] ERD documentation (`erd_master.md`) - [ ] Column lineage documentation (`COLUMN_LINEAGE.csv`) ## Critical Dependencies (Read at Indicated Phase) ### Gold-Domain Skills | Skill | Read At | Purpose | |-------|---------|---------| | `pipeline-workers/01-yaml-table-setup` | Phase 1 | YAML-to-DDL patterns, setup script structure | | `pipeline-workers/02-merge-patterns` | Phase 2 | SCD Type 1/2, fact aggregation, column mapping | | `pipeline-workers/03-deduplication` | Phase 2 | Deduplication before MERGE (mandatory) | | `pipeline-workers/04-grain-validation` | Phase 2 | Grain inference from PK, pre-merge validation | | `pipeline-workers/05-schema-validation` | Phase 2 | DataFrame-to-DDL schema validation | ### Common Skills | Skill | Read At | Purpose | |-------|---------|---------| | `databricks-asset-bundles` | Phase 3 | Job YAML patterns, serverless config, sync | | `databricks-table-properties` | Phase 1 | Standard TBLPROPERTIES by layer | | `unity-catalog-constraints` | Phase 1 | PK/FK constraint application patterns | | `schema-management-patterns` | Phase 1 | CREATE SCHEMA IF NOT EXISTS | | `databricks-python-imports` | Phase 2 | Pure Python modules, avoid sys.path issues | | `databricks-expert-agent` | All | Schema extraction over generation principle | | `databricks-autonomous-operations` | Phase 4+ (if user-triggered) | Deploy → Poll → Diagnose → Fix → Redeploy loop when jobs fail | ### 🔴 Non-Negotiable Defaults (Applied to EVERY Gold Table and Job) These defaults are ALWAYS applied. There are NO exceptions, NO overrides, NO alternative options. | Default | Value | Applied Where | NEVER Do This Instead | |---------|-------|---------------|----------------------| | **Serverless** | `environments:` block with `environment_key` | Every job YAML | ❌ NEVER define `job_clusters:` or `existing_cluster_id:` | | **Environments V4** | `environment_version: "4"` | Every job's `environments.spec` | ❌ NEVER omit or use older versions | | **Auto Liquid Clustering** | `CLUSTER BY AUTO` | Every `CREATE TABLE` in `setup_tables.py` | ❌ NEVER use `CLUSTER BY (col1, col2)` or `PARTITIONED BY` | | **Change Data Feed** | `'delta.enableChangeDataFeed' = 'true'` | Every table's TBLPROPERTIES | ❌ NEVER omit (required for incremental propagation) | | **Row Tracking** | `'delta.enableRowTracking' = 'true'` | Every table's TBLPROPERTIES | ❌ NEVER omit (breaks downstream MV refresh) | | **notebook_task** | `notebook_task:` with `base_parameters:` | Every task in job YAML | ❌ NEVER use `python_task:` or CLI-style `parameters:` | | **FK graceful failure** | Warn + continue | Every FK in `add_fk_constraints.py` | ❌ NEVER raise/crash if FK constraint fails (serverless cannot apply FKs to non-PK columns) | See [Setup Script Patterns](references/setup-script-patterns.md) for DDL template and [Asset Bundle Job Patterns](references/asset-bundle-job-patterns.md) for job YAML template. ### 🔴 YAML Extraction Over Generation (Merge Scripts Included) **EVERY value below MUST be extracted from Gold YAML files or COLUMN_LINEAGE.csv. NEVER generate, guess, or hardcode.** Table names, column names/types, PKs, FKs, business keys, grain types, SCD types, source Silver tables, and column mappings — ALL come from YAML. The ONLY things coded by hand are aggregation expressions and derived column formulas (business logic). See [Merge Script Patterns](references/merge-script-patterns.md) for the complete extraction helpers: `load_table_metadata()`, `build_inventory()`, `load_column_mappings_from_yaml()`, `build_merge_condition()`, and correct vs. incorrect usage examples. ## Quick Start (3-4 hours) ### What You'll Create 1. `setup_tables.py` — Generic script reads YAML, creates all tables dynamically 2. `add_fk_constraints.py` — Apply FK constraints AFTER all PKs exist 3. `merge_gold_tables.py` — Merge Silver to Gold with explicit column mapping 4. `gold_setup_job.yml` — Asset Bundle job for table setup + FK constraints 5. `gold_merge_job.yml` — Asset Bundle job for periodic MERGE operations ### Deliverables Checklist **Setup Scripts:** - [ ] `src/{project}_gold/setup_tables.py` — Generic YAML-driven table creation - [ ] `src/{project}_gold/add_fk_constraints.py` — FK constraint application - [ ] (User-triggered) Verify: `databricks bundle run gold_setup_job -t dev` **Merge Scripts:** - [ ] `src/{project}_gold/merge_gold_tables.py` — Silver-to-Gold MERGE - [ ] Dimension merges (SCD Type 1 or 2) with deduplication - [ ] Fact merges with aggregation and grain validation - [ ] (User-triggered) Verify: `databricks bundle run gold_merge_job -t dev` **Asset Bundle Jobs:** - [ ] `resources/gold/gold_setup_job.yml` — Setup + FK constraints (two tasks) - [ ] `resources/gold/gold_merge_job.yml` — Periodic merge with schedule - [ ] YAML files synced in `databricks.yml` ### Deployment Commands (run when ready — NOT auto-executed by this skill) > **Client note:** IDE runs these in a terminal; Genie Code runs the `databricks bundle …` commands via `runDatabricksCli` (be on the bundle's page; generated files anchor to `{REPO_ROOT}`). See `skills/genie-code-environment`. ```bash # 1. Deploy setup job (creates tables from YAML) databricks bundle deploy -t dev databricks bundle run gold_setup_job -t dev # 2. Verify tables created # SHOW TABLES IN {catalog}.{gold_schema} # 3. Run merge job (Silver to Gold) databricks bundle run gold_merge_job -t dev ``` --- ## Working Memory Management This orchestrator spans 5+ phases. To maintain coherence without context pollution, follow this note-taking discipline: **After each phase, persist a brief summary note** (e.g., in a scratch file or conversation notes) capturing: - **Phase 0 output:** Silver contract pass/fail per table, any YAML fixes made, resolution report location - **Phase 1 output:** Table inventory dict, YAML base path, count of tables created, any FK failures - **Phase 2 output:** Merge function inventory (which tables use SCD1 vs SCD2, aggregated vs transaction), any column mapping issues - **Phase 3 output:** Job YAML file paths, databricks.yml sync status - **Phase 4 output (if user-triggered):** Deployment results, validation failures to investigate **What to keep in working memory:** Only the current phase's worker skill, the table inventory dict, and the previous phase's summary note. Discard intermediate tool outputs (DDL strings, full DataFrames, raw validation SQL results) — they are reproducible from YAML. **What to offload to references:** Each pipeline-worker skill has `Pipeline Notes to Carry Forward` and `Next Step` sections. Read them to know what to pass to the next phase. The worker skills form a chain: `01 → 02 → 03 → 04 → 05`. --- ## Step-by-Step Workflow ### Phase 0: Upstream Contract Validation (15 min) **MANDATORY: Execute before proceeding to any implementation phase.** **Reference:** `data_product_accelerator/skills/gold/01-gold-layer-setup/references/design-to-pipeline-bridge.md` — Full validation logic documentation **Purpose:** Validate that all source column names and types referenced in YAML lineage actually exist in the deployed upstream tables. This catches column name mismatches BEFORE any code is written, eliminating the most common source of iteration. **Steps:** 1. Execute `scripts/validate_upstream_contracts.py` with the project's `catalog` and `source_schema` widget parameters (or run `run_phase0_validation()` inline from the script) 2. Capture and paste the full printed output — it produces a PASS/FAIL report per Gold table 3. If ANY table shows FAILED, fix YAML lineage `silver_column` values in `gold_layer_design/yaml/` to match actual source columns, then re-run the script **Gate:** ALL contracts must show PASSED in the script output before proceeding to Phase 1. **Minimum-viable validation (if scripts unavailable or context-constrained):** For each Gold table you plan to implement, run `DESCRIBE TABLE {catalog}.{silver_schema}.{silver_table}` and confirm every non-GENERATED `silver_column` in its YAML lineage exists. Output one line per table: ``` dim_destination: 7 lineage columns validated against silver_destinations — PASS dim_user: 9 lineage columns validated against silver_users — PASS ``` If ANY column is missing, fix the YAML lineage before proceeding. **Backup guardrail:** The merge template (`scripts/merge_gold_tables_template.py`) also embeds `validate_upstream_contracts()` as a fail-fast check in `main()`. Even if Phase 0 is skipped, the merge job will abort with a clear error before any MERGE executes. **Output:** Column resolution reports for all Gold tables, confirming source→Gold mappings are correct. --- ### Phase 1: YAML-Driven Table Creation (30 min) **MANDATORY: Read each skill below using the Read tool BEFORE writing any code for this phase.** If context-constrained, read AT MINIMUM these two (they prevent the most common errors): - `data_product_accelerator/skills/common/unity-catalog-constraints/SKILL.md` — prevents FK-to-non-PK errors (the #1 Gold layer failure mode) - `data_product_accelerator/skills/gold/pipeline-workers/01-yaml-table-setup/SKILL.md` — prevents DDL and YAML discovery errors Full list: 1. `data_product_accelerator/skills/gold/pipeline-workers/01-yaml-table-setup/SKILL.md` — YAML-to-DDL patterns, `find_yaml_base()`, `build_create_table_ddl()` 2. `data_product_accelerator/skills/common/databricks-table-properties/SKILL.md` — Standard TBLPROPERTIES by layer 4. `data_product_accelerator/skills/common/unity-catalog-constraints/SKILL.md` — PK/FK `ALTER TABLE` patterns, NOT NULL requirements 5. `data_product_accelerator/skills/common/schema-management-patterns/SKILL.md` — `CREATE SCHEMA IF NOT EXISTS` pattern **Activities:** 1. Create `setup_tables.py` — Single generic script reads ALL YAML files, creates tables 2. Create `add_fk_constraints.py` — Applies FK constraints AFTER all PKs exist 3. Define standard table properties (CDF, row tracking, auto-optimize, layer=gold) 4. Handle schema creation with `CREATE SCHEMA IF NOT EXISTS` 5. Enable Predictive Optimization on Gold schema **Key Implementation Rules:** - FK constraints via `ALTER TABLE` AFTER all PKs exist (never inline in CREATE TABLE) - PK columns must be NOT NULL in YAML - Use `CREATE OR REPLACE TABLE` for idempotent setup - Include error handling with try/except for constraint application - YAML directory discovery pattern (`find_yaml_base()`) - PyYAML dependency in job environment **Output:** `src/{project}_gold/setup_tables.py` and `src/{project}_gold/add_fk_constraints.py` See `references/setup-script-patterns.md` for complete implementation patterns. See `references/fk-constraint-patterns.md` for FK constraint details. See `scripts/setup_tables_template.py` for starter template. See `scripts/add_fk_constraints_template.py` for starter template. --- ### Phase 1b: Advanced Setup Patterns (Optional) **Read if applicable:** `references/setup-advanced-patterns.md` These patterns extend Phase 1 based on design decisions from `design-workers/02-dimension-patterns`: **Role-Playing Dimension Views:** If any dimension has `dimension_pattern: role_playing` in YAML, create alias views after table creation. Example: `dim_date` → `dim_order_date`, `dim_ship_date`, `dim_delivery_date`. **Unknown Member Row Insertion:** Insert a `-1` key "Unknown" member row in every dimension table AFTER PKs are applied but BEFORE FK constraints. This prevents NULL foreign keys for late-arriving facts. **Execution Order:** 1. Create tables (Phase 1) 2. Apply PK constraints (Phase 1) 3. Create role-playing views (Phase 1b) 4. Insert unknown member rows (Phase 1b) 5. Apply FK constraints (Phase 1) 6. Run merge scripts (Phase 2) --- ### Phase 2: MERGE Script Implementation (2 hours) **MANDATORY: Read each skill below using the Read tool BEFORE writing any merge code.** If context-constrained, read AT MINIMUM these two: - `data_product_accelerator/skills/gold/pipeline-workers/03-deduplication/SKILL.md` — prevents MERGE duplicate-row errors (the #1 merge failure mode) - `data_product_accelerator/skills/gold/pipeline-workers/05-schema-validation/SKILL.md` — prevents schema mismatch at runtime Full list: 1. `data_product_accelerator/skills/gold/pipeline-workers/02-merge-patterns/SKILL.md` — SCD Type 1/2, fact aggregation, column mapping, `spark_sum` alias 2. `data_product_accelerator/skills/gold/pipeline-workers/03-deduplication/SKILL.md` — Deduplication before MERGE (ALWAYS required, prevents `DELTA_MULTIPLE_SOURCE_ROW_MATCHING_TARGET_ROW_IN_MERGE`) 3. `data_product_accelerator/skills/gold/pipeline-workers/04-grain-validation/SKILL.md` — Grain inference from PK, transaction vs aggregated patterns 4. `data_product_accelerator/skills/gold/pipeline-workers/05-schema-validation/SKILL.md` — `validate_merge_schema()`, DataFrame-to-DDL checks 5. `data_product_accelerator/skills/common/databricks-python-imports/SKILL.md` — Pure Python modules, avoid `sys.path` issues in serverless 6. `data_product_accelerator/skills/gold/01-gold-layer-setup/references/advanced-merge-patterns.md` — Accumulating snapshot, factless fact, periodic snapshot, junk dimension patterns 7. `data_product_accelerator/skills/gold/01-gold-layer-setup/references/design-to-pipeline-bridge.md` — Silver contract validation, lineage-driven column builder, scripted column resolution **Activities:** **Step 0 — EXTRACTION FIRST (before writing ANY code):** 1. Load ALL Gold YAML files using `load_table_metadata()` (see YAML Extraction section above) 2. Load `COLUMN_LINEAGE.csv` using `load_column_mappings()` for Silver→Gold renames 3. For each table: extract `table_name`, `pk_columns`, `business_key`, `scd_type`, `grain`, `columns`, `lineage` 4. Build a table inventory dict keyed by table name — this drives ALL merge functions 5. Verify source tables exist: `spark.table(source_table)` before coding any merge logic 6. Use `build_column_expressions()` from `references/design-to-pipeline-bridge.md` for DIRECT_COPY/RENAME/CAST/GENERATED columns instead of writing `.withColumn()` calls manually — this automates ~70% of column mappings deterministically from YAML lineage **Step 1 — Create merge functions using extracted metadata:**