Databricks-DA-Assoc Practice Test 20 — 15 Questions

Question 1

What is the primary function of the 'VACUUM' command in Databricks SQL data modeling?

Accepted Answer

To permanently remove files no longer referenced by the table state.. The VACUUM command is essential for storage management and compliance. It removes old, unreferenced data files that are no longer part of the current table state beyond a specified retention period. This is vital for managing cloud storage costs and ensuring that data is physically purged, which helps companies meet regulatory requirements like GDPR by permanently deleting sensitive data that is no longer needed in the historical logs.

Answer

To shrink the transaction log file size.

Answer

To re-index the table for faster performance.

Answer

To force a merge of all Delta logs into a single Parquet file.

Question 2

Which of the following describes the purpose of 'Data Skipping' in Databricks SQL when querying Delta tables?

Accepted Answer

It uses min/max statistics to skip irrelevant files. Data Skipping is a performance optimization where the Delta engine uses file-level statistics (min/max values) to prune files that do not contain data relevant to the query's filter conditions. By avoiding the I/O of reading irrelevant data files, the engine significantly speeds up queries. This is a primary benefit of the Delta format and is automatically handled by the engine during the planning phase of a SQL query.

Answer

It deletes old data files to save storage space

Answer

It caches frequently used tables in the browser

Answer

It allows queries to run without reading any files

Question 3

Which TWO of the following scenarios are valid use cases for using the COPY INTO command? (Choose two)

Accepted Answer

Ingesting data that arrives in files at unpredictable, irregular intervals.. COPY INTO is a powerful, idempotent command for loading data into Delta tables. It is best suited for scenarios where you need to ingest files from cloud storage periodically without setting up complex streaming infrastructure. Understanding when to use COPY INTO versus Auto Loader helps analysts choose the right balance between simplicity and advanced streaming capabilities for their specific data engineering requirements.

Answer

Implementing a continuous streaming pipeline with sub-second latency.

Answer

Performing complex, multi-stage ELT pipeline orchestration.

Answer

Ingesting historical data from a database using JDBC connectors.

Question 4

An analyst opens the Databricks SQL editor and wants to browse the tables available in the workspace's Unity Catalog metastore before writing a query. Which workspace object should the analyst use to navigate catalogs, schemas, and tables?

Accepted Answer

The Data Explorer in the Databricks workspace. The Data Explorer is the built-in workspace UI for browsing Unity Catalog objects, letting analysts navigate catalogs and schemas, inspect table metadata, and preview data. Before writing SQL, an analyst can confirm table names and columns there. The other listed surfaces handle Git integration, compute configuration, or job monitoring, none of which expose the metastore hierarchy needed for table discovery.

Answer

The Cluster configuration page

Answer

The Repos folder in the workspace sidebar

Answer

The Jobs run history UI

Question 5

A data analyst notices that a query involving a large join between two tables is consistently slow. The analyst suspects that one of the tables is significantly skewed. Which tool in the Databricks SQL query profile is most effective for confirming this skew?

Accepted Answer

The SQL Query Profile 'Metrics' tab.. The Query Profile provides visual metrics to identify bottlenecks. By examining the 'Max vs Median' row count metrics per task, an analyst can see if specific executors are processing significantly more data than others, indicating skew. Understanding data distribution is critical in Databricks, as skew often leads to 'straggler' tasks that delay entire query execution, regardless of overall cluster size or compute power.

Answer

The Query History list view.

Answer

The Cluster Usage dashboard.

Answer

The Table details metadata panel.

Question 6

A data analyst runs a query in Databricks SQL that joins a large fact table to a small dimension table. The query spills to disk and is slow. The analyst adds a /*+ BROADCAST(dim) */ hint to the query. What is the primary effect of this hint on the query execution?

Accepted Answer

It forces the optimizer to broadcast the dimension table to all executor nodes, avoiding a shuffle of the large fact table.. The BROADCAST hint tells the optimizer to replicate the small dimension table to all executors, so the join with the large fact table can be done locally without shuffling the fact table. This reduces network traffic and avoids disk spills, making the query faster. The hint is effective when the broadcasted table is small enough to fit in memory.

Answer

It caches the dimension table in Delta cache to speed up subsequent reads.

Answer

It partitions the fact table by the join key before the join to co-locate matching rows.

Answer

It sorts both tables by the join key before merging them, ensuring a sort-merge join.

Question 7

A data analyst is working with a Delta table that contains a column 'status' with values 'active', 'inactive', and 'pending'. The analyst wants to enforce that only these three values can be inserted or updated. Which Databricks SQL feature should the analyst use?

Accepted Answer

CHECK constraint on the status column.. A CHECK constraint is the appropriate feature to enforce that the status column only contains a specific set of values. It is evaluated on insert and update, rejecting rows that violate the condition. This ensures data integrity directly in the table definition. Other options like NOT NULL, generated columns, or column masks do not restrict the domain of values for a column.

Answer

Column mask that filters out invalid statuses.

Answer

NOT NULL constraint on the status column.

Answer

Generated column that maps status to an integer.

Question 8

Which THREE actions are required to successfully secure an external location in Unity Catalog?

Accepted Answer

Create a Storage Credential in Databricks.. Securing an external location in Unity Catalog involves creating a Storage Credential, linking that credential to an External Location, and granting specific permissions on that location. This layered approach ensures that access is controlled at both the cloud identity level and the Databricks governance level, allowing administrators to audit all access to external storage paths through Unity Catalog logs.

Answer

Manually update the cluster's Spark configuration.

Answer

Provide all users the 'Owner' role on the metastore.

Question 9

Which DDL statement is used to remove a table's data and metadata permanently from the catalog?

Accepted Answer

