Databricks

Certified Data Engineer Associate

Complete Study Guide

Exam Code Databricks-Certified-Data-Engineer-Associate

Duration 90 minutes
Questions 45 multiple-choice questions
Passing Score 70%
Format Multiple choice & multi-select
Validity 2 years

Exam Domain Weights

Domain Weight
Databricks Lakehouse Platform 24%
ELT with Apache Spark 29%
Incremental Data Processing 22%
Production Pipelines 16%
Data Governance 9%
Domain 1: Databricks Lakehouse Platform (24%)

1.1 What is the Lakehouse?

The Lakehouse is a modern data architecture that combines the flexibility and low cost of data lakes with the
reliability, performance, and ACID transactions of data warehouses. It eliminates the need for separate systems
by providing a unified platform for all analytics workloads.

Key Lakehouse Characteristics:

• ACID Transactions: Ensures data reliability and consistency
• Schema Enforcement: Prevents bad data from being written
• Governance & Security: Fine-grained access controls via Unity Catalog
• BI Support: Direct SQL access without data movement
• Open Storage Formats: Parquet, Delta — no vendor lock-in
• Unified for All Workloads: Batch, streaming, ML, SQL

1.2 Databricks Architecture

Databricks runs on a Control Plane (managed by Databricks) and a Data Plane (in your cloud account). The
cluster driver sends tasks to worker nodes, which process data in parallel using Apache Spark.

Cluster Types:
Type Use Case Key Feature

All-Purpose Clusters Interactive development, notebooks Manually started/stopped

Job Clusters Automated production jobs Auto-terminates after job

SQL Warehouses SQL Analytics / BI workloads Serverless or classic

1.3 Delta Lake

Delta Lake is an open-source storage layer that brings ACID transactions to Apache Spark and big data
workloads. It is the default table format in Databricks and stores data as Parquet files with a transaction log
(_delta_log).

Delta Lake Key Features:

• ACID Transactions: Atomicity, Consistency, Isolation, Durability
• Time Travel: Query previous versions of data using VERSION AS OF or TIMESTAMP AS OF
• Schema Evolution: Safely add/modify columns with mergeSchema option
• Schema Enforcement: Rejects writes that don't match the table schema
• Upserts (MERGE): Efficiently update/insert/delete records
• Z-Ordering: Co-locate related data for faster queries (multi-dimensional clustering)
• OPTIMIZE: Compacts small files into larger ones for better performance
 • VACUUM: Removes old data files no longer referenced (default 7-day retention)

Important Delta Lake SQL Commands:

DESCRIBE HISTORY table_name -- View all changes

SELECT * FROM table_name VERSION AS OF 3

SELECT * FROM table_name TIMESTAMP AS OF '2024-01-01'

OPTIMIZE table_name ZORDER BY (column1, column2)

VACUUM table_name RETAIN 168 HOURS -- 7 days

RESTORE TABLE table_name TO VERSION AS OF 5

Domain 2: ELT with Apache Spark (29%)

2.1 Spark Architecture

Apache Spark uses a master-worker (driver-executor) model. The Driver coordinates the execution plan while
Executors run tasks on worker nodes. Spark operations are either Transformations (lazy) or Actions (trigger
execution).

Transformations vs Actions:
Transformations (Lazy) Actions (Eager)

select(), filter(), groupBy() show(), count(), collect()

join(), union(), withColumn() write(), save(), take()

map(), flatMap() first(), reduce()

orderBy(), sort() foreach(), toPandas()

2.2 DataFrame API & Spark SQL

Core DataFrame Operations:
# Read data df = [Link]('delta').load('/path/to/data') df =
[Link]('/path', header=True, inferSchema=True)

# Transformations [Link]('col1','col2').filter(df.col1 >

10).groupBy('col2').agg(count('*'))

# Write data [Link]('delta').mode('overwrite').saveAsTable('my_table')

[Link]('date').parquet('/output/path')

# SQL [Link]('SELECT * FROM my_table WHERE col1 > 10') # Or use

createOrReplaceTempView [Link]('temp_view')

2.3 Common Functions

Frequently tested Spark functions:
Function Purpose

col(), lit() Reference column / literal value

when().otherwise() Conditional logic (like CASE WHEN)

explode() Flatten arrays into rows

collect_list() / collect_set() Aggregate values into array

regexp_replace() / regexp_extract() String pattern operations

to_date() / to_timestamp() Convert strings to date/timestamp

year(), month(), dayofweek() Extract date parts

lag() / lead() Window functions — access prev/next rows

rank() / dense_rank() Window functions — assign row ranks

 broadcast() Hint for broadcast joins (small tables)

coalesce(n) Reduce partitions (avoid shuffle)

repartition(n) Increase/redistribute partitions (shuffle)

2.4 Join Types

Join Type Description

inner Only matching rows from both sides

left / left_outer All rows from left, matching from right

right / right_outer All rows from right, matching from left

full / outer All rows from both sides

left_semi Left rows that have a match on right (no right cols)

left_anti Left rows that do NOT have a match on right

