Databricks-DA-Assoc Practice Test 11 — 15 Questions

Question 1

An organization requires that certain sensitive columns be removed from a table for specific groups of users. Which Databricks feature should be used to enforce this at the data modeling level?

Accepted Answer

Use Delta Lake dynamic masking.. Dynamic Data Masking and Row-Level Security are the preferred methods for controlling access to sensitive data within Databricks SQL. By using SQL functions to define masking policies, administrators can ensure that users only see the data they are authorized to view. This approach keeps the underlying data intact while providing secure, role-based access control, ensuring compliance with data privacy regulations without duplicating data for different security profiles.

Answer

Create separate physical tables for each security level.

Answer

Change the file format to JSON to strip sensitive fields.

Answer

Hard-code the filtering logic in every user's SQL query.

Question 2

You are modeling a table where users need to query based on a 'user_id' but also need to perform historical point-in-time analysis. Which feature is most appropriate?

Accepted Answer

Use Delta Time Travel.. Time Travel is a native feature of Delta Lake that allows users to query previous versions of a table using a timestamp or version number. This is crucial for point-in-time analysis, as it lets analysts reproduce reports or audit changes without needing to maintain manual snapshots or separate history tables. It leverages the transaction log to provide a consistent, historical view of the data efficiently.

Answer

Implement a materialized view with a static snapshot.

Answer

Create a new table for every daily load.

Answer

Store all historical records in a single table with an 'is_current' flag.

Question 3

An analyst is optimizing a query that performs multiple aggregations on a Delta table. Which TWO actions can the analyst take to improve query performance via the SQL editor?

Accepted Answer

Use Z-Ordering on columns frequently used in WHERE filters.. Improving performance requires reducing the amount of data read. Using partition pruning allows the engine to skip irrelevant files, while Z-Ordering improves data skipping by clustering related information together. These techniques are fundamental for Databricks SQL performance, as they minimize I/O overhead. Mastering these adjustments allows analysts to write highly efficient code that scales effectively with growing datasets, reducing both latency and costs for the organization.

Answer

Increase the cluster size to 'Large' for every query execution.

Answer

Force a Broadcast Join for all small tables.

Answer

Convert all tables to Parquet format.

Question 4

A data analyst is designing a star schema in Databricks SQL to optimize query performance for a large sales dataset. Which strategy most effectively minimizes data shuffling during join operations between a large fact table and a small dimension table?

Accepted Answer

Leverage the BROADCAST join hint on the dimension table.. Utilizing the broadcast join strategy is essential when joining a massive fact table with a significantly smaller dimension table. By distributing the small table to all worker nodes, Databricks eliminates the need for expensive network shuffles of the fact table rows. This approach is fundamental for maintaining low latency in BI dashboards where users require sub-second query responses on complex star schema structures.

Answer

Apply the CLUSTER BY clause on the primary key of the fact table.

Answer

Use the Z-ORDER BY clause on the dimension table's primary key.

Answer

Convert the fact table into a temporary view before joining.

Question 5

When designing a table to support frequent 'MERGE' operations, which data modeling practice will lead to the best performance?

Accepted Answer

Use Z-Ordering on the join keys.. MERGE operations are expensive because they involve reading, joining, and rewriting data. To optimize this, the target table should be well-organized using partitioning and Z-Ordering or Liquid Clustering. This allows the MERGE operation to perform 'data skipping', only loading the relevant files into memory during the join and update process. Without these optimizations, the engine must perform a full table scan for every single merge statement, leading to significant latency.

Answer

Store the data in a CSV file format.

Answer

Disable the Delta transaction log.

Answer

Ensure the target table has no indexes or clustering.

Question 6

A Data Analyst is configuring an AI/BI Genie Space to allow business users to query sales data. The analyst needs to ensure the model understands specific business logic, such as how to calculate 'Net Revenue' from raw columns. Where should the analyst define this logic to ensure Genie consistently applies these business rules across all user queries?

