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. | 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` DDL | ❌ NEVER use `CLUSTER BY (col1, col2)` or `PARTITIONED BY` | | **Change Data Feed** | `'delta.enableChangeDataFeed' = 'true'` | Every table's TBLPROPERTIES | ❌ NEVER omit (required for Silver streaming) | | **Auto-Optimize** | `'delta.autoOptimize.optimizeWrite' = 'true'` | Every table's TBLPROPERTIES | ❌ NEVER omit | | **notebook_task** | `notebook_task:` with `base_parameters:` | Every task in job YAML | ❌ NEVER use `python_task:` or CLI-style `parameters:` | ```sql -- ✅ 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' ) ``` ```yaml # ✅ 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`. ```bash # 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](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: | Field | Drives TBLPROPERTY | Consequence of Skipping | |-------|--------------------|-------------------------| | Entity type (dim/fact) | `entity_type` | Missing governance metadata; Silver/Gold mis-classification | | Has PII (yes/no) | `contains_pii` + `class.*` governed tags | PII columns untagged; compliance risk | | Data classification | `data_classification` | Missing compliance metadata | | Business owner | `business_owner` | Unowned tables | | Primary key | PK constraint | Missing constraint metadata | **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](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](references/data-source-approaches.md): | Approach | Best For | Time | |---|---|---| | **A: Schema CSV + Faker** (recommended) | Create Bronze tables matching customer's source schema from `context/*.csv`, then populate with Faker-generated data | 30-45 min | | **B: Existing tables** | Data already exists in Databricks — read it directly | 15-20 min | | **C: External copy** | Copy data from sample datasets, CSVs, databases, or other workspaces | 20-30 min | **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. ```python # ✅ 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}) ..." ``` ```python # ❌ 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](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](scripts/copy_from_source.py) ### Step 5: Configure Asset Bundle Jobs (15 min) Use the job templates: - [assets/templates/bronze-setup-job.yaml](assets/templates/bronze-setup-job.yaml) - Table creation job - [assets/templates/bronze-data-generator-job.yaml](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](references/validation-queries.md) ## Critical Rules ### Required TBLPROPERTIES Every Bronze table must include: ```sql 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.** | Step | Read Skill (MANDATORY) | What It Provides | |------|------------------------|------------------| | All steps | `skills/databricks-expert-agent/SKILL.md` | Core extraction principle: extract names from source, never hardcode | | Step 1, Step 3 | `data_product_accelerator/skills/common/naming-tagging-standards/SKILL.md` | Table naming prefixes (`bronze_`), schema COMMENTs, PII governed tags, workflow tags | | Step 3 (DDLs) | `data_product_accelerator/skills/common/databricks-table-properties/SKILL.md` | Bronze TBLPROPERTIES, `CLUSTER BY AUTO`, governance metadata |