CCNA Data Acquisition and Preparation Questions — Page 1 of 3

MCQhard

An analyst is using Python pandas and has a DataFrame 'sales' with columns 'date', 'product', 'revenue'. They need to create a pivot table showing total revenue per product per month. Which pandas function is most appropriate?

A.sales.groupby(['product', 'month']).sum()

B.sales.pivot(index='product', columns='month', values='revenue')

C.sales.pivot_table(index='product', columns='month', values='revenue', aggfunc='sum')

D.sales.melt(id_vars=['product'], value_vars=['month', 'revenue'])

AnswerC

pivot_table creates a matrix with products as rows and months as columns.

Why this answer

pivot_table is specifically designed to reshape data and aggregate values based on index and columns.

Practice this question →

Multi-Selecthard

A data analyst needs to identify the top 3 most frequent product categories from a sales table. Which SQL techniques can be used to achieve this? (Choose two.)

Select 2 answers

A.SELECT category, COUNT(*) as cnt FROM sales GROUP BY category QUALIFY DENSE_RANK() OVER (ORDER BY cnt DESC) <= 3

B.GROUP BY category ORDER BY COUNT(*) DESC LIMIT 3

C.SELECT category FROM sales LEFT JOIN (SELECT category FROM sales GROUP BY category HAVING COUNT(*) > 3) AS t ON sales.category = t.category

D.SELECT DISTINCT category FROM sales ORDER BY category DESC LIMIT 3

E.SELECT category, COUNT(*) FROM sales GROUP BY category HAVING COUNT(*) > 3

AnswersA, B

Using window function DENSE_RANK() with QUALIFY (or in a subquery) returns top 3 categories including ties.

Why this answer

Both GROUP BY with ORDER BY and LIMIT, and using a window function like DENSE_RANK() to rank categories by count and filter top 3, are valid. HAVING is for filtering groups after aggregation, but without ORDER BY and LIMIT, it doesn't give top 3. A subquery with COUNT(*)>3 would get categories with count >3, not top 3.

LEFT JOIN is irrelevant.

Practice this question →

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

Correct. HAVING filters aggregated results.

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 →

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

Profiling provides the necessary insights to plan transformations, handle inconsistencies, and design the matching strategy.

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 →

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

Standard IQR outlier definition.

Why this answer

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

Practice this question →

Multi-Selectmedium

A data analyst wants to sample a large dataset of customer transactions. Which TWO sampling methods are probability-based and ensure every element has a known chance of being selected? (Select TWO.)

Select 2 answers

A.Simple random sampling

B.Stratified sampling

C.Convenience sampling

D.Systematic sampling

E.Cluster sampling

AnswersA, B

Every element has an equal chance.

Why this answer

Simple random sampling and stratified sampling are probability-based methods where each element has a known probability. Systematic sampling is also probability-based but the question asks for TWO; convenience sampling is non-probability, and cluster sampling is probability-based but the question likely expects the two most common.

Practice this question →

MCQmedium

An analyst needs to retrieve the year from an order_date column (datetime type). Which function should be used in SQL?

A.FORMAT(order_date, 'yyyy')

B.DATEADD(YEAR, order_date, 0)

C.YEAR(order_date)

D.EXTRACT(YEAR FROM order_date)

AnswerD

EXTRACT is the standard SQL function for date parts.

Why this answer

EXTRACT(YEAR FROM order_date) is the standard SQL function to get the year part.

Practice this question →

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

LEFT JOIN includes all customers, with order data where available.

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 →

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

HAVING COUNT(*) > 5 ensures only customers with more than 5 orders are included.

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 →

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 provides structured, real-time access to third-party data, which is ideal for social media engagement data.

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 →

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

Legal and ethical best practice.

Why this answer

Options B, C, and E are correct because best practices for web scraping include respecting robots.txt (ethical and legal compliance), identifying yourself with a user-agent (transparency), and limiting request rate (avoid overloading servers). Option A is incorrect because using multiple IP addresses is often used to circumvent blocking, which can violate terms of service and is not a recommended best practice. Option D is incorrect because scraping all data without regard to terms is unethical and potentially illegal.

