DA0-002 Practice Questions

The issue described is that a report is showing a correctly formatted but incomplete dataset, specifically filtered to the current month. This is a classic symptom of a logical error in the data retrieval query. The SQL WHERE clause is used to filter records based on specified conditions. An incorrect condition, such as WHERE saledate >= DATETRUNC('month', CURRENTDATE), would intentionally and precisely limit the result set to only the current month's sales, causing the observed problem. The other options describe different types of failures that would manifest with more severe and obvious errors.

A. Lack of permissions: This would typically result in an "access denied" error or a completely empty report, not a report showing a filtered subset of the data.

C. Report refresh failure: This would cause the report to display stale or outdated data from the last successful refresh, not a dynamically updated view of only the current month's data.

D. Connectivity issues: This would prevent the report from loading entirely or would produce a specific connection error message, as the reporting tool could not reach the data source.

1. Microsoft SQL Server Documentation

"WHERE (Transact-SQL)". Microsoft Corporation. This document details the function of the WHERE clause

stating its purpose is to "specify the search condition for the rows returned by the query." An error in this search condition is a direct cause of incorrect data filtering. (Refer to the introductory paragraph on the function of the WHERE clause).

2. Silberschatz

A.

Korth

H. F.

& Sudarshan

S. (2020). Database System Concepts (7th ed.). McGraw-Hill. In Chapter 3

"Introduction to SQL

" the text explains that the where clause provides a condition that must be met by tuples (rows) to be included in the result. A logical error in this condition

especially when using date functions

is a common source of bugs leading to incorrectly scoped reports. (See Section 3.3

"The Where Clause").

3. Interlandi

M.

et al. (2020). What Went Wrong: A Large-Scale Study of Bugs in Real-World Data-Intensive Pipelines. Proceedings of the VLDB Endowment

13(11)

