1.

Question

In a Databricks dashboard designed to track regional sales data, an analyst introduces a parameter
that allows users to select a specific region from a dropdown list. Upon selection, the dashboard
updates all visualizations, such as bar charts and line graphs, to reflect sales data exclusively for the
chosen region. This change in data display triggered by the parameter selection is an example of
which behavior in a Databricks dashboard?

A. The parameter behaves as a dynamic filter, altering the scope of the data presented
based on user selection.

B. The parameter serves as an input field for users to add new data into the dashboard for
the selected region.

C. The parameter automatically recalculates the entire dataset for the new region, affecting
the data source.

D. The parameter solely adjusts the layout of the dashboard without changing the data
displayed.

E. The parameter functions as a decorative element, enhancing the visual appeal but not the
data content.

Explanation
In Databricks dashboards, parameters like the one described can be used to dynamically filter data.
When a user selects a region from the dropdown list, the parameter acts as an interactive control
element, filtering and displaying data pertinent only to the chosen region. This behavior allows for a
more focused and user-specific data analysis experience, enabling users to interact with the
dashboard to extract insights relevant to their specific area of interest. The data itself remains
unchanged; the parameter simply controls which subset of it is displayed based on the user‘s
selection.
This feature enhances the interactivity and flexibility of dashboards in Databricks, making them more
useful for users who need to analyze different segments of data without manually adjusting data
sources or creating multiple dashboards for each subset of data.
References:
https://learn.microsoft.com/en-us/azure/databricks/sql/user/queries/query-parameters

2. Question

Implementing performance tuning on a Delta Lake table, which technique provides the most
significant improvement for query speed on a frequently queried column?

A. Increasing the file size of stored data.

B. Partitioning the table based on the queried column.

C. Creating a secondary index on the queried column.

D. Storing the table in a columnar format.

Explanation
B. Partitioning the table based on the queried column. More details: Partitioning the table based on
the queried column is the most efficient technique for improving query speed on a frequently
queried column in a Delta Lake table. Here‘s why: 1. Reduced Data Scanning: When a table is
partitioned based on the queried column, the data is physically organized into separate directories or
files based on the values of the partitioned column. This means that when a query is executed, only
the relevant partitions need to be scanned, reducing the amount of data that needs to be processed.
This significantly improves query performance, especially for frequently queried columns. 2. Parallel
Processing: Partitioning allows for parallel processing of queries on different partitions, which can
further improve query speed. Each partition can be processed independently, utilizing the available
resources more efficiently. 3. Predicate Pushdown: Partitioning enables predicate pushdown, where
the query engine can push filters down to the relevant partitions, reducing the amount of data that
needs to be read and processed. This can lead to significant performance improvements, especially
for large datasets. 4. Data Skew Mitigation: Partitioning helps in mitigating data skew issues by
distributing the data evenly across partitions based on the values of the partitioned column. This can
prevent hotspots and uneven distribution of data, leading to more balanced query performance. In
contrast, the other options may also improve query performance to some extent, but partitioning
based on the queried column is specifically designed to optimize query speed for frequently queried
columns. Increasing the file size of stored data may improve performance to some extent, but it may
not be as effective as partitioning. Creating a secondary index on the queried column can improve
lookup performance but may not be as efficient for range queries or aggregations. Storing the table
in a columnar format can improve query performance in general, but partitioning based on the
queried column can provide more targeted and significant improvements for frequently queried
columns.

3. Question

In the context of SQL server, which of the following queries uses a CTE and window function to rank
customers based on their total purchase amount, then selects only the top 2 customers from each
city?

A) WITH CustomerRank AS (SELECT city, customer_id, SUM(purchase_amount) OVER

(PARTITION BY city ORDER BY SUM(purchase_amount) DESC) AS total_purchase, RANK()
OVER (PARTITION BY city ORDER BY SUM(purchase_amount) DESC) AS rank FROM
customers GROUP BY city, customer_id) SELECT city, customer_id FROM CustomerRank
WHERE rank <= 2;

B) SELECT city, customer_id, RANK() OVER (PARTITION BY city ORDER BY

SUM(purchase_amount) DESC) FROM customers GROUP BY city, customer_id HAVING
RANK() <= 2;

C) WITH CustomerRank AS (SELECT city, customer_id, SUM(purchase_amount) AS

total_purchase, RANK() OVER (PARTITION BY city ORDER BY SUM(purchase_amount) DESC)
AS rank FROM customers GROUP BY city, customer_id) SELECT city, customer_id FROM
CustomerRank WHERE rank <= 2;

D) SELECT city, customer_id FROM (SELECT city, customer_id, SUM(purchase_amount) AS

total_purchase, RANK() OVER (PARTITION BY city ORDER BY total_purchase DESC) AS rank
FROM customers GROUP BY city, customer_id) AS CustomerRank WHERE rank <= 2;

Explanation
The correct proposition for the given question is: A) WITH CustomerRank AS (SELECT city,
customer_id, SUM(purchase_amount) OVER (PARTITION BY city ORDER BY SUM(purchase_amount)
DESC) AS total_purchase, RANK() OVER (PARTITION BY city ORDER BY SUM(purchase_amount) DESC)
AS rank FROM customers GROUP BY city, customer_id) SELECT city, customer_id FROM
CustomerRank WHERE rank <= 2; More details: 1. Common Table Expression (CTE): The query starts
with a Common Table Expression (CTE) named CustomerRank, which calculates the total purchase
amount for each customer in a specific city using the SUM(purchase_amount) function with the
OVER clause to partition by city and order by the total purchase amount in descending order. 2.
Window Function - RANK(): The query uses the RANK() window function to assign a rank to each
customer within their respective city based on the total purchase amount. This allows us to rank
customers without affecting the result set. 3. Filtering the top 2 customers per city: The final SELECT
statement retrieves the city and customer_id from the CustomerRank CTE where the rank is less than
or equal to 2. This ensures that only the top 2 customers from each city are selected based on their
total purchase amount ranking. 4. Grouping and Aggregating: The query correctly groups by city and
customer_id to calculate the total purchase amount for each customer in a specific city. This ensures
that the ranking is done at the correct granularity level. 5. Correct Syntax: The syntax used in the
query is correct and follows the standard SQL syntax for using CTEs, window functions, and filtering
based on ranks. In contrast, the other options either do not correctly calculate the total purchase
amount, do not use the RANK() function properly, or do not filter the top 2 customers per city
accurately. Option A is the most efficient and suitable proposition for ranking customers based on
their total purchase amount and selecting the top 2 customers from each city in SQL Server.

4. Question

How would you write an SQL query to find the third highest salary from a table containing employee
salaries without using the LIMIT or TOP clause?

A. Utilize the RANK() window function partitioned by salary.

B. Employ the DENSE_RANK() window function ordered by salary DESC.

C. Use a subquery that counts the distinct salaries higher than each salary.

D. Apply the ROW_NUMBER() window function and filter for the third row.

Explanation

B. Employ the DENSE_RANK() window function ordered by salary DESC. More details: 1.
DENSE_RANK() function: DENSE_RANK() is a window function in SQL that assigns a rank to each row
within a partition of a result set, with no gaps in the ranking values. This function is particularly
useful for finding the nth highest or lowest value in a dataset without using the LIMIT or TOP clause.
2. Ordered by salary DESC: By ordering the results in descending order of salary, we can easily
identify the third highest salary by looking at the rank assigned by the DENSE_RANK() function. 3.
Efficient and concise solution: Using the DENSE_RANK() function with the appropriate ordering
allows us to find the third highest salary in a straightforward and efficient manner, without the need
for complex subqueries or additional filtering. 4. No need for subqueries: Unlike the other options
provided, employing the DENSE_RANK() function eliminates the need for subqueries or additional
calculations to determine the third highest salary. This results in a cleaner and more optimized query.
Overall, utilizing the DENSE_RANK() window function ordered by salary DESC is the most suitable and
efficient proposition for finding the third highest salary from a table containing employee salaries
without using the LIMIT or TOP clause.

