CCNA Data Acquisition and Preparation Questions

The SQL query uses a HAVING clause with COUNT(*) > 5 to filter customers who placed more than 5 orders in 2023. The WHERE clause restricts records to the year 2023, and the GROUP BY customer_id aggregates orders per customer. The condition '> 5' explicitly excludes customers with exactly 5 or fewer orders, making option A correct.

The HAVING clause filters groups after aggregation, whereas WHERE filters rows before grouping.

ORDER BY salary DESC sorts from highest to lowest, and LIMIT 5 restricts to the first 5 rows.

Profiling a date column typically includes checking for null values and verifying the data type. Cardinality and pattern analysis are more relevant for categorical or string columns; variance is for numeric data.

The WITH clause introduces a CTE.

LAG returns the previous row's value in the ordered partition. For the first row (highest salary), there is no previous row, so it returns NULL.

Stratified sampling divides the population into strata (e.g., age groups) and samples proportionally from each stratum.

Anonymizing PII before storage is a fundamental data privacy requirement under regulations like GDPR and CCPA. When acquiring data via a public API, the company must ensure that personal identifiers (e.g., names, email addresses, IP addresses) are removed or obfuscated to prevent re-identification, reducing legal liability and ethical risk.

Fuzzy matching using Levenshtein distance (Option A) is correct because it measures the edit distance between two strings, allowing identification of duplicates even when there are minor typographical differences, such as 'Jon Smith' vs. 'John Smith'. This is essential for deduplicating customer records where names, addresses, or other fields may have slight variations without being exact matches.

The JSONPath expression `$.customers[*].email` uses the wildcard `[*]` to select all elements in the `customers` array and then accesses the `email` property of each element. This matches the requirement to extract email addresses for all customers from the JSON snippet.

A recursive CTE requires an anchor member (initial query), a recursive member (that references the CTE itself), and the UNION ALL operator to combine them. The WITH clause is the outer syntax, not part of the recursion itself.

The legacy system only supports flat file exports, meaning it cannot provide direct database or API access. SFTP (SSH File Transfer Protocol) is the most appropriate method because it securely transfers flat files over a network, aligning with the system's export capabilities while ensuring data integrity and encryption during transit.

Stratified sampling with proportional allocation: sample size per stratum = (stratum proportion) * total sample size. North = 0.40 * 1000 = 400.

A recursive CTE must have an anchor member (non-recursive) and a recursive member that references the CTE name, connected by UNION ALL.

The first step when dealing with inconsistent text values (like 'Jon' vs 'John') is to standardize the data using a lookup table or reference mapping. This ensures that all variations are normalized to a canonical form before any merging or deduplication is attempted, preventing data loss and preserving referential integrity.

The WHERE clause in SQL is used to filter records based on a specified condition, such as 'amount > 100'. It is applied directly to the rows in the 'sales' table before any grouping or ordering, making it the correct choice for retrieving only records where the amount exceeds 100.

ROW_NUMBER() assigns a unique sequential integer to each row within a partition, starting at 1.

ORDER BY DESC sorts prices descending, and LIMIT restricts to 5 rows. GROUP BY and HAVING are for aggregation; WHERE is for filtering but not needed here.

Using a window function with ROW_NUMBER() to rank orders per customer by date descending, then filtering for rank=1, gives the most recent order details. FIRST_VALUE() can also get the amount, but requires careful framing. GROUP BY with MAX(date) alone cannot get the corresponding amount.

Association rules (market basket analysis) discover co-occurrence relationships. Regression predicts numeric values, clustering groups similar items, classification assigns categories.

The DISTINCT keyword removes duplicate rows from the result set, returning only unique values.

The WHERE clause is used to filter rows based on specified conditions, while the ORDER BY clause sorts the result set in ascending or descending order. Both are standard SQL clauses for data filtering and sorting, respectively.

describe() gives summary statistics, info() shows data types and non-null counts, and value_counts() shows frequency distributions.

pandas provides to_csv, to_excel, and to_json for exporting data.

The 3-2-1 rule involves multiple copies, different media, offsite storage, and regular testing.

Streaming the file directly from SFTP into the warehouse using a data pipeline tool (e.g., Apache NiFi, Airbyte, or Fivetran) eliminates the intermediate staging step, reducing disk I/O and latency. This approach leverages incremental processing and parallel streams to handle large 10 GB files efficiently, while built-in retry and checkpoint mechanisms ensure reliability against network interruptions.

ORDER BY salary DESC sorts descending, and LIMIT/TOP/FETCH restricts rows.

Pattern analysis (e.g., using regular expressions) can validate whether strings match the expected format of an email address. Row counts, null counts, and cardinality do not validate format.

