Databricks Certified Data Analyst Associate Test 2

Welcome to your Databricks Certified Data Analyst Test-2

Name

Phone

Q1. How can a dashboard be shared with up-to-date results?

Select 1 option(s):

By exporting it as a PDF file

By taking a screenshot

By copying and pasting the visualization

By sharing it as a link with appropriate permissions

Q2. What is the main benefit of configuring query parameters in Databricks SQL dashboards?

Select 1 option(s):

Query parameters enhance visualization aesthetics by applying dynamic color schemes.

Query parameters allow users to manually input data values, reducing the need for complex SQL queries.

Query parameters facilitate interactive exploration by enabling users to dynamically filter and customize visualizations, enhancing data analysis and insights.

Query parameters eliminate the need for data preprocessing, ensuring immediate visualization readiness.

Q3. How can you change access rights to a table using Data Explorer in Databricks?

Select 1 option(s):

Using SQL queries only

Through Databricks CLI

By modifying the transaction log

By changing permissions using Data Explorer

Q4. Scenario:

Select 1 option(s):

A Databricks analyst needs to create a user-defined function named calculate_discount to calculate the discounted price based on the original price and a discount percentage.
Question: What is the correct syntax to create the calculate_discount function?

CREATE FUNCTION calculate_discount(original_price DOUBLE, discount_percentage DOUBLE) RETURNS DOUBLE
RETURN original_price * (1 - discount_percentage);

FUNCTION calculate_discount(original_price DOUBLE, discount_percentage DOUBLE) RETURNS DOUBLE
RETURN original_price * (1 - discount_percentage);

CREATE DISCOUNT FUNCTION calculate_discount(original_price DOUBLE, discount_percentage DOUBLE) RETURNS DOUBLE
RETURN original_price * (1 - discount_percentage);

CREATE FUNCTION calculate_discount(original_price, discount_percentage) RETURNS DOUBLE
RETURN original_price * (1 - discount_percentage);

Q5. In Databricks SQL, when alerts are established according to particular criteria, what is the usual method for sending notifications to notify users or administrators about the triggered alerts?

Select 1 option(s):

Notifications are automatically sent to the dashboard‘s viewers via email when alerts are triggered.

Alerts trigger notifications via a variety of channels, such as email, Slack, or webhook integrations, based on the defined configuration.

Notifications are not supported for alerts in Databricks SQL Analytics.

Alerts generate a pop-up notification within the Databricks SQL Analytics interface, visible to all users.

Notifications are sent through SMS messages to designated phone numbers when alerts are triggered.

Q6. What behavior is exhibited by a dashboard parameter in Databricks SQL?

Select 1 option(s):

It allows users to edit the underlying queries

It dynamically changes the visualization data based on user input

It restricts access to the dashboard

It controls the dashboard layout

Q7. How can you monitor the performance of Databricks SQL queries?

Select 1 option(s):

Use the SQL Performance Insights dashboard.

View the query history and details.

Create alerts for slow queries.

All of the above

Q8. How does Delta Lake optimize query performance for filter predicates?

Select 1 option(s):

Applies predicate pushdown

Uses bitmap indexes

Implements index scan for all columns

Performs full table scans

Q9. In data analytics, identify a scenario where data enhancement would significantly improve the outcome.

Select 1 option(s):

During the initial stages of data collection where data volume is more critical than data quality.

When migrating data from one storage system to another without changing its format or content.

In a marketing campaign, to enrich customer data with additional demographic and psychographic information for targeted advertising.

When performing routine data backup and recovery processes.

When aggregating large volumes of data for storage efficiency without analysis.

Q10. How can you optimize a SQL query in Databricks?

Select 1 option(s):

Use indexes on frequently used columns.

Minimize the number of columns returned.

Avoid using subqueries if possible.

All of the above

Q11. A data analyst is creating a dashboard in Databricks SQL and wants to add visual appeal through formatting. Which aspect of visualization formatting allows the analyst to customize the information displayed when hovering over visual elements?

Select 1 option(s):

Modifying the font style

Changing the background color

Formatting tooltips

Adjusting alignment

Q12. Which SQL keyword is used to remove duplicates from a result set?

Select 1 option(s):

Q13. What is the purpose of a checkpoint in Databricks?

Select 1 option(s):

To save the state of a streaming query so it can be resumed later.

To store temporary data during a calculation.

To create a backup of a table.

None of the above

Q14. Databricks has put in place controls to meet the unique compliance needs of highly regulated industries.

