DA0-002 Practice Exam 58 — 20 Questions

Question 1

A data analyst needs to compare the salary distribution across five departments. Which visualization is most appropriate?

Accepted Answer

Side-by-side box plot. A side-by-side box plot (option B) is the most appropriate visualization for comparing salary distributions across multiple departments because it displays the median, quartiles, and potential outliers for each group simultaneously. This allows the analyst to assess central tendency, spread, and skewness across all five departments in a single, compact chart.

Answer

Line chart

Answer

Scatter plot

Answer

Stacked bar chart

Question 2

A data analyst is reviewing a SQL query that joins three large tables. The query takes over an hour to run. The analyst notices that the WHERE clause filters on indexed columns in only two tables. Which of the following should the analyst do first to improve performance?

Accepted Answer

Check the query execution plan and optimize join order. The query execution plan reveals how the database engine processes joins and filters. By checking the plan, the analyst can identify the most selective filter and rearrange the join order to reduce the number of rows processed early, which is the most impactful first step. Optimizing join order leverages existing indexes without requiring schema changes or hardware upgrades.

Answer

Use subqueries instead of joins

Answer

Add indexes to all columns used in joins

Answer

Increase server memory

Question 3

Which THREE elements should be included in a data insight report to ensure it is actionable? (Choose three.)

Accepted Answer

The level of confidence or statistical significance.. An actionable data insight report must include the level of confidence or statistical significance to allow decision-makers to assess the reliability of the findings. Without this, stakeholders cannot determine whether the observed patterns are likely to be real or due to random chance, which is critical for making data-driven decisions.

Answer

The source code of the analysis scripts.

Answer

All raw data used in the analysis.

Question 4

A data analyst is communicating insights about a sales forecast to stakeholders. Which three of the following should the analyst include to build trust and clarity? (Select THREE.)

Accepted Answer

The confidence intervals around the forecast.. Confidence intervals are correct because they quantify the uncertainty around the forecast, providing a range within which the true value is expected to fall with a certain probability (e.g., 95%). This directly builds trust by showing stakeholders that the analyst acknowledges variability and does not present a single point estimate as absolute truth.

Answer

Only the most optimistic scenario.

Answer

The raw data used for the forecast.

Question 5

A data analyst calculates a correlation coefficient of -0.85 between temperature and heating costs. What does this indicate?

Accepted Answer

Strong negative correlation. A correlation coefficient of -0.85 indicates a strong negative linear relationship between temperature and heating costs. As temperature increases, heating costs decrease significantly, and the magnitude of 0.85 (close to -1) confirms the strength of this inverse association.

Answer

No correlation

Answer

Strong positive correlation

Answer

Weak negative correlation

Question 6

A data analyst creates a bar chart showing monthly sales for the current year. The CEO asks to see the same data but grouped by region. Which chart type is most appropriate for this new requirement?

Accepted Answer

Grouped bar chart. A grouped bar chart is the most appropriate choice because it allows the CEO to compare monthly sales across different regions side by side within each month. This chart type effectively displays two categorical variables (month and region) and one quantitative variable (sales), making it easy to see both regional performance and monthly trends simultaneously.

Answer

Pie chart

Answer

Stacked bar chart

Answer

Line chart

Question 7

A data analyst is creating a data story about sales performance. Which THREE elements are essential for effective data storytelling? (Choose THREE.)

Accepted Answer

A clear narrative with a beginning, middle, and end.. A clear narrative with a beginning, middle, and end is the structural backbone of effective data storytelling. It guides the audience through the data insights in a logical, engaging sequence, transforming raw numbers into a compelling story that drives understanding and retention.

Answer

Raw data tables for reference.

Answer

Use of multiple chart types to show variety.

Question 8

A data analyst is designing a dashboard for non-technical managers. Which TWO design principles should be applied? (Choose TWO.)

Accepted Answer

