Databricks Data Engineer Professional Practice Test

A data engineering team has an existing production Spark Structured Streaming job written in PySpark that reads from Delta Lake, applies a stateful sessionization window function using flatMapGroupsWithState, and writes results to a Gold Delta table. The team is evaluating migrating this job to Lakeflow Spark Declarative Pipelines. A senior engineer raises concerns about whether Lakeflow SDP can support all the features required. Which statement MOST accurately describes the capabilities and limitations of Lakeflow SDP compared to the team's direct Structured Streaming job for this specific use case?

1. ALakeflow SDP can fully replace the direct Structured Streaming job because it executes all pipeline code on the same Spark Structured Streaming engine. flatMapGroupsWithState can be used inside a @dlt.table decorated function by wrapping the streaming DataFrame operation before returning the result DataFrame. The SDP framework passes the raw streaming DataFrame to the function, allowing arbitrary Spark Structured Streaming operations.
2. BBoth approaches are equivalent in capability, but direct Structured Streaming is always preferable for production workloads because Lakeflow SDP adds unnecessary management overhead (pipeline metadata, event logging, Unity Catalog lineage) that increases query latency by 15–30% compared to an equivalent direct Structured Streaming job with the same transformations.
3. CLakeflow SDP is strictly more capable than direct Structured Streaming because it runs on a newer, proprietary streaming engine built by Databricks that has replaced Spark Structured Streaming internally. All Structured Streaming operators, including flatMapGroupsWithState, are available in Lakeflow SDP and execute with better performance due to the optimized runtime. Migrating to Lakeflow SDP is always recommended for any streaming workload.
4. DThe team can migrate to Lakeflow SDP by declaring the Gold table as a Streaming Table and using APPLY CHANGES INTO with a custom SQL SEQUENCE BY expression that replicates the sessionization logic. APPLY CHANGES supports arbitrary windowing functions in its SEQUENCE BY clause, making it a suitable replacement for flatMapGroupsWithState in sessionization use cases.
5. ELakeflow SDP does not natively support flatMapGroupsWithState or other arbitrary stateful Structured Streaming operators within its declarative table definitions. Lakeflow SDP is designed for standard SQL-expressible transformations and common streaming patterns (APPLY CHANGES, aggregations, joins). For workloads requiring custom stateful operator logic like flatMapGroupsWithState, direct Spark Structured Streaming (via the DataFrame API in a regular Python/Scala job) remains the more appropriate choice because it gives full access to all Structured Streaming operators without the constraints of the declarative pipeline framework.

A data engineer is optimizing a 10 TB Unity Catalog managed Delta table storing e-commerce events. Queries frequently filter on user_id (100 million distinct values) and event_type (12 distinct values). The table was created without any explicit file layout optimization. The Spark UI shows queries scanning all 2,000 data files even with highly selective WHERE user_id = '...' filter clauses. Which combination of actions correctly diagnoses the root cause and applies the most effective optimization?

1. AThe root cause is that Delta Lake does not maintain statistics for columns with more than 1,000 distinct values. Solution: partition on event_type (low cardinality) and set spark.sql.shuffle.partitions=200 for query-level parallelism.
2. BThe root cause is that without any data layout optimization, high-cardinality user_id values are scattered randomly across all files — even though per-file min/max statistics are collected, every file's range includes the target value, so no file can be skipped. Solution: run OPTIMIZE ... ZORDER BY (user_id, event_type) to co-locate similar user_id values within fewer files, tightening per-file min/max ranges and enabling data skipping to prune most files.
3. CThe root cause is that data skipping in Delta Lake only works for partitioned columns. Since neither user_id nor event_type is a partition key, Delta skips no files. Solution: partition the table by user_id using range partitioning to enable partition pruning.
4. DThe root cause is that the table requires explicit ANALYZE TABLE ... COMPUTE STATISTICS FOR COLUMNS user_id, event_type before any data skipping can occur — Delta Lake never collects statistics automatically. Solution: run ANALYZE and then queries will use data skipping without any layout changes.
5. EThe root cause is that Spark UI showing all files scanned indicates the query is not using predicate pushdown. Solution: switch from the PySpark DataFrame API to Spark SQL string queries, which support predicate pushdown while DataFrame operations do not.

A user opens Query History and clicks a query, but the Query Profile option is missing. Which explanation is most consistent with the documented behavior of query profiles?