Is there any data protection and compliance for the users based out of California?

Select 1 option(s):

CCPA

HIPAA

GDPR

PCI-DSS

Q15. A data analyst wants to create a temporary view in Databricks SQL for exploratory data analysis. What is a key characteristic of a temp view that distinguishes it from permanent tables?

Select 1 option(s):

Temp views are stored in memory and are automatically dropped after the session ends

Temp views can be shared across multiple users

Temp views are stored as physical files on disk

Temp views can be modified by other users

Q16. What is the main difference between discrete and continuous statistics?

Select 1 option(s):

Discrete statistics deal with numerical data, whereas continuous statistics deal with categorical data

Discrete statistics deal with individual, separate values, whereas continuous statistics deal with a range of values

Discrete statistics are used for qualitative data, whereas continuous statistics are used for quantitative data

Discrete statistics focus on variance, whereas continuous statistics focus on mean

Q17. In the context of Databricks, there are distinct types of parameters used in dashboards and visualizations. Based on the descriptions provided, how do Widget Parameters, Dashboard Parameters, and Static Values differ in their application and impact?

Select 1 option(s):

Widget Parameters are tied to a single visualization and affect only the query underlying that specific visualization. Dashboard Parameters, on the other hand, can influence multiple visualizations within a dashboard and are configured at the dashboard level. Static Values are used to replace parameters, making them ‘disappear‘ and setting a fixed value in their place.

Dashboard Parameters are specific to individual visualizations and cannot be shared across multiple visualizations within a dashboard. Widget Parameters are used at the dashboard level to influence all visualizations. Static Values change dynamically in response to user interactions.

Both Widget Parameters and Dashboard Parameters have the same functionality and impact, allowing for dynamic changes across all visualizations in a dashboard. Static Values provide temporary placeholders for these parameters.

Widget Parameters apply to the entire dashboard and can change the layout, whereas Dashboard Parameters are fixed and do not allow for interactive changes. Static Values are dynamic and change frequently based on user input.

Static Values are used to create interactive elements in dashboards, while Widget and Dashboard Parameters are used for aesthetic modifications only, without impacting the data or queries.

Q18. Which of the following statements describes Delta Lake?

Select 1 option(s):

Delta Lake is an open-source platform to help manage the complete machine learning lifecycle..

Delta Lake is an open-source data storage format for distributed data.

Delta Lake is an open-format storage layer that delivers reliability, security, and performance.

Delta Lake is an open-source analytics engine used for big data workloads

Q19. What is the primary purpose of the OVER clause in Databricks SQL?

Select 1 option(s):

Filters rows based on conditions

Defines the window for window functions

Applies row-level security

Sorts query results

Q20. Databricks maintains a number of proprietary tools, which of the following listed is not Databricks proprietary tool?

Select 1 option(s):

Photon

Workflows

Unity Catalog

Apache Spark

Q21. How does Databricks SQL handle large datasets in memory processing?

Select 1 option(s):

Uses disk-based processing

Utilizes in-memory caching

Implements parallel processing

Applies row-level compression

Q22. The data analysis team is looking to quickly analyze data in Tableau, considering information from two distinct data sources and examining their collective behavior. What specific activity are they engaged in?

Select 1 option(s):

last-mile ETL

data blending

data integration

data enhancement

Q23. A data analyst has created a dashboard with multiple visualizations, and they want to ensure that viewers can see the dashboard‘s insights without any interaction. Which parameter should the analyst set to achieve this in Databricks SQL?

Select 1 option(s):

Interaction Parameter

Display Parameter

Presentation Mode Parameter

Query Parameter

Q24. You are analyzing a dataset in Databricks SQL named WeatherReadings which includes the columns StationID (integer), ReadingTimestamp (timestamp), and Temperature (float).

You need to calculate the average temperature for each station in 1-hour windows, sliding every 30 minutes. Which SQL query correctly uses the windowing function to achieve this?

Select 1 option(s):

SELECT StationID, window(ReadingTimestamp, ‘1 hour‘), AVG(Temperature) FROM WeatherReadings GROUP BY StationID, window(ReadingTimestamp, ‘1 hour‘);

SELECT StationID, AVG(Temperature) OVER (PARTITION BY StationID ORDER BY ReadingTimestamp ROWS BETWEEN INTERVAL ‘30 minutes‘ PRECEDING AND INTERVAL ‘30 minutes‘ FOLLOWING) FROM WeatherReadings;