5. Question
When constructing a complex SQL query that involves multiple CTEs for analyzing customer
engagement metrics, what is a key benefit of using CTEs over subqueries?

A. CTEs provide a more readable and organized structure, especially when the query
involves multiple steps of data transformation.

B. CTEs can be indexed, which significantly improves the performance of the query.

C. Subqueries, unlike CTEs, cannot reference themselves, making them unsuitable for
recursive queries.

D. CTEs execute faster than subqueries because they are materialized by the database
before being used in the main query.

Explanation
A. CTEs provide a more readable and organized structure, especially when the query involves
multiple steps of data transformation. More details: 1. Readability and organization: CTEs allow for
the query to be broken down into smaller, more manageable parts. This makes it easier for
developers to understand and maintain the query, especially when it involves multiple steps of data
transformation. Subqueries, on the other hand, can make the query harder to read and follow, as
they are nested within the main query. 2. Reusability: CTEs can be referenced multiple times within
the same query, allowing for code reuse and reducing redundancy. This can be particularly useful in
complex queries where the same subquery needs to be used in multiple places. Subqueries, on the
other hand, cannot be referenced multiple times within the same query, leading to duplication of
code. 3. Performance: While it is true that CTEs are materialized by the database before being used
in the main query, which can potentially improve performance by reducing the number of times the
same subquery needs to be executed, the performance benefit may not always be significant. In
some cases, subqueries may actually perform better than CTEs, depending on the specific query and
database optimization. Overall, the key benefit of using CTEs over subqueries when constructing a
complex SQL query that involves multiple CTEs for analyzing customer engagement metrics is the
improved readability and organization of the query, especially when it involves multiple steps of data
transformation. This can lead to easier maintenance, better code reuse, and a more efficient
development process.

6. Question

Which SQL clause is essential for computing a running total of sales within a partitioned dataset by
date in Databricks SQL?

A) PARTITION BY date ORDER BY sales

B) ORDER BY date RANGE UNBOUNDED PRECEDING

C) ORDER BY date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW

D) GROUP BY date ORDER BY sales

Explanation
C) ORDER BY date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW More details: In
this scenario, we are looking to compute a running total of sales within a partitioned dataset by date.
The key here is to calculate the running total based on the order of dates within each partition. The
ORDER BY clause is essential for specifying the order of rows within the partition. In this case, we
want to order the rows by date to ensure that the running total is calculated correctly. The ROWS
BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW clause is used to define the window frame
for the running total calculation. This clause specifies that the window frame includes all rows from
the beginning of the partition up to the current row. This is crucial for computing the running total
accurately based on the order of dates. Therefore, the correct proposition is C) ORDER BY date ROWS
BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW.

7. Question

When a Delta Lake table‘s performance degrades over time due to an increase in small files, what
technique can be used to optimize query performance?

A. Running the OPTIMIZE command to compact files and improve read efficiency.

B. Increasing the number of partitions in the Delta table.

C. Manually merging small files into larger ones using custom scripts.

D. Redistributing data across more clusters to parallelize reads.

Explanation
A. Running the OPTIMIZE command to compact files and improve read efficiency. More details:
When a Delta Lake table‘s performance degrades over time due to an increase in small files, running
the OPTIMIZE command is the most suitable technique to optimize query performance. Here‘s why:
1. Compacting files: The OPTIMIZE command compacts small files into larger ones, reducing the
overall number of files in the table. This helps in improving read efficiency as the query engine has to
scan fewer files to retrieve the required data. Small files can lead to inefficiencies in query processing
due to the overhead of opening and closing multiple files. 2. Metadata optimization: In addition to
compacting files, the OPTIMIZE command also optimizes the table‘s metadata, which can further
improve query performance. This optimization includes updating statistics and data skipping indexes,
which help the query engine to skip unnecessary data blocks during query execution. 3. Incremental
optimization: The OPTIMIZE command can be run incrementally to optimize only the new data that
has been added since the last optimization. This helps in maintaining optimal query performance
over time, even as the table continues to grow. 4. Ease of use: Running the OPTIMIZE command is a
built-in feature of Delta Lake, making it a convenient and efficient technique for optimizing query
performance. It does not require manual intervention or custom scripts to merge files, making it a
more straightforward solution. In conclusion, running the OPTIMIZE command to compact files and
optimize metadata is the most effective technique for improving query performance in a Delta Lake
table experiencing degradation due to an increase in small files.

8. Question

What is the key advantage of using Databricks SQL to create interactive dashboards directly over
traditional BI tools?

A. Elimination of data pre-processing steps.

B. Direct access to live data without the need for data export.

C. Improved security through Databricks’ native access controls.

D. Availability of advanced machine learning algorithms for data analysis.

Explanation
B. Direct access to live data without the need for data export. More details: The key advantage of
using Databricks SQL to create interactive dashboards directly over traditional BI tools is the direct
access to live data without the need for data export. This means that users can access and analyze
real-time data without having to go through the time-consuming process of exporting data from
different sources and then importing it into a separate BI tool. This direct access to live data allows
for faster decision-making and more accurate insights as users are working with the most up-to-date
information available. Additionally, by eliminating the need for data export, Databricks SQL reduces
the risk of data errors or discrepancies that can occur during the data transfer process. This ensures
that the insights and analysis generated from the interactive dashboards are reliable and
trustworthy. Furthermore, direct access to live data also enables users to perform ad-hoc analysis
and explore data in real-time, allowing for more flexibility and agility in decision-making. This can be
particularly beneficial in fast-paced industries where quick and informed decisions are crucial for
success. Overall, the ability to access live data directly through Databricks SQL provides a significant
advantage over traditional BI tools by streamlining the data analysis process, reducing the risk of
errors, and enabling faster and more accurate decision-making.

9. Question

You‘re using Databricks Visualizations to analyze a dataset containing time-series data of website
traffic. The dataset updates every hour with new access logs. You want to create a visualization that
automatically reflects new data as it arrives, without manual intervention. How do you achieve this
level of automation within a Databricks notebook?

A) Utilize the built-in display function with a streaming DataFrame query that refreshes at
set intervals, ensuring the visualization updates with the latest data.

B) Implement a scheduled job in Databricks to rerun the notebook every hour, automatically
updating the visualization with the latest data.

C) Create a static visualization initially, then use Databricks REST APIs to programmatically
update the notebook cell‘s content with new query results periodically.

D) Embed custom JavaScript in a Databricks notebook that polls a data endpoint at regular
intervals, dynamically refreshing the visualization without rerunning the notebook.

Explanation
A) Utilize the built-in display function with a streaming DataFrame query that refreshes at set
intervals, ensuring the visualization updates with the latest data. More details: Option A is the most
suitable proposition for achieving the level of automation required in this scenario. By utilizing the
built-in display function with a streaming DataFrame query, you can set up a continuous query that
automatically refreshes at specified intervals. This ensures that the visualization reflects the latest
data as it arrives in the dataset without the need for manual intervention. Here‘s a detailed
explanation of why Option A is the best choice: 1. Streaming DataFrame: By using a streaming
DataFrame, you can process data in real-time as it arrives in the dataset. This allows you to
continuously update the visualization with the latest information without having to manually refresh
the query. 2. Built-in display function: Databricks provides a built-in display function that allows you
to create interactive visualizations directly within the notebook. By using this function in conjunction
with the streaming DataFrame query, you can easily update the visualization as new data is ingested.
3. Set intervals: With a streaming DataFrame query, you can specify the intervals at which the data
should be refreshed. This ensures that the visualization is automatically updated at regular time
intervals, keeping it in sync with the latest access logs. 4. Automation: By setting up the streaming
DataFrame query with the display function, you can achieve a high level of automation in your
analysis process. The visualization will continuously reflect the most up-to-date data without
requiring manual intervention, saving you time and effort. Overall, Option A provides a seamless and
efficient way to create a visualization that automatically updates with new data in a Databricks
notebook. It leverages the capabilities of streaming DataFrames and the built-in display function to
ensure a smooth and automated analysis process for time-series data of website traffic.

