Databricks Certified Data Analyst Associate Test 3

Welcome to your Databricks Certified Data Analyst Test-3

Name

Phone

Q1. In Databricks SQL, when creating a basic, schema-specific visualization, what is the first step you should take?

Select 1 option(s):

Configure the dashboard settings to match the schema requirements.

Write a SQL query to retrieve data from the specific schema.

Adjust the data refresh rate to ensure real-time visualization.

Import external visualization libraries for advanced charting.

Select the visualization type from the visualization menu.

Q2. Which statement is true about using customizable tables as visualizations in Databricks SQL?

Select 1 option(s):

Customizable tables can only display numerical data

Customizable tables allow users to define the column names and formatting

Customizable tables can only be used with fixed-size images

Customizable tables cannot display aggregated data

Q3. In Databricks, setting up a refresh schedule for dashboards is essential for ensuring that the displayed data is current. How does one configure a refresh schedule for a Databricks SQL dashboard?

Select 1 option(s):

Writing a custom script in the dashboard‘s SQL queries to automate refreshes.

Manually updating the dashboard at regular intervals without any automated scheduling.

Click Schedule at the upper-right of the dashboard. Then, click Add schedule.

Utilizing an external tool to trigger refreshes in the Databricks dashboard.

Sending periodic requests to the Databricks support team to refresh the dashboard.

Q4. How does Databricks SQL optimize query performance for star schema queries?

Select 1 option(s):

Using bitmap indexes

Implementing star join optimization

Applying columnar storage

Enforcing strict data types

Q5. How does Databricks handle data persistence in a temp view?

Select 1 option(s):

Temp views are persisted across sessions

Temp views are persisted across clusters

Temp views are not persisted and exist only for the current session

Temp views are persisted permanently

Q6. Consider a scenario where you have a database with a table named ProductSales that contains columns ProductId, SaleDate, Region, and SaleAmount. You need to find the total sales amount for each product in a specific region, say “North America,“ but only for sales that occurred in the year 2023. Each row in the table represents a sale, where SaleDate is stored in the format YYYY-MM-DD.

Which SQL query will correctly aggregate this data?

Select 1 option(s):

Q7. In a Databricks dashboard tailored to monitor regional sales data, an analyst incorporates a parameter enabling users to choose a specific region from a dropdown menu. Upon selection, the dashboard dynamically adjusts all visualizations, like bar charts and line graphs, to showcase sales data solely for the chosen region. This adaptation in data presentation triggered by the parameter selection exemplifies which feature in a Databricks dashboard?

Select 1 option(s):

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

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

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

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

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

Q8. In Databricks SQL, how are temporary view scoped?

Select 1 option(s):

temporary views are scoped to multiple schema levels. It is generally used when you have to query various schema and get data from multiple schemas

temporary views are scoped to the warehouse level. It can be referenced in other queries, even within the same dashboard

Temporary views result in the same output irrespective of the environment you are using

temporary views are scoped to the query level. Multiple statements within the same query can use the temp view, but it cannot be referenced in other queries, even within the same dashboard

Q9. When you drop managed table, you do not affect the underlying data. Is this true or False?

Select 1 option(s):

TRUE

False

Q10. What does a Serverless Databricks SQL endpoint provide?

Select 1 option(s):

Dedicated server resources

On-demand query processing

High availability clusters

Real-time data synchronization

Q11. What does the term “last-mile ETL“ refer to in the context of data enhancement?

Select 1 option(s):

The final stage of the data transformation process

The first step in data processing

The stage where data is loaded into a database

The process of cleaning and preparing raw data for analysis

Q12. When sharing Databricks SQL dashboards, there are two primary settings: ‘Run as viewer‘ and ‘Run as owner‘. What are the pros and cons of each setting in the context of sharing dashboards?

Select 1 option(s):

Run as owner‘ offers greater customization of dashboard settings (pro), but requires viewers to have advanced knowledge of SQL (con). ‘Run as viewer‘ simplifies the user experience (pro), but may lead to inconsistent data reporting (con).

Run as viewer‘ allows full customization of queries for each viewer (pro), but can be more resource-intensive (con). ‘Run as owner‘ simplifies query management (pro), but doesn‘t account for individual user access levels (con).

Run as viewer‘ and ‘Run as owner‘ both provide the same level of data visibility (pro), but may have limitations in customizing query execution (con).

