DA0-001 Sample Questions & Answers

The mean (average) provides a measure of central tendency, giving the analyst a quick understanding of the typical value in a numerical column. Calculating the mean and standard deviation are fundamental first steps in profiling numerical data to understand its distribution and spread.

The standard deviation measures the amount of variation or dispersion of a set of values. A low standard deviation indicates that the values tend to be close to the mean, while a high standard deviation indicates that the values are spread out over a wider range. This is crucial for understanding data consistency and identifying potential outliers.

The primary reason for a data warehouse is to separate analytical workloads (OLAP) from transactional workloads (OLTP). Running complex, long-running analytical queries directly against an OLTP database can lock tables and severely degrade the performance of the primary business application. The data warehouse is structured specifically for analysis and reporting, ensuring the operational system remains responsive.

The GROUP BY clause is used to group rows that have the same values in specified columns into summary rows. It is almost always used with aggregate functions like COUNT(), MAX(), MIN(), SUM(), AVG() to perform a calculation on each group. For example, GROUP BY category would allow you to calculate the SUM(sales) for each product category.

Data standardization is the process of transforming data into a common format. In this case, converting various phone number formats into a single, consistent one (e.g., 5551234567) is a classic example of standardization. This is a critical step in data cleansing to ensure data quality and enable accurate matching, grouping, and analysis.

The use of '0' as a placeholder for missing age data is an example of invalid data entry. While technically a number, '0' is not a valid age for a customer and acts as a hidden NULL value. Including these invalid entries in a calculation for the average will skew the result, making it artificially low. This is a common data quality problem that must be addressed by filtering out or imputing these values.

To find the top 5 most profitable customers, the analyst must first calculate the profit for each customer (likely using SUM and GROUP BY), then sort the results in descending order of profit using ORDER BY profit DESC. Finally, the LIMIT 5 clause restricts the output to only the top 5 rows from the sorted result set.

Association analysis, also known as market basket analysis, is a technique used to discover relationships between variables in large datasets. Its primary application is to find items that are frequently purchased together. The output is a set of association rules, such as '{Diapers} -> {Beer}', which can be used for product placement, promotions, and recommendation engines.

When calculating a total sum, replacing missing (NULL) values with zero is the most appropriate action. This ensures that the record is included in the dataset without artificially inflating or deflating the total. Replacing with the mean would incorrectly add value, and deleting the row would exclude other potentially useful data in that record from other analyses. Most SQL SUM functions automatically treat NULLs as zero, but explicitly replacing them is a safe and clear data cleansing practice.

To find the median, you must first sort the dataset in ascending order: [12, 18, 23, 23, 31, 45, 50]. The median is the middle value. In this dataset of 7 numbers, the middle value is the 4th one, which is 23. If there were an even number of values, the median would be the average of the two middle numbers.

A scorecard, also known as a KPI card or metric visual, is specifically designed to display a single, important number in a large, prominent format. This allows executives to quickly absorb the most critical business metrics without needing to interpret a complex chart. They often include secondary information like a comparison to a target or a previous period.