1. AQuery Profile is available only for queries executed from notebooks, not from SQL Warehouses.
2. BQuery Profile requires enabling Change Data Feed on every table referenced by the query.
3. CQuery Profile is not available if the query result was served from the query cache.
4. DQuery Profile is available only to workspace admins, regardless of warehouse permissions.

A data engineer is designing a star schema for a large analytical workload in Databricks. The central fact table fact_orders (500 GB, growing ~5 GB/day) is joined frequently with dim_product (10 MB) and dim_customer (200 MB) on product_id and customer_id respectively. Analysts filter primarily by order_date and product_category. Which combination of design decisions BEST optimizes query performance for this star schema?

1. APartition fact_orders by product_id and customer_id to enable partition pruning on the join keys used with both dimension tables.
2. BPartition fact_orders by product_id (high cardinality) and use ZORDER BY (order_date, product_category) since ZORDER handles all filter columns equally regardless of cardinality.
3. CPartition fact_orders by order_date and apply CLUSTER BY (product_category) — partitioning and liquid clustering can be combined on the same table for complementary optimization.
4. DApply liquid clustering on fact_orders with CLUSTER BY (order_date, product_category) to optimize for the primary query filters; dim_product and dim_customer are small enough to be broadcast-joined automatically by Spark without additional layout optimization.

You need to share a Unity Catalog-managed dataset with another organization that also uses a Unity Catalog-enabled Databricks workspace. You also want to share a notebook and a Unity Catalog volume along with the tables, and you do not want to manage long-lived bearer tokens for the recipient. Which Delta Sharing model best fits these requirements?

1. AOpen Delta Sharing (D2O) using bearer tokens, because it supports notebooks and volume sharing and avoids token management.
2. BDatabricks-to-Databricks (D2D) Delta Sharing, because it supports sharing tables plus notebooks/volumes/models and does not require token-based credentials for the recipient.
3. CCustomer-managed open-source Delta Sharing server, because it is required for any Databricks-to-Databricks sharing and provides the best performance.
4. DUnity Catalog cross-workspace permissions, because Delta Sharing is only for non-Databricks recipients.

A security engineer is applying column masks to a Unity Catalog table. Which of the following statements correctly describes the constraints on column masks?

1. AA column can have multiple masks applied simultaneously, all of which are evaluated in the order they were added.
2. BEach column can have at most one column mask, and the mask function must return the same data type as the masked column.
3. CA column mask is defined as a Python UDF and must be stored in the same schema as the table.
4. DColumn masks can be applied to views as well as base tables, allowing centralized masking logic.
5. EColumn masks work with all Databricks Runtime versions, including those below 12.2 LTS, where data is returned in plain text.

A data engineer must build an append-only ingestion pipeline that lands raw events from a message bus into a Delta Bronze table. The pipeline is a Structured Streaming job that must be able to fail and be restarted at any time without producing duplicate rows in the Bronze table, and it must not require a downstream deduplication step. The source provides an at-least-once delivery guarantee. Which combination of mechanisms provides exactly-once append semantics for the Bronze write under restarts?

1. AWrite with .mode('append') in a foreachBatch and, inside the batch function, run a DELETE on the Bronze table for the current batch's key range before inserting, so each restart cleans up any partial prior write. This makes the append idempotent because the delete-then-insert pair is atomic per batch.
2. BUse a Structured Streaming write to the Delta table with a configured checkpointLocation. Delta's streaming sink records each committed batch id in its transaction log, so on restart Delta skips micro-batches that were already committed, giving exactly-once appends without a manual dedup step.
3. CSet the Delta table property delta.enableChangeDataFeed to true and write in complete output mode. Change Data Feed tracks every committed version, so on restart the sink replays only the uncommitted deltas, guaranteeing no duplicate rows are appended.
4. DAdd .option('txnAppId', 'bronze').option('txnVersion', current_timestamp()) to the streaming write. The txnVersion derived from the current timestamp guarantees a unique transaction id per batch so that Delta rejects any replayed write with an older timestamp.
5. EEnable spark.databricks.delta.autoCompact and optimizeWrite, then write in append mode without a checkpoint. Auto-compaction rewrites duplicate rows into a single file during ingestion, so restarts that re-append the same records are automatically collapsed to one copy.