Data Acquisition and Preparation

A retail analyst receives a flat file containing individual point-of-sale transactions, with one row per item sold including store ID, transaction timestamp, and sale amount. The finance team has requested a report showing total revenue per store per day. Which transformation should the analyst apply to prepare the data for this report?

An analyst is preparing a customer contact list. The source table stores names in two separate columns, FirstName and LastName, but the marketing team's email tool requires a single FullName field formatted as 'LastName, FirstName'. Which transformation should the analyst apply to produce the required field?

A data analyst needs to pull data directly from a company's on-premises Microsoft SQL Server database into a desktop analytics tool for a recurring report. The tool must maintain a live connection so refreshes reflect current data, and IT has asked that no data be exported to intermediate flat files. Which acquisition method best meets these requirements?

An analyst receives three monthly sales exports from different regional systems: one CSV, one tab-delimited TXT, and one Excel workbook. All three contain the same columns in the same order (Date, Region, Product, Amount) but were generated by separate teams. The analyst needs to combine all rows into a single dataset for a company-wide report. Which transformation approach is most appropriate?

An analyst writes a SQL query to combine a Customers table (500 rows) with an Orders table (2,000 rows), expecting roughly 2,000 result rows. Instead, the query returns 1,000,000 rows and runs extremely slowly. Reviewing the query, the analyst notices there is no ON condition linking the two tables. What most likely happened?

A data analyst imports a comma-delimited CSV file into a reporting tool, but the address column—which contains values like "123 Main St, Suite 400, Denver"—is split across multiple fields, misaligning every subsequent column. The source data itself is correct. What is the BEST way to resolve this parsing issue?

An analyst is preparing a global sales dataset for reporting. The 'amount' column contains transaction values recorded in different currencies, and a separate 'currency_code' column identifies each one (USD, EUR, GBP, etc.). Management requires all figures expressed in USD for accurate comparison and aggregation. Which transformation step best addresses this requirement?

A data analyst is preparing a customer contact list merged from two CRM exports. The same customer sometimes appears twice: once with an email of 'JSmith@Corp.com' and once as 'jsmith@corp.com', but with identical phone numbers and mailing addresses. A simple deduplication on the exact email string leaves both records in the dataset. What is the MOST effective approach to identify and remove these duplicate records?

A data analyst pulls order data from a REST API. Each JSON object represents one order and contains a nested array called 'lineItems', where each element holds a product ID, quantity, and price. The analyst needs to load this into a relational table where each row represents a single product within an order. What transformation must the analyst perform?

An analyst is merging two customer lists: one from the online store's database and one from the in-store loyalty program. Some customers appear in both lists, some only shop online, and some only shop in-store. The analyst needs a single combined dataset that retains every customer from BOTH sources, matching records where a shared customer ID exists. Which join type should the analyst use?

An analyst is preparing a customer survey dataset of 10,000 records for a satisfaction analysis. The 'annual_income' column is missing in about 4% of records, and these missing values appear randomly across all customer segments. The income figures are roughly symmetric with no extreme outliers. Management requires that the full sample size be preserved for statistical power. What is the most appropriate way to handle the missing income values?

A data analyst is preparing a dataset of monthly customer transaction amounts for a machine learning model. Profiling reveals a small number of legitimate but extremely large B2B purchases that inflate the mean and skew the distribution. The business confirms these transactions are valid and must remain represented in the analysis, but their extreme magnitude distorts the model's sensitivity to typical retail purchases. Which technique best addresses the outliers while satisfying the business requirement?

A data analyst maintains a reporting table that pulls from a 50-million-row source transaction system. The nightly ETL job now takes over six hours because it reloads the entire table each run. Business users only need newly added or modified transactions reflected, and the source table includes a reliable 'last_modified' timestamp column. Which extraction approach should the analyst adopt to reduce load time while keeping the reporting table accurate?

An analyst is combining a Customers table (12,000 rows) with an Orders table so that a report can show total revenue per customer, including customers who have not yet placed any orders. When joining the two tables in SQL, which join type should the analyst use to ensure that all customers appear in the result set even when no matching order exists?

A data analyst is preparing a dataset for a clustering algorithm. One column, 'annual_income', ranges from 20,000 to 250,000, while another column, 'age', ranges from 18 to 85. The analyst is concerned that the income variable will dominate the distance calculations because of its much larger magnitude. Which transformation should the analyst apply to rescale both columns to a common range of 0 to 1?

A data analyst is writing a script to pull customer order records from a REST API. The API returns a maximum of 100 records per response and includes a 'next_page_token' field in each response body. It also returns an HTTP 429 status code when too many requests are sent in a short window. Which approach should the analyst implement to reliably acquire the complete dataset?

A data analyst imports a customer file where a single column named 'Location' contains values like 'Austin, TX, 78701'. The reporting requirements specify that city, state, and ZIP code must each be filterable independently in the dashboard. Which data preparation technique should the analyst apply to meet this requirement?

An analyst receives a sales table in 'long' format where each row contains a StoreID, a Month column (values like 'Jan', 'Feb', 'Mar'), and a Revenue value. The finance team wants a report where each store appears on a single row with separate columns for each month's revenue. Which data transformation should the analyst apply?

An analyst receives a new 500,000-row customer file from a recently acquired company and must integrate it into the corporate CRM. Before writing any cleaning transformations, the analyst wants to understand the completeness, value distributions, data types, and frequency of distinct values in each column. Which activity should the analyst perform to gather this information?

An analyst is profiling a customer dataset and notices the 'State' column contains values such as 'CA', 'Calif.', 'California', and 'ca'. When they attempt to group total sales by state, the same state is split across multiple groups, inflating the count of distinct states. What data preparation task should the analyst perform to resolve this issue?