Both settings allow for easy sharing of dashboards (pro), but can lead to complexities in managing user permissions and data access (con).

Run as viewer‘ enhances data security by adhering to individual viewer‘s permissions (pro), but may limit data visibility (con). ‘Run as owner‘ ensures consistent data visibility across users (pro), but might pose security risks if the owner has broader data access (con).

Q13. Given a database with a table Orders containing columns OrderId, CustomerId, OrderDate, and Amount, you want to retrieve the list of orders placed by a particular customer, identified by CustomerId = 123, where the order amount is greater than $500.

Which SQL query will correctly retrieve this data?

Select 1 option(s):

Q14. The scenario featuring a data analyst crafting a dashboard to present monthly sales data to company executives depicts an initial version of the dashboard that, despite containing accurate data, receives feedback about being challenging to interpret. Subsequently, the analyst modifies the dashboard by enhancing color contrast, employing consistent and legible fonts, and arranging charts logically.

Following these adjustments, the executives perceive the dashboard as significantly more informative and easier to comprehend. This situation highlights which aspect of visualization formatting?

Select 1 option(s):

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

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

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

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

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

Q15. How is data enhancement commonly applied in analytics?

Select 1 option(s):

By converting unstructured data into structured format for easier database storage.

Through cleaning and normalizing data to maintain consistency in databases.

By incorporating external data sources to enrich existing datasets for deeper insights.

By reducing the size of the dataset to improve processing speed.

By anonymizing sensitive information in datasets for privacy compliance.

Q16. How can you restrict the number of clusters per users using the UI?

Select 1 option(s):

To restrict the number of clusters a user can create using a policy, use the Min and Max clusters per user setting under the Permissions tab in the cluster policies UI.

To restrict the number of clusters a user can create using a policy, use the Min clusters per user setting under the Permissions tab in the cluster policies Ul.

Databricks SQL allows an unlimited number of users, there is no restriction

To restrict the number of clusters a user can create using a policy, use the Max clusters per user setting under the Permissions tab in the cluster policies Ul.

Q17. A data analyst in Databricks is tasked with updating a table named SalesData. Faced with a new batch of data containing both existing and new records, the analyst must choose between employing MERGE INTO, INSERT INTO, and COPY INTO commands.

Given the functionalities and common application scenarios of these commands, which statement accurately reflects the situation?

Select 1 option(s):

Q18. What key moment of a statistical distribution represents the point below which a given percentage of observations fall?

Select 1 option(s):

Mean

Median

Percentile

Standard Deviation

Q19. A data analyst wants to create a visualization that shows the distribution of customer ages in the dataset. Which type of visualization is most suitable for displaying this information in Databricks SQL?

Select 1 option(s):

Bar Chart

Pie Chart

Histogram

Line Chart

Q20. In the context of analytics applications, what does the term “statistical moment“ refer to?

Select 1 option(s):

A specific point in time when a statistical measurement is taken

A specific value in the dataset

A set of specific measures used to describe the shape of a distribution

A specific data transformation technique

Q21. Compare and contrast correlated and non-correlated subqueries in SQL.

Select 1 option(s):

Correlated subqueries can be independent of the outer query, while non-correlated subqueries are always dependent on the outer query.

Non-correlated subqueries execute once for the entire outer query, while correlated subqueries execute once for each row processed by the outer query.

Non-correlated subqueries cannot reference columns from the outer query, while correlated subqueries can reference columns from the outer query.

Correlated subqueries always return a single value, while non-correlated subqueries can return multiple values.

Q22. A data analyst is working with a team in Databricks SQL and needs to create a view that provides an aggregated summary of a large dataset for reporting purposes. What is a suitable type of view for this scenario?

Select 1 option(s):

Temp view

Permanent view

Managed view

Materialized view

Q23. You have a table customer_orders in Databricks SQL with a column order_info in JSON format. This column includes a nested array items, where each element has product_id (string) and quantity (integer). How would you write a SQL query to list each product_id and its corresponding quantity for every order?

Syntax used to create the table CustomerOrders:
CREATE TABLE customer_orders AS
SELECT
{
“items“: [
{“product_id“: “X100“, “quantity“: 3},
{“product_id“: “Y200“, “quantity“: 1}
]
} as order_info;

Select 1 option(s):

