Databricks Data Engineer Associate

COPY INTO

COPY INTO main.sales.orders_bronze
FROM 's3://landing/orders/'
FILEFORMAT = JSON
FORMAT_OPTIONS ('inferSchema' = 'true')
COPY_OPTIONS ('mergeSchema' = 'true');

• Idempotent: files already loaded are skipped on the next run, so it's safe to schedule.
• COPY_OPTIONS ('force' = 'true') reloads files that were already loaded.

Auto Loader

chk = "/Volumes/main/sales/raw/_chk/orders"

(spark.readStream
    .format("cloudFiles")
    .option("cloudFiles.format", "json")
    .option("cloudFiles.schemaLocation", chk)
    .load("s3://landing/orders/")
  .writeStream
    .option("checkpointLocation", chk)
    .trigger(availableNow=True)  # all new files, then stop
    .toTable("main.sales.orders_bronze"))

• File detection: directory listing (default, no extra setup) or file notification (recommended; use it with file events on the external location). Both give exactly-once processing.
• Schema inference: JSON, CSV and XML columns are inferred as strings unless you set cloudFiles.inferColumnTypes or add cloudFiles.schemaHints. The inferred schema is stored at cloudFiles.schemaLocation.
• _rescued_data collects values that don't fit the schema instead of dropping them.

Semi-structured data

-- Path into a JSON string column
SELECT payload:customer.id AS customer_id
FROM main.sales.orders_bronze;

-- Parse a JSON array, then one row per element
SELECT explode(
  from_json(items, 'ARRAY<STRUCT<sku STRING, qty INT>>')
) AS item
FROM main.sales.orders_bronze;

Bronze to silver cleaning

from pyspark.sql import functions as F

silver = (spark.read.table("main.sales.orders_bronze")
    .dropna(subset=["order_id"])
    .fillna({"country": "unknown"})
    .withColumn("amount", F.col("amount").cast("double"))
    .withColumn("order_date", F.to_date("order_ts"))
    .withColumnRenamed("cust", "customer_id")
    .dropDuplicates(["order_id"]))

(silver.write.mode("overwrite")
    .saveAsTable("main.sales.orders_silver"))

Other row and column moves: filter/where, drop, split, explode (one row per array element).

Joins and unions

Aggregations

groupBy().agg(F.count, F.sum, F.mean, F.countDistinct, F.approx_count_distinct). approx_count_distinct is faster on large data and accepts a small error. df.describe() gives count, mean, stddev, min and max; df.summary() adds the 25/50/75% percentiles.

Gold objects

Data quality

Expectations in Lakeflow pipelines:

CREATE OR REFRESH STREAMING TABLE orders_silver (
  CONSTRAINT valid_id EXPECT (order_id IS NOT NULL)
    ON VIOLATION DROP ROW,
  CONSTRAINT valid_amount EXPECT (amount >= 0)
    ON VIOLATION FAIL UPDATE,
  CONSTRAINT recent EXPECT (order_date >= '2020-01-01')
) AS SELECT * FROM STREAM(orders_bronze);

Outside pipelines, Delta CHECK constraints (ALTER TABLE … ADD CONSTRAINT … CHECK (…)) reject bad writes.

Control-flow features

Run if conditions

Triggers

Use a time-based trigger when data arrives on a fixed timetable or consumers expect a set refresh time. Use a data-driven trigger (file arrival, table update) when arrival is irregular, to avoid empty runs and cut latency.

Git Folders

Git Folders clone a remote repo into the workspace: create and switch branches, pull, commit and push from the UI. Pull requests are opened, reviewed and merged in your Git provider.

Bundle configuration

Declarative Automation Bundles describe jobs, pipelines and other resources as YAML (databricks.yml) alongside the code, so one codebase deploys to dev, test and prod.

bundle:
  name: orders

variables:
  catalog:
    default: dev_catalog

targets:
  dev:
    mode: development   # "[dev you]" prefix, paused schedules
    default: true
  prod:
    mode: production
    variables:
      catalog: prod_catalog   # per-target override

Reference a variable as ${var.catalog}. Set it per target (as above), with --var="catalog=…", or with a BUNDLE_VAR_catalog environment variable.

Monitor jobs

The job's runs view compares durations and outcomes over time, so a run that is suddenly twice as slow stands out. A run's task graph shows which upstream task blocked or failed, and repair re-runs just the failed part.

Data layout

• Liquid clustering (CLUSTER BY (col, …) or CLUSTER BY AUTO) replaces partitioning and ZORDER. You can change keys without rewriting data, and OPTIMIZE clusters new data incrementally. It isn't compatible with partitioning or ZORDER on the same table.
• Predictive optimization runs OPTIMIZE, VACUUM and ANALYZE automatically on Unity Catalog managed tables.

Cluster problems

Managed vs external tables

Convert external to managed with ALTER TABLE t SET MANAGED. It keeps the table's history and permissions, and UNSET MANAGED rolls it back within 14 days. Managed to external has no single command: copy the data into a new external table.

Privileges

GRANT USE CATALOG ON CATALOG main TO `analysts`;
GRANT USE SCHEMA, SELECT ON SCHEMA main.sales TO `analysts`;
GRANT MODIFY ON TABLE main.sales.orders TO `etl-sp`;
REVOKE SELECT ON SCHEMA main.sales FROM `analysts`;

• To read a table you need USE CATALOG on its catalog, USE SCHEMA on its schema, and SELECT on the table (or on the schema or catalog, which is inherited downwards).
• Principals are users, groups and service principals. Grant to groups, and run production jobs as a service principal.

Row filters and column masks

-- Row filter: admins see everything, others only US rows
CREATE FUNCTION main.sec.us_only(region STRING)
  RETURN is_account_group_member('admins') OR region = 'US';
ALTER TABLE main.sales.orders
  SET ROW FILTER main.sec.us_only ON (region);

-- Column mask: only support sees real emails
CREATE FUNCTION main.sec.mask_email(email STRING)
  RETURN CASE WHEN is_account_group_member('support')
    THEN email ELSE '***' END;
ALTER TABLE main.sales.customers
  ALTER COLUMN email SET MASK main.sec.mask_email;

A table's own row filters and column masks are set by its owner. ABAC policies apply row filters and column masks centrally: you tag data with governed tags (e.g. pii = email), and a policy on a catalog, schema or table applies its UDF to every matching table or column, now and in future. Use ABAC when the same rule must cover many tables.