Accepted Answer

Define the logic in the SQL view metadata or table comments within Unity Catalog.. Defining business logic in the underlying Databricks SQL Warehouse or Unity Catalog perspective is the most reliable way to guide Genie. By providing clear descriptions and semantic definitions within the catalog or view metadata, the LLM-driven engine can correctly interpret business terminology. This ensures that non-technical users receive consistent, accurate results without needing to write complex SQL, directly impacting the trust and adoption of the AI/BI solution within the organization.

Answer

Add the logic as a comment in the user's prompt history.

Answer

Hardcode the business logic into the Genie Space configuration UI.

Answer

Require users to provide the formula in every natural language query.

Question 7

Which THREE of the following are benefits of using the Delta Lake format in Databricks SQL?

Accepted Answer

ACID transactions for data integrity. Delta Lake provides the foundation for reliable, performant SQL querying in Databricks. Key benefits include ACID transactions, which prevent data corruption during concurrent operations; time travel, which allows analysts to query previous versions of data; and data skipping/compaction, which drastically improve query performance. Together, these features enable reliable data warehousing and complex analytics at scale, which are the main reasons why it is the standard format in Databricks SQL environments.

Answer

The ability to run queries without a cluster

Answer

Automatic conversion of all queries to Python

Question 8

A data analyst runs COPY INTO to load new CSV files from an S3 path into a Delta table every night. One night the job completes successfully but the row count does not change, even though new files were placed in the source path. The files have the same names as files loaded previously. What is the most likely cause?

Accepted Answer

COPY INTO skips files whose paths were already loaded unless the load is forced or the files are modified.. COPY INTO is idempotent by design: it records the paths it has loaded and skips them on subsequent runs unless the files are new or the load is explicitly forced. Because the new files reused prior names, their paths matched the load history, so no rows were added despite a successful job status.

Answer

The CSV files were written with a different compression codec, so COPY INTO could not parse them.

Answer

The Delta table's transaction log has reached its retention limit and stopped accepting new commits.

Answer

Auto Loader had already claimed the files, so COPY INTO is blocked from reading them.

Question 9

An analyst is setting up a Genie Space to handle financial reporting. They want the model to always group results by 'fiscal_quarter'. How should they enforce this requirement?

Accepted Answer

Add a constraint to the system instructions.. Forcing specific groupings is best achieved through system instructions in the Genie Space configuration. By explicitly stating 'Always group by fiscal_quarter for all time-based queries,' the analyst provides a mandatory rule for the model. This guarantees that every time-series query generated by the AI aligns with the company's fiscal calendar, preventing the model from defaulting to calendar months and ensuring reporting consistency across the organization.

Answer

Update the default table sort order in the catalog.

Answer

Force users to type 'group by fiscal_quarter' in their prompts.

Answer

Create a view that excludes the 'date' column.

Question 10

An organization stores sensitive financial records in a Unity Catalog managed Delta table stored in cloud object storage. To comply with corporate security mandates, all data at rest must be encrypted using a customer-managed encryption key rather than the default cloud provider-managed keys. Where must the administrator configure this encryption setting?

Accepted Answer

Within the Unity Catalog metastore root storage configuration using a cloud-provider storage credential linked to a customer-managed key.. Customer-managed keys (CMK) for managed storage in Unity Catalog are configured at the storage credential or metastore level when establishing the root storage location. This ensures that all managed tables and volumes created within that storage root automatically leverage the specified encryption key, enforcing enterprise compliance policies transparently across all downstream data assets and analytics workloads.

Answer

Inside the Databricks notebook before executing any SQL queries against the sensitive financial Delta table.

Answer

By modifying the Spark session configuration spark.databricks.io.encryption.enabled to true in the cluster policy.

Answer

Through the Apache Spark dataframe write option by setting the encryption parameter to customer_managed prior to saving.

Question 11