Practice this question →

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

Returns distinct count.

Why this answer

COUNT(DISTINCT column) returns the number of unique non-null values.

Practice this question →

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

Correct. DENSE_RANK() gives same rank to ties and no gaps.

Why this answer

DENSE_RANK() assigns the same rank to ties and does not skip subsequent ranks. RANK() also assigns same rank to ties but skips numbers, creating gaps.

Practice this question →

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

Web scraping can collect competitor pricing from public websites.

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 →

Multi-Selectmedium

An analyst needs to aggregate sales data by region and product, then sort the results by total sales in descending order. Which SQL clauses are required? (Select THREE).

Select 3 answers

A.GROUP BY

B.HAVING

C.SUM

D.ORDER BY

E.DESC

AnswersA, D, E

Required to define groups for aggregation.

Why this answer

GROUP BY is needed to aggregate, ORDER BY to sort, and DESC for descending order. SUM is an aggregate function but not a clause.

Practice this question →

MCQhard

A financial analytics firm needs to acquire historical stock market tick data (millions of records per day) from a data vendor. The vendor provides data via FTP in binary format. The firm's existing infrastructure uses on-premise servers with limited storage and processing power. They need to stream the data into a cloud data lake for analysis. However, the binary format is proprietary and requires a licensed decoder. The budget is constrained. Which approach best meets the data acquisition requirements?

A.Negotiate with the vendor to provide an API that outputs JSON

B.Purchase a license for the decoder and set up an ETL job to convert and upload

C.Request the vendor to send data in CSV format via email

D.Use a third-party cloud service that already decodes and normalizes the data for a subscription fee

AnswerB

This allows processing of the binary format and integration with the cloud data lake, with a one-time cost.

Why this answer

Purchasing the decoder license and setting up an ETL job to convert and upload to the cloud directly addresses the format issue and enables streaming to the data lake. Requesting CSV via email is impractical for millions of records. Negotiating for a JSON API is a good idea but may not be available or cost more than the decoder.

Using a third-party service adds recurring costs and may introduce dependency.

Practice this question →

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

APIs provide structured access to external data.

Why this answer

Public APIs (C) are a common method for acquiring data from external sources because they provide a standardized, programmatic interface (often RESTful over HTTP/HTTPS) for requesting and receiving structured data, such as JSON or XML, from third-party services like social media platforms or weather services. Web scraping (D) is another common method that involves programmatically extracting data from web pages by parsing HTML or DOM structures, often using tools like BeautifulSoup or Selenium, when no API is available.

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 →

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 groups after aggregation.

Why this answer

HAVING is used to filter groups based on aggregate conditions, unlike WHERE which filters before aggregation.

Practice this question →

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

Standard IDs prevent mismatches.

Why this answer

Options A (standardize product IDs), B (convert monetary amounts to a common currency), and D (synchronize timestamps to a single time zone) are essential for data consistency when merging online and in-store transaction data. Option C (remove all transactions with missing customer ID) is not required and may discard useful data; missing customer IDs can be handled in other ways. Option E (merge using only store location) is insufficient as it ignores other identifiers and may cause mismatches.

Practice this question →

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 is a standard, simple method for file transfer from legacy systems.

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 →

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

Buffering ensures data is not lost and is transmitted later, providing continuity despite temporary outages.

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 →

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 provides real-time changes; streaming handles JSON logs with low latency.

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 →

MCQmedium

A data analyst is using pandas in Python to clean a dataset. Which method is most appropriate to replace missing numerical values with the median of the column?

A.df.fillna(df.median())

B.df.interpolate()

C.df.dropna()

D.df.replace(np.nan, df.mean())

AnswerA

fillna with median replaces nulls with median.

Why this answer

fillna with median replaces missing values with median.

Practice this question →

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

Python provides libraries and string manipulation ideal for parsing fixed-width files.

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 →

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

Equal probability for all.

Why this answer

Simple random sampling gives each individual an equal chance of being selected.

Practice this question →

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

Profiling identifies inconsistencies and guides next steps.

Why this answer