2121–2134. This peer-reviewed study analyzes bugs in data pipelines and identifies "incorrect logic" in data processing steps (which includes SQL queries) as a significant category of errors. An incorrect date filter falls directly into this category of logical bugs. (See Section 4.2

"Bug Categorization

" which discusses logic errors). https://doi.org/10.14778/3407790.3407811

The AND logical operator is used to combine multiple conditions in a query, such as in a WHERE or HAVING clause. It functions as a logical conjunction, meaning it returns a true result only if all of the individual conditions it connects are true. If any single condition is false, the entire expression evaluates to false. This makes AND the correct choice for ensuring every specified condition is met before a record is included in the result set.

A. OR: The OR operator returns true if any of the connected conditions are met, not necessarily all of them.

B. NOT: The NOT operator is a unary operator used to negate or invert the result of a single condition.

D. IF: The IF function (or CASE statement in SQL) is used for conditional flow control to return different values based on a condition.

---

1. Silberschatz

A.

Korth

H. F.

& Sudarshan

S. (2019). Database System Concepts (7th ed.). McGraw-Hill. In Chapter 3

"Introduction to SQL

" Section 3.2.3

"The Where Clause

" the text explains that the AND logical connective allows for the combination of multiple predicates

where the overall predicate is true only if all the combined predicates are true.

2. PostgreSQL 16 Documentation. (2023). Chapter 9: Functions and Operators

Section 9.2: Logical Operators. The official documentation states

"The ordinary logical operators are available: AND

OR

NOT." The truth table provided for AND demonstrates that the result is true only when both boolean inputs are true.

3. Stanford University. (n.d.). CS145: Introduction to Databases

Lecture 2: SQL. Retrieved from https://web.stanford.edu/class/cs145/slides/02-sql1.pdf. Slide 20 ("Conditions in WHERE Clause") explicitly shows the use of AND to combine multiple conditions

stating that the result includes tuples satisfying all specified conditions.

The interquartile range (IQR) is the statistical measure that represents the middle 50% of a dataset. It is calculated by subtracting the first quartile (Q1), or the 25th percentile, from the third quartile (Q3), or the 75th percentile. The range between Q1 and Q3 contains the central half of the ordered data, providing a robust measure of statistical dispersion that is not affected by outliers. This is precisely what the analyst's report identifying the middle 50% of the data describes.

B. The difference between mode and median is a measure of a distribution's skewness, not a range representing the central 50% of data points.

C. Mean variance quantifies the average squared difference from the mean, measuring the overall spread of the data, not specifically the middle 50%.

D. Skewness describes the asymmetry of a data distribution relative to its mean, not the range of the central portion of the data.

---

1. Freund

J. E.

& Perles

B. M. (2007). Modern Elementary Statistics (12th ed.). Pearson Prentice Hall. In Chapter 3

"Summarizing Data: Measures of Dispersion

" the interquartile range is defined as "the length of the interval that contains the middle 50 percent of the data" (p. 79).

2. Illowsky

B.

& Dean

S. (2018). Introductory Statistics. OpenStax

Rice University. In Chapter 2

"Descriptive Statistics

" Section 2.5

"Measures of the Center of the Data

" it is stated: "The interquartile range is the range of the middle 50 percent of the data values; the IQR is found by subtracting the first quartile from the third quartile" (p. 93).

3. MIT OpenCourseWare. (2014). 18.05 Introduction to Probability and Statistics

Spring 2014. Reading 1: Introduction to Statistics. Section 6.2

"Quartiles and the Interquartile Range (IQR)

" defines the IQR as Q3 - Q1 and explains that "The interval [Q1

Q3] contains the middle 50% of the data."

User Acceptance Testing (UAT) is the final phase in the software testing process. It is performed by the end-users or client representatives to validate that the software system meets the agreed-upon business requirements and is fit for its intended purpose from a user's perspective. The primary goal of UAT is to gain confidence and formal acceptance from the user that the system is ready for operational use. This involves testing the software in a real-world or simulated operational environment to ensure it can handle the required tasks and workflows.

A. UAT is a final validation stage that occurs before deployment, not at the beginning of the development process.

B. Ensuring different parts of an application work together is the purpose of Integration Testing, not UAT.

C. Reviewing crashes and deploying patches is part of the post-deployment maintenance and support phase, which happens after UAT.

1. ISO/IEC/IEEE 29119-1:2013

Software and systems engineering — Software testing — Part 1: Concepts and definitions. Section 4.2

"Test levels

" defines acceptance testing as a test level focused on determining whether to accept the system

often performed by customers or users based on their requirements.

2. Bourque

P.

& Fairley

R. E. (Eds.). (2014). Guide to the Software Engineering Body of Knowledge (SWEBOK)

Version 3.0. IEEE Computer Society. In Chapter 4

"Software Testing

" Section 2.1.3

"Acceptance Testing

" it is stated that this testing determines if a system satisfies its acceptance criteria

enabling the customer to determine whether to accept the system.

3. University of Toronto

Department of Computer Science. (2021). CSC301: Introduction to Software Engineering - Lecture 10: Software Testing. The course material describes User Acceptance Testing (UAT) as the process where clients/users test to confirm the system meets their needs

serving as a final verification of required business functions.

This scenario is a classic example of data drift. Data drift occurs when the statistical properties of the production data, on which the model makes predictions, change or "drift" away from the data the model was originally trained on. The viral event caused a sudden, significant shift in the distribution of sales demand, making the historical training data no longer representative of the current reality. Consequently, the forecasting model's performance degrades because the patterns it learned are now obsolete.

A. Model bias: This refers to inherent, systematic errors in the model's predictions due to flawed assumptions, not a change in data distribution over time.

C. Incorrect sizing: This describes a potential consequence of the inaccurate forecast (e.g., insufficient inventory), not the underlying statistical problem with the model's data.

D. Skewing: This is a measure of a distribution's asymmetry. While the new data may be skewed, "data drift" is the specific term for the phenomenon of the data distribution changing over time.

---

1. Microsoft Azure Official Documentation. In the "What is data drift?" section for Azure Machine Learning

it states: "Data drift is one of the top two reasons for model accuracy degradation. It occurs in most machine learning applications... Data drift is the change in model input data that leads to model performance degradation." This directly aligns with the scenario where a change in demand patterns (input) causes the sales forecasting model to fail.

Source: Microsoft Corporation. (2023). Detect data drift (preview) on datasets. Azure Machine Learning Documentation. Section: "What is data drift?".

2. Google Cloud Official Documentation. The documentation for Vertex AI Model Monitoring defines data drift (also called feature skew) as occurring when the statistical properties of input features change over time. It distinguishes between training-serving skew and prediction drift. The scenario described is a form of prediction drift

where feature data distributions change between training and serving.

Source: Google Cloud. (2023). Introduction to Vertex AI Model Monitoring. Vertex AI Documentation. Section: "Data drift and concept drift".

3. Academic Publication. A foundational survey on the topic defines the problem: "In a changing environment

the data distribution may change over time

which is known as concept drift... This change may hurt the performance of a model trained on old data." The viral event represents a sudden change in the environment

causing the data distribution to shift.

Source: Gama

J.

Žliobaitė

I.

Bifet

A.

Pechenizkiy

M.

& Bouchachia

A. (2014). A survey on concept drift adaptation. ACM Computing Surveys (CSUR)

46(4)

1-37. Section 2: "Concept Drift". DOI: https://doi.org/10.1145/2523813

4. University Courseware. Stanford University's course on Machine Learning Systems Design explicitly covers this issue. Lecture notes describe data drift as a change in the input data distribution over time

which is a common reason for performance degradation in production ML systems.

Source: Stanford University. (2021). CS 329S: Machine Learning Systems Design

Lecture 4: Data. Course materials. Section: "Data Distribution Shifts".

The objective is to segment customers based on a continuous numerical variable, Amountspent. Binning, also known as discretization or bucketing, is the data preprocessing technique used to convert continuous data into a finite number of categorical intervals or "bins." By applying binning to the Amountspent column, the team can group customers into distinct segments such as 'low spenders,' 'medium spenders,' and 'high spenders.' This directly accomplishes the task of segmenting customers based on their spending behavior for further analysis.

A. Standardization: This rescales numerical data to have a mean of 0 and a standard deviation of 1, which is useful for certain machine learning algorithms but does not create customer segments.

B. Concatenate: This operation joins text strings or data structures (like tables) together; it is not used for grouping numerical values into categories.

D. Appending: This process adds new rows of data to an existing dataset, which is unrelated to segmenting the current customer data.

---

1. Pandas Development Team. (2023). pandas.cut. In pandas 2.1.1 documentation. This official documentation describes the cut function

which is used for binning. It states

"Bin values into discrete intervals. Use cut when you need to segment and sort data values into bins." This directly aligns with the requirement to segment customers.

Source: https://pandas.pydata.org/docs/reference/api/pandas.cut.html

2. DeNero

J.

& Gonzalez

J. (2023). Data 100: Principles and Techniques of Data Science

Lecture 12: Feature Engineering. University of California

Berkeley. The course material explains feature engineering techniques. In the section on "Discretization (Binning)

" it describes the process of converting a quantitative feature into a set of binary features (one for each bin)

which is precisely the technique needed to create spending segments.

Source: https://ds100.org/su23/lecture/lec12/

3. Han

J.

Kamber

M.

& Pei

J. (2012). Data Mining: Concepts and Techniques (3rd ed.). Morgan Kaufmann. In Chapter 3

"Data Preprocessing

" Section 3.5.2

"Discretization and concept hierarchy generation for numeric data

" the authors detail binning as a primary method for data discretization. They explain that binning "is a top-down splitting

unsupervised discretization technique" used to partition a continuous-valued attribute into a series of intervals.

Source: Chapter 3

Section 3.5.2

pp. 113-114.

In standard SQL, NULL is a special marker indicating that a data value does not exist. It is not equivalent to zero or an empty string. Standard comparison operators like = cannot be used to test for NULL because the result of any arithmetic comparison with NULL is also NULL (or unknown), which evaluates to false in a WHERE clause. The correct, standard-compliant operator to specifically test for the absence of a value is IS NULL. This syntax correctly identifies and filters for all records where the specified column is empty.

A. WHERE [ColumnName] = NULL is incorrect because the = operator cannot be used to test for NULL values; this expression will not return any rows.

C. WHERE [ColumnName] IS NOT NULL is the opposite of the required logic; it returns records where the field has a value, not where it is empty.

D. WHERE [ColumnName] = 'NULL' searches for the literal text string 'NULL', not for fields that are actually empty (i.e., have the NULL state).

1. PostgreSQL 16 Documentation. (2023). 9.2. Comparison Functions and Operators. In the section detailing comparison operators

it states

"Do not write expression = NULL because NULL is not 'equal to' NULL... To test for nullness

use the constructs IS NULL and IS NOT NULL." Retrieved from postgresql.org/docs/16/functions-comparison.html.

2. Microsoft Transact-SQL (T-SQL) Documentation. (2023). IS NULL (Transact-SQL). Microsoft Learn. This official documentation specifies

"To determine whether an expression is NULL

use IS NULL or IS NOT NULL instead of comparison operators (such as = or !=)." Retrieved from learn.microsoft.com/en-us/sql/t-sql/queries/is-null-transact-sql.

3. Ullman

J. D.

& Widom

J. (n.d.). CS145 Introduction to Databases

Lecture 4: SQL 1. Stanford University. In the lecture slides covering SQL basics

slide 28 ("A Subtlety About NULLs") explicitly states: "Test for NULL using IS NULL and IS NOT NULL

not = or ." Retrieved from web.stanford.edu/class/cs145/slides/04-sql1.pdf.

The most efficient and standard method for this task is to work with structured data formats that Python's data analysis libraries, like pandas, are optimized for. Web APIs commonly return data in JSON (JavaScript Object Notation) format. This structured format can be directly and easily parsed into a pandas DataFrame, preserving data types and relationships. Similarly, data queried from a database can be loaded directly into a DataFrame. Once both data sources are represented as DataFrames, pandas provides powerful and optimized merge or join functions to combine them based on common keys. This approach minimizes intermediate conversion steps and avoids the loss of data type information.

A. varchar is a database-specific data type, not a universal data interchange format. Converting all data to this string-like type would unnecessarily discard valuable numeric and date-type information.

C. Converting structured API and database data to a flat TXT file is an inefficient intermediate step that adds complexity and risks data integrity issues during parsing.

D. Converting all data to a generic string format is a poor practice as it loses essential data type information (e.g., integers, floats), making subsequent numerical analysis and joins difficult.

1. Pandas Official Documentation

IO Tools (JSON): The documentation explicitly details the pandas.readjson function for converting a JSON string directly into a pandas DataFrame. This is the standard method for handling API data.

Source: Pandas Development Team. (2023). pandas-docs: IO Tools (Text

CSV

HDF5

…). Section: JSON. Retrieved from https://pandas.pydata.org/pandas-docs/stable/userguide/io.html#json

2. Pandas Official Documentation

IO Tools (SQL): The documentation shows the pandas.readsql function

which is designed to read the result of a SQL query directly into a DataFrame

preserving data types from the database where possible.

Source: Pandas Development Team. (2023). pandas-docs: IO Tools (Text

CSV

HDF5

…). Section: SQL queries. Retrieved from https://pandas.pydata.org/pandas-docs/stable/userguide/io.html#sql-queries

3. University of Illinois Urbana-Champaign

Courseware: In the "Data Visualization" course materials

the standard workflow for handling web data is demonstrated. It involves making an API request

parsing the resulting JSON response

and loading it into a data structure like a pandas DataFrame for analysis.

Source: University of Illinois Urbana-Champaign. (2022). CS 307: Working with APIs and JSON. Week 3 Notes. Retrieved from https://uiuc-ischool-dataviz.github.io/spring2022/week03/workingwithapisandjson.html (Specifically

the section on parsing JSON and creating DataFrames).

