Databricks-DA-Assoc Practice Test 10 — 10 Questions

Question 1

An analyst is performing a join between a large fact table and a small dimension table. To ensure optimal performance in Databricks SQL, which join type is preferred when the dimension table fits in memory?

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

Accepted Answer

Broadcast hash join. When one side of a join is significantly smaller than the other, the engine can use a broadcast join. By sending the smaller table to all worker nodes, the engine avoids a costly shuffle of the larger fact table. This is the most efficient way to perform joins in a distributed environment, as it minimizes network traffic and accelerates data processing speeds significantly.

Answer

Sort-merge join

Answer

Cartesian join

Answer

Full outer join

Question 2

Which THREE of the following are valid sources for importing data into a Databricks Delta table? (Choose three)

Accepted Answer

S3, ADLS, and GCS cloud object storage.. Databricks provides flexible connectivity to ingest data from diverse sources into the Lakehouse. Whether the data is in cloud storage, a relational database, or a streaming message broker, the platform offers optimized connectors to load this information into Delta format. Mastering these integration points allows analysts to architect robust data pipelines that can pull information from virtually any enterprise system for unified analysis and reporting within a single environment.

Answer

Local files stored on the Databricks Driver node.

Answer

Direct memory buffers from the user's browser.

Question 3

An analyst is reviewing a slow query and identifies that the 'FileScan' stage is taking most of the time. Which THREE factors could be causing this inefficiency?

Accepted Answer

The table contains a high number of small files.. Slow FileScans usually point to I/O-related issues. If too many small files are present, the metadata overhead becomes significant. Without proper pruning, the system scans unnecessary data. Z-Ordering or partitioning issues cause the engine to read more data than required. Addressing these factors is vital for analysts, as they directly impact the 'Data Skipping' efficiency of the Delta Lake engine, which is the cornerstone of high-performance analytics in Databricks.

Answer

The query uses a cross join.

Answer

The query is using a User-Defined Function (UDF).

Question 4

Which capability is provided by Databricks SQL 'Alerts' when a threshold is triggered?

Accepted Answer

Sending notifications via integrated channels.. Alerts in Databricks SQL are designed to monitor specific query results and trigger notifications based on defined conditions. When the threshold is met, the system can send notifications via email or external integrations like Slack or Webhooks. This functionality is vital for business monitoring, enabling teams to respond quickly to changes in data, such as sudden drops in performance or reaching critical business KPIs without manual intervention.

Answer

Automatically restarting the SQL Warehouse.

Answer

Rolling back the last data transaction.

Answer

Purging the SQL result cache.

Question 5

An analyst wants to publish a dashboard for the executive team. The dashboard contains sensitive data that should only be accessible to specific users. What is the correct way to handle access control?

Accepted Answer

Use the 'Share' button to grant 'Can View' access to authorized groups.. Managing dashboard permissions via the Workspace sharing settings is the standard approach to secure sensitive data. By assigning 'Can View' or 'Can Edit' roles based on user groups, the analyst ensures that only authorized individuals see the dashboard. This is a fundamental skill for data governance, ensuring that insights remain secure and compliant while still being available to the relevant stakeholders within the organization.

Answer

Hard-code the security logic into the SQL queries.

Answer

Export the dashboard as a PDF and email it to the team.

Answer

Make the dashboard public and add a password to the title.

Question 6

Refer to the exhibit. Why might the analyst choose to create a new table with 'AS SELECT *' instead of just running OPTIMIZE on the original table?

Accepted Answer

It provides a cleaner way to reorganize data and apply Z-Ordering. Creating a new table via CTAS (Create Table As Select) allows the analyst to re-partition or re-cluster the data from scratch, which is often faster and cleaner than reorganizing an existing, fragmented table. This process also allows for the application of Z-Ordering during the initial creation, ensuring that the new table has the optimal data layout from the first day of its existence.

Answer

The original table is read-only and cannot be modified

Answer

It is the only way to delete old data from the table

Answer

CTAS automatically creates indexes on the table

Question 7

Which visualization type is most appropriate for showing the distribution of a single continuous variable, such as the spread of customer ages?

Accepted Answer

Histogram. A histogram is the standard tool for visualizing the distribution of numerical data. It groups continuous values into ranges, or 'bins', and displays the frequency of observations in each bin. This allows analysts to quickly identify the shape of the data, such as whether it is normally distributed, skewed, or contains outliers, which is foundational for exploratory data analysis and effective business reporting.

Answer

Pie Chart

Answer

Line Chart

Answer

Scatter Plot

Question 8

Which component of an AI/BI Genie space is responsible for defining the scope of data available to a user and ensuring that the natural language model only references authorized tables?

Accepted Answer

The Genie space instructions and metadata definition. Genie spaces rely on the definition of data instructions and schema mappings within the space configuration. By explicitly linking specific tables and columns, the system enforces access control and ensures the LLM does not hallucinate using external data. This metadata-driven approach is critical for maintaining data governance and security while enabling self-service analytics for business users within the Databricks ecosystem.

Answer

The Unity Catalog Data Explorer integration

Answer

The Databricks SQL Warehouse access policy

Answer

The workspace-level workspace AI configuration

Question 9

An analyst notices that the AI/BI Genie Space is generating queries that take too long to execute. The underlying data is stored in partitioned tables. What is the most effective way to help the Genie generate more performant SQL?

Accepted Answer

Modify the system instructions to require filtering on partition keys.. Genie models are highly responsive to context. By adding explicit instructions about filtering on partition columns, such as 'always filter by date or region,' the model learns to include these constraints in its generated SQL. This ensures that the generated queries prune data partitions effectively, significantly reducing query runtime and resource consumption on the SQL Warehouse, which directly improves the end-user experience for all business users.

Answer

Instruct the model to use 'SELECT *' to get all data at once.

Answer

Increase the SQL Warehouse cluster size to handle larger queries.

Answer

Use a different LLM model within the Genie settings.

Question 10

You are auditing access to several AI/BI Genie Spaces. You discover that a group of users can ask questions but cannot see the underlying source tables in the Catalog Explorer. Is this a functional issue?

Accepted Answer

No, this is a secure configuration that follows the principle of least privilege.. This is expected behavior and a sound security practice. Genie acts as an intermediary; users do not need to be able to browse or query the source tables directly to use the Genie interface. By decoupling the interface from direct data access, you simplify the user experience and maintain stricter control over how data is accessed, preventing users from bypassing the Genie's curated, secure environment.

Answer

Yes, users must have direct access to source tables to use Genie.

Answer

No, but it will prevent the Genie from generating any results.

Answer

Yes, the users will be unable to run any queries through Genie.