DB DA DA

1. Which of the following layers of the medallion architecture is most commonly used by data analysts?. None of these layers are used by data analysts. Gold. All of these layers are used equally by data analysts. Silver. Bronze.

2. A data analyst has recently joined a new team that uses Databricks SQL, but the analyst has never used Databricks before. The analyst wants to know where in Databricks SQL they can write and execute SQL queries. On which of the following pages can the analyst write and execute SQL queries?. Data page. Dashboards page. Queries page. Alerts page. SQL Editor page.

3. Which of the following describes how Databricks SQL should be used in relation to other business intelligence (Bl) tools like Tableau, Power BI, and looker?. As an exact substitute with the same level of functionality. As a substitute with less functionality. As a complete replacement with additional functionality. As a complementary tool for professional-grade presentations. As a complementary tool for quick in-platform BI work.

4. Which of the following approaches can be used to connect Databricks to Fivetran for data ingestion?. Use Workflows to establish a SQL warehouse (formerly known as a SQL endpoint) for Fivetran to interact with. Use Delta Live Tables to establish a cluster for Fivetran to interact with. Use Partner Connect's automated workflow to establish a cluster for Fivetran to interact with. Use Partner Connect's automated workflow to establish a SQL warehouse (formerly known as @ SQL endpoint) for Fivetran to interact with. Use Workflows to establish a cluster for Fivetran to interact with.

5. Data professionals with varying titles use the Databricks SQL service as the primary touchpoint with the Databricks Lakehouse Platform. However, some users will use other services like Databricks Machine Learning or Databricks Data Science and Engineering Which of the following roles uses Databricks SQL as a secondary service while primarily using one of the other services?. Business analyst. SQL analyst. Data engineer. Business intelligence analyst,. Data analyst.

6. A data analyst has set up a SQL query to run every four hours on a SQL endpoint, but the SQL endpoint is taking too long to start up with each run Which of the following changes can the data analyst make to reduce the start-up time for the endpoint while managing costs?. Reduce the SQL endpoint cluster size. Increase the SQL endpoint cluster size. Turn off the Auto stop feature. Increase the minimum scaling value E, Use a Serverless SQL endpoint. Use a Serverless SQL endpoint.

7. A data engineering team has created a Structured Streaming pipeline that processes data in micro-batches and populates gold-level tables. The microbatches are triggered every minute. A data analyst has created a dashboard based on this gold-level data. The project stakeholders want to see the results in the dashboard updated within one minute or less of new data becoming available within the gold-level tables. Which of the following cautions should the data analyst share prior to setting up the dashboard to complete this task?. The required compute resources could be costly. The gold-level tables are not appropriately clean for business reporting. The streaming data is not an appropriate data source for a dashboard. The streaming cluster is not fault tolerant E, The dashboard cannot be refreshed that quickly. The dashboard cannot be refreshed that quickly.

8. Which of the following approaches can be used to ingest data directly from cloud-based object storage?. Create an external table while specifying the DBFS storage path to FROM. Create an external table while specifying the DBFS storage path to PATH. Itis not possible to directly ingest data from cloud-based object storage. Create an external table while specifying the object storage path to FROM E, Create an external table while specifying the object storage path to LOCATION. Create an external table while specifying the object storage path to LOCATION.

9. A data analyst wants to create a dashboard with three main sections: Development, Testing, and Production. They want all three sections on the same dashboard, but they want to clearly designate the sections using text on the dashboard. Which of the following tools can the data analyst use to designate the Development, Testing, and Production sections using text?. Separate endpoints for each section. Separate queries for each section. Markdown-based text boxes. Direct text written into the dashboard in editing mode. Separate color palettes for each section.

10. A data analyst needs to use the Databricks Lakehouse Platform to quickly create SQL queries and data visualizations. It is a requirement that the compute resources in the platform can be made serverless, and it is expected that data visualizations can be placed within a dashboard. Which of the following Databricks Lakehouse Platform services/capabilties meets all of these requirements?. Delta Lake. Databricks Notebooks. Tableau. Databricks Machine Learning. Databricks SQL.

11. A data analyst is attempting to drop a table my_table. The analyst wants to delete all table metadata and data They run the following command: DROP TABLE IF EXISTS my_table; While the object no longer appears when they run SHOW TABLES, the data files still exist. Which of the following describes why the data files stil exist and the metadata files were deleted?. The table's data was larger than 10 GB. The table did not have a location. The table was external. The table's data was smaller than 10 GB. The table was managed.

12. After running DESCRIBE EXTENDED accounts.customers;, the following was returned: Name accounts.customers Location dbfs:/stakeholders/customers Provider delta Owner root Type EXTERNAL Now, a data analyst runs the following command: DROP accounts.customers; Which of the following describes the result of running this command?. Running SELECT * FROM delta. “dbfs:/stakeholders/customers’ results in an error. Running SELECT * FROM accounts.customers will return all rows in the table. Allfiles with the customers extension are deleted. The accounts.customers table is removed from the metastore, and the underlying data files are deleted. E, The accounts. customers table is removed from the metastore, but the underlying data files are untouched. The accounts. customers table is removed from the metastore, but the underlying data files are untouched.

13. Which of the following should data analysts consider when working with personally identifiable information (Pll) data?. Organization-specific best practices for Pill data. Legal requirements for the area in which the data was collected. None of these considerations. Legal requirements for the area in which the analysis is being performed. All of these considerations.

14. Delta Lake stores table data as a series of data files, but it also stores a lot of other information. Which of the following is stored alongside data files when using Delta Lake?. None of these. Table metadata, data summary visualizations, and owner account information. Table metadata. Data summary visualizations. Owner account information.

15. Which of the following is an advantage of using a Delta Lake-based data lakehouse over common data lake solutions?. ACID transactions. Flexible schemas. Data deletion. Scalable storage E, Open-source formats. Open-source formats.

16. Which of the following benefits of using Databricks SQL is provided by Data Explorer?. It can be used to run UPDATE queries to update any tables in a database. It can be used to view metadata and data, as well as view/change permissions. It can be used to produce dashboards that allow data exploration. It can be used to make visualizations that can be shared with stakeholders. It can be used to connect to third party Bl cools.

17. The stakeholders. customers table has 15 columns and 3,000 rows of data. The following command is run: CREATE TEMP VIEW stakeholders.eur_customers AS SELECT * FROM stakeholders.customers WHERE continent = 'eur'; After running SELECT * FROM stakeholders.eur_customers, 15 rows are returned. After the command executes completely, the user logs out of Databricks. After logging back in two days later, what is the status of the stakeholders.eur_customers view?. The view remains available and SELECT * FROM stakeholders.eur_customers will execute correctly. The view has been dropped. The view is not available in the metastore, but the underlying data can be accessed with SELECT * FROM delta “stakeholders.eur_customers’. The view remains available but attempting to SELECT from it results in an empty result set because data in views are automatically deleted after logging out. E, The view has been converted into a table. The view has been converted into a table.

18. A data analyst created and is the owner of the managed table my_table. They now want to change ownership of the table to a single other user using Data Explorer. Which of the following approaches can the analyst use to complete the task?. Edit the Owner field in the table page by removing their own account. Edit the Owner field in the table page by selecting All Users,. Edit the Owner field in the table page by selecting the new owner's account. Edit the Owner field in the table page by selecting the Admins group. Edit the Owner field in the table page by removing all access.

19. A data analyst has a managed table table_name in database database_name. They would now like to remove the table from the database and all of the data files associated with the table. The rest of the tables in the database must continue to exist. Which of the following commands can the analyst use to complete the task without producing an error?. DROP DATABASE database_name;. DROP TABLE database_name.table_name;. DELETE TABLE database_name.table_name;. DELETE TABLE table_name FROM database_name; E, DROP TABLE table_name FROM database_name;. DROP TABLE table_name FROM database_name;.

20. A data analyst runs the following command: SELECT age, country FROM my_table WHERE age >= 75 AND country = 'canada'; Which of the following tables represents the output of the above command?. age | country: 80 | canada; NULL | canada; 90 | NULL. age | country: 80 | NULL; 75 | NULL; 90 | NULL. id | age | country: 900 | 80 | canada; 901 | 75 | canada; 902 | 90 | canada. age | country: 80 | canada; 74 | canada; 90 | canada. age | country: 80 | canada; 75 | canada; 90 | canada.

21. A data analyst runs the following command: INSERT INTO stakeholders. suppliers TABLE stakeholders.new_suppliers; What is the result of running this command?. The suppliers table now contains both the data it had before the command was run and the data from the new_suppliers table, and any duplicate data is deleted. The command fails because itis written incorrectly. The suppliers table now contains both the data it had before the command was run and the data from the new_suppliers table, including any duplicate data. The suppliers table now contains the data from the new_suppliers table, and the new_suppliers table now contains the data from the suppliers table. E, The suppliers table now contains only the data from the new_suppliers table. The suppliers table now contains only the data from the new_suppliers table.

