Databricks Real-World Scenarios

This document outlines practical, real-world engineering scenarios encountered in production Databricks environments. Each scenario details the business problem, design considerations, solution architecture, and step-by-step implementation.

Scenario 1: Migrating from Legacy Spark (Hive Metastore) to Unity Catalog

Problem Statement

A retail company has hundreds of tables defined in a legacy Hive Metastore (hive_metastore catalog in Databricks). Data is stored in various S3 buckets under ad-hoc IAM user keys. Data governance is managed at the workspace level, making it impossible to share data securely across workspaces or trace column-level data lineage. The team needs to centralize governance under Unity Catalog without interrupting downstream BI dashboards.

Solution Architecture

We will migrate tables to a new catalog structure production_retail with schemas representing business domains. Legacy external tables will be upgraded to Unity Catalog using the SYNC command or CTAS (Create Table As Select) depending on metadata status.

[Legacy Workspace] ---> Hive Metastore (No Lineage, Workspace-Locked ACLs)
                               |
                               v
                       [Migration Stage]
                               |
                               v
[New Workspace]    ---> Unity Catalog (Central Governance, Cross-Workspace Sharing, Lineage)

Step-by-Step Implementation

1. Create S3 Storage Credentials & External Location Create an IAM role allowing access to your production bucket s3://prod-retail-data-bucket/. In the Databricks account console / SQL editor, create the credentials and map them to an external location: ``sql -- Create storage credential mapping to IAM role / Managed Identity CREATE STORAGE CREDENTIALretail_s3_credential` IDENTIFIED BY 'arn:aws:iam::123456789012:role/databricks-retail-access';

-- Define external location referencing the S3 path CREATE EXTERNAL LOCATION retail_s3_external_location URL 's3://prod-retail-data-bucket/' WITH (STORAGE CREDENTIAL retail_s3_credential); ```

1. Initialize the Unity Catalog Schema sql CREATE CATALOG IF NOT EXISTS `production_retail`; USE CATALOG `production_retail`; CREATE SCHEMA IF NOT EXISTS `sales`;

2. Dry-Run and Synchronize Legacy Metadata For Delta tables, use the SYNC command to upgrade them in-place to Unity Catalog without copying the underlying files: ``sql -- Run dry-run to identify potential issues SYNC TABLEproduction_retail.sales.transactionsFROMhive_metastore.sales.transactions` DRY RUN;

-- Execute the in-place upgrade SYNC TABLE production_retail.sales.transactions FROM hive_metastore.sales.transactions; ```

1. Verify Lineage and Grant Permissions Grant select privileges to the analyst group: sql GRANT USAGE ON CATALOG `production_retail` TO `analysts`; GRANT USAGE ON SCHEMA `production_retail`.`sales` TO `analysts`; GRANT SELECT ON TABLE `production_retail`.`sales`.`transactions` TO `analysts`;

Scenario 2: Optimizing a Slow-Running ETL Job (Data Skew & Small File Problem)

Problem Statement

A daily ETL job ingesting IoT device telemetry data is running extremely slowly (taking 4 hours). An investigation reveals two main bottlenecks:

1. The Small File Problem: Ephemeral micro-batches write thousands of tiny 500KB files, causing massive metadata overhead during reads.
2. Data Skew: When joining telemetry tables with device metadata on device_id, a single large device (e.g., device_id = 9999) receives 80% of the events, overloading a single partition executor during shuffles.

Solution Architecture

We will configure Delta Lake Auto-Optimize, apply Liquid Clustering on device_id and timestamp, and utilize Spark's Adaptive Query Execution (AQE) with skew join hints to balance executor workloads.

Step-by-Step Implementation

1. Define the Target Table with Liquid Clustering Instead of static hive partitioning which causes small files and limits filter flexibility, use Liquid Clustering: sql CREATE TABLE `production_telemetry`.`iot`.`sensor_readings` ( device_id LONG, timestamp TIMESTAMP, metric_name STRING, metric_value DOUBLE ) CLUSTER BY (device_id, timestamp);

2. Enable Auto-Optimize Properties Ensure new writes auto-compact during ingestion: sql ALTER TABLE `production_telemetry`.`iot`.`sensor_readings` SET TBLPROPERTIES ( 'delta.autoOptimize.optimizeWrite' = 'true', 'delta.autoOptimize.autoCompact' = 'true' );

3. Configure Spark Session to Handle Skew Joins Set configurations for Adaptive Query Execution (AQE) in the notebook or workspace cluster setup: ```python # Enable Adaptive Query Execution spark.conf.set("spark.sql.adaptive.enabled", "true")

# Enable Skew Join handling spark.conf.set("spark.sql.adaptive.skewJoin.enabled", "true")

# Adjust skew threshold (partition size is > 5x median and > 64MB) spark.conf.set("spark.sql.adaptive.skewJoin.skewedPartitionFactor", "5") spark.conf.set("spark.sql.adaptive.skewJoin.skewedPartitionThresholdInBytes", "67108864") ```

1. Execute Optimization and Maintenance Schedule Add a maintenance job running weekly to clean up stale transaction log history and compact files: ``sql -- Optimize layout manually if required OPTIMIZEproduction_telemetry.iot.sensor_readings`;

-- Clean up older unneeded files (default safety threshold of 7 days) VACUUM production_telemetry.iot.sensor_readings; ```

Scenario 3: CI/CD Pipeline Automation with Databricks Asset Bundles (DABs)

Problem Statement