Data profiling is the initial step to understand the structure, quality, and issues in the data. Rejecting or transforming without profiling may lead to errors, and contacting the vendor is premature without understanding the scope.

Practice this question →

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

Error handling ensures the pipeline can recover from failures.

Why this answer

Implementing error handling and logging is a critical best practice in data pipeline extraction. It ensures that failures (e.g., network timeouts, authentication errors, or schema mismatches) are captured and can be diagnosed without data loss or silent corruption, which is essential for maintaining pipeline reliability and auditability.

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 →

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

A CTE with ROW_NUMBER() can identify duplicates by filtering on rn > 1.

Why this answer

GROUP BY with COUNT and HAVING COUNT > 1 filters groups with duplicates. ROW_NUMBER() with PARTITION BY can assign row numbers to identify duplicates.

Practice this question →

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 by gender ensures proportional representation.

Why this answer

Stratified sampling ensures proportional representation of subgroups (strata).

Practice this question →

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

Correct: selects every k-th element.

Why this answer

Systematic sampling selects every k-th item after a random start.

Practice this question →

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

Filters rows based on conditions.

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 →

MCQhard

A financial institution is merging transaction data from two different systems. System A stores currency amounts as integers in cents, and System B stores as decimals in dollars. What is the best way to integrate the data?

A.Convert System A amounts to dollars by dividing by 100.

B.Keep both as is and use a transformation layer.

C.Store all amounts as strings to preserve precision.

D.Convert System B amounts to cents by multiplying by 100.

AnswerA

This standardizes all amounts to dollar decimal format.

Why this answer

Converting System A's integer cents to dollars by dividing by 100 ensures both datasets share a consistent unit (dollars) and numeric data type (decimal). This direct transformation eliminates ambiguity in aggregation and reporting, as financial calculations require uniform precision and scale. Using a transformation layer or storing as strings would introduce unnecessary complexity or risk of rounding errors.

Exam trap

The trap here is that candidates may assume keeping both formats (Option B) is simpler or that converting to cents (Option D) is safer, but they overlook the critical requirement for a single, consistent unit to enable direct arithmetic and avoid precision loss in financial data integration.

How to eliminate wrong answers

Option B is wrong because keeping both formats as-is forces every downstream query or application to repeatedly apply conversion logic, increasing complexity, maintenance overhead, and the risk of inconsistent results. Option C is wrong because storing currency amounts as strings prevents arithmetic operations (e.g., SUM, AVG) without explicit casting, degrades query performance, and can lead to sorting or comparison errors due to lexical ordering. Option D is wrong because converting System B's decimal dollars to cents by multiplying by 100 would lose fractional cent precision (e.g., $1.234 becomes 123 cents, truncating 0.4 cents), which is unacceptable for financial data integrity.

Practice this question →

MCQhard

A social media monitoring company collects public tweets using the Twitter API. The API has a tiered access: free tier allows 500,000 tweets per month, and paid tier allows 2 million tweets per month. The company needs to collect 1.5 million tweets per month for analysis. They are on a free tier but have been exceeding the limit, causing account suspension. They need a sustainable solution without significantly increasing costs. What should they do?

A.Request an academic research exemption

B.Reduce the collection to exactly 500,000 tweets per month by sampling

C.Use multiple developer accounts to stay within free limits

D.Upgrade to the paid tier

AnswerC

Multiple accounts can split the load, staying within free limits and avoiding costs.

Why this answer

Using multiple developer accounts to distribute the collection load can allow access to more tweets while staying within each account's free limit. This avoids the cost of upgrading to a paid tier. Reducing collection to 500,000 tweets would cause loss of critical data.

Requesting an academic exemption is unlikely because the company is commercial. Upgrading to paid tier increases costs significantly.

Practice this question →

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

Correct: YEAR returns the year as an integer.

Why this answer

The YEAR function extracts the year portion from a date.

Practice this question →

Multi-Selectmedium

A data analyst is performing data profiling on a customer table. Which TWO metrics are most useful for understanding the completeness of the data? (Choose two.)

Select 2 answers

A.Minimum and maximum values

B.Null count per column

C.Row count

D.Cardinality

E.Mean value

AnswersB, C

