Databricks Certified Data Analyst Associate Free Sample Questions
Covers the Databricks SQL service and query optimization, Delta Lake and Unity Catalog management, advanced lakehouse SQL, building visualizations and dashboards, and analytics applications.
19 free sample questions204 in the full practice test
A financial services company is analyzing streaming transaction data stored in a bronze Delta table. An analyst needs to create a silver table that includes a new column, is_flagged, which is set to true if a transaction amount exceeds $10,000. The process must be idempotent and handle late-arriving data. Which SQL command is most appropriate for this continuous transformation?
Answer and explanation
Correct answer: B
The MERGE command is the correct choice because it is designed for idempotent upsert (update/insert) operations. It can match records on a key (transaction_id) and either update existing records or insert new ones. This handles new and late-arriving data gracefully. CREATE OR REPLACE TABLE would reprocess the entire dataset each time, which is inefficient. INSERT INTO would create duplicates, and a simple UPDATE would not handle new records.
Question 2
An analyst is building a dashboard to monitor daily user engagement. A key visualization needs to show the count of active users. The underlying query for this visualization is computationally expensive. The dashboard is viewed frequently by executives, and fast load times are critical. Which feature should the analyst enable for this specific query to improve dashboard performance for all users?
Answer and explanation
Correct answer: C
Databricks SQL provides a Query Cache that stores the results of a query. When the same query is executed again, Databricks returns the result from the cache, which is significantly faster than re-executing it. This is ideal for frequently accessed dashboards with expensive underlying queries. While increasing cluster size or using serverless compute can help with initial query execution, caching provides the most significant performance boost for repeated views of the same data.
Question 3
True or False: In Databricks SQL, a VIEW always stores a physical copy of the data derived from its defining query, similar to a materialized view in other database systems.
Answer and explanation
Correct answer: B
This statement is false. A standard VIEW in Databricks SQL is a logical object that stores only the query definition. The query is re-executed each time the view is accessed. It does not store a physical copy of the data. Databricks does support MATERIALIZED VIEWs, which pre-compute and store the result set, but a standard VIEW does not.
Question 4
An analyst at a logistics company needs to create a report on late shipments. The shipments table contains shipment_id, estimated_delivery_date, and actual_delivery_date. The analyst needs to add a column delivery_status with three possible values: 'On-Time', 'Late', or 'In-Transit'. Which of the following SQL constructs is the most appropriate and readable way to implement this logic?
Answer and explanation
Correct answer: B
A CASE statement is the standard and most readable SQL construct for handling multi-condition logic. It allows for clear evaluation of each condition (WHEN actual_delivery_date IS NULL THEN 'In-Transit', WHEN actual_delivery_date > estimated_delivery_date THEN 'Late') with a final ELSE clause. While nested IIF() functions can achieve this, they become very difficult to read and maintain as the number of conditions increases.
Question 5
Multiple answers
A data governance team wants to ensure that analysts can only query a version of the customers table from exactly 7 days ago for a weekly compliance report, preventing access to any more recent data. Which Delta Lake feature allows for this specific type of historical data access? (Select TWO)
Answer and explanation
Correct answers: A, C
Question 6
Case Study:
Company Background: Global Retail Innovations (GRI) is a large e-commerce company that uses Databricks for all its data analytics. They follow the medallion architecture, with raw event data landing in bronze tables, cleaned and enriched data in silver tables, and aggregated business-level data in gold tables. The data analytics team primarily uses Databricks SQL to build dashboards for various departments.
Current Situation: The marketing department has requested a new, complex dashboard to track customer lifetime value (LTV). The primary data source for this is a large silver table named customer_transactions with over 5 billion rows. The preliminary query developed by a junior analyst to calculate LTV is taking over 30 minutes to run, which is too slow for an interactive dashboard. The query involves multiple joins with other large dimension tables (customers, products) and uses several window functions.
Requirements:
The LTV dashboard must load in under 60 seconds.
The solution should not require data engineers to build a new ETL pipeline if possible.
The solution must be cost-effective and leverage existing Databricks SQL capabilities.
The final data presented in the dashboard must be aggregated at the customer level.
Problem: How should the data analyst restructure the analytics workflow to meet the performance requirements for the LTV dashboard?
Answer and explanation
Correct answer: B
This is the best practice within the medallion architecture. For complex, slow-running queries that feed dashboards, you should pre-compute the results and store them in a gold-level aggregate table. Querying a small, pre-aggregated table will be extremely fast and easily meet the <60 second requirement. This approach is cost-effective as the expensive computation runs infrequently on a schedule, and the dashboard queries are cheap. It aligns perfectly with the purpose of the gold layer.
Question 7
A data analyst has been given a CSV file containing quarterly sales targets. The file needs to be uploaded to Databricks and queried via SQL. The analyst does not have permissions to create external locations or configure cloud storage. What is the simplest method for the analyst to upload this file and make it queryable?
Answer and explanation
Correct answer: B
The 'Upload Data' feature in the Databricks UI is the most straightforward method for users to upload small files like CSVs and create a managed Delta table from them without needing advanced permissions or knowledge of cloud storage configurations. This tool handles the file upload, schema inference, and table creation in a simple, guided workflow.
Question 8
When configuring a SQL warehouse, what is the primary purpose of the 'Scaling' setting?
Answer and explanation
Correct answer: C
The 'Scaling' setting allows a SQL warehouse to dynamically adjust the number of clusters it uses based on the number of concurrent queries it receives. By setting a minimum and maximum, you enable the warehouse to automatically 'scale out' by adding more clusters to handle high concurrency and 'scale in' by removing clusters during periods of low activity, balancing performance and cost.
Question 9
An analyst is examining the query history to troubleshoot a slow dashboard. They notice that a specific query, which joins a large fact table with a small dimension table, is consistently taking a long time. The query profile shows a large amount of data being shuffled across the network during the join operation. Which Databricks SQL optimization technique could most effectively mitigate this issue?
Answer and explanation
Correct answer: B
A broadcast join (or map-side join) is an optimization where the smaller table is sent to every worker node that holds a partition of the larger table. This avoids the expensive shuffling of the large fact table's data across the network. While Databricks often does this automatically, using a broadcast hint ensures this strategy is used, directly addressing the shuffle-related bottleneck identified in the query profile.
