--- name: databricks-core-workflow-a-6a494bbf0f39 description: | Execute Databricks primary workflow: Delta Lake ETL pipelines. Use when building data ingestion pipelines, implementing medallion architecture, or creating Delta Lake transformations. Trigger with phrases like "databricks ETL", "delta lake pipeline", "medallion architecture", "databricks data pipeline", "bronze silver gold". allowed-tools: Read, Write, Edit, Bash(databricks:*), Grep version: 1.0.0 license: MIT author: Jeremy Longshore --- # Databricks Core Workflow A: Delta Lake ETL ## Overview Build production Delta Lake ETL pipelines using medallion architecture. ## Prerequisites - Completed `databricks-install-auth` setup - Understanding of Delta Lake concepts - Unity Catalog configured (recommended) ## Medallion Architecture ``` Raw Sources → Bronze (Raw) → Silver (Cleaned) → Gold (Aggregated) ↓ ↓ ↓ Landing Zone Business Logic Analytics Ready ``` ## Instructions ### Step 1: Bronze Layer - Raw Ingestion ```python # src/pipelines/bronze.py from pyspark.sql import SparkSession, DataFrame from pyspark.sql.functions import current_timestamp, input_file_name, lit from delta.tables import DeltaTable def ingest_to_bronze( spark: SparkSession, source_path: str, target_table: str, source_format: str = "json", schema: str = None, ) -> DataFrame: """ Ingest raw data to Bronze layer with metadata. Args: spark: SparkSession source_path: Path to source data target_table: Unity Catalog table name (catalog.schema.table) source_format: Source file format (json, csv, parquet) schema: Optional schema string """ # Read raw data reader = spark.read.format(source_format) if schema: reader = reader.schema(schema) df = reader.load(source_path) # Add ingestion metadata df_with_metadata = ( df .withColumn("_ingested_at", current_timestamp()) .withColumn("_source_file", input_file_name()) .withColumn("_source_format", lit(source_format)) ) # Write to Delta with merge for idempotency df_with_metadata.write \ .format("delta") \ .mode("append") \ .option("mergeSchema", "true") \ .saveAsTable(target_table) return df_with_metadata # Auto Loader for streaming ingestion def stream_to_bronze( spark: SparkSession, source_path: str, target_table: str, checkpoint_path: str, schema_location: str, ) -> None: """Stream data to Bronze using Auto Loader.""" ( spark.readStream .format("cloudFiles") .option("cloudFiles.format", "json") .option("cloudFiles.schemaLocation", schema_location) .option("cloudFiles.inferColumnTypes", "true") .load(source_path) .withColumn("_ingested_at", current_timestamp()) .writeStream .format("delta") .option("checkpointLocation", checkpoint_path) .option("mergeSchema", "true") .trigger(availableNow=True) .toTable(target_table) ) ``` ### Step 2: Silver Layer - Data Cleansing ```python # src/pipelines/silver.py from pyspark.sql import SparkSession, DataFrame from pyspark.sql.functions import ( col, when, trim, lower, to_timestamp, regexp_replace, sha2, concat_ws ) from delta.tables import DeltaTable def transform_to_silver( spark: SparkSession, bronze_table: str, silver_table: str, primary_keys: list[str], watermark_column: str = "_ingested_at", ) -> DataFrame: """ Transform Bronze to Silver with cleansing and deduplication. Args: spark: SparkSession bronze_table: Source Bronze table silver_table: Target Silver table primary_keys: Columns for deduplication/merge watermark_column: Column for incremental processing """ # Read Bronze (incremental using CDF) bronze_df = spark.readStream \ .format("delta") \ .option("readChangeFeed", "true") \ .table(bronze_table) # Apply transformations silver_df = ( bronze_df # Standardize strings .withColumn("name", trim(col("name"))) .withColumn("email", lower(trim(col("email")))) # Parse timestamps .withColumn("created_at", to_timestamp(col("created_at"))) # Remove PII for analytics .withColumn("email_hash", sha2(col("email"), 256)) # Data quality filters .filter(col("email").isNotNull()) .filter(col("created_at").isNotNull()) # Generate surrogate key .withColumn( "_row_key", sha2(concat_ws("||", *[col(k) for k in primary_keys]), 256) ) ) # Merge into Silver (upsert pattern) if DeltaTable.isDeltaTable(spark, silver_table): delta_table = DeltaTable.forName(spark, silver_table) merge_condition = " AND ".join( [f"target.{k} = source.{k}" for k in primary_keys] ) ( delta_table.alias("target") .merge(silver_df.alias("source"), merge_condition) .whenMatchedUpdateAll() .whenNotMatchedInsertAll() .execute() ) else: silver_df.write \ .format("delta") \ .mode("overwrite") \ .saveAsTable(silver_table) return silver_df ``` ### Step 3: Gold Layer - Business Aggregations ```python # src/pipelines/gold.py from pyspark.sql import SparkSession, DataFrame from pyspark.sql.functions import ( col, count, sum, avg, max, min, date_trunc, window, current_timestamp ) def aggregate_to_gold( spark: SparkSession, silver_table: str, gold_table: str, group_by_columns: list[str], aggregations: dict[str, str], time_grain: str = "day", ) -> DataFrame: """ Aggregate Silver to Gold for analytics. Args: spark: SparkSession silver_table: Source Silver table gold_table: Target Gold table group_by_columns: Columns to group by aggregations: Dict of {output_col: "agg_func(source_col)"} time_grain: Time aggregation grain (hour, day, week, month) """ silver_df = spark.table(silver_table) # Build aggregation expressions agg_exprs = [] for output_col, expr in aggregations.items(): agg_exprs.append(f"{expr} as {output_col}") # Apply aggregations gold_df = ( silver_df .withColumn("time_period", date_trunc(time_grain, col("created_at"))) .groupBy(*group_by_columns, "time_period") .agg(*[eval(e) for e in agg_exprs]) .withColumn("_aggregated_at", current_timestamp()) ) # Write to Gold (overwrite partition) gold_df.write \ .format("delta") \ .mode("overwrite") \ .option("replaceWhere", f"time_period >= '{get_min_date()}'") \ .saveAsTable(gold_table) return gold_df # Example usage gold_df = aggregate_to_gold( spark=spark, silver_table="catalog.silver.events", gold_table="catalog.gold.daily_metrics", group_by_columns=["region", "product_category"], aggregations={ "total_orders": "count(*)", "total_revenue": "sum(amount)", "avg_order_value": "avg(amount)", "unique_customers": "count(distinct customer_id)", }, time_grain="day" ) ``` ### Step 4: Delta Live Tables (DLT) Pipeline ```python # pipelines/dlt_pipeline.py import dlt from pyspark.sql.functions import * @dlt.table( name="bronze_events", comment="Raw events from source", table_properties={"quality": "bronze"} ) def bronze_events(): return ( spark.readStream .format("cloudFiles") .option("cloudFiles.format", "json") .load("/mnt/landing/events/") .withColumn("_ingested_at", current_timestamp()) ) @dlt.table( name="silver_events", comment="Cleansed and validated events", table_properties={"quality": "silver"} ) @dlt.expect_or_drop("valid_email", "email IS NOT NULL") @dlt.expect_or_drop("valid_amount", "amount > 0") def silver_events(): return ( dlt.read_stream("bronze_events") .withColumn("email", lower(trim(col("email")))) .withColumn("event_time", to_timestamp(col("event_time"))) ) @dlt.table( name="gold_daily_summary", comment="Daily aggregated metrics", table_properties={"quality": "gold"} ) def gold_daily_summary(): return ( dlt.read("silver_events") .groupBy(date_trunc("day", col("event_time")).alias("date")) .agg( count("*").alias("total_events"), sum("amount").alias("total_revenue"), countDistinct("customer_id").alias("unique_customers") ) ) ``` ## Output - Bronze layer with raw data and metadata - Silver layer with cleansed, deduplicated data - Gold layer with business aggregations - Delta Lake tables with ACID transactions ## Error Handling | Error | Cause | Solution | |-------|-------|----------| | Schema mismatch | Source schema changed | Use `mergeSchema` option | | Duplicate records | Missing deduplication | Add merge logic with primary keys | | Null values | Data quality issues | Add expectations/filters | | Memory errors | Large aggregations | Increase cluster size or partition data | ## Examples ### Complete Pipeline Orchestration ```python # Run full medallion pipeline from src.pipelines import bronze, silver, gold # Bronze ingestion bronze.ingest_to_bronze( spark, "/mnt/landing/orders/", "catalog.bronze.orders" ) # Silver transformation silver.transform_to_silver( spark, "catalog.bronze.orders", "catalog.silver.orders", primary_keys=["order_id"] ) # Gold aggregation gold.aggregate_to_gold( spark, "catalog.silver.orders", "catalog.gold.order_metrics", group_by_columns=["region"], time_grain="day" ) ``` ## Resources - [Delta Lake Guide](https://docs.databricks.com/delta/index.html) - [Medallion Architecture](https://www.databricks.com/glossary/medallion-architecture) - [Delta Live Tables](https://docs.databricks.com/delta-live-tables/index.html) - [Auto Loader](https://docs.databricks.com/ingestion/auto-loader/index.html) ## Next Steps For ML workflows, see `databricks-core-workflow-b`. --- Source attribution: Dicklesworthstone Original project: https://github.com/Dicklesworthstone/pi_agent_rust/blob/f28fc46ee0db8e710d95b8b55592cd38d60f2b26/tests/ext_conformance/artifacts/plugins-community/plugins/saas-packs/databricks-pack/skills/databricks-core-workflow-a/SKILL.md License: MIT License information: https://opensource.org/license/mit Original upstream instructions; downloaded from Mega AI Hub. ## Upstream license notice MIT License (with OpenAI/Anthropic Rider) Copyright (c) 2026 Jeffrey Emanuel Permission is hereby granted, free of charge, to any person obtaining a copy of this software and associated documentation files (the "Software"), to deal in the Software without restriction, including without limitation the rights to use, copy, modify, merge, publish, distribute, sublicense, and/or sell copies of the Software, and to permit persons to whom the Software is furnished to do so, subject to the following conditions: ADDITIONAL RIDER / RESTRICTION (OpenAI / Anthropic): This rider is part of the "conditions" of this License. In the event of any conflict between this rider and any other portion of this License, this rider controls. "Restricted Parties" means OpenAI, L.L.C.; Anthropic, PBC; any of their respective Affiliates; and any person or entity acting directly or indirectly on behalf of, for the benefit of, or under the direction of any of the foregoing (including any officer, director, employee, contractor, agent, consultant, service provider, or representative). Notwithstanding any other provision of this License, no rights are granted to any Restricted Party. Any purported license, sublicense, assignment, transfer, or other permission to any Restricted Party is null and void absent the express prior written permission of Jeffrey Emanuel. You may not provide, disclose, distribute, sublicense, sell, lease, lend, host, make available, or otherwise permit access to the Software or any derivative work of the Software (as defined in applicable copyright law) (collectively, "Derivative Works") to or for any Restricted Party. For purposes of this rider, "use" includes, without limitation: copying, modifying, merging, publishing, distributing, sublicensing, selling, transferring, making available, hosting, deploying, executing, benchmarking, testing, analyzing, indexing, or incorporating the Software or any Derivative Works into any dataset, training corpus, evaluation harness, or pipeline for machine learning or other automated systems. This rider applies to the Software and all Derivative Works. As a condition of use, you agree that this rider is a precondition to exercising any rights under this License, and you agree that any distribution of the Software or any Derivative Works must include this rider provision unmodified. Any breach of this rider automatically and immediately terminates the permissions granted by this License. Upon termination, you must immediately cease all use and distribution of the Software and any Derivative Works and destroy all copies under your control. You agree that a breach of this rider would cause irreparable harm and that Jeffrey Emanuel may seek injunctive or other equitable relief to enforce this rider, in addition to any other remedies available at law. To the maximum extent permitted by applicable law, the prevailing party in any action to enforce this rider shall be entitled to recover reasonable attorneys' fees and costs. For purposes of this rider, "Affiliate" means any entity that directly or indirectly controls, is controlled by, or is under common control with the specified party. "Control" means ownership of more than 50% of the voting securities or other ownership interest, or the power to direct management or policies by contract or otherwise. The above copyright notice and this permission notice shall be included in all copies or substantial portions of the Software. THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM, OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN THE SOFTWARE. # Mega AI Hub resource metadata Creator / publisher: Dicklesworthstone Original project: https://github.com/Dicklesworthstone/pi_agent_rust/blob/f28fc46ee0db8e710d95b8b55592cd38d60f2b26/tests/ext_conformance/artifacts/plugins-community/plugins/saas-packs/databricks-pack/skills/databricks-core-workflow-a/SKILL.md Attribution basis: Source repository record Fields of work: Data & Analytics, Development & Engineering Roles: Data Analyst, Data Engineer, ML / AI Engineer Specialties: API, Backend, Database Tools: Databricks Country applicability: Country not specified Classification basis: Source catalog labels Instruction SHA-256: 77027430597dbdfeb0eac1f8ae56993cb1833ba5ab020508bf688ebbbbc579ff