The most appropriate delivery method is a high-level email because the target audience is a Chief Financial Officer (CFO). C-level executives are primarily concerned with strategic insights and key performance indicators (KPIs) rather than granular operational details. A high-level email can concisely summarize the week's sales performance, highlight significant trends, and provide the "bottom line" information needed for strategic decision-making, respecting the executive's limited time. This format is direct, efficient, and focuses on insights over raw data.

A. A granular daily report in a dashboard: This is too detailed and frequent for a CFO. This level of granularity is better suited for an operational sales manager who tracks daily activities.

B. A detailed text document: This format is inefficient for conveying quantitative data and trends. A CFO needs to quickly absorb key financial metrics, which are better presented visually or as summarized figures.

C. A spreadsheet with raw data: Providing raw data is inappropriate as it shifts the burden of analysis from the analyst to the executive. The CFO expects synthesized information and insights, not unprocessed numbers.

---

1. Microsoft Power BI Documentation. In the guide for creating reports

a key principle is to consider the audience. For an executive audience

the guidance emphasizes creating a "summary" or "high-level" view that highlights key metrics. A granular view is reserved for analysts or managers who need to explore the data.

Source: Microsoft Docs

"Tips for designing a great report in Power BI

" Section: "Consider your audience."

2. University Courseware. Business and data analytics programs consistently teach that communication must be tailored to the audience. Course materials from institutions like MIT Sloan emphasize that reporting for senior leadership should be concise

focused on strategic implications

and summarize key findings

contrasting with the detailed

operational reports for middle management.

Source: MIT Sloan Management

Course 15.S08: "Data-Driven Decision Making

" Module: "Communicating Data-Driven Insights." The course stresses the "Pyramid Principle

" where the main conclusion for an executive is presented first.

3. Peer-reviewed Academic Publications. Foundational research in Management Information Systems (MIS) establishes that the "quality" of information is a critical factor for its usefulness in decision-making. Quality includes relevance

timeliness

and appropriate aggregation level for the user. For a strategic user like a CFO

highly aggregated

summarized information is of higher quality than raw

detailed data.

Source: DeLone

W. H.

& McLean

E. R. (2003). The DeLone and McLean Model of Information Systems Success: A Ten-Year Update. Journal of Management Information Systems

19(4)

9–30. (Discusses "Information Quality" as a key dimension

which implies tailoring content and format to the user). DOI: https://doi.org/10.1080/07421222.2003.11045748

The provided SQL query fails because it attempts to use an aggregate function, SUM(income), alongside non-aggregated columns, firstName and lastName, without specifying how to group the data. According to standard SQL rules, when a SELECT list contains both aggregate functions and regular column names, all non-aggregated columns must be included in a GROUP BY clause. This clause tells the database how to group the rows before applying the aggregate function. Adding GROUP BY firstName, lastName resolves this fundamental error by instructing the database to calculate the sum of income for each unique combination of first and last names.