DROP TABLE table_name. The DROP TABLE command is the definitive way to delete both the table metadata and the underlying data files. In a production environment, this is a destructive action that requires careful consideration. Knowing the difference between TRUNCATE (which clears data but keeps the schema) and DROP (which removes everything) is critical for maintaining data integrity and preventing the accidental loss of important organizational assets.

Answer

TRUNCATE TABLE table_name

Answer

DELETE FROM table_name

Answer

REMOVE TABLE table_name

Question 10

A data analyst runs the following command in a Databricks SQL editor connected to a Unity Catalog workspace:

```sql
DROP TABLE IF EXISTS analytics.events.raw_clicks;
```

What is the result of this statement if `analytics.events.raw_clicks` is a managed Delta table?

Accepted Answer

The table metadata, data files, and any associated directories are permanently removed from the metastore and cloud storage.. Dropping a managed Delta table in Unity Catalog removes both the catalog metadata and the underlying data files because Databricks owns the data lifecycle. External tables behave differently: their files persist. Analysts must therefore treat DROP on a managed table as destructive and irreversible through normal query tools, which is why knowing the table type before running DDL matters.

Answer

The table is renamed to a recycle-bin schema and can be restored by any workspace user within 30 days.

Answer

The command fails because Databricks SQL does not permit dropping tables that other users may be querying.

Answer

Only the table metadata is removed; the underlying Parquet data files remain in cloud storage for later recovery.

Question 11

Which TWO actions should an analyst take to improve the accuracy of an AI/BI Genie Space when users report that it frequently hallucinates metric definitions?

Accepted Answer

Provide clear, concise system instructions that define key metrics.. Improving AI/BI Genie accuracy involves a dual approach: tightening the instruction set and refining the data layer. By providing specific, clear instructions, the analyst reduces ambiguity. Simultaneously, creating curated views in Unity Catalog enforces a 'source of truth,' limiting the model's ability to interpret ambiguous raw columns. These steps collectively ground the LLM, ensuring that it generates reliable SQL based on verified organizational definitions rather than probabilistic guesses.

Answer

Increase the number of tables available to the Genie Space.

Answer

Enable 'auto-discovery' of all columns in the Unity Catalog tables.

Answer

Use a higher temperature setting to allow for more flexible interpretations.

Question 12

An analyst has a Databricks SQL dashboard with a table visualization showing order details, including an order_date column. A stakeholder wants to see only orders from the last 30 days but also wants the ability to change the date range without editing the query. The analyst adds a date-range parameter to the query and a parameter widget to the dashboard. After publishing, the stakeholder reports that changing the parameter does not affect the table. Which action should the analyst take to resolve this?

Accepted Answer

Verify that the parameter is referenced in the query's WHERE clause using the correct parameter syntax and that the query was saved and the dashboard refreshed after the change.. For a parameter to affect a visualization, the query must reference it, typically in a WHERE clause, and the dashboard must reflect the saved query version. If the parameter is defined but unused, or the dashboard was not refreshed after saving, changing the widget value will not alter results. Verifying the reference and refreshing addresses the most common causes of this symptom.

Answer

Change the parameter's data type from DATE to STRING so that the date-range selection is passed correctly to the query.

Answer

Recreate the table visualization from scratch and re-add the parameter widget, because parameters only apply to newly created visualizations.

Answer

Convert the parameter to a dashboard-level filter by adding the order_date field to the dashboard filters and removing the parameter widget.

Question 13

What is the primary benefit of using a 'Materialized View' in Databricks SQL for frequent, expensive queries?

Accepted Answer

It stores precomputed results to improve read performance.. Materialized views precompute query results, allowing for faster read performance by storing the output rather than recalculating it on every request. This is critical for data analysts working with large, complex datasets, as it significantly reduces latency and compute costs for repetitive dashboards or reports. By shifting the workload from query time to refresh time, analysts can provide consistent and fast performance for their business stakeholders.

Answer

It bypasses the need for Unity Catalog permissions.

Answer

It automatically scales the cluster to infinite size.

Answer

It eliminates the need for any data partitioning.

Question 14

A data analyst is configuring an AI/BI Genie Space to allow business users to query sales data. The analyst needs to ensure the Space uses the most performant and governed data source available in the Unity Catalog. Which configuration should the analyst prioritize?

Accepted Answer

Configure the Genie Space to use a certified, high-performance Delta table registered in Unity Catalog.. Genie Spaces rely on Delta tables registered in Unity Catalog to provide natural language insights. By pointing the Genie Space to a curated, high-quality Delta table rather than a raw landing zone table, the analyst ensures that the AI model produces accurate, governed results. This setup is crucial for maintaining data integrity and performance while enabling self-service analytics for non-technical stakeholders who interact with the Genie interface.

Answer

Link the Genie Space directly to a JSON file located in an external cloud storage bucket.

Answer

Upload a static CSV file containing sales history directly into the Genie Space interface.

Answer

Point the Genie Space to a temporary View that contains no documented column descriptions or metadata.

Question 15

An analyst needs to display a comparison between actual and target sales figures in a dashboard. The data exists in two separate tables. Which approach is best to display this comparison in a single visualization?

Accepted Answer

Use a JOIN operation in the SQL query to combine the tables before visualization.. Combining data from disparate tables within the SQL layer is the recommended best practice for dashboarding in Databricks. By performing a JOIN within the SQL query, the analyst delivers a pre-aggregated, clean dataset to the visualization engine. This approach simplifies the visualization setup, minimizes the processing load on the visualizer, and ensures high performance, which is critical for complex comparative analyses in business intelligence.

Answer

Create two separate visualizations and place them side-by-side on the dashboard.

Answer

Use the visualization's 'secondary data source' feature to link the tables.

Answer

Import both tables into the dashboard's local memory and filter them.