Databricks Certified Data Engineer Associate Cheat Sheet

This Cheat Sheet 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.

Use the tables for a quick pre-exam check. Expand a topic’s notes for explanations, examples, and additional distinctions.

High-Yield Exam Map

Lakehouse Object Model

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

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

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

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 main.bronze.orders_raw
FROM 's3://example-bucket/incoming/orders/'
FILEFORMAT = CSV
FORMAT_OPTIONS ('header' = 'true', 'inferSchema' = 'true');

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

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

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 Cheat Sheet

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`;

catalog.schema.table

Compute Selection

Databricks Workflows and Jobs

    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]

SQL Warehouse vs Cluster

Performance Reference

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.

Cheat Sheet for Databricks DEA Candidates

This Cheat Sheet is for candidates preparing for the Databricks Certified Data Engineer Associate exam, official exam code Databricks DEA, from Databricks. Use it as a focused refresh before moving into IT Mastery practice, topic drills, mock exams, and detailed explanations.

The exam rewards practical understanding of how data engineering work is done on the Databricks Lakehouse Platform: ingesting data, transforming it reliably, using Delta Lake correctly, building production pipelines, scheduling jobs, and applying basic governance and performance practices.

High-Yield Exam Map

Ingestion: Batch, Incremental, and Streaming

The exam often tests whether you can select the correct ingestion approach.

Ingestion Method Decision Table

Spark SQL and Data Transformations

A data engineer on Databricks should be comfortable reading and reasoning about SQL transformations.

SQL Transformation Patterns

SELECT *
FROM (
  SELECT
    *,
    ROW_NUMBER() OVER (
      PARTITION BY customer_id
      ORDER BY updated_at DESC
    ) AS rn
  FROM source_table
)
WHERE rn = 1;

Delta Live Tables and Declarative Pipelines

If a scenario involves declarative pipelines, data quality checks, managed dependencies, or simplified batch/streaming pipeline operations, review Delta Live Tables concepts.

Expectations: What to Remember

Candidate trap: expectations are not just documentation. Depending on configuration, they can track, drop, or fail records.

Databricks SQL Review

Databricks SQL supports analytics, dashboards, visualizations, and alerts using SQL warehouses.

Databricks SQL Candidate Traps

• A SQL warehouse is not the same as a general-purpose interactive cluster.
• Dashboards show query results; they do not replace upstream data modeling.
• Alerts depend on query results and refresh behavior.
• Slow dashboard performance may require improving the underlying query, table layout, or warehouse sizing.

Scenario Decision Flow

    flowchart TD
	    A[New data engineering scenario] --> B{Is the source continuously arriving?}
	    B -- No, periodic files --> C{Need simple incremental batch load?}
	    C -- Yes --> D[COPY INTO]
	    C -- No --> E[Batch read and write]
	    B -- Yes --> F{Cloud files at scale?}
	    F -- Yes --> G[Auto Loader with checkpointing]
	    F -- No --> H[Structured Streaming source]
	    D --> I[Write Bronze Delta]
	    E --> I
	    G --> I
	    H --> I
	    I --> J{Need cleaned reusable data?}
	    J -- Yes --> K[Transform to Silver]
	    J -- No --> L[Keep raw/audit layer]
	    K --> M{Need BI or business metrics?}
	    M -- Yes --> N[Gold tables or views]
	    M -- No --> O[Reusable Silver tables]

Use this flow as a quick mental model. The real exam may phrase the scenario differently, but the decision points are usually about arrival pattern, reliability, transformation need, and serving layer.

Frequently Tested Command Intent

Common Candidate Mistakes

Conceptual Mistakes

• Treating Delta Lake as just another file format.
• Assuming all ingestion should be streaming.
• Confusing Bronze/Silver/Gold with security levels.
• Choosing overwrite when the scenario requires upsert.
• Ignoring checkpointing in streaming recovery scenarios.
• Assuming a view stores physical data.
• Using inner joins when unmatched source rows must be retained.
• Treating watermarks as late-data guarantees rather than state boundaries.

Practical Design Mistakes

• Full-refreshing a large table when incremental processing is appropriate.
• Partitioning by a high-cardinality column.
• Running BI dashboards directly on messy Bronze tables.
• Using development clusters for all production automation.
• Skipping data quality checks until the Gold layer.
• Not preserving raw data before cleaning.
• Granting broad access instead of using least privilege.

Put the review into practice