SELECT EXPLODE(order_info:items) AS (product_id, quantity) FROM customer_orders;

SELECT FLATTEN(order_info:items.product_id, order_info:items.quantity) FROM customer_orders;

SELECT order_info:items[*].product_id, order_info:items[*].quantity FROM customer_orders;

SELECT order_info:items[*].product_id, order_info:items[*].quantity FROM customer_orders UNNEST items;

SELECT order_info:items.product_id, order_info:items.quantity FROM customer_orders;

Q24. A data analyst is working with time-based data and needs to calculate the moving average of sales over a specific period. Which SQL technique should the analyst use to efficiently calculate the moving average?

Select 1 option(s):

JOIN operations

Aggregate functions with GROUP BY

Window functions with OVER() clause

Subqueries with WHERE clause

Q25. When performing data blending between two datasets, what challenge is often encountered?

Select 1 option(s):

Merging datasets results in data duplication

Datasets from different sources are always compatible

Handling inconsistencies and discrepancies in data formats and values

Data blending has no challenges

Q26. A data analyst is working with complex queries in Databricks SQL and wants to simplify them for better readability and maintainability. Which SQL technique should the analyst use to achieve this goal?

Select 1 option(s):

Joins

Subqueries

CTEs (Common Table Expressions)

Indexing

Q27. How does Delta Lake handle schema evolution for nested data structures?

Select 1 option(s):

Requires manual adjustment of nested schema

Automatically adjusts schema for nested structures

Rejects nested data structures

Requires additional metadata files for nested schema

Q28. What is the key difference between managed and unmanaged tables in Databricks?

Select 1 option(s):

Managed tables store data in external locations

Unmanaged tables store data in external locations

Managed tables store data in managed locations

Unmanaged tables store data in managed locations

Q29. In a Databricks SQL scenario, imagine a dataset comprising columns ‘Department‘, ‘Employee‘, and ‘Sales‘. Your task is to analyze the data using the ROLLUP and CUBE functions. Considering this context, choose the accurate statement concerning the nature of aggregations that ROLLUP and CUBE would produce when employed on the ‘Department‘ and ‘Employee‘ columns.

Select 1 option(s):

ROLLUP provides aggregations only for each combination of ‘Department‘ and ‘Employee‘, while CUBE gives a detailed breakdown including each ‘Department‘, each ‘Employee‘, and a grand total.

ROLLUP generates hierarchical aggregations starting from the leftmost column in the GROUP BY clause.

It would produce subtotals for each ‘Department‘, subtotals for each combination of ‘Department‘ and ‘Employee‘, and a grand total.

CUBE creates aggregations for all possible combinations of the columns in the GROUP BY clause. It would generate subtotals for each ‘Department‘, each ‘Employee‘, each combination of ‘Department‘ and ‘Employee‘, and a grand total.

Neither ROLLUP nor CUBE will generate subtotals for individual ‘Departments‘ or ‘Employees‘; they only provide a grand total.

Both ROLLUP and CUBE produce identical aggregations, including subtotals for each ‘Department‘, each ‘Employee‘, each combination of ‘Department‘ and ‘Employee‘, and a grand total.

Q30. What are the primary responsibilities of a table owner in Databricks?

Select 1 option(s):

Regularly updating the table data to keep it current.

Ensuring the table is always available for querying and analysis.

Managing user access and permissions for the table.

Optimizing the table for faster query performance.

Designing the visual representation of the table data.

Q31. While working on a Databricks dashboard comprising numerous visualizations, a data analyst aims to maintain a consistent color scheme across all visuals to enhance aesthetic coherence and readability. What strategy should the analyst employ to modify the colors of all visuals within the dashboard uniformly?

Select 1 option(s):

Implement a script in the dashboard code to automatically adjust the colors of all visualizations.

Manually adjust the color settings in each individual visualization to match the desired scheme.

Export the dashboard data to a third-party tool for color scheme adjustments, then re-import it into Databricks.

Change the default color settings in the Databricks user preferences to automatically apply to all dashboards and visualizations.

Use a dashboard-wide setting that allows the analyst to apply a uniform color scheme to all visualizations simultaneously.

Q32. What is a potential con of sharing dashboards in Databricks SQL via public links?

Select 1 option(s):

Public links do not allow viewers to interact with the dashboard; they can only view static images.