SELECT StationID, window(ReadingTimestamp, ‘1 hour‘, ‘30 minutes‘), AVG(Temperature) FROM WeatherReadings GROUP BY StationID, window(ReadingTimestamp, ‘1 hour‘, ‘30 minutes‘);

SELECT StationID, AVG(Temperature) OVER (PARTITION BY StationID ORDER BY ReadingTimestamp RANGE BETWEEN INTERVAL 1 HOUR PRECEDING AND CURRENT ROW) FROM WeatherReadings;

SELECT StationID, AVG(Temperature) OVER (PARTITION BY StationID, window(ReadingTimestamp, ‘1 hour‘, ‘30 minutes‘)) FROM WeatherReadings;

Q25. What does the SQL CROSS JOIN operator do?

Select 1 option(s):

Combines rows from two or more tables based on a common column

Returns the Cartesian product of two or more tables

Performs a natural join between tables

Sorts the output based on specified columns

Q26. A data analyst is working with large-scale data transformations in Databricks SQL and needs to optimize query performance. Which technique should the analyst use to improve the efficiency of complex data transformations?

Select 1 option(s):

Parallel processing

Sequential processing

Recursive queries

Batch processing

Q27. Caching is an essential technique for improving the performance of data warehouse systems by avoiding the need to recompute or fetch the same data multiple times. Does Databricks SQL also use query caching techniques?

Select 1 option(s):

No, Databricks SQL does not need query caching as the speed at which the query is executed is 6x faster than data warehouse systems

Yes, Databricks SQL uses query caching to improve query performance, minimize cluster usage, and optimize resource utilization for a seamless data warehouse experience.

Only the Gold layer of Databricks SQL uses query caching

If the SQL Warehouse is created using Pro or Classic mode, query caching is enabled, it is disabled in serverless SQL warehouse

Q28. In a large-scale Databricks environment, there exists a dataset named Transactions, featuring a column TransactionAmount. The objective is to employ a custom scaling function to standardize transaction amounts for analysis, accomplished through the creation of a Python-based User-Defined Function (UDF).

Which of the provided methods accurately demonstrates the creation and utilization of a UDF for
this task?

Select 1 option(s):

Define a Python function normalize(amount) and register it as a UDF, then use SELECT normalize(TransactionAmount) FROM Transactions;.

Create a Python script outside Databricks to preprocess the data, then import the normalized dataset into Databricks.

Use a built-in SQL function to normalize TransactionAmount directly within a SQL query without defining a UDF.

Implement a machine learning model within Databricks to automatically normalize TransactionAmount.

Manually apply the normalization function to each row of the Transactions dataset using a Spark DataFrame loop.

Q29. How can a dashboard owner configure an alert to notify them when a specific visualization exceeds a predefined threshold in Databricks SQL?

Select 1 option(s):

Alerts can only be set for entire dashboards, not individual visualizations.

Alerts are configured automatically based on common thresholds; manual configuration is not required.

By defining alert rules specifically for the visualization, including the threshold value and notification method, ensuring timely notifications when the condition is met.

Dashboard owners cannot receive alerts based on visualizations; alerts are sent to administrators only.

Q30. What is a key benefit of using Databricks SQL for in-Lakehouse platform data processing?

Select 1 option(s):

Scalable data processing

Real-time data processing

Streamlined data visualization

Enhanced data storage

Q31. How can Databricks SQL import data from object storage?

Select 1 option(s):

Using FTP protocols

Importing from cloud storage only

Importing from local drives

Import from object storage

Q32. When analyzing the key moments of a statistical distribution, what does a negative skewness value indicate?

Select 1 option(s):

The distribution is symmetric

The distribution is positively skewed

The distribution is negatively skewed, with a longer left tail

The distribution has no outliers

Q33. What is Partner Connect used for in Databricks SQL?

Select 1 option(s):

Data storage and backup

Implementing simple integrations with other data products

User authentication

Query optimization

Q34. A data analyst is creating a dashboard and wants to add visual appeal through formatting. Which formatting technique can be used to enhance visual appeal in Databricks SQL visualizations?

Select 1 option(s):

Using a monochromatic color scheme

Using vibrant and contrasting colors

Avoiding labels and titles for a minimalist look

Using only grayscale colors

Q35. 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 code block 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 1 option(s):

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
FROM customer_summary

SELECT PRICE customer_spend, customer_units AS customer_price
FROM customer_summary