22. A data engineer is working with a nested array column products in table transactions. They want to expand the table so each unique item in products for each row has its own row where the transaction_id column is duplicated as necessary. They are using the following incomplete command: SELECT transaction _ id, AS product FROM transactions; Which of the following lines of code can they use to fill in the blank in the above code block so that it successfully completes the task?. array distinct(products). explode(products). reduce(products). array(products). flatten(products).

23. A data analysis team is working with the table_bronze SQL table as a source for one of its most complex projects. A stakeholder of the project notices that some of the downstream data is duplicative. The analysis team identifies table_bronze as the source of the duplication. Which of the following queries can be used to deduplicate the data from table_bronze and write it to a new table table_silver?. CREATE TABLE table _silver AS - SELECT DISTINCT * FROM table_bronze;. CREATE TABLE table_silver AS - INSERT * FROM table_bronze;. CREATE TABLE table_silver AS - MERGE DEDUPLICATE * FROM table_bronze;. INSERT INTO TABLE table_silver - SELECT * FROM table_bronze; E, INSERT OVERWRITE TABLE table_silver SELECT * FROM table_bronze;. INSERT OVERWRITE TABLE table_silver SELECT * FROM table_bronze;.

24. A business analyst has been asked to create a data entity/object called sales_by_employee. It should always stay up-to-date when new data are added to the sales table. The new entity should have the columns sales_person, which will be the name of the employee from the employees table, and sales, which will be all sales for that particular sales person. Both the sales table and the employees table have an employee_id column that is used to identify the sales person. Which of the following code blocks will accomplish this task?. CREATE TEMPORARY TABLE sales_by_employee AS SELECT employees.employee_name sales_person, sales.sales FROM sales JOIN employees ON employees.employee_id = sales.employee_id;. CREATE OR REPLACE VIEW sales_by_employee USING SELECT employees.employee_name sales_person, sales.sales FROM sales JOIN employees ON employees.employee_id = sales.employee_id;. SELECT employees.employee_name sales_person, sales.sales FROM sales JOIN employees ON employees.employee_id = sales.employee_id USING CREATE OR REPLACE VIEW sales_by_employee;. CREATE OR REPLACE VIEW sales_by_employee AS SELECT employees.employee_name sales_person, sales.sales FROM sales JOIN employees ON employees.employee_id = sales.employee_id;. CREATE OR REPLACE TABLE sales_by_employee AS SELECT employees.employee_name sales_person, sales.sales FROM sales JOIN employees ON employees.employee_id = sales.employee_id;.

25. A data analyst has been asked to use the below table sales_table to get the percentage rank of products within region by the sales: region | product | sales: WEST | A | 1880.59; EAST | A | 2045.99; EAST | B | 4583.23; WEST | B | 3391.19. The result should be: region | product | sales: EAST | B | 0; EAST | A | 1; WEST | B | 0; WEST | A | 1. Which of the following queries will accomplish this task?. SELECT region, product, RANK() OVER (PARTITION BY region ORDER BY sales DESC) AS rank FROM sales_table GROUP BY region, product;. SELECT region, product, PERCENT_RANK() OVER (PARTITION BY region ORDER BY sales DESC) AS rank FROM sales_table GROUP BY region, product;. SELECT region, product, PERCENT_RANK() OVER (ORDER BY sales DESC) AS rank FROM sales_table;. SELECT region, product, PERCENT_RANK() OVER (PARTITION BY product ORDER BY sales DESC) AS rank FROM sales_table GROUP BY region, product;.

26. In which of the following situations should a data analyst use higher-order functions?. When custom logic needs to be applied to simple, unnested data. When custom logic needs to be converted to Python-native code. When custom logic needs to be applied at scale to array data objects. When built-in functions are taking too long to perform tasks E, When built-in functions need to run through the Catalyst Optimizer. When built-in functions need to run through the Catalyst Optimizer.

27. Consider the following two statements: Statement 1: SELECT * FROM customers LEFT SEMI JOIN orders ON customers.customer_id = orders.customer_id? Statement 2 SELECT * FROM customers LEFT ANTI JOIN orders ON customers.customer_id = orders.customer_id; Which of the following describes how the result sets will differ for each statement when they are run in Databricks SQL?. The first statement will return all data from the customers table and matching data from the orders table. The second statement will return all data from the orders table and matching data from the customers table. Any missing data will be filled in with NULL. When the first statement is run, only rows from the customers table that have at least one match with the orders table on customer_id will be returned. When the second statement is run, only those rows in the customers table that do not have at least one match with the orders table on customer_id will be returned. There is no difference between the result sets for both statements. Both statements will fail because Databricks SQL does not support those join types. When the first statement is run, all rows from the customers table will be returned and only the customer_id from the orders table will be returned. When the second statement is run, only those rows in the customers table that do not have at least one match with the orders table ‘on customer_id will be returned.

28. A data analyst has created a user-defined function using the following line of code: CREATE FUNCTION price(spend DOUBLE, units DOUBLE) RETURNS DOUBLE - RETURN spend / units; Which of the following code blocks can be used to apply this function to the customer_spend and customer_units columns of the table customer_summary to create column customer_price?. SELECT PRICE customer_spend, customer_units AS customer_price FROM customer_summary B, SELECT price - FROM customer_summary. SELECT price - FROM customer_summary. SELECT function(price(customer_spend, customer_units)) AS customer_price FROM customer_summary. SELECT double(price(customer_spend, customer_units)) AS customer_price FROM customer_summary. SELECT price(customer_spend, customer_units) AS customer_price FROM customer_summary.

29. A data analyst has been asked to count the number of customers in each region and has written the following query: SELECT region, count(*) AS number of customers FROM customers ORDER BY region; If there is a mistake in the query, which of the following describes the mistake?. The query is using count(*), which will count all the customers in the customers table, no matter the region. The query is missing a GROUP BY region clause. The query is using ORDER BY, which is not allowed in an aggregation. There are no mistakes in the query. The query is selecting region, but region should only occur in the ORDER BY clause.

30. A data analyst is processing a complex aggregation on a table with zero null values and their query returns the following result: group 1 group 2 sum null null 100, null Y 70 null Z 30 A null 50 A Y 30 A Z 20 B null 50 B Y 40 B Z 10 Which of the following queries did the analyst run to obtain the above result?. SELECT group_1, group_2, count(values) AS count FROM my_table GROUP BY group_1, group_2 INCLUDING NULL;. SELECT group_1, group_2, count(values) AS count FROM my_table GROUP BY group_1, group_2 WITH ROLLUP;. SELECT group_1, group_2, count(values) AS count FROM my_table GROUP BY group_1, group_2;. SELECT group_1, group_2, count(values) AS count FROM my_table GROUP BY group_1, group_2, (group_1, group_2);. SELECT group_1, group_2, count(values) AS count FROM my_table GROUP BY group_1, group_2 WITH CUBE;.

31. Which of the following is a benefit of Databricks SQL using ANSI SQL as its standard SQL dialect?. Ithas increased customization capabilities. Itis easy to migrate existing SQL queries to Databricks SQL. It allows for the use of Photon’s computation optimizations. It is more performant than other SQL dialects. It is more compatible with Spark's interpreters.

32. How can a data analyst determine if query results were pulled from the cache?. Go to the Query History tab and click on the text of the query. The slideout shows if the results came from the cache. Go to the Alerts tab and check the Cache Status alert. Go to the Queries tab and click on Cache Status, The status will be green if the results from the last run came from the cache. Go to the SQL Warehouse (formerly SQL Endpoints) tab and click on Cache. The Cache file will show the contents of the cache. Go to the Data tab and click Last Query. The details of the query will show if the results came from the cache.

33. Which of the following statements about a refresh schedule is incorrect?. Aquery can be refreshed anywhere from 1 minute to 2 weeks. Refresh schedules can be configured in the Query Editor. A query being refreshed on a schedule does not use a SQL Warehouse (formerly known as SQL Endpoint),. Arefresh schedule is not the same as an alert. You must have workspace administrator privileges to configure a refresh schedule.

34. A data analyst creates a Databricks SQL Query where the result set has the following schema: region STRING number_of_customer INT When the analyst clicks on the "Add visualization’ button on the SQL Editor page, which of the following types of visualizations will be selected by default?. Violin Chart. Line Chart. Bar Chart. Histogram. There is no default. The user must choose a visualization type.

35. A data analyst has created a Query in Databricks SQL, and now they want to create two data visualizations from that Query and add both of those data visualizations to the same Databricks SQL Dashboard Which of the following steps will they need to take when creating and adding both data visualizations to the Databricks SQL Dashboard?. They will need to alter the Query to return two separate sets of results. They will need to add two separate visualizations to the dashboard based on the same Query. They will need to create two separate dashboards. They will need to decide on a single data visualization to add to the dashboard. They will need to copy the Query and create one data visualization per query.

