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

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.

environments:

environment_key

job_clusters:

existing_cluster_id:

environment_version: "4"

environments.spec

CLUSTER BY AUTO

CREATE TABLE

CLUSTER BY (col1, col2)

PARTITIONED BY

'delta.enableChangeDataFeed' = 'true'

'delta.autoOptimize.optimizeWrite' = 'true'

notebook_task:

base_parameters:

python_task:

parameters:

-- ✅ 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):

# 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)

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

Step 2: Choose Data Source Approach

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

context/*.csv

Approach A (Schema CSV + Faker) is the standard approach for this framework. It reads the customer's source schema CSV from

context/

# ✅ 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

• 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.

data_product_accelerator/skills/common/databricks-expert-agent/SKILL.md

data_product_accelerator/skills/common/databricks-table-properties/SKILL.md

CLUSTER BY AUTO

data_product_accelerator/skills/common/schema-management-patterns/SKILL.md

CREATE SCHEMA IF NOT EXISTS

data_product_accelerator/skills/bronze/01-faker-data-generation/SKILL.md

data_product_accelerator/skills/common/databricks-asset-bundles/SKILL.md

notebook_task

base_parameters

data_product_accelerator/skills/common/databricks-python-imports/SKILL.md

data_product_accelerator/skills/common/databricks-autonomous-operations/SKILL.md

NEVER do these without FIRST reading the corresponding skill:

• 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

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

path/to/SKILL.md

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