Databricks Data Engineer Associate Practice Test

A data engineering team is building a Gold layer aggregation pipeline. They have a Silver layer PySpark DataFrame silver_orders_df with columns customer_id, order_date, product_category, order_total, and is_returned (BOOLEAN). The business requires a summary DataFrame with the following per-customer, per-category metrics: (1) total number of orders, (2) total revenue from non-returned orders only, (3) percentage of orders that were returned (return rate as a decimal between 0 and 1), and (4) the rank of each customer within each product category by total revenue (rank 1 = highest revenue). Null order_total values should be treated as 0 in all revenue calculations. Which PySpark implementation is correct?

1. Afrom pyspark.sql.functions import count, sum, avg, rank, coalesce, lit, col from pyspark.sql import Window base_df = silver_orders_df.groupBy('customer_id', 'product_category').agg( count('order_date').alias('total_orders'), sum(coalesce('order_total', lit(0))).alias('total_revenue'), avg('is_returned').alias('return_rate') ) window_spec = Window.partitionBy('product_category').orderBy(col('total_revenue').desc()) result_df = base_df.withColumn('revenue_rank', rank().over(window_spec))
2. Bfrom pyspark.sql.functions import count, sum, mean, dense_rank, coalesce, lit, col, when from pyspark.sql import Window base_df = silver_orders_df.groupBy('customer_id', 'product_category').agg( count('order_date').alias('total_orders'), sum(when(col('is_returned') == False, coalesce(col('order_total'), lit(0))).otherwise(lit(0))).alias('total_revenue'), (sum(when(col('is_returned') == True, 1).otherwise(0)) / count('order_date')).alias('return_rate') ) window_spec = Window.partitionBy('product_category').orderBy(col('total_revenue').desc()) result_df = base_df.withColumn('revenue_rank', dense_rank().over(window_spec))
3. Cfrom pyspark.sql.functions import count, sum, coalesce, lit, col, when, rank from pyspark.sql import Window base_df = silver_orders_df.groupBy('customer_id', 'product_category').agg( count('*').alias('total_orders'), sum(when(~col('is_returned'), coalesce(col('order_total'), lit(0))).otherwise(lit(0))).alias('total_revenue'), (sum(col('is_returned').cast('int')) / count('*')).alias('return_rate') ) window_spec = Window.partitionBy('product_category').orderBy(col('total_revenue').desc()) result_df = base_df.withColumn('revenue_rank', rank().over(window_spec))
4. Dfrom pyspark.sql.functions import count, sum, coalesce, lit, col, when, row_number from pyspark.sql import Window base_df = silver_orders_df.filter(col('is_returned') == False).groupBy('customer_id', 'product_category').agg( count('order_date').alias('total_orders'), sum(coalesce(col('order_total'), lit(0))).alias('total_revenue'), lit(0.0).alias('return_rate') ) window_spec = Window.partitionBy('product_category').orderBy(col('total_revenue').desc()) result_df = base_df.withColumn('revenue_rank', row_number().over(window_spec))

A data engineer is troubleshooting a performance issue in a Databricks notebook. A PySpark job that aggregates 500 GB of sales data completes in 45 minutes — far longer than the expected 8 minutes for similar data volume. The engineer suspects either a data skew problem (one partition receiving disproportionate data) or excessive shuffle operations. The engineer knows that Databricks provides built-in debugging and observability tools accessible from within the workspace. Which combination of Databricks built-in tools and their correct usage best helps diagnose data skew and excessive shuffle in the described scenario?

