Databricks-DE-Pro Practice Test 16 — 15 Questions

Question 1

A data engineer is using Lakehouse Federation to query a PostgreSQL database. The engineer notices that a query filtering on a column with a high cardinality is performing poorly, even though the remote database has an index on that column. What is the most likely reason for the poor performance?

Choose an answer to begin — your selection is scored in the full session.

Accepted Answer

The filter condition uses a function or expression that cannot be pushed down to PostgreSQL, causing a full table scan.. Poor performance on a filtered query against a federated PostgreSQL database often indicates that the filter was not pushed down. If the filter uses an expression that PostgreSQL cannot evaluate, Databricks retrieves all data and filters locally, bypassing the remote index. The engineer should simplify the filter or use pushdown-compatible expressions to leverage the index and improve performance.

Answer

The PostgreSQL database is not configured with the correct statistics, causing the query planner to choose a sequential scan.

Answer

The foreign catalog is using a JDBC connection with a small fetch size, causing many round trips to the database.

Answer

The foreign catalog is not configured to allow predicate pushdown for the PostgreSQL database.

Question 2

A data engineer needs to grant a new data analyst the ability to query tables in the `sales` catalog, which is in Unity Catalog. The analyst should only be able to read data and not modify any tables or metadata. Which sequence of privileges should the engineer grant to the analyst?

Accepted Answer

Grant `USE CATALOG` on `sales`, `USE SCHEMA` on all schemas, and `SELECT` on all tables.. In Unity Catalog, querying a table requires a chain of privileges: `USE CATALOG` on the catalog, `USE SCHEMA` on the schema, and `SELECT` on the table. Granting these three privileges provides read-only access without allowing any modifications. This follows the principle of least privilege and ensures the analyst can only read data as required.

Answer

Grant `ALL PRIVILEGES` on the catalog and rely on table-level `SELECT` to restrict modifications.

Answer

Grant `USE CATALOG` on `sales` and `SELECT` on all tables, without schema-level privileges.

Answer

Grant `BROWSE` on the catalog, `READ VOLUME` on all volumes, and `SELECT` on all tables.

Question 3

A data engineer is asked to implement column-level masking for a Unity Catalog table `main.hr.employees` that contains a column `ssn` with Social Security numbers. The requirement is that only members of the `hr_group` should see the full SSN, while all other users should see only the last four digits (e.g., XXX-XX-1234). The engineer decides to use a column mask function. Which statement accurately describes how to apply the mask?

Accepted Answer

Create a function that returns the masked value based on IS_MEMBER('hr_group'), then use ALTER TABLE ... ALTER COLUMN ssn SET MASK function_name.. Column masks in Unity Catalog are implemented via user-defined functions that can dynamically return different values based on the user's group membership. The function can use IS_MEMBER to check if the user is in hr_group and return either the full SSN or a masked version. The mask is attached to the column using ALTER TABLE ... ALTER COLUMN ... SET MASK. This enforces masking for all users except those in the specified group, meeting the requirement.

Answer

Use row-level filters to exclude rows where the user is not in hr_group, effectively hiding sensitive data.

Answer

Create a view that selects all columns except ssn, and a separate view that includes ssn for hr_group, then grant appropriate privileges on each view.

Answer

Grant SELECT on the ssn column only to hr_group, and revoke it from all other users.

Question 4

A data engineering team runs a nightly batch job on a Databricks job cluster. The job reads a large Parquet dataset, performs transformations, and writes the result to a Delta table. The cluster is configured with autoscaling from 4 to 16 workers and uses the default Spark configuration. The team observes that the job runs for 2 hours, but the cluster's CPU utilization is only around 30% throughout the run. They want to reduce cost without increasing runtime. Which action is most likely to achieve this?

Accepted Answer

Reduce the number of workers in the cluster to match the observed CPU utilization.. The cluster is over-provisioned because CPU utilization is low, indicating the job is not CPU-bound. Reducing the number of workers aligns compute with actual demand and lowers cost without increasing runtime. Other options either add overhead or target the wrong bottleneck, such as shuffle partitioning or driver size, which are not indicated by uniformly low CPU usage.

Answer

Cache the Parquet dataset in memory before performing transformations.

Answer

Switch the job to use Photon and increase the driver node size.

Answer

Enable auto-optimize shuffle and set spark.sql.shuffle.partitions to a higher value.

Question 5

A data engineer is designing a Gold layer table that must support slowly changing dimension (SCD) Type 2 for a customer dimension. The source data arrives daily with updates to customer attributes. The engineer wants to implement this using Delta Lake. Which two features or techniques are essential for maintaining SCD Type 2? (Choose two.)

