Skip to content

Library · Data Practice OS

Data engineering on Databricks

Reference guide · Published · Source reviewed

Databricks gives you a lot of power, and therefore a lot of ways to make a mess. The practice default is a small set of patterns on serverless compute, deployed as code.

Ingestion: choose by source

Source Default Alternative
Operational databases (SQL Server, Oracle, Postgres) Lakeflow Connect Change data capture connectors A partner CDC tool landing in S3
SaaS (Salesforce, ServiceNow, SharePoint, Workday) Lakeflow Connect managed connectors A partner ELT service
Files in S3 Auto Loader (read_files / cloudFiles) into streaming tables COPY INTO for small, rare loads
Streams (Kinesis, Kafka, MSK) Structured Streaming in a Lakeflow declarative pipeline Snowflake Snowpipe Streaming if the consumer is Snowflake-only
Mainframe or legacy extracts Files to S3, then Auto Loader Lakebridge for code, not data (Code conversion)

Verify connector coverage and serverless availability in Sydney in the documentation.

Pipelines: declarative first

Lakeflow Declarative Pipelines (formerly Delta Live Tables) are the default for bronze to silver. You declare tables and quality expectations, and the platform handles dependencies, incremental processing, retries and lineage.

CREATE OR REFRESH STREAMING TABLE network.bronze.loop_counts
AS SELECT *, _metadata.file_path AS source_file
FROM STREAM read_files('s3://org-landing/streams/loop_counts/', format => 'json');

CREATE OR REFRESH STREAMING TABLE network.silver.loop_counts (
  CONSTRAINT valid_detector EXPECT (detector_id IS NOT NULL) ON VIOLATION DROP ROW,
  CONSTRAINT sane_count     EXPECT (vehicle_count BETWEEN 0 AND 5000)
)
AS SELECT detector_id,
          CAST(interval_start AS TIMESTAMP) AS interval_start,
          vehicle_count,
          occupancy_pct
FROM STREAM network.bronze.loop_counts;

Expectations make quality visible: dropped and failed rows are counted in the pipeline event log, which feeds Data quality and observability.

For silver to gold, use dbt (on a SQL warehouse) when the logic is modelling SQL that should stay portable across platforms (ADR-003: dbt as the transformation standard). Keep declarative pipelines for streaming and heavy Python. Do not split one domain's gold layer between the two.

Use Lakeflow Jobs to orchestrate: pipelines, dbt tasks, notebooks and model retraining in one DAG, with retries, alerts and table-update triggers.

Layout: catalogs by environment, schemas by domain and layer

catalog  tmr_dev | tmr_test | tmr_prod
schema   <domain>_bronze | <domain>_silver | <domain>_gold     e.g. network_silver
         sandbox_<team>  (dev only)

Bind workspaces to catalogs, so the production catalog is writable only from production jobs running as a service principal. See Governance on Databricks - Unity Catalog and CI/CD and environments.

Deploy as code

  • Databricks Asset Bundles define jobs, pipelines, dashboards, model serving endpoints and apps as YAML, deployed per environment from GitHub Actions.
  • Terraform owns the workspace, the metastore assignment, catalogs, external locations, cluster policies and grants.
  • Notebooks are fine for exploration. Production code lives in the repo as .py or .sql files in a bundle.
# databricks.yml (excerpt)
bundle: { name: network-pipelines }
targets:
  dev:  { mode: development, workspace: { host: https://<dev-workspace> } }
  prod: { mode: production,  workspace: { host: https://<prod-workspace> },
          run_as: { service_principal_name: sp-network-prod } }
resources:
  pipelines:
    loop_counts:
      name: loop_counts
      catalog: tmr_${bundle.target}
      schema: network_silver
      serverless: true
      libraries: [{ file: { path: ./pipelines/loop_counts.sql } }]

Iceberg interop

Enable UniForm (Iceberg reads) on gold Delta tables that Snowflake or AWS engines consume. Databricks remains the writer and the catalog of record, and Snowflake reads through the Unity Catalog Iceberg REST endpoint. See Apache Iceberg as the interchange format and ADR-004: One catalog of record per table.

Pitfalls

  • All-purpose clusters running production jobs. They cost more and are not isolated. Use job or serverless compute.
  • Hand-tuned Spark configs copied from blogs. Start with serverless and defaults, and tune only with evidence.
  • Ignoring small files and table maintenance. Turn on predictive optimisation for managed tables.
  • Business logic split across notebooks, pipelines and dbt for one domain. Pick one per layer.

Worked example

The vehicle registrations system of record (SQL Server) replicates to registrations_bronze through a Lakeflow Connect CDC connector. A declarative pipeline applies SCD2 history with AUTO CDC into silver (AUTO CDC APIs; they replace APPLY CHANGES with the same syntax). dbt builds the gold registration_snapshot_daily product, the bundle deploys it from GitHub Actions, and a Lakeflow Job runs it nightly. A contract test (Data contracts) checks the gold schema before each publish.