A data engineering team wants to automate the deployment of their multi-task orchestration workflow (consisting of Spark notebooks and SQL queries) to three environments: Dev, Staging, and Prod. They want to ensure that all workspaces have identical configurations, credentials are parameterized, and all code changes are validated before merging into the main branch.

Solution Architecture

We will use Databricks Asset Bundles (DABs) to define the project structure, and GitHub Actions to automate validation on pull requests and deployments on release merges.

Local IDE / git ---> Pull Request ---> GitHub Actions: validate bundle
                                                 | (Merge)
                                                 v
                                       GitHub Actions: deploy bundle to Prod

Step-by-Step Implementation

1. Configure the databricks.yml Bundle Template Create a project directory locally with a databricks.yml file: ```yaml bundle: name: retail-telemetry-etl

targets: dev: workspace: host: https://adb-dev.cloud.databricks.com mode: development

 prod:
   workspace:
     host: https://adb-prod.cloud.databricks.com
   mode: production
   resources:
     jobs:
       telemetry_etl_job:
         name: "[Prod] Telemetry ETL Workflow"
         tasks:

           - task_key: ingest_raw
             notebook_task:
               notebook_path: ./src/ingest_telemetry.py
             new_cluster:
               spark_version: 14.3.x-scala2.12
               node_type_id: i3.xlarge
               num_workers: 4

           - task_key: aggregations
             depends_on:

               - task_key: ingest_raw
             notebook_task:
               notebook_path: ./src/aggregate_data.py
             existing_cluster_id: 0618-120000-sample1

```

1. Configure GitHub Actions Workflow (.github/workflows/deploy.yml) ```yaml name: Deploy Databricks Asset Bundle

on: push: branches:

     - main
 pull_request:

jobs: validate-and-deploy: runs-on: ubuntu-latest steps:

     - name: Checkout Code
       uses: actions/checkout@v3

     - name: Install Databricks CLI
       run: |
         curl -fsSL https://raw.githubusercontent.com/databricks/setup-cli/main/install.sh | sh

     - name: Validate Bundle (PR Checks)
       env:
         DATABRICKS_HOST: ${{ secrets.DATABRICKS_DEV_HOST }}
         DATABRICKS_TOKEN: ${{ secrets.DATABRICKS_DEV_TOKEN }}
       run: |
         databricks bundle validate -t dev

     - name: Deploy to Production (On Push to Main)
       if: github.ref == 'refs/heads/main' && github.event_name == 'push'
       env:
         DATABRICKS_HOST: ${{ secrets.DATABRICKS_PROD_HOST }}
         DATABRICKS_TOKEN: ${{ secrets.DATABRICKS_PROD_TOKEN }}
       run: |
         databricks bundle deploy -t prod

```

Scenario 4: GDPR/PII Compliance with Column Masking and Row-Level Filtering

Problem Statement

A hospital network stores clinical patient records in Unity Catalog. The database contains highly sensitive personal data. According to compliance rules:

1. Security Officer / Admin: Can see full patient Social Security Numbers (SSN).
2. Medical Staff / Doctors: Can see diagnostic data, but the SSN must be masked (e.g. XXX-XX-1234). They must only see patient rows belonging to their designated hospital branch (region-locking).
3. Data Analysts: Can query aggregate metrics but cannot see any names or SSNs at all.

Solution Architecture

We will implement dynamic access control policies directly in Unity Catalog using User-Defined Functions (UDFs).

                 [Patient Clinical Records Table]
                               |
            ---------------------------------------
           |                                       |
           v                                       v
   [Column Masking UDF]                  [Row-Level Filter UDF]
(Masks SSN based on role/group)         (Filters rows based on user branch)

Step-by-Step Implementation

1. Create the Column Mask UDF Write a SQL function checking user group status to mask SSNs: ``sql CREATE CATALOG IF NOT EXISTSgovernance_policies; CREATE SCHEMA IF NOT EXISTSpii_masks; USE CATALOGgovernance_policies; USE SCHEMApii_masks`;

-- Masking function for SSN CREATE OR REPLACE FUNCTION ssn_mask(ssn STRING) RETURN CASE WHEN IS_MEMBER('security_officers') THEN ssn WHEN IS_MEMBER('medical_staff') THEN CONCAT('XXX-XX-', RIGHT(ssn, 4)) ELSE 'REDACTED' END; ```

1. Apply Column Masking to the Target Table Apply the masking policy directly to the target schema: sql ALTER TABLE `production_clinical`.`patients`.`records` ALTER COLUMN `patient_ssn` SET MASK `governance_policies`.`pii_masks`.`ssn_mask`;

2. Create the Row-Level Filter UDF Filter rows so that medical staff only view patients belonging to their clinic branch: ``sql CREATE SCHEMA IF NOT EXISTSrow_filters; USE SCHEMArow_filters`;

-- Row filtering function mapping user account names to branches CREATE OR REPLACE FUNCTION branch_filter(patient_branch STRING) RETURN CASE WHEN IS_MEMBER('security_officers') THEN TRUE -- Admins see everything WHEN IS_MEMBER('medical_staff') AND patient_branch = CURRENT_USER() THEN TRUE -- Matches branch location id ELSE FALSE -- Analysts or others see no rows unless granted aggregate views END; ```

1. Apply Row Filtering to the Target Table sql ALTER TABLE `production_clinical`.`patients`.`records` SET ROW FILTER `governance_policies`.`row_filters`.`branch_filter` ON (`clinic_location_branch`);