Data Acquisition and Preparation

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 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?

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

A 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?

Refer to the exhibit. What does the query return?

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?

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

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

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 marketing team wants to collect data on competitor pricing for similar products. Which data source is most appropriate?

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

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 data scientist is merging retail transaction data from online and in-store sources. Which THREE steps are required to ensure data consistency?

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?

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

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

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

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

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?

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

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

A 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).

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?

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

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?

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

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

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

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

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

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 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?

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

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?

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

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 data analyst uses Python's pandas library to read a CSV file into a DataFrame. Which function is used to read the file?

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

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

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 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?

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

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

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

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 data analyst needs to retrieve all unique job titles from the employees table. Which SQL clause should be used with the SELECT statement?

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

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

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 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 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 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)

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?

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 analyst wants to generate a report showing employee names and their department names, but some employees are not assigned to any department. The analyst wants to include all employees. Which JOIN type should be used?

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

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

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

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

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

A 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?

A data analyst needs to count the number of customers who have placed at least one order. Which SQL query should be used?

A data analyst is assessing the quality of a newly acquired dataset from an external source. The analyst needs to evaluate two dimensions of data quality that directly affect the dataset's fitness for use in analysis. Which two dimensions should the analyst prioritize? (Choose two.)

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

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?

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?

A data analyst is performing data profiling on a customer table. Which metric would best help identify missing values in the 'phone' column?

A data engineer is ingesting JSON data from an IoT sensor network. The JSON records contain nested arrays and objects. The engineer needs to flatten the structure to load it into a relational table. Which approach is most appropriate?

A data engineer is loading a large CSV file into a relational staging table. Several columns contain numeric values with thousands separators, such as '1,234.56', and a few rows contain the text 'N/A' in those columns. The target columns are defined as DECIMAL. Which approach best prepares the data for a successful load while preserving the ability to audit rejected values?

An analyst wants to identify outliers in a dataset using the IQR method. Which values are typically considered outliers?

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?

Match each database concept to its definition.

A data engineer is extracting data from a REST API that returns JSON. The API paginates results with a 'next_page' token. The engineer needs to load all pages into a database. Which approach should the engineer use to ensure all data is acquired?

In SQL, you want to retrieve all products whose names start with 'Pro'. Which WHERE clause should you use?

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?

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?

A data engineer is profiling a newly acquired customer table before loading it into a warehouse. The table has a customer_id column that should be unique, a signup_date column stored as text in 'YYYY-MM-DD' format, and a country column with values such as 'US', 'USA', 'United States', and 'U.S.'. The engineer must document which data quality dimensions are violated and plan remediation. Which TWO actions best address the identified data quality issues? (Choose two.)

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?

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?

A data analyst at a retail bank is preparing a daily transaction file for loading into the analytics warehouse. The source system exports dates in the format 'MM/DD/YYYY', but the target warehouse requires ISO 8601 format 'YYYY-MM-DD'. The analyst needs to transform the date column without changing the underlying date value. Which transformation should the analyst perform?

A healthcare analytics team is acquiring a monthly extract of patient encounter records from a partner hospital. The extract arrives as a compressed CSV with a documented layout, but the team notices that the record count has dropped by roughly 15 percent compared with the prior month and that several encounters near month-end are absent. Which acquisition control should the team apply FIRST to determine whether the issue is a delivery problem or a source-system problem?

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?

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?

A data pipeline log shows the above error. Which data transformation should be applied during acquisition?

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?

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?

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

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?

A data analyst is performing EDA on a dataset with numerical features. Which methods are appropriate for identifying outliers? (Select TWO).

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?

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?

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?

A data analyst is validating a dataset acquired from an external source. Which TWO actions are appropriate for data quality assessment?

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?

What is the primary purpose of the HAVING clause in the query shown?

A data analyst needs to collect customer sentiment data from social media platforms. Which data acquisition method is most appropriate?

A data analyst is validating referential integrity between orders and customers tables. Which TWO of the following checks should the analyst perform?

Which TWO are common methods for acquiring internal data? (Choose two.)

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

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?

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?

A data analyst is conducting exploratory data analysis (EDA) on a dataset. Which TWO tasks are typically performed during EDA? (Select two.)

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?

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?