10. Question

When is it most efficient to use a materialized view in Databricks SQL?

A) When you need real-time updates for data changes in the underlying tables.

B) When the data size is small, and query response time is not a concern.

C) When aggregating large datasets that do not change frequently to speed up query
performance.

D) When performing simple SELECT operations without aggregations.

Explanation
C) When aggregating large datasets that do not change frequently to speed up query performance.
More details: – Materialized views are precomputed result sets stored as tables, which can
significantly improve query performance by reducing the need to recompute the same result set
multiple times. – When dealing with large datasets, especially when performing aggregations,
materialized views can help speed up query performance by storing the precomputed results and
avoiding the need to process the entire dataset each time a query is run. – Since materialized views
store the results of queries, they are most efficient when the underlying data does not change
frequently. This is because updating the materialized view can be a resource-intensive process, so it
is best suited for datasets that are relatively static. – In the case of real-time updates for data
changes in the underlying tables (option A), materialized views may not be the most efficient
solution as they would need to be constantly updated to reflect the changes, which can be resource-
intensive and defeat the purpose of using materialized views for performance optimization. –
Similarly, when the data size is small and query response time is not a concern (option B),
materialized views may not be necessary as the performance gains may not be significant enough to
justify the overhead of maintaining materialized views. – When performing simple SELECT operations
without aggregations (option D), materialized views may not be needed as the performance gains
would be minimal compared to more complex queries that involve aggregations on large datasets.
Overall, option C is the most efficient proposition for using materialized views in Databricks SQL as it
aligns with the primary purpose of materialized views – to improve query performance on large
datasets by precomputing and storing results that do not change frequently.

11. Question

What strategy would you employ to manage schema evolution in Delta Lake efficiently while
minimizing the impact on downstream data pipelines?

A. Disabling schema evolution to ensure consistent data structure.

 B. Automatically accepting schema changes and notifying downstream applications to adapt
accordingly.

C. Manually reviewing and applying schema changes during off-peak hours.

D. Implementing a version-controlled schema update process with rollback capabilities.

Explanation
D. Implementing a version-controlled schema update process with rollback capabilities. More details:
Managing schema evolution in Delta Lake efficiently while minimizing the impact on downstream
data pipelines is crucial for maintaining data integrity and ensuring smooth data processing.
Implementing a version-controlled schema update process with rollback capabilities is the most
suitable strategy for achieving this goal. Here‘s a detailed explanation of why this proposition is the
best choice: 1. Version control: By implementing a version-controlled schema update process, you
can track and manage changes to the schema over time. This allows you to have a clear history of
schema modifications, making it easier to identify and troubleshoot any issues that may arise.
Version control also provides the ability to roll back to previous schema versions if needed, ensuring
data consistency and minimizing disruptions to downstream data pipelines. 2. Rollback capabilities:
Having rollback capabilities is essential for mitigating the impact of any schema changes that may
cause issues in downstream data pipelines. If a schema change leads to errors or data corruption,
being able to quickly revert to a previous schema version can help minimize downtime and prevent
data loss. This ensures that downstream applications can continue to operate smoothly without
being affected by unexpected schema changes. 3. Efficiency: A version-controlled schema update
process with rollback capabilities streamlines the schema evolution process, making it more efficient
and less error-prone. Instead of manually reviewing and applying schema changes during off-peak
hours, which can be time-consuming and prone to human error, having a structured process in place
allows for automated schema updates with the ability to roll back if necessary. This reduces the risk
of data inconsistencies and ensures that downstream data pipelines can adapt to schema changes
seamlessly. In conclusion, implementing a version-controlled schema update process with rollback
capabilities is the most effective strategy for managing schema evolution in Delta Lake efficiently
while minimizing the impact on downstream data pipelines. This approach provides a structured and
automated way to track and manage schema changes, ensuring data integrity and smooth data
processing without disrupting downstream applications.

12. Question

To identify the top 5% of customers by total purchase amount in the last year, which SQL query
would you use?

A) SELECT customer_id, SUM(purchase_amount) AS total_purchase FROM purchases

WHERE purchase_date >= DATEADD(year, -1, GETDATE()) GROUP BY customer_id QUALIFY
RANK() OVER (ORDER BY SUM(purchase_amount) DESC) <= COUNT(*) * 0.05;

B) SELECT customer_id, total_purchase FROM (SELECT customer_id,

SUM(purchase_amount) AS total_purchase, PERCENT_RANK() OVER (ORDER BY
SUM(purchase_amount) DESC) AS pr FROM purchases WHERE purchase_date >=
DATEADD(year, -1, GETDATE()) GROUP BY customer_id) AS RankedCustomers WHERE pr <=
0.05;

C) WITH TotalPurchases AS (SELECT customer_id, SUM(purchase_amount) AS

total_purchase FROM purchases WHERE purchase_date >= DATEADD(year, -1, GETDATE())
 GROUP BY customer_id) SELECT customer_id FROM TotalPurchases WHERE total_purchase
>= (SELECT PERCENTILE_CONT(0.95) WITHIN GROUP (ORDER BY total_purchase) OVER ()
FROM TotalPurchases);

D) SELECT customer_id FROM (SELECT customer_id, SUM(purchase_amount) AS

total_purchase, NTILE(20) OVER (ORDER BY SUM(purchase_amount) DESC) AS percentile
FROM purchases WHERE purchase_date >= DATEADD(year, -1, GETDATE()) GROUP BY
customer_id) AS PurchasePercentiles WHERE percentile = 1;

Explanation
The correct proposition for identifying the top 5% of customers by total purchase amount in the last
year is: B) SELECT customer_id, total_purchase FROM (SELECT customer_id, SUM(purchase_amount)
AS total_purchase, PERCENT_RANK() OVER (ORDER BY SUM(purchase_amount) DESC) AS pr FROM
purchases WHERE purchase_date >= DATEADD(year, -1, GETDATE()) GROUP BY customer_id) AS
RankedCustomers WHERE pr <= 0.05; More details: 1. The query starts by selecting the customer_id
and the total_purchase amount from a subquery. 2. In the subquery, it calculates the total purchase
amount for each customer by summing up their purchase amounts. 3. It then uses the
PERCENT_RANK() window function to calculate the percentile rank of each customer based on their
total purchase amount. 4. The PERCENT_RANK() function assigns a value between 0 and 1 to each
customer, with 0 being the lowest total purchase amount and 1 being the highest. 5. The main query
then filters out only those customers whose percentile rank is less than or equal to 0.05, which
corresponds to the top 5% of customers by total purchase amount. 6. By using the PERCENT_RANK()
function, this query directly identifies the top 5% of customers based on their total purchase amount
without the need for additional calculations or comparisons. Overall, proposition B is the most
suitable and efficient option for identifying the top 5% of customers by total purchase amount in the
last year because it leverages the PERCENT_RANK() window function to directly calculate the
percentile rank of each customer and filter out the top 5% based on this rank. This approach
simplifies the query and ensures accurate results without the need for complex calculations or
additional subqueries.