Directly measures missing values.

Why this answer

Row count gives total records, null count gives missing values per column, both help assess completeness. Cardinality is for uniqueness, min/max for range, mean for central tendency.

Practice this question →

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

These bounds are used to flag outliers.

Why this answer

The IQR method involves calculating Q1 and Q3 to find IQR, then defining lower and upper bounds as Q1 - 1.5*IQR and Q3 + 1.5*IQR. Computing mean and standard deviation is for Z-score method; calculating median alone is insufficient.

Practice this question →

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

Window functions operate on a set of rows related to the current row, perfect for moving averages.

Why this answer

Window functions, specifically using OVER() with ORDER BY and a frame specification, can compute moving averages. Aggregate functions alone cannot access previous rows without a self-join. String and date functions are irrelevant.

Practice this question →

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 is the count of distinct values.

Why this answer

Cardinality refers to the number of unique values in a column, which directly indicates distinct states.

Practice this question →

MCQeasy

A data analyst wants to create a temporary result set that can be referenced within a single SQL statement. Which feature should be used?

A.Subquery

B.CTE

C.Temporary table

D.Derived table

AnswerB

CTE with WITH clause is the correct feature.

Why this answer

CTE (Common Table Expression) defined with WITH clause creates a temporary named result set usable within the query.

Practice this question →

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

Read replicas are designed for such purposes and do not affect the primary.

Why this answer

(use replication or a read replica) is best because it offloads the extraction from the primary database, minimizing impact on operational write loads. Option A extracts directly from the primary, impacting performance. Option B runs extraction during peak hours, which increases load and negatively affects operations.

Option C still extracts from the primary even at midnight, though less busy, it still adds load to the primary.

Practice this question →

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

This adds a space between the two names.

Why this answer

CONCAT joins strings; adding a space produces 'First Last'.

Practice this question →

Multi-Selectmedium

A data analyst is performing data profiling on a customer table. Which TWO of the following are key metrics to assess data quality? (Select TWO.)

Select 2 answers

A.Row count

B.Minimum and maximum values

C.Cardinality

D.Data type verification

E.Null count

AnswersB, E

Min and max help identify out-of-range values or anomalies.

Why this answer

Null counts indicate missing values, and min/max values can reveal outliers or unexpected ranges. Row count alone doesn't assess quality; cardinality and data type verification are also important but the question asks for key metrics among the options.

Practice this question →

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 →

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

Recursive CTEs iterate through levels, ideal for hierarchies.

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 →

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

Probabilistic record linkage is designed to link records using non-unique identifiers like name, DOB, and ZIP, making it suitable for this scenario.

Why this answer

(probabilistic linkage) is designed for such situations. Option A (merge without deduplication) creates duplicates and loses connections. Option B (random unique IDs) loses connections because there is no common identifier across systems.

Option C (manual matching) is not scalable.

Practice this question →

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

Why this answer

AVG calculates the arithmetic mean of a numeric column.

Practice this question →

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

This approach respects the rate limit, handles failures gracefully, and ensures complete data acquisition.

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 →

MCQhard

A healthcare organization is building a data warehouse to support population health analytics. The data sources include: (1) an electronic health record (EHR) system with a relational database containing patient demographics, diagnoses, and medications; (2) a claims system that generates CSV files daily; (3) patient-generated health data from mobile apps via a REST API returning JSON. The data engineer needs to design a data acquisition process that runs nightly. The EHR system has a change tracking mechanism that logs changes with timestamps. The claims CSV files are appended daily. The API supports filtering by date. The data warehouse uses a star schema with fact and dimension tables. The engineer must ensure data consistency and minimize load times. Which approach should the engineer take?

A.Perform a full extraction of all data from all sources every night and load directly into the data warehouse

