Databricks Certified Data Engineer Professional

A silver-layer SQL transform must retain exactly one latest order record per `order_id` using `updated_at`. Which Spark SQL pattern is the most reliable?

• AGROUP BY `order_id` and keep `MAX(updated_at)` without returning the full row
• BUse `ROW_NUMBER()` over `PARTITION BY order_id ORDER BY updated_at DESC` and filter to `r…
• CUse `DISTINCT order_id` after selecting all columns
• DSort the table by `updated_at` and apply `LIMIT 1`

You need to classify rows with a few regex-based rules in PySpark and keep the job optimizable by Catalyst. What should you do?

• AImplement the logic with a Python UDF
• BConvert the DataFrame to pandas first
• CUse built-in column expressions such as `when`, `regexp_extract`, and `rlike`
• DCollect the data to the driver and loop in Python

In Lakeflow Declarative Pipelines, when should a dataset be modeled as a streaming table instead of a materialized view?

• AWhen the source is append-oriented or streaming and the target should be updated incremen…
• BWhen every refresh should fully recompute the dataset from scratch
• COnly when the target is stored outside Unity Catalog
• DOnly when the table is smaller than 1 GB

A structured streaming job writes to Delta through `foreachBatch`. The job may be restarted after failures, and duplicate output must be avoided. Which design is best?

• AWrite in append mode without a checkpoint
• BCollect the micro-batch to the driver before writing
• CDelete the target and replay the full source after every restart
• DUse a stable checkpoint and make each micro-batch idempotent, for example with `MERGE` on…

A CDC feed contains operation codes `I`, `U`, and `D`. Which `MERGE` logic is required to correctly apply deletes into a Delta target?

• AOverwrite the target table on every batch
• BAdd a `WHEN MATCHED AND op = 'D' THEN DELETE` clause
• CIgnore delete rows and rely on `VACUUM`
• DUnion source rows to the target and run `DROP DUPLICATES`

A 20 MB dimension table is joined to a 4 TB fact DataFrame in PySpark. Which approach usually produces the most efficient plan?

• ARepartition both sides to 2,000 partitions before the join
• BCache both DataFrames regardless of reuse
• CBroadcast the dimension table during the join
• DConvert the dimension to a Python dictionary and look up values in a UDF

A Lakeflow Declarative Pipelines project has bronze, silver, and gold datasets in one pipeline. How should downstream dependencies be expressed so Databricks can manage refresh order automatically?

• AReference upstream datasets directly inside downstream dataset definitions
• BCreate separate Workflows jobs for every dependency edge
• CTrigger notebooks manually with `dbutils.notebook.run`
• DSchedule the gold layer last and assume upstream layers are finished

You need 15-minute event-time aggregations from a streaming source while limiting state growth and allowing late data up to 2 hours. Which design is correct?

• AUse processing-time windows only
• BWindow by event time and set a 2-hour watermark
• CUse complete mode with no watermark
• DSort every micro-batch on the driver before writing

A Spark SQL join must treat two NULL business-key values as equal. Which operator should you use in the join condition?

• A=
• BLIKE
• C<=>
• DIS DISTINCT FROM

Your team wants reusable PySpark cleansing steps that can be unit tested and chained cleanly across multiple DataFrames. Which pattern fits best?

• ADuplicate the logic in every notebook
• BCollect each DataFrame to pandas and transform locally
• CHide the logic inside widget default values
• DWrap transformations in functions and apply them with `DataFrame.transform(...)`

A batch transformation needs reference data from a REST API for every product code. Which design is most scalable on Databricks?

• ASnapshot the API data to Delta and join it in Spark
• BCall the API from a Python UDF for each row
• CUse `requests` inside `mapPartitions` for every row in every batch
• DQuery the API from the driver whenever a task completes

You enabled Change Data Feed on a Delta source and want to query only the rows that changed between versions in SQL. Which built-in function should you use?

• A`DESCRIBE HISTORY`
• B`delta_log()`
• C`table_changes()`
• D`snapshot_between()`

You need continuous cloud-file ingestion from an object store landing zone, with scalable file discovery and checkpointed progress. Which Databricks feature is designed for this?

• ACOPY INTO
• BAuto Loader
• CDelta Sharing
• DLakehouse Federation

An Auto Loader stream receives JSON files with occasional new fields and unexpected type mismatches. Which behavior best preserves ingestion continuity while surfacing anomalies for review?

• AUse schema evolution with a rescued data column
• BSet the stream to fail permanently on the first mismatch
• CInfer the schema from scratch in every micro-batch and skip checkpoints
• DTurn off schema tracking and ignore new columns

A team wants a managed connector to replicate data from a SaaS application into Databricks without building custom extraction code. Which service is the best fit?

• AUnity Catalog volumes
• BDatabricks Connect
• CDelta Sharing
• DLakeflow Connect

A source database emits out-of-order inserts, updates, and deletes with a monotonically increasing sequence column. Which ingestion pattern best builds a current-state silver table?

• AAppend every event forever and let analysts resolve current state in queries
• BReload the full source every minute
• CUse a Lakeflow Declarative Pipelines AUTO CDC flow keyed by the business key and sequence…
• DUse `COPY INTO` with `mergeSchema` and no delete handling

Customer state codes arrive as `ca`, `Ca `, `CALIFORNIA`, and blank strings. What is the best silver-layer first step before joining to a state dimension?

• AJoin first and clean later
• BTrim and normalize values to a canonical representation with built-in string functions
• CCast the column to integer
• DLeave the values raw and rely on `DISTINCT` downstream

A streaming pipeline must remove duplicate click events keyed by `event_id`, but duplicates can arrive up to 30 minutes late. Which approach should you use?

• AUse `DISTINCT` on each micro-batch with no watermark
• BOrder by ingestion time and keep the first row
• CRun `VACUUM` on the target after each batch
• DSet a 30-minute watermark on event time and apply streaming deduplication on the event key

A Lakeflow Declarative Pipeline should prevent invalid records from landing in the target table but continue processing valid rows. Which expectation behavior should be used?

• AUse an expectation action that drops invalid rows instead of failing the whole update
• BFail the entire pipeline whenever any bad row appears
• CDisable expectations and trust downstream consumers to clean the data
• DWrite invalid rows directly into the target and label them later

You are building a customer dimension that must preserve attribute history after each source change. Which pattern best satisfies the requirement?

• AOverwrite the dimension on every load
• BUse SCD Type 1 only
• CUse SCD Type 2 with surrogate keys, effective dates, and a current-row indicator
• DAppend raw CDC events directly to the serving model

A silver table combines phone numbers from multiple countries in inconsistent formats. What transformation goal should be prioritized before deduplication?

• AHash the raw string as-is
• BStandardize numbers into one canonical format
• CSort rows alphabetically
• DConvert all values to NULL when separators differ

A streaming deduplication job uses a 24-hour watermark. What does Databricks guarantee about duplicates that arrive more than 24 hours later?

• AThey are not guaranteed to be removed because state older than the watermark may be evict…
• BThey are always removed regardless of arrival time
• CThey trigger an automatic full state rebuild
• DThey are rewritten into the checkpoint as updates

Your company needs to provide an external partner read-only access to curated Delta tables without copying the data into the partner's cloud account first. Which Databricks capability is intended for this?

• AAuto Loader
• BLakeflow Connect
• CDelta Sharing
• DDatabricks Asset Bundles

Analysts need to query operational PostgreSQL data from Databricks using Unity Catalog naming and permission controls, but the data should remain in PostgreSQL. Which design is best?

• AExport PostgreSQL nightly to CSV and load it to bronze
• BCreate a Lakehouse Federation connection and expose the source through a foreign catalog
• CUse Delta Sharing against the PostgreSQL database directly
• DMount PostgreSQL through DBFS

Across several workspaces, you need historical visibility into job runs, query activity, and billing data for dashboards and trend analysis. Where should you start?

• ADBFS root
• BNotebook revision history
• CCluster driver logs only
• DSystem tables

A bronze ingestion pipeline has an SLA that new files must be reflected in downstream tables within 20 minutes. Which metric is most useful to alert on?

• AData freshness or end-to-end lag
• BJVM heap size
• CNumber of notebooks in the repo
• DLength of the target table comment

A production workflow should notify the on-call channel immediately when retries are exhausted and a task still fails. What is the simplest robust approach?

• AAdd print statements to the notebook
• BRely on a developer watching the run page
• CConfigure job failure notifications in Databricks Workflows
• DSend emails manually after business hours

A structured streaming job's checkpoint directory was accidentally deleted, but the target Delta table must remain correct after recovery. What is the safest restart strategy?

• ADisable checkpointing and resume the same code path
• BStart with a new checkpoint and use an idempotent sink or replayable source logic
• CReuse the missing checkpoint path and expect Spark to rebuild it automatically
• DAppend blindly and remove duplicates later with `VACUUM`

You want to find the slowest pipelines and the most failure-prone jobs over the last 14 days at the account level. Which approach is most appropriate?

• AQuery system tables and aggregate run duration and failure metrics
• BInspect only the most recent run for each job
• CRead Spark UI pages manually for each cluster
• DExport notebook HTML files for comparison

A large Delta table is frequently filtered by `customer_id` and `order_date`, and the access pattern changes over time. Which storage layout strategy is generally better than high-cardinality partitioning?

• ADisable statistics collection
• BStore everything in one CSV file
• CPartition the table by `customer_id`
• DUse liquid clustering on the common filter columns

A SQL warehouse runs aggregation-heavy analytics on Delta tables. Which engine setting typically improves performance without changing query logic?

• AForce Python UDF execution
• BEnable Photon
• CTurn off adaptive query execution
• DDisable whole-stage code generation

A Delta table receives many small batch writes and query latency is rising. Which action most directly addresses the small-file problem?

• AIncrease the notebook timeout
• BDrop and recreate the metastore
• CCompact the data with `OPTIMIZE` or an equivalent file-compaction strategy
• DConvert the table to CSV

A join between a very large fact table and a medium dimension table is slow because a small number of keys are massively overrepresented. What is the most targeted mitigation?

• AHandle skew with techniques such as salting or skew-aware join planning
• BCache the dimension on the driver only
• CDisable Adaptive Query Execution
• DRepartition both tables to a single partition

A nightly ETL job runs for 20 minutes and is idle the rest of the day. Which compute choice is usually the most cost-efficient?

• AKeep an interactive cluster running 24/7
• BUse the largest fixed-size cluster available
• CPin one executor so autoscaling cannot shrink fully
• DUse ephemeral job compute or serverless job compute instead of an always-on cluster

A BI dashboard query scans far more data than necessary from a wide gold table. Which change most directly reduces both cost and latency?

• AUse `SELECT *` and rely on caching later
• BSelect only required columns and apply selective filters early
• CExport the table to JSON before querying
• DIncrease the warehouse auto-stop time

Large `MERGE` statements into a Delta table remain slow even after scaling up compute. The merge predicates align with a small set of business keys. Which improvement is most likely to help?

• ADisable the Delta transaction log
• BSwitch the target to plain Parquet
• CCluster or reorganize the table on the merge keys so fewer files are scanned and rewritten
• DIncrease shuffle partitions indefinitely

A team should query a curated customer view but must not access the underlying raw tables directly. In Unity Catalog, what is the least-privilege pattern?

• AGrant `SELECT` on the view and withhold direct access to the base tables
• BGrant `ALL PRIVILEGES` on the schema
• CAdd the users as metastore admins
• DGive `CAN MANAGE` on the warehouse

A workflow needs a database password for a JDBC connection. What is the recommended way to manage the credential?

• AHard-code it in the notebook
• BStore it in a secret and reference it at runtime
• CPut it in a table comment
• DEmail it to the job owner monthly