gold-layer-setup

name	gold-layer-setup
description	End-to-end orchestrator for implementing Gold layer tables, merge scripts, FK constraints, and Asset Bundle jobs from YAML schema definitions. Guides users through Silver contract validation, YAML-driven table creation, Silver-to-Gold MERGE operations (SCD Type 1/2 dimensions, aggregated/transaction facts, accumulating snapshots, factless facts, periodic snapshots, junk dimensions), foreign key constraint application, Asset Bundle job configuration, and post-deployment validation. Orchestrates pipeline-workers (01-yaml-table-setup, 02-merge-patterns, 03-deduplication, 04-grain-validation, 05-schema-validation) and common skills (databricks-asset-bundles, databricks-table-properties, databricks-python-imports, schema-management-patterns, unity-catalog-constraints, databricks-expert-agent). Use when implementing Gold layer from YAML designs, creating table setup scripts, writing merge scripts, deploying Gold layer jobs, or troubleshooting Gold layer implementation errors.
license	Apache-2.0
clients	["ide_cli","genie_code"]
bundle_resource	jobs
deploy_verb	bundle_deploy
deploy_note	Gold tables + Silver-to-Gold MERGE jobs + FK constraints deploy via `bundle deploy --target dev` (runDatabricksCli on Genie Code). Strip DEFAULT clauses from DDL (SCD2 cols set in INSERT/MERGE); printSchema() before MERGE. On Genie Code, write generated table/merge scripts under the cloned repo root (`{REPO_ROOT}` = `state_file_root` from `skills/vibecoding-state`, e.g. `src/`), not a bare relative path — relative paths resolve against the page CWD (see `skills/genie-code-environment` §8).
coverage	full
metadata	{"author":"prashanth subrahmanyam","version":"2.0.0","domain":"gold","role":"orchestrator","pipeline_stage":4,"pipeline_stage_name":"gold-implementation","next_stages":["project-planning"],"workers":["pipeline-workers/01-yaml-table-setup","pipeline-workers/02-merge-patterns","pipeline-workers/03-deduplication","pipeline-workers/04-grain-validation","pipeline-workers/05-schema-validation"],"common_dependencies":["databricks-asset-bundles","databricks-table-properties","databricks-python-imports","schema-management-patterns","unity-catalog-constraints","databricks-expert-agent","naming-tagging-standards","databricks-autonomous-operations"],"dependencies":["pipeline-workers/01-yaml-table-setup","pipeline-workers/02-merge-patterns","pipeline-workers/03-deduplication","pipeline-workers/04-grain-validation","pipeline-workers/05-schema-validation","databricks-asset-bundles","databricks-table-properties","databricks-python-imports","schema-management-patterns","unity-catalog-constraints","databricks-expert-agent"],"last_verified":"2026-02-07","volatility":"low","upstream_sources":[]}

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 for DDL template and Asset Bundle Job Patterns 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 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.

# 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
3. data_product_accelerator/skills/common/unity-catalog-constraints/SKILL.md — PK/FK ALTER TABLE patterns, NOT NULL requirements
4. 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: