Courseiva

CCNA Data Acquisition and Preparation Questions

75 of 208 questions · Page 1/3 · Data Acquisition and Preparation · Answers revealed

Practice these questionsDomain overviewAll questions
1
MCQmedium

A data analyst needs to count the number of orders placed by each customer, but only for customers who have placed more than 5 orders. Which SQL clause should be used to filter the aggregated results?

A.FILTER
B.HAVING
C.WHERE
D.LIMIT
AnswerB

HAVING filters groups after aggregation, so it can test COUNT(*) > 5 per customer. WHERE cannot reference aggregate functions, making HAVING the clause that satisfies the requirement to restrict results to customers exceeding five orders.

Why this answer

HAVING is used to filter groups after aggregation. The query would use GROUP BY customer_id, then HAVING COUNT(*) > 5.

Practice this question →
2
MCQhard

A large retail company is integrating customer data from two separate CRM systems into a new data warehouse. System A stores customer IDs as integers (e.g., 12345), while System B stores them as alphanumeric strings (e.g., 'CUST-12345-X'). Additionally, some customers exist in both systems but with slight name variations (e.g., 'John Smith' vs 'Jon Smith'). The data warehouse requires a unified customer table with a single unique identifier for each customer. The analyst needs to design the data acquisition process. Which of the following is the most appropriate first step?

A.Use a simple crosswalk table based on exact name matches to link records
B.Load all data from both systems into a staging table, then run a fuzzy matching algorithm to identify duplicates
C.Perform data profiling to analyze data distributions, data types, and quality issues in each source
D.Standardize all customer IDs to a common format (e.g., UUIDs) and then merge the tables
AnswerC

Data profiling first exposes the exact type mismatch between integer and string customer IDs, plus the scale of near-duplicate names such as 'John Smith' versus 'Jon Smith'. This evidence, covering distributions, formats and quality, is required before choosing a matching or standardisation strategy for the unified customer table.

Why this answer

Data profiling is the foundational first step in any data integration project. It systematically assesses source data types, formats, completeness, and quality issues (e.g., integer vs. alphanumeric IDs, name variations) before designing transformation logic. Without profiling, subsequent steps like fuzzy matching or ID standardization risk being built on incorrect assumptions about the data.

Exam trap

The trap here is that candidates often jump to a technical solution (fuzzy matching or ID standardization) without recognizing that data profiling is the prerequisite step that validates source assumptions and prevents costly rework.

How to eliminate wrong answers

Option A is wrong because exact name matches cannot resolve the known name variations (e.g., 'John Smith' vs 'Jon Smith'), leading to missed linkages and duplicate customers. Option B is wrong because loading all data into a staging table before profiling risks propagating unknown data quality issues (e.g., inconsistent ID formats, nulls) into the staging area, making fuzzy matching less reliable and harder to tune. Option D is wrong because standardizing IDs to a common format (e.g., UUIDs) without first profiling the source data ignores the need to understand existing relationships and quality issues, and may break referential integrity if applied prematurely.

Practice this question →
3
MCQmedium

In a dataset of employee salaries, the analyst notices one value that is significantly higher than the rest. Using the IQR method, which values are typically considered outliers?

A.Values beyond Q1 - 3*IQR or Q3 + 3*IQR
B.Values beyond Q1 - 1.5*IQR or Q3 + 1.5*IQR
C.Values beyond mean ± 2 standard deviations
D.Values beyond min and max
AnswerB

Values falling below Q1 − 1.5×IQR or above Q3 + 1.5×IQR are flagged as outliers, satisfying the stem's requirement to identify the salary that sits significantly higher than the rest. Tukey's fences define this boundary, so an extreme high salary exceeding Q3 + 1.5×IQR is correctly detected.

Why this answer

Outliers are values less than Q1 - 1.5*IQR or greater than Q3 + 1.5*IQR.

Practice this question →
4
MCQmedium

A healthcare analytics team is ingesting a nightly CSV extract of patient encounters. The extract occasionally contains rows where the 'discharge_date' is earlier than the 'admission_date'. The team wants the pipeline to flag these rows for review rather than silently load them. Which data preparation action best addresses this requirement?

A.Swap the values in admission_date and discharge_date whenever discharge_date is earlier.
B.Load the rows as-is and rely on downstream report filters to exclude invalid dates.
C.Apply a validation rule that compares admission_date and discharge_date and routes failing rows to a quarantine table with a reason code.
D.Drop all rows where discharge_date is earlier than admission_date before loading.
AnswerC

A validation rule that checks the chronological relationship and diverts violations to quarantine preserves the suspect records while preventing them from entering the trusted layer. Attaching a reason code supports triage and root-cause analysis. This matches the requirement to flag rather than silently load or delete, and it is a standard data quality control in healthcare ETL pipelines.

Why this answer

The requirement is to identify and isolate suspect records for human review. A validation rule comparing the two dates and routing failures to a quarantine table with a reason code achieves this without deleting data or contaminating the trusted layer. Dropping, passing through, or auto-correcting records all fail because they either lose information, hide the problem, or make unverified changes to clinical data.

Exam trap

The trap here is treating a data quality exception as something to fix or delete automatically, when the stated requirement is to flag it for review.

Practice this question →
5
MCQeasy

A data analyst needs to combine two datasets: one contains customer information (customer_id, name, address) and the other contains order information (order_id, customer_id, order_date). The analyst wants to include all customers, even those who have not placed orders. Which type of join should be used?

A.FULL OUTER JOIN
B.INNER JOIN
C.LEFT JOIN
D.RIGHT JOIN
AnswerC

A LEFT JOIN returns every row from the customer table plus matching order rows, emitting NULLs where no orders exist. This satisfies the stem's requirement to retain customers without orders, unlike an INNER JOIN which would exclude them.

Why this answer

A LEFT JOIN returns all rows from the left table (customers) and the matching rows from the right table (orders). If a customer has no orders, the order columns will contain NULLs. This satisfies the requirement to include all customers, even those without orders.

Exam trap

The trap here is that candidates often confuse LEFT JOIN with INNER JOIN, assuming all customers must have orders, or they pick FULL OUTER JOIN thinking it includes all customers, but it also includes unmatched orders, which is not required.

How to eliminate wrong answers

Option A is wrong because a FULL OUTER JOIN returns all rows from both tables, which would include unmatched orders (if any) — unnecessary for this requirement. Option B is wrong because an INNER JOIN returns only rows with matching keys in both tables, excluding customers who have never placed an order. Option D is wrong because a RIGHT JOIN returns all rows from the right table (orders) and only matching rows from the left table (customers), which would exclude customers without orders.

Practice this question →
6
MCQmedium

Refer to the exhibit. What does the query return?

A.All orders grouped by customer ID.
B.Customers who have placed at least 5 orders.
C.Customers who have placed more than 5 orders.
D.All customers who have placed orders.
AnswerC

The HAVING clause filters aggregated groups rather than individual rows, so COUNT(order_id) grouped by customer is compared against 5. Only customers whose order count exceeds that threshold are returned, excluding those with five or fewer orders.

Why this answer

The query uses a HAVING clause with COUNT(*) > 5, which filters groups (by customer ID) to only those with more than 5 orders. The GROUP BY customer ID ensures the count is per customer, so the result is customers who have placed more than 5 orders. Option C is correct because the condition is strictly greater than 5, not at least 5.

Exam trap

CompTIA often tests the distinction between 'at least' (>=) and 'more than' (>) in HAVING clauses, and candidates may misread the condition as including exactly 5 orders.

How to eliminate wrong answers

Option A is wrong because the query does not return all orders; it returns aggregated results (counts) per customer, not individual order rows. Option B is wrong because the condition is COUNT(*) > 5, not COUNT(*) >= 5; 'at least 5' would include exactly 5, which is excluded by the strict greater-than operator. Option D is wrong because the HAVING clause filters out customers with 5 or fewer orders; the query does not return all customers who have placed orders, only those exceeding the threshold.

Practice this question →
7
MCQeasy

A marketing analyst needs to combine customer data from a CRM database with social media engagement data from a third-party API. Which data acquisition method is most appropriate?

A.Web scraping
B.Manual data entry
C.API integration
D.Batch file upload
AnswerC

API integration lets the analyst pull social media engagement data programmatically from the third-party service and join it with CRM records, satisfying the need to combine two distinct sources. It handles authentication, scheduled retrieval and structured responses that flat-file exports cannot provide reliably.

Why this answer

API integration is the most appropriate method because it allows the analyst to programmatically retrieve structured social media engagement data directly from the third-party service's RESTful or GraphQL API endpoints. This approach ensures real-time or near-real-time data synchronization, supports authentication (e.g., OAuth 2.0), and returns data in standardized formats like JSON or XML, which can be directly ingested into the CRM system without manual intervention.

Exam trap

The trap here is that candidates may confuse web scraping with API integration, assuming both can retrieve web data, but the question specifically requires combining structured data from a third-party API, where web scraping would be unreliable, unauthorized, and technically inappropriate for programmatic data acquisition.

How to eliminate wrong answers

Option A is wrong because web scraping is used to extract unstructured data from HTML pages, which is inefficient, brittle, and often violates the third-party API's terms of service; it is not designed for reliable, authenticated access to structured social media metrics. Option B is wrong because manual data entry is error-prone, time-consuming, and impractical for large volumes of social media engagement data, and it lacks any automated validation or consistency checks. Option D is wrong because batch file upload assumes the data is already exported into a file (e.g., CSV) and delivered manually, which introduces latency and requires the third-party to support file exports, whereas the API provides direct, on-demand access to live data.

Practice this question →
8
Multi-Selecthard

Which THREE are best practices for acquiring data via web scraping? (Select exactly 3)

Select 3 answers
A.Use multiple IP addresses
B.Respect robots.txt
C.Identify yourself with a user-agent
D.Scrape all data without regard to terms
E.Limit request rate
AnswersB, C, E

Honouring robots.txt satisfies the legal and ethical constraint of scraping only permitted paths. The file declares which directories crawlers may access, so parsing it before requesting pages prevents the crawler from breaching site owner directives and reduces the risk of being blocked or facing legal action.

Why this answer

Option B is correct because robots.txt is the standard mechanism (the Robots Exclusion Protocol) by which site owners declare which paths crawlers may access, and honoring it keeps scraping ethical and within the site's stated permissions. Option C is correct because setting a descriptive User-Agent header identifies your crawler and provides contact information, letting administrators recognize and reach you rather than blocking you as an anonymous bot. Option E is correct because limiting request rate (e.g., throttling to a modest number of requests per second and honoring Retry-After on 429/503 responses) avoids overloading servers and reduces the chance of being rate-limited or banned.

Option A does not belong because rotating multiple IP addresses to evade blocks is an evasion technique, not a best practice, and can violate terms of service. Option D does not belong because ignoring terms of service and scraping indiscriminately is unethical and often unlawful, contrary to responsible scraping.

Practice this question →
9
MCQmedium

During data profiling, an analyst wants to identify the number of distinct values in a column. Which SQL function should be used?

A.DISTINCT(column)
B.COUNT(DISTINCT column)
C.COUNT(*)
D.COUNT(column)
AnswerB

COUNT(DISTINCT column) returns the cardinality of a column by eliminating duplicate values before counting, directly satisfying the requirement to identify the number of distinct values during data profiling. Unlike COUNT(*), which counts every row including repeats, this aggregate yields the unique-value total the analyst needs.

Why this answer

COUNT(DISTINCT column) returns the number of unique non-null values in a column, which is exactly what data profiling requires to measure cardinality. It is the standard SQL aggregate for distinct value counts.

Exam trap

DA0-002 often tests the difference between COUNT(*), COUNT(column), and COUNT(DISTINCT column) — candidates must remember that only COUNT(DISTINCT) deduplicates values.

How to eliminate wrong answers

Option A is wrong because DISTINCT(column) is not a valid aggregate function — DISTINCT is a keyword used with SELECT or inside aggregate functions like COUNT, not a standalone function. Option C is wrong because COUNT(*) returns the total number of rows, including duplicates and nulls, not the number of distinct values. Option D is wrong because COUNT(column) returns the number of non-null values in the column, which still counts duplicates.

Practice this question →
10
MCQhard

A data analyst is writing a query to rank products by total sales amount within each category. They want ties to have the same rank and no gaps in the ranking sequence. Which window function should they use?

A.ROW_NUMBER()
B.DENSE_RANK()
C.RANK()
D.NTILE()
AnswerB

DENSE_RANK() assigns identical ranks to tied sales totals yet never skips subsequent numbers, satisfying the no-gaps constraint. RANK() would leave gaps after ties, while ROW_NUMBER() breaks ties arbitrarily. Partitioning by category and ordering by total sales amount yields the required per-category sequence.

Why this answer

DENSE_RANK() assigns the same rank to tied rows and does not skip subsequent ranks, so if two products tie for rank 1, the next product receives rank 2 (no gap). This matches the requirement for ties to share a rank with no gaps in the sequence. It is the correct window function for this ranking behavior.

Exam trap

DA0-002 often tests the distinction between RANK() (gaps after ties) and DENSE_RANK() (no gaps), and candidates frequently pick RANK() when the question specifies 'no gaps'.

How to eliminate wrong answers

Option A (ROW_NUMBER()) is wrong because it assigns a unique sequential number to every row, even ties, so tied products would receive different ranks. Option C (RANK()) is wrong because it assigns the same rank to ties but skips subsequent ranks (e.g., 1,1,3), creating gaps. Option D (NTILE()) is wrong because it divides rows into a specified number of roughly equal buckets, not a ranking based on sales amount.

Practice this question →
11
MCQeasy

A marketing team wants to collect data on competitor pricing for similar products. Which data source is most appropriate?

A.Customer surveys
B.Internal ERP system
C.External public web scraping
D.Internal sales data
AnswerC

External public web scraping directly satisfies the competitor-pricing constraint, since competitor prices sit outside the organisation's own systems and cannot be obtained from internal transactional databases or CRM records. Scraping public pages supplies the required market data without purchasing third-party reports, making it the most appropriate source for this competitive-intelligence scenario.

Why this answer

External public web scraping is the most appropriate data source because competitor pricing is publicly available on websites, and web scraping allows automated extraction of this structured or unstructured data. This approach directly addresses the need for external competitive intelligence without relying on internal or customer-reported data.

Exam trap

The trap here is that candidates may confuse internal data sources (ERP, sales) with external data needs, or mistakenly think customer surveys can provide accurate, unbiased competitor pricing data.

How to eliminate wrong answers

Option A is wrong because customer surveys collect subjective opinions and self-reported data, not objective, real-time competitor pricing from external sources. Option B is wrong because an internal ERP system contains only the company's own operational and financial data, not competitor pricing information. Option D is wrong because internal sales data reflects the company's own transactions and pricing, not competitor pricing.

Practice this question →
12
Multi-Selectmedium

Which TWO of the following are common methods for acquiring data from external sources?

Select 2 answers
A.Data warehousing
B.Manual data entry
C.Public APIs
D.Web scraping
E.Direct database connection to an internal server
AnswersC, D

Public APIs expose structured endpoints that return JSON or XML over HTTP, letting scripts pull external data directly without manual exports. This satisfies the stem's requirement for acquiring data from external sources, since the API provider hosts the data and your tooling retrieves it programmatically on demand.

Why this answer

Public APIs (C) are a standard method for acquiring external data, since they expose structured endpoints (typically REST/HTTP or GraphQL) that applications can query programmatically to pull data from third-party services. Web scraping (D) is also a common external data acquisition method, using tools such as HTTP clients and HTML parsers to extract data from public web pages when no API is available. Both are specifically designed to bring in data from outside sources, which matches the scenario.

Data warehousing (A) is a storage and analytics architecture, not an acquisition method. Manual data entry (B) is a way to input data, usually from internal or human sources, not a typical external acquisition technique. Direct database connection to an internal server (E) targets internal systems, so it is not an external data source method.

Exam trap

The trap here is that candidates may confuse data warehousing (a storage/management process) with data acquisition methods, or think manual data entry is a valid external acquisition method, when the exam specifically tests automated, programmatic techniques for pulling data from outside the organization.

Practice this question →
13
MCQmedium

An analyst needs to count the number of orders per customer but only for customers who have placed more than 5 orders. Which SQL construct allows filtering after aggregation?

A.WHERE COUNT(*) > 5
B.LIMIT 5
C.HAVING COUNT(*) > 5
D.ORDER BY COUNT(*) > 5
AnswerC

HAVING filters grouped rows after aggregation, unlike WHERE, which filters individual rows before grouping. Because the requirement is to count orders per customer and then retain only those exceeding five, HAVING COUNT(*) > 5 applies the condition to the aggregated count, satisfying the post-aggregation filtering constraint in the stem.

Why this answer

HAVING is the SQL clause specifically designed to filter groups after aggregation has been performed by GROUP BY. Because COUNT(*) is an aggregate function, it cannot be evaluated in the WHERE clause, which runs before grouping. HAVING executes after GROUP BY and aggregation, so it can reference aggregate results like COUNT(*) > 5.

Exam trap

DA0-002 often tests the WHERE vs. HAVING distinction by presenting an aggregate filter and tempting candidates to pick WHERE because it is the more familiar filtering clause.

How to eliminate wrong answers

Option A is wrong because WHERE is evaluated before GROUP BY and aggregation, so aggregate functions like COUNT(*) are not yet available and the query will error out. Option B is wrong because LIMIT restricts the number of rows returned, not the number of orders per customer, and it cannot filter based on an aggregate value. Option D is wrong because ORDER BY only sorts result rows and cannot be used as a filter predicate; COUNT(*) > 5 in an ORDER BY clause is a boolean expression that does not restrict rows.

Practice this question →
14
Multi-Selecthard

A data scientist is merging retail transaction data from online and in-store sources. Which THREE steps are required to ensure data consistency?

Select 3 answers
A.Ensure product IDs are standardized across sources
B.Convert all monetary amounts to a common currency
C.Remove all transactions with missing customer ID
D.Synchronize timestamps to a single time zone
E.Merge data using only store location
AnswersA, B, D

Standardising product IDs gives both sources a shared join key, satisfying the consistency requirement for merging online and in-store transactions. Without identical identifiers, the same product appears as distinct records, producing duplicate or unmatched rows during the merge.

Why this answer

Option A is correct because standardizing product IDs across online and in-store sources is essential for matching the same item across systems, preventing duplicate or mismatched records during the merge. Option B is correct because converting all monetary amounts to a common currency ensures that transaction values are comparable and can be aggregated without unit inconsistencies. Option D is correct because synchronizing timestamps to a single time zone aligns event times across sources, which is necessary for accurate chronological ordering and time-based joins.

Option C is not required because removing transactions with missing customer IDs would discard valid sales data and is a data-quality choice, not a consistency requirement. Option E is not required because merging on store location alone would ignore online transactions and other key fields, producing an incomplete and inconsistent dataset.

Exam trap

The trap is selecting data-cleaning actions (dropping null customer IDs) or weak join keys (store location) instead of the true consistency steps of standardizing IDs, currency, and time zones.

Practice this question →
15
MCQhard

A data engineer needs to acquire data from a legacy mainframe system that does not support modern APIs or direct database connectivity. Which approach is most feasible?

A.Re-platform the mainframe to a modern system
B.Use a database gateway
C.Use FTP to transfer flat files
D.Manual data entry
AnswerC

FTP moves flat-file extracts from the mainframe, requiring only basic network file transfer rather than APIs or database drivers. The legacy system's lack of modern interfaces and direct connectivity makes this the feasible extraction route, with files parsed downstream.

Why this answer

FTP (File Transfer Protocol, RFC 959) is a widely supported, low-overhead method for transferring flat files (e.g., CSV, EBCDIC-encoded text) from legacy mainframe systems that lack modern APIs or direct database connectivity. Mainframes like IBM z/OS natively support FTP, allowing the data engineer to schedule periodic file exports without requiring system modernization or complex middleware.

Exam trap

The trap here is that candidates may assume a database gateway (Option B) is always the best integration approach, but the question explicitly denies direct database connectivity, making FTP the only practical option that leverages existing mainframe capabilities without major infrastructure changes.

How to eliminate wrong answers

Option A is wrong because re-platforming the mainframe to a modern system is a costly, high-risk, and time-consuming project that far exceeds the scope of a simple data acquisition task; it introduces unnecessary complexity and potential downtime. Option B is wrong because a database gateway typically requires the mainframe to support ODBC/JDBC or similar database connectivity protocols, which the question explicitly states is not available. Option D is wrong because manual data entry is error-prone, unscalable, and impractical for any reasonable volume of data, violating basic data integrity and efficiency requirements.

Practice this question →
16
MCQmedium

While profiling a customer dataset, an analyst finds that the 'country' column contains values including 'USA', 'United States', 'U.S.A.', and 'US' for the same nation, plus 'usa ' with trailing whitespace. Reports grouped by country show fragmented counts. Which preparation step resolves this issue?

A.Impute the mode of the column for every row to make all values identical.
B.Apply standardization by trimming whitespace and mapping all variants to a single canonical country code.
C.Remove all rows containing any country value that is not 'USA' to enforce consistency.
D.Increase the column length to accommodate the longest variant string.
AnswerB

Standardization collapses the synonymous spellings and whitespace variants into one canonical representation, which is exactly what eliminates the fragmented grouping. Mapping to a controlled code such as ISO 3166 also makes future joins and comparisons reliable. Trimming handles the formatting defect while the mapping handles the semantic equivalence, so both parts of the problem are addressed together.

Why this answer

The column is not missing data; it holds the same nation under several spellings and spacing. Standardizing with trimming plus a canonical mapping such as an ISO code unifies those variants so grouped counts consolidate correctly. Deleting rows, widening the column, or imputing a single value either destroys data or leaves the fragmentation untouched.

Exam trap

The trap here is mistaking a representation inconsistency for missing or invalid data, which leads to deletion or imputation instead of standardization.

Practice this question →
17
MCQmedium

A data analyst is working with a dataset that contains a 'salary' column with extreme outliers. Before performing a linear regression analysis, the analyst wants to reduce the impact of these outliers. Which technique should be applied?

A.One-hot encoding
B.Standardization
C.Binning
D.Winsorizing
AnswerD

Winsorizing replaces extreme values with the nearest non-extreme value, typically at a specified percentile (e.g., 5th and 95th). This reduces the influence of outliers without removing them, preserving the sample size. It is suitable for linear regression as it limits the leverage of extreme points while maintaining the data distribution's shape.

Why this answer

Winsorizing is the correct technique because it caps extreme values at a specified percentile, reducing their influence while retaining all data points. For linear regression, outliers can disproportionately affect the slope and intercept, so limiting their impact is crucial. Winsorizing is preferable to deletion when sample size is limited, and it preserves the order of values, making it a robust choice for this scenario.

Exam trap

The trap here is thinking that standardization or normalization reduces outlier impact, when in fact they only change the scale and do not address the extreme values themselves.

Practice this question →
18
MCQmedium

A logistics company receives GPS tracking data from fleet vehicles at 1-second intervals via a cellular network. The data is used to optimize routes and monitor driver behavior. Recently, the data acquisition system has been missing updates for some vehicles when they pass through tunnels or remote areas. The data team notices gaps during these periods. The company needs a solution to ensure near-real-time data continuity. What should they do?

A.Use a hybrid approach that combines cellular and Wi-Fi networks
B.Implement a store-and-forward mechanism that buffers data on the vehicle's onboard unit and uploads when connectivity resumes
C.Increase the frequency of data transmission to every 0.5 seconds
D.Switch to a satellite-based GPS system
AnswerB

Store-and-forward buffers readings on the onboard unit during tunnel or remote-area outages, then uploads the backlog once cellular connectivity returns. This preserves near-real-time continuity by preventing permanent data gaps, unlike simply retrying requests that fail while the vehicle remains disconnected.

Why this answer

A store-and-forward mechanism buffers GPS data locally on the vehicle's onboard unit during connectivity loss (e.g., in tunnels) and automatically uploads the backlog when cellular connectivity resumes. This ensures data continuity without requiring real-time transmission, directly addressing the intermittent connectivity issue while maintaining near-real-time updates.

Exam trap

The trap here is that candidates confuse the data source (GPS) with the transmission method, thinking satellite GPS solves connectivity issues, when the real problem is the cellular network's coverage gaps, not the positioning technology.

How to eliminate wrong answers

Option A is wrong because Wi-Fi networks are not suitable for fleet vehicles in motion; they have limited range and are not available in tunnels or remote areas, so combining them with cellular does not solve the core problem of coverage gaps. Option C is wrong because increasing transmission frequency to 0.5 seconds would exacerbate data loss during connectivity gaps and increase bandwidth/cost without addressing the root cause of missing updates. Option D is wrong because switching to satellite-based GPS only changes the positioning source, not the data transmission method; the vehicle still needs a network to send data, and satellite communication (e.g., Iridium) is expensive, high-latency, and not typically used for high-frequency GPS telemetry in logistics.

Practice this question →
19
MCQmedium

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?

A.Export the SQL Server data to flat files every 15 minutes and use a cloud storage trigger to load
B.Continue with nightly batch loads but increase the frequency to every hour
C.Implement CDC for the SQL Server database and stream the JSON logs via a message queue to the cloud warehouse
D.Use a data virtualization tool to query the source systems directly without moving data
AnswerC

CDC captures only changed rows from SQL Server, and a message queue streams JSON logs continuously, cutting latency from six hours to under fifteen minutes. This satisfies the sub-15-minute requirement while avoiding full nightly batch reloads, keeping operational overhead lower than custom polling scripts.

Why this answer

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.

Exam trap

The trap here is that candidates may choose Option A or D because they seem simpler, but they fail to meet the strict latency requirement or introduce hidden operational complexity, while Option C's CDC and streaming approach is the only one that balances low latency with minimal overhead.

How to eliminate wrong answers

Option A is wrong because exporting SQL Server data to flat files every 15 minutes introduces latency from file generation, cloud storage upload, and trigger-based loading, which can easily exceed the 15-minute requirement and adds operational overhead for file management. Option B is wrong because increasing nightly batch loads to hourly still results in up to 60-minute latency, failing the 15-minute requirement, and does not address the need for real-time streaming of JSON logs. Option D is wrong because data virtualization queries source systems directly, which can cause performance degradation on the transactional SQL Server and web analytics system, and does not provide a persistent, low-latency data pipeline to the cloud warehouse.

Practice this question →
20
MCQmedium

A data analyst is tasked with extracting data from a legacy system that outputs fixed-width text files. The analyst needs to parse these files into a structured format. Which tool or method is most appropriate for this task?

A.A spreadsheet application
B.An ETL tool with a graphical interface
C.A scripting language such as Python
D.SQL
AnswerC

A scripting language such as Python parses fixed-width files by slicing each line at defined column positions, handling the absence of delimiters. This directly addresses the fixed-width constraint, unlike delimiter-based tools that cannot infer field boundaries.

Why this answer

Python is the most appropriate choice because fixed-width text files require precise column slicing based on character positions, which Python's string slicing and libraries like `struct` or `pandas.read_fwf` handle natively. Unlike graphical ETL tools or spreadsheets, Python provides programmatic control to define exact field widths, handle edge cases like missing delimiters, and process large files efficiently without manual intervention.

Exam trap

The trap here is that candidates assume a graphical ETL tool is always the best for data extraction, but the question specifically tests the ability to handle unstructured or semi-structured legacy formats where scripting provides the necessary precision and automation.

How to eliminate wrong answers

Option A is wrong because spreadsheet applications like Excel are designed for delimited data (e.g., CSV) and lack built-in functionality to parse fixed-width columns without manual column splitting, which is error-prone and impractical for large datasets. Option B is wrong because while ETL tools can parse fixed-width files, they typically require defining column widths in a graphical interface, which is less flexible and harder to automate than a scripting language for legacy systems with inconsistent formatting. Option D is wrong because SQL operates on structured data within a database and cannot directly parse raw fixed-width text files; it would require the data to be pre-processed into a table format first.

Practice this question →
21
MCQmedium

A data analyst is profiling a dataset of customer orders. The 'order_date' column contains dates in various formats, including 'YYYY-MM-DD', 'DD/MM/YYYY', and 'MM-DD-YYYY'. The analyst needs to standardize these dates into a single format for time-series analysis. Which approach should the analyst take?

A.Sort the dates lexicographically and then apply a standard format.
B.Apply a single date parsing function with a fixed format string.
C.Convert the dates to Unix timestamps using a generic conversion function.
D.Use a regular expression to extract year, month, and day, then reconstruct the date in a standard format.
AnswerD

Regular expressions can parse the different date patterns by identifying the position and separators of year, month, and day. Once extracted, the components can be reassembled into a consistent format like ISO 8601. This approach handles variability without relying on locale-specific parsing.

Why this answer

The dates are stored in multiple formats, so a one-size-fits-all parsing function will fail. Using regular expressions to extract the year, month, and day components allows the analyst to handle each pattern systematically and then reconstruct a standardized date. This method is robust and does not depend on locale settings.

Exam trap

The trap here is assuming that a single date format string can handle all variations, which leads to parsing errors.

Practice this question →
22
MCQmedium

A data analyst needs to sample 1000 customers from a database of 100,000 customers for a survey, ensuring every customer has an equal chance of selection. Which sampling method is most appropriate?

A.Cluster sampling
B.Stratified sampling
C.Systematic sampling
D.Simple random sampling
AnswerD

Simple random sampling assigns every customer an identical selection probability, directly satisfying the equal-chance constraint. By drawing 1000 individuals from the full 100,000-customer frame without stratification or systematic intervals, it avoids selection bias. Stratified, cluster, or convenience methods would alter probabilities across subgroups or rely on non-random access.

Why this answer

Simple random sampling is the method where every individual in the population has an equal chance of being selected. This directly matches the requirement that every customer has an equal chance of selection. It is the most straightforward probability sampling technique.

Exam trap

DA0-002 often tests the distinction between simple random sampling and stratified or systematic sampling, causing candidates to choose stratified when the requirement is equal chance for all, not representation of subgroups.

How to eliminate wrong answers

Option A is wrong because cluster sampling involves dividing the population into clusters and randomly selecting entire clusters, which does not give every individual an equal chance (only clusters have equal chance). Option B is wrong because stratified sampling divides the population into strata and samples from each, ensuring representation but not equal chance for every individual across the whole population. Option C is wrong because systematic sampling selects every nth individual, which can introduce bias if the population has a periodic pattern, and it does not guarantee equal chance for all.

Practice this question →
23
MCQeasy

A data analyst is preparing a dataset for analysis and discovers that the 'customer_id' column has missing values. The analyst decides to remove all rows with missing 'customer_id' because it is a primary key. Which data preparation technique is being applied?

A.Discretization
B.Listwise deletion
C.Normalization
D.Imputation
AnswerB

Listwise deletion removes entire records that have missing values in any column. Here, the analyst removes rows where customer_id is missing, which is exactly listwise deletion applied to a specific column. Since customer_id is a primary key, missing values cannot be imputed, so removal is a valid approach.

Why this answer

Listwise deletion is the correct technique because it involves removing records with missing values. Since customer_id is a primary key, missing values cannot be reliably filled, so deleting those rows is a standard data cleaning step. This ensures that each record has a valid unique identifier, which is essential for relational integrity and accurate analysis.

Exam trap

The trap here is confusing missing value handling with data transformation techniques like normalization or discretization, which do not address missing data.

Practice this question →
24
MCQmedium

During data acquisition, an analyst notices that the data from an external vendor has inconsistent date formats. What is the first step the analyst should take?

A.Contact the vendor to request corrected data
B.Immediately transform dates to a standard format
C.Perform data profiling
D.Reject the entire dataset
AnswerC

Data profiling examines the vendor dataset to identify the actual date formats, patterns and anomalies present before any transformation. This establishes what standardisation is needed, satisfying the requirement to understand inconsistent formats prior to cleansing or conversion.

Why this answer

Before transforming or rejecting data, the analyst must first understand its shape, quality, and anomalies — that is data profiling. Profiling reveals the extent and pattern of the inconsistent date formats, how many records are affected, and whether other issues exist, which then informs the correct remediation approach. Jumping straight to transformation without profiling risks applying the wrong parsing rules.

Exam trap

DA0-002 often tests the data-quality workflow order — candidates who jump to 'fix it' (transform) or 'escalate it' (contact vendor) miss that profiling must come first to characterize the problem before any remediation decision.

How to eliminate wrong answers

Option A is wrong because contacting the vendor is premature — the analyst has not yet quantified the problem or confirmed it is the vendor's fault rather than a parsing issue on ingestion. Option B is wrong because transforming dates before profiling risks applying incorrect format assumptions and silently corrupting values (e.g., misreading DD/MM vs MM/DD). Option D is wrong because rejecting the entire dataset is a drastic overreaction; profiling may show only a small subset is malformed and the rest is usable.

Practice this question →
25
Multi-Selecthard

Which THREE of the following are best practices when performing data extraction for a data pipeline?

Select 3 answers
A.Performing a full refresh every time
B.Implementing error handling and logging
C.Documenting the extraction process
D.Ignoring data quality issues during extraction
E.Using incremental extraction where possible
AnswersB, C, E

Extraction jobs fail silently on source timeouts, schema drift or malformed records, so try/catch blocks with structured logging capture the failure point and row counts. This satisfies the stem's best-practise requirement by making pipeline failures detectable and diagnosable rather than corrupting downstream loads.

Why this answer

Option B is correct because robust error handling and logging are essential in a data pipeline: they capture failures (e.g., connection timeouts, schema mismatches, partial loads) and provide an audit trail for troubleshooting and reprocessing without silently losing data. Option C is correct because documenting the extraction process records source systems, connection details, extraction frequency, filters, and transformation assumptions, which enables maintainability, reproducibility, and knowledge transfer across teams. Option E is correct because incremental extraction (e.g., using high-water marks, change data capture, or timestamp/ID deltas) reduces load on source systems and shortens pipeline runtime compared with repeatedly pulling entire datasets.

Option A is not a best practice because a full refresh every time is inefficient, increases source-system load, and lengthens processing windows when only changed data is needed. Option D is not a best practice because ignoring data quality issues during extraction allows bad, missing, or malformed records to propagate downstream, corrupting analytics and requiring costly rework.

Exam trap

CompTIA often tests the misconception that full refreshes are always safer or simpler, but the trap is that they ignore the operational cost and scalability issues, while incremental extraction with proper error handling is the standard in production pipelines.

Practice this question →
26
MCQeasy

A data analyst is preparing a dataset for analysis and needs to combine data from two tables: 'orders' and 'customers'. The 'orders' table contains a 'customer_id' column, and the 'customers' table contains a 'customer_id' column as the primary key. The analyst wants to include all orders, even those without matching customers. Which type of join should be used?

A.RIGHT JOIN
B.INNER JOIN
C.LEFT JOIN
D.FULL OUTER JOIN
AnswerC

A LEFT JOIN returns all rows from the left table (orders) and matching rows from the right table (customers). If there is no match, NULLs are returned for customer columns. This ensures all orders are included, satisfying the requirement to keep orders without matching customers.

Why this answer

To preserve all rows from the orders table regardless of matching customers, a LEFT JOIN is appropriate. It keeps every order and appends customer details when available. Other join types either exclude unmatched orders or include extra unmatched customers, which is not desired.

Exam trap

The trap here is assuming that a FULL OUTER JOIN is needed to keep all orders, but it also brings in customers without orders.

Practice this question →
27
Multi-Selectmedium

A data analyst needs to identify duplicate customer records based on email and phone number. Which SQL techniques can be used to find duplicates? (Select TWO).

Select 2 answers
A.SELECT email, phone FROM customers ORDER BY email, phone
B.SELECT DISTINCT email, phone FROM customers
C.SELECT email, phone, ROW_NUMBER() OVER (PARTITION BY email, phone ORDER BY customer_id) AS rn FROM customers WHERE rn > 1
D.Use a CTE to assign ROW_NUMBER() and then select rows where rn > 1
E.SELECT email, phone, COUNT(*) FROM customers GROUP BY email, phone HAVING COUNT(*) > 1
AnswersD, E

ROW_NUMBER() partitioned by email and phone assigns sequential integers within each duplicate group, so filtering rn > 1 isolates every redundant row. This satisfies the requirement to identify duplicates while retaining full row detail, unlike aggregation alone.

Why this answer

Option E is correct because grouping by email and phone with GROUP BY and filtering with HAVING COUNT(*) > 1 returns exactly those email/phone combinations that appear more than once, which is the standard way to detect duplicate records. Option D is correct because a CTE can compute ROW_NUMBER() OVER (PARTITION BY email, phone ORDER BY customer_id) and then the outer query filters WHERE rn > 1, reliably identifying all rows beyond the first occurrence of each duplicate key. Option A is not correct because ORDER BY only sorts rows and does not detect or filter duplicates.

Option B is not correct because SELECT DISTINCT removes duplicates and returns only unique combinations, the opposite of finding them. Option C is not correct because a window function cannot be referenced in the WHERE clause of the same query level, so WHERE rn > 1 would fail; the ROW_NUMBER() must be wrapped in a subquery or CTE as in option D.

Exam trap

DA0-002 often tests SQL logical processing order, tricking candidates into selecting a query that references a window-function alias in WHERE, which is invalid because window functions are evaluated after WHERE.

Practice this question →
28
MCQmedium

A data analyst wants to randomly select 100 customers from a database for a survey, ensuring that the sample reflects the proportion of male and female customers in the population. Which sampling method is most appropriate?

A.Stratified sampling
B.Cluster sampling
C.Simple random sampling
D.Systematic sampling
AnswerA

Stratified sampling divides the population into strata (male and female) then samples proportionally within each, guaranteeing the sample mirrors the population's gender proportions. Simple random sampling could skew those proportions by chance, failing the stem's proportionality constraint.

Why this answer

Stratified sampling divides the population into homogeneous subgroups (strata) — here, male and female — and then draws random samples from each stratum in proportion to its size in the population. This guarantees the sample mirrors the population's gender proportions, which is exactly what the analyst requires. Simple random sampling could, by chance, under- or over-represent one gender.

Exam trap

The trap here is confusing stratified sampling with cluster sampling — both involve grouping, but stratification samples within every group to ensure representation, while clustering samples entire groups and ignores proportional representation.

How to eliminate wrong answers

Option B is wrong because cluster sampling divides the population into clusters (e.g., geographic regions) and randomly selects entire clusters, which does not guarantee proportional representation of gender and typically increases sampling error. Option C is wrong because simple random sampling selects individuals purely by chance without regard to strata, so the sample's gender ratio may deviate from the population's. Option D is wrong because systematic sampling picks every kth element from an ordered list; if the list has a periodic pattern related to gender, the sample can be biased, and it does not enforce proportional representation.

Practice this question →
29
MCQeasy

Which data sampling method involves selecting every k-th element from a list after a random start?

A.Systematic sampling
B.Cluster sampling
C.Stratified sampling
D.Simple random sampling
AnswerA

Systematic sampling selects every k-th element after a random starting point, directly matching the stem's definition. The random start prevents bias from periodic patterns in the list, while the fixed interval k ensures even coverage across the dataset. This satisfies the constraint of deterministic interval selection combined with randomised initiation.

Why this answer

Systematic sampling selects every k-th element from a list after choosing a random starting point. For example, with k=10 and a random start of 3, you pick elements 3, 13, 23, and so on. This makes it efficient for large, ordered populations while still providing a probabilistic sample.

Exam trap

DA0-002 often tests the confusion between systematic (every k-th) and stratified (proportional subgroups) sampling — candidates mix up the interval-based method with the subgroup-based method.

How to eliminate wrong answers

Option B is wrong because cluster sampling divides the population into clusters (e.g., schools, cities) and randomly selects entire clusters, then samples all or some members within them. Option C is wrong because stratified sampling divides the population into homogeneous strata (e.g., age groups) and randomly samples from each stratum. Option D is wrong because simple random sampling gives every element an equal, independent chance of selection with no fixed interval.

Practice this question →
30
MCQeasy

A data analyst is extracting data from a relational database using SQL. Which clause is essential for limiting the rows retrieved to only those needed?

A.GROUP BY
B.ORDER BY
C.WHERE
D.HAVING
AnswerC

The WHERE clause applies row-level predicates, filtering records so only those matching specified conditions are returned. It satisfies the requirement to limit retrieved rows, reducing data transfer and processing compared with retrieving the full table.

Why this answer

WHERE. The WHERE clause is used to filter rows from a table based on specified conditions, limiting the result set to only the rows that meet those conditions. Option A (GROUP BY) is incorrect because it groups rows with same values into summary rows, not for filtering.

Option B (ORDER BY) is incorrect because it sorts the result set, not filters. Option D (HAVING) is incorrect because it filters groups after aggregation, not individual rows. Therefore, WHERE is essential for limiting rows retrieved.

Practice this question →
31
MCQeasy

Which SQL function can be used to extract the year from a date column 'order_date'?

A.DATEDIFF(year, order_date)
B.DATEADD(year, order_date)
C.YEAR(order_date)
D.FORMAT(order_date, 'yyyy')
AnswerC

YEAR(order_date) returns the year portion as an integer directly from the date value, satisfying the requirement to extract only the year. Unlike DATEPART, which needs a datepart argument, YEAR is a dedicated function that isolates that single component without additional parameters.

Why this answer

The YEAR function extracts the year portion from a date.

Practice this question →
32
Multi-Selectmedium

An analyst needs to identify outliers in a numeric column 'transaction_amount' using the interquartile range (IQR) method. Which TWO steps are part of this process? (Select TWO).

Select 2 answers
A.Subtract 1.5 times the IQR from Q1 and add 1.5 times the IQR to Q3 to define bounds
B.Calculate the median of the column
C.Calculate the first quartile (Q1) and third quartile (Q3)
D.Sort the data and remove the top and bottom 5%
E.Compute the mean and standard deviation of the column
AnswersA, C

The IQR method defines outlier bounds at Q1 minus 1.5 times the IQR and Q3 plus 1.5 times the IQR. Values falling outside these fences are flagged as outliers, directly satisfying the stem's requirement to identify the bounding step.

Why this answer

The IQR method requires first calculating the first quartile (Q1) and third quartile (Q3) of the numeric column, since the IQR is defined as Q3 minus Q1 — this makes option C correct. Once Q1, Q3, and the IQR are known, the outlier bounds are established by subtracting 1.5 × IQR from Q1 (lower fence) and adding 1.5 × IQR to Q3 (upper fence), so option A is correct. Values falling outside these fences are flagged as outliers.

Option B is not required because the median is not used in the IQR fence calculation. Option D describes trimming extremes by percentile, which is a different technique and not part of the IQR method. Option E describes a z-score approach using mean and standard deviation, which is an alternative outlier-detection method, not the IQR method.

Practice this question →
33
MCQmedium

You are analyzing sales data and need to calculate the moving average of monthly sales over the previous 3 months for each month. Which type of function is best suited for this task?

A.String function
B.Window function with OVER()
C.Aggregate function with GROUP BY
D.Date function
AnswerB

A window function with OVER() computes an aggregate across a defined frame of preceding rows while retaining each row's detail. This satisfies the three-month moving average requirement, which GROUP BY cannot produce without collapsing the monthly rows.

Why this answer

A window function with OVER() computes a value across a defined set of rows related to the current row without collapsing them, which is exactly what a moving average over the previous 3 months requires. The OVER() clause defines the window (e.g., ROWS BETWEEN 2 PRECEDING AND CURRENT ROW), and the aggregate (AVG) is applied per row while preserving all rows.

Exam trap

The trap is choosing GROUP BY because 'average' sounds like an aggregate — but GROUP BY collapses rows, whereas a moving average requires per-row output, which only window functions provide.

How to eliminate wrong answers

Option A is wrong because string functions manipulate text (e.g., SUBSTRING, CONCAT) and have nothing to do with numeric rolling calculations. Option C is wrong because an aggregate function with GROUP BY collapses rows into groups, so you would lose the per-month detail and could not produce a rolling 3-month average for each month. Option D is wrong because date functions extract or manipulate date parts (e.g., EXTRACT, DATE_ADD) but do not compute moving averages.

Practice this question →
34
MCQmedium

A data analyst is performing data profiling on a customer dataset. Which metric would best reveal the number of distinct values in the 'state' column?

A.Mean
B.Row count
C.Cardinality
D.Null count
AnswerC

Cardinality counts the distinct values present in a column, so profiling the 'state' column returns how many unique states appear. This directly satisfies the stem's requirement to reveal the number of distinct values, distinguishing it from row count or null-count metrics.

Why this answer

Cardinality measures the number of distinct values in a column, so it directly reveals how many unique states appear in the 'state' column. This is a standard data profiling metric used to understand column variability and potential categorical encoding needs. Mean, row count, and null count do not provide distinct value counts.

Exam trap

The trap is that candidates might confuse cardinality with row count or null count, but cardinality specifically counts distinct values, which is the key to profiling categorical columns.

How to eliminate wrong answers

Option A is wrong because mean is a measure of central tendency for numeric data and is not applicable to categorical columns like 'state'. Option B is wrong because row count gives the total number of records, not the number of distinct values. Option D is wrong because null count only counts missing values, not the variety of non-null values.

Practice this question →
35
MCQmedium

A data team is designing an ETL process to extract data from an operational database daily. The database experiences heavy write loads during business hours. What is the best practice to minimize impact on operations?

A.Extract directly from the primary database with high priority
B.Run the extraction during peak hours to ensure data freshness
C.Schedule extraction at midnight when load is low
D.Use replication or a read replica to extract data
AnswerD

A read replica or replicated copy serves extraction queries on separate hardware, so the heavy business-hours write load on the operational database is not contended by the daily ETL reads. This satisfies the stated constraint of minimising operational impact.

Why this answer

Extracting from a read replica or replicated copy isolates the ETL workload from the primary database that serves production transactions. This prevents the heavy read queries of the ETL process from competing with business-critical writes and locks, preserving performance for users. It is the standard best practice for minimizing operational impact while still obtaining the needed data.

Exam trap

The trap here is assuming that off-peak scheduling (midnight) is always sufficient, but the question emphasizes heavy write loads during business hours and asks for best practice to minimize impact—isolating the workload via replication is the more robust answer.

How to eliminate wrong answers

Option A is wrong because extracting directly from the primary database with high priority would compete with production write loads, potentially causing contention, blocking, and performance degradation. Option B is wrong because running extraction during peak hours directly contradicts the goal of minimizing impact on operations, as it adds load when the system is already busiest. Option C is wrong because scheduling at midnight only reduces impact if the database is actually idle then; many operational databases have batch jobs, backups, or global users active at night, and it does not provide a structural isolation like a replica does.

Practice this question →
36
Multi-Selecthard

A data analyst is integrating customer data from two source systems: a CRM and a billing system. The CRM uses a customer ID format of 'CUST-12345', while the billing system uses '12345'. The analyst needs to join records on customer ID. Which two steps are necessary to prepare the data for a successful join? (Choose two.)

Select 2 answers
A.Extract the numeric portion from the CRM customer ID to match the billing system format.
B.Apply a hash function to both customer ID fields to anonymize them.
C.Convert both customer ID fields to a consistent string data type.
D.Sort both datasets by customer ID before joining.
E.Create a cross-reference table mapping CRM IDs to billing IDs.
AnswersA, C

Extracting the numeric portion from the CRM ID removes the 'CUST-' prefix, making it compatible with the billing system's numeric ID. This is essential for a direct join on the ID fields. Without this step, the join would fail because the formats differ. It standardizes the key across both sources.

Why this answer

The necessary steps are to extract the numeric portion from the CRM ID and to ensure both ID fields share a consistent data type. These actions standardize the join key, allowing records to match correctly across the two systems. Without them, the join would fail due to format and type mismatches, leading to incomplete or incorrect integration.

Exam trap

The trap here is assuming that a simple CAST or CONVERT is enough, but the format difference ('CUST-' prefix) must also be resolved before the join can succeed.

Practice this question →
37
MCQmedium

A data analyst wants to concatenate first_name and last_name columns with a space in between. Which string function combination should be used in SQL?

A.first_name + ' ' + last_name
B.SUBSTRING(first_name, 1, 1) + '.' + last_name
C.CONCAT(first_name, last_name)
D.CONCAT(first_name, ' ', last_name)
AnswerD

CONCAT joins its arguments directly, so passing first_name, a literal space, and last_name inserts exactly one space between the names without requiring a separator parameter. This satisfies the stem's requirement to concatenate both columns with a space in between, and CONCAT also handles NULL inputs more gracefully than the || operator.

Why this answer

CONCAT(first_name, ' ', last_name) is correct because CONCAT accepts multiple arguments and joins them in order; inserting a literal space string ' ' between the two column arguments produces the desired 'first last' format. This is the standard, portable SQL approach for joining columns with a delimiter.

Exam trap

The trap is assuming the + operator is universal string concatenation across all SQL dialects, or forgetting that CONCAT without a delimiter argument produces no space — the exam tests whether you read the requirement for a space between the names.

How to eliminate wrong answers

Option A is wrong because the + operator is not universal string concatenation in SQL — it works in SQL Server and some dialects but is invalid or interpreted as arithmetic in others (e.g., PostgreSQL, Oracle), and it does not reliably handle NULLs the way CONCAT does. Option B is wrong because SUBSTRING(first_name, 1, 1) extracts only the first initial of the first name and concatenates it with a period and the last name, producing 'J.Smith' rather than 'John Smith'. Option C is wrong because CONCAT(first_name, last_name) joins the two columns with no separator, yielding 'JohnSmith' with no space.

Practice this question →
38
MCQeasy

A marketing analyst is reviewing a dataset of campaign responses. The 'response' column contains values 'Yes', 'No', 'Y', 'N', 'yes', and 'no'. Before analysis, the analyst needs to count how many customers responded positively. Which data preparation step is most appropriate?