36. A data analyst has been asked to provide a list of options on how to share a dashboard with a client. It is a security requirement that the client does not gain access to any other information, resources, or artifacts in the database. Which of the following approaches cannot be used to share the dashboard and meet the security requirement?. Download the Dashboard as a POF and share it with the client. Set a refresh schedule for the dashboard and enter the client's email address in the "Subscribers" box. C, Take a screenshot of the dashboard and share it with the client. Take a screenshot of the dashboard and share it with the client. Generate a Personal Access Token that is good for 1 day and share it with the client. E, Download a PNG file of the visualizations in the dashboard and share them with the client. Download a PNG file of the visualizations in the dashboard and share them with the client.

37. A data analyst has been asked to produce a visualization that shows the flow of users through a website. Which of the following is used for visualizing this type of flow?. Heatmap. Choropleth ©. Word Cloud. Pivot Table E, Sankey. Sankey.

38. An analyst writes a query that contains a query parameter. They then add an area chart visualization to the query. While adding the area chart visualization to a dashboard, the analyst chooses "Dashboard Parameter” for the query parameter associated with the area chart. Which of the following statements is true?. The area chart will use whatever is selected in the Dashboard Parameter while all or the other visualizations will remain changed regardless of their parameter use. The area chart will use whatever is selected in the Dashboard Parameter along with all of the other visualizations in the dashboard that use the same parameter. The area chart will use whatever value is chosen on the dashboard at the time the area chart is added to the dashboard. The area chart will use whatever value is input by the analyst when the visualization is added to the dashboard. The parameter cannot be changed by the user afterwards. E, The area chart will convert to a Dashboard Parameter. The area chart will convert to a Dashboard Parameter.

39. ‘A data analyst has been asked to configure an alert for a query that returns the income in the accounts_receivable table for a date range. The date range is configurable using a Date query parameter. The Alert does not work. Which of the following describes why the Alert does not work?. Alerts don't work with queries that access tables. Queries that return results based on dates cannot be used with Alerts. The wrong query parameter is being used. Alerts only work with Date and Time query parameters. Queries that use query parameters cannot be used with Alerts. The wrong query parameter is being used. Alerts only work with dropdown list query parameters, not dates.

40. Which of the following statements about adding visual appeal to visualizations in the Visualization Editor is incorrect?. Visualization scale can be changed. Data Labels can be formatted. Colors can be changed. Borders can be added. Tooltips can be formatted.

41. A data team has been given a series of projects by a consultant that need to be implemented in the Databricks Lakehouse Platform. Which of the following projects should be completed in Databricks SQL?. Testing the quality of data as itis imported from a source. Tracking usage of feature variables for machine learning projects. Combining two data sources into a single, comprehensive dataset. Segmenting customers into like groups using a clustering algorithm. Automating complex notebook-based workflows with multiple tasks.

42. A data organization has a team of engineers developing data pipelines following the medallion architecture using Delta Live Tables. While the data analysis team working on a project is using gold-layer tables from these pipelines, they need to perform some additional processing of these tables prior to performing their analysis. Which of the following terms is used to describe this type of work?. Data blending. Last-mile dashboarding. Data testing. Last-mile ETL. Data enhancement.

43. Which of the following statements describes descriptive statistics?. Abranch of statistics that uses summary statistics to quantitatively describe and summarize data. A branch of statistics that uses a variety of data analysis techniques to infer properties of an underlying distribution of probability. A branch of statistics that uses quantitative variables that must take on a finite or countably infinite set of values. A branch of statistics that uses summary statistics to categorically describe and summarize data,. A branch of statistics that uses quantitative variables that must take on an uncountable set of values.

44. In which of the following situations will the mean value and median value of variable be meaningfully different?. When the variable contains no outliers. When the variable contains no missing values. When the variable is of the boolean type. When the variable is of the categorical type E, When the variable contains a lot of extreme outliers. When the variable contains a lot of extreme outliers.

45. A data analyst is working with gold-layer tables to complete an ad-hoc project. A stakeholder has provided the analyst with an additional dataset that can be used to augment the gold-layer tables already in use. Which of the following terms is used to describe this data augmentation?. Data testing. Ad-hoc improvements. Last-mile dashboarding. Last-mile ETL E, Data enhancement. Data enhancement.

46. A data analyst needs to query the data in my_table. They want to return the values in columns user_id and email_address where records fit the following filter criteria: age is greater than 25 and country is Canada. The analyst does not want to return values from any other columns. Which code block will accomplish the above task?. SELECT * FROM my_table WHERE age > 25 AND country = ‘Canada’;. SELECT user_id, email_address FROM my_table WHERE age > 25 AND country = ‘Canada’,. SELECT age, country FROM my_table WHERE age > 25 AND country = ‘Canada’. SELECT user_id, email_address FROM my_table WHERE age = 25 AND country > ‘Canada’;.

47. Which example of data projects represents a common analytics application to be completed in Databricks SQL?. Augmenting gold-layer tables with additional external information. Automating complex notebook-based workflows with multiple tasks. Testing the quality of data as it is imported from a source. Segmenting customers into like groups using a clustering algorithm.

48. What is a benefit of using Databricks SQL for business intelligence (Bl) analytics projects instead of using third-party Bl tools?. Computations, data, and analytical tools on the same platform. Advanced dashboarding capabilities. Simultaneous multi-user support. Automated alerting systems.

49. A data analyst wants the following output: customer_name | number_of_orders; John Doe | 388; Zhang San | 234. Which statement will produce this output?. SELECT customer_name, (order_id) number_of_orders FROM customers JOIN orders ON customers.customer_id = orders.customer_id;. SELECT customer_name, count(order_id) FROM customers JOIN orders ON customers.customer_id = orders.customer_id GROUP BY customer_name;. SELECT customer_name, count(order_id) number_of_orders FROM customers JOIN orders ON customers.customer_id = orders.customer_id USE customer_name;. SELECT customer_name, count(order_id) AS number_of_orders FROM customers JOIN orders ON customers.customer_id = orders.customer_id GROUP BY customer_name;.

50. What is an advantage of using a Delta Lake-based data lakehouse over classic enterprise data warehouse solutions?. Open-source formats. Schema enforcement. ACID transactions. Generic optimizations.

51. Query History provides Databricks SQL users with a lot of benefits. A data analyst has been asked to share all of these benefits with their team as part of a training exercise. One of the benefit statements the analyst provided to their team is incorrect. Which statement about Query History is incorrect?. It'can be used to troubleshoot slow running queries. Itcan be used to view the query plan of queries that have run. Itcan be used to debug queries. it can be used to automate query execution on multiple warehouses (formerly endpoints).

52. A data scientist has asked a data analyst to create histograms for every continuous variable in a data set. The data analyst needs to identify which columns are continuous in the data set. What describes a continuous variable?. A quantitative variable that never stops changing. A quantitative variable that can take on a finite or countably infinite set of values. A quantitative variable that can take on an uncountable set of values. A categorical variable in which the number of categories continues to increase over time.

53. A data analyst has been asked to use the below table sales_table to rank products within region by the sales: region | product | sales: WEST | A | 1880.59; EAST | A | 2045.99; EAST | B | 4583.23; WEST | B | 3391.19. The result should be: region | product | rank: EAST | B | 1; EAST | A | 2; WEST | B | 1; WEST | A | 2. Which query will accomplish this task?. SELECT region, product, RANK() OVER (ORDER BY sales DESC) AS rank FROM sales_table;. SELECT region, product, RANK() OVER (PARTITION BY product ORDER BY sales DESC) AS rank FROM sales_table;. SELECT region, product, RANK() OVER (PARTITION BY region) AS rank FROM sales_table;. SELECT region, product, RANK() OVER (PARTITION BY region ORDER BY sales DESC) AS rank FROM sales_table;.

54. A database was created in Databricks SQL using the following statement: CREATE SCHEMA accounting LOCATION ‘dbfs:/accounting/data’; Where will data for this database be stored?. dbfs:/accounting/data/accounting.db. dbfs:/accounting/data. dbfs:/accounting/data.db. dbfs:/user/hive/warehouse/accounting.db.

55. A data analyst is processing a complex aggregation on a table with zero null values and the query returns the following result: group_1 group_2 sum null null 100 A null 50 A ¥: 30 A Zz 20 B null 50 B ha 40 B Zz 1 Which query did the analyst execute in order to get this result?. SELECT group_1, group_2, SUM(values) AS sum FROM my_table GROUP BY group_1, group_2;. SELECT group_1, group_2, SUM(values) AS sum FROM my_table GROUP BY group_1, group_2 INCLUDING NULL;. SELECT group_1, group_2, SUM(values) AS sum FROM my_table GROUP BY group_1, group_2 WITH ROLLUP;. SELECT group_1, group_2, SUM(values) AS sum FROM my_table GROUP BY group_1, group_2 WITH CUBE;.