Place the most important metrics at the top.. Placing the most important metrics at the top follows the principle of visual hierarchy, ensuring that non-technical managers immediately see key performance indicators without scrolling. This aligns with dashboard design best practices for executive audiences, where attention span is limited and decisions rely on top-level data first.

Answer

Use pie charts for all comparisons.

Answer

Include complex statistical terms in labels.

Answer

Provide interactive filters for drill-down.

Question 9

A retail company is migrating its on-premises data warehouse to a cloud data warehouse. The current ETL process extracts data from a transactional database (SQL Server) and a web analytics system (JSON logs). The ETL runs nightly and takes 6 hours. The business requires that the new cloud warehouse support real-time reporting with data latency of less than 15 minutes. The data engineer proposes using change data capture (CDC) from the SQL Server database and streaming the JSON logs via a message queue. However, management is concerned about cost and complexity. The engineer must design a solution that meets the latency requirement while minimizing operational overhead. Which approach should the engineer recommend?

Accepted Answer

Implement CDC for the SQL Server database and stream the JSON logs via a message queue to the cloud warehouse. CDC captures only changed rows from SQL Server, minimizing data volume and enabling near-real-time ingestion, while streaming JSON logs via a message queue (e.g., Apache Kafka or Amazon Kinesis) provides sub-15-minute latency. This combination meets the latency requirement without the overhead of full batch exports or complex virtualization, addressing management's cost and complexity concerns.

Answer

Export the SQL Server data to flat files every 15 minutes and use a cloud storage trigger to load

Answer

Continue with nightly batch loads but increase the frequency to every hour

Answer

Use a data virtualization tool to query the source systems directly without moving data

Question 10

A data analyst receives a file with the extension .json. This file contains product information with attributes that vary between records. How should this file be classified?

Accepted Answer

Semi-structured data. A JSON file with varying attributes per record is a classic example of semi-structured data. Unlike strictly structured data (e.g., a relational table with fixed columns), JSON allows each object to have a different set of key-value pairs, making it schema-flexible. This self-describing nature, where metadata is embedded within the data itself, is the defining characteristic of semi-structured formats.

Answer

Structured data

Answer

Transactional data

Answer

Unstructured data

Question 11

A retail company wants to predict future sales based on historical data. Which modeling approach is most appropriate if the data shows a clear seasonal pattern?

Accepted Answer

Time series analysis. Time series analysis is specifically designed to model data points indexed in time order, making it ideal for capturing and forecasting seasonal patterns. Unlike regression models, it accounts for autocorrelation, trends, and seasonality components, which are critical for accurate sales prediction from historical data.

Answer

Linear regression

Answer

K-means clustering

Answer

Logistic regression

Question 12

Which TWO are best practices for data storytelling?

Accepted Answer

Start with the conclusion. Starting with the conclusion is a best practice for data storytelling because it immediately communicates the key insight to the audience, allowing them to understand the takeaway before diving into supporting details. This approach aligns with the inverted pyramid structure used in data communication, where the most critical finding is presented first to capture attention and provide context for the subsequent data. It ensures that even if the audience does not follow every detail, they still grasp the primary message.

Answer

Use complex jargon to show expertise

Answer

Use a single visualization to avoid confusion

Answer

Include all data points for completeness

Question 13

A DBA wants to improve query performance on a large table that is frequently filtered on two columns: department_id and hire_date. The table has millions of rows. Which index strategy would be most effective?

Accepted Answer

Create a composite B-tree index on (department_id, hire_date). A composite B-tree index on (department_id, hire_date) is most effective because it allows the database to satisfy equality and range predicates on both columns in a single index scan. B-tree indexes are optimized for high-cardinality columns and support efficient multi-column filtering when the leading column matches the query's equality condition, followed by the range condition on hire_date.

Answer

Create a bitmap index on hire_date

Answer

Create a hash index on department_id only

Answer

Create two separate B-tree indexes, one on each column

Question 14

Refer to the exhibit. Which clause is used to aggregate the data by department?