13. Question

In Databricks SQL, when alerts are configured based on specific criteria, how are notifications
typically sent to inform users or administrators of the triggered alerts?

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

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

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

D. Notifications are not supported for alerts in Databricks SQL Analytics.

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

Explanation
Databricks SQL allows users to configure alerts based on specific criteria, and these alerts can be set
up to trigger notifications through various channels. The notification channels can include email,
messaging platforms like Slack, or webhook integrations, depending on the configuration chosen by
the user. This flexibility ensures that users or administrators can be informed of triggered alerts in a
way that suits their preferences and needs.
References:
https://learn.microsoft.com/en-us/azure/databricks/sql/user/alerts/

14.Question

A data analyst is processing a complex aggregation on a table with zero null values
and their query returns the following result:

Which of the following queries did the analyst run to obtain the above result?

A)

B)
C)

D)

E)

A Option A

B Option B
C Option C

D Option D

E Option E

Explanation
Suggested Answer: B

The result set provided shows a combination of grouping by two columns (group_1
and group_2) with subtotals for each level of grouping and a grand total. This pattern
is typical of a GROUP BY ... WITH ROLLUP operation in SQL, which provides
subtotal rows and a grand total row in the result set.

Considering the query options:

A) Option A: GROUP BY group_1, group_2 INCLUDING NULL - This is not a

standard SQL clause and would not result in subtotals and a grand total.

B) Option B: GROUP BY group_1, group_2 WITH ROLLUP - This would create

subtotals for each unique group_1, each combination of group_1 and group_2, and a
grand total, which matches the result set provided.

C) Option C: GROUP BY group_1, group 2 - This is a simple GROUP BY and would

not include subtotals or a grand total.

D) Option D: GROUP BY group_1, group_2, (group_1, group_2) - This syntax is not

standard and would likely result in an error or be interpreted as a simple GROUP BY,
not providing the subtotals and grand total.

E) Option E: GROUP BY group_1, group_2 WITH CUBE - The WITH CUBE

operation produces subtotals for all combinations of the selected columns and a
grand total, which is more than what is shown in the result set.

The correct answer is Option B, which uses WITH ROLLUP to generate the
subtotals for each level of grouping as well as a grand total. This matches the result
set where we have subtotals for each group_1, each combination of group_1 and
group_2, and the grand total where both group_1 and group_2 are NULL.

15. Question

A company needs to create interactive dashboards showcasing real-time sales data to stakeholders.
Which tool can be integrated with Databricks SQL to create visually appealing and interactive
dashboards for real-time data analysis?
 A. Tableau

B. Fivetran

C. Small-file upload

D.Databricks SQL schema browser

Explanation
The best tool to integrate with Databricks SQL for creating interactive dashboards showcasing real-
time sales data to stakeholders is: Tableau. Here‘s why: Tableau: Offers a powerful and user-friendly
interface for creating visually engaging and interactive dashboards. It connects directly to Databricks
SQL, enabling live refreshes with real-time data. Fivetran: A data integration platform primarily
focused on data movement and ingestion, not visualization or dashboarding. It can be used to get
data into Databricks SQL but doesn‘t offer dashboard creation features. Small-file upload: This is not
a relevant option for real-time data analysis. It‘s a manual data loading process unsuitable for
dynamic and constantly updated sales data. Databricks SQL schema browser: This tool helps explore
data structures but doesn‘t offer dashboarding or visualization capabilities. Here are some specific
advantages of using Tableau with Databricks SQL for real-time sales dashboards: Live data
connection: Tableau can connect directly to Databricks SQL and automatically refresh dashboards as
new data arrives, ensuring stakeholders always see the latest information. Visual storytelling: Tableau
offers a wide range of chart types, graphs, and interactive elements to present sales data in a clear
and engaging way. Customization: Dashboards can be customized with branding, filters, and drill-
down capabilities to cater to specific stakeholder needs. Collaboration: Tableau allows sharing
dashboards with stakeholders, facilitating data-driven discussions and decision-making. Therefore,
Tableau‘s combination of visual appeal, real-time data connection, and interactive features makes it
the most suitable tool for creating interactive dashboards for real-time sales data analysis in
conjunction with Databricks SQL.

16. Question

In the context of analytics, what is an example of effectively enhancing data in a common

application?

A. Keeping data in its original, raw format for archival purposes.

B. Strictly categorizing data based on its source without additional processing.

C. Integrating weather data into a retail sales analysis to understand the impact of weather
on sales trends.

D. Restricting data access to a limited number of users to ensure data security.

E. Performing routine software updates on data analysis tools without modifying data.

Explanation
Integrating weather data into a retail sales analysis to understand the impact of weather on sales
trends.
Explanation:
Understanding External Factors: By integrating weather data into the analysis, analysts can assess
how external factors such as temperature, precipitation, or seasonal patterns impact sales trends.
This additional context allows for a more comprehensive understanding of sales performance beyond
internal factors alone.
Identifying Correlations: Analyzing the relationship between weather conditions and sales data can
reveal correlations or patterns that might not be immediately obvious. For example, certain products
may sell better during specific weather conditions, or there may be a seasonal effect on consumer
behavior based on weather patterns.
Optimizing Business Strategies: Insights gained from analyzing weather-related sales trends can
inform business decisions and strategies. For instance, retailers can adjust inventory levels, marketing
campaigns, or pricing strategies based on anticipated changes in weather patterns to better meet
customer demand and maximize sales opportunities.
Enhanced Predictive Analytics: Integrating weather data allows for more sophisticated predictive
modeling and forecasting. By incorporating weather forecasts into sales predictions, businesses can
anticipate demand fluctuations and proactively adjust operations to optimize resource allocation and
minimize stockouts or excess inventory.
Overall, integrating weather data into retail sales analysis exemplifies how enhancing data with
external sources can provide valuable insights and enable data-driven decision-making in analytics
applications.

17. Question

What is the purpose of caching in Databricks SQL?

A. Speed up query execution by storing intermediate results

B. Store historical query data for auditing

C. Improve data durability

D. Optimize storage utilization

Explanation
The purpose of caching in Databricks SQL is: Speed up query execution by storing intermediate
results. Here‘s why the other options are incorrect: Store historical query data for auditing: While
historical data can be stored in Databricks SQL, caching specifically focuses on optimizing future
query execution, not long-term data storage. Improve data durability: Data durability relates to
ensuring data persistence and availability, which is achieved through mechanisms like replication and
backups, not caching. Optimize storage utilization: While caching can indirectly affect storage
utilization by reducing redundant data access, its primary purpose is performance optimization, not
storage efficiency. Therefore, caching in Databricks SQL is primarily used to store frequently used
intermediate results from previous queries, allowing subsequent queries to access them directly
from the cache instead of recomputing them from the source data. This significantly reduces query
execution times and improves overall performance, especially for complex or frequently executed
queries.

18. Question

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

A. SELECT DISTINCT

B. SELECT DUPLICATE

C. SELECT ALL
 D.SELECT UNIQUE

Explanation
The SQL keyword used to remove duplicates from a result set is:
SELECT DISTINCT
he SELECT DISTINCT SQL statement is used to retrieve unique values from a specified column in a
table. It eliminates duplicate records in the result set, ensuring that only distinct values are included.
Here‘s a brief explanation:
1. Syntax:
SELECT DISTINCT column1, column2, …
FROM table_name;
2. Example: Consider a table named “employees“ with a column “department“ that may have
duplicate values. The following query retrieves distinct department names:
SELECT DISTINCT department
FROM employees;
3. Result: If the “department“ column contains duplicate values, the result set will only include
unique department names, removing any duplicates.
departmentHRITSalesMarketing
4. Functionality:
The DISTINCT keyword operates on the specified columns, and it applies to the entire result set.
It is commonly used in combination with the SELECT statement when you want to retrieve unique
values from one or more columns.
In summary, SELECT DISTINCT is a powerful SQL keyword that helps remove duplicate records from
the result set, ensuring that the retrieved values are distinct and unique.