Accepted Answer

MERGE INTO with condition on business key and effective dates. SCD Type 2 requires a mechanism to expire old records and insert new ones, which is achieved with MERGE INTO. Additionally, the dimension table must include columns to track the validity period of each version, such as effective dates and a current flag. Time travel, generated columns, and partitioning are not essential for implementing SCD Type 2.

Answer

Using Delta Lake's generated columns for surrogate keys

Answer

Partitioning the dimension table by effective_start_date

Answer

Delta Lake time travel to query previous versions

Question 6

A data engineer is reviewing a Databricks job that runs on a job cluster and reads a large Delta table. The engineer notices that the job takes a long time to start because the cluster is provisioned from scratch each time. The engineer wants to reduce the startup time without increasing cost significantly. Which action should the engineer take?

Accepted Answer

Enable cluster pools and configure the job to use a pool.. Cluster pools keep a set of idle instances ready, so job clusters can start almost instantly by borrowing from the pool. This reduces startup time significantly. The other options either do not address startup time or could increase cost without solving the problem.

Answer

Use a larger driver node to speed up initialization.

Answer

Set the cluster's Spark configuration to enable adaptive query execution.

Answer

Increase the cluster's autoscaling maximum to allow more nodes.

Question 7

A data engineer manages a Delta table that is used for both batch analytics and frequent small updates from a streaming job. The table is not partitioned, and the engineer notices that queries are slowing down as the table grows. The engineer wants to improve query performance without changing the table schema or partitioning strategy. Which action should the engineer take?

Accepted Answer

Run OPTIMIZE with Z-ORDER on a commonly filtered column.. OPTIMIZE with Z-ORDER improves data skipping by clustering similar data together, which directly speeds up queries that filter on the Z-ORDERed column. It works without schema changes or partitioning and is compatible with frequent updates. Other options either do not address query performance or introduce trade-offs that harm Delta Lake capabilities.

Answer

Increase the cluster size used for all queries against the table.

Answer

Convert the table to a Parquet table and use partitioning by date.

Answer

Enable change data feed on the table and run VACUUM with a shorter retention period.

Question 8

An engineer is building a Gold-layer star schema for a sales analytics workload. The business wants to analyze revenue by product, by store, and by promotion independently, and also drill down through a hierarchy of region to country to city. Which dimensional modeling structure best supports these requirements?

Accepted Answer

A star schema with conformed dimensions that include hierarchical attributes for drill-down. Independent analysis by product, store, and promotion with hierarchical drill-down is the classic use case for a star schema built on conformed dimensions. Denormalized dimensions keep joins few, conformed dimensions keep definitions consistent across facts, and embedded hierarchy attributes like region, country, and city enable drill-down without additional tables.

Answer

A fully normalized third-normal-form schema with separate tables for each attribute level

Answer

A snowflake schema that normalizes each hierarchy level into its own dimension table

Answer

A single wide denormalized table that embeds all dimensional attributes directly in the fact rows

Question 9

A financial institution is building a Gold layer table that must support point-in-time queries to reconstruct account balances as of any past date. The source data includes transactions with effective dates and an audit log of changes. Which modeling technique is most appropriate?

Accepted Answer

Implement a Type 2 slowly changing dimension (SCD) with effective start and end dates.. A Type 2 SCD retains full history by adding new rows with effective date ranges, enabling accurate point-in-time queries. This is essential for financial data where past states must be reconstructable. Delta Lake time travel is limited by retention and not designed for continuous history. Type 1 and Type 3 SCDs lack the necessary historical depth.

Answer

Use Delta Lake time travel to query previous versions of the table.

Answer

Use a Type 1 slowly changing dimension (SCD) to overwrite old values.

Answer

Create a Type 3 slowly changing dimension (SCD) with previous value columns.

Question 10

A data engineer notices that a Databricks SQL warehouse used for executive dashboards runs 24/7 but is only actively queried during business hours. The warehouse is a Pro-sized warehouse with auto-stop set to 10 minutes. The team wants to reduce cost without affecting dashboard availability during business hours. Which action should the data engineer take?

Accepted Answer

Create a schedule to stop the warehouse outside business hours and start it before the business day begins.. The warehouse is billed for the time it is running, so the largest saving comes from eliminating the predictable idle window outside business hours. Scheduling a stop at the end of the day and a start before business hours ensures the warehouse is available when dashboards are used and incurs no compute cost overnight and on weekends. Auto-stop alone does not help if the warehouse is kept running or restarted by sporadic queries, and resizing or adding warehouses does not target the idle period.