Refer to the exhibit. The 'sales_data' table is growing rapidly. You notice queries filtering by 'event_date' are fast, but queries filtering by 'id' are slow. What is the most effective data modeling change to optimize for 'id' lookups?

Accepted Answer

Use Z-Ordering or Liquid Clustering on the 'id' column.. The table is currently partitioned by 'event_date', which creates a folder structure that only aids queries filtering on dates. Partitioning by a high-cardinality column like 'id' is a major anti-pattern as it leads to the 'small file problem'. Instead, the analyst should retain the partitioning on 'event_date' but apply Z-Ordering or Liquid Clustering on the 'id' column to facilitate efficient data skipping without creating excessive directory partitions.

Answer

Re-partition the table by 'id' instead of 'event_date'.

Answer

Create a secondary table for 'id' lookups that is partitioned by 'id'.

Answer

Change the table type to a standard Parquet table without partitioning.

Question 12

A data analyst needs to optimize query performance for a large sales table that is frequently filtered by 'region_id'. Which physical data modeling strategy should be implemented to minimize data scanning?

Accepted Answer

Apply Z-Ordering on the region_id column.. Z-Ordering is a technique to co-locate related information in the same set of files, significantly reducing the amount of data read during filter operations. By applying Z-Ordering on the 'region_id' column, the Databricks engine can skip irrelevant files more effectively during query execution. This strategy is essential for large-scale datasets where traditional partitioning alone may lead to excessive file fragmentation or suboptimal data distribution across the cluster nodes.

Answer

Implement a primary key constraint on the region_id column.

Answer

Execute the ANALYZE TABLE command periodically without any data clustering.

Answer

Change the file format to CSV to allow easier manual partitioning.

Question 13

A data analyst is running a complex aggregate query in Databricks SQL that frequently times out before finishing. The underlying Delta table contains hundreds of gigabytes of historical log data partitioned by date. What is the most effective Databricks SQL query optimization technique to apply directly within the SQL statement?

Accepted Answer

Include a explicit WHERE clause filtering on the date partition column to leverage partition pruning.. Applying a partition filter using the date column allows Databricks SQL to prune irrelevant files instantly, significantly reducing the data scan volume and preventing timeouts. This practice ensures queries execute efficiently over large historical datasets without needing constant infrastructure scaling or manual intervention.

Answer

Increase the overall timeout limit within the cluster configuration settings.

Answer

Convert the Delta table format into a standard Parquet format inside an external Hive metastore.

Answer

Wrap the main aggregation query inside a recursive common table expression.

Question 14

An analyst is preparing a dashboard and needs to ensure that the queries powering it are as performant as possible. Which THREE techniques should the analyst use to optimize these SQL queries in Databricks?

Accepted Answer

Regularly run OPTIMIZE on the underlying tables. Optimizing dashboards requires a combination of efficient data layout, metadata management, and query-level tuning. Using the 'OPTIMIZE' command keeps files consolidated, while 'ANALYZE' provides the CBO with the statistics it needs for optimal plan selection. Finally, using 'Z-Ordering' on high-cardinality columns frequently appearing in filters or joins ensures the query engine can skip large volumes of irrelevant data, directly leading to faster dashboard response times.

Answer

Always use SELECT * to fetch all available columns

Answer

Convert all Delta tables to CSV format for speed

Question 15

Which THREE of the following are valid methods to optimize the performance of a slow-running SQL query in Databricks?

Accepted Answer

Using Z-Ordering on high-cardinality columns.. Performance optimization in Databricks involves a combination of data organization, compute resource management, and query plan refinement. By using techniques like Z-Ordering, partitioning, and leveraging the cost-based optimizer, analysts can significantly reduce query latency. These methods are essential for managing large-scale datasets, ensuring that compute resources are utilized effectively, and providing timely insights to business stakeholders who rely on the performance of analytical dashboards and reports.

Answer

Manually rewriting every query to use nested subqueries.

Answer

Increasing the query timeout setting to 24 hours.