A retail analytics team loads a nightly CSV export into their warehouse. During validation, the analyst notices that the 'order_date' column, defined as DATE in the target schema, contains values like '2023-13-45' and 'N/A' in several rows. The ETL job currently fails silently on these rows. Which data acquisition and preparation action BEST addresses the root cause while preserving as much data as possible?

A data analyst at a healthcare provider is reconciling patient records from two source systems. System A stores dates in 'MM/DD/YYYY' format, while System B stores dates in 'DD/MM/YYYY' format. During integration, the analyst notices that some records from System B have been incorrectly parsed, resulting in invalid dates. Which data preparation technique should the analyst apply to ensure consistent date interpretation?

A data analyst needs to combine sales data from multiple regional databases with different schemas. Which process is best?

A company wants to collect real-time clickstream data from its website. Which acquisition method is most suitable?

A data analyst is preparing a dataset for a machine learning model. The dataset contains a categorical column 'color' with values 'red', 'green', 'blue', and 'yellow'. The analyst needs to transform this column into a numerical format suitable for the model. Which technique should be used?

A data analyst wants to ensure a sample proportionally represents different regions in a population. Which sampling method should be used?

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?

An analyst is using SQL to analyze employee data. Which THREE of the following are valid uses of the WHERE clause? (Select three.)

A data analyst at a retail chain is importing a CSV file into a database. The file contains a 'transaction_date' column with values like '2023-13-01' and '2023-02-30'. The target column is defined as DATE. The analyst needs to ensure that invalid dates are flagged and not loaded. Which approach best handles this data quality issue during acquisition?

Refer to the exhibit. If the date column is stored as a string in 'MM/DD/YYYY' format, what will be the result?

A data analyst is preparing a dataset for predictive modeling and must handle missing values in several numeric and categorical columns. The team needs defensible, documented choices rather than ad hoc deletion. Which TWO actions are appropriate for handling missing data in this scenario? (Choose two.)

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:

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?

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?

Drag and drop the steps to perform a data audit in the correct order.

A data analyst is integrating data from two source systems into a single customer dataset. Source A uses a customer ID format like 'CUST-12345', while Source B uses '12345'. Additionally, Source A records dates in 'MM/DD/YYYY' format, while Source B uses 'YYYY-MM-DD'. Which two data preparation tasks are essential to ensure the integrated dataset is consistent and usable? (Choose two.)

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 data analyst is importing a CSV file that contains a mixture of numeric and text fields. What is the most common issue when importing?

A marketing analyst is combining two datasets: one containing campaign IDs and spend, and another containing campaign IDs and impressions. The first dataset has 1,200 rows and the second has 950 rows. After an inner join on campaign_id, the result has 1,050 rows. Which statement best explains this result?

A marketing team wants to analyze customer sentiment from social media posts. Which data acquisition method is most appropriate?

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:

In SQL, which string function would you use to remove leading and trailing spaces from a column named 'city'?

A data analyst is profiling a dataset and notices that the 'age' column contains negative values and values exceeding 120. The analyst needs to address these anomalies. Which data preparation technique is most appropriate?

Which THREE are best practices for data profiling during acquisition? (Choose three.)

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?

Match each data analysis technique to its primary purpose.

A data analyst is profiling a newly acquired customer dataset before loading it into a warehouse. The analyst notices that the 'country' column contains values such as 'USA', 'United States', 'U.S.A.', and 'US'. Which TWO actions are appropriate for standardizing this column during data preparation? (Choose two.)

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

An analyst receives a dataset of website sessions where the session_duration_seconds column contains several negative values and a few values exceeding 86,400 seconds. The analyst must prepare this data for analysis of average session length. Which action best addresses this data quality issue while preserving analytical integrity?

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?

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?

An analyst is reviewing the above SQL query used to acquire data. What does this query retrieve?

A dataset contains sales transactions with columns 'order_date', 'amount', and 'region'. The analyst wants to calculate the total sales per region for orders placed in 2023, but only include regions where total sales exceed $10,000. Which SQL clause should be used to filter the aggregated results?

A data analyst uses a CTE to simplify a complex query. Which keyword is used to define a CTE?

An analyst writes a SQL query that uses a window function: SELECT employee_id, salary, LAG(salary, 1) OVER (ORDER BY salary DESC) AS prev_salary FROM employees. What does the LAG function return for the row with the highest salary?

