75 of 208 questions · Page 2/3 · Data Acquisition and Preparation · Answers revealed
A data analyst needs to extract the year from a column named 'order_date' in a SQL database. The database supports standard SQL functions. Which function should they use?
Correct standard SQL syntax.
Why this answer
The EXTRACT function is standard SQL for extracting date parts. EXTRACT(YEAR FROM order_date) returns the year.
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?
LEFT JOIN includes all employees, even those without a department.
Why this answer
LEFT JOIN includes all rows from the left table (employees) even if no match in departments.
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?
This counts only unique non-null categories.
Why this answer
COUNT(DISTINCT column) counts unique non-null values in a column.
A data analyst wants to extract the year from a date column 'order_date' in a SQL database. Which function should be used?
Returns the year portion of the date.
Why this answer
The YEAR() function extracts the year from a date. DATEADD adds intervals, DATEDIFF calculates differences, GETDATE() returns current date.
Refer to the exhibit. What data quality issue is indicated?
The use of 'N/A' in a numeric field indicates lack of standardization.
Why this answer
The error shows that a non-numeric value 'N/A' is present in a numeric column, indicating non-standardized data entry. Duplication, inconsistency, or outliers are not directly shown.
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?
Correctly counts unique customer IDs.
Why this answer
COUNT(DISTINCT column) counts the number of unique non-null values in a column. COUNT(*) counts all rows including duplicates, COUNT(column) counts non-null values including duplicates. DISTINCT alone is not an aggregate function.
A dataset contains a column 'birthdate' in 'YYYY-MM-DD' format. The analyst needs to calculate the average age of customers as of today. Which combination of functions is most appropriate?
DATEDIFF with year returns the number of year boundaries crossed, which is a common approximation of age.
Why this answer
DATEDIFF(year, birthdate, GETDATE()) returns the number of year boundaries crossed between the two dates, which is the standard SQL method for calculating age. The other options subtract year components, which gives only an estimate that ignores the month and day, leading to inaccuracies.
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?
Correct. This computes the average of the current and previous 6 rows per product.
Why this answer
A moving average requires averaging over a frame of rows. Using AVG() with an ORDER BY in the OVER clause and a frame specification (ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) calculates the 7-day moving average.
A data team is using web scraping to collect competitor pricing data. The target website has anti-scraping measures like CAPTCHAs and rate limiting. Which approach is most effective?
Mimics human behavior and avoids detection.
Why this answer
Using rotating proxies and respectful delays helps evade anti-scraping mechanisms like CAPTCHAs and rate limiting by distributing requests across multiple IPs and mimicking human browsing behavior. Option A is incorrect because using a single IP address makes it easy for the website to block all requests from that IP. Option B is incorrect because disregarding robots.txt may violate the website's terms of service and could lead to legal action or IP bans.
Option D is incorrect because increasing request frequency would trigger rate limiting and increase the likelihood of being blocked.
A data analyst needs to retrieve all unique job titles from an employees table. Which SQL keyword should be used in the SELECT clause?
Correct. DISTINCT filters out duplicate rows.
Why this answer
DISTINCT removes duplicate rows from the result set, returning only unique values. In this case, SELECT DISTINCT job_title would return each job title only once.
A data analyst uses a Common Table Expression (CTE) to query hierarchical employee data (manager_id references employee_id). Which THREE statements about recursive CTEs are correct? (Select THREE).
The anchor member is the non-recursive initial query.
Why this answer
Recursive CTEs require the WITH RECURSIVE clause (or WITH in some DBMS that imply recursion). The UNION ALL is typical to combine anchor and recursive members. Anchor member is the starting set; recursive member references the CTE itself.
The anchor member is defined before the recursive member.
A healthcare organization collects patient questionnaire data via paper forms at clinics. The forms are scanned and sent to a central office, where staff manually enter data into an electronic system. This process is slow and error-prone. The organization wants to reduce manual entry errors and speed up data availability. Which method should they adopt?
OCR automates data extraction from scanned forms, reducing errors and increasing speed.
Why this answer
Optical Character Recognition (OCR) can convert scanned images to text automatically, reducing manual entry errors and speeding up the process. Requiring patients to fill out online forms may not be feasible for all patients, especially those without internet access. Continuing manual entry with double-checking is still slow and labor-intensive.
Sending to an external company introduces additional cost and potential privacy concerns.
An e-commerce company wants to integrate product pricing data from competitor websites to adjust its own prices dynamically. They plan to scrape pricing pages every hour. However, the competitors' websites have anti-scraping measures such as IP blocking and CAPTCHAs. The company's legal team also advises caution regarding terms of service. Which data acquisition strategy is both effective and compliant?
An API agreement is legal, compliant, and provides structured data access.
Why this answer
Negotiating with competitors to obtain pricing data via API agreements is the most compliant approach, as it avoids violating terms of service and ensures reliable data access. Using rotating proxies and automation to bypass anti-scraping measures may be effective but could violate laws or terms of service. Manual recording is too slow and not dynamic.
Using a public data aggregator may not provide the specific competitor data needed and could be costly.
A data analyst is using pandas in Python to merge two DataFrames: sales (columns: sale_id, product_id, amount) and products (columns: product_id, product_name). Which pandas function should they use to combine these DataFrames on the 'product_id' column?
Correct. merge() is designed for database-style joins.
Why this answer
The pandas merge function is used to combine DataFrames on common columns. The syntax is pd.merge(sales, products, on='product_id').
A data analyst is performing data profiling on a customer table. Which metric would best help identify missing values in the 'phone' column?
Null count shows number of records with missing phone values.
Why this answer
Null count directly measures missing values.
A data analyst needs to sample records from a large dataset for a quick analysis. Which TWO sampling methods are examples of probability sampling?
Every element has an equal probability of selection.
Why this answer
Simple random sampling and systematic sampling are probability-based methods where every element has a known chance of selection.
An analyst wants to identify outliers in a dataset using the IQR method. Which values are typically considered outliers?
Standard IQR outlier definition.
Why this answer
Outliers are values below Q1 - 1.5*IQR or above Q3 + 1.5*IQR.
In pandas, you have a DataFrame 'df' with columns 'product' and 'sales'. You want to calculate the total sales per product. Which method should you use?
Correctly aggregates sales by product.
Why this answer
df.groupby('product')['sales'].sum() groups by product and sums sales. df.pivot_table can also do it but is more complex. df.merge is for joining, df.apply is for applying a function element-wise or row/column-wise.
Match each database concept to its definition.
Drag a concept onto its matching description — or click a concept then click the description.
Unique identifier for each record in a table
Field that links to primary key in another table
Structure to speed up data retrieval
Virtual table based on a query result
Process to reduce data redundancy
Why these pairings
Primary keys uniquely identify records, foreign keys link tables, indexes speed retrieval, and normalization reduces redundancy. Common confusions include swapping primary and foreign key definitions.
In SQL, you want to retrieve all products whose names start with 'Pro'. Which WHERE clause should you use?
Matches product names starting with 'Pro'.
Why this answer
LIKE with pattern 'Pro%' matches strings starting with 'Pro' followed by any characters. '%Pro%' matches any string containing 'Pro', 'Pro_' matches 'Pro' plus one character, and 'Pro' is exact match.
A data analyst wants to combine first_name and last_name columns into a single full_name column in a SQL query. Which string function should be used?
CONCAT() concatenates strings.
Why this answer
CONCAT() joins two or more strings together.
You have a hierarchical table 'Employees' with columns emp_id, emp_name, manager_id (referencing emp_id). You need to generate a full reporting chain from a given employee up to the CEO. Which SQL construct is most appropriate?
Recursively joins the table to itself to traverse the hierarchy.
Why this answer
Recursive CTEs are designed for hierarchical data, allowing iteration through parent-child relationships. Non-recursive CTEs cannot loop. Self-join with multiple levels is possible but requires knowing the depth.
Window functions are not suitable for tree traversal.
An analyst is performing EDA and wants to measure the strength and direction of linear relationship between two continuous variables. Which statistical measure should they compute?
Correlation measures linear relationship.
Why this answer
Correlation coefficient (Pearson's r) measures linear relationship strength and direction.
During data acquisition, a data engineer uses a tool to extract data from a source system incrementally based on a timestamp column. Which method is being used?
CDC uses timestamps or logs to extract only changed data.
Why this answer
Change data capture (CDC) captures modifications since the last extraction. Full extraction retrieves all data each time, snapshot extracts a point-in-time copy, and manual is not automated.
A data analyst is using pandas to read a CSV file named 'sales.csv'. Which line of code correctly reads the file into a DataFrame?
Correct syntax.
Why this answer
The pandas function read_csv reads a CSV file into a DataFrame.
A company is merging two databases from different departments. In Database A, customer IDs are integers. In Database B, customer IDs are alphanumeric strings. To merge, the data analyst must reconcile these differences. Which step should be taken first?
Profiling helps determine the best strategy for reconciliation.
Why this answer
Data profiling is the essential first step before any transformation or mapping. It allows the analyst to examine the actual formats, patterns, and relationships in both ID columns (e.g., whether Database B's alphanumeric IDs contain embedded numeric sequences or consistent prefixes). Without profiling, any conversion or mapping would be based on assumptions that could lead to data loss or incorrect merges.
Exam trap
The trap here is that candidates assume immediate conversion (Option B) is the simplest solution, but the exam tests the principle that data profiling must precede any transformation to avoid irreversible data corruption.
How to eliminate wrong answers
Option A is wrong because dropping the ID column and using a surrogate key discards the existing business meaning and relationships, which may be critical for linking records across departments. Option B is wrong because converting all IDs to integers using CAST will fail on alphanumeric strings that contain non-numeric characters, causing errors or data loss. Option D is wrong because creating a mapping table based solely on the first character is arbitrary and ignores the full ID structure, leading to incorrect or incomplete mappings.
Which THREE data sources are suitable for web scraping? (Select three.)
HTML is the primary source for web scraping.
Why this answer
HTML pages are suitable for web scraping because they contain structured or semi-structured data in markup format that can be parsed using libraries like BeautifulSoup or Scrapy. Web scrapers extract information from the DOM tree by targeting specific tags, classes, or attributes, making HTML a primary source for scraping.
Exam trap
The trap here is that candidates may confuse 'web scraping' with any form of data extraction, but the exam specifically tests the understanding that scraping involves HTTP-based retrieval of web content, not direct file downloads or database queries.
A data pipeline log shows the above error. Which data transformation should be applied during acquisition?
Removing symbols before conversion ensures successful casting.
Why this answer
The error indicates that the pipeline encountered a string with non-numeric characters (e.g., '$1,234.56') when trying to load it into a DECIMAL column. Preprocessing the string to remove non-numeric characters (like currency symbols, commas) before conversion ensures the data is clean and parseable, which is a standard data transformation during acquisition to handle dirty source data.
Exam trap
The trap here is that candidates assume CAST in SQL can handle any string-to-number conversion, but CAST strictly requires a valid numeric string and will throw an error for non-numeric characters, making preprocessing essential.
How to eliminate wrong answers
Option A is wrong because skipping rows that cause errors would result in data loss and is not a proper transformation; it ignores the root cause of the dirty data. Option C is wrong because using CAST(transaction_amount AS DECIMAL(10,2)) in SQL would still fail if the string contains non-numeric characters, as CAST does not automatically strip them. Option D is wrong because changing the target column type to VARCHAR would avoid the conversion error but defeats the purpose of storing numeric data for calculations, leading to data integrity and performance issues.
A data analyst is profiling a dataset and finds that the 'email' column contains some NULL values. Which SQL query can be used to count how many rows have a NULL email?
Correct: counts all rows with null email.
Why this answer
COUNT(*) counts all rows; WHERE email IS NULL filters only null rows.
A data analyst runs the query: SELECT AVG(salary) FROM employees GROUP BY department HAVING AVG(salary) > 60000. What is the purpose of the HAVING clause?
HAVING filters groups based on aggregate conditions.
Why this answer
HAVING filters groups after aggregation, unlike WHERE which filters rows before aggregation.
A data analyst is cleaning text data in a SQL database. Which THREE string functions are commonly used to standardize and clean text? (Choose three.)
Replaces occurrences of a substring.
Why this answer
TRIM removes leading/trailing spaces, UPPER/LOWER standardize case, REPLACE substitutes substrings. CONCAT concatenates strings, LENGTH returns length, SUBSTRING extracts part of string.
A financial analyst is integrating data from multiple stock exchanges. One exchange provides trade timestamps in UTC, another in Eastern Time. The analyst needs accurate time synchronization for time-series analysis. What is the best approach?
Converting to a common timezone ensures consistent timestamps for analysis.
Why this answer
(convert to UTC) is the standard. Option A (keep original with offset) adds complexity. Option B (local time) is inconsistent.
Option D (ignore) leads to errors.
A data analyst is performing EDA on a dataset with numerical features. Which methods are appropriate for identifying outliers? (Select TWO).
Points with |Z| > 3 are often considered outliers.
Why this answer
IQR method uses Q1 - 1.5*IQR and Q3 + 1.5*IQR to define outliers. Z-score method uses threshold (e.g., |Z| > 3) to identify outliers.
A data analyst runs a query to count the number of customers in each city. The query uses COUNT(*) and GROUP BY city. However, the result includes NULL for some cities. What will COUNT(*) return for a group where the city is NULL?
COUNT(*) includes all rows, including those with NULL in the grouped column.
Why this answer
COUNT(*) counts all rows in a group, regardless of NULL values in any column. If the city is NULL, all rows in that group are counted.
Refer to the exhibit. An analyst sees this log during data acquisition. What action should be taken first?
Adjusting the mapping resolves the type mismatch for all rows.
Why this answer
The log shows a data type mismatch during ETL (Extract, Transform, Load) processing, where a date field is being read as a string. The correct first action is to modify the ETL mapping for data types to ensure the date string is properly cast or converted to the target date format, preventing data loss or corruption. This aligns with standard data acquisition best practices: adjust the transformation layer to handle source data anomalies before rejecting or altering the source.
Exam trap
CompTIA often tests the misconception that you should always fix the source data first, but in data acquisition, the ETL layer is the standard place to handle format conversions without altering the original source.
How to eliminate wrong answers
Option B is wrong because rejecting the entire dataset is an overreaction; a single data type mismatch can be resolved by adjusting the ETL mapping without discarding potentially valid data. Option C is wrong because ignoring warnings can lead to downstream errors, such as failed joins or incorrect date calculations, violating data integrity requirements. Option D is wrong because correcting the date string in the source is not always feasible (e.g., if the source is a third-party system or read-only), and the ETL layer is the appropriate place to handle such transformations.
A retail company's data analytics team needs to acquire point-of-sale (POS) transaction data from 200 stores daily. Each store sends a CSV file via email at the end of the day. The files often arrive late, have inconsistent column names (e.g., "StoreID", "Store_ID", "store_id"), and occasionally contain corrupted rows. The team manually processes these files, leading to frequent errors and delays. The company wants to automate the acquisition process to ensure data is available by 9 AM the next business day with high quality. Which approach best addresses these issues?
This automates the entire process, handles inconsistencies, and ensures timely availability with quality checks.
Why this answer
It directly addresses all three issues: automating the retrieval of email attachments (handling late arrivals), standardizing inconsistent column names via a script (e.g., mapping 'StoreID', 'Store_ID', 'store_id' to a canonical schema), and implementing validation logic to flag corrupted rows for manual review. This approach ensures data is processed reliably by 9 AM without manual intervention, meeting the automation and quality requirements.
Exam trap
The trap here is that candidates may choose Option C or D because they seem more 'modern' or 'direct,' but they fail to recognize that the question specifically requires handling existing CSV files and late arrivals, which a script-based ETL approach (Option A) directly solves without requiring stores to change their behavior or infrastructure.
How to eliminate wrong answers
Option B is wrong because hiring a data entry contractor introduces manual processing, which is the root cause of delays and errors, and does not automate the acquisition process. Option C is wrong because asking stores to use a standardized web form shifts the burden to 200 stores, which is impractical to enforce uniformly and does not address the existing CSV files or late arrivals; it also introduces new integration complexity without solving the immediate data pipeline issue. Option D is wrong because implementing a VPN for real-time writes requires significant network infrastructure changes, assumes stores have stable high-speed internet, and does not handle the existing CSV files or the need for batch processing by 9 AM; real-time writes also increase the risk of data corruption without validation.
An organization is acquiring data from an external vendor. The vendor provides a flat file with inconsistent delimiters and missing values. Which step should be performed first in data acquisition?
Profiling reveals structure, quality, and inconsistencies first.
Why this answer
Data profiling is the first step because it examines the data to understand its structure, quality, and issues (like inconsistent delimiters and missing values) before any further processing. Option A (Data integration) is wrong because integration combines data from multiple sources and should follow profiling. Option C (Data transformation) is wrong because transforming data requires first understanding its current state through profiling.
Option D (Data cleansing) is wrong because cleansing is performed after profiling identifies the issues.
A data analyst is validating a dataset acquired from an external source. Which TWO actions are appropriate for data quality assessment?
Missing value checks are fundamental to data quality.
Why this answer
Checking for missing values in critical fields is a fundamental data quality assessment step because missing data can indicate incomplete records, data corruption, or extraction errors. Identifying these gaps early allows the analyst to decide on appropriate handling strategies, such as imputation or rejection, before further processing. This aligns with data profiling best practices in the mining and acquisition phase.
Exam trap
The trap here is that candidates may confuse data cleaning (which includes deletion or transformation) with data quality assessment, which is the diagnostic step that should occur before any irreversible actions like deletion or production loading.
A marketing company is building a customer segmentation model. The data team has access to two sources: a CRM database with customer demographics and purchase history, and a third-party data provider that offers social media activity scores. The CRM data is updated daily, while the third-party data is refreshed weekly on Sundays. The analyst needs to create a unified dataset for the model training scheduled for Wednesday morning. The analyst runs a SQL query to join the two tables on CustomerID, but the resulting dataset has far fewer rows than expected. Upon investigation, the analyst finds that many customers in the CRM do not have matching records in the third-party data. Additionally, some customers in the third-party data have multiple entries due to unresolved duplicates. The analyst must produce the most complete dataset possible while maintaining data quality. Which course of action should the analyst take?
This preserves all CRM customers and handles duplicates correctly.
Why this answer
It first resolves the duplicate issue in the third-party data by keeping the most recent record per CustomerID, ensuring each customer has a single, current social media score. Then, a LEFT JOIN from CRM to the deduplicated third-party data preserves all CRM customers, maximizing completeness while maintaining data quality. This approach aligns with the goal of producing the most complete dataset for model training, as the CRM is the primary source with daily updates.
Exam trap
The trap here is that candidates may choose an INNER JOIN (Option B) thinking it ensures data quality by only including matched records, but they overlook the requirement for completeness, which necessitates preserving all CRM customers even without third-party matches.
How to eliminate wrong answers
Option B is wrong because an INNER JOIN would exclude CRM customers without matching third-party records, reducing dataset completeness, and removing duplicates after the join does not address the root cause of multiple entries in the third-party data. Option C is wrong because using only third-party data discards the CRM's daily-updated demographics and purchase history, which are essential for segmentation and would result in an incomplete dataset. Option D is wrong because a LEFT JOIN from third-party data to CRM would prioritize third-party customers, potentially losing CRM-only customers, and averaging scores across duplicates introduces data quality issues by conflating multiple records into a single value without considering recency or validity.
What is the primary purpose of the HAVING clause in the query shown?
HAVING filters groups that meet the aggregate condition.
Why this answer
The HAVING clause is used to filter groups after the GROUP BY clause has aggregated the data. In SQL, WHERE filters individual rows before aggregation, while HAVING applies conditions to the results of aggregate functions like SUM, COUNT, or AVG. Option D is correct because the query uses HAVING to restrict which grouped results appear in the final output.
Exam trap
The trap here is confusing WHERE and HAVING: candidates often pick 'Filter rows before grouping' because they think all filtering happens before aggregation, but HAVING specifically filters groups after aggregation, not individual rows.
How to eliminate wrong answers
Option A is wrong because sorting is performed by the ORDER BY clause, not HAVING; HAVING has no sorting functionality. Option B is wrong because joining tables is done with JOIN (or FROM with comma-separated tables) and ON conditions, not with HAVING. Option C is wrong because filtering rows before grouping is the role of the WHERE clause; HAVING operates after aggregation, on groups, not on individual rows.
A data analyst needs to collect customer sentiment data from social media platforms. Which data acquisition method is most appropriate?
Web scraping automates extraction of data from social media platforms.
Why this answer
Web scraping is the most appropriate method because it allows the data analyst to programmatically extract unstructured customer sentiment data (e.g., posts, comments, reviews) directly from social media platforms using HTTP requests and HTML parsing. Unlike surveys or focus groups, scraping can collect large volumes of real-time, publicly available data without relying on self-reported or curated responses.
Exam trap
CompTIA often tests the distinction between primary data collection (surveys, focus groups) and secondary data acquisition (web scraping, APIs), where candidates mistakenly choose a primary method for a task that requires large-scale, unsolicited external data.
How to eliminate wrong answers
Option A is wrong because conducting a survey collects self-reported, structured data from a controlled sample, which is not suitable for capturing organic, unsolicited sentiment from social media platforms in real time. Option B is wrong because organizing focus groups gathers qualitative feedback from a small, moderated group, which lacks the scale and authenticity of public social media sentiment and introduces moderator bias. Option D is wrong because querying the internal CRM retrieves structured customer data from internal systems (e.g., purchase history, support tickets), not the unstructured, external social media content needed for sentiment analysis.
A data analyst is validating referential integrity between orders and customers tables. Which TWO of the following checks should the analyst perform?
Ensures no orphaned records.
Why this answer
Referential integrity ensures foreign keys match primary keys and no orphaned records.
Which TWO are common methods for acquiring internal data? (Choose two.)
Transaction logs record internal system activities.
Why this answer
Transaction logs are a primary source of internal data because they record every interaction or event within a system, such as database changes, user access, or application errors. This data is generated and stored internally by the organization's own infrastructure, making it a classic example of internal data acquisition.
Exam trap
The trap here is that candidates may confuse 'internal data' with 'publicly available data' or 'data from third-party sources,' leading them to select social media APIs or government databases, which are external, not internal.
A data team is integrating customer data from three sources. After joining, they find that the count of unique customers is lower than expected. What is the most likely cause?
Mismatched key formats cause join failures, reducing matches.
Why this answer
When joining customer data from multiple sources, inconsistent key definitions (e.g., one source uses integer IDs while another uses string IDs, or different formats like 'CUST-001' vs '1001') cause the join to fail to match records that actually represent the same customer. This results in fewer unique customers than expected because the join treats mismatched keys as different entities, effectively dropping or misaligning records. The data team likely used an inner join or a left join that only retains matches based on exact key equality, so any key inconsistency reduces the count of matched unique customers.
Exam trap
The trap here is that candidates often assume missing values or duplicates are the primary cause of a lower unique count, but CompTIA Data+ tests the nuance that inconsistent key definitions—not missing data—are the most common reason for unexpected join results in multi-source integration scenarios.
How to eliminate wrong answers
Option B is wrong because missing values in join keys would typically cause rows to be excluded from the join (e.g., NULL keys in SQL inner joins are not matched), which could reduce the total row count but not specifically the count of unique customers—missing keys usually lead to fewer rows overall, not a lower unique customer count after join. Option C is wrong because data truncation during transfer (e.g., cutting off characters from a VARCHAR field) would likely cause data loss or corruption, but it would not systematically reduce the count of unique customers; it might introduce mismatches or duplicates, but the primary effect is not a lower unique count. Option D is wrong because duplicate entries across sources would actually increase the count of unique customers if duplicates are not deduplicated, or if they are deduplicated, the unique count might be accurate; duplicates do not inherently cause a lower unique count—they cause inflated counts or require deduplication logic.
A data analyst needs to perform a stratified random sample of a customer database. Which TWO steps are essential for this sampling method? (Select two.)
Proportional selection ensures representation.
Why this answer
Stratified sampling requires dividing the population into strata and then randomly sampling from each stratum.
A data analyst is tasked with combining customer data from a CRM system and a billing system. The CRM uses a GUID for customer ID, while billing uses an integer. Which approach should the analyst use to ensure a reliable merge?
Standardizing keys ensures a consistent, unique identifier for accurate merging.
Why this answer
Standardizing the customer ID format (e.g., converting the billing integer to a GUID or mapping both to a common string key) ensures a consistent join key across heterogeneous systems. This eliminates type mismatch errors and guarantees that each customer record can be matched reliably, as GUIDs are globally unique and integers are typically sequential, so direct comparison would fail without transformation.
Exam trap
The trap here is that candidates may assume customer name or address are sufficient join keys due to their human readability, underestimating the importance of unique, system-agnostic identifiers for reliable data merging.
How to eliminate wrong answers
Option B is wrong because customer names are not guaranteed to be unique (e.g., multiple customers named 'John Smith') and may have formatting inconsistencies (e.g., case, spaces), leading to incorrect or missed matches. Option C is wrong because a cross-join produces a Cartesian product of all rows, which is computationally expensive and requires manual filtering that is error-prone and does not leverage any reliable key for accurate merging. Option D is wrong because fuzzy matching on addresses is imprecise and computationally intensive; addresses can have variations (e.g., 'St.' vs 'Street') and may not uniquely identify a customer (e.g., multiple customers at the same address), making it unreliable for a deterministic merge.
A data analyst needs to create a new column 'full_name' by concatenating 'first_name' and 'last_name' with a space. Which SQL function should be used in the SELECT clause?
Correct: CONCAT joins strings.
Why this answer
CONCAT concatenates strings; in some DBMS, || or + is used, but CONCAT is standard.
A data analyst is conducting exploratory data analysis (EDA) on a dataset. Which TWO tasks are typically performed during EDA? (Select two.)
Outlier identification is part of EDA.
Why this answer
Outlier detection and correlation analysis are key EDA activities. Model building and data sampling are separate steps.
A data quality assessment reveals that a column named 'email' contains values like 'user@example' (missing domain extension). Which data profiling technique would best identify such pattern violations?
Identifies values that do not conform to expected formats.
Why this answer
Pattern analysis involves checking values against expected patterns (e.g., regex for email format). Cardinality counts distinct values, referential integrity checks relationships between tables, and data type verification checks data types.
A data analyst is writing a query to rank products by total sales within each category, showing dense rank and avoiding gaps. Which window function should be used?
DENSE_RANK() ranks without gaps.
Why this answer
DENSE_RANK() assigns ranks without gaps, so tied values get the same rank and the next rank is the next consecutive number.
A data analyst needs to combine sales data from multiple regional databases with different schemas. Which process is best?
ETL extracts data from various sources, transforms it to a consistent schema, and loads it into a target system, which is ideal for combining data from multiple databases with different schemas.
Why this answer
ETL (Extract, Transform, Load) is designed to extract data from various sources, transform it to a common schema, and load it into a target system. Option A (Data federation) is wrong because it provides virtual integration without transforming data into a consistent schema. Option C (Data replication) is wrong because it copies data without transformation.
Option D (Data virtualization) is wrong because it provides real-time access to disparate sources without physically storing the transformed data.
A company wants to collect real-time clickstream data from its website. Which acquisition method is most suitable?
Enables continuous, low-latency data ingestion.
Why this answer
A streaming API is the most suitable method for collecting real-time clickstream data because it enables continuous, low-latency ingestion of events as they occur. Unlike batch or manual methods, a streaming API (e.g., using WebSockets or HTTP/2 Server-Sent Events) pushes each click event immediately to the data pipeline, satisfying the real-time requirement.
Exam trap
CompTIA often tests the distinction between 'real-time' and 'near-real-time' or 'batch' methods, and the trap here is that candidates may confuse web scraping (which can be automated frequently) with true streaming, not realizing that scraping is still a pull-based, scheduled operation that cannot match the push-based immediacy of a streaming API.
How to eliminate wrong answers
Option B (Web scraping) is wrong because it is a pull-based technique that typically retrieves static HTML pages at intervals, not real-time event streams, and is inefficient for high-frequency click data. Option C (Batch processing nightly) is wrong because it introduces a delay of up to 24 hours, failing the real-time requirement. Option D (Manual entry) is wrong because it is error-prone, non-scalable, and cannot capture high-velocity clickstream data in real time.
A data analyst wants to ensure a sample proportionally represents different regions in a population. Which sampling method should be used?
Stratified sampling ensures proportional representation from each stratum.
Why this answer
Stratified sampling divides the population into strata (regions) and samples proportionally from each.
A data analyst is cleaning a dataset and finds that some cells in the 'email' column contain leading spaces. Which string function should be used to remove these spaces?
TRIM removes both leading and trailing spaces.
Why this answer
TRIM removes leading and trailing spaces from a string.
An analyst is using SQL to analyze employee data. Which THREE of the following are valid uses of the WHERE clause? (Select three.)
IS NULL is used in WHERE to check for NULL values.
Why this answer
WHERE can filter using LIKE, BETWEEN, and IS NULL. HAVING is for aggregated results, and ORDER BY is for sorting.
A data analyst needs to extract data from an API that returns JSON. The analyst wants to convert the JSON output into a tabular format for analysis. Which function in a scripting language is commonly used for this purpose?
This function normalizes semi-structured JSON data into a flat table.
Why this answer
`json_normalize()` is a function in the pandas library specifically designed to flatten semi-structured JSON data (including nested lists and dictionaries) into a tabular DataFrame. This makes it the ideal tool for converting API responses with complex nesting into rows and columns for analysis, unlike simpler JSON parsing functions.
Exam trap
The trap here is that candidates confuse `read_json()` (which works only for flat JSON) with `json_normalize()` (which handles nested structures), leading them to choose option C when the API response contains hierarchical data.
How to eliminate wrong answers
Option A is wrong because `json.loads()` only parses a JSON string into a Python dictionary or list; it does not flatten nested structures or produce a tabular format. Option B is wrong because `to_csv()` is a pandas method for exporting a DataFrame to a CSV file, not for converting JSON to a table. Option C is wrong because `read_json()` in pandas reads a JSON file or string into a DataFrame but only handles simple, flat JSON structures; it fails with deeply nested JSON (e.g., arrays of objects with sub-objects) without additional normalization.
Which TWO are examples of internal data sources? (Select exactly 2)
Common internal source.
Why this answer
Relational databases are internal data sources because they store structured data generated and controlled within an organization's own systems. They are typically managed by internal IT teams and accessed via SQL queries, making them a classic example of an internal data repository.
Exam trap
CompTIA often tests the distinction between data sources and data access methods, so candidates mistakenly select APIs or sensor readings as internal sources when they are actually mechanisms or external origin points.
Refer to the exhibit. If the date column is stored as a string in 'MM/DD/YYYY' format, what will be the result?
The different format causes lexicographic comparison to fail.
Why this answer
When dates are stored as strings in 'MM/DD/YYYY' format, string comparison is lexicographic (character-by-character). This means that '01/02/2023' (January 2) would be considered greater than '12/31/2022' because '0' > '1' at the first character, leading to incorrect chronological ordering. The comparison does not interpret the string as a date value.
Exam trap
CompTIA often tests the misconception that string comparison of dates in 'MM/DD/YYYY' format will yield correct chronological order, but the trap is that lexicographic comparison compares month first, not year, leading to incorrect results.
How to eliminate wrong answers
Option B is wrong because string comparison does not produce NULL values; it simply compares strings lexicographically and returns a valid boolean result. Option C is wrong because no error occurs; the database or application will perform string comparison without expecting a DATE type, as the column is defined as a string. Option D is wrong because string comparison does not work correctly for dates in this format; lexicographic order does not match chronological order for 'MM/DD/YYYY' strings.
A data analyst needs to merge two customer tables from different sources. One table uses 'CUST_ID' as the primary key, the other uses 'CustomerID'. To ensure accurate merging, the analyst should first:
Standardizing key names allows for accurate merging without data loss.
Why this answer
Normalizing key column names to a common format (Option B) is the correct first step because the merge operation requires a consistent join key. Without aligning 'CUST_ID' and 'CustomerID' to a single name and data type, the database or ETL tool will treat them as different columns, resulting in a cross join or an error. This step ensures referential integrity and enables an accurate inner or outer join based on the primary key.
Exam trap
The trap here is that candidates assume deduplication (Option C) is the most critical first step, but without first standardizing the join keys, any deduplication logic would operate on mismatched or incomplete data, leading to incorrect results.
How to eliminate wrong answers
Option A is wrong because performing a fuzzy match on names is an advanced, resource-intensive technique used only when exact key values are unavailable or inconsistent; it is unnecessary when the tables already have primary key columns that can be standardized. Option C is wrong because removing duplicate rows before aligning key names could inadvertently delete legitimate records that only appear duplicated due to key naming differences, and deduplication should occur after the merge or as a separate quality step. Option D is wrong because aggregating data by region is a post-merge analytical operation that has no bearing on resolving key column mismatches and would corrupt the granularity needed for accurate joining.
Refer to the exhibit. An analyst runs this query before acquiring data from a PostgreSQL database. What is the primary purpose of this query?
The 'last_analyzed' column shows when statistics were last updated, indicating freshness.
Why this answer
The query shown in the exhibit retrieves the `last_analyzed` column from PostgreSQL's system catalog, which records when table statistics were last updated. Analyzing this timestamp helps the analyst determine if the table's data is fresh enough for the intended analysis, thus checking data freshness before acquisition.
Exam trap
The trap here is that candidates may misinterpret the query as checking data types or primary keys because they see column names like `last_analyzed` or `last_updated` and assume schema inspection. In reality, any column representing a timestamp of last modification or analysis is used to gauge data freshness, not structural properties.
How to eliminate wrong answers
Option A is wrong because verifying data types requires querying the `information_schema.columns` table or using `pg_typeof()`, not `current_timestamp`. Option C is wrong because finding primary keys involves querying `information_schema.table_constraints` or `pg_indexes`, not a simple timestamp function. Option D is wrong because identifying duplicate tables would require comparing table names or schemas via `information_schema.tables`, not a timestamp query.
A data analyst is tasked with gathering data from a legacy system that only exports CSV files. The files contain headers but no data types. Which tool would best facilitate initial data exploration?
Provides powerful data structures and functions for CSV exploration.
Why this answer
Python pandas. Python pandas is ideal for initial data exploration of CSV files because it provides Series and DataFrames for tabular data, automatic type inference, and functions like head(), info(), describe() to quickly understand the data. Option A (Hadoop) is overkill for a simple CSV file exploration.
Option B (Tableau) is primarily for visualization and requires data to be already structured or imported. Option C (SQL database) is not the best tool for immediate exploration since it requires database setup and data import. Therefore, Python pandas is the most appropriate tool for initial exploration of CSV data.
During EDA, an analyst calculates the Z-score for each data point in a dataset. A data point with a Z-score of 3.5 is identified. What does this indicate?
A Z-score above 3 or below -3 is often considered an outlier.
Why this answer
A Z-score of 3.5 means the value is 3.5 standard deviations from the mean, commonly considered an outlier (threshold often >3 or <-3).
Drag and drop the steps to perform a data audit in the correct order.
Drag steps to the numbered slots on the right, or tap a step then tap a slot.
Why this order
Data audit begins with inventory, quality assessment, compliance check, documentation, and recommendations.
A data analyst is performing data profiling on a customer table. Which TWO metrics are commonly used to assess the completeness of a column? (Select TWO.)
Total rows; used to compute percentage complete.
Why this answer
Completeness is measured by null count and row count; null count shows missing values, row count gives total rows.
A data engineer is designing a data pipeline to ingest streaming data from IoT sensors. The sensors send data every second, and the pipeline must handle bursts of up to 10,000 messages per second. Which approach is most appropriate for capturing this data before processing?
A message queue handles high throughput and provides reliable buffering.
Why this answer
A message queue (e.g., Apache Kafka, Amazon Kinesis, or RabbitMQ) provides an asynchronous buffer that decouples the high-velocity ingestion (up to 10,000 messages/second) from downstream processing. This allows the pipeline to absorb burst traffic without overwhelming the processing layer, ensures data durability, and supports replayability in case of failures.
Exam trap
CompTIA often tests the misconception that relational databases or data warehouses can handle real-time streaming ingestion at scale, when in fact they require a buffering layer like a message queue to absorb bursts and decouple ingestion from processing.
How to eliminate wrong answers
Option A is wrong because directly writing each message to a relational database (RDBMS) at 10,000 messages/second would cause severe write contention, lock contention, and I/O bottlenecks, leading to dropped data and unacceptable latency. Option B is wrong because loading directly into a data warehouse (e.g., Snowflake, Redshift) is designed for batch or micro-batch ingestion, not for real-time streaming at this scale; it would incur high costs and fail to handle bursty throughput without prior buffering. Option D is wrong because storing data in flat files and processing in nightly batches introduces unacceptable latency (up to 24 hours) for streaming IoT data, and the file system cannot reliably handle 10,000 writes per second without data loss or corruption.
A data analyst is importing a CSV file that contains a mixture of numeric and text fields. What is the most common issue when importing?
CSV import tools often guess types incorrectly, leading to conversion errors.
Why this answer
Data type inference often fails, causing numbers to be read as text or vice versa. File size limitations, missing headers, and duplicate rows are less common or not specific to mixed types.
A marketing team wants to analyze customer sentiment from social media posts. Which data acquisition method is most appropriate?
Allows direct access to public posts for sentiment analysis.
Why this answer
Web scraping from public social media APIs allows direct access to public posts for sentiment analysis, which is exactly what the marketing team needs. Option A is wrong because internal databases typically do not contain social media data. Option B is wrong because physical sensors are unrelated to social media sentiment.
Option D is wrong because survey questionnaires are not real-time and do not capture existing social media posts.
An e-commerce company is merging customer data from three legacy systems. Two systems use email as unique identifier, but one system allows multiple customers per email. The third uses phone number. To create a unified customer view, the analyst should first:
Multi-attribute matching handles non-unique identifiers and improves accuracy.
Why this answer
Merging data from systems with different identifier schemas requires a probabilistic matching approach. Using multiple attributes (email, phone, name) with a confidence score allows the analyst to resolve conflicts where email is not unique and phone numbers may be missing or formatted differently, creating a unified customer view without forcing a single key.
Exam trap
The trap here is that candidates assume a single unique identifier (email) can be forced as a primary key, ignoring the real-world data quality issue of non-unique emails, which the question explicitly states.
How to eliminate wrong answers
Option A is wrong because modifying legacy systems is often impractical, costly, and outside the analyst's scope; the question asks what the analyst should do first, not a long-term IT project. Option C is wrong because using email as primary key and ignoring conflicts would lose data integrity when one email maps to multiple customers, violating the goal of a unified view. Option D is wrong because assigning new unique IDs and discarding existing identifiers eliminates the ability to link records back to source systems and loses valuable matching context, making deduplication impossible.
In SQL, which string function would you use to remove leading and trailing spaces from a column named 'city'?
Correct. TRIM removes both leading and trailing spaces.
Why this answer
TRIM removes leading and trailing spaces (or other specified characters) from a string. TRIM(city) returns the city without extra spaces.
Which THREE are best practices for data profiling during acquisition? (Choose three.)
Ensuring all required fields are populated is essential.
Why this answer
Checking for completeness (Option B) is a best practice during data acquisition because it ensures that all required fields and records are present before further processing. Incomplete data can lead to incorrect analysis or failed transformations, so profiling for missing values or nulls is a fundamental validation step.
Exam trap
The trap here is that candidates confuse 'best practices for acquisition' with 'best practices for transformation,' leading them to select normalization (Option A) as an immediate step rather than a later processing stage.
A data analyst is using a recursive CTE to traverse an organizational hierarchy. What is the purpose of the anchor member in the recursive CTE?
The anchor member returns the base result set.
Why this answer
The anchor member initializes the recursion with the base result set.
Match each data analysis technique to its primary purpose.
Drag a concept onto its matching description — or click a concept then click the description.
Model relationships between variables
Group similar data points without labels
Analyze data points collected over time
Compare means across multiple groups
Test association between categorical variables
Why these pairings
The correct matches are: Regression with predicting continuous outcomes, Clustering with grouping similar data, Classification with assigning categories, and PCA with reducing dimensionality. Common confusions include swapping regression and clustering definitions.
An analyst wants to use Python (pandas) to compute the average sales amount per region from a DataFrame 'df' with columns 'region' and 'sales'. Which TWO pandas operations are needed? (Select TWO).
Pivot table with mean aggregation.
Why this answer
To compute average per group, you can use groupby() followed by mean(), or pivot_table() with aggfunc='mean'. merge() combines DataFrames, apply() can be used but is less direct, and fillna() handles missing values.
In a table with columns 'employee_id' and 'manager_id', a data analyst needs to retrieve the hierarchy level of each employee, where the top manager has manager_id NULL. Which SQL feature is best suited?
Recursive CTE can iterate through levels to assign hierarchy depth.
Why this answer
Recursive CTE can traverse hierarchical data to compute levels.
A data analyst is reviewing sales data and wants to find orders where the order total is between $100 and $500, inclusive. Which WHERE clause is correct?
BETWEEN includes both boundary values.
Why this answer
BETWEEN is inclusive of both endpoints.
Ready to test yourself?
Try a timed practice session using only Data Acquisition and Preparation questions.