56. A managed table and an unmanaged (external) table were both created in Databricks SQL, and data were ingested into each table. Later, both tables were dropped. What is the status of data for each of those tables?. The data in both tables were deleted. The data in the managed table were deleted, and the data in the unmanaged (external) table were left untouched. The data in the unmanaged (external) table were deleted and the data in the managed table were left untouched, for both tables the metadata was the same. The data in both tables were left untouched.

57. Which statement about subqueries is correct?. Subqueries are not available in Databricks SQL. Subqueries can be used like other user-defined functions to transform data into different data types. Subqueries can retrieve data without requiring the creation of a table or view. D, Subqueries can be used like other built-in functions to transform data into different data types. Subqueries can be used like other built-in functions to transform data into different data types.

58. Which location can be used to determine the owner of a managed table?. Review the Owner field in the table page using Catalog Explorer. Review the Owner field in the database page using Data Explorer. Review the Owner field in the schema page using Data Explorer. Review the Owner field in the table page using the SQL Editor.

59. Which statement about visualizations is true?. All visualizations must use the same data in order to be included in the same Databricks SQL dashboard. Line charts are the preferred visualization type for categorical data,. Different visualizations can be used to tell different stories about the data. There is no difference between the bar chart and a histogram in Databricks SQL.

60. What describes the variance of a set of values?. Variance is a measure of how far a single observed value is from a set of values. Variance is a measure of how far an observed value is from the variable’s maximum or minimum value. Variance is a measure of central tendency of a set of values. Variance is a measure of how far a set of values is spread out from the set's central value.

61. Which SQL standards does Databricks SQL use as its default SQL dialect? A. Databricks SQL B, Spark SQL C. ANSI D, Delta SQL. Databricks SQL. Spark SQL. ANSI. Delta SQL.

62. A data analyst is working in an organization that utilizes a multi-hop, medallion architecture. They have been tasked with creating a new Gold table from an existing Silver table. Which query is performing a hop from a Silver table to a Gold table?. CREATE TABLE cleaned_transactions AS SELECT store_id, customer_id, sales, units FROM transactions;. CREATE TABLE transactions AS SELECT * FROM raw_transactions;. CREATE TABLE store_sales AS SELECT store_id, SUM(sales) AS store_sales FROM cleaned_transactions GROUP BY store_id;. CREATE TABLE cleaned_transactions AS SELECT * FROM transactions WHERE units > 0;.

63. What is used as a compute resource for Databricks SQL?. Standard clusters. Single-node clusters. SQL warehouses. Downstream BI tools integrated with Databricks SQL.

64. A data analyst has opened up the SQL Editor page and written a new SQL statement. The data analyst now wants to save that statement to easily refer back to it later to add it to a dashboard. The results of the SQL statement must be able to be displayed as a counter, table, or a data visualization. Which approach should the data analyst use to accomplish this task?. Save the SQL statement as a Dashboard. Save the SQL statement within a Notebook. Save the SQL statement as a Query D, Save the SQL statement in the Query History page. Save the SQL statement in the Query History page.

65. A data analyst wants to create a Databricks SQL dashboard with multiple data visualizations and multiple counters. What must be completed before adding the data visualizations and counters to the dashboard?. All data visualizations and counters must be created using Queries. A SQL warehouse (formerly known as SQL endpoint) must be turned on and selected. A markdown-based tile must be added to the top of the dashboard displaying the dashboards name. The dashboard owner must also be the owner of the queries, data visualizations, and counters.

66. ‘An analyst has been asked to combine the data in two tables: suppliers and new_suppliers. It’s possible that some of the supplier_id values match in both tables, meaning that those particular suppliers have already been added to the suppliers table. If that is the case, the data should be unchanged. Which command will combine the two tables without duplicating the rows with the same supplier_id?. MERGE INTO suppliers - USING new_suppliers - ON suppliers.supplier_id = new_suppliers.supplier_id WHEN NOT MATCHED THEN INSERT *. COPY INTO suppliers - USING new_suppliers - ON suppliers.supplier_id = new_suppliers.supplier_id WHEN NOT MATCHED THEN INSERT *. UPDATE suppliers - USING new_suppliers - ON suppliers. supplier_id = new_suppliers.supplier_id WHEN NOT MATCHED THEN INSERT *. INSERT INTO suppliers - USING new-suppliers - ON suppliers.supplier_id = new_suppliers.supplier_id WHEN NOT MATCHED THEN INSERT *.

67. What does Partner Connect do when connecting Power BI and Tableau?. Creates a Personal Access Token, downloads and installs an ODBC driver, and downloads a configuration file for connection by Power BI or Tableau to a SQL Warehouse (formerly known as a SQL Endpoint). Creates a Personal Access Token for authentication into Databricks SQL and emails it to you. Downloads a configuration file for connection by Power BI or Tableau to a SQL Warehouse (formerly known as a SQL Endpoint). Downloads and installs an ODBC driver.

68. A data analyst is working with a nested array column products in table transactions. The analyst wants to return the first item in the array for each row. The data analyst is using the following incomplete command: SELECT transaction_id, AS first_product FROM transactions; Which lines of code should the data analyst use to fill in the blank in the above code block so that it successfully completes the task?. products.1. products.0. products{0]. products[1].

69. A dashboard contains a bar chart visualization. The bar chart has a Date widget parameter associated with it. Which statement about the Date widget parameter is true?. The Date widget parameter provides a start date and an end date that is used by a WHERE clause in the query that provides data to the bar chart. The Date widget parameter provides a start date and an end date that sets the x-axis range of the bar chart. The Date widget parameter provides a start date and an end date that filters all visualizations in the dashboard. The Date widget parameter functions only as a text box that tells the dashboard’s users what date range the dashboard is using.

70. A data analyst needs to share a Databricks SQL dashboard with stakeholders that are not permitted to have accounts in the Databricks deployment. The stakeholders need to be notified every time the dashboard is refreshed. Which approach can the data analyst use to accomplish this task with minimal effort?. By granting the stakeholders’ email addresses permissions to the dashboard. By adding the stakeholders’ email addresses to the refresh schedule subscribers list. By granting the stakeholders’ email addresses to the SQL Warehouse (formerly known as endpoint) subscribers list. By downloading the dashboard as a PDF and emailing it to the stakeholders each time it is refreshed.

71. What describes Partner Connect in Databricks?. Itallows for free use of Databricks partner tools through a common API. It allows multi-directional connection between Databricks and Databricks partners easier. It exposes connection information to third-party tools via Databricks partners. It is a feature that runs Databricks partner tools on a Databricks SQL Warehouse (formerly known as a SQL Endpoint).

72. In which scenario should a data analyst create and use a user-defined function (UDF)?. When the result of logic needs to be restricted to certain users. When logic needs to be deployed within a subquery. When complex logic cannot be implemented with built-in capabilities. When simple logic needs to be optimized for scaling purposes.

73. A data analyst has produced a visualization. A stakeholder has viewed the visualization and is complaining that the visualization is difficult to interpret. After looking at the visualization, the analyst determines that the scale of the y-axis must be changed. Where are the controls for changing the scale of the y-axis in Databricks SQL?. Query Editor-> Y Axis. Dashboard Editor -> Axes -> Y Axis. Visualization Editor -> ¥ Axis. Settings > User Settings > Scaling.

74. Which example of data projects represents a common analytics application to be completed in Databricks SQL?. Testing the latency of real-time streaming workloads. Performing analysis-specific ETL on gold-layer tables. Predicting customer churn by automatically developing time-series models. Monitoring the performance of deployed machine learning solutions.

75. A data analyst has two data sources that are providing similar but complementary information. The analyst wants to combine these sources of data into a single, comprehensive dataset for ongoing use for their team in a variety of different projects. Which terms is used to describe this type of work?. Last-mile ETL. Ad-hoc improvements. Data testing. Data blending.

76. A data analyst has been asked to determine which customers have placed an order in the past year and which customers have not placed an order in the past year. This can be determined with one statement by joining the customers table (which is unique by customer ID) and the orders table. Which type of join should the data analyst use?. Left Semi Join. Left Join. Middle Join. Left Anti Join.

77. A data analyst has come across a column in a table that contains personally identifiable information (Pll). The data analyst should not have access to this type of Pll data How should the data analyst proceed?. Stop working with the data and delete the table and any existing metadata or data files. Stop working with the data and proceed with the project using other data. Stop working with the data and notify their supervisor to ensure the data is handled following organizational and legal best practices. Drop the column containing Pll data and continue with the project without notifying anybody.

