Databricks-DA-Assoc Practice Test 12 — 15 Questions

Question 1

A data analyst needs to share a sensitive sales table with the marketing team in Databricks. The marketing team should only see rows where the region column matches 'North America' and should not have access to the credit_card column. Which Unity Catalog feature should the data analyst implement?

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

Accepted Answer

Apply row filters and column masks using SQL functions within Unity Catalog to restrict data visibility dynamically for the marketing group.. Row-level and column-level filtering in Unity Catalog allow administrators and data owners to secure fine-grained access to tables using SQL functions. By applying a dynamic view with conditional logic, users see only permitted rows and columns. This ensures regulatory compliance and data minimization principles are met without duplicating physical storage assets across different business units.

Answer

Create a static clone of the table, drop the restricted column, and grant SELECT permission on the clone to the marketing group.

Answer

Configure an access control list on the parent catalog to deny SELECT access on specific columns for the marketing group.

Answer

Export the filtered subset of data into CSV files and upload them to a secured volume inside the marketing team's schema.

Question 2

A data engineer has created a Delta table named `sales_summary` and needs to ensure that downstream analysts can only read rows where the `region` column matches their assigned territory. Which Databricks feature should be implemented to enforce this restriction securely at the row level?

Accepted Answer

Implement Unity Catalog row filters using a SQL function that evaluates the current user.. Row filters in Unity Catalog allow administrators and data owners to apply SQL-based filtering logic directly to tables, ensuring users only see authorized rows. This mechanism is critical for maintaining data governance, security, and compliance across diverse enterprise teams accessing shared datasets in Databricks.

Answer

Apply dynamic views using standard Spark SQL case statements that evaluate session user functions.

Answer

Grant SELECT privileges on filtered partitions using Unity Catalog folder-level access controls.

Answer

Configure cluster-level environment variables to filter data frames automatically upon session start.

Question 3

Which TWO of the following statements accurately describe the relationship between Databricks SQL Warehouses and Delta Lake?

Accepted Answer

SQL Warehouses utilize Delta Lake's transaction log to ensure consistent query results.. Databricks SQL Warehouses provide the compute layer to execute ANSI-compliant SQL against data stored in Delta Lake format. Delta Lake serves as the underlying storage layer, providing ACID transactions and schema enforcement. Analysts must understand this separation, as it allows compute resources to scale independently from storage. This architecture ensures that SQL Warehouses can efficiently query massive volumes of data while leveraging the reliability and performance optimizations inherent in the Delta Lake storage structure.

Answer

SQL Warehouses must always be co-located in the same cloud region as the Delta Lake storage.

Answer

Delta Lake is a compute engine that replaces the need for SQL Warehouses.

Answer

SQL Warehouses require all data to be imported into a proprietary internal database format.

Question 4

A junior data analyst attempts to load a large JSON file into a Databricks DataFrame using spark.read.json(path), but the resulting DataFrame contains numerous null values across critical columns. Upon inspection, the raw JSON records have inconsistent nesting structures and missing attributes. What is the most effective way for the analyst to inspect the inferred schema before transforming the data?

Accepted Answer

Run the schema property or printSchema() method on the DataFrame to review the hierarchical structure and data types inferred by Spark.. Using the printSchema() method on the loaded DataFrame prints out the hierarchical data types and inferred structure determined by Spark during the read operation. Data analysts rely heavily on this method to quickly identify schema evolution issues, unexpected nulls, and nested arrays before building downstream analytical queries and dashboards.

Answer

Execute the display() function on the raw file path string directly without creating a DataFrame object first.

Answer

Query the system information tables in the Hive metastore using a standard SQL DESCRIBE DATABASE statement.

Answer

Export the JSON file into Microsoft Excel locally to manually count the frequency of null values in each column.

Question 5

Refer to the exhibit. Given the provided JSON configuration for a Databricks cluster, what is the primary use case for this resource?

Accepted Answer

