Bronze Layer Setup

Bronze Layer Setup

Create Bronze layer tables with test data for rapid prototyping of Medallion Architecture.

When to Use

• Setting up Bronze layer tables for a new project
• Creating test/demo data for Silver/Gold layer development
• Rapid prototyping of Medallion Architecture
• Bootstrapping a Databricks project with realistic test data
• Copying data from existing sources to a new Bronze schema

For Faker-specific patterns (corruption rates, function signatures, provider examples), the faker-data-generation worker skill is loaded at Step 4 via the Mandatory Skill Dependencies table below.

Core Philosophy

The Bronze layer in this approach is optimized for testing, demos, and rapid prototyping:

• Quick setup with realistic test data
• Faker data generation as the primary method
• Unity Catalog compliance (proper governance metadata)
• Change Data Feed enabled for downstream Silver/Gold testing
• Automatic liquid clustering for query optimization
• Flexible data sources (generate, copy, or reference existing)
• NOT for production ingestion (use separate ingestion pipelines for that)

🔴 Non-Negotiable Defaults (Applied to EVERY Bronze Table and Job)

These defaults are ALWAYS applied. There are NO exceptions, NO overrides, NO alternative options.

-- ✅ CORRECT: Every Bronze table DDL MUST include
CREATE TABLE IF NOT EXISTS {catalog}.{schema}.{table_name} (
    ...
)
USING DELTA
CLUSTER BY AUTO          -- 🔴 MANDATORY
TBLPROPERTIES (
    'delta.enableChangeDataFeed' = 'true',          -- 🔴 MANDATORY
    'delta.autoOptimize.optimizeWrite' = 'true',    -- 🔴 MANDATORY
    'delta.autoOptimize.autoCompact' = 'true',      -- 🔴 MANDATORY
    'layer' = 'bronze'
)

# ✅ CORRECT: Every Bronze job MUST include
environments:
  - environment_key: "default"
    spec:
      environment_version: "4"   # 🔴 MANDATORY
tasks:
  - task_key: setup_tables
    environment_key: default     # 🔴 MANDATORY on every task
    notebook_task:               # 🔴 MANDATORY (never python_task)
      notebook_path: ../src/setup_tables.py
      base_parameters:           # 🔴 MANDATORY (never CLI-style parameters)
        catalog: ${var.catalog}

Quick Start (30 minutes)

What You'll Create:

1. setup_tables.py - DDL definitions for all Bronze tables
2. generate_dimensions.py - Faker-based dimension data generator
3. generate_facts.py - Faker-based fact data generator (with FK integrity)
4. bronze_setup_job.yml + bronze_data_generator_job.yml - Asset Bundle jobs

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. Generated bundle files anchor to dp_bundle_root (= <artifact_root>/<use_case_slug>_dab), and on Genie Code that folder is also the bundle deploy page-context root — be on the dp_bundle_root page to deploy. The bundle job is the only mechanism that creates tables; never run the DDL/clone directly. See skills/genie-code-environment.

# 1. Deploy setup job
databricks bundle deploy -t dev

# 2. Create tables
databricks bundle run bronze_setup_job -t dev

# 3. Generate data (dimensions -> facts in sequence)
databricks bundle run bronze_data_generator_job -t dev

Key Decisions:

• Data Source: Faker (recommended) | Existing tables | External copy
• Record Counts: Dimensions: 100-200 | Facts: 1,000-10,000
• Tables Needed: 5-10 tables (dimensions + facts)

Output: Bronze Delta tables with Change Data Feed enabled, ready for Silver layer testing

Working Memory Management

This orchestrator spans 5 steps (Step 6 is user-triggered). To maintain coherence without context pollution:

After each step, persist a brief summary note capturing:

• Step 1 output: Requirements filled — project name, entity list, data source approach, record counts
• Step 2 output: Data source decision (Faker / existing / external), rationale
• Step 3 output: DDL file path (setup_tables.py), count of tables defined, any schema deviations
• Step 4 output: Generator file paths, Faker config decisions (providers, FK integrity strategy)
• Step 5 output: Job YAML file paths, databricks.yml sync status
• Step 6 output (if user-triggered): Deployment results, row counts per table, CDF verification status