A.Convert the column to uppercase to make all values consistent.
B.Filter out rows with 'Y' and 'N' because they are abbreviations.
C.Create a new binary column where 'Yes' and 'Y' are 1, and all else 0.
D.Standardize the values to 'Yes' and 'No' using case normalization and mapping.
AnswerD

Standardizing the values ensures consistency, so that 'Y', 'yes', and 'Yes' are all recognized as positive responses. This simplifies counting and prevents undercounting due to case or abbreviation variations. It is a fundamental data cleaning step that improves data quality and analysis accuracy. Without it, aggregations would be misleading.

Why this answer

The analyst should standardize the values to 'Yes' and 'No' by normalizing case and mapping abbreviations. This ensures all positive responses are consistently represented, enabling accurate counting and analysis. Without standardization, variations like 'Y' and 'yes' would be treated as separate categories, leading to incorrect insights.

Exam trap

The trap here is assuming that converting to uppercase alone solves the problem, but it ignores abbreviations like 'Y' that still need mapping.

Practice this question →
39
MCQmedium

A data analyst is pulling data from a production database for a report. The database contains customer orders with a column 'order_date'. The analyst notices that some orders have dates in the future. Which data quality issue does this represent?

A.Invalid data type
B.Inconsistent data
C.Missing data
D.Violation of business rules
AnswerD

Future orders are not valid per business rules, indicating a data quality issue.

Why this answer

Future order dates violate a business rule that order_date must be in the past or present. This is a classic data integrity issue where the data does not conform to domain-specific constraints, such as 'order_date <= CURRENT_DATE'. The analyst should flag this as a violation of business rules, not a data type or consistency problem.

Exam trap

The trap here is that candidates confuse 'invalid data type' (Option A) with 'invalid data value' — the data is of the correct type but violates a logical business rule, which is a distinct quality issue often tested in DA0-001.

How to eliminate wrong answers

Option A is wrong because the column 'order_date' is of a valid date data type (e.g., DATE or TIMESTAMP), so there is no data type mismatch. Option B is wrong because inconsistent data refers to contradictory values across related columns (e.g., different date formats), not a single column containing future dates. Option C is wrong because missing data would involve NULL or empty values, not dates that are present but invalid according to business logic.

Practice this question →
40
MCQmedium

A retail analytics team is building a monthly dashboard that must show the running total of sales for each store from the first day of the fiscal year through the current transaction date. The sales table contains one row per transaction with columns store_id, transaction_date, and sale_amount. The analyst needs a query that returns every transaction row with an additional column showing the cumulative sales for that store up to and including that transaction. Which SQL approach should the analyst use?

A.Use a SUM(sale_amount) OVER (PARTITION BY store_id ORDER BY transaction_date) window expression.
B.Use a correlated subquery with SUM(sale_amount) and a WHERE clause that filters transaction_date to the current month only.
C.Use GROUP BY store_id with SUM(sale_amount) and a HAVING clause on transaction_date.
D.Use a self-join on store_id where the joined transaction_date is greater than or equal to the outer transaction_date, then SUM the inner sale_amount.
AnswerA

This window expression computes a running total because the ORDER BY inside OVER defines the cumulative frame from the start of the partition to the current row, and PARTITION BY store_id restarts the accumulation for each store. It preserves every transaction row while adding the cumulative value, which matches the requirement exactly.

Why this answer

A window function with PARTITION BY store_id and ORDER BY transaction_date produces a running total per store while keeping every transaction row. The other choices either collapse rows through aggregation, reverse the accumulation logic, or restrict the date range so the total is not year-to-date. The window approach is the standard, efficient way to add a cumulative column without losing detail.

Exam trap

The trap here is assuming any SUM with GROUP BY yields a running total, when grouping actually collapses rows and cannot produce a per-row cumulative value.

Practice this question →
41
MCQhard

In a table 'employee_hierarchy' with columns 'employee_id', 'manager_id', and 'employee_name', an analyst needs to generate a list of all employees under a specific manager, including multiple levels of subordinates. Which SQL construct is most appropriate for querying this hierarchical data efficiently?

A.Recursive CTE
B.Window function with PARTITION BY
C.Subquery in WHERE clause
D.Self-JOIN with WHERE clause
AnswerA

A recursive CTE satisfies the multi-level requirement by self-referencing: the anchor member selects direct subordinates where manager_id matches the given manager, then the recursive member joins employee_hierarchy back to the CTE on manager_id = employee_id, iterating until no rows return. This traverses the full hierarchy in one statement, unlike fixed self-joins.

Why this answer

Recursive CTEs are designed to handle hierarchical data by repeatedly joining a CTE to itself until all levels are included.

Practice this question →
42
MCQmedium

A healthcare organization acquires data from multiple hospitals with different patient record systems. The data includes patient IDs but no common identifier across systems. Which technique should be used to link records?

A.Merge all records without deduplication
B.Generate random unique IDs for each system
C.Manually match records for all patients
D.Probabilistic record linkage using name, DOB, and ZIP
AnswerD

Without a common identifier, deterministic joins fail, so probabilistic record linkage compares name, date of birth and ZIP to compute match weights across hospitals. This satisfies the stem's constraint of linking records lacking a shared key, tolerating minor discrepancies between systems.

Why this answer

Probabilistic record linkage using name, DOB, and ZIP is the appropriate technique when there is no common identifier. It calculates the likelihood that two records refer to the same entity based on matching fields, accounting for errors and variations. This is standard in healthcare data integration.

Exam trap

DA0-002 often tests the misconception that deterministic matching (e.g., exact ID) is always possible, ignoring the need for probabilistic methods when identifiers are absent.

How to eliminate wrong answers

Option A is wrong because merging without deduplication would create duplicate and inconsistent records, leading to data quality issues. Option B is wrong because generating random unique IDs for each system would not link records across systems; it would only create new identifiers. Option C is wrong because manually matching records for all patients is impractical and error-prone for large datasets.

Practice this question →
43
MCQeasy

Which SQL aggregate function would an analyst use to calculate the average value of a numeric column?

A.SUM
B.AVG
C.COUNT
D.MEDIAN
AnswerB

AVG computes the arithmetic mean of a numeric column, directly satisfying the requirement to calculate an average value. Unlike SUM, which totals values, or COUNT, which tallies rows, AVG divides the sum by the count of non-NULL entries, returning the precise average the analyst needs.

Why this answer

The AVG aggregate function computes the arithmetic mean of a numeric column, which is exactly what the analyst needs. It ignores NULL values by default and returns a single average value for the specified column or expression.

Exam trap

DA0-002 often tests whether candidates confuse AVG with SUM or COUNT, or assume MEDIAN is a standard SQL aggregate, leading them to select a function that returns a total or a count instead of a mean.

How to eliminate wrong answers

Option A is wrong because SUM adds all values together and returns a total, not an average. Option C is wrong because COUNT returns the number of rows or non-null values, not a numeric average. Option D is wrong because MEDIAN is not a standard SQL aggregate function in most database platforms and returns the middle value, not the mean.

Practice this question →
44
MCQhard

A research firm is acquiring data from public government databases via API. The API rate limits at 100 requests per minute. They need to download 10,000 records, but each request returns a maximum of 100 records. What is the most efficient approach to ensure complete acquisition without being blocked?

A.Use a retry logic with exponential backoff and pagination
B.Request a data dump from the government via email
C.Download one record per second
D.Send all requests simultaneously in parallel
AnswerA

Pagination splits the 10,000 records into 100 requests of 100 records each, satisfying the API's 100-records-per-request cap. Exponential backoff handles the 100-requests-per-minute rate limit by spacing retries after HTTP 429 responses, preventing blocks while ensuring every record is retrieved.

Why this answer

Pagination with retry logic using exponential backoff allows the firm to send requests in a controlled manner, respecting the rate limit and handling potential failures. Sending all requests in parallel would likely exceed the rate limit and cause blocking. Downloading one record per second is too slow.

Requesting a data dump via email is inefficient and may not be supported.

Practice this question →
45
MCQeasy

An analyst is profiling a newly acquired customer table and wants a quick summary of the central tendency and spread of the 'annual_income' column, which contains a few extreme outliers from data entry errors. Which combination of descriptive statistics is most appropriate to report the typical income while limiting the influence of those outliers?

A.Sum and count
B.Median and interquartile range
C.Mean and standard deviation
D.Mode and range
AnswerB

The median is the middle value and is resistant to extreme outliers, so it reflects the typical income even when a few records are corrupted. The interquartile range describes the spread of the middle half of the data and likewise ignores extreme tails. Together they give a robust central tendency and dispersion summary that suits skewed income data with known entry errors.

Why this answer

For skewed data with known outliers, robust statistics are preferred. The median resists extreme values and represents the middle of the distribution, while the interquartile range measures spread using the middle 50 percent of records. Mean and standard deviation, mode and range, and sum and count either amplify outlier influence or fail to describe central tendency and dispersion together.

Exam trap

The trap here is defaulting to mean and standard deviation for any numeric column, even when the scenario states that extreme outliers are present.

Practice this question →
46
MCQmedium

An analyst needs to combine two datasets from different sources that share a common key but have different levels of granularity. Dataset A has daily sales per store, Dataset B has hourly foot traffic per store. The analyst wants to analyze correlation. Which approach is appropriate?

A.Aggregate Dataset B to daily level before merging
B.Use an outer join and keep all rows
C.Disaggregate Dataset A to hourly level by dividing daily sales by hours
D.Join on store and date without aggregation
AnswerA

Correlation requires matching granularity; hourly foot traffic cannot align one-to-one with daily sales rows. Aggregating Dataset B to daily totals creates a shared daily key per store, enabling a valid join and meaningful correlation analysis across the two sources.

Why this answer

Aggregating Dataset B (hourly foot traffic) to the daily level ensures both datasets share the same granularity before merging on the common key (store and date). This allows a valid correlation analysis between daily sales and daily foot traffic without introducing artificial patterns or data duplication. Merging at mismatched granularities would violate the assumption that each row represents a comparable unit of observation.

Exam trap

CompTIA often tests the misconception that disaggregating (splitting) the coarser dataset is acceptable, but this introduces artificial data and violates the assumption of uniform distribution, whereas aggregation preserves the actual measured values.

How to eliminate wrong answers

Option B is wrong because an outer join without aggregation would produce multiple rows per store-date (one for each hour) when joined with daily sales, inflating the number of rows and creating a many-to-one relationship that distorts correlation calculations. Option C is wrong because disaggregating daily sales by simply dividing by hours (e.g., 24) assumes uniform sales distribution, which is rarely true and introduces artificial hourly values that do not reflect actual sales patterns. Option D is wrong because joining on store and date without aggregation retains hourly granularity from Dataset B, causing each daily sales row to repeat for every hour, leading to duplicate data and invalid statistical analysis.

Practice this question →
47
MCQeasy

A data analyst uses Python's pandas library to read a CSV file into a DataFrame. Which function is used to read the file?

A.pd.import_csv()
B.pd.read_excel()
C.pd.load_csv()
D.pd.read_csv()
AnswerD

`pd.read_csv()` parses delimited text into a DataFrame, handling the header row, comma delimiter and type inference automatically. It directly satisfies the stem's requirement to load a CSV file into pandas, unlike `read_excel()` or `read_sql()`, which target different source formats.

Why this answer

pandas reads delimited text files with the top-level function pd.read_csv(), which parses the CSV into a DataFrame and supports parameters like sep, header, dtype, and parse_dates. It is the canonical entry point for CSV ingestion in pandas and is imported as part of the standard `import pandas as pd` idiom. The other options either do not exist in the pandas API or target a different file format.

Exam trap

The trap is that candidates see plausible-sounding function names like import_csv or load_csv and pick them by pattern-matching, rather than recalling that pandas consistently uses the read_* naming convention for I/O.

How to eliminate wrong answers

Option A is wrong because pd.import_csv() is not a pandas function — it is a fabricated name that resembles import statements in other libraries. Option B is wrong because pd.read_excel() reads Excel workbooks (.xls/.xlsx) via openpyxl or xlrd, not CSV files. Option C is wrong because pd.load_csv() does not exist in pandas; 'load' is used in other ecosystems (e.g., numpy.load, json.load) but not for CSV in pandas.

Practice this question →
48
MCQhard

A data analyst is merging two datasets: one containing customer demographics with a 'customer_id' column, and another containing transaction records with a 'cust_id' column. The analyst needs to combine these datasets to analyze purchasing behavior by demographic. Which SQL join condition should be used?