Public links may expose sensitive or confidential information to unauthorized users, leading to data privacy concerns.

Public links do not provide real-time updates; viewers need to refresh the page manually to see the latest data.

Public links have no cons; they are the recommended method for sharing dashboards.

Q33. When an admin transfers ownership of a SQL warehouse to a new user, Which of the following permissions the new user should have in order to be an owner:

Select 1 option(s):

Allow Cluster Creation entitlement

Allow Schema Creation entitlement

Allow Database Creation entitlement

Allow Table create/delete entitlement

Q34. Data Analytics Team is working with census data of the United States. They want to display the population across the different states in the US using different shades of blue on the map. Which visualization would you use?

Select 1 option(s):

Box plot

Choropleth

Funnel

Bar Chart

Q35. What is Data Enrichment?

Select 1 option(s):

This is a process that involves moving the data from the bronze layer to the silver layer

This is a process that involves identifying or correcting the errors in the data

This is a process that involves filling in missing data

This is a process that involves adding additional information to the data

Q36. What are external tables used for in Unity Catalog?

Select 1 option(s):

Managing data lifecycle and file layout

Managing privileges on managed tables

Registering large amounts of existing data

Direct access to data using Databricks clusters

Q37. Which of the following is correct for three-tier namespacing?

Select 1 option(s):

< database >. < manage-table >. < external-table >

< catalog >. < database > < table >

< metastore >. < catalog >. < database >

< database > < schema >. < manage-table >

Q38. Which of the following features is a primary function of the Catalog Explorer in Databricks?

Select 1 option(s):

Scheduling and automating data pipeline workflows.

Automatically transforming data into normalized forms for analysis.

Generating real-time analytics and visualizations without the need for coding.

Previewing and exploring data, along with configuring security and access controls.

Executing complex machine learning algorithms on large datasets.

Q39. The company wants to send a customized message with the company logo and text. The project manager asked Data Analyst to find out if they can send customized emails from Databricks when any alerts are triggered. Is it possible?

Select 1 option(s):

Databricks support custom template, which can be created using HTML to format the message before sending

Databricks uses the template provided by AWS or Azure when sending email to user

Databricks has its own alert team which is sent with the Databricks logo and text, and it cannot be edited

None of the above

Q40. Within a Databricks environment, you‘re enhancing the efficiency of a data processing task that encompasses intricate operations on arrays within a Spark SQL dataset. Among the provided options, which higher-order function in Spark SQL would be the most appropriate choice for effectively transforming elements within an array column named “scores“?

Select 1 option(s):

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

Select 1 option(s):

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

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

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

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

Q42. You are analyzing a dataset OrderDetails in a Databricks SQL environment, which includes OrderID, ProductID, Quantity, and UnitPrice.

Select 1 option(s):

SELECT ProductID, SUM(Quantity) FROM OrderDetails GROUP BY ProductID;

SELECT ProductID, SUM(UnitPrice) FROM OrderDetails GROUP BY ProductID;

SELECT OrderID, SUM(UnitPrice * Quantity) AS TotalRevenue FROM OrderDetails GROUP BY OrderID;

SELECT ProductID, AVG(UnitPrice * Quantity) FROM OrderDetails GROUP BY ProductID;

SELECT ProductID, SUM(UnitPrice * Quantity) AS TotalRevenue FROM OrderDetails GROUP BY ProductID;

Q43. Your customer does not have access to the Databricks Infrastructure, however, your project management team wants to send some visualizations you have created, without providing the customer access to Databricks. Which of the following options will you choose?

Select 1 option(s):

Nothing can be done

Create a PAT token and ask the customer to use it to access the dashboard

Add a refresh schedule to the dashboard and add the customer email as a subscriber

Share your login credentials with the customer and ask them to use your credentials to view the dashboard

Q44. A company wants to implement Databricks SQL for their analytics needs and is considering different endpoint options. Which option provides a quick-starting, serverless option for running SQL queries in Databricks SQL without managing clusters?

Select 1 option(s):

Serverless Databricks SQL endpoint

Partner Connect

Databricks SQL warehouses

Import from object storage

Q45. Which of the following is the correct syntax for writing unmanaged tables?

Select 1 option(s):

CREATE TABLE table_name
USING DELTA

CREATE TABLE table_name
USING DELTA
LOCATION ‘/path/to/existing/data‘