Upper bound = Q3 + 1.5 * IQR; IQR = Q3 - Q1 = 20; 1.5*20 = 30; 45+30 = 75.

HAVING filters groups after aggregation, similar to WHERE but for aggregated results.

Imputing nulls with the median is a standard technique for handling missing numerical data, especially when the distribution is skewed or contains outliers. The median is robust to extreme values and preserves the central tendency of the column, making it a safe choice for many analytical models. This approach avoids data loss while maintaining statistical integrity.

DISTINCT removes duplicate rows from the result set. In this query, it returns each unique city name only once.

Common Table Expressions (CTEs) are defined using the WITH keyword, followed by the CTE name and AS (query).

For a running total of sales for each product over time, you must group the calculation by product (PARTITION BY product_id) and order by sale_date (ORDER BY sale_date). The default window frame in SQL (RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) suffices for running totals. Option B provides both PARTITION BY product_id and ORDER BY sale_date, making it essential.

Data mapping and transformation is the correct technique because the JSON responses from different supplier APIs use inconsistent field naming conventions (e.g., 'product_id' vs. 'ProductID'). This technique defines a schema to map source fields to a standardized target format, ensuring data consistency before loading into the company's system. Without transformation, downstream processes like analytics or inventory management would fail due to mismatched field names.

Median is not affected by extreme values, while mean is sensitive.

Web scraping and API calls are direct methods to acquire data from external sources. Data profiling and data normalization are data preparation techniques, not acquisition. Data encryption is a security measure.

df.dropna() removes rows with any missing values by default. df.fillna() fills missing values, df.isna() returns a boolean mask, df.drop_duplicates() removes duplicate rows.

SUM() with OVER(PARTITION BY department ORDER BY date) computes a running total within each department.

Cardinality refers to the number of distinct values in a column.

Identifying missing values and verifying data types are fundamental data profiling tasks that help ensure data quality before acquisition. Building models, creating dashboards, or clustering are not part of profiling.

A survey is a structured data collection method where respondents answer predefined questions, typically via a form or questionnaire. In this scenario, the business is using a short questionnaire emailed after purchase to gather customer feedback, which directly aligns with the definition of a survey as a data acquisition method.

Paginating with appropriate delays respects the API's rate limit of 100 requests per minute while maximizing throughput. By splitting the 10,000 records into pages (e.g., 100 records per page) and sending requests at a rate just under the limit (e.g., one request every 0.6 seconds), the analyst can retrieve all data in approximately 100 minutes without triggering HTTP 429 rate-limit errors.

The error indicates that the server is configured for Windows authentication only, but the login attempt used SQL authentication. Enabling Mixed Mode authentication allows both Windows and SQL authentication, resolving the issue.

RANK() assigns the same rank to ties and leaves gaps (e.g., 1,1,3). DENSE_RANK() does not leave gaps.

Fuzzy matching on name and address is the most effective technique because customer databases from different acquisitions often contain variations in spelling, formatting, and abbreviations (e.g., 'Bob' vs. 'Robert', 'St.' vs. 'Street'). Exact matching would miss these duplicates, while fuzzy matching uses algorithms like Levenshtein distance or Jaro-Winkler to quantify similarity and identify near-matches, ensuring comprehensive deduplication.

Incremental extraction using Change Data Capture (CDC) minimizes impact on the legacy on-premise database by reading only the changed rows (inserts, updates, deletes) from transaction logs or change tables, rather than issuing heavy SELECT queries. This avoids full table scans or frequent queries during business hours, preserving database performance for operational workloads.

The IQR method defines outliers as values below Q1 - 1.5*IQR or above Q3 + 1.5*IQR. Upper bound = 350,000 + (1.5 * 150,000) = 350,000 + 225,000 = $575,000.

Both C and D correctly implement the filter. SQL's operator precedence gives AND higher priority than OR, so option C is parsed as (quarter = 'Q1' AND amount > 100) OR (quarter = 'Q3' AND amount > 100), which is equivalent to (quarter = 'Q1' OR quarter = 'Q3') AND amount > 100. Option D expresses the same condition directly with parentheses.

The exhibit shows a date field containing '2023-02-30', which is an invalid date (February never has 30 days). This data quality issue causes the import to fail, as the system likely validates date values against calendar rules before inserting them into the target table. The error is not due to file emptiness, non-numeric characters, or corruption, but specifically a semantic data integrity violation.

Common pandas methods for missing data include dropna (remove rows with NaN), fillna (replace NaN with a value), and apply with a custom function. Merge is for combining DataFrames; pivot_table is for reshaping.

Stratified sampling requires dividing the population into strata (categories), then randomly sampling from each stratum in proportion to its size. Combining strata or simple random sampling without stratification would not achieve proportional representation.