Download Databricks-Certified-Data-Analyst-Associate Latest Version: 7.0 Practice Exam Questions A and more Exams Data Mining in PDF only on Docsity!
Databricks-Certified-Data-Analyst-Associate Latest Version: 7.0 Practice
Exam Questions And Correct Answers (Verified Answers)
Section 1: Platform Understanding Question 1 Which of the following layers of the medallion architecture is most commonly used by data analysts for business reporting and dashboarding? A) None of these layers are used by data analysts. B) Gold C) All of these layers are used equally by data analysts. D) Silver E) Bronze Correct Answer: B) Gold Rationale: The medallion architecture consists of three layers: Bronze (raw data), Silver (cleaned and validated data), and Gold (aggregated business-level data). Data analysts work most frequently with Gold-layer tables because these contain the aggregated, business-ready data ideal for reporting, dashboards, and analytics, providing a clean and consistent view for decision-making. Question 2 A data analyst who is new to Databricks wants to write and execute SQL queries. In which area of the Databricks SQL workspace should they do this? A) Data page B) Dashboards page C) Queries page D) Alerts page E) SQL Editor page Correct Answer: E) SQL Editor page Rationale: The SQL Editor is the primary interface for writing, running, and saving SQL queries in Databricks SQL. It includes features like a schema browser, query results, and the ability to create visualizations, making it the central tool for analysts to interact with data directly.
Question 3 In relation to other business intelligence tools like Tableau or Power BI, how should Databricks SQL be viewed? A) As an exact substitute with the same level of functionality. B) As a substitute with less functionality. C) As a complete replacement with additional functionality. D) As a complementary tool for professional-grade presentations. E) As a complementary tool for quick in-platform BI work. Correct Answer: E) As a complementary tool for quick in-platform BI work. Rationale: Databricks SQL serves as a powerful complement to tools like Tableau or Power BI. It is excellent for performing quick exploration, writing SQL queries, and building dashboards within the platform. For advanced visualizations and high-end presentations, these specialized BI tools can still be used alongside Databricks SQL. It is not intended to replace them entirely. Question 4 A data analyst is part of a team that uses the medallion architecture. They need to run data quality checks and standardize data formats. At which layer should they primarily perform these tasks? A) Bronze B) Silver C) Gold D) Raw E) Source Correct Answer: B) Silver Rationale: The Silver layer is where data is cleaned, enriched, and made conformant for analysis. This includes enforcing schemas, handling nulls, removing duplicates, and ensuring data quality. Gold-level data is aggregated and ready for business use, but the heavy lifting of cleaning happens in the Silver layer. Question 5
A data analyst is asked to create a long-running SQL query that aggregates several months of sales data. Which compute resource is designed specifically for this type of workload? A) All-purpose clusters B) Job clusters C) SQL warehouses D) High-concurrency clusters E) Single-node clusters Correct Answer: C) SQL warehouses Rationale: SQL warehouses (formerly known as SQL endpoints) are the compute resources optimized for running SQL queries and powering dashboards. They are designed to be scalable and provide fast performance for analytics workloads like the one described, unlike general-purpose clusters intended for data engineering or machine learning. Question 8 A data analyst is told to create a dashboard that will be used by a team of 50 people concurrently, all running different filters and views. What is the most effective way to design this? A) Create 50 individual dashboards, one for each user. B) Build a dashboard and share the entire workspace folder with the team. C) Use dashboard parameters and filters to create an interactive experience where all points of view can be explored through a single dashboard. D) Provide each user with a personal data copy of the source tables to avoid conflicts. E) Use the dashboard only as a static PDF report distributed daily. Correct Answer: C) Use dashboard parameters and filters to create an interactive experience where all points of view can be explored through a single dashboard. Rationale: Dashboard parameters and filters allow a single dashboard to be highly interactive, enabling users to drill down, slice data, and change views themselves. This is a scalable approach for large teams, eliminating the need to build duplicate reports. Question 9 The built-in Databricks Assistant is designed to help data analysts by:
A) Automatically fixing all data errors. B) Generating and debugging SQL queries using natural language prompts. C) Replacing the need for any SQL knowledge. D) Automatically scheduling all dashboards for email delivery. E) Managing access control lists for all tables. Correct Answer: B) Generating and debugging SQL queries using natural language prompts. Rationale: The Databricks Assistant is an AI-powered tool that helps analysts be more productive. It can understand natural language requests to generate SQL code and can also help explain and debug existing queries, acting as an intelligent coding partner. Question 10 Which of the following is a primary responsibility of a Table Owner in Databricks SQL? A) Writing all queries for the table. B) Creating all visualizations from the table. C) Managing table permissions and data lifecycle. D) Ensuring the table is used in at least one dashboard. E) Optimizing query performance for every user. Correct Answer: C) Managing table permissions and data lifecycle. Rationale: The Table Owner is a governance role in Databricks, primarily focused on data management. Their core responsibilities include managing access to the table (e.g., granting SELECT), and managing its lifecycle, such as updates, cleanup, and archival. Question 11 Which role in the Databricks Lakehouse Platform uses Databricks SQL as their primary service for querying and data analysis? A) Data Scientist B) Data Engineer C) Data Analyst D) Machine Learning Engineer E) DevOps Engineer Correct Answer: C) Data Analyst.
Rationale: A Serverless SQL Warehouse offloads the management of the underlying compute infrastructure to Databricks, which automatically scales resources as needed. This means the warehouse can start up much faster (often in seconds) and the analyst doesn't have to worry about cluster sizing or tuning, making it efficient for both cost and time management. Question 14 When using Databricks SQL for data analysis, what is a key benefit of having all data stored in a single Lakehouse architecture rather than in multiple silos? A) It guarantees that data is always 100% accurate. B) It allows for direct data editing by all users. C) It eliminates the need for any data governance. D) It provides a single source of truth, reducing data duplication and inconsistencies. E) It automatically makes all data publicly accessible. Correct Answer: D) It provides a single source of truth, reducing data duplication and inconsistencies. Rationale: The Lakehouse architecture centralizes data storage, which creates a single source of truth. This helps avoid the common problem of data silos, where different departments or applications have their own copies of data, leading to inconsistencies and conflicting information. It promotes data integrity and a unified view of the business. Question 15 A data analyst is setting up a new SQL warehouse and wants to ensure it can handle many concurrent queries without excessive queuing. How can they best configure the warehouse to achieve this? A) Reduce the cluster size. B) Turn off the Auto stop feature. C) Use a Serverless SQL endpoint, which automatically handles scaling. D) Increase the minimum scaling value to pre-warm a set of clusters. E) Use a static, single-node cluster. Correct Answer: D) Increase the minimum scaling value to pre-warm a set of clusters. (The best choice if Serverless is not an option. If Serverless is available, it's the more robust option.)
Rationale: Increasing the minimum scaling number ensures a base number of clusters are always running and ready to serve queries. This pre-warming reduces cold start times and provides immediate concurrency, preventing queries from waiting for new clusters to spin up. A Serverless endpoint is the ideal and simplest way to achieve this, as it auto-scales, but if using a classic warehouse, adjusting the minimum scaling value is the direct method. Question 16 A data analyst needs to verify the SQL syntax or test a small part of a query's logic without running the entire query and pulling back all results. What is the best practice within the SQL Editor? A) Use a LIMIT clause, like LIMIT 10, on the query to only return a small sample. B) Manually count the rows in the source table before running the query. C) Run the full query and hope it is correct. D) Check the syntax by compiling the code without execution. E) Use a test notebook with a different programming language. Correct Answer: A) Use a LIMIT clause, like LIMIT 10 , on the query to only return a small sample. Rationale: Using a LIMIT clause is a highly effective and lightweight testing strategy. It allows the analyst to quickly view a subset of the result rows to check for logical errors, confirm column expressions, or ensure joins are correct, without incurring the cost of processing the entire dataset. Section 2: Managing Data Question 17 Which of the following actions can be performed using the Data Explorer in Databricks? A) Only viewing the schema of a table. B) Running a machine learning model. C) Browsing, previewing, and managing data across all catalogs, schemas, and tables registered in Unity Catalog. D) Writing complex Python scripts. E) Configuring alerts and notifications.
Rationale: Delta Lake is an open format storage layer that sits on top of your cloud object storage (your data lake). Its core function is to add reliability to data lakes by providing ACID transactions, scalable metadata handling, and unified streaming and batch data processing. Question 20 A data analyst is part of a team that wants to start tracking the history of all changes to their main "customer" table. They need to be able to query previous versions of the data for audit purposes. What Delta Lake feature should they use? A) MERGE command B) OPTIMIZE command C) VACUUM command D) Time Travel E) CLONE command Correct Answer: D) Time Travel Rationale: Delta Lake's Time Travel feature allows you to query a snapshot of data as it existed at a specific version number or timestamp. This is enabled by the Delta transaction log, which stores a history of all changes to a table, making it possible to roll back or recreate previous states for auditing and debugging. Question 21 An analyst needs to recommend a best practice for naming a catalog that will store the final, business-validated data. Following medallion architecture naming conventions, which name is most appropriate? A) bronze B) silver C) gold D) raw E) source Correct Answer: C) gold Rationale: The medallion architecture uses intuitive naming for its processing layers: bronze (raw data), silver (cleansed data), and gold (business-level aggregated data).
Therefore, a catalog storing validated and aggregated data for consumers would most appropriately be named gold. Question 22 A new data analyst joins a team and needs to understand the lineage of a "customer_gold" table, such as which upstream tables were used to create it. Where is this metadata stored and visualized? A) The Databricks SQL Alerts page. B) The SQL Editor history. C) The data lineage view in Unity Catalog, accessible via the Data Explorer. D) The Databricks Assistant chat log. E) The INFORMATION_SCHEMA tables only. Correct Answer: C) The data lineage view in Unity Catalog, accessible via the Data Explorer. Rationale: Unity Catalog automatically captures and displays data lineage information. This includes a graphical representation of how a dataset was created, showing its dependencies and transformations. Analysts can view this in the Data Explorer by selecting the table and navigating to the "Lineage" tab, which provides a clear audit trail. Question 23 To permanently remove old file versions from a Delta table and reclaim storage space, which SQL command should be run periodically? A) VACUUM B) DELETE FROM C) DROP TABLE D) OPTIMIZE E) REMOVE Correct Answer: A) VACUUM Rationale: The VACUUM command in Delta Lake is used to clean up and delete old data files that are no longer referenced by the Delta table's transaction log, effectively reclaiming storage space. While OPTIMIZE compacts small files, it does not remove old versions; VACUUM is specifically for data lifecycle management.
Question 26 An analyst wants to quickly understand the schema, data types, and some basic statistics of a table named sales_data. Which command is most useful for this purpose? A) SELECT * FROM sales_data B) SHOW TABLES C) DESCRIBE EXTENDED sales_data D) ANALYZE TABLE sales_data COMPUTE STATISTICS E) SHOW DATABASES Correct Answer: C) DESCRIBE EXTENDED sales_data Rationale: The DESCRIBE EXTENDED (or simply DESCRIBE) command provides detailed metadata about a table, including column names, data types, and additional table properties. For basic schema information, DESCRIBE is sufficient. ANALYZE TABLE...COMPUTE STATISTICS goes a step further to collect table-level and column-level statistical information for query optimization. Question 27 An analyst needs to permanently delete a specific version of a Delta table to comply with a data governance request. What is the correct approach? A) Run the VACUUM command with a very short retention period. B) Use the DELETE VERSION command. C) Use the RESTORE command to override it. D) Delta Lake does not support deleting specific versions; you must drop and recreate the table. E) Use DELETE FROM the table. Correct Answer: A) Run the VACUUM command with a very short retention period. Rationale: Delta Lake retains all versions of a table for a default period (usually 7 days). While there is no direct DELETE VERSION command, you can run the VACUUM command with a retention period of 0 hours or just enough to exclude the version you want to delete. This will remove all data files not referenced by the current table state, effectively deleting all versions older than the retention period.
Question 28 Z-ordering is a technique used to improve query performance. A data analyst should consider applying Z-ordering on a Delta table when: A) The table is very small (less than 1GB). B) Queries filter heavily on a specific column or set of columns. C) The table is streamed into as a change data feed. D) The table is not partitioned. E) The table is stored in a MariaDB database. Correct Answer: B) Queries filter heavily on a specific column or set of columns. Rationale: Z-ordering co-locates related information in the same set of files. This dramatically speeds up queries that filter data on the Z-order columns because the query engine can quickly skip over files that don't contain relevant data. It's a powerful optimization for commonly filtered columns. Section 3: Importing Data Question 29 A data analyst has a CSV file stored in their cloud object storage (e.g., S3, ADLS). They want to create a reference to this data in Databricks without moving the files. What is the correct SQL command to do this? A) CREATE MANAGED TABLE B) CREATE EXTERNAL TABLE USING LOCATION C) INSERT INTO D) LOAD DATA INPATH E) COPY INTO Correct Answer: B) CREATE EXTERNAL TABLE USING LOCATION Rationale: An external table in Databricks is a table whose data is stored in a user-defined location outside of the managed table root location. Using CREATE EXTERNAL TABLE ... LOCATION 'path/to/file' creates a table in the metastore that points to the existing data, without moving it into Databricks-managed storage. Question 30
A) Ingest the files all in one COPY INTO command with the OPTIMIZE option. B) Use SELECT * FROM read_files(). C) Create a new SQL warehouse. D) Manually concatenate all the CSV files locally. E) Use CREATE TABLE AS SELECT from a UNION ALL of file paths. Correct Answer: A) Ingest the files all in one COPY INTO command with the OPTIMIZE option. Rationale: The COPY INTO command can be combined with OPTIMIZE to automatically compact the small input files into larger, more efficient files in the target Delta table. Many small files can cause performance issues, and ingesting them with an OPTIMIZE clause is a best practice. Question 33 A data analyst needs to set up an automated, scheduled import of data from a table in an external PostgreSQL database. Which Databricks feature is best suited for this complex, recurring task? A) Data Explorer UI upload B) Databricks SQL Alerts C) A SQL notebook scheduled as a Databricks Job D) The CREATE EXTERNAL TABLE command E) Databricks Assistant Correct Answer: C) A SQL notebook scheduled as a Databricks Job Rationale: While Partner Connect is excellent for initial setup, for custom, scheduled, or complex ETL logic (like connecting to Postgres, performing transformations, and writing to a Delta table), the standard is to create a notebook with the necessary JDBC connection logic and then schedule it as a Databricks Job. This provides the most flexibility and control. Question 34 An analyst is using COPY INTO but notices it is re-processing the same files every time the command runs, creating duplicate data. What is the most likely cause and solution?
A) The analyst is not using Z-ORDER BY. B) The source files are being overwritten. C) The schema at the source location has changed. D) The analyst is running COPY INTO without a storage location for FOREIGN metadata. E) The analyst is running COPY INTO without a storage location for the internal metadata log. Correct Answer: E) The analyst is running COPY INTO without a storage location for the internal metadata log. Rationale: COPY INTO uses an internal metadata store to track which files have already been successfully loaded. This metadata must be persisted. If a permanent storage location is not provided for this metadata (e.g., COPY INTO ... FROM ... FILEFORMAT ... STORED AS ... LOCATION '...'), then the command will have no memory of previous runs and will reload all data, causing duplicates. Question 35 When creating a new PROD_SALES managed table in the default hive_metastore, where is the data physically stored? A) In the user's local database. B) In a Databricks-managed storage location (e.g., DBFS root). C) In an S3 bucket chosen by the user. D) In the Databricks SQL warehouse memory. E) On the cluster's local SSD. Correct Answer: B) In a Databricks-managed storage location (e.g., DBFS root). Rationale: A managed table's data files are stored in a managed location within the Databricks environment. By default, this is the DBFS (Databricks File System) root location, which is managed by the Databricks account. The user does not need to specify a path; Databricks controls the entire lifecycle of the data. Section 4: SQL in the Lakehouse Question 36
An analyst needs to update a set of records in a Delta table based on a key from a source table. If the key exists, the record should be updated; if not, a new record should be inserted. Which SQL command accomplishes this in a single atomic operation? A) INSERT OVERWRITE B) MERGE INTO C) UPDATE JOIN D) REPLACE WHERE E) COPY INTO Correct Answer: B) MERGE INTO Rationale: The MERGE INTO command (also known as "upsert") is designed for this exact pattern. It allows you to define the condition for when to update and when to insert, making it atomic and efficient for incremental data updates and Change Data Capture (CDC) scenarios. Question 39 An analyst has a user_activity table that gets new events daily. They want to incrementally add only the new day's data to a historical daily_activity_summary table without reprocessing all historical data. Which pattern is most efficient? A) A daily DROP TABLE and CREATE TABLE AS SELECT. B) A daily INSERT INTO ... SELECT for the new day's data. C) A daily REFRESH MATERIALIZED VIEW. D) Using COPY INTO on the summary table. E) Manually appending the data in Excel. Correct Answer: B) A daily INSERT INTO ... SELECT for the new day's data. Rationale: For incremental batches, the simplest and most efficient approach is to use INSERT INTO ... SELECT to append only the new, processed data to the historical summary table. This avoids the cost of reprocessing all historical data. Question 40 An analyst runs a SELECT * FROM sales query and gets 1,000 rows. They then run SELECT DISTINCT * FROM sales and get 975 rows. What does this indicate about the sales table?
A) There are 975 distinct rows and 25 duplicate rows. B) The sales table is corrupted. C) The analyst must have made a typing error. D) The DISTINCT keyword does not work on * in Databricks. E) There are 25 NULL values in the table. Correct Answer: A) There are 975 distinct rows and 25 duplicate rows. Rationale: The DISTINCT keyword removes duplicate rows from the result set. Since DISTINCT * returns a row for every unique combination of values, the difference in count (1000 - 975 = 25) is the number of duplicate rows present in the original sales table. Question 41 A user needs to combine all rows from two tables table_a and table_b into a single result, including duplicates. Which set operator should they use? A) UNION DISTINCT B) UNION ALL C) INTERSECT D) EXCEPT E) MINUS Correct Answer: B) UNION ALL Rationale: UNION ALL combines the result sets of two queries and retains all rows, including duplicates. UNION (by itself or UNION DISTINCT) would remove duplicate rows, which is not the requirement here. Question 42 What is a key benefit of writing SQL in a Databricks notebook instead of using a typical SQL workbench? A) Notebooks cannot use parameters, making them easier. B) They have a built-in database for data storage. C) They allow for multi-language integration (SQL, Python, R) in a single workflow and provide version control. D) Notebooks automatically tune all SQL queries for performance. E) They can only be run as a one-time script.