An analyst is sampling a large customer database to estimate the average purchase amount. To ensure that the sample proportionally represents different customer segments (e.g., age groups), which sampling method should be used?

A company is acquiring social media data via a public API. Which TWO considerations are important for ensuring ethical and legal compliance?

A data analyst needs to identify duplicate customer records. Which TWO methods are commonly used? (Select two.)

A data analyst receives the above JSON snippet from a web API. The analyst needs to extract the email addresses for all customers. Which JSONPath expression should be used?

A data team needs to extract data from a legacy system that only supports flat file exports. Which data acquisition method is most appropriate?

A data analyst is importing a fixed-width text file into a relational database. The file has no header row, and fields are separated by a single space, but some fields contain trailing spaces of varying lengths that shift the apparent column boundaries. The analyst must load the data reliably into the correct columns. Which approach is most appropriate?

A data analyst needs to perform stratified sampling on a customer database to ensure proportional representation across three regions: North (40%), South (30%), and West (30%). The total sample size required is 1,000. How many customers should be sampled from the North region?

A data analyst needs to create a recursive CTE to traverse a hierarchical employee-manager table. Which of the following is a key requirement for a recursive CTE?

A data analyst discovers that a dataset contains multiple records for the same customer with different spellings (e.g., 'Jon' vs 'John'). Which data preparation step should be applied first?

A data analyst is using SQL to extract data. The analyst wants to retrieve all records from a table named 'sales' where the 'amount' column is greater than 100. Which SQL clause should be used?

A marketing analyst must combine two datasets: a CRM extract with one row per customer and a transactions extract with many rows per customer. The analyst wants every customer from the CRM to appear in the output, even customers with no matching transactions. Which join type should be used?

A data analyst wants to assign a unique sequential integer to each row in a result set, starting at 1, based on the order of the 'sales_amount' column descending. Which window function should be used?

A data analyst needs to retrieve the top 5 most expensive products from a 'products' table sorted by price descending. Which TWO SQL clauses are required to achieve this? (Select TWO).

A data engineer is ingesting a 40 GB JSON event log into a columnar analytics platform. The file contains deeply nested arrays of user actions, and queries only ever filter on three top-level fields: event_id, event_type, and event_timestamp. The ingestion is currently slow and queries scan excessive data. Which preparation approach is MOST appropriate?

A retail company wants to analyze customer purchase patterns to identify products frequently bought together. Which data mining technique is most appropriate?

A data analyst is preparing to acquire clickstream data from a web analytics platform via its REST API. The API returns paginated results with a maximum of 500 records per page and issues a short-lived bearer token that expires after one hour. The analyst needs to backfill six months of event data reliably. Which acquisition design is most appropriate?

A data analyst needs to retrieve only unique job titles from the 'employees' table. Which SQL keyword should be used in the SELECT clause?

A retail analytics team needs to load a 40 GB CSV file of point-of-sale transactions into a cloud data warehouse nightly. The file is generated as a single object by an upstream system, and the team must minimize load time. Which approach best addresses the load performance bottleneck?

A financial analyst is preparing a dataset of stock transactions for a machine learning model. The 'transaction_amount' column has a highly skewed distribution with a few extremely large values. The analyst decides to apply a logarithmic transformation to this column. Which statement best describes the effect of this transformation?

Which TWO of the following are valid SQL clauses used to filter and sort data?

A data analyst is using Python pandas to perform exploratory data analysis. Which THREE methods are commonly used to assess data quality and distributions?

A data analyst is preparing a dataset for a machine learning model. The dataset contains a 'country' column with 150 unique values. To reduce dimensionality, the analyst wants to group less frequent countries into an 'Other' category. Which technique is being applied?

A data analyst at a retail company is profiling a newly acquired customer table. They observe that the 'last_purchase_date' column contains values such as '2023-13-45', '0000-00-00', and '2023-02-30'. Which data quality dimension is primarily violated?

Drag and drop the steps to perform a data backup using the 3-2-1 rule in the correct order.

A data analyst is profiling a new dataset and needs to assess data quality. Which two metrics are most appropriate for evaluating the completeness and consistency of the data? (Choose two.)

A data analyst wants to retrieve the top 5 highest-paid employees from the 'employees' table. Which SQL clauses could be used to achieve this? (Select TWO.)

A data analyst is profiling a new dataset containing customer information. When assessing data quality, which metric would be most appropriate to determine if the 'email' column contains valid email addresses?

