Databricks-DA-Assoc Practice Test 9 — 10 Questions

Question 1

When designing a star schema in Databricks SQL, why is it recommended to use Delta Lake for both Fact and Dimension tables?

Accepted Answer

Delta Lake supports ACID transactions, which are necessary for reliable SCD updates.. Delta Lake provides ACID compliance and time travel, which are critical for maintaining the integrity of star schemas. Analytical workloads often rely on SCD (Slowly Changing Dimension) updates and complex joins. By using Delta Lake, you ensure that dimension updates are consistent and atomic, preventing users from seeing partial updates or corrupted data states, which is essential for accurate business intelligence reporting and historical analysis.

Answer

It forces the use of Star Schema optimization, which is only supported for Delta format.

Answer

Delta Lake automatically reorders dimension tables to improve join performance.

Answer

It is required to store dimensions in the same schema as fact tables for performance.

Question 2

Refer to the exhibit. This IAM policy is attached to a Storage Credential. A user tries to run 'DROP TABLE' on a table stored in this bucket and fails. Why?

Accepted Answer

The IAM policy does not include 's3:DeleteObject'.. The IAM policy only grants 's3:GetObject' and 's3:ListBucket', which are read-only permissions. The 'DROP TABLE' command in Databricks requires 's3:DeleteObject' permission on the underlying data to remove the files from S3. Because the policy lacks delete permissions, the operation is blocked by the cloud provider, even if the user has the 'DROP' privilege in Databricks.

Answer

The user lacks the 'DROP' privilege in Unity Catalog.

Answer

The bucket is marked as 'Read Only' in Unity Catalog.

Answer

The table is owned by the metastore admin.

Question 3

A data analyst is using Databricks SQL to query a large Delta table. The query is performing slowly because it must scan the entire table to retrieve data for a specific date range. Which action should the analyst take to optimize query performance?

Accepted Answer

Execute an OPTIMIZE command with ZORDER BY on the relevant date columns.. Implementing Z-Ordering on the partition columns or columns frequently used in WHERE clauses significantly enhances data skipping capabilities. By physically organizing data on disk based on these values, Databricks SQL engine can skip irrelevant files during query execution. This optimization is critical in Databricks SQL environments because it reduces I/O overhead, lowers latency for end-user dashboards, and minimizes the computational resources required to process large-scale analytical workloads efficiently.

Answer

Convert the table format from Delta to Parquet to improve raw read throughput.

Answer

Increase the number of clusters in the SQL Warehouse to enable higher concurrency.

Answer

Use the CACHE SELECT statement on the entire table to force all data into memory.

Question 4

An analyst notices that a dashboard visualization is displaying an incorrect aggregation of total revenue. After reviewing the SQL, the analyst confirms the query logic is correct. What is the most likely cause of this discrepancy in the rendered visualization?

Accepted Answer

The visualization configuration in the dashboard editor has an unintended aggregation setting.. Visualizations in Databricks SQL handle data formatting and aggregation settings independently of the raw SQL output. Sometimes, the 'Plot' settings within the visualization editor define a different aggregation (like 'Average' instead of 'Sum') or apply data formatting that masks the true value. Checking these visualization-specific configurations is the final step in troubleshooting data accuracy issues within the dashboarding workflow.

Answer

The SQL warehouse is currently running in 'Serverless' mode.

Answer

The Unity Catalog metastore is failing to update the table statistics.

Answer

The dashboard is using a legacy version of the Databricks SQL visualizer.

Question 5

An analyst is optimizing a dashboard that queries a Delta table partitioned by 'event_date'. The query filters on 'event_date' and 'user_id', but is still slow. What is the most effective next step to improve query speed?

Accepted Answer

Enable Liquid Clustering on the 'user_id' column.. Partitioning by date is good for coarse pruning, but if 'user_id' is the primary filter, the engine still scans all files within the selected dates. Z-Ordering by 'user_id' creates data skipping metadata that allows the engine to skip files not containing the specific user, drastically reducing data read volume. This is a critical optimization for high-cardinality columns used in frequent point-lookups.

Answer

Change the table format to Parquet to improve scan performance.

Answer

Increase the cluster's driver node memory.

Answer

Rewrite the query to use a subquery instead of a JOIN.

Question 6

What is the purpose of the 'Account Admin' role in Databricks?

Accepted Answer

To configure metastores, identities, and settings.. The Account Admin role is the highest level of privilege in Databricks, providing control over the entire account including metastore creation, user provisioning, and billing. This role is responsible for the foundational security configuration of the environment. Because of its power, this role should be restricted to a very small number of individuals to prevent unauthorized changes to security or governance policies.

Answer

To manage SQL queries for a specific user.

Answer

To monitor the performance of all SQL Warehouses.

Answer

To create tables in the metastore.

Question 7

Refer to the exhibit. A data analyst attempts to run a query in the SQL editor but receives the error displayed. Which action should the administrator take to resolve this?

Accepted Answer

Grant the user the 'CAN_USE' permission on the specific SQL Warehouse.. The error indicates a lack of the necessary access control privilege for the specific SQL Warehouse. In Databricks, permissions are managed via Access Control Lists (ACLs). The analyst requires the 'CAN_USE' permission to attach to and execute queries on a warehouse. Administrators must update the warehouse's permission settings in the Databricks workspace to grant this access level to the user or their associated group.

Answer

Restart the SQL Warehouse to refresh the user's session token.

Answer

Upgrade the user's workspace role to 'Workspace Admin'.

Answer

Increase the maximum number of clusters in the SQL Warehouse configuration.

Question 8

Which component of the Databricks Data Intelligence Platform allows users to discover, govern, and share data across the entire organization?

Accepted Answer

Unity Catalog. Unity Catalog is the centralized governance solution for data, analytics, and AI on the Databricks platform. It provides a single interface to manage permissions, track data lineage, and discover assets. Mastery of Unity Catalog is essential for any data analyst as it ensures that data access is secure, compliant, and transparent, effectively bridging the gap between raw storage and meaningful, governed business insights for all users.

Answer

Databricks SQL

Answer

Delta Live Tables

Answer

Compute Clusters

Question 9

Which Databricks feature provides a detailed, lineage-based view of how data flows from source to destination?

Accepted Answer

Unity Catalog Data Lineage. Data lineage in Unity Catalog tracks the relationships between data assets, transformations, and end-users. This visibility is essential for impact analysis, debugging, and compliance. Understanding lineage helps analysts understand the origin of their data and the downstream effects of any changes they make, ensuring that business stakeholders always have confidence in the data's quality, provenance, and the transformation logic applied throughout the analytical pipeline.

Answer

Databricks SQL Audit Logs

Answer

Delta Live Tables Pipeline Monitoring

Answer

Cluster Event Logs

Question 10

A data analyst needs to ingest a large volume of CSV files from an external S3 bucket into a Delta table. Which method provides the most efficient, fault-tolerant, and incremental loading approach in Databricks?

Accepted Answer

Implement Auto Loader using cloudFiles source.. Auto Loader is specifically designed to process files as they arrive in cloud storage. It maintains state information in a checkpoint location, ensuring that only new or modified files are ingested during subsequent runs. This pattern is critical for production pipelines where data volume scales over time, as it avoids full scans of the source directory, significantly reducing latency and compute costs while ensuring idempotent processing for reliability.

Answer

Use the COPY INTO command with a static file path.

Answer

Manually read files into a DataFrame and use write.mode('append').

Answer

Use the Databricks SQL 'Import' wizard via the UI.