SELECT price(customer_spend, customer_units) AS customer_price
FROM customer_summary

Q36. In the context of analytics applications, what does the term “central tendency“ refer to?

Select 1 option(s):

The tendency of data to converge towards a specific value

The spread of data values

The presence of outliers in the dataset

The frequency of specific values

Q37. What does the term “last-mile ETL“ stand for in the context of data enhancement?

Select 1 option(s):

Extract, Transform, Load

Extract, Transfer, Load

Extract, Transform, Load, Evaluate

Extract, Transform, Load, Deliver

Q38. 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;
What is the mistake in the query

Select 1 option(s):

The query is selecting region, but region should only occur in the ORDER BY clause.

The query is missing a GROUP BY region clause.

The query is using ORDER BY, which is not allowed in an aggregation.

The query is using count(*), which will count all the customers in the customers table, no matter the region

Q39. Which SQL Dialect does Databricks SQL use:

Select 1 option(s):

PostgreSQL

ANSI standard SQL dialect

Microsoft SQL Server

MySQL

Q40. A new data analyst has joined your team. He has recently been added to the company‘s Databricks workspace as new.analyst@company.com. The data analyst should be able to query the table orders in the database ecommerce. The new data analyst has been granted USAGE on the database ecommerce already.

Which of the following commands can be used to grant the appropriate permission to the new data analyst?

Select 1 option(s):

GRANT SELECT ON TABLE orders TO new.analyst@company.com;

GRANT CREATE ON TABLE orders TO new. analyst@company.com;

GRANT USAGE ON TABLE orders TO new.analyst@company.com;

GRANT USAGE ON TABLE new.analyst@company.com TO orders;

Q41. Which of the following clause cannot be used in SQL sub-queries?

Select 1 option(s):

DELETE

ORDER BY

GROUP BY

FROM

Q42. In a Databricks environment, you‘re analyzing query performance improvements. After several runs of a complex query on a large dataset, you notice a significant reduction in latency. What feature of Databricks is most likely contributing to this decrease in query execution time?

Select 1 option(s):

Improved data indexing mechanisms.

Increased hardware resources allocation.

Automatic query rewriting for optimization.

Caching of intermediate data and results from previous query executions.

Use of persistent tables instead of temporary views.

Q43. In the context of Databricks dashboards, how do query parameters influence the output of underlying SQL queries within a dashboard?

Select 1 option(s):

They modify the layout of the dashboard, rearranging the visualizations based on user preferences, but do not change the data output of the queries.

Query parameters act as static reference points in SQL queries, ensuring that the output remains constant irrespective of user interactions.

They automatically update the SQL queries on a set schedule, such as daily or weekly, to change the output data based on temporal parameters.

Query parameters serve as placeholders in SQL queries, allowing for dynamic data filtering based on user input, thus altering the output of the query according to the specified parameter values.

Query parameters are used to format the visual aspects of the output, such as color and font, without changing the actual data returned by the query.

Q44. How can you create a temporary table in Databricks SQL?

Select 1 option(s):

Use the CREATE TEMPORARY TABLE statement.

Use the CREATE TABLE statement with the TEMPORARY keyword.

Both A and B

Q45. An organization is performing data quality checks in Databricks SQL and wants to identify records with missing values in specific columns. Which SQL operation should the organization use to filter records with missing values in the specified columns?

Select 1 option(s):

NULL FILTER

MISSING()

MISSING VALUES

IS NULL

Q46. How does Databricks SQL handle schema evolution in ingested data files?

Select 1 option(s):

Rejects files with schema mismatch

Automatically adjusts schema

Requires manual schema mapping

Creates separate tables for each schema

Q47. Scenario: An analyst working with Databricks wants to create a temporary table to store the results of a complex query for further analysis.

Question: What is the correct syntax to create a temporary table named temp_results based on the results of a query from the table source_data?

Select 1 option(s):

CREATE TEMPORARY TABLE temp_results
AS
SELECT column1, column2
FROM source_data;

CREATE TABLE temp_results TEMPORARY
AS
SELECT column1, column2
FROM source_data;

CREATE TABLE temp_results USING Delta
AS
SELECT column1, column2
FROM source_data;

CREATE TEMP TABLE temp_results
SELECT column1, column2
FROM source_data;

CREATE TEMPORARY TABLE temp_results
SELECT column1, column2
INTO source_data
FROM source_data;

Q48. Assuming you have a table TrafficData with columns Timestamp (timestamp) and VehicleCount (integer), and you need to calculate the sum of VehicleCount for every 10-minute window.