19. Question

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?

A. CCPA

B. HIPAA

C. GDPR

D. PCI-DSS

Explanation
For users based out of California, the data protection and compliance regulation that applies is:
CCPA (California Consumer Privacy Act)
Explanation:
1. CCPA (California Consumer Privacy Act):
The CCPA is a data protection and privacy law in the state of California. It grants California residents
certain rights regarding their personal information, including the right to know what personal
information is collected, how it‘s used, and the right to request its deletion.
It applies to businesses that meet certain criteria, including those that collect and process the
personal information of California residents.
2. Other Regulations:
HIPAA (Health Insurance Portability and Accountability Act): Primarily applies to the healthcare
industry.
GDPR (General Data Protection Regulation): Applies to the protection of personal data of individuals
in the European Union.
PCI-DSS (Payment Card Industry Data Security Standard): Focuses on the protection of credit card
data and applies to entities that process payment transactions.
In the context of users based out of California, CCPA is the relevant regulation that addresses their
data protection and privacy rights.

20. Question

How does Databricks SQL handle large datasets in memory processing?

A. Uses disk-based processing

B. Utilizes in-memory caching

C. Implements parallel processing

D. Applies row-level compression

Explanation

While all the options listed contribute to Databricks SQL‘s ability to handle large datasets, the most
crucial approach for memory processing is: Utilizes in-memory caching. Here‘s why the other options
play a role but aren‘t the primary factor for memory processing: Uses disk-based processing: While
Databricks SQL utilizes disk storage for persistent data, memory processing specifically focuses on
keeping frequently accessed data in memory for faster retrieval and manipulation. Implements
parallel processing: Parallel processing across multiple nodes distributes workload, but it doesn‘t
necessarily translate to all data residing in memory. Certain datasets might still be accessed from
disk. Applies row-level compression: Compression can reduce data storage footprint, but it doesn‘t
directly impact memory processing. Compressed data still needs to be decompressed when accessed
in memory. Databricks SQL leverages in-memory caching through techniques like caching frequently
used tables or intermediate results in RAM. This significantly reduces disk access for subsequent
operations, leading to faster query execution and performance improvements for large datasets.
Here‘s how in-memory caching benefits large datasets in Databricks SQL: Reduced disk I/O: By
keeping frequently accessed data in memory, the need for repetitive disk reads is minimized,
improving overall processing speed. Faster data access: Data stored in RAM can be accessed much
quicker than data residing on disk, leading to quicker query responses and analysis. Improved
performance for iterative jobs: If a job involves repeatedly accessing the same data, caching
significantly reduces processing time as the data remains readily available in memory. Of course, the
size and complexity of your datasets, workload characteristics, and available memory resources will
influence the effectiveness of in-memory caching. It‘s important to tune caching strategies and
consider other optimization techniques like partitioning and data filtering to achieve optimal
performance for your specific needs. Therefore, while Databricks SQL utilizes various approaches to
handle large datasets, emphasizing in-memory caching through targeted data persistence in RAM
remains the critical factor for efficient memory processing, providing significant performance gains
for your data analysis tasks.

21. Question
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?

A. last-mile ETL

B. data blending

C. data integration

D. data enhancement

Explanation

The data analysis team would be performing data blending. Data blending involves combining data
from multiple sources to create a unified dataset for analysis. In Tableau, data blending allows users
to analyze data from different sources and understand their behavior together in a single
visualization.

22. Question

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?

A. Interaction Parameter

B. Display Parameter

C. Presentation Mode Parameter

D. Query Parameter

Explanation

In Databricks SQL, the most appropriate parameter to set for a dashboard to ensure viewers can see
insights without interaction is: Presentation Mode Parameter Here‘s why the other options are not as
suitable: Interaction Parameter: This would actually control the level of interaction viewers have with
the dashboard, potentially limiting it rather than ensuring it‘s available. Display Parameter: This
might influence the visuals or data displayed, but it wouldn‘t necessarily guarantee a non-interactive
viewing experience. Query Parameter: This mainly affects the underlying data used in the
visualizations, not the interaction mode for viewers. Presentation Mode Parameter specifically exists
in Databricks SQL dashboards to: Hide all control elements and interactive features. This removes
filters, parameter panels, and other interactive components, leaving viewers with a focused view of
the visualizations and their key insights. Automatically refresh the dashboard at user-defined
intervals. This ensures viewers see the latest data without needing to manually refresh. Prevent
unnecessary user actions. Viewers can still scroll and zoom within the visualizations but are restricted
from modifying data selections or parameters. Therefore, by setting the Presentation Mode
Parameter to “Enabled,“ the data analyst can create a streamlined viewing experience for their
audience, maximizing focus on the key insights of the dashboard without requiring any interaction.

23. Question

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?

A. SELECT StationID, window(ReadingTimestamp, ‘1 hour‘), AVG(Temperature) FROM

WeatherReadings GROUP BY StationID, window(ReadingTimestamp, ‘1 hour‘);

B. SELECT StationID, AVG(Temperature) OVER (PARTITION BY StationID ORDER BY

ReadingTimestamp ROWS BETWEEN INTERVAL ‘30 minutes‘ PRECEDING AND INTERVAL ‘30
minutes‘ FOLLOWING) FROM WeatherReadings;

C. SELECT StationID, window(ReadingTimestamp, ‘1 hour‘, ‘30 minutes‘), AVG(Temperature)

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

D. SELECT StationID, AVG(Temperature) OVER (PARTITION BY StationID ORDER BY

ReadingTimestamp RANGE BETWEEN INTERVAL 1 HOUR PRECEDING AND CURRENT ROW)
FROM WeatherReadings;

E. SELECT StationID, AVG(Temperature) OVER (PARTITION BY StationID,

window(ReadingTimestamp, ‘1 hour‘, ‘30 minutes‘)) FROM WeatherReadings;

Explanation

The correct query is:

SELECT StationID, window(ReadingTimestamp, ‘1 hour‘, ‘30 minutes‘), AVG(Temperature) FROM
WeatherReadings GROUP BY StationID, window(ReadingTimestamp, ‘1 hour‘, ‘30 minutes‘);
Explanation:
This SQL query is designed to calculate the average temperature for each station in 1-hour windows,
sliding every 30 minutes, which is a common requirement for time series analysis in datasets like
weather readings.
SELECT StationID: This part of the query specifies that the output should include the station ID,
allowing you to identify which station each record pertains to.
window(ReadingTimestamp, ‘1 hour‘, ‘30 minutes‘): The window function is used here to define the
sliding window over the timestamp column ReadingTimestamp. The first parameter (‘1 hour‘)
specifies the duration of the window, and the second parameter (‘30 minutes‘) specifies the sliding
interval. This means that the window moves forward by 30 minutes after each calculation, ensuring
that the average temperature is calculated for every half-hour interval within each hour-long
window.
AVG(Temperature): This part of the query calculates the average temperature within each window
for a given station. The AVG function is applied to the Temperature column, which computes the
mean temperature across all readings in the window.
FROM WeatherReadings: Specifies the dataset being queried, which in this case is WeatherReadings,
containing weather data such as temperature readings.
GROUP BY StationID, window(ReadingTimestamp, ‘1 hour‘, ‘30 minutes‘): This clause groups the
results by StationID and each calculated window. Grouping by the window ensures that the average
temperature is calculated separately for each distinct time window and station, allowing for a
detailed analysis of temperature changes over time across different locations.
This query leverages the power of window functions in SQL to perform complex time-based
calculations efficiently. By using a sliding window, it allows for a more granular analysis of
temperature trends and variations within the dataset, providing valuable insights into weather
patterns at each station.