Answer

Enable serverless compute for the warehouse and set the auto-stop to 1 minute.

Answer

Add a second warehouse for the executive dashboards and route queries to it only during business hours.

Answer

Change the warehouse to a Classic-sized warehouse and increase the auto-stop to 30 minutes.

Question 11

A data engineer is tuning a Spark job that reads from a Delta table and writes to another Delta table. The job uses a groupByKey operation followed by an aggregation. The engineer notices that the job is spilling to disk during the shuffle and taking a long time. The engineer wants to reduce shuffle spill and improve performance. Which action is most likely to help?

Accepted Answer

Replace groupByKey with reduceByKey or aggregateByKey to perform map-side aggregation.. Replacing groupByKey with reduceByKey or aggregateByKey enables map-side aggregation, which reduces the volume of data shuffled and thus reduces spill and improves performance. Other options either add overhead, do not address the root cause, or are costly workarounds that do not fix the fundamental inefficiency.

Answer

Increase the number of shuffle partitions to reduce the amount of data per partition.

Answer

Increase the executor memory and enable off-heap memory for the shuffle.

Answer

Set spark.sql.shuffle.partitions to a very high value and enable adaptive query execution.

Question 12

A data engineer is reviewing the cost of a Databricks job that runs on a daily basis. The job uses an all-purpose cluster that is manually started and stopped by the engineer. The job typically runs for 30 minutes, but the engineer often forgets to stop the cluster, leading to hours of idle time. Which action should the engineer take to reduce cost?

Accepted Answer

Use a job cluster instead of an all-purpose cluster for the job.. Job clusters are purpose-built for automated jobs and terminate automatically when the job completes, eliminating idle time. They are also cheaper than all-purpose clusters. This directly solves the problem of forgotten clusters and reduces cost, unlike auto-stop thresholds or scheduling, which are workarounds.

Answer

Increase the cluster's auto-stop threshold to 60 minutes to avoid premature termination.

Answer

Schedule the cluster to start and stop at specific times using a cron expression.

Answer

Configure the cluster to auto-stop after 30 minutes of inactivity.

Question 13

A data engineer is configuring a Unity Catalog external location to securely access data in an AWS S3 bucket. The engineer has already created an IAM role with the necessary permissions and configured the storage credential. Which additional step is required to allow Databricks to access the S3 bucket?

Accepted Answer

Create an external location that references the storage credential and the S3 bucket path, then grant appropriate privileges on the external location.. After creating a storage credential, you must create an external location that maps the credential to a specific S3 path. Then, you grant privileges on that external location to allow access. This is the standard Unity Catalog workflow for external data. The other options describe incorrect or legacy methods.

Answer

Generate a personal access token (PAT) for the IAM role and store it in Databricks secrets.

Answer

Configure the S3 bucket policy to allow access from the Databricks control plane's IP addresses.

Answer

Attach the IAM role directly to the Databricks workspace's EC2 instances so they can assume the role.

Question 14

A Data Engineer is tasked with cleaning a dataset in Databricks. The dataset contains a column 'phone_number' with various formats, including parentheses, dashes, and spaces. The engineer needs to standardize all phone numbers to a digits-only format (e.g., '1234567890'). Which approach is most efficient and scalable?

Accepted Answer

Use the regexp_replace() function in Spark SQL to remove all non-digit characters.. regexp_replace() is a built-in, distributed function that efficiently removes all non-digit characters in one step. It leverages Spark's optimized execution engine and is the most scalable and straightforward method for this cleansing task. Other options involve less efficient UDFs, incomplete character replacement, or complex splitting logic.

Answer

Use a Python UDF with the re.sub() method to strip non-digit characters.

Answer

Use the translate() function to replace parentheses and dashes with empty strings.

Answer

Use the split() function to split on non-digit characters and then concatenate the parts.

Question 15

An engineer needs to optimize a massive table that is frequently joined with other large tables. Which strategy is most effective for performance?

Accepted Answer

Apply Z-Ordering to the join keys.. For large-scale joins, the most effective strategy is to Z-Order the join keys. This ensures that records with the same join keys are physically stored together, allowing the Databricks engine to use merge joins rather than costly shuffle-heavy hash joins. By minimizing data movement across the cluster during the shuffle phase, Z-Ordering on join keys significantly reduces execution time and resource consumption for complex, large-scale join operations.

Answer

Increase the number of partitions to spread the data across more nodes.

Answer

Cast all join keys to strings to ensure data type compatibility.

Answer

Use a broadcast join for all large-table join operations.