CREATE TABLE table_name
(field_name1 INT, field_name2 STRING)

None of the above

Q46. While working on a Databricks dashboard comprising numerous visualizations, a data analyst aims to maintain a consistent color scheme across all visuals to enhance aesthetic coherence and readability. What strategy should the analyst employ to modify the colors of all visuals within the dashboard uniformly?

Select 1 option(s):

Implement a script in the dashboard code to automatically adjust the colors of all visualizations.

Manually adjust the color settings in each individual visualization to match the desired scheme.

Export the dashboard data to a third-party tool for color scheme adjustments, then re-import it into Databricks.

Change the default color settings in the Databricks user preferences to automatically apply to all dashboards and visualizations.

Use a dashboard-wide setting that allows the analyst to apply a uniform color scheme to all visualizations simultaneously.

Q47. How can a refresh schedule be configured in Databricks SQL to keep the dashboard data current?

Select 1 option(s):

Refresh schedules can only be configured for individual visualizations, not for the entire dashboard.

Refresh schedules are configured automatically upon dashboard creation; manual configuration is not required.

Refresh schedules can be configured at the cluster level, affecting all dashboards using the same cluster.

Refresh schedules can be configured at the dashboard level, specifying the frequency for refreshing the entire dashboard.

Q48. How can a data analyst change the colors of all visualizations in a dashboard to match the company‘s branding in Databricks SQL?

Select 1 option(s):

Custom CSS coding is required to change visualization colors.

Colors can be changed individually for each visualization but not universally across the dashboard.

There is no way to change visualization colors; they are fixed based on the data.

The dashboard theme can be customized to set universal colors for all visualizations, ensuring consistent branding.

Q49. The Data Engineering Team has set up a dashboard to monitor the data quality of the data setup in the medallion architecture. They want to label the reports by table type i.e. Bronze, Silver, and Gold.

How will they achieve that?

Select 1 option(s):

Use the feature to add text to the dashboard directly.

Create a PDF and use PDF Writer to label it

Use Add option. Click on “Text“ and add the text.

Add text to the visualization as there is no way to add text to the Dashboard

Q50. What is the role of Role-based Access Control in Databricks SQL?

Select 1 option(s):

Manages database schema

Restricts user access based on roles

Optimizes SQL queries

Defines table relationships

Q51. What does the Lakehouse allow in terms of batch and streaming workloads?

Select 1 option(s):

Isolation of batch and streaming workloads

Mixing of batch and streaming workloads

Streaming only, no batch processing

Batch processing only, no streaming

Q52. Which statement correctly differentiates discrete and continuous statistics within the context of data analysis?

Select 1 option(s):

Both discrete and continuous data are types of categorical data used in qualitative analysis.

Discrete data is used for time series analysis, while continuous data is not suitable for this purpose.

Continuous data is always non-numeric, whereas discrete data is numeric.

Discrete data can only take on a finite number of values, while continuous data can take on any value within a given range.

Continuous data can only take on integer values, whereas discrete data can take on any value, including decimals.

Q53. Select the statement which is TRUE.

Select 1 option(s):

In a table, there could be any number of rows and any number of columns.

In a table, there could be a specified number of rows and a specified number of columns.

In a table, there could be any number of rows and a specified number of columns.

In a table, there could be any number of columns and a specified number of rows.

Q54. In Azure Databricks, when using a table visualization type for SQL queries, which of the following statements accurately reflects its capabilities and limitations?

Select 1 option(s):

Table visualizations in Databricks automatically aggregate data within the result set, providing a summary view.

They allow for manual reordering, hiding, and formatting of data columns, but do not perform data aggregations within the result set.

They are limited to displaying only numerical data and cannot handle textual or categorical data.

Table visualizations in Databricks are primarily used for external data export and are not suitable for in-dashboard data presentation.

Table visualizations primarily function to display graphical representations like charts and graphs, rather than tabular data.

Q55. In the context of Unity Catalog in Databricks, what is the purpose of specifying a MANAGED LOCATION when creating a catalog or schema?

Select 1 option(s):

To configure the network access settings for the catalog or schema.

To set the encryption type for data stored in the catalog or schema.

To define the cloud object storage location for data of managed tables and volumes.

To determine the default file format for the catalog or schema.

To specify the computational resources for processing data in the catalog or schema.