Databricks Certified Data Engineer Associate Quick Reference

This Quick Reference is an independent study aid for candidates preparing for the Databricks Certified Data Engineer Associate exam, code Databricks DEA. It focuses on practical distinctions, commands, and design choices that commonly appear in data engineering scenarios on Databricks.

High-Yield Exam Map

Lakehouse Object Model

Naming Pattern

Prefer fully qualified object names in exam scenarios involving governance or multiple environments:

SELECT *
FROM catalog_name.schema_name.table_name;

Managed vs External Tables

Example DDL:

-- Managed Delta table
CREATE TABLE main.sales.orders (
  order_id STRING,
  order_ts TIMESTAMP,
  amount DECIMAL(10,2)
);

-- External Delta table
CREATE TABLE main.sales.orders_ext
LOCATION 's3://example-bucket/path/orders';

Delta Lake Essentials

Delta Time Travel

SELECT *
FROM main.sales.orders VERSION AS OF 12;

SELECT *
FROM main.sales.orders TIMESTAMP AS OF '2026-06-01T00:00:00Z';

High-yield distinction:

Medallion Architecture

Common exam pattern:

1. Land source files into bronze.
2. Apply schema, validation, deduplication into silver.
3. Build aggregated or dimensional outputs in gold.
4. Orchestrate dependencies with a Databricks job or declarative pipeline.
5. Govern access by catalog/schema/table privileges.

Ingestion Selection Matrix

COPY INTO

Use when the source is file-based and the target is a Delta table.

COPY INTO main.bronze.orders_raw
FROM 's3://example-bucket/incoming/orders/'
FILEFORMAT = CSV
FORMAT_OPTIONS ('header' = 'true', 'inferSchema' = 'true');

Exam reminders:

Auto Loader

Use Auto Loader for scalable incremental file ingestion.

df = (
    spark.readStream
    .format("cloudFiles")
    .option("cloudFiles.format", "json")
    .option("cloudFiles.schemaLocation", "/Volumes/main/ops/checkpoints/orders_schema")
    .load("/Volumes/main/landing/orders")
)

(
    df.writeStream
    .format("delta")
    .option("checkpointLocation", "/Volumes/main/ops/checkpoints/orders_stream")
    .toTable("main.bronze.orders_raw")
)

Batch vs Streaming

Structured Streaming Checkpoints

Spark SQL and DataFrame Core

Transformations vs Actions

Common trap: caching a DataFrame is lazy. It is materialized only after an action.

df_cached = df.filter("amount > 0").cache()
df_cached.count()  # materializes cache

SQL Operations to Know

Join Types

Example anti join for new records:

new_customers = incoming.join(existing, on="customer_id", how="left_anti")

Deduplication Patterns

Latest record per key:

WITH ranked AS (
  SELECT
    *,
    ROW_NUMBER() OVER (
      PARTITION BY customer_id
      ORDER BY updated_at DESC
    ) AS rn
  FROM main.bronze.customers_raw
)
SELECT *
FROM ranked
WHERE rn = 1;

MERGE for Upserts

Use MERGE when records may be new, changed, or deleted.

MERGE INTO main.silver.customers AS target
USING main.bronze.customers_updates AS source
ON target.customer_id = source.customer_id
WHEN MATCHED THEN
  UPDATE SET *
WHEN NOT MATCHED THEN
  INSERT *;

Exam-safe CDC flow:

1. Read source changes.
2. Cast and validate fields.
3. Deduplicate changes by business key and sequence/timestamp.
4. MERGE into silver table.
5. Write audit metrics or job status.

Schema Handling

Example explicit schema:

from pyspark.sql.types import StructType, StructField, StringType, TimestampType, DecimalType

schema = StructType([
    StructField("order_id", StringType(), False),
    StructField("order_ts", TimestampType(), True),
    StructField("amount", DecimalType(10, 2), True)
])

Data Quality Patterns

Useful metadata columns:

SELECT
  *,
  current_timestamp() AS ingestion_ts,
  _metadata.file_name AS source_file
FROM read_files('/Volumes/main/landing/orders', format => 'json');

Unity Catalog Security Quick Reference

Governance Decision Table