A.ON demographics.customer_id = transactions.cust_id AND demographics.customer_id = transactions.customer_id
B.ON demographics.cust_id = transactions.customer_id
C.ON demographics.customer_id = transactions.cust_id
D.ON demographics.customer_id = transactions.customer_id
AnswerC

This condition correctly matches the 'customer_id' from the demographics table with the 'cust_id' from the transactions table. Despite different column names, they represent the same entity. Using this join condition ensures that each customer's demographic data is linked to their transactions, enabling accurate analysis of purchasing behavior by demographic.

Why this answer

The correct join condition matches the customer identifier from the demographics table with the corresponding identifier in the transactions table, despite the different column names. This allows the analyst to combine the datasets accurately. Using the wrong column names or adding non-existent conditions would result in SQL errors and prevent the merge, so it is essential to map the keys correctly.

Exam trap

The trap here is assuming that column names must match exactly for a join, when in fact they can differ as long as the data values correspond.

Practice this question →
49
MCQhard

A data engineer is profiling a dataset of customer orders and notices that the 'order_date' column contains values in multiple formats: 'YYYY-MM-DD', 'MM/DD/YYYY', and 'DD-Mon-YYYY'. The column is currently stored as a string. Which action should the engineer take to ensure consistent date handling for analysis?

A.Use a CASE statement to convert each format to a standard date type during queries.
B.Replace all delimiters with hyphens to make the strings look uniform.
C.Leave the column as string and rely on the BI tool to interpret the formats.
D.Parse each format and load the dates into a new column with a consistent DATE data type.
AnswerD

Parsing the various string formats and storing the result in a DATE column enforces consistency at the storage layer. This ensures all downstream queries and tools interpret the dates correctly without additional conversion logic. It also enables date-specific functions and comparisons. This is the most reliable way to standardize date data for analysis.

Why this answer

The engineer should parse each date format and load the values into a new column with a consistent DATE data type. This standardizes the data at the storage level, ensuring all analytical queries and tools interpret dates uniformly. It also allows the use of native date functions and avoids repeated conversion logic in every query.

Exam trap

The trap here is thinking that a query-time CASE statement is sufficient, but it leaves the data inconsistent and burdens every future query with conversion logic.

Practice this question →
50
MCQeasy

In a sales database, an analyst needs to retrieve all orders where the order amount is between $100 and $500. Which WHERE clause should be used?

A.WHERE amount IN (100, 500)
B.WHERE amount >= 100 AND amount <= 500
C.WHERE amount BETWEEN 100 AND 500
D.WHERE amount > 100 AND amount < 500
AnswerC

BETWEEN is inclusive on both bounds, returning rows where amount equals 100 or 500 as well as those between. This satisfies the stem's requirement to capture orders from $100 to $500, whereas exclusive comparisons would silently omit boundary orders worth exactly $100 or $500.

Why this answer

The BETWEEN operator is inclusive of the boundary values, so WHERE amount BETWEEN 100 AND 500 will return all orders where the amount is greater than or equal to 100 and less than or equal to 500. This is the most concise and correct way to express the range.

Exam trap

DA0-002 often tests the inclusivity of BETWEEN, and candidates may incorrectly choose strict inequalities or the IN operator. The trap is forgetting that BETWEEN includes the boundary values.

How to eliminate wrong answers

Option A is wrong because IN (100, 500) only matches exactly 100 or 500, not the range in between. Option B is technically correct but not the best answer because it is more verbose; however, in a multiple-choice setting, BETWEEN is the standard and preferred syntax for ranges. Option D is wrong because it uses strict inequalities, excluding orders with amount exactly 100 or 500, which does not meet the requirement of 'between $100 and $500' inclusive.

Practice this question →
51
MCQhard

A data architect is designing an ETL pipeline to ingest streaming data from IoT sensors. The data must be available for real-time analytics. Which acquisition method is best?

A.Real-time streaming via API
B.Poll sensors every hour
C.Manually upload sensor logs
D.Batch load daily CSV files
AnswerA

Real-time streaming via API pushes each IoT sensor reading to the pipeline as it is produced, rather than buffering batches. This satisfies the requirement that data be available for real-time analytics, unlike batch acquisition which introduces latency before availability.

Why this answer

Real-time streaming via API is the best method because IoT sensors generate continuous data that must be ingested with sub-second latency for real-time analytics. APIs (e.g., REST, WebSocket, or MQTT) enable event-driven ingestion, allowing the ETL pipeline to process each sensor reading as it arrives, which is essential for time-sensitive use cases like anomaly detection or live monitoring.

Exam trap

The trap here is that candidates may confuse 'real-time' with 'frequent batch' and choose hourly polling (Option B), not realizing that real-time analytics requires sub-second latency, not just periodic updates.

How to eliminate wrong answers

Option B is wrong because polling sensors every hour introduces latency of up to 60 minutes, which violates the real-time analytics requirement and can cause data staleness for time-critical decisions. Option C is wrong because manually uploading sensor logs is not automated, introduces human error, and cannot achieve the low-latency ingestion needed for streaming data. Option D is wrong because batch loading daily CSV files imposes a 24-hour delay, making the data unavailable for real-time analytics and contradicting the explicit requirement for immediate data availability.

Practice this question →
52
MCQhard

An organization needs to acquire data from a third-party vendor. The data will be used for regulatory reporting. Which of the following should be the primary consideration before acquiring the data?

A.Legal and compliance requirements
B.Volume of data
C.Data format
D.Cost of the data
AnswerA

Legal and compliance requirements determine whether the vendor's data may lawfully be acquired and used for regulatory reporting, covering licensing, privacy and provenance. This satisfies the stem's primary-consideration constraint because non-compliant data invalidates the report regardless of its quality.

Why this answer

When acquiring data for regulatory reporting, legal and compliance requirements must be the primary consideration because the data must adhere to specific laws (e.g., GDPR, HIPAA, SOX) and industry regulations. Failing to ensure compliance can result in legal penalties, fines, or rejection of the report by regulatory bodies. This overrides technical or cost concerns, as non-compliant data is unusable for its intended purpose.

Exam trap

The trap here is that candidates prioritize technical or cost factors (volume, format, price) over the foundational legal and compliance gate, mistakenly assuming any data can be adapted later without verifying regulatory fitness first.

How to eliminate wrong answers

Option B is wrong because the volume of data is a secondary operational concern (e.g., storage, processing bandwidth) but does not address whether the data legally satisfies regulatory mandates. Option C is wrong because data format (e.g., CSV, JSON, XML) is a technical integration detail that can be transformed later, not a primary legal or compliance gate. Option D is wrong because cost is a business negotiation factor; even free data must first meet regulatory requirements to be used for reporting.

Practice this question →
53
MCQmedium

A data analyst is building a dataset from multiple sources and needs to ensure data quality. During the data acquisition phase, which activity is most important to perform?

A.Data visualization
B.Data cleaning
C.Data profiling
D.Data modeling
AnswerC

Profiling computes null counts, distinct values, ranges and formats across each source column, exposing anomalies before data enters the warehouse. This satisfies the data quality requirement by revealing inconsistencies at acquisition time, when they are cheapest to correct.

Why this answer

Data profiling is the most important activity during the data acquisition phase because it involves examining source data to understand its structure, content, and quality issues before integration. This step identifies missing values, data types, duplicates, and inconsistencies early, preventing downstream errors in analysis. Without profiling, subsequent cleaning and modeling may be based on flawed assumptions about the data.

Exam trap

CompTIA often tests the distinction between data profiling (discovery/assessment) and data cleaning (correction), leading candidates to mistakenly choose cleaning as the first step during acquisition when profiling must come first to identify what needs cleaning.

How to eliminate wrong answers

Option A is wrong because data visualization is a presentation and exploratory analysis technique used after data is acquired and cleaned, not during acquisition. Option B is wrong because data cleaning is a corrective process that typically follows data profiling; performing cleaning without first profiling can waste effort on unknown issues or miss critical quality problems. Option D is wrong because data modeling defines relationships and structures for storage or analysis, which occurs after data is acquired and understood, not during the initial acquisition phase.

Practice this question →
54
MCQmedium

A data analyst wants to find the top 5 products by total sales amount, but only for products that have been sold more than 50 times. Which SQL query accomplishes this?

A.SELECT product_id, SUM(sales_amount) FROM sales GROUP BY product_id HAVING COUNT(*) > 50 ORDER BY SUM(sales_amount) DESC LIMIT 5
B.SELECT product_id, SUM(sales_amount) FROM sales WHERE COUNT(*) > 50 GROUP BY product_id ORDER BY SUM(sales_amount) DESC LIMIT 5
C.SELECT product_id, SUM(sales_amount) FROM sales GROUP BY product_id HAVING COUNT(*) > 50 ORDER BY SUM(sales_amount) ASC LIMIT 5
D.SELECT product_id, SUM(sales_amount) FROM sales GROUP BY product_id WHERE COUNT(*) > 50 ORDER BY SUM(sales_amount) DESC LIMIT 5
AnswerA

HAVING filters grouped rows after aggregation, so COUNT(*) > 50 removes products with 50 or fewer sales before ranking. ORDER BY SUM(sales_amount) DESC with LIMIT 5 then returns the five highest-revenue products. WHERE cannot reference aggregates, making HAVING the only clause satisfying both the sales-count threshold and the top-5 constraint.

Why this answer

HAVING filters after aggregation, then ORDER BY and LIMIT give the top 5.

Practice this question →
55
MCQmedium

A retail analytics team is acquiring point-of-sale transaction files from 40 different store locations. Each store exports its nightly file with a different column order and slightly different header names (for example, 'TxnID' vs 'Transaction_ID' vs 'TransNo'). The analyst must load all files into a single normalized staging table before transformation. Which data preparation approach best addresses this inconsistency at acquisition time?

A.Create a source-to-target mapping for each store's file layout and apply a schema-on-read extraction that renames and reorders columns into a canonical staging schema.
B.Concatenate all raw files into one text file and parse columns positionally using the order from the largest store.
C.Instruct each store to re-export its file using the corporate header standard before the nightly transfer begins.
D.Load each file as-is into separate tables and let the BI tool join them by matching column names at query time.
AnswerA

A per-source mapping preserves each store's original file while projecting it into one canonical column set, so downstream transformations see a single consistent structure. Because the variation is in column names and ordering rather than values, mapping at read time solves the problem without altering source exports or waiting for stores to standardize. It also documents lineage, which supports later auditing of how each field was sourced.

Why this answer

The core issue is structural inconsistency across many source files, so the fix belongs at the acquisition boundary: define a canonical staging schema and map each source layout into it. Schema-on-read mapping handles renamed and reordered columns without changing upstream systems or delaying ingestion, and it creates documented lineage. Approaches that defer reconciliation to queries or force upstream re-exports either spread complexity or block progress.

Exam trap

The trap here is assuming that column-name differences must be fixed in the source system, when the acquisition layer is responsible for normalizing heterogeneous layouts.

Practice this question →
56
Multi-Selecthard

Which THREE are challenges in acquiring data from external sources? (Select three.)

Select 3 answers
A.Data redundancy
B.Unauthorized access
C.Licensing restrictions
D.Rate limiting
E.Data format inconsistency
AnswersC, D, E

External datasets arrive under vendor or public licences governing permitted use, redistribution and derivative works. These contractual terms constrain whether the data can legally be ingested and stored, making licensing restrictions a genuine acquisition challenge rather than a technical processing concern.

Why this answer

Licensing restrictions (C) are a genuine external-data acquisition challenge because third-party providers impose contractual terms, usage caps, and redistribution limits that can legally block or constrain how the data is ingested and reused. Rate limiting (D) is correct because external APIs and feeds typically enforce request quotas (e.g., HTTP 429 responses, tokens per minute), forcing throttling, retries, or batching that complicate acquisition. Data format inconsistency (E) is correct because external sources deliver data in heterogeneous schemas and encodings (JSON, XML, CSV, proprietary formats, differing date/number conventions), requiring parsing and normalization before use.

Data redundancy (A) is an internal data-quality issue about duplicate records rather than an external acquisition obstacle, and unauthorized access (B) is a security concern affecting data protection, not a challenge inherent to acquiring data from outside sources.

Exam trap

DA0-002 often tests the misconception that generic data-quality issues like redundancy or unauthorized access are external-acquisition challenges, when the exam expects licensing, rate limiting, and format inconsistency.

Practice this question →
57
MCQmedium

A data analyst needs to sample 10% of customers from each of three regions (North, South, Central) to ensure proportional representation. Which sampling method should be used?

A.Systematic sampling
B.Cluster sampling
C.Stratified sampling
D.Simple random sampling
AnswerC