What to keep in working memory: Only the current step's context, the table list from Step 1, and the previous step's summary note. Discard intermediate outputs (full DDL strings, generated DataFrames) — they are on disk and reproducible.

Workflow

Step 1: Gather Requirements (15 min)

🔴 MANDATORY — DO NOT SKIP even if the user's request seems complete.

Fill in the requirements template: references/requirements-template.md

• Project name, entity list (5-10 tables), data source approach
• Domain taxonomy, data classification, record counts
• Business/technical ownership

The template captures per-table governance metadata that is REQUIRED for Bronze TBLPROPERTIES in Step 3. Missing any field here cascades into governance gaps downstream:

For Approach B/C (existing tables / external copy): The user specifies a source but rarely specifies per-table classification. The agent MUST still fill the template by inferring from the source schema (see the "Approach C Inference Playbook" section of references/requirements-template.md). Ask the user ONLY for fields that cannot be inferred — typically just business_owner.

Gate: Do not proceed to Step 2 until the requirements template is filled (inferred defaults are acceptable).

Step 2: Choose Data Source Approach

Three approaches detailed in references/data-source-approaches.md:

Approach A (Schema CSV + Faker) is the standard approach for this framework. It reads the customer's source schema CSV from context/ to create Bronze DDLs with the exact same table structure, then generates Faker data matching those column types and FK relationships. This ensures Bronze faithfully represents the customer's source system.

# ✅ CORRECT: Read schema CSV to build Bronze DDLs
import csv
from pathlib import Path

def extract_tables_from_schema_csv(csv_path: Path) -> dict:
    """Extract table definitions from customer schema CSV."""
    from collections import defaultdict
    tables = defaultdict(list)
    with open(csv_path) as f:
        reader = csv.DictReader(f)
        for row in reader:
            tables[row["table_name"]].append({
                "name": row["column_name"],
                "type": row.get("full_data_type", row.get("data_type", "STRING")),
                "nullable": row.get("is_nullable", "YES") == "YES",
                "comment": row.get("comment", ""),
            })
    return dict(tables)

# Extract tables from customer schema
schema_tables = extract_tables_from_schema_csv(Path("context/Wanderbricks_Schema.csv"))

# Generate DDL for each table — names and types come from CSV, never hardcoded
for table_name, columns in schema_tables.items():
    col_defs = ", ".join(f"{c['name']} {c['type']}" for c in columns)
    ddl = f"CREATE TABLE IF NOT EXISTS {{catalog}}.{{schema}}.{table_name} ({col_defs}) ..."

# ❌ WRONG: Hardcoding table definitions instead of extracting from schema CSV
tables = {
    "bookings": ["booking_id BIGINT", "user_id BIGINT", ...],  # ❌ Might be incomplete
    "users": ["user_id BIGINT", "email STRING", ...],           # ❌ Might have wrong types
}

Step 3: Create Table DDLs (30 min)

Use the setup script template: scripts/setup_tables.py

File structure to create:

src/{project}_bronze/
├── __init__.py                # Package initialization
├── setup_tables.py            # Table DDL definitions
├── generate_dimensions.py     # Generate dimension data with Faker
├── generate_facts.py          # Generate fact data with Faker
└── copy_from_source.py        # Optional: Copy from existing source

Critical DDL rules:

• CLUSTER BY AUTO on all tables (never specify columns manually)
• delta.enableChangeDataFeed = true (required for Silver)
• Standard audit columns: ingestion_timestamp, source_file
• Mark tables as data_purpose = testing_demo, is_production = false

Step 4: Generate or Load Data (30-45 min)

Option A (Faker): Use the faker-data-generation skill for patterns.

• Generate dimensions first (for FK integrity)
• Generate facts with references to dimension keys
• Use seeded Faker for reproducibility

Option B/C (Copy): Use the copy script template: scripts/copy_from_source.py

Step 5: Configure Asset Bundle Jobs (15 min)

Use the job templates:

• assets/templates/bronze-setup-job.yaml - Table creation job
• assets/templates/bronze-data-generator-job.yaml - Data generation job