Example grants:

GRANT USE CATALOG ON CATALOG main TO `data_analysts`;
GRANT USE SCHEMA ON SCHEMA main.gold TO `data_analysts`;
GRANT SELECT ON TABLE main.gold.sales_summary TO `data_analysts`;

Compute Selection

Common traps:

Databricks Workflows and Jobs

Workflow design pattern:

    flowchart LR
	    A[Ingest bronze] --> B[Validate and clean silver]
	    B --> C[MERGE dimensions]
	    B --> D[Build facts]
	    C --> E[Refresh gold marts]
	    D --> E
	    E --> F[Run quality checks]

Exam decision points:

SQL Warehouse vs Cluster

Performance Reference

Partitioning, OPTIMIZE, and Data Skipping

Troubleshooting Checklist

Common Exam Traps

Compact Snippet Bank

Create and Use Namespaces

CREATE CATALOG IF NOT EXISTS main;
CREATE SCHEMA IF NOT EXISTS main.silver;

USE CATALOG main;
USE SCHEMA silver;

Create Table from Query

CREATE OR REPLACE TABLE main.gold.daily_sales AS
SELECT
  date(order_ts) AS order_date,
  sum(amount) AS total_amount,
  count(*) AS order_count
FROM main.silver.orders
GROUP BY date(order_ts);

Append Cleaned Data

clean_df = (
    raw_df
    .filter("order_id IS NOT NULL")
    .withColumn("ingestion_ts", current_timestamp())
)

clean_df.write.mode("append").format("delta").saveAsTable("main.silver.orders")

Overwrite Safely for Rebuildable Gold Table

CREATE OR REPLACE TABLE main.gold.customer_metrics AS
SELECT
  customer_id,
  count(*) AS order_count,
  sum(amount) AS lifetime_value
FROM main.silver.orders
GROUP BY customer_id;

Watermarked Streaming Deduplication

deduped = (
    stream_df
    .withWatermark("event_ts", "1 day")
    .dropDuplicates(["event_id"])
)

Explain a Query Plan

EXPLAIN
SELECT customer_id, sum(amount)
FROM main.silver.orders
WHERE order_ts >= current_date() - INTERVAL 30 DAYS
GROUP BY customer_id;

Decision Trees

Ingestion Choice

    flowchart TD
	    A[Need to ingest data?] --> B{Source is files?}
	    B -- No --> C{Source is continuous events?}
	    C -- Yes --> D[Structured Streaming connector]
	    C -- No --> E[Batch read or connector-specific load]
	    B -- Yes --> F{Need scalable continuous file discovery?}
	    F -- Yes --> G[Auto Loader]
	    F -- No --> H{Prefer SQL incremental load?}
	    H -- Yes --> I[COPY INTO]
	    H -- No --> J[Spark batch read]

Table Maintenance Choice

    flowchart TD
	    A[Need to update target table?] --> B{Only new rows?}
	    B -- Yes --> C[Append]
	    B -- No --> D{Need inserts and updates?}
	    D -- Yes --> E[MERGE INTO]
	    D -- No --> F{Rebuild full result?}
	    F -- Yes --> G[CREATE OR REPLACE TABLE]
	    F -- No --> H[Use DELETE/UPDATE with clear predicate]

Final Review Checklist

Before exam day, make sure you can:

• Explain Delta Lake transaction log, time travel, schema enforcement, and VACUUM.
• Choose between COPY INTO, Auto Loader, batch reads, and Structured Streaming.
• Write or recognize MERGE INTO for upserts.
• Distinguish managed tables, external tables, views, materialized views, and volumes.
• Apply Unity Catalog hierarchy and least-privilege grants.
• Identify when a Spark operation is lazy, an action, narrow, or wide.
• Use SQL windows for latest-record deduplication.
• Choose job compute, all-purpose compute, or SQL warehouses based on workload.
• Diagnose common failures involving checkpoints, permissions, schema drift, small files, and driver collection.
• Recognize production patterns: idempotency, parameterization, retries, alerts, and quality checks.

For the next step, practice with scenario questions that force you to choose the right Databricks service, SQL command, Delta Lake operation, or production troubleshooting action under exam-style constraints.