A data analyst is using the IQR method to identify outliers in a dataset. The first quartile (Q1) is 25 and the third quartile (Q3) is 45. What is the upper bound for identifying outliers?

A data analyst uses the following query: SELECT department, AVG(salary) AS avg_salary FROM employees GROUP BY department HAVING AVG(salary) > 50000. What is the purpose of the HAVING clause in this query?

After merging two datasets, an analyst finds that the resulting dataset has many null values in some columns. Which TWO steps should the analyst take to address this? (Select two.)

A data analyst runs the following query: SELECT DISTINCT city FROM customers. What is the primary purpose of using the DISTINCT keyword in this query?

A retail company is integrating sales data from three regional databases into a central data warehouse. The 'product_id' column is defined as an integer in two databases but as a variable-length string in the third. During the ETL process, the analyst must ensure that product_id values are consistent for joining with the product dimension table. Which data transformation should the analyst perform?

A data analyst is tasked with collecting data from multiple spreadsheets provided by different departments. Each spreadsheet has different column names and formats. What is the best first step?

A data analyst uses a CTE to find employees who earn more than the average salary in their department. Which SQL clause is used to define the CTE?

An e-commerce company is acquiring product data from multiple supplier APIs. The APIs return JSON with inconsistent field naming conventions. Which data acquisition technique should be applied?

A dataset contains transaction amounts with a few extremely high values. The analyst wants to reduce the impact of these outliers on the average. Which measure of central tendency is most robust?

During exploratory data analysis, you calculate the IQR for a numeric column and find that several data points fall below Q1 - 1.5*IQR. These points are likely:

A data team is ingesting JSON event data from a mobile application into a columnar warehouse. Each event has a nested array of product objects, and analysts frequently need to report on individual products within those events. The team wants to avoid repeated manual parsing in every query. Which approach best prepares the data for efficient product-level analysis?

Which TWO are valid data acquisition methods? (Select two.)

You are using pandas in Python to clean a dataset. You notice several rows with missing values in the 'age' column. Which method would you use to remove those rows?

An analyst needs to compute a running total of sales for each department, ordered by date. Which window function is most appropriate?

A data analyst is performing data profiling on a customer table. Which metric provides the number of unique values in a column?

A data analyst is performing data acquisition from multiple source files. Which TWO data profiling tasks should the analyst complete before loading the data into the target system?

A small business wants to acquire customer feedback through a short questionnaire emailed after purchase. Which data acquisition method does this represent?

A data analyst is using a public API to collect historical weather data. The API has a rate limit of 100 requests per minute, but the analyst needs to retrieve 10,000 records as quickly as possible. What strategy should be used?

A data analyst is preparing a dataset for analysis and notices that the 'age' column has a significant number of missing values. The analyst decides to impute the missing values using the mean age. Which data preparation technique is being applied?

A data analyst is using a window function to assign a unique rank to each employee within their department based on salary, with ties receiving the same rank and leaving gaps. Which function should be used?

A company is merging two customer databases from different acquisitions. They need to identify duplicate records. Which data profiling technique is most effective?

A data analyst at a healthcare clinic is preparing a patient records dataset for analysis. The analyst discovers that the 'date_of_birth' column contains values stored as text strings in the format 'MM/DD/YYYY', but the analytics tool requires a date data type for age calculations. Which data transformation technique should the analyst apply?

A data engineer is designing an ETL pipeline to extract sales data from a legacy on-premise database and load it into a cloud data warehouse. The database is slow and queries during business hours affect performance. Which extraction strategy minimizes impact?

A data analyst is performing exploratory data analysis on a dataset containing house prices. They want to identify outliers in the 'price' column using the IQR method. The first quartile (Q1) is $200,000, the third quartile (Q3) is $350,000, and the IQR is $150,000. What is the upper bound for identifying outliers?

Based on the exhibit, what is the most likely cause of the import failure?

A data analyst is using pandas to clean a DataFrame that contains missing values in the 'age' and 'income' columns. Which THREE pandas methods are appropriate for handling missing data? (Select THREE).

An e-commerce company wants to analyze sales performance across product categories. The dataset includes transaction amounts and a column 'category' with values (Electronics, Clothing, Home). The analyst decides to use stratified sampling to ensure proportional representation. Which THREE steps are required to implement this? (Select THREE).