gold-layer-design

name	gold-layer-design
description	End-to-end orchestrator for designing complete Gold layer schemas with ERDs, YAML files, lineage tracking, and comprehensive business documentation. Guides users through dimensional modeling, ERD creation (master/domain/summary based on table count), YAML schema generation, column-level lineage documentation, business onboarding guide creation, source table mapping, and design validation. Orchestrates design-workers (05-erd-diagrams, 06-table-documentation, 01-grain-definition, 07-design-validation, 02-dimension-patterns, 03-fact-table-patterns, 04-conformed-dimensions, 08-industry-alignment). Use when designing a Gold layer from scratch, creating dimensional models, documenting business processes, aligning to Databricks Industry Vibe Data Models, or preparing for Gold layer implementation.
license	Apache-2.0
clients	["ide_cli","genie_code"]
bundle_resource	none
deploy_verb	bundle_deploy
deploy_note	Design-only: produces ERDs/YAML schemas/lineage/docs consumed by gold-layer-setup; no deployed resource of its own. Downstream artifacts deploy via `bundle deploy --target dev`. On Genie Code, this skill and its design-workers must write all generated design files under the cloned repo root (`{REPO_ROOT}` = `state_file_root` from `skills/vibecoding-state`, e.g. `gold_layer_design/`), 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":"3.0.0","domain":"gold","role":"orchestrator","pipeline_stage":1,"pipeline_stage_name":"gold-design","next_stages":["bronze-layer-setup"],"reads":["context/*.csv","context/industry_reference.yaml"],"emits":["gold_layer_design/yaml/","gold_layer_design/erd_master.md","gold_layer_design/docs/BUSINESS_ONBOARDING_GUIDE.md","gold_layer_design/COLUMN_LINEAGE.csv","gold_layer_design/SOURCE_TABLE_MAPPING.csv","gold_layer_design/INDUSTRY_CROSSWALK.csv","gold_layer_design/INDUSTRY_ALIGNMENT.md"],"workers":["design-workers/01-grain-definition","design-workers/02-dimension-patterns","design-workers/03-fact-table-patterns","design-workers/04-conformed-dimensions","design-workers/05-erd-diagrams","design-workers/06-table-documentation","design-workers/07-design-validation","design-workers/08-industry-alignment"],"common_dependencies":["databricks-expert-agent","naming-tagging-standards"],"dependencies":["design-workers/01-grain-definition","design-workers/02-dimension-patterns","design-workers/03-fact-table-patterns","design-workers/04-conformed-dimensions","design-workers/05-erd-diagrams","design-workers/06-table-documentation","design-workers/07-design-validation","design-workers/08-industry-alignment"],"last_verified":"2026-02-07","volatility":"low","upstream_sources":[]}

Gold Layer Design Orchestrator

This skill orchestrates the complete Gold layer design process, ensuring all mandatory deliverables are created with proper dependencies on specialized Gold layer skills.

When to Use This Skill

• Designing a Gold layer from scratch for a new project
• Creating dimensional models (facts and dimensions)
• Documenting business processes and data lineage
• Preparing for Gold layer implementation (before 03b-gold-layer-setup-prompt)
• Generating mandatory documentation (Business Onboarding Guide, Source Table Mapping, Column Lineage CSV)

Critical Dependencies (Read at Indicated Phase)

This skill orchestrates the following skills. Each MUST be read and followed at the phase where it is invoked:

Skill	Read At	Purpose
design-workers/01-grain-definition	Phase 2	Grain type decision tree for fact tables
design-workers/02-dimension-patterns	Phase 2	Dimension design patterns (role-playing, junk, degenerate, hierarchies)
design-workers/03-fact-table-patterns	Phase 2	Fact table patterns (measure additivity, factless, accumulating snapshots)
design-workers/04-conformed-dimensions	Phase 2	Enterprise integration (bus matrix, conformed dims, drill-across)
design-workers/05-erd-diagrams	Phase 3	ERD creation and organization strategy
design-workers/06-table-documentation	Phase 4	Dual-purpose documentation standards
design-workers/07-design-validation	Phase 8	YAML↔ERD↔Lineage cross-validation
design-workers/08-industry-alignment	Phase 0 & 2	(Optional) Align the design to Databricks Industry Vibe Data Models — coverage overlay + active guidance. Read only when industry_reference_source ≠ none

🔴 Non-Negotiable Defaults (YAML Schemas MUST Encode These)

The design phase produces YAML schemas that drive all downstream table creation. These YAML schemas MUST include the following properties so that setup_tables.py generates compliant DDL.

Default	YAML Location	Value	NEVER Do This Instead
Auto Liquid Clustering	clustering: field	auto	❌ NEVER specify column names or omit clustering
Change Data Feed	table_properties:	'delta.enableChangeDataFeed' = 'true'	❌ NEVER omit (required for incremental propagation)
Row Tracking	table_properties:	'delta.enableRowTracking' = 'true'	❌ NEVER omit (breaks downstream MV refresh)
Auto-Optimize	table_properties:	optimizeWrite + autoCompact = 'true'	❌ NEVER omit
Layer Tag	table_properties:	'layer' = 'gold'	❌ NEVER omit or use wrong layer
PK NOT NULL	columns: with nullable: false	All PRIMARY KEY columns	❌ NEVER leave PK columns nullable

# ✅ CORRECT: Every Gold YAML schema MUST include these
table_name: dim_example
clustering: auto  # 🔴 MANDATORY (generates CLUSTER BY AUTO)
table_properties:
  delta.enableChangeDataFeed: "true"     # 🔴 MANDATORY
  delta.enableRowTracking: "true"        # 🔴 MANDATORY
  delta.autoOptimize.optimizeWrite: "true"
  delta.autoOptimize.autoCompact: "true"
  layer: "gold"

Note: Serverless and Environments V4 are job-level concerns enforced by gold/01-gold-layer-setup and databricks-asset-bundles during the implementation phase, not during design.

Quick Start (4-8 hours)

Deliverables Checklist

ERD Organization (based on table count):

• Master ERD (erd_master.md) - ALWAYS required
• Domain ERDs (erd/erd_{domain}.md) - If 9+ tables
• Summary ERD (erd_summary.md) - If 20+ tables

YAML Schemas (organized by domain):

• gold_layer_design/yaml/{domain}/dim_*.yaml - Dimension schemas
• gold_layer_design/yaml/{domain}/fact_*.yaml - Fact schemas

Mandatory Documentation (ALL required):

• BUSINESS_ONBOARDING_GUIDE.md - Business processes + real-world stories
• COLUMN_LINEAGE.csv - Machine-readable column lineage
• COLUMN_LINEAGE.md - Human-readable lineage
• SOURCE_TABLE_MAPPING.csv - Source table inclusion/exclusion rationale
• DESIGN_SUMMARY.md - Design decisions
• DESIGN_GAP_ANALYSIS.md - Coverage analysis
• README.md - Navigation hub

Advisory (only when an industry reference is provided):

• INDUSTRY_CROSSWALK.csv - Industry entity ↔ Gold table crosswalk (from 08-industry-alignment)
• INDUSTRY_ALIGNMENT.md - Industry coverage scorecard (from Validation 6)

ERD Organization Decision

Tables	Strategy	Required Deliverables
1-8	Master only	erd_master.md
9-20	Master + Domain	erd_master.md + erd/erd_{domain}.md
20+	Master + Domain + Summary	erd_master.md + erd_summary.md + erd/erd_{domain}.md

---

Working Memory Management

This orchestrator spans 9 phases over 4-8 hours. To maintain coherence without context pollution:

After each phase, persist a brief summary note capturing:

• Phase 0: Table inventory dict, entity classifications (dim/fact/bridge), FK relationships, suggested domains
• Phase 1: Project context (name, schemas, use cases, stakeholders)
• Phase 2: Dimensional model: dims, facts, measures, relationships, bus matrix, domain assignments
• Phase 3: ERD file paths, strategy used (master-only / master+domain / full)
• Phase 4: YAML file paths per domain, schema count, lineage gaps
• Phases 5-7: Output file paths (COLUMN_LINEAGE.csv, BUSINESS_ONBOARDING_GUIDE.md, SOURCE_TABLE_MAPPING.csv)
• Phase 8: Validation pass/fail summary, inconsistencies to fix

What to keep in working memory: Current phase's design-worker skill, the table inventory dict (Phase 0), and previous phase's summary. Discard intermediate outputs (full CSV data, raw ERD source, complete YAML contents) — they are on disk.

What to offload: Each design-worker skill has Design Notes to Carry Forward and Next Step sections. Read them to know what to pass to the next phase.

Skill reading strategy (just-in-time, per phase): Read worker skills only when the phase that needs them is about to start. This keeps the salient rules at the top of the context window when they matter most.

Phase	Worker/reference skills to read at the start of this phase
0	skills/databricks-expert-agent, common/naming-tagging-standards, design-workers/00-schema-intake; if industry_reference_source ≠ none: design-workers/08-industry-alignment (coverage overlay)
1	design-workers/01-business-onboarding
2	design-workers/02-dimension-patterns, design-workers/03-fact-patterns, references/dimensional-modeling-guide.md; if industry_reference_source ≠ none: design-workers/08-industry-alignment (active guidance rules)
3	design-workers/04-erd-patterns, references/erd-organization-strategy.md
4	design-workers/05-yaml-schema-patterns, design-workers/06-table-documentation, references/yaml-schema-patterns.md, references/lineage-documentation-guide.md
5-7	design-workers/06-table-documentation (lineage CSV + business onboarding guide + source mapping)
8	design-workers/07-design-validation, references/validation-checklists.md

Anti-pattern — do NOT batch-read all design-worker skills upfront. The retrospective found that pre-loading every worker at Phase 0 pushes the common-skill rules and the active phase's rules out of the attention window, producing format divergence across YAML files and non-standard transformation types. Read each skill the moment its phase begins.

---

Step-by-Step Workflow

Phase 0: Source Schema Intake (MANDATORY First Step)

PREREQUISITE — Read Common Skills Before Proceeding:

Before parsing the schema, read these two common skills. They set enterprise-wide constraints that every downstream phase depends on. Skipping them is the most common root cause of format divergence across YAML files.

1. skills/databricks-expert-agent/SKILL.md — Retain: "Extract, Don't Generate" (all table/column names come from the YAML or source schema, never from memory), CLUSTER BY AUTO, CDF + Row Tracking, comments + tags on every object.
2. data_product_accelerator/skills/common/naming-tagging-standards/SKILL.md — Retain: snake_case everywhere, dim_/fact_/bridge_ prefixes, dual-purpose description pattern <Definition>. Business: <context>. Technical: <details>. (the angle brackets are placeholders — do NOT write literal < or [ characters into descriptions), mandatory tags (layer, domain, PII).

These rules are the authoritative source whenever a domain-specific skill below paraphrases them.

This is the entry point for the entire data platform build. The customer provides a source schema CSV (e.g., context/Wanderbricks_Schema.csv) containing table and column metadata. This phase parses it into a structured inventory that drives all subsequent design decisions.

Input: context/{ProjectName}_Schema.csv — CSV with columns: table_catalog, table_schema, table_name, column_name, ordinal_position, full_data_type, data_type, is_nullable, comment

Steps:

1. Read and parse the schema CSV from context/:

   MANDATORY: Read references/schema-intake-patterns.md for complete implementations of parse_schema_csv(), classify_tables(), and infer_relationships().

   • parse_schema_csv(csv_path) — Reads CSV into structured dict keyed by table name, with columns, types, nullability, and comments per table.
   • Output: {table_name: {"columns": [...], "column_count": N, "catalog": ..., "schema": ...}}

2. Classify tables as dimensions, facts, or bridge/junction:

   • classify_tables(schema) — Classifies each table based on column patterns:
     • bridge: 3 or fewer columns AND 2+ FK-like columns
     • fact: 2+ FK columns AND numeric measures, OR 2+ timestamps AND 2+ FKs
     • dimension: everything else
   • Also identifies: PK candidates, FK columns, measures, timestamps per table
   • This classification is a HEURISTIC, not ground truth. It mis-classifies tables whose numeric columns are descriptive attributes rather than additive measures (geographic coordinates, prices, physical counts), and tables with no numeric columns or only one FK. After running it, print the full classification, then explicitly document every override you make and WHY (e.g. "properties reclassified dimension→… : base_price/latitude/bedrooms are attributes, not measures"). Never silently correct a classification — an undocumented override becomes invisible technical debt if a later step reads the raw classification instead of DESIGN_DECISIONS.md.

3. Identify FK relationships from column comments and naming patterns:

   • infer_relationships(classified) — Two inference strategies:
     • Pattern 1: Column comment contains "Foreign Key to 'X'" → direct FK
     • Pattern 2: Column name other_table_id matches a known table name
   • Returns list of {from_table, from_column, to_table, to_column, source}

4. (Optional) Industry coverage overlay — only if industry_reference_source ≠ none:

   Read data_product_accelerator/skills/gold/design-workers/08-industry-alignment/SKILL.md. Run build_industry_overlay(classified, Path(industry_reference_path)) from its scripts/industry_overlay.py to annotate each source table with its industry entity (Covered / Absorbed / Gap) and a provisional coverage %. This is a lens over the parsed source schema — it never invents tables. Fold the printed overlay into the Schema Intake Report and carry every core/extended Gap into Phase 1/2 for a Waived/Planned decision. Skip this step entirely when no industry reference is provided.

5. Produce Schema Intake Report:

The report summarizes: table inventory, entity classification, inferred relationships, and domain suggestions. This feeds Phase 1 (Requirements) and Phase 2 (Dimensional Modeling).

Output:

• Printed table inventory with classification (dimension/fact/bridge)
• Inferred FK relationships list
• Suggested domain groupings
• Recommended dimensional model skeleton (which tables → which Gold dims/facts)

Critical: The schema CSV is the single source of truth for table and column names. ALL downstream phases must extract names from the parsed schema — never generate table/column names from memory.

---

Phase 1: Requirements Gathering

Collect the following project context (enhanced with schema intake):

Field	Example
Project Name	wanderbricks_analytics
Source Schema	wanderbricks (from schema CSV)
Gold Schema	wanderbricks_gold (convention: {project}_gold)
Business Domain	travel, hospitality (inferred from schema CSV)
Primary Use Cases	booking analytics, revenue reporting
Key Stakeholders	Revenue Ops, Marketing
Reporting Frequency	Daily, Weekly, Monthly
Table Count	15 tables — 8 dims, 5 facts, 2 bridge (from Phase 0)
Inferred Relationships	12 FK relationships (from Phase 0)
Industry Vertical	retail, banking, healthcare (optional — enables industry alignment)
Industry Reference Source	vibe_generated | published | none (default none)
Industry Reference Path	context/industry_reference.yaml (required when source ≠ none)

Output: Populated project context document (with Phase 0 schema intake data incorporated)

---

Phase 2: Dimensional Model Design

Read and Follow: references/dimensional-modeling-guide.md

Activities:

1. Identify 2-5 dimensions with SCD type decisions (Type 1 vs Type 2)
2. Identify 1-3 facts with explicit grain definitions
3. Define measures and metrics with calculation logic
4. Define relationships (FK constraints)
5. Assign tables to domains (Location, Product, Time, Sales, etc.)