Accepted Answer

GROUP BY. The GROUP BY clause is used to aggregate data by department because it groups rows that have the same values in the specified column(s), allowing aggregate functions like SUM, AVG, or COUNT to be applied per group. In SQL, without GROUP BY, aggregate functions would operate on the entire result set, not per department.

Answer

HAVING

Answer

WHERE

Answer

ORDER BY

Question 15

Refer to the exhibit. An analyst runs a query to count orders in June 2023 and gets 12,345. However, a dashboard shows 12,298 for the same month. What is the most likely cause?

Accepted Answer

The dashboard includes time zone conversion. The most likely cause is that the dashboard applies a time zone conversion to the order timestamps, while the analyst's query counts orders based on UTC or a different time zone. If the dashboard converts timestamps to a local time zone (e.g., US/Eastern), orders placed near midnight UTC may fall into a different calendar day or month, causing a discrepancy of 47 orders. This is a common issue when raw data is stored in UTC but reporting tools apply a time zone offset without adjusting the query logic.

Answer

The query has a syntax error

Answer

The query excludes orders that were canceled

Answer

The dashboard is using a different data source

Question 16

Refer to the exhibit. An analyst sees this log during data acquisition. What action should be taken first?

Accepted Answer

Modify the ETL mapping for data types. The log shows a data type mismatch during ETL (Extract, Transform, Load) processing, where a date field is being read as a string. The correct first action is to modify the ETL mapping for data types to ensure the date string is properly cast or converted to the target date format, preventing data loss or corruption. This aligns with standard data acquisition best practices: adjust the transformation layer to handle source data anomalies before rejecting or altering the source.

Answer

Reject the entire dataset

Answer

Ignore warnings and continue

Answer

Correct the date string in the source

Question 17

The exhibit shows an SQL query executed on an 'orders' table that contains 'order_id', 'customer_id', and 'order_date'. What is the purpose of this query?

Accepted Answer

Identify customers who placed more than 5 orders in 2023. The query groups orders by customer_id and filters using a HAVING clause with COUNT(*) > 5, which counts the number of orders per customer. The WHERE clause restricts orders to those placed in 2023, so the result identifies customers who placed more than 5 orders in that year. This matches option D exactly.

Answer

Count total orders per customer regardless of date

Answer

Calculate average order count per customer for 2023

Answer

Find products with more than 5 orders in 2023

Question 18

Refer to the exhibit. A data analyst wants to create a visualization that best shows the trend of sales over time for each department. Which chart type should be used?

Accepted Answer

Line chart with multiple lines.. A line chart with multiple lines is the best choice because it clearly shows the trend of sales over time for each department, with time on the x-axis and sales on the y-axis. Each line represents a department, making it easy to compare trends across departments while preserving the continuous nature of time. This aligns with the goal of visualizing trends, as line charts excel at showing changes over a continuous interval.

Answer

Stacked bar chart.

Answer

Pie chart for each quarter.

Answer

Grouped bar chart.

Question 19

An analyst is preparing data for an A/B test and wants to ensure valid results. Which TWO of the following should be considered when calculating the required sample size?

Accepted Answer

Desired effect size. Sample size calculation depends on desired effect size and statistical power, among other factors like significance level.

Answer

Data dimensionality

Answer

Skewness of data

Answer

Number of features

Question 20

A data analyst is extracting data from a web page using web scraping techniques. The data will be used for market research. Which TWO of the following are common challenges associated with web scraping?

Accepted Answer

Legal and ethical restrictions. Web scraping often involves accessing data that may be protected by copyright, terms of service, or privacy regulations such as GDPR or the Computer Fraud and Abuse Act (CFAA). Even if data is publicly accessible, repurposing it for market research without permission can lead to legal liability or ethical violations, making this a fundamental challenge.

Answer

Limited API rate limits

Answer

High latency of data transfer

Answer

Inconsistent data formatting