Interactive data analysis and notebook development.. Refer to the exhibit. The configuration shows an all-purpose cluster with autoscaling enabled. All-purpose clusters are primarily used for interactive development and data exploration within notebooks. Because they consume more DBU resources compared to job clusters, setting an idle termination limit is a cost-optimization best practice. Understanding cluster types is fundamental for managing platform costs and ensuring that resources are allocated appropriately based on the specific requirements of the workload being executed.

Answer

Running automated production ETL jobs.

Answer

Long-running streaming data ingestion.

Answer

Batch processing of large ML models.

Question 6

An analyst is creating a visualization of daily sales trends over the past year. They notice that the chart is cluttered and difficult to interpret due to the high volume of individual data points. Which action should the analyst take to improve readability?

Accepted Answer

Change the X-axis grouping from 'Day' to 'Month'.. Aggregating data by a larger time grain, such as weeks or months, is a best practice when visualizing high-frequency time-series data. This reduces noise and highlights long-term trends rather than daily fluctuations. By adjusting the visualization settings to group data, the analyst makes the insights more accessible to stakeholders, facilitating faster decision-making and preventing cognitive overload caused by displaying hundreds of individual data points simultaneously.

Answer

Change the chart type to a scatter plot.

Answer

Filter the dataset to only include the last seven days.

Answer

Increase the chart size to fill the entire screen width.

Question 7

Refer to the exhibit. An analyst notices that widget 2 is displaying incorrect trends. Based on the configuration provided, what is the most likely cause?

Accepted Answer

The query for widget 2 does not aggregate data to match the X-axis.. The configuration shows that both widgets share the same X-axis ('date'), but they are visualizing different metrics ('sales' vs 'revenue'). If the data is aggregated at different levels or filtered differently, the trends might appear conflicting. Identifying mismatches between data sources and visualization axes is a common troubleshooting step for analysts, ensuring that the dashboard accurately reflects the underlying business logic and prevents misleading interpretations of the reported data.

Answer

The widget type is set to 'line' instead of 'bar'.

Answer

The X-axis for widget 2 is not properly aligned to the date range.

Answer

The 'revenue' column is incompatible with the 'line' chart type.

Question 8

A data analyst is building a Databricks SQL dashboard to track daily sales. They need to ensure that the dashboard automatically updates every hour to reflect the latest ingestion from Delta tables. Which mechanism should the analyst configure to achieve this requirement?

Accepted Answer

Configure a schedule on the dashboard using a SQL warehouse to run at the desired frequency.. Databricks SQL dashboards include a built-in schedule feature that allows analysts to define an interval for automated refreshes. By configuring a schedule, the dashboard queries run automatically in the background, ensuring stakeholders view current data without manual intervention. This feature is vital for operational reporting where timeliness is critical for business decision-making and performance monitoring across the organization.

Answer

Enable the 'Auto-refresh' toggle within the browser settings of the dashboard viewer.

Answer

Configure a SQL warehouse to 'Auto-stop' every hour to trigger a metadata update.

Answer

Create a Job in the Workflows UI that explicitly opens the dashboard URL every hour.

Question 9

An analyst is preparing a report and needs to ensure that sensitive PII columns are not exposed. Which TWO techniques can be used to achieve this in Databricks SQL?

Accepted Answer

Apply Dynamic Data Masking to the sensitive columns.. Data governance and security are paramount in Databricks SQL. Using column-level filtering and dynamic data masking ensures that analysts only see the data they are authorized to access. These security controls are enforced at the query plan level by Unity Catalog, ensuring that security policies are consistent regardless of the tool or interface the user is utilizing to connect to the warehouse.

Answer

Use the GRANT ALL PRIVILEGES command on all tables.

Answer

Implement Row-Level Security using a filter clause in the view definition.

Answer

Run the query as a superuser to bypass masks.

Question 10

A data analyst is writing a Databricks SQL query that needs to join a large table with a small table and then aggregate the results. To optimize performance, they want to ensure the small table is broadcasted. Which SQL hint syntax should they use in the query?

Accepted Answer