78. A data engineering team has created a Structured Streaming pipeline that processes data in micro-batches and populates gold-level tables. The microbatches are triggered every 10 minutes. A data analyst has created a dashboard based on this gold-level data. The project stakeholders want to see the results in the dashboard updated within 10 minutes or less of new data becoming available within the gold-level tables. What is the ability to ensure the streamed data is included in the dashboard at the standard requested by the project stakeholders?. Arefresh schedule with an always-on SQL Warehouse (formerly known as SQL Endpoint). A refresh schedule with an interval of 10 minutes or less. Arefresh schedule with stakeholders included as subscribers. Arefresh schedule with a Structured Streaming cluster.

79. Where in the Databricks SQL workspace can a data analyst configure a refresh schedule for a query when the query is not attached to a dashboard or alert?. Data Explorer. The Visualization Editor. The Query Editor. The Dashboard Editor.

80. A stakeholder has provided a data analyst with a lookup dataset in the form of a 50-row CSV file. The data analyst needs to upload this dataset for use as a table in Databricks SQL. Which approach should the data analyst use to quickly upload the file into a table for use in Databricks SQL?. Create a table by uploading the file using the Create page within Databricks SQL. Create a table via a connection between Databricks and the desktop facilitated by Partner Connect. Create a table by uploading the file to cloud storage and then importing the data to Databricks. Create a table by manually copying and pasting the data values into cloud storage and then importing the data to Databricks.

81. Which open-source project helps to enable the data lakehouse by adding organization, reliability, performance, and data governance to data lake architectures? A.MLflow. MLflow. Apache Spark. Delta Lake. Databricks SQL.

82. Which data visualization use cases is best completed using Databricks SQL relative to other visualization tools?. Presentation-grade visualizations for publication. Organization-branded visualizations for marketing material. Custom, interactive visualizations on small data. Simple, exploratory visualizations on big data.

83. A data analyst has written and saved a series of queries that reveal trends that need to be monitored by several stakeholders Which too! should the data analyst use to share the results of all of the queries to be viewed at once?. ASQL warehouse (formerly known as a SQL endpoint). A Query History page. A dashboard. A data visualization tab on a Query page.

84. A data analyst needs to create an empty managed table table_name in database database_name with a specific schema. The table needs to be recreated and empty, regardless of whether or not the table already exists Which command can the analyst use to complete the task?. CREATE TABLE database_name.table_name USING (width INT, length INT, height INT);. CREATE OR REPLACE TABLE database_name.table_name USING (width INT, length INT, height INT);. CREATE OR REPLACE TABLE database_name.table_name (width INT, length INT, height INT);. CREATE OR REPLACE TABLE table_name FROM database_name USING (width INT, length INT, height INT);.

85. Data professionals with varying responsibilities use the Databricks Lakehouse Platform Which role in the Databricks Lakehouse Platform use Databricks SQL as their primary service?. Data scientist. Data engineer. Platform architect. Business analyst.

86. A data analyst is working with the following table my_table: customer_name dollars_spent Hex Sprockets [125.34, 100.15, 9003.99] Dented Fenders [16.99, 200.85, 33.49, 88.17] ‘The analyst now wants to divide each value in the dollars_spent array by 100 to get the spend in terms of hundreds of dollars using the following code block: SELECT - customer_name, FROM my_table- Which line of code can be used to fill in the blank so that the above code block successfully completes the task?. TRANSFORM(hundreds_spent, dollars_spent / 100). TRANSFORM(dollars_spent, value / 100) AS hundreds_spent. TRANSFORM(dollars_spent, value-> value / 100) AS hundreds_spent. TRANSFORM(dollars_spent, dollars_spent / 100) AS hundreds_spent.

87. Where can an admin or Data owner grant database, table, and view permissions to a group?. Dashboard. Data. SQL Warehouses (formerly SQL Endpoints). Settings.

88. A data architect has informed a data analyst team that its organization will now use a data design pattern that will logically organize data in a lakehouse, with the goal of incrementally and progressively improving the structure and quality of data as it flows through each layer of the architecture. Which term is used to describe this data design pattern?. Medallion architecture. Change data feed. Delta Lake. Data mesh.

89. Which Databricks SQL action is used to audit and view the history of operations performed on a Delta Lake table in Databricks?. Runa SELECT logs FROM table_name query. Use the SHOW TRANSACTIONS table_name command. Execute a VACUUM table_name statement. Use the DESCRIBE HISTORY table_name command.

90. Despite having SELECT permission on tables and USAGE on schemas, a data analyst is unable to access the objects. Which action enables the data analyst to access the objects?. Grant OWNERSHIP of the table to the Data Analyst. Grant MANAGE DATABASE access on the containing catalog. Grant MANAGE TABLE access. Grant USE CATALOG on the containing catalog.

91. A data analyst is reviewing the status of alerts in the Databricks SQL Alerts listing page. One alert was recently executed, and the monitored value met the defined condition and exceeded the threshold, meaning the alert criteria were met. Which status should appear for this alert in the listing?. Status: YES. Status: TRIGGERED. Status: OK D, Status: SUCCESS. Status: SUCCESS.

92. A data analyst wants to compare the contents of a Delta table as they existed last week to the current state in order to track changes in sales records. Which Delta Lake feature enables this comparison?. RESTORE command. VACUUM retention settings. Time travel querying. CLONE with VERSION AS OF.

93. A data analyst wants to generate insights from large, complex datasets. The analyst needs to quickly understand the meaning of various data columns, ask questions in natural language, and receive Al-driven recommendations for optimizing data queries and workflows. Which Databricks component is primarily responsible for enabling these capabilities?. Data Intelligence Engine. Unity Catalog. Genie Spaces. Databricks Assistant.

94. A data analyst filters rows where the tags array includes the value ‘sql using this query: SELECT * FROM main.analytics. articles WHERE tags = ‘sql’; This query returns no results. How should the analyst query to filter for rows where the tags array contains ‘sql’? A, SELECT * FROM main.analytics.articles WHERE tags IN (‘sql’);. SELECT * FROM main.analytics.articles WHERE tags IN (‘sql’);. SELECT * FROM main.analytics. articles WHERE tags LIKE "%sql%';. SELECT * FROM main.analytics.articles WHERE ‘sql’ IN tags;. SELECT * FROM main.analytics.articles WHERE array_conatins (tags, ‘sq’;.

95. A data analyst needs to programmatically mark a Unity Catalog table, sales_data, as certified so that it is discoverable as a trusted asset across their organization. Which SQL command can be used to meet the requirement?. ALTER TABLE sales_data SET TAGS (‘system.Certified’);. ALTER TABLE sales_data SET TAGS (‘certified’ = ‘true’);. GRANT CERTIFY ON TABLE sales_data TO analyst_group;. UPDATE TABLE sales_data ADD COMMENT ‘Certified dataset’;.

96. A data analyst is troubleshooting a query in Databricks SQL that fails when processing large datasets and complex join operations. Logs indicate that the job consistently aborts due to resource constraint errors on the cluster. Which Query Profile metric should the analyst use to identify the operator that is causing resource overuse?. Time spent per operator. Shuffle read size per operator. Memory peak per operator. Bytes spilled to disk per operator.

97. A data analyst encounters an unclear error while running a SQL query in the Databricks SQL Editor. The analyst wants to quickly understand and resolve the issue How can Databricks Assistant help the analyst diagnose and fix the error?. Databricks Assistant requires switching to a notebook environment to provide any debugging support. Databricks Assistant only supports Python code and cannot help with SQL queries. Databricks Assistant can only create new queries and does not assist with debugging. Databricks Assistant can analyze the error, suggest solutions, and provide corrected code directly in the SQL Editor.

98. A BI analyst is building an analytical data model in Databricks using Delta Lake tables. The source system contains transactional sales data that changes frequently. The analyst chooses to apply the Data Vault 2.0 methodology to manage historical changes while ensuring scalability and auditability across multiple business domains. Which component is used to capture the many-to-many relationship between hubs in a Data Vault v2 model?. Hub Table. Satellite Table. Link Table. Reference Table.

99. A data analyst is using Databricks Unity Catalog. The datasets are tagged by sensitivity, and confidential data is marked with the tag key confidential. The data analyst needs to quickly find all tables tagged as confidential to review their access permissions in the Databricks workspace search bar. Which search key text should the data analyst use to find these tables?. confidentialtrue. tag=confidential. tag:confidential. search tag = ‘confidential’.

100. How are analytical models mapped to the Gold layer in the Delta Lake medallion architecture on Databricks?. The Gold layer is primarily used for storing backup copies of all data for disaster recovery purposes. The Gold layer stores raw, unprocessed data for initial ingestion and schema evolution,. The Gold layer is used for data cleaning, deduplication, and joining across domains to establish data integrity. The Gold layer contains de-normalized data models optimized for analytics, reporting, and machine learning.