24. Question

What does the SQL CROSS JOIN operator do?

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

B. Returns the Cartesian product of two or more tables

C. Performs a natural join between tables

D. Sorts the output based on specified columns

Explanation

Returns the Cartesian product of two or more tables The purpose of the SQL CROSS JOIN operator is
to return the Cartesian product of two or more tables. It combines each row from the first table with
every row from the second table, resulting in a result set that contains all possible combinations of
rows from the involved tables. It does not consider any condition for joining; it simply forms the
Cartesian product.

25. Question

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?

A. Parallel processing
B. Sequential processing
C. Recursive queries
D. Batch processing
Explanation

Parallel processing To optimize query performance when working with large-scale data
transformations in Databricks SQL, the analyst should use parallel processing. Parallel processing
involves breaking down a large task into smaller subtasks that can be executed simultaneously by
multiple processors or cores. This parallel execution can significantly improve the efficiency of
complex data transformations by leveraging the available computing resources. Databricks, being
built on Apache Spark, inherently supports parallel processing for distributed data processing.

26. Question

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?

A. No, Databricks SQL does not need query caching as the speed at which the query is executed
is 6x faster than data warehouse systems
 B. Yes, Databricks SQL uses query caching to improve query performance, minimize cluster
usage, and optimize resource utilization for a seamless data warehouse experience.

C. Only the Gold layer of Databricks SQL uses query caching

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

Explanation

Yes, Databricks SQL uses query caching to improve query performance, minimize cluster usage, and
optimize resource utilization for a seamless data warehouse experience.
Query caching in Databricks SQL is a performance optimization technique that stores the results of a
query so that if the same query is issued again, the system can retrieve the cached results instead of
re-executing the query. This helps in reducing query execution time, minimizing cluster usage, and
optimizing resource utilization.
When a query is executed, Databricks SQL checks if the results are already cached. If the results are
found in the cache and the underlying data has not changed, the cached results are returned without
re-executing the entire query. This is particularly beneficial for repeated queries or dashboards
where the underlying data hasn‘t changed frequently.
By using query caching, Databricks SQL can provide a faster and more efficient data processing
experience, making it a valuable feature for optimizing performance in analytical workloads.

27. Question

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

A. Scalable data processing

B. Real-time data processing

C. Streamlined data visualization

D. Enhanced data storage

Explanation

Scalable data processing A key benefit of using Databricks SQL for in-Lakehouse platform data
processing is scalable data processing. Databricks SQL, integrated with the Databricks Unified
Analytics Platform, provides the capability to scale data processing tasks efficiently. It allows
organizations to handle large volumes of data, perform complex analytics, and process data at scale,
making it well-suited for big data and analytics workloads.

28. Question

How can Databricks SQL import data from object storage?

A. Using FTP protocols

B. Importing from cloud storage only

C. Importing from local drives

D. Import from object storage

Explanation

Import from object storage directly. Here‘s why the other options are not as relevant for Databricks
SQL data import: Using FTP protocols: While Databricks might support data transfer through other
protocols for specific situations, importing data directly from object storage uses dedicated drivers
and optimized integrations for efficiency and scalability. Importing from cloud storage only: Object
storage is considered a type of cloud storage, so this option is essentially the same as the correct
answer. Databricks supports importing data from various cloud object storage providers like Amazon
S3, Azure Blob Storage, and Google Cloud Storage. Importing from local drives: Databricks primarily
operates in a cloud environment, and loading data directly from local drives might not be the most
appropriate or performant option for large datasets. Therefore, Databricks SQL offers powerful
functionalities for seamlessly importing data from various object storage platforms. These
functionalities typically involve: Utilizing pre-configured connectors: Databricks provides connectors
for popular object storage services, allowing you to easily configure and establish connections for
data import. Specifying source paths: You can provide the specific object storage path (uri) of the
data file you want to import within your SQL query or other data processing tasks. Leveraging COPY
INTO command: This SQL command specifically caters to loading data from external sources like
object storage into Delta tables within your Databricks Lakehouse. Importing data directly from
object storage offers several advantages: Scalability: Object storage is designed for handling massive
datasets, and Databricks‘ integration ensures efficient import of large volumes of data. Cost-
effectiveness: Object storage often offers cost-efficient data storage solutions compared to other
options. Flexibility: You can import data from diverse object storage providers based on your needs
and existing infrastructure. Remember, the specific steps and methods for importing data from
object storage might vary depending on the chosen storage provider and desired workflow. Consult
Databricks documentation and resources for detailed instructions and best practices specific to your
configuration.

29. Question

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

A. The distribution is symmetric

B. The distribution is positively skewed

C. The distribution is negatively skewed, with a longer left tail

D. The distribution has no outliers

Explanation

The most accurate answer to what a negative skewness value indicates when analyzing the key
moments of a statistical distribution is: The distribution is negatively skewed, with a longer left tail.
Here‘s why the other options are incorrect: The distribution is symmetric: A symmetric distribution
has a skewness value of 0, meaning the tails on either side of the central tendency are of equal
length. A negative skewness value contradicts this. The distribution is positively skewed: A positively
skewed distribution has a skewness value of greater than 0, meaning the longer tail is on the right
side. A negative skewness points to the opposite scenario. The distribution has no outliers: Skewness
measures the asymmetry of the distribution, not the presence of outliers. Even a distribution with
outliers can have a negative skewness as long as the left tail is longer. Therefore, a negative skewness
value indicates that the distribution has a longer tail on the left side. This means that there are more
data points located below the central tendency compared to those above it. The distribution is
“tilted“ towards the left, hence the negative skewness value. Understanding skewness and other
distributional characteristics is crucial for interpreting data effectively. A negative skewness can have
implications for further analysis, model building, and decision-making based on the data.

30. Question

What is Partner Connect used for in Databricks SQL?

A. Data storage and backup

B. Implementing simple integrations with other data products

C. User authentication

D. Query optimization

Explanation

The correct answer is: Implementing simple integrations with other data products. Here‘s why the
other options are incorrect: Data storage and backup: While Databricks offers its own data storage
and backup solutions, Partner Connect focuses on integrating with external data products and
services. User authentication: Databricks has its own authentication system, and Partner Connect
doesn‘t directly deal with user management. Query optimization: While Partner Connect can
indirectly impact query performance through efficient data access, its primary purpose is integration,
not optimization. Databricks Partner Connect simplifies the process of connecting your Databricks
SQL environment with various data products and services offered by trusted partners. It provides
several benefits, including: Preconfigured integrations: Partner Connect offers pre-built connectors
for various data sources, analytics tools, and other platforms, eliminating the need for manual
configuration and coding. Trial accounts: You can try out partner solutions within your Databricks
environment using trial accounts, helping you evaluate potential solutions before committing.
Simplified data access: Partner Connect streamlines data access and movement between your
Databricks workspace and partner products, enabling seamless data workflows. Therefore, Partner
Connect is primarily used for implementing simple integrations with other data products within the
Databricks SQL environment. It allows you to leverage the capabilities of various tools and services
without complex setup or coding, facilitating efficient data analysis and collaboration.

31. Question

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?

A. Using a monochromatic color scheme

B. Using vibrant and contrasting colors

C. Avoiding labels and titles for a minimalist look

 D. Using only grayscale colors