SELECT /*+ BROADCAST(small_table) */ .... The BROADCAST hint in Databricks SQL is specified as /*+ BROADCAST(table_name) */ after SELECT. It directs the optimizer to broadcast the smaller table, avoiding a shuffle of the larger table. Other hints like MAPJOIN or BROADCASTJOIN are not supported.

Answer

SELECT /*+ MAPJOIN(small_table) */ ...

Answer

SELECT /*+ SHUFFLE_HASH(small_table) */ ...

Answer

SELECT /*+ BROADCASTJOIN(small_table) */ ...

Question 11

What happens if a user submits a question that requires data from a table NOT explicitly defined in the Genie space configuration?

Accepted Answer

The model will deny the request due to lack of access. Genie spaces are designed with a security-first approach. If a table is not listed in the space's configuration, the model is physically prevented from accessing it. This ensures that users cannot 'force' the AI to query unauthorized or sensitive data outside the intended analytical scope, protecting the organization from data leakage or unauthorized access to proprietary information stored in other parts of the Unity Catalog.

Answer

The model will try to guess the schema of the table

Answer

The model will use the table anyway if the user has access

Answer

The query will execute but return null results

Question 12

When analyzing query execution metrics in Databricks, what does 'Task Duration' represent?

Accepted Answer

The time elapsed for a single core to process a task.. Task Duration measures the time taken by individual executor nodes to perform their assigned portion of the work. It is distinct from total query time because it highlights parallel execution efficiency. Understanding this is vital because if one task is much longer than others, it indicates data skew or uneven distribution, helping analysts identify the root cause of performance bottlenecks that are hidden within the overall query execution time.

Answer

The time taken to compile the SQL query.

Answer

The total time the cluster was running.

Answer

The latency added by network overhead only.

Question 13

An analyst is optimizing query performance in Databricks SQL. Which TWO of the following actions directly improve query execution speed by reducing the amount of data scanned during query execution?

Accepted Answer

Implementing Z-Ordering on frequently filtered columns.. Data skipping and partition pruning are critical performance optimizations in Databricks SQL. By leveraging the Delta Lake transaction log and metadata, Databricks skips unnecessary files and partitions, drastically reducing I/O requirements. These techniques are fundamental for large-scale data analysis, as they allow queries to target only the specific subsets of data required to satisfy filters, significantly lowering latency and improving overall warehouse performance for end-users.

Answer

Increasing the SQL Warehouse cluster size.

Answer

Enabling query result caching for all users.

Answer

Updating statistics using the ANALYZE TABLE command.

Question 14

An analyst is writing a query in Databricks and needs to reference a temporary view that is scoped only to their current interactive notebook session. Which command or syntax ensures the view is automatically dropped when the session ends?

Accepted Answer

CREATE TEMP VIEW view_name AS SELECT .... Spark SQL supports various view scopes for different collaboration needs. A standard temporary view is tied to the lifecycle of the active SparkSession. Once the notebook session terminates, the temporary view is automatically cleaned up, preventing namespace pollution in shared database catalogs.

Answer

CREATE GLOBAL TEMP VIEW view_name AS SELECT ...

Answer

CREATE TABLE view_name AS SELECT ...

Answer

CREATE MANAGED VIEW view_name AS SELECT ...

Question 15

Refer to the exhibit. An analyst receives this error when attempting to query a table. Which step should they take to fix this?

Accepted Answer

Check the Catalog Explorer to confirm the correct fully qualified table name.. The error indicates a Namespace resolution issue. In Unity Catalog, objects must be referenced using the three-level namespace: catalog.schema.table. The error shows that the system is looking in 'main.default', but the table might be in a different catalog or schema. The analyst needs to verify the correct path using the Catalog Explorer to ensure they are referencing the qualified table name correctly in their SQL query.

Answer

Increase the SQL Warehouse size to handle larger metadata lookups.

Answer

Switch the notebook language to Python to access the table.

Answer

Request Workspace Admin privileges to access all system tables.