101. A data analyst team is building reliable, production-grade data pipelines on Databricks. They require a solution that allows them to define data transformations declaratively, automatically enforce data quality expectations, and handle errors without manual intervention. Which Databricks capability addresses these requirements?. Lakeflow Tables. Lakeflow Spark Declarative Pipelines. Lakeflow Jobs. Lakeflow Connect.

102. A data analyst has configured a Delta table with default retention settings. The analyst runs VACUUM operations weekly to manage storage costs. Abusiness user asks to access data from 10 days ago for a compliance audit. What is the result of a time travel query in this scenario?. The time travel query will succeed because Delta Lake automatically backs up data for compliance purposes, regardless of VACUUM operations. The time travel query will succeed because the default log retention period is 30 days, which Is sufficient to access 10-day-old data. The time travel query will fail because the default maximum table version is 7 and numbering resets after each VACUUM operation, making historical versions inaccessible. The time travel query will fail because the default data file retention period is 7 days, and VACUUM operations have likely removed the required data files.

103. A data analyst is preparing a dataset that includes customer information such as email addresses and birthdates. This dataset will be used by multiple teams for generating reports, but some teams should not have permissions to access sensitive data. The analyst must ensure the data is stored securely and that personally identifiable information (Pll) is protected according to organizational standards and best practices in Databricks Which action should the analyst take to secure the Pll data while allowing safe reporting access to other teams?. Grant SELECT privileges to all report writers for the required subset of data from the base table. Use Dynamic Views with IS_MEMBER() functions to restrict Pll columns based on user roles. Store the Pll data in a shared CSV file on DBFS and apply access control at the folder level. Mask Pll data by obfuscating it in the source table before conducting analysis.

104. A data analyst is working in a Databricks workspace with Unity Catalog enabled. While using a shared SQL Warehouse, the analyst runs the following query: SELECT * FROM sales_data; This query results in a “TABLE_OR_VIEW_NOT_FOUND" error, even though: + The table sales_data exists in the reporting schema of the corp_data catalog. + The analyst has SELECT and USAGE privileges on the corp_data.reporting.sales_data table. + Another user running the same query in a notebook attached to a cluster is able to retrieve results without fully qualifying the table. Which action helps the analyst to resolve the issue without rewriting the query?. Set the spark.databricks. q].initial.catalog.namespace Spark configuration in the cluster attached to the SQL Warehouse. Run the following command to set the default catalog and schema for the current session: USE CATALOG corp_data; USE SCHEMA reporting;. Modify the workspace-level catalog default so that all SQL Warehouses automatically point to corp_data.teporting. Set spark.sql.default. database to corp_data.reporting.sales_data in the SQL Warehouse runtime configuration.

105. How are privileges inherited in Unity Catalog across the hierarchy levels of metastore, catalog, schema, and table/view?. Privileges granted at a higher level (such as a catalog) automatically apply to all child objects (schemas and tables) unless overridden by more specific permissions at a lower level. Privileges are only inherited from the metastore level, and no inheritance occurs between catalogs, schemas, or tables. Privileges must be granted individually for each object, regardless of their position in the hierarchy, to ensure secure access control. Privileges granted at the schema level have no impact on tables or views in that schema unless directly applied to them.

106. A data analyst needs to create a table in Unity Catalog for a production analytics workload. The table will be frequently queried and needs automatic performance optimization. The data should be governed by Unity Catalog with lifecycle management. Which approach should the data analyst use?. Create a foreign table using CREATE FOREIGN TABLE with external connections to maintain compatibility with external systems while keeping data in original location and get benefits like Liquid Clustering and Deletion Vectors. Create a managed table using CREATE TABLE catalog.schema.table_name (columns...) allowing Unity Catalog to manage the data lifecycle, storage location, and provide automatic optimizations like Predictive Optimization. Create a temporary table using CREATE TEMPORARY TABLE for better performance and then convert it to a managed table later using ALTER TABLE SET MANAGED. Create an external table using CREATE EXTERNAL TABLE catalog.schema.table_name LOCATION ‘s3://bucket/path/" to maintain control over the data location while benefiting from Unity Catalog governance.

107. A data analyst team is working with large datasets stored as Delta tables (Parquet format) in Databricks on Azure Data Lake. The teams want to speed up query performance and reduce repeated computation by leveraging caching. However, they notice that sometimes caching does not improve performance ot even slows down queries. The team wants to understand the best practices and limitations of caching in Databricks to optimize their workflows. What is the limitation when using caching in Databricks to reduce development time and query latency?. Query result caching in Databricks SQL works best with non-deterministic queries that use functions like NOW() or RAND(). Caching CSV or JSON files with Delta Cache is fully supported and recommended for best performance. Delta Cache (disk cache) automatically caches Parquet files on local SSDs and improves read performance, but only supports Parquet files stored on Azure Blob Storage or Azure Data Lake. Spark caching always improves performance and should be applied liberally to all dataframes regardless of size or query complexity.

108. A data analyst needs to share a certified dataset containing approved public economic indicators with an external research institution. The institution does not have access to the Databricks workspace. The dataset is managed in Unity Catalog and has already been reviewed and tagged appropriately. The analyst's requirements are + Share the dataset securely and read-only + Ensure the institution receives only the specified tables + Avoid giving access to the full catalog or workspace + Maintain auditability and governance using Unity Catalog How should the analyst share the dataset?. Grant the external users access to Unity Catalog by adding their emails to the workspace. Use COPY INTO to export the dataset into a managed Delta table, then use Unity Catalog's GRANT SELECT to provide access to the institution. Use the GRANT SELECT command to give the external group's principal access to the table. Create a Delta Share, add the required table, define a recipient, and ensure that a Unity Catalog metastore admin or privileged user in the recipient's workspace grants appropriate access permissions to their users.

109. A data analyst is monitoring delivery delays in real time using a Databricks dashboard shared with the operations team. After a meeting, the manager asks to add a new widget that graphically displays, with a gauge, the percentage of on-time shipments compared to the total, The data analyst needs to quickly modify the dashboard to add this information element. How should the analyst add a new widget to an existing dashboard in Databricks?. Export the data from a notebook as a static image and upload it to the dashboard as a visual reference. Ask a platform administrator to add a materialized view for the on-time percentage, since visualizations require pre-aggregated values from the source to display correctly. Click “Add Visualization’ in the dashboard, select the desired type, and associate a query that calculates the requested percentage. Clone the existing dashboard, add the new visualization in the duplicate, and replace the original dashboard after review.

110. A data analyst needs to improve the performance of a daily sales report that aggregates data from a large transactions table. The report is run multiple times a day, but the underlying data only changes once daily. Which approach should the analyst use to improve performance while minimizing redundant computation?. Use a standard view and schedule a notebook to cache results. Create a materialized view that precomputes the dally sales aggregation. Use a dynamic view to compute the aggregation on each query. Set up a streaming table to ingest new sales data in real time.

111. A data analyst discovers that a group named developers has unauthorized access to sensitive data within the Unity Catalog catalog finance_catalog, which should be accessible only by the finance team. Which SQL command should the analyst use to remove the developers group's access to the catalog?. GRANT USAGE ON CATALOG ‘finance_catalog’ TO ‘finance’;. ALTER CATALOG ‘finance_catalog’ DROP PERMISSIONS FOR ‘developers’;. REVOKE USAGE ON CATALOG ‘finance_catalog’ FROM ‘developers’; D, REVOKE USE ON CATALOG ‘finance_catalog’ FROM ‘developers’;. REVOKE USE ON CATALOG ‘finance_catalog’ FROM ‘developers’;.

112. A data analyst needs to join two tables, orders and customers, using both customer_id and region_id as join keys. The goal is to return only rows where both values match in the two tables. Which SQL query correctly performs this join?. SELECT * FROM orders INNER JOIN customers ON orders.customer_id = customers. customer_id;. SELECT * FROM orders INNER JOIN customers USING (customer_id, region_id);. SELECT * FROM orders INNER JOIN customers ON orders.customer_id = customers.customer_id AND orders.region_id = customers.region_id;. SELECT * FROM orders LEFT JOIN customers ON orders.customer_id = customers. customer_id AND orders.tegion_id = customers. region_id;.

113. A data analyst is reviewing a Genie space used by operations managers and notices that multiple responses have been flagged as inaccurate. Which action should the analyst take to improve performance?. Add more tables and columns to the space because increasing the data volume improves Genie's ability to find the correct answers. Update the instructions and add example SQL queries because this guides Genie to generate correct and context-aware responses. Turn off the feedback feature because it prevents noise from influencing Genie’s learning mechanism. D, Delete the flagged responses because this clears prior data that is biasing Genie's answer generation. Delete the flagged responses because this clears prior data that is biasing Genie's answer generation.