Explanation

While each option has its place in design, the choice that can most effectively enhance visual appeal
in Databricks SQL visualizations is: Using vibrant and contrasting colors. Here‘s why the other options
wouldn‘t be ideal for enhancing visual appeal: Using a monochromatic color scheme: While
monochromatic schemes can be elegant, they may lack the pop and differentiation needed to
effectively highlight key data points and trends in visualizations. Avoiding labels and titles for a
minimalist look: This approach might appeal to some, but it can hinder understanding and
interpretation of the data. Clear and concise labels and titles are crucial for guiding users through the
dashboard and ensuring they grasp the presented information. Using only grayscale colors: Similar to
a monochromatic scheme, grayscale visuals can lack the necessary contrast and vibrancy to
effectively draw attention to important data points and convey the narrative of the dashboard.
Vibrant and contrasting colors, however, have several advantages: Improve data differentiation:
Using different colors for distinct data series or categories allows users to easily distinguish them and
identify patterns or relationships. Highlight key information: Strategic use of color can draw attention
to critical data points or trends, guiding users to the most relevant aspects of the visualization.
Enhance understanding: Color can be used to encode meaning and context within the data, making
the information more readily interpretable and impactful. Of course, using vibrant colors effectively
requires careful consideration of color theory and accessibility. Combining complementary colors,
avoiding clashing palettes, and ensuring good contrast for users with visual impairments are crucial
for optimal visual appeal and inclusivity. Remember, effective dashboard formatting aims to strike a
balance between aesthetics and clarity. While vibrant and contrasting colors can significantly
enhance visual appeal, it‘s essential to use them thoughtfully and prioritize clear communication of
the data insights for the viewers.

32. Question

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

A. SELECT function(price(customer_spend, customer_units)) AS customer_price FROM

customer_summary

B. SELECT double(price(customer_spend, customer_units)) AS customer_price FROM

customer_summary

C. SELECT price
FROM customer_summary

D. SELECT PRICE customer_spend, customer_units AS customer_price

FROM customer_summary
 E. SELECT price(customer_spend, customer_units) AS customer_price
FROM customer_summary

Explanation

The correct code block to apply the user-defined function to

the customer_spend and customer_units columns of the table customer_summary and create a
column customer_price is:
E.
SELECT price(customer_spend, customer_units) AS customer_price
FROM customer_summary;
Explanation:
The SELECT statement is used to query the data.
price(customer_spend, customer_units) applies the user-defined function to the specified columns.
AS customer_price aliases the result column as customer_price in the output.
Options A, B, C, and D contain syntax errors or incorrect usage of the function. Option E correctly
applies the function to the specified columns and aliases the result as customer_price.

33. Question

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

A. The tendency of data to converge towards a specific value

B. The spread of data values

C. The presence of outliers in the dataset

D. The frequency of specific values

Explanation

The tendency of data to converge towards a specific value In the context of analytics applications,
the term “central tendency“ refers to the tendency of data to converge towards a specific value. It is
a measure that provides information about the center or average of a distribution of values,
indicating where most values in the dataset are concentrated. Common measures of central
tendency include the mean, median, and mode.

34. Question

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

A. Extract, Transform, Load

B. Extract, Transfer, Load

C. Extract, Transform, Load, Evaluate

D. Extract, Transform, Load, Deliver

Explanation
Extract, Transform, Load, Deliver In the context of data enhancement, the term “last-mile ETL“ stands
for Extract, Transform, Load, Deliver. It refers to the final stages of the ETL (Extract, Transform, Load)
process where the transformed and enriched data is delivered to its destination for consumption,
often by end-users, applications, or downstream systems. This phase ensures that the enhanced data
is accessible and utilized effectively for analytics, reporting, or other purposes.

35. Question

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

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

B. The query is missing a GROUP BY region clause.

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

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

Explanation

B. The query is missing a GROUP BY region clause.

Explanation: When using an aggregate function like count() along with other non-aggregated
columns, you typically need to include a GROUP BY clause for those non-aggregated columns. In this
case, the query should include “GROUP BY region“ to correctly count the number of customers in
each region.

36. Question

Which SQL Dialect does Databricks SQL use:

A. PostgreSQL

B. ANSI standard SQL dialect

C. Microsoft SQL Server

D. MySQL

Explanation

Databricks SQL primarily uses the ANSI standard SQL dialect. While it supports standard SQL, it also
provides some extensions and optimizations to enhance performance and compatibility with its
underlying Databricks platform. It‘s important to note that Databricks SQL is designed to work
seamlessly with Apache Spark, and therefore, it may have features and optimizations specific to
Spark SQL.

37. Question
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?

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

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

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

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

Explanation

The correct command to grant the appropriate permission to the new data analyst would be:
GRANT SELECT ON TABLE orders TO new_analyst@company.com;
This command grants the SELECT permission on the “orders“ table to the user
“new_analyst@company.com.“

38. Question

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

A. DELETE

B. ORDER BY

C. GROUP BY

D. FROM

Explanation

The ORDER BY clause cannot be used in SQL sub-queries. The ORDER BY clause is used to sort the
result set of a query, and it is typically used at the end of a statement. In a sub-query, the ordering is
done in the outer query, not within the sub-query itself.

39.Question

Which of the following data visualizations displays a single number by default? Select one response.
A. Bar chart

B. Counter

C. Map – markers

D. Funnel

40.Question

Which of the following automations are available in Databricks SQL? Select one response.

A. Query refresh schedules

B. Dashboard refresh schedules

C. Alerts

D. All of the above

41.Question
Which of the following statements about the silver layer in the medallion
architecture is true?
Option:
A. The silver layer is where data is transformed and processed for analytics
use
B. The silver layer is where raw data is stored in its original format
C. The silver layer is optimized for fast querying
D. The silver layer is the largest of the three layers
Correct Answer: A
Explanation:
A framework for managing and analyzing massive amounts of data has been developed
by Databricks called the medallion architecture. The bronze layer, the silver layer, and
the gold layer are the three layers that make up architecture.
In the medallion architecture, the silver layer serves as an intermediary layer between
the bronze layer, which stores raw data, and the gold layer, which houses data analysis.
The silver layer’s function is to aggregate, filter, and transform the raw data so that
analytics can be performed on it. Additionally, the silver layer may be used to organize,
normalize, and clean up data. Data is sent to the gold layer for analysis after it has been
transformed into the silver layer. Users can run queries, produce reports, and visualize
data in the gold layer because it is optimized for fast querying and analysis.
Option A is correct. In the medallion architecture, data transformation and processing
for analytics purposes take place in the silver layer. The silver layer is located between
the bronze layer, which is used to store raw data, and the gold layer, which is used to
analyze data. The silver layer’s function is to aggregate, filter, and transform the raw
data so that analytics can be performed on it. Additionally, the silver layer may be used
to organize, normalize, and clean up data. Data is sent to the gold layer for analysis after
it has been transformed into the silver layer.
42.Question
Delta Lake provides many benefits over traditional data lakes. In which of the
following scenarios would Delta Lake not be the best choice?
Option:
A. When data is mostly unstructured and does not require any schema enforcement
B. When data is primarily accessed through batch processing
C. When data is stored in a single file and does not require partitioning
D. When data requires frequent updates and rollbacks
Correct Answer: B
Explanation:
A unified data management system called Delta Lake offers capabilities for ACID
transactions, schema enforcement, and schema evolution on top of a data lake. In
comparison to conventional data lakes, Delta Lake has several advantages, including
reliability, performance, and scalability. Delta Lake, though, might not always be the best
option for batch processing.
43.Question
A healthcare company stores patient information in a table in Databricks. The
company needs to ensure that only authorized personnel can access the table.
Which of the following actions would best address this security concern?
Option:
A. Assigning table ownership to a generic company account
B. Granting access to the table to all employees
C. Implementing role-based access control with specific privileges assigned
to individual users
D. Storing the patient information in an unsecured Excel file
Explanation:
Option C is correct. The best option is to implement role-based access control with
specific privileges assigned to individual users. This approach allows the creation of
custom roles with specific privileges assigned to each role, which can then be assigned to
individual users. This ensures that only authorized personnel have access to the patient
information table and that they only have access to the specific data they need to
perform their job duties. By implementing this security measure, the healthcare company
can ensure that patient information is kept private and secure.
44. Question