Stratified sampling divides the population into the three region strata, then draws a 10% sample independently within each. This guarantees proportional representation of North, South and Central, satisfying the stem's requirement that each region contributes its correct share rather than leaving representation to chance.

Why this answer

Stratified sampling involves dividing the population into homogeneous subgroups (strata) based on a characteristic (here, region) and then sampling proportionally from each stratum. This ensures that each region is represented in the sample according to its proportion in the population. Simple random sampling does not guarantee proportional representation.

Exam trap

The trap is confusing stratified sampling with cluster sampling; candidates might think that sampling from each region is cluster sampling, but cluster sampling involves selecting entire groups, not sampling within each group.

How to eliminate wrong answers

Option A is wrong because systematic sampling selects every nth element from a list, which does not ensure proportional representation across regions unless the list is ordered in a way that aligns with regions, which is not guaranteed. Option B is wrong because cluster sampling involves dividing the population into clusters and randomly selecting entire clusters, which can lead to underrepresentation of some regions. Option D is wrong because simple random sampling selects individuals randomly from the entire population, which may by chance under- or over-represent certain regions, especially with small sample sizes.

Practice this question →
58
Multi-Selecthard

A data engineer is acquiring semi-structured JSON event logs from a mobile application. Each event contains nested objects and arrays, and some fields are missing depending on event type. Before loading into a relational warehouse, the engineer must flatten and validate the data. Which TWO acquisition practices are most appropriate for this semi-structured source? (Choose two.)

Select 2 answers
A.Validate required fields, data types, and allowed enumerations during ingestion, and quarantine records that fail validation for later review.
B.Store each raw JSON document as a single text column and defer all parsing to report-level SQL using string functions.
C.Allow the ingestion job to infer types from each batch and create new columns automatically whenever an unfamiliar key appears.
D.Load only the fields that are present in every event type and discard optional fields to guarantee a uniform structure.
E.Define an explicit target schema and use schema-on-read extraction that maps nested JSON paths to flat columns, applying defaults or nulls for missing fields.
AnswersA, E

Because JSON allows missing keys and mixed types, ingestion-time validation catches malformed records before they pollute the warehouse. Quarantining failures preserves evidence for debugging and reprocessing once the upstream bug is fixed. Enforcing enumerations on event names prevents silent schema drift, and separating valid from invalid records keeps downstream transformations and aggregates trustworthy.

Why this answer

Semi-structured acquisition works best when the pipeline imposes a governed target schema and validates records at ingestion. Mapping nested paths to flat, typed columns with explicit null handling makes missing fields intentional, while validation with quarantine protects the warehouse from malformed data and schema drift. Deferring parsing to reports, discarding optional fields, or auto-inferring schemas all trade short-term convenience for long-term inconsistency and lost information.

Exam trap

The trap here is treating JSON flexibility as a reason to postpone structure, when the acquisition layer should still enforce a target schema and validate records.

Practice this question →
59
MCQmedium

A dataset contains a 'salary' column. The analyst wants to identify outliers using the IQR method. If Q1 = 40,000 and Q3 = 70,000, what is the upper threshold for a non-outlier?

A.130,000
B.85,000
C.115,000
D.100,000
AnswerC

The IQR is 30,000 (70,000 − 40,000), and the upper fence is Q3 + 1.5 × IQR = 70,000 + 45,000 = 115,000. This satisfies the stem's request for the upper non-outlier threshold, so any salary above 115,000 is flagged as an outlier.

Why this answer

The IQR method defines the upper fence as Q3 + 1.5 x IQR. Here IQR = 70,000 - 40,000 = 30,000, so 1.5 x IQR = 45,000 and the upper threshold is 70,000 + 45,000 = 115,000. Any salary above 115,000 is considered an outlier.

Exam trap

The trap is using the wrong multiplier or adding IQR to Q1 instead of Q3; candidates frequently compute Q3 + IQR or Q3 + 2 x IQR and land on 100,000 or 130,000.

How to eliminate wrong answers

Option A is wrong because 130,000 would result from adding 2 x IQR (60,000) to Q3, which is not the standard 1.5 multiplier. Option B is wrong because 85,000 is only 15,000 above Q3, which corresponds to 0.5 x IQR, far too strict and not the conventional threshold. Option D is wrong because 100,000 equals Q3 + IQR (30,000), which is the 1.0 multiplier, not the standard 1.5 used in Tukey's fences.

Practice this question →
60
MCQeasy

A data analyst needs to retrieve all unique job titles from the employees table. Which SQL clause should be used with the SELECT statement?

A.TOP
B.UNIQUE
C.DISTINCT
D.ORDER BY
AnswerC

DISTINCT removes duplicate rows from the result set, so each job title appears once. The stem explicitly requires all unique job titles, and DISTINCT operates on the selected column values, satisfying that uniqueness constraint directly. GROUP BY would also deduplicate but returns grouped aggregates rather than a simple unique list.

Why this answer

The DISTINCT keyword is used to return only distinct (different) values.

Practice this question →
61
MCQeasy

A data analyst wants to identify customers whose last name starts with 'Mc' from the 'customers' table. Which WHERE clause condition should be used?

A.last_name LIKE 'Mc_'
B.last_name LIKE 'Mc%'
C.last_name IN ('Mc%')
D.last_name = 'Mc%'
AnswerB

The LIKE operator with the wildcard pattern 'Mc%' matches any last name beginning with the literal characters 'Mc', satisfying the prefix constraint. The percent sign represents any sequence of zero or more characters, so surnames such as 'McDonald' or 'McIntyre' are returned while others are excluded.

Why this answer

The LIKE operator with the '%' wildcard matches any sequence of zero or more characters, so 'Mc%' correctly finds all last names beginning with 'Mc' (e.g., 'McDonald', 'McIntyre', 'Mc'). The underscore '_' matches exactly one character, and the equals operator requires an exact literal match, so neither can express a prefix pattern.

Exam trap

The trap here is confusing the two LIKE wildcards — candidates often pick '_' thinking it means 'any characters' when it actually matches exactly one character, while '%' is the multi-character wildcard.

How to eliminate wrong answers

Option A is wrong because the underscore wildcard '_' matches exactly one character, so 'Mc_' would only match three-character names like 'McX' and miss 'McDonald'. Option C is wrong because IN is a set-membership operator that compares against literal values, not patterns — it would look for the literal string 'Mc%' and never perform wildcard matching. Option D is wrong because '=' performs an exact string comparison, so it would only match a last name literally equal to 'Mc%' and not any name starting with 'Mc'.

Practice this question →
62
Multi-Selecthard

A data analyst is investigating a correlation between two continuous variables. Which THREE of the following are appropriate steps in this exploratory data analysis? (Select THREE.)

Select 3 answers
A.Calculate the Pearson correlation coefficient
B.Create a scatter plot
C.Perform a t-test
D.Check for outliers using box plots
E.Create a contingency table
AnswersA, B, D

Calculating the Pearson correlation coefficient quantifies the strength and direction of a linear relationship between two continuous variables, directly satisfying the stem's correlation investigation. It assumes interval or ratio data, linearity, and approximately normal distributions, making it the standard parametric measure for this exploratory step.

Why this answer

Option A is correct because the Pearson correlation coefficient (r) is the standard statistic for quantifying the strength and direction of a linear relationship between two continuous variables, which is exactly the analyst's goal. Option B is correct because a scatter plot visually reveals the form, direction, and strength of the relationship between the two continuous variables and can expose non-linearity that a single correlation value would hide. Option D is correct because box plots (or their underlying IQR-based rules) identify outliers that can disproportionately distort the Pearson correlation coefficient, so checking for them is a necessary data-quality step before trusting r.

Option C is not appropriate here because a t-test compares means between groups (or against a hypothesized mean), not the association between two continuous variables. Option E is not appropriate because a contingency table summarizes counts of categorical variables, whereas both variables in this scenario are continuous.

Exam trap

DA0-002 often tests the distinction between correlation analysis (continuous variables, Pearson/scatter/outliers) and group comparison or categorical analysis (t-test, contingency table) — candidates who pick the t-test confuse 'comparing' with 'correlating'.

Practice this question →
63
MCQhard

A data analyst is merging two datasets: one containing employee details (employee_id, name, department) and another containing salary information (employee_id, salary). The employee_id in the first dataset is stored as an integer, while in the second dataset it is stored as a string with leading zeros (e.g., '00123'). The analyst attempts to join the tables on employee_id but gets no matches. What is the most likely cause of the join failure?

A.The employee_id data types are different, causing implicit conversion issues.
B.The employee_id column has NULL values in both tables.
C.The join condition is missing a necessary filter on department.
D.The employee_id column contains duplicate values in one of the tables.
AnswerA

When joining on columns with different data types, the database may perform implicit conversion, but it can lead to unexpected results or errors. Here, integer vs. string with leading zeros means '123' does not equal '00123'. This mismatch causes the join to fail. Explicitly converting both to the same type and format is necessary for a successful join.

Why this answer

The join fails because the employee_id values are stored differently: one as integer, the other as string with leading zeros. Even if the numeric values are the same, the string representation differs, so equality comparison fails. Converting both to a consistent type and format resolves the issue.

Exam trap

The trap here is overlooking data type and format differences, assuming that '123' and '00123' are equivalent when they are not in a join condition.

Practice this question →
64
MCQhard

In a table 'sales_team' with columns 'salesperson', 'quarter', and 'revenue', an analyst wants to assign a rank to each salesperson within their quarter based on revenue, with the highest revenue getting rank 1. However, if two salespeople have the same revenue, they should receive the same rank, and the next rank should be the next consecutive integer (no gaps). Which window function should be used?

A.RANK()
B.NTILE(4)
C.DENSE_RANK()
D.ROW_NUMBER()
AnswerC

DENSE_RANK() assigns identical ranks to tied revenues and continues with the next consecutive integer, producing no gaps. RANK() would skip numbers after a tie, and ROW_NUMBER() would break ties arbitrarily, so neither meets the stated requirement.

Why this answer

DENSE_RANK() assigns ranks without gaps, so if two salespeople tie for rank 1, the next rank is 2. This matches the requirement that ties receive the same rank and the next rank is consecutive. It is the correct window function for this scenario.

Exam trap

DA0-002 often tests the distinction between RANK, DENSE_RANK, and ROW_NUMBER; candidates may choose RANK() when no gaps are required, forgetting that RANK() leaves gaps.

How to eliminate wrong answers

Option A is wrong because RANK() leaves gaps after ties (e.g., 1,1,3). Option B is wrong because NTILE(4) divides rows into four buckets, not ranking within quarters. Option D is wrong because ROW_NUMBER() assigns unique sequential numbers, ignoring ties.

Practice this question →
65
MCQhard

A financial institution needs to acquire credit transaction data from multiple sources while ensuring compliance with data privacy regulations. What is the most critical step?

A.Data replication for redundancy
B.Data enrichment with external sources
C.Data compression for storage
D.Data anonymization during extraction
AnswerD

Anonymising data during extraction prevents personal identifiers from entering downstream storage or processing, satisfying privacy regulation requirements at the earliest point. Applying it at extraction rather than later limits exposure and supports purpose limitation and data minimisation obligations.

Why this answer

Data anonymization during extraction is the most critical step because it ensures that personally identifiable information (PII) is irreversibly masked or removed before the data enters the processing pipeline, directly addressing compliance with regulations such as GDPR and PCI DSS. Without this step, even if other measures are applied later, the initial exposure of sensitive data violates privacy mandates and increases breach risk.

Exam trap

The trap here is that candidates confuse operational efficiency measures (replication, compression) or data enhancement (enrichment) with privacy compliance, overlooking that anonymization must be applied at the earliest point of data acquisition to satisfy regulatory requirements.

How to eliminate wrong answers

Option A is wrong because data replication for redundancy focuses on high availability and disaster recovery, not on privacy compliance; it does not prevent exposure of sensitive credit transaction data. Option B is wrong because data enrichment with external sources typically adds more data attributes, which can increase privacy risk and regulatory exposure rather than ensuring compliance. Option C is wrong because data compression for storage reduces storage footprint and may improve I/O performance but has no effect on data privacy or regulatory compliance.

Practice this question →
66
MCQeasy

A company receives daily sales data in CSV format. The data includes a 'Date' column in MM/DD/YYYY format. To load this into a database that expects YYYY-MM-DD, the analyst should:

A.Manually edit the CSV files before loading
B.Change the database schema to accept MM/DD/YYYY
C.Ignore the date column and use a default date
D.Use a data transformation tool to convert the date format during ETL
AnswerD