114. A data analyst needs to programmatically mark the sales_data table in Unity Catalog as deprecated so that users across their organization can clearly see that it is no longer recommended for use. Which SQL command should they use?. ALTER TABLE sales_data SET TAGS (‘deprecated’ = ‘true’);. UPDATE TABLE sales_data ADD COMMENT ‘Deprecated dataset’;. ALTER TABLE sales_data SET TBLPROPERTIES (‘system. certification_status'="deprecated’);. ALTER TABLE sales_data SET TAGS (‘system.certification_status’ = ‘deprecated’);.

115. A data analyst is curating a Genie space for the marketing team to explore campaign performance data. To ensure accurate and context-aware responses, the analyst includes curated Unity Catalog tables, writes clear domain instructions, and adds sample questions. Which additional step can the analyst take to maximize trust and reuse of frequently asked queries?. Disable the use of domain instructions to prevent bias in Al-generated answers. Mark commonly used queries as Trusted Assets after verifying their correctness. Enable automatic query generation so users receive instant responses without review. Allow users to edit the underlying datasets to improve Genie responses.

116. A data analyst wants to identify product categories that meet two criteria: 1. Each category contains more than 10 products. 2. The average price of products in the category is greater than $50. Which SQL statement applies aggregate functions to return only the categories that meet these requirements? A, SELECT category, COUNT(*) FROM products GROUP BY category HAVING COUNT(*) > 10 AND AVG(price) > 50;. SELECT category, COUNT(*) FROM products GROUP BY category HAVING COUNT(*) > 10 AND AVG(price) > 50;. SELECT category, COUNT(*) FROM products WHERE COUNT(*) > 10 GROUP BY category HAVING AVG(price) > 50;. SELECT category, COUNT(*) FROM products HAVING COUNT(*) > 10 GROUP BY category WHERE AVG(price) > 50;. SELECT category, COUNT(*) FROM products GROUP BY category WHERE COUNT(*) > 10 AND AVG(price) > 50;.

117. A data analyst wants to implement a use case that requires managing both structured and unstructured data How will Unity Catalog help in this scenario?. It allows for the management of both structured data and unstructured data through objects such as tables and volumes. B, It requires all unstructured data to be converted into Delta Lake format before it can be managed. It requires all unstructured data to be converted into Delta Lake format before it can be managed. It can catalog data coming from relational databases, excluding files and non-tabular data. It manages structured data in tabular format and unstructured files like images in blob storage.

118. A data analyst is working with a Unity Catalog table named sales_data that contains customer order information. The order_quantity column is defined as an INTEGER, but occasionally has values of -1, indicating a data entry error. To support accurate reporting, the analyst needs to create a new view named clean_sales_data, that excludes these erroneous entries. Which SQL query creates the clean_sales_data view by filtering out records where order_quantity is -1?. CREATE VIEW clean_sales_data AS SELECT * FROM sales_data WHERE order_quantity IS NOT NULL AND order_quantity > 0;. CREATE VIEW clean_sales_data AS SELECT * FROM sales_data WHERE ABS(order_quantity) != 1;. CREATE VIEW clean_sales_data AS SELECT * FROM sales_data HAVING order_quantity != -1;. CREATE VIEW clean_sales_data AS SELECT * FROM sales_data WHERE order_quantity != -1;.

119. A data analyst is building a parameterized SQL query in Databricks to filter sales data by region and year. The analyst wants to test the query with different parameter values before adding it to a dashboard. Which approach should the analyst use to allow for dynamic testing of the parameters in the Databricks SQL editor?. Hard-code the region and year values directly into the SQL query and rerun it for each test. Use parameter markers (€.g., :region, :year) in the SQL query and enter their values in the input boxes before running the query. Replace the column names (e.g., region, year) with question marks ("?") and expect Databricks to prompt for values at runtime. Add the parameters as variables in a Python notebook celll and reference them in the SQL query.

120. What is the primary role of a SQL Warehouse in Databricks?. SQL Warehouses are primarily used for ETL operations and data transformations, offering better performance for batch processing compared to interactive queries. SQL Warehouses are specialized compute resources designed specifically for SQL query execution, powered by the Photon engine for accelerated performance and optimized for BI tool integration. SQL Warehouses are general-purpose compute resources that support Python, Scala, R, and SQL workloads, with automatic library management and custom runtime configurations for enhanced performance. SQL Warehouses function as data storage systems that maintain cached query results and provide direct access to Delta tables without requiring additional compute resources.

121. A data analyst is setting up a Genie space in Databricks. Which sequence should the analyst use for defining reasonable sample questions to help guide user interactions and improve Genie's accuracy?. Create a dedicated notebook for each sample question, write the corresponding SQL query in the notebook, and link these notebooks to the Genie space using a workspace object reference. Navigate to “Configure” > “Settings” and enter the sample questions directly into the "Description" field. Genie automatically parses this text to guide users. Upload a spreadsheet with sample questions to the space, manually map each question to a SQL query in a separate document, and periodically update the mappings as new questions arise. Click "Configure" > “Context” in the Genie space, use the “SQL Queries” tab to add sample questions and matching SQL, then review and resolve any inconsistencies in the guidance provided.

122. A data analyst has noticed that a key dashboard is loading slowly and suspects a specific SQL query may be causing performance issues. To diagnose the root cause, the analyst wants to review query execution characteristics, such as execution time, data scanned, and rows processed over time. The analyst is using the Databricks SQL environment and has access to admin tools and workspace objects. How should the analyst investigate query performance over time?. Use the SQL Dashboard Editor to identify slow-performing queries by reviewing visualization types and layout. Use Lakehouse Monitoring to view real-time alerts and trends across all data pipelines that run in Unity Catalog. Use the Query Profiler to examine long-term trends in query performance across multiple executions. Use Query Insights to view metrics, such as duration, rows read, and execution history for the query.

123. ABl analyst has created a Genie space containing customer sales data and needs to share it with external stakeholders who are not workspace members, The stakeholders require the ability to ask questions about the data but should not have access to the underlying Databricks workspace or compute resources. How should the BI analyst share the Genie space with external stakeholders?. Share the Genie space with embedded credentials and assign the stakeholders as account users with published dashboard access. Export the Genie space data to CSV files and share via email with instructions to upload to their own Databricks workspace. Create individual workspace accounts for each stakeholder and assign Databricks SQL access entitlement with CAN RUN permissions. Grant the stakeholders workspace membership with Consumer access entitlement and CAN VIEW permissions on the Genie space.

124. A company uses a star schema to analyze sales data. The sales fact table records transactions, and dimension tables include products, stores, and dates. How are primary and foreign keys used to enable efficient joining between these tables?. The fact table and dimension tables are joined using arbitrary columns, and primary/foreign key relationships are not required in a star schema. The fact table contains primary keys that directly match the primary keys in each dimension table, allowing for one-to-one joins. Both fact and dimension tables use only foreign keys, and joins are performed using non-unique columns. Each dimension table has a primary key, which is referenced as a foreign key in the fact table. This structure allows the fact table to join with multiple dimension tables for analytical queries.

125. ABl analyst is designing a Databricks SQL dashboard that tracks monthly revenue for three product categories across two years. Leadership wants a chart that makes it straightforward to compare each category's trend over time and quickly spot seasonal patterns or declines without relying on additional filters or calculations. Which visualization type in Databricks SQL will clearly communicate the revenue trends and category comparisons described?. Multi-series line chart plotting month on the x-axis and revenue on the y-axis for each category. Single-value counter displaying combined revenue for the entire two-year period. Pie chart showing each category's percentage of total revenue for the final month. Scatter chart with revenue on the x-axis and profit margin on the y-axis for all transactions.

126. A data engineer is ingesting large volumes of semi-structured log data stored in an $3 bucket into Databricks. The data arrives continuously throughout the day, and the engineer needs to implement an efficient, scalable solution that supports incremental loads, handles schema changes gracefully, and minimizes manual intervention. Which approach should be used for ingesting this data?. Build a custom Spark Structured Streaming job that tracks processed files via checkpointing and manual schema management. Use Auto Loader to incrementally ingest data from S3. Build a custom API-driven ingestion pipeline to pull data from $3. Use Delta Sharing to share data with Databricks from external systems.

127. A data analyst needs to quickly estimate the number of unique user sessions in a dataset containing billions of records on Databricks SQL. Due to performance constraints, the analyst must ensure the query runs efficiently and delivers results with acceptable accuracy. What is the primary advantage of using APPROX_COUNT_DISTINCT instead of COUNT_DISTINCT in this scenario?. APPROX_COUNT_DISTINCT provides exact counts, while COUNT DISTINCT only provides estimates. APPROX_COUNT_DISTINCT can only be used with string columns, while COUNT DISTINCT works with numeric columns. APPROX_COUNT_DISTINCT uses the HyperLogLog++ algorithm to provide fast approximate counts with configurable accuracy (default 5% relative standard deviation). APPROX_COUNT_DISTINCT requires less memory by storing only the first 1000 unique values.

128. A data analyst is evaluating options to optimize query performance on a large table with frequently changing query filters. The analyst is considering using Liquid Clustering versus traditional Hive-style partitioning. What is the primary advantage of Liquid Clustering over traditional partitioning for tables with evolving query patterns?. Liquid Clustering provides better compression ratios than partitioned tables, reducing storage costs. Liquid Clustering allows changing clustering columns without rewriting existing data, while partitioning requires reprocessing and rewriting data to change the partitioning structure. Liquid Clustering automatically creates backup copies of data files, while partitioning does not. Liquid Clustering supports more data types than partitioning, including nested structures and arrays.

129. A data analyst is configuring a Genie space to support natural language questions about product sales, including fields like region_code, sku, and channel_type. The analyst notices that Genie often generates ambiguous or incorrect queries due to unclear column meanings. Which two actions will help to improve Genie’s accuracy and relevance when interpreting user questions? (Choose two.). Provide sample values for ambiguous columns to help Genie infer the data's meaning and usage. Write domain-specific instructions that describe how terms like region and channel relate to the business. Add more columns from the underlying tables to give Genie access to a broader set of data points for reasoning. D, Add descriptions to columns using Unity Catalog so Genie can use them as context during query generation. Add descriptions to columns using Unity Catalog so Genie can use them as context during query generation. Include example queries that reflect how business users refer to the data in everyday language.

130. A data analyst is preparing a quarterly analytics dashboard in Databricks and needs to share it with external stakeholders who are not members of the Databricks workspace. The stakeholders should be able to view the dashboard securely without being granted workspace access or edit permissions. Which approach should the analyst use to meet the requirements?. Enable external sharing by publishing the dashboard, turning on embedded credentials, and sharing the link with stakeholders. Add the external stakeholders as workspace admins in the Databricks environment and grant them CAN MANAGE permissions on the dashboard. Assign the stakeholders to the All workspace users group and provide them with CAN EDIT permissions on the dashboard, even if they are not currently members of the workspace. Export the dashboard as a PDF file and email it to the stakeholders, since Databricks dashboards offer an export-to-PDF feature.

131. A data analyst is setting up a new AI/BI Genie space in Databricks to support natural language querying and ensure high-quality, governed responses. Which component is essential for configuring an effective Genie space that returns accurate and useful results?. An external data warehouse configured to store and replicate all data used by the Genie space. A standalone chatbot application integrated with Databricks through custom APIs for user interaction. Well-annotated datasets registered to Unity Catalog, curated sample queries, and domain-specific instructions to guide natural language translation. A dedicated physical server allocated for the Genie space to maximize availability and scalability.

132. A company has started using the Databricks Platform and currently has only one workspace. They need to manage data access across different teams and want to ensure proper isolation and security controls. Given this setup, which approach should be used?. Create a unique catalog with all schemas inside of it. Use Unity Catalog’s built-in environment separation features. Create a schema and share the schema for production, development, and testing. Create a catalog for production, another for development, and another for testing,.

133. A data analyst is exploring user engagement trends and wants to create a visualization while working interactively in a Databricks notebook. The analyst executes a SQL query using %sql and wants to turn the result into a line chart showing weekly user counts over time. Which step allows the analyst to create the chart directly in the notebook?. Save the query as a table, export it to Excel, and use Excel's charting tools. Wrap the SQL query in a Python plotly.express call using python. Use %sq] to run the query, then copy the result into a new dashboard to access visualization options. Use the display() function or %sql to run the query, then click the + icon in the result cell to add a visualization.

134. A data analyst needs to generate a sales report by joining a Unity Catalog-managed table with a table stored in a MySQL database. The analyst wants the join to be efficient, performed within Databricks, and governed using Unity Catalog permissions and lineage. Which approach should the data analyst use to meet these requirements?. Use Lakehouse Federation to create a foreign catalog for the MySQL database, then write a SQL query joining the Delta table and the federated MySQL table directly in Databricks. Write a Python script to connect to both sources separately, download the data and join them in memory outside Databricks. Fully ingest the MySQL table into Databricks and store it as a new Delta table before joining. Register the MySQL table as a temporary view in Databricks using a standard Spark JDBC read, then join it with the Unity Catalog-managed table using Databricks SQL.

135. A data analyst is enabling analytics on raw Parquet files stored in an external Amazon $3 bucket. The files must be queried directly using Databricks SQL without duplicating the data. The analyst also wants to register the table with Unity Catalog to enforce data governance policies such as access control and lineage tracking. Which SQL command should the analyst use to create a Unity Catalog-enabled external table that directly references the Parquet files?. CREATE TABLE catalog.schema.table_name USING PARQUET AS SELECT * FROM parquet’'s3://my-bucket/data/’,. CREATE TABLE catalog.schema.table_name OPTIONS (‘path’ ‘s3://my-bucket/data/') STORED AS PARQUET;. CREATE EXTERNAL TABLE catalog. schema.table_name USING DELTA LOCATION ‘s3://my-bucket/data/’;. CREATE TABLE catalog.schema.table_name USING PARQUET LOCATION ‘s3://my-bucket/data/’;.