When importing data from an Amazon S3 bucket into a Databricks environment using Databricks
SQL, which SQL command is typically used to perform this operation?

A. LOAD DATA INPATH ‘s3://mybucket/mydata.csv‘ INTO TABLE my_table;

B. CREATE TABLE my_table USING CSV LOCATION ‘s3://mybucket/mydata.csv‘;

C. SELECT * INTO my_table FROM OPENROWSET(BULK ‘s3://mybucket/mydata.csv‘,

SINGLE_CLOB) AS mydata;

D. INSERT INTO my_table SELECT * FROM s3a://mybucket/mydata.csv;

E. COPY INTO my_table FROM ‘s3://mybucket/mydata.csv‘ FILEFORMAT = CSV;

Explanation
The COPY INTO command is often used for this purpose, as it is well-suited for incremental and bulk
data loading in Databricks SQL.
References:
https://docs.databricks.com/en/ingestion/copy-into/tutorial-dbsql.html

45. Question

What is the correct sequence of steps to execute a SQL query in Databricks?

A. Write the query in an external tool, import it into Databricks, select a data source, and
execute the query.

B. Create a query using Terraform, execute the query in a Databricks job, and use COPY INTO to
load data.

C. Open SQL Editor, select a SQL warehouse, construct and edit the query, execute the query.

D. Choose a SQL warehouse, construct and edit the query, execute the query, and visualize
results.

E. Manually input data, write a query in Databricks notebook, execute the query, and export
the results.

Explanation

The correct sequence for executing a SQL query in Databricks starts with opening the SQL Editor.
Then, you select a SQL warehouse where the query will be executed. After this, you construct and
edit your SQL query directly in the editor, which supports features like autocomplete. Once the query
is ready, you execute it and the results are displayed in the results pane. During or after execution,
you can manage or terminate the query if necessary. Additionally, Databricks SQL provides options to
visualize the results and create dashboards for deeper analysis and sharing insights.
References:
https://docs.databricks.com/en/sql/user/queries/queries.html
https://docs.databricks.com/en/sql/get-started/index.html

46. Question

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?

A. 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.

B. 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.

C. 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.

D. 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.

E. 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.

Explanation

Widget Parameters are specific to individual visualizations within a dashboard. They appear within
the visualization panel and their values apply only to the query of that particular visualization.
Dashboard Parameters are more versatile and can be applied to multiple visualizations within a
dashboard. They are configured for one or more visualizations and are displayed at the top of the
dashboard. The values specified for these parameters apply to all visualizations that reuse them. A
dashboard can contain multiple such parameters, each affecting different sets of visualizations.
Static Values replace the need for a parameter and are used to hard code a value. When a static
value is used, the parameter it replaces no longer appears on the dashboard or widget, effectively
making the parameter static and non-interactive.
References:
https://learn.microsoft.com/en-us/azure/databricks/sql/user/queries/query-parameters

47. Question

In the context of Databricks, how is Personally Identifiable Information (PII) typically handled to
ensure data privacy and compliance?
 A. By automatically encrypting all data fields that contain PII.

B. PII is not specifically handled in Databricks; it relies on external tools.

C. By creating a separate database for PII.

D. By anonymizing PII data through built-in Databricks functions.

E. Through the use of Delta Lake features for fine-grained access control.

Explanation

Databricks, especially with the integration of Delta Lake, provides mechanisms for handling PII, such
as fine-grained access control. This allows for specific permissions on sensitive data fields, ensuring
that only authorized users can access PII. It‘s a crucial aspect of maintaining data privacy and
compliance with regulations like GDPR and HIPAA in data processing and analytics workflows.
References:
https://www.databricks.com/blog/2020/11/20/enforcing-column-level-encryption-and-avoiding-
data-duplication-with-pii.html

48. Question

A data analyst is creating a dashboard to present monthly sales data to company executives.
The initial version of the dashboard contains accurate data but receives feedback that it is hard to
interpret. The analyst then revises the dashboard by adjusting colors for better contrast, using
consistent and clear fonts, and organizing charts logically.
After these changes, the executives find the dashboard much more informative and easier to
understand. This scenario illustrates which of the following points about visualization formatting?

A. Formatting changes the underlying data, thus altering the data‘s interpretation.

B. Proper formatting can enhance readability and comprehension, leading to a more accurate
interpretation of the data.

C. Formatting only affects the aesthetic aspect of the visualization and has no impact on its
reception.

D. Over-formatting can lead to data misinterpretation by introducing visual biases.

E. Formatting is primarily used to reduce the size of the data set visually displayed.

Explanation

1. In this scenario, the changes made by the analyst – adjusting colors for better contrast,
using consistent and clear fonts, and logically organizing charts – demonstrate how
formatting can significantly impact the reception of a visualization. Proper formatting
does not alter the underlying data but enhances its presentation, making it easier to read
and understand. This leads to a more accurate and effective interpretation of the data,
which is crucial in a business setting where decisions are often based on such
visualizations.

49.Question.
In a Databricks environment, you are optimizing the performance of a data processing task that
involves complex operations on arrays within a Spark SQL dataset.
Which of the following higher-order functions in Spark SQL would be most suitable for efficiently
transforming elements within an array column scores?

A. SELECT ARRAY_SORT(scores) FROM dataset;

B. SELECT ARRAY_CONTAINS(scores, 10) FROM dataset;

C. SELECT COLLECT_LIST(scores) FROM dataset GROUP BY scores;

D. SELECT TRANSFORM(scores, score -> score * 2) FROM dataset;

E. SELECT EXPLODE(scores) FROM dataset;

Explanation

The TRANSFORM function in Spark SQL is a higher-order function that allows for efficient
manipulation of array elements. It applies a specified lambda function to each element of an array,
enabling complex transformations within a single SQL query. This approach optimizes performance
by minimizing the need for multiple operations and reducing data shuffling. Such functions are
particularly useful in big data scenarios where data processing needs to be both efficient and
scalable.
50. Question

How does the SQL LATERAL join behave in comparison to a regular join?

A. LATERAL join allows referencing columns from preceding tables in the join, whereas
regular join does not

B. LATERAL join performs a cross join, whereas regular join performs an inner join

C. LATERAL join can be used without specifying an ON or USING clause, whereas regular join
requires an ON or USING clause

D. LATERAL join always performs a left join, whereas regular join can perform various types of
joins

Explanation

The correct answer is LATERAL join allows referencing columns from preceding tables in the join,
whereas regular join does not. Key Differences between LATERAL and Regular Joins: Column
Referencing: LATERAL join enables you to reference columns from tables that appear earlier in the
join order within the join condition or the lateral table itself. This allows for dynamic transformations
and calculations based on values from those preceding tables. Regular join restricts you to
referencing columns only from the current table or those that have already been joined before it in
the join order. Order of Execution: LATERAL join is evaluated row by row, executing the lateral table
expression for each row from the preceding table. Regular join is typically evaluated as a whole,
joining tables based on the specified condition before returning results.