B.Extract only new and changed EHR data using change tracking, extract the full claims CSV (since it's append-only), and extract API data filtered by the last extraction date

C.Use a staging area to land all raw data first, then transform and load

D.Extract the EHR data using change tracking, extract the full claims CSV, and extract the API data using a full dump

AnswerB

This minimizes data transfer and load time while capturing all changes.

Why this answer

It uses incremental extraction for the EHR system (via change tracking) and the API (via date filtering), while performing a full extraction of the claims CSV since it is append-only and small enough to reload nightly. This minimizes load times by avoiding full re-extraction of large, slowly changing datasets, and ensures data consistency by capturing only new or modified records. The star schema in the data warehouse is then populated efficiently from these targeted extracts.

Exam trap

The trap here is that candidates may assume a staging area (Option C) is always required for data consistency, but the question specifically asks for the acquisition approach to minimize load times, and incremental extraction (Option B) directly achieves that without mandating a staging area.

How to eliminate wrong answers

Option A is wrong because performing a full extraction of all data every night would be extremely inefficient, causing unnecessarily long load times and high resource consumption, especially for large relational databases like the EHR system. Option C is wrong because while using a staging area is a best practice for data quality and transformation, it does not address the core requirement of minimizing load times through incremental extraction; the question specifically asks for the acquisition approach, not the ETL pipeline design. Option D is wrong because extracting a full dump of the API data every night ignores the API's built-in date filtering capability, leading to redundant data transfer and longer load times compared to incremental extraction.

Practice this question →

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

Aggregating the more granular dataset to match the less granular is the standard approach.

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 →

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

This is the correct function to read CSV files into a DataFrame.

Why this answer

pd.read_csv() is the standard pandas function to read a CSV file.

Practice this question →

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 filters values within the inclusive range.

Why this answer

The BETWEEN operator is inclusive and is the standard way to filter a range of values.

Practice this question →

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

Streaming provides continuous, low-latency data flow.

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 →

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

Regulatory reporting requires adherence to data governance and privacy laws.

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 →

MCQmedium

A data analyst wants to retrieve the top 5 highest-paid employees from an 'employees' table, including ties. Which SQL clause should be used?

A.TOP 5 WITH TIES

B.ORDER BY salary DESC LIMIT 5

C.HAVING salary > 50000

D.WHERE ROWNUM <= 5

AnswerA

TOP 5 WITH TIES (SQL Server) includes all rows that tie for the 5th position.

Why this answer

In many SQL dialects, LIMIT can be used to restrict rows. However, to include ties, some databases offer WITH TIES with FETCH FIRST or TOP. Standard SQL: FETCH FIRST 5 ROWS WITH TIES.

TOP 5 alone does not include ties. RANK() with WHERE clause can work but is more complex.

Practice this question →

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 assesses data quality and structure before further processing.

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 →

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

Correct use of HAVING, ORDER BY, and LIMIT.

Why this answer

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

Practice this question →

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 data may have legal restrictions on usage, sharing, or redistribution.

Why this answer

Data format inconsistency occurs when integrating data from different sources. Rate limiting is a common API restriction that limits how much data can be accessed. Licensing restrictions may limit the use or redistribution of acquired data.

Data redundancy is an internal data quality issue, not a challenge specific to acquisition. Unauthorized access is a security concern but not a typical acquisition challenge.

Practice this question →

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

Ensures each region is represented proportionally.

Why this answer

Stratified sampling divides the population into strata (regions) and samples proportionally from each. Simple random sampling would not guarantee proportional representation. Systematic sampling selects every kth element.

Cluster sampling selects entire groups randomly.

Practice this question →

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

Correct calculation: 70,000 + 1.5*30,000 = 115,000.

Why this answer

Upper threshold = Q3 + 1.5 * IQR. IQR = 70,000 - 40,000 = 30,000. So upper = 70,000 + 45,000 = 115,000.

Practice this question →

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 filters out duplicate rows.

Why this answer

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

Practice this question →

MCQeasy

A table 'orders' contains columns 'order_id', 'customer_id', 'order_date', and 'total'. An analyst needs to find orders placed between January 1, 2023 and December 31, 2023. Which WHERE clause is correct?

A.WHERE order_date > '2023-01-01' AND order_date < '2023-12-31'

B.WHERE order_date IN ('2023-01-01', '2023-12-31')

C.WHERE order_date >= '2023-01-01' OR order_date <= '2023-12-31'

D.WHERE order_date BETWEEN '2023-01-01' AND '2023-12-31'

AnswerD

BETWEEN is inclusive and concise.

Why this answer

The BETWEEN operator is inclusive and is the standard way to filter date ranges.

Practice this question →

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

Correct: % matches any sequence of characters after 'Mc'.

Why this answer

The LIKE operator with '%' wildcard matches any sequence of characters after 'Mc'.

Practice this question →

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

Quantifies linear correlation.

Why this answer

Scatter plot visualizes relationship, correlation coefficient quantifies strength, and removing outliers may be needed to avoid misleading results.

Practice this question →

MCQhard

During a data mining project, an analyst discovers that a significant number of records have a negative value for the age field. What is the most appropriate first step?

A.Impute using regression.

B.Replace negative age with the mean age.

C.Investigate the source system for data entry errors.

D.Remove all records with negative age.

AnswerC

Determining why negative ages occur enables targeted correction and prevents future errors.

Why this answer

The first step in handling anomalous data like negative ages is to investigate the source system for data entry errors. This aligns with the data mining process, where understanding the root cause of data quality issues is critical before applying any imputation or removal techniques. Without investigation, you risk masking systemic problems that could affect all records.

Exam trap

The trap here is that candidates often jump to data cleaning techniques like imputation or removal without first verifying whether the anomaly is a data quality issue or a legitimate value, which the DA0-001 exam tests by emphasizing the investigative step as the most appropriate first action.

How to eliminate wrong answers

Option A is wrong because imputing using regression assumes the negative values are missing at random and that other variables can predict age, which is inappropriate when the negative values likely indicate a data entry error rather than missing data. Option B is wrong because replacing negative age with the mean age introduces bias and does not address the underlying cause; it also assumes the negative values are outliers rather than errors. Option D is wrong because removing all records with negative age could discard valid data if the negative values are due to a correctable entry mistake, and it reduces sample size without solving the root issue.

Practice this question →

Multi-Selectmedium

A data analyst is merging two datasets from different departments. The analyst notices that the 'CustomerID' field in the first dataset is stored as an integer, while in the second dataset it is stored as a string with leading zeros. Which TWO steps should the analyst take to ensure successful data integration?

Select 2 answers

A.Perform the merge directly without transformation since databases handle type conversions automatically.

B.Strip all non-numeric characters from the string CustomerID before joining.

C.Use a left join and treat the CustomerID as a string after conversion.

D.Convert the string CustomerID to an integer by removing leading zeros.

E.Convert the integer CustomerID to a string with leading zeros to match the format in the second dataset.

AnswersC, E

A left join requires matching keys; converting to string ensures compatibility.

Why this answer

Converting the integer CustomerID to a string ensures both datasets have a compatible data type for the join. This approach preserves the leading zeros in the second dataset, which are semantically significant (e.g., '00123' vs. 123). A left join is appropriate to retain all records from the primary dataset while matching on the converted key.

Exam trap

The trap here is that candidates assume implicit type conversion will handle the join correctly, but they overlook that leading zeros are lost during conversion, causing silent data loss or incorrect matches.

Practice this question →

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 consecutive ranks even with ties.

Why this answer

DENSE_RANK() assigns ranks with no gaps for ties, whereas RANK() leaves gaps.

Practice this question →

MCQmedium

Refer to the exhibit. What is the most likely issue causing the unexpectedly low count?

A.The customers table is indexed incorrectly

B.The query is missing a GROUP BY clause

C.The database was not refreshed

D.The signup_date column is in a different date format

AnswerD

Format mismatch causes filter mismatch.

Why this answer

If the signup_date column is stored in a different date format (e.g., MM/DD/YYYY), the comparison with '2023-01-01' (YYYY-MM-DD) may not match many records, leading to an unexpectedly low count. Option A is incorrect because indexing affects performance, not the correctness of the count. Option B is incorrect because COUNT(*) aggregates all rows and does not require a GROUP BY clause.

Option C is incorrect because a database refresh typically updates data but does not directly cause a mismatch in date comparisons.

Practice this question →

Multi-Selectmedium

A data analyst is exploring a sales dataset and wants to identify columns that are likely to be foreign keys. Which TWO characteristics would indicate a foreign key?

Select 2 answers

A.The column name ends with '_id'

B.The column contains NULL values

C.The column is of integer data type

D.The column values are a subset of a primary key column in another table

E.The column has a UNIQUE constraint

AnswersA, D

Often foreign keys are named with '_id' suffix.

Why this answer

Foreign keys typically match primary keys in another table and have a name suggestive of the relationship.

Practice this question →

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

Ensures sensitive information is protected early.

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 →

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

Transformation tools automate the conversion and ensure consistency.

Why this answer

(use a data transformation tool) is the standard practice during ETL to convert date formats programmatically. Option A (manually edit) is inefficient and error-prone. Option B (change schema) would require altering the database and may cause compatibility issues.

Option C (ignore) loses data integrity.

Practice this question →

MCQhard

A retail company is acquiring sales data from 150 stores worldwide. Each store sends daily CSV files via email to a central email address. The data acquisition process is manual: an intern downloads each attachment and copies it into a shared folder. The shared folder is then accessed by an ETL tool that loads data into a data warehouse. Recently, the data warehouse has been missing records for several stores. The intern reports that some emails are not being received or are delayed. The company needs to improve the reliability and timeliness of data acquisition. Which course of action should be taken first?

A.Train the intern to check email more frequently and manually verify all attachments.

B.Replace the email method with a web-based API that stores push data in real-time.

C.Implement an automated email parser that downloads attachments and moves them to the shared folder.

D.Require stores to upload CSV files directly to a cloud-based storage bucket.

AnswerD

Eliminates email dependency and manual steps.

Why this answer

Requiring stores to upload CSV files directly to a cloud-based storage bucket eliminates dependency on email and manual intervention, addressing the root cause of missing records due to email delays or non-receipt. This approach improves reliability and timeliness. Option A is wrong because it still relies on email and increases manual effort.

Option B, while potentially effective, is more complex to implement as a first step compared to switching to cloud uploads. Option C is wrong because automating email parsing still depends on email reliability, which is the underlying problem.

Practice this question →

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

Common when merging overlapping data.

Why this answer

Options B and E are correct because merging data from different sources often leads to duplicate records (the same entity represented differently across sources) and formatting inconsistencies (such as different date formats, units of measurement, or naming conventions). Option A is wrong: user access permissions are a security concern, not a direct data quality issue arising from merging. Option C is wrong: slow network speed is a performance or infrastructure issue, not a data quality issue.

Option D is wrong: high storage cost is a cost consideration, not a data quality issue.

Practice this question →

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

Correctly fills NaN with median.

Why this answer

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

Practice this question →

MCQeasy

A data analyst is tasked with collecting data from a web API that returns JSON. The API requires an API key in the header. Which method should be used to authenticate?

A.Use a session cookie

B.Add the API key in the HTTP header as 'Authorization: Bearer <key>'

C.Store the API key in the database and reference it

D.Include the API key in the URL query string

AnswerB

Standard bearer token authentication is secure and widely used.

Why this answer

The correct method is to include the API key in the HTTP header using the 'Authorization: Bearer <key>' format. This is the standard approach for token-based authentication in REST APIs, as defined by RFC 6750. It keeps the credential out of URLs and logs, and is the expected mechanism for API key authentication in modern web APIs.

Exam trap

CompTIA often tests the distinction between authentication methods, and the trap here is that candidates may confuse storing credentials (Option C) with transmitting them, or think that query strings (Option D) are acceptable because they work technically, ignoring security and standard practices.

How to eliminate wrong answers

Option A is wrong because session cookies are used for stateful web application sessions, not for stateless API authentication with a fixed API key; cookies are typically managed by the server and browser, not suitable for programmatic API calls. Option C is wrong because storing the API key in a database and referencing it describes a storage mechanism, not an authentication method sent in the request; the key must be transmitted with each API call, not just stored. Option D is wrong because including the API key in the URL query string exposes the key in server logs, browser history, and is less secure; it violates best practices and is not the standard method for API key authentication.

Practice this question →

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 up data to a consistent level.

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 →