136. A data analyst is migrating data lakehouse workloads to Databricks. The analyst manages a large table with thousands of columns and several billion rows. The analyst is concemed about scan performance, query speed, and handling the overhead of processing such a large volume of data. In which two ways does the Photon engine help the team address these challenges? (Choose two.). Photon uses a caching layer to transcode data into a CPU-efficient format for faster scan performance. Photon processes data row by row for maximum compatibility with legacy systems. Photon automatically parallelizes all queries across multiple clusters without additional configuration. Photon automatically merges small files into a single large file before processing. E, Photon leverages a columnar, vectorized execution engine for efficient scan and aggregation operations. Photon leverages a columnar, vectorized execution engine for efficient scan and aggregation operations.

137. An analyst needs external datasets and analytics assets to enrich their lakehouse reporting. The organization uses Databricks with Unity Catalog enabled and wants fast, governed access to third-party data without complex ETL or replication. What is the benefit of using Databricks Marketplace in this context?. It provides a marketplace for purchasing compute resources and serverless SQL warehouses that are managed outside the Unity Catalog framework. It enables scheduling of ETL pipelines to load partner data into proprietary formats before use in Databricks SQL. It allows the BI Analyst to copy external datasets into their workspace for offline analysis, eliminating dependency on provider access. It enables direct, governed access to live external data, notebooks, models, and dashboards via Delta Sharing—without data replication.

138. A data analyst uses Databricks to create a dashboard that monitors daily sales volumes and store performance. To ensure that area managers always see up-to-date data without having to manually intervene, the data analyst wants to automate the dashboard data refresh. Which process should the data analyst use to automatically refresh the data displayed, avoiding manual intervention?. Set the Dashboard query to ‘auto-update’ by using a browser extension that reloads the tab at set intervals. Export the dashboard as a PDF the night before, so the team can see "recent" data the next day. Use the dashboard’s builtin scheduling option to set an automatic refresh at regular intervals. Ask all users to press the “Refresh” button on the dashboard whenever they want updated data.

139. A data analyst needs to automate scheduled ETL jobs that ingest and transform data from multiple sources. The analyst wants to use multiple programming languages for flexibility and ensure that the pipeline can automatically handle schema changes as new data arrives. Which approach in Databricks should be used to meet this requirement?. Databricks Workflows with Apache Spark notebooks and manual schema management. Unity Catalog with manual ETL scripts and cron-based scheduling. Separate Delta tables for each data source with fixed schemas. Lakeflow Spark Declarative Pipelines with Auto Loader.

140. A data analyst is given a sales table with the columns product id, sale_date, and sale_amount. The analyst's goal is to calculate the total sales per product per day, with exactly one row per product per day. They first try the following SQL query: SELECT product id, sale_date, sale_amount, SUM(sale_amount) OVER (PARTITION BY product_id ORDER BY sale_date) AS daily total FROM sales ORDER BY product id, sale_date; However, this produces multiple rows for the same product and date instead of one aggregated row per product/date combination. Which corrected SQL query will produce exactly one row per product per day with the total sales for that day?. SELECT product_id, sale_date, sale_amount, SUM(sale_amount) OVER (PARTITION BY product_id ORDER BY sale_date) AS daily_total FROM sales ORDER BY product_id, sale_date;. SELECT product_id, sale_date, sale_amount, SUM(sale_amount) OVER (ORDER BY product_id, sale_date) AS daily_total FROM sales ORDER BY product_id, sale_date;. SELECT product_id, sale_date, sale_amount, ROW_NUMBER() OVER (PARTITION BY product_id ORDER BY sale_date) AS row_num FROM sales ORDER BY product_id, sale_date;. SELECT product_id, sale_date, SUM(sale_amount) AS daily_total FROM sales GROUP BY product_id, sale_date ORDER BY product_id, sale_date;.