cross Cartesian product of both DataFrames

Domain 3: Incremental Data Processing (22%)

3.1 Structured Streaming

Structured Streaming is Spark's scalable, fault-tolerant stream processing engine. It treats streaming data as an
unbounded table and supports micro-batch and continuous processing modes.

Output Modes:
• Append: Only new rows added to the result table (default for most queries)
• Complete: Entire result table is written each trigger (required for aggregations)
• Update: Only changed rows are written (not supported for sorting)

Key Streaming Concepts:

# Read stream df = [Link]('delta').load('/path/to/table')

# Write stream query = [Link]\ .format('delta')\ .outputMode('append')\

.option('checkpointLocation', '/checkpoints/path')\ .trigger(processingTime='1
minute')\ .start('/output/path')

# Watermarking (handle late data) [Link]('event_time', '10 minutes')\

.groupBy(window('event_time','5 minutes'), 'user_id')\ .count()

Trigger Types:
Trigger Behavior

trigger(processingTime='1 minute') Process every 1 minute

trigger(once=True) Process all available data once, then stop

trigger(availableNow=True) Like once=True but uses multiple batches

trigger(continuous='1 second') Low-latency continuous processing

3.2 Auto Loader

Auto Loader (cloudFiles) efficiently ingests new files as they arrive in cloud storage. It uses directory listing or
file notification mode to detect new files and supports schema inference and evolution.

[Link]('cloudFiles')\ .option('[Link]', 'json')\

.option('[Link]', '/schema/path')\ .load('/source/path')

3.3 MERGE (Upsert) with Delta Lake

MERGE INTO target USING source ON condition WHEN MATCHED THEN UPDATE SET * WHEN NOT
MATCHED THEN INSERT * WHEN NOT MATCHED BY SOURCE THEN DELETE

Note: WHEN NOT MATCHED BY SOURCE requires Delta Lake 2.0+. MERGE is the foundation of SCD Type 1
(overwrite) operations.

3.4 Change Data Feed (CDF)

Change Data Feed tracks row-level changes (insert, update, delete) in Delta tables. Enable it at table creation
or alter time.

-- Enable CDF ALTER TABLE my_table SET TBLPROPERTIES ([Link] =

true)

-- Read changes SELECT * FROM table_changes('my_table', 2, 5) -- versions 2 to 5 SELECT

* FROM table_changes('my_table', '2024-01-01', '2024-01-10')
Domain 4: Production Pipelines (16%)

4.1 Delta Live Tables (DLT)

Delta Live Tables is a declarative ETL framework that automatically manages pipeline orchestration, error
handling, monitoring, and data quality. You declare what your data should look like; DLT manages how and
when to build it.

DLT Table Types:

Type Keyword Description

Streaming Table @[Link] + readStream Incremental, stateful processing

Materialized View @[Link] Batch, recomputed as needed

View @[Link] Temporary, not persisted to storage

DLT Data Quality Expectations:

@[Link]('valid_age', 'age > 0') -- warn, keep row
@dlt.expect_or_drop('valid_email', 'email IS NOT NULL') -- drop bad rows
@dlt.expect_or_fail('not_null_id', 'id IS NOT NULL') -- fail pipeline

Pipeline Modes:
• Triggered: Runs once when manually triggered or on a schedule, then stops
• Continuous: Runs perpetually, processing data as it arrives
• Development Mode: Reuses clusters, faster iteration; no retries
• Production Mode: New cluster per update, automatic retries on failure

4.2 Databricks Workflows (Jobs)

Databricks Jobs orchestrate notebooks, Python scripts, DLT pipelines, and SQL queries as multi-task
workflows with dependencies, scheduling, and alerting.

Key Job Features:

• Task Dependencies: Sequential, parallel, or conditional task execution
• Scheduling: Cron-based or triggered schedules
• Retry Policies: Configure max retries and retry intervals
• Notifications: Email alerts on start, success, or failure
• Repair Runs: Re-run only failed tasks without re-running successful ones
• Job Clusters: Auto-created and terminated per run (cost efficient)

4.3 Medallion Architecture

Layer Also Called Purpose Data Quality

Bronze Raw Land raw data as-is from source Unvalidated

Silver Cleaned Filter, cleanse, deduplicate Validated, conformed

Gold Business Aggregated, business-ready data Curated, trusted
Domain 5: Data Governance (9%)

5.1 Unity Catalog

Unity Catalog is Databricks' centralized governance solution that provides unified access control, auditing,
lineage, and data discovery across all Databricks workspaces in an account.

Unity Catalog Object Hierarchy:

Metastore → Catalog → Schema (Database) → Table / View / Function Full object name
format: [Link]

Securable Objects & Privileges:

Object Key Privileges

Catalog USE CATALOG, CREATE SCHEMA

Schema USE SCHEMA, CREATE TABLE, CREATE VIEW

Table SELECT, MODIFY, ALL PRIVILEGES

View SELECT

Function EXECUTE

Storage Credential CREATE EXTERNAL LOCATION

External Location READ FILES, WRITE FILES, CREATE TABLE

5.2 Table Types in Unity Catalog

Type Storage Managed By DROP behavior

Managed Table UC metastore storage Unity Catalog Deletes data + metadata

External Table External cloud path User Deletes only metadata

5.3 Data Lineage & Auditing

• Column-level lineage: Unity Catalog automatically tracks how data flows between tables
• Audit logs: All access and administrative actions are logged
• Data Discovery: Search for tables, views, and columns across the metastore
• Tags: Attach metadata tags to catalogs, schemas, tables, and columns
Quick Reference: Must-Know Concepts

Delta Lake File Layout

my_delta_table/ ■■■ _delta_log/ ← transaction log (JSON + checkpoint Parquet) ■ ■■■
[Link] ■ ■■■ [Link] ■ ■■■
[Link] ■■■ part-00000-....parquet ■■■
part-00001-....parquet

Common Gotchas / Exam Tips

1. VACUUM removes files older than retention threshold — default is 7 days (168 hours). Time travel
beyond this period won't work after VACUUM.
2. OPTIMIZE compacts files but does NOT remove old files — VACUUM does.
3. Auto Loader requires a checkpoint location. It tracks which files were processed.
4. coalesce() reduces partitions without full shuffle; repartition() does a full shuffle.
5. broadcast() hint tells Spark to broadcast the smaller table to all nodes — avoids shuffle join.
6. DLT @[Link] keeps bad rows (logs violation). @dlt.expect_or_drop removes them.
@dlt.expect_or_fail stops the pipeline.
7. Job clusters are preferred for production — they terminate after the run, reducing cost.
8. Unity Catalog GRANT syntax: GRANT privilege ON object TO principal
9. Change Data Feed (CDF) adds _change_type, _commit_version, _commit_timestamp columns.
10. Structured Streaming checkpoint stores offsets and state — never delete it mid-stream.
11. Watermark in streaming defines how long to wait for late data before closing a window.
12. MERGE INTO is idempotent if the source has no duplicates on the join key.
13. Schema enforcement rejects writes; schema evolution (mergeSchema=true) allows adding columns.
14. Delta tables store history indefinitely until VACUUM is run.
15. trigger(availableNow=True) is the recommended replacement for trigger(once=True).

Useful SQL Syntax Reference

-- Create Delta table CREATE TABLE [Link].my_table (id INT, name STRING) USING
DELTA LOCATION '/path/to/data'

-- Clone a table (shallow = metadata only, deep = full copy) CREATE TABLE my_clone
SHALLOW CLONE source_table VERSION AS OF 5 CREATE TABLE my_clone DEEP CLONE source_table

-- Convert Parquet to Delta CONVERT TO DELTA parquet.`/path/to/parquet`

-- Check table details DESCRIBE DETAIL my_table DESCRIBE HISTORY my_table DESCRIBE
EXTENDED my_table

-- Grant permissions (Unity Catalog) GRANT SELECT ON TABLE [Link].my_table TO

`user@[Link]` GRANT USE CATALOG ON CATALOG my_catalog TO `group_name` REVOKE
SELECT ON TABLE my_table FROM `user@[Link]`
Sample Practice Questions

Q1. Which Delta Lake command would you use to remove data files that are no longer referenced by the
transaction log?
A. OPTIMIZE
B. VACUUM
C. RESTORE
D. PURGE
✓ Answer: B. VACUUM

Q2. A streaming query must output a running total count grouped by category. Which output mode
should be used?
A. Append
B. Update
C. Complete
D. Overwrite
✓ Answer: C. Complete

Q3. Which Auto Loader format option is used to read JSON files?
A. [Link] = 'json'
B. format = 'auto'
C. [Link] = 'json'
D. [Link]()
✓ Answer: A. [Link] = 'json'

Q4. What happens when you DROP a managed table in Unity Catalog?
A. Only metadata is deleted
B. Only data is deleted
C. Both metadata and underlying data are deleted
D. Nothing is deleted
✓ Answer: C. Both metadata and underlying data are deleted

Q5. Which DLT expectation causes the pipeline to FAIL when violated?
A. @[Link]
B. @dlt.expect_or_drop
C. @dlt.expect_or_fail
D. @[Link]
✓ Answer: C. @dlt.expect_or_fail

Q6. What is the default retention period for VACUUM in Delta Lake?
 A. 24 hours
B. 48 hours
C. 7 days (168 hours)
D. 30 days
✓ Answer: C. 7 days (168 hours)

Q7. Which trigger type processes all available data and then stops the stream (recommended
approach)?
A. trigger(once=True)
B. trigger(continuous='1 second')
C. trigger(availableNow=True)
D. trigger(processingTime='0 seconds')
✓ Answer: C. trigger(availableNow=True)

Q8. Which join hint broadcasts a DataFrame to all executors to avoid a shuffle join?
A. repartition()
B. coalesce()
C. broadcast()
D. persist()
✓ Answer: C. broadcast()

Good luck on your exam! Remember: the official Databricks Academy learning paths and practice tests at
[Link] are the best preparation resources.