Which SQL query using the window_time function correctly achieves this in a Databricks
environment?

Select 1 option(s):

SELECT Timestamp, SUM(VehicleCount) OVER (ORDER BY Timestamp RANGE BETWEEN INTERVAL 10 MINUTES PRECEDING AND CURRENT ROW) FROM TrafficData;

SELECT window_time(Timestamp, ‘10 minutes‘), COUNT(VehicleCount) FROM TrafficData GROUP BY Timestamp;

SELECT Timestamp, SUM(VehicleCount) OVER (PARTITION BY window_time(Timestamp, ‘10 minutes‘)) FROM TrafficData;

SELECT window_time(Timestamp, ‘10 minutes‘), AVG(VehicleCount) FROM TrafficData GROUP BY window_time(Timestamp, ‘10 minutes‘);

SELECT window_time(Timestamp, ‘10 minutes‘), SUM(VehicleCount) FROM TrafficData GROUP BY window_time(Timestamp, ‘10 minutes‘);

Q49. What is the purpose of configuring a refresh schedule for a dashboard in Databricks SQL?

Select 1 option(s):

To automatically update the dashboard data at regular intervals

To restrict access to the dashboard

To set permissions for dashboard viewers

To modify the dashboard layout

Q50. What is the result of

SELECT pink()
WHERE pink()
is SQL UDF
CREATE FUNCTION pink()
RETURNS STRING
COMMENT ‘Pink color code‘
LANGUAGE SQL
RETURN ‘FFCBCB‘

Select 1 option(s):

UDFs are not supported in Databricks SQL

FFCOCB‘

Pink color code‘

This is not a correct usage

Q51. Under what condition can Databricks SQL ingest directories of files if they are of the same type?

Select 1 option(s):

Files have similar content

Files are in the same directory

Files have different formats

Files are of the same type

Q52. What does Delta Lake‘s Vacuum operation do?

Select 1 option(s):

Removes old versions of files and data

Creates backups of data

Encrypts table data

Triggers automatic schema updates

Q53. What is the purpose of partitioning a table in Databricks?

Select 1 option(s):

To improve query performance

To organize data by date or other criteria

To reduce storage costs

All of the above

Q54. What is the lowest level of permission required for a user to Configure a refresh schedule for a Databricks SQL Dashboard?

Select 1 option(s):

Owner.

Can Edit.

Can View.

No permissions.

Modify Permissions.

Q55. Which of the following data workloads will utilize a Silver table as its source?

Select 1 option(s):

A job that aggregates cleaned data to create standard summary statistics

A job that queries aggregated data that already feeds into a dashboard

A job that cleans data by removing malformatted records

A job that enriches data by parsing its timestamps into a human-readable format

Q56. What kind of information can you anticipate finding showcased when utilizing the schema browser within the Query Editor of Databricks?

Select 1 option(s):

Detailed documentation and syntax for all SQL functions and commands.

List of all users and groups who have access to the Databricks workspace.

Only the SQL queries that have been executed in the past sessions.

Real-time performance metrics and logs of the Databricks cluster.

Available databases, tables, and columns, along with their data types.

Q57. If a user doesn‘t have access to Databricks SQL, which statement will you use as workspace admin to grant access to the user?

Select 1 option(s):

Go to User Settings.
Click to the Users tab. In the user row,
click Databricks SQL access.

Go to Workspace.
Select the workspace, then Click to the Users tab.
In the user row, click Databricks SQL access.

Go to Admin Settings.
Click to the Users tab. In the user row,
click Databricks SQL access.

Q58. A transportation company is analyzing commuter data to optimize bus routes. What statistical measure can help assess the variability in commuter arrival times at different bus stops?

Select 1 option(s):

Mean

Range

Standard Deviation

Median

Q59. A company is implementing Databricks SQL for their analytics needs and wants to ensure seamless integration with visualization tools like Tableau, Power BI, and Looker. Which feature of Databricks SQL enables this integration for creating interactive dashboards and reports?

Select 1 option(s):

Databricks SQL warehouses

Databricks SQL schema browser

Partner Connect

Query compatibility with visualization tools

Q60. A data analyst is tasked with identifying unique values in a specific column of a table. Which SQL keyword should the analyst use to remove duplicate values and return only distinct values in the result set?

Select 1 option(s):

UNIQUE

DISTINCT

UNIQUEVALUES

DISTINCTVALUES