Transforming the date format during ETL converts MM/DD/YYYY strings into the YYYY-MM-DD structure the target database requires, satisfying the format-mismatch constraint. Handling this in the transformation layer preserves source data integrity and loads correctly typed values.

Why this answer

The correct approach is to use a data transformation tool to convert the date format during ETL. This ensures the data is standardized to the database's expected YYYY-MM-DD format without manual intervention, preserving data integrity and enabling automated, repeatable loads. Transformation tools can parse MM/DD/YYYY and reformat it consistently, which is a core ETL function.

Exam trap

DA0-002 often tests the misconception that manual editing or schema changes are acceptable solutions for data format mismatches, when in fact automated transformation during ETL is the standard best practice.

How to eliminate wrong answers

Option A is wrong because manually editing CSV files is error-prone, not scalable, and defeats the purpose of automated ETL. Option B is wrong because changing the database schema to accept MM/DD/YYYY would require altering the database design and could break other applications or queries that expect the standard format. Option C is wrong because ignoring the date column and using a default date would result in loss of critical sales data and inaccurate analysis.

Practice this question →
67
Multi-Selectmedium

A data analyst is evaluating data quality issues during acquisition. Which TWO issues are most likely to arise from merging data from different sources? (Select exactly 2)

Select 2 answers
A.User access permissions
B.Duplicate records
C.Slow network speed
D.High storage cost
E.Formatting inconsistencies
AnswersB, E

Merging sources that share overlapping entities, such as the same customer appearing in two systems, produces duplicate records unless deduplication keys are defined. This directly satisfies the stem's constraint of combining data from different sources, where repeated identifiers create redundancy.

Why this answer

Option B (Duplicate records) is correct because merging data from multiple sources commonly produces the same entity appearing in more than one source, and without deduplication via matching keys or fuzzy matching, redundant rows inflate counts and skew analysis. Option E (Formatting inconsistencies) is correct because different sources often represent the same field differently — for example dates as MM/DD/YYYY versus ISO 8601 YYYY-MM-DD, or units in metric versus imperial — requiring normalization before the data can be combined reliably. Option A (User access permissions) is an authorization/security concern rather than a data quality issue arising from the merge itself.

Option C (Slow network speed) is a performance/infrastructure factor that affects transfer time, not the quality of the merged data. Option D (High storage cost) is a cost/resource consideration, not a data quality defect introduced by combining sources.

Exam trap

DA0-002 often tests the difference between data-quality issues (duplicates, formatting) and non-quality concerns (permissions, cost, network), causing candidates to select operational or security issues as data-quality problems.

Practice this question →
68
MCQhard

A data analyst is using pandas to clean a DataFrame. They need to replace missing values in the 'age' column with the median age. Which method should they use?

A.df['age'].replace(np.nan, df['age'].mean())
B.df['age'].dropna()
C.df['age'].fillna(df['age'].median())
D.df['age'].interpolate()
AnswerC

fillna substitutes missing entries, and passing the column's median supplies a single imputation value computed across non-null ages. This directly satisfies the requirement to replace NaN values in 'age' with the median, preserving row count without dropping records.

Why this answer

fillna() with median() fills NaN values with the median of the column.

Practice this question →
69
MCQmedium

An organization is integrating data from multiple sources into a data warehouse. They need to handle differences in data granularity (e.g., daily vs. hourly sales data). Which technique is most appropriate?

A.Data aggregation
B.Data normalization
C.Data deduplication
D.Data profiling
AnswerA

Aggregation rolls hourly sales records up to daily totals, aligning finer-grained source data with the coarser warehouse grain. This resolves the daily-versus-hourly mismatch by summarising detail, which is precisely the granularity conflict the scenario describes.

Why this answer

Data aggregation is the correct technique because it allows the organization to roll up hourly sales data to a daily granularity, ensuring consistency when integrating sources with different levels of detail. By applying aggregation functions (e.g., SUM, AVG) during the ETL process, the data warehouse can store all data at a common grain, which is essential for accurate reporting and analysis.

Exam trap

The trap here is that candidates may confuse data normalization (a schema design concept) with the need to standardize data granularity, leading them to incorrectly select normalization instead of aggregation.

How to eliminate wrong answers

Option B is wrong because data normalization is a database design technique used to reduce redundancy and dependency by organizing columns and tables, not to reconcile differences in data granularity. Option C is wrong because data deduplication focuses on identifying and removing duplicate records, which does not address the mismatch in time-based granularity between daily and hourly data. Option D is wrong because data profiling is an exploratory process to assess data quality and structure, but it does not transform or harmonize data to a common granularity level.

Practice this question →
70
MCQmedium

A data analyst wants to generate a report showing employee names and their department names, but some employees are not assigned to any department. The analyst wants to include all employees. Which JOIN type should be used?

A.INNER JOIN
B.LEFT JOIN
C.CROSS JOIN
D.RIGHT JOIN
AnswerB

A LEFT JOIN returns every row from the employees table and matches department rows where they exist, producing NULLs for unassigned staff. An INNER JOIN would drop those employees, so LEFT JOIN satisfies the requirement to include all employees.

Why this answer

LEFT JOIN includes all rows from the left table (employees) even if no match in departments.

Practice this question →
71
MCQeasy

A data analyst needs to count the number of distinct product categories in a table named 'products'. Which SQL function should be used in the SELECT clause?

A.COUNT(category)
B.DISTINCT COUNT(category)
C.COUNT(DISTINCT category)
D.COUNT(*) WHERE category IS NOT NULL
AnswerC

COUNT(DISTINCT category) returns the number of unique category values, satisfying the requirement to count distinct categories rather than all rows. Plain COUNT(category) would include duplicates and ignore nulls, so DISTINCT is essential to the stated goal.

Why this answer

The correct syntax to count unique non-null values in a column is COUNT(DISTINCT column_name). This function first eliminates duplicate values in the specified column and then counts the remaining distinct entries. In this scenario, COUNT(DISTINCT category) will return the number of unique product categories present in the 'products' table, ignoring any NULLs.

Exam trap

The trap here is confusing the correct placement of DISTINCT within the COUNT function; many candidates mistakenly write DISTINCT COUNT() or COUNT() DISTINCT, or they forget that COUNT(*) with a WHERE clause does not deduplicate.

How to eliminate wrong answers

Option A is wrong because COUNT(category) counts all non-null rows in the category column, including duplicates, so it would return the total number of products with a non-null category, not the number of distinct categories. Option B is wrong because DISTINCT COUNT(category) is not valid SQL syntax; DISTINCT is not a function and cannot be used as a prefix to COUNT in this manner. Option D is wrong because COUNT(*) WHERE category IS NOT NULL counts all rows where category is not null, but it does not eliminate duplicates, so it would give the total count of non-null categories, not the distinct count.

Practice this question →
72
MCQmedium

A data analyst wants to extract the year from a date column 'order_date' in a SQL database. Which function should be used?

A.YEAR(order_date)
B.DATEADD(year, order_date, 0)
C.DATEDIFF(year, order_date, GETDATE())
D.GETDATE()
AnswerA

The YEAR() function extracts the year component from a date or timestamp value, returning an integer. Applied to 'order_date', it isolates the year portion, satisfying the requirement to extract the year from that column in a single SQL expression.

Why this answer

The YEAR() function is a standard SQL date function that extracts the year from a date or datetime expression. It takes a single argument, the date column, and returns an integer representing the year. This is the most direct and appropriate function for the task.

Exam trap

DA0-002 often tests the confusion between functions that extract date parts and those that manipulate or compare dates, leading candidates to pick DATEADD or DATEDIFF.

How to eliminate wrong answers

Option B is wrong because DATEADD is used to add or subtract a specified time interval from a date, not to extract a component. Option C is wrong because DATEDIFF calculates the difference between two dates in a specified unit, not extract a part of a date. Option D is wrong because GETDATE() returns the current system date and time, not a component of a given column.

Practice this question →
73
MCQeasy

Refer to the exhibit. What data quality issue is indicated?

A.Data inconsistency
B.Non-standardized data entry
C.Outlier
D.Data duplication
AnswerB

The exhibit shows the same values recorded in inconsistent formats and spellings, indicating non-standardised data entry. This inconsistency stems from missing input validation or controlled vocabularies at the point of capture rather than duplication or completeness problems.

Why this answer

Option B is correct because the exhibit shows the same logical value entered in multiple inconsistent formats (e.g., 'NY', 'New York', 'new york', 'N.Y.'), which is the hallmark of non-standardized data entry. The data is not duplicated (different spellings), not an outlier (no extreme numeric value), and not a referential inconsistency (no conflicting values across tables) — it is a formatting/standardization problem at the point of capture.

Exam trap

The trap here is confusing non-standardized data entry (formatting variants of the same value) with data duplication (identical repeated records) — candidates often pick duplication because both involve 'the same thing appearing multiple times'.

How to eliminate wrong answers

Option A is wrong because data inconsistency refers to conflicting values for the same attribute across systems or records (e.g., a customer's birthdate differing between CRM and billing), not multiple spellings of the same value. Option C is wrong because an outlier is a data point that lies far outside the expected distribution (e.g., an age of 250), which is a statistical anomaly, not a formatting issue. Option D is wrong because data duplication means the same record appears more than once with identical or near-identical values, whereas here the values differ in format, indicating entry standardization failure rather than duplication.

Practice this question →
74
MCQeasy

In a dataset of customer orders, you need to count the number of distinct customers who have placed orders. Which SQL aggregate function should you use?

A.DISTINCT COUNT(customer_id)
B.COUNT(customer_id)
C.COUNT(DISTINCT customer_id)
D.COUNT(*)
AnswerC

COUNT(DISTINCT customer_id) eliminates duplicate customer identifiers before tallying, returning the number of unique customers rather than total order rows. This directly satisfies the requirement to count distinct customers, whereas plain COUNT(customer_id) would include repeat purchasers multiple times and overstate the customer base.

Why this answer

The correct syntax to count unique non-null values in a column is COUNT(DISTINCT column_name). In this case, COUNT(DISTINCT customer_id) returns the number of different customers who have placed at least one order. This is the standard SQL aggregate function designed for exactly this purpose, and it ignores NULLs in the customer_id column.

Exam trap

The trap here is confusing the syntax of DISTINCT with COUNT, leading candidates to pick 'DISTINCT COUNT(customer_id)' instead of the correct 'COUNT(DISTINCT customer_id)'. Many also mistakenly think COUNT(customer_id) automatically deduplicates, but it does not.

How to eliminate wrong answers

Option A is wrong because DISTINCT COUNT(customer_id) is not valid SQL syntax; DISTINCT is a keyword used inside the COUNT function, not a standalone function. Option B is wrong because COUNT(customer_id) counts all non-null customer_id values, including duplicates, so it returns the total number of orders (with a customer_id) rather than the number of distinct customers. Option D is wrong because COUNT(*) counts all rows in the result set, including rows with NULL customer_id, and does not deduplicate, so it gives the total number of orders, not distinct customers.

Practice this question →
75
MCQhard

A data analyst is working with a sales table that contains columns: sale_id, product_id, sale_date, and amount. They need to calculate a 7-day moving average of sales amount for each product, ordered by sale_date. Which window function syntax should they use?

A.AVG(amount) OVER (PARTITION BY product_id ORDER BY sale_date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW)
B.AVG(amount) OVER (PARTITION BY product_id ORDER BY sale_date)
C.AVG(amount) OVER (ORDER BY sale_date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW)
D.SUM(amount) OVER (PARTITION BY product_id ORDER BY sale_date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW)
AnswerA

ROWS BETWEEN 6 PRECEDING AND CURRENT ROW defines a seven-row frame ending at the current row, giving a 7-day moving average. PARTITION BY product_id restarts the window per product, and ORDER BY sale_date ensures chronological ordering.

Why this answer

The correct syntax uses AVG with OVER, partitioning by product_id to calculate per product, ordering by sale_date, and specifying ROWS BETWEEN 6 PRECEDING AND CURRENT ROW to include the current row and the previous six rows, yielding a 7-day moving average.

Exam trap

The trap is forgetting to partition by product_id or using the default frame, which results in a cumulative average instead of a moving average, or confusing SUM with AVG.

How to eliminate wrong answers

Option B is wrong because without a frame clause, the default frame is RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW, which computes a cumulative average, not a moving average. Option C is wrong because it lacks PARTITION BY product_id, so it would compute a moving average across all products combined, not per product. Option D is wrong because it uses SUM instead of AVG, which would give a rolling sum, not an average.

Practice this question →
Page 1 of 3 · 208 questions totalNext →

Ready to test yourself?

Try a timed practice session using only Data Acquisition and Preparation questions.

Start 20-question session