A. Adding a HAVING clause is used for filtering after aggregation, but it does not resolve the primary error of the missing GROUP BY clause.

C. While ORDER BY is the correct syntax for sorting (not SORT BY), this change alone does not fix the more critical aggregation error.

D. Simply removing the sorting clause does not address the core requirement of a GROUP BY clause when mixing aggregate and non-aggregate columns.

---

1. PostgreSQL 16 Documentation

Section 7.2.4. Aggregate Functions: "In an aggregate query

the SELECT list can only contain aggregate function calls and grouping columns... The reason for this restriction is that otherwise

there would be no way to decide which value of the non-aggregated column to return." This directly explains why the original query is invalid and why a GROUP BY clause is necessary.

2. Stanford University

CS 145 Introduction to Databases

Lecture Notes on "SQL - Aggregation and Grouping": The notes state the following rule: "If SELECT uses aggregation

then any attribute in SELECT list that is not aggregated must be in GROUP BY list." This academic source confirms the fundamental SQL principle violated by the original query.

3. Microsoft SQL Server Documentation

"GROUP BY (Transact-SQL)": "A SELECT statement clause that divides the query result into groups of rows... When a GROUP BY clause is used

each group has a single row that summarizes it

and the SELECT list can only contain... expressions from the GROUP BY clause [and] aggregate functions." This official vendor documentation reinforces the requirement for non-aggregated columns in the SELECT list to be present in the GROUP BY clause.