🛑 STOP — Artifact Creation Complete

Steps 1–5 are complete. All files (DDLs, generators, job YAMLs) have been created. Do NOT proceed to Step 6 unless the user explicitly requests deployment.

Report what was created and ask the user if they want to deploy and run.

Step 6: Deploy & Validate (15 min) — USER-TRIGGERED ONLY

This step is executed ONLY when the user explicitly requests deployment. Do not auto-execute.

Run validation queries: references/validation-queries.md

Critical Rules

Required TBLPROPERTIES

Every Bronze table must include:

TBLPROPERTIES (
    'delta.enableChangeDataFeed' = 'true',
    'delta.autoOptimize.optimizeWrite' = 'true',
    'delta.autoOptimize.autoCompact' = 'true',
    'layer' = 'bronze',
    'source_system' = '{source}',
    'domain' = '{domain}',
    'entity_type' = '{dimension|fact}',
    'contains_pii' = '{true|false}',
    'data_classification' = '{confidential|internal|public}',
    'business_owner' = '{team}',
    'technical_owner' = 'Data Engineering',
    'data_purpose' = 'testing_demo',
    'is_production' = 'false'
)

Table Naming Convention

• Dimensions: bronze_{entity}_dim (e.g., bronze_store_dim, bronze_product_dim)
• Facts: bronze_{entity} (e.g., bronze_transactions, bronze_inventory)
• Date dimension: bronze_date_dim (SQL-generated, not Faker)

Data Generation Order

1. Dimensions first - Create master data tables
2. Date dimension - Generated via SQL SEQUENCE (not Faker)
3. Facts last - Load dimension keys for FK integrity

Mandatory Skill Dependencies

MANDATORY: Read each skill below using the Read tool BEFORE writing any code for the indicated step. Do NOT generate these patterns from memory.

NEVER do these without FIRST reading the corresponding skill:

• NEVER name a table or schema without reading naming-tagging-standards
• NEVER write TBLPROPERTIES without reading databricks-table-properties
• NEVER write Faker generators without reading faker-data-generation
• NEVER write Asset Bundle YAML without reading databricks-asset-bundles
• NEVER write CREATE SCHEMA without reading schema-management-patterns

Reference Files

• references/requirements-template.md - Fill-in template for project requirements, entity list, data classification, ownership
• references/data-source-approaches.md - Detailed patterns for all 3 data source approaches (Faker, Existing, Copy)
• references/validation-queries.md - Validation SQL queries and implementation checklist

Scripts

• scripts/setup_tables.py - Template table creation notebook with DDL patterns, audit columns, governance metadata
• scripts/copy_from_source.py - Template copy-from-source notebook for Approach B/C

Asset Templates

• assets/templates/bronze-setup-job.yaml - Asset Bundle job for table creation
• assets/templates/bronze-data-generator-job.yaml - Asset Bundle job for Faker data generation with dependency chain

Pipeline Progression

Previous stage: gold/00-gold-layer-design → Gold layer design must be complete so that the target dimensional model is understood before creating Bronze tables

Next stage: After completing the Bronze layer, proceed to:

• silver/00-silver-layer-setup — Set up Silver layer DLT pipelines with data quality rules

Post-Completion: Skill Usage Summary (MANDATORY)

After completing all steps of this orchestrator, output a Skill Usage Summary reflecting what you ACTUALLY did — not a pre-written summary.

What to Include

1. Every skill SKILL.md or references/ file you read (via the Read tool), in the order you read them
2. Which step you were in when you read it
3. Whether it was a Worker, Common, Cross-domain, or Reference file
4. A one-line description of what you specifically used it for in this session

Format

Summary Footer

End with:

• Totals: X worker skills, Y common skills, Z reference files read across N steps
• Skipped: List any skills from the dependency table above that you did NOT need to read, and why (e.g., "step not applicable", "user skipped", "no issues encountered")
• Unplanned: List any skills you read that were NOT listed in the dependency table (e.g., for troubleshooting, edge cases, or user-requested detours)

References

• Unity Catalog
• Delta Lake
• Asset Bundles
• Faker Library