1. AThe engineer should use the %sh top shell magic command to monitor real-time CPU utilization on the driver node, combined with print(df.rdd.getNumPartitions()) to count partitions in the notebook output; if the partition count is below 200, the engineer should repartition the DataFrame and re-run the job. These are the only two built-in diagnostic approaches — the Spark UI is not accessible from within a Databricks notebook environment.
2. BThe engineer should use the Spark UI (accessible via the cluster compute page or the running job's notebook link), specifically the Stages tab to identify tasks with maximum task durations significantly higher than median duration (indicating skew), and the SQL/DataFrame tab to view the query execution plan and measure shuffle read/write bytes; additionally, the notebook's built-in cluster metrics panel shows live executor memory and CPU utilization during the job run.
3. CThe engineer should add spark.conf.set('spark.sql.adaptive.enabled', 'false') and then use the explain() method on the DataFrame (e.g., df.explain('extended')) to print the logical and physical plan in the notebook cell output, which will explicitly flag skewed partitions by listing their row counts alongside each shuffle exchange node in the plan tree; no other tools are needed since the extended explain plan contains all skew and shuffle metrics.
4. DThe engineer should install the third-party spark-monitor PyPI package via %pip install spark-monitor and attach it to the notebook to enable rich task-level metrics, because the built-in Databricks Spark UI only shows aggregate job-level statistics and does not provide per-task duration breakdowns, stage-level shuffle read/write bytes, or partition-level row counts that are needed to diagnose skew and excessive shuffle.

A data engineer is setting up a new Databricks Asset Bundle project. After running databricks bundle init, they examine the generated project structure. Which of the following BEST describes the required top-level components of a well-formed Asset Bundle, and what role each component plays in the deployment lifecycle?

1. AA valid Asset Bundle requires only a databricks.yml root configuration file; all other files such as notebooks and Python scripts are optional and referenced dynamically at runtime from the Databricks workspace file system.
2. BA valid Asset Bundle must contain a requirements.txt for Python dependencies, a Makefile for build automation, and a databricks.yml file—all three are mandatory for the bundle to pass validation during databricks bundle deploy.
3. CA valid Asset Bundle is anchored by a databricks.yml root configuration file that declares the bundle name, workspace targets (environments), and resource definitions. Supporting files such as notebooks, Python scripts, and additional YAML resource files are referenced from this root and organized within the project directory structure.
4. DA valid Asset Bundle must include a bundle_manifest.json file that enumerates every asset by its SHA-256 hash, a databricks.yml configuration file, and a dedicated clusters/ directory containing one JSON file per cluster definition used in the bundle.
5. EA valid Asset Bundle requires a pipeline_spec.json for Delta Live Tables definitions, a jobs_spec.json for workflow definitions, and a databricks.yml to link them—separate JSON spec files are mandatory because YAML alone cannot express the full resource schema.

A data engineering team manages a large Delta table containing billions of rows of IoT sensor events with columns device_id, event_type, region, and timestamp. For two years the team has been running OPTIMIZE ... ZORDER BY (device_id, event_type) nightly. Recently, stakeholders have added new dashboards that filter exclusively on region, and the team has observed that Z-ORDER provides no benefit for these queries. A senior architect proposes enabling Liquid Clustering on all four columns instead. After running ALTER TABLE sensor_events CLUSTER BY (device_id, event_type, region, timestamp), a junior engineer asks for clarification on how Liquid Clustering fundamentally differs from Z-ORDER in terms of data layout maintenance and multi-column filter flexibility. Which of the following most accurately describes the architectural difference?

1. ALiquid Clustering uses a space-filling curve algorithm to co-locate related data across all specified clustering keys simultaneously, allowing efficient data skipping for any combination or subset of those keys without requiring a fixed column order, and it maintains clustering incrementally on newly written files without requiring a full table rewrite via OPTIMIZE each time.
2. BLiquid Clustering applies a static, lexicographic sort on all four clustering columns in the exact order they are declared, meaning queries benefit from data skipping only when filters are applied from left to right on those columns — identical behavior to a composite index in a relational database — and therefore still requires a full OPTIMIZE run every time the priority of filter columns needs to be adjusted to accommodate new query patterns.
3. CLiquid Clustering eliminates the Delta transaction log and replaces it with a new proprietary metadata index that maps every data value in the clustering columns to specific Parquet row groups, which means that enabling it requires a complete table migration that will render all pre-existing Delta time travel snapshots inaccessible because the old transaction log entries reference file stats that are incompatible with the new clustering metadata format.
4. DLiquid Clustering is only cost-effective for Delta tables smaller than 500 GB because the clustering algorithm must load all file-level statistics into the driver node's memory to compute optimal cluster assignments, and for tables exceeding this size, Z-ORDER combined with aggressive partition pruning via PARTITIONED BY on the highest-cardinality column remains the only supported path to achieving sub-second query response times in the Databricks platform.

A data engineering team at a financial services company needs to set up compliance monitoring for their Databricks environment. The compliance officer asks the following three questions: 1. Where are Unity Catalog audit logs stored, and how can they be queried? 2. What types of events are captured in the audit logs? 3. Can non-admin users be granted access to query the audit logs? Which answer CORRECTLY addresses all three questions based on the current Unity Catalog architecture as of November 2025?

1. A1. Unity Catalog audit logs are written by Databricks to a customer-specified S3 bucket or Azure Data Lake Storage (ADLS) path that must be configured by an Account Admin in the Databricks Account Console under 'Log Delivery'. Once delivered, they can be queried using any external tool (Athena, Synapse, etc.) or ingested back into Databricks. 2. The logs capture workspace-level events such as notebook edits, job runs, cluster creation/deletion, and Unity Catalog object access (table reads, GRANT statements, table creation). 3. Non-admin users cannot be granted access to the raw log delivery bucket unless an admin grants them cloud-level IAM permissions to the storage account directly, bypassing Unity Catalog access control entirely.
2. B1. Unity Catalog audit logs are stored in the system catalog as part of Unity Catalog's built-in system tables, specifically accessible via the system.access.audit table. They are queryable using standard SQL from any Unity Catalog-enabled workspace. 2. The logs capture a broad range of events including data access events (table reads, schema queries), administrative actions (GRANT/REVOKE statements, cluster creation, job runs, Unity Catalog object lifecycle events like CREATE TABLE and DROP TABLE), and Delta Sharing events. 3. Yes—account admins can grant non-admin users USE and SELECT privileges on the relevant system schemas (e.g., system.access), allowing those users to query audit logs via SQL without requiring admin privileges.
3. C1. Unity Catalog audit logs are maintained exclusively inside the Unity Catalog metastore's internal Hive-compatible metadata store, and can only be retrieved using the DESCRIBE HISTORY command on individual tables or the Databricks REST API's /api/2.0/unity-catalog/audit-events endpoint. Direct SQL querying of audit logs is not supported. 2. The logs capture only data-access events (SELECT queries and MERGE operations) on Unity Catalog tables, because write events such as INSERT and UPDATE are not considered audit-relevant by default and require a separate 'write auditing' feature flag to be enabled by Databricks Support. 3. Non-admin users cannot query audit logs under any configuration—access to Databricks audit data is restricted exclusively to Account Admins and Metastore Admins by a hardcoded platform policy.
4. D1. Unity Catalog audit logs are stored in a dedicated audit catalog named databricks_audit that is automatically created in every Unity Catalog metastore, with tables organized by event date as daily partitions (e.g., databricks_audit.workspace_events.events_2025_11_30). They must be queried using Spark SQL with the /*+ SCAN */ hint to bypass Delta caching for real-time compliance queries. 2. The logs capture only Unity Catalog metadata operations (CREATE, ALTER, DROP on catalogs, schemas, and tables) and do not include data read or write events, because capturing row-level query data would violate GDPR data minimization principles by default. 3. Non-admin users can be granted access only by a Metastore Admin assigning them the built-in audit_reader role using the command GRANT ROLE audit_reader TO user 'user@company.com'.