DA0-002 Data Concepts and Environments Practice Test 2 — 15 Questions

Question 1

A healthcare organization maintains a database of patient records. The database has a table 'patients' with columns: patient_id (primary key), first_name, last_name, date_of_birth, gender, and last_visit_date. A data analyst is tasked with creating a report that lists all patients who have not visited in the last two years. The analyst writes a query: SELECT * FROM patients WHERE last_visit_date < DATEADD(year, -2, GETDATE()); However, the query returns zero rows, even though the analyst knows there are patients who have not visited for over two years. Upon inspection, the analyst discovers that the last_visit_date column contains NULL values for patients who have never visited. Which modification to the query should the analyst make to include patients with NULL last_visit_date?

Choose an answer to begin — your selection is scored in the full session.

Accepted Answer

Add OR last_visit_date IS NULL to the WHERE clause.. The original query uses a WHERE clause that compares last_visit_date to a computed date, but NULL comparisons in SQL always yield UNKNOWN, so rows with NULL last_visit_date are excluded. Adding OR last_visit_date IS NULL explicitly includes those rows, ensuring patients who have never visited are listed in the report.

Answer

Remove the WHERE clause entirely.

Answer

Use COALESCE(last_visit_date, '1900-01-01') in the WHERE clause.

Answer

Add AND last_visit_date IS NOT NULL to the WHERE clause.

Question 2

A data analyst needs to ensure that a customer's address is stored in a consistent format across multiple databases. Which data quality dimension is the analyst primarily concerned with?

Accepted Answer

Consistency. The data analyst is primarily concerned with consistency, which ensures that the same data values are represented uniformly across different systems or databases. In this scenario, the customer's address must follow the same format (e.g., street, city, state, ZIP code) in every database to enable reliable merging and querying. Consistency is a key data quality dimension that focuses on cross-system uniformity, distinct from accuracy (correctness of values) or completeness (presence of all required fields).

Answer

Completeness

Answer

Accuracy

Answer

Timeliness

Question 3

A data engineer is designing a data warehouse for a retail company. The fact table must record each sale transaction, including product ID, store ID, date, and quantity sold. The product details (name, category, price) are stored in a separate table. This design is an example of which data modeling concept?

Accepted Answer

Star schema. This design is a classic star schema, where a central fact table (sales transactions) contains foreign keys to dimension tables (product, store, date). The fact table stores quantitative measures (quantity sold) and foreign keys, while dimension tables hold descriptive attributes (product name, category, price). This separation optimizes query performance for OLAP workloads by reducing joins and enabling straightforward aggregations.

Answer

Data lake

Answer

Normalization

Answer

Snowflake schema

Question 4

A data analyst is troubleshooting a report that shows unusually high sales for a specific product. Upon investigation, the analyst finds that the product was returned by several customers, but the returns were recorded in a separate system and not reflected in the sales data. Which data integration concept was likely missing?

Accepted Answer

Data reconciliation. The core issue is that the sales data and returns data are inconsistent because they were not cross-verified. Data reconciliation is the process of comparing datasets to ensure they are in agreement and identifying discrepancies, such as returns not being reflected in sales figures. Without reconciliation, the analyst would not detect that the high sales number is inflated by unrecorded returns.

Answer

ETL (Extract, Transform, Load)

Answer

Data profiling

Answer

Data governance

Question 5

When the analyst runs the query, it fails. What is the most likely reason?

Accepted Answer

The alias 'TotalValue' cannot be used in the WHERE clause.. The alias 'TotalValue' is defined in the SELECT clause but is referenced in the WHERE clause. In SQL, column aliases cannot be used in the WHERE clause because the WHERE clause is evaluated before the SELECT clause, so the alias does not yet exist at that point in the query execution order. This causes a syntax or 'unknown column' error.

Answer

The table name 'Products' is misspelled.

Answer

The data types of Price and Quantity are incompatible.

Answer

The expression 'Price * Quantity' is invalid in SQL.

Question 6

A data analyst is creating a report for a marketing campaign. The campaign data includes customer names, email addresses, and purchase history. Which of the following best describes the 'customer name' data type?

Accepted Answer

Nominal. Customer names are categorical labels that identify individuals without any inherent order or numerical value. This fits the definition of nominal data, which is used for naming or classifying variables. In data analysis, nominal data can be stored as strings and used for grouping or filtering, but arithmetic operations are meaningless.

Answer

Quantitative

Answer

Ordinal

Answer

Discrete

Question 7

Which TWO of the following are considered structured data?

Accepted Answer

A relational database table. A relational database table stores data in a predefined schema of rows and columns, where each column has a fixed data type. This rigid structure allows for efficient querying, indexing, and relational operations, making it a classic example of structured data.

Answer

A PDF report with free-form text

Answer

A JPEG image of a product

Answer

A JSON file with nested key-value pairs

Question 8

Which TWO of the following are examples of data governance best practices?

Accepted Answer

Defining data owners for each dataset. Defining data owners for each dataset is a core data governance practice that establishes accountability and responsibility for data assets. Data owners are typically senior stakeholders who ensure data is managed according to policies, including access controls and quality standards. This practice aligns with frameworks like DAMA-DMBOK, which emphasizes stewardship and ownership as foundational to governance.

Answer

Creating indexes on frequently queried columns

Answer

Using a data lake for storage

Answer

Encrypting all data at rest

Question 9

Which THREE of the following are characteristics of a relational database?

Accepted Answer

Enforces referential integrity through foreign keys. Relational databases enforce referential integrity through foreign keys, which ensure that relationships between tables remain consistent. A foreign key in a child table must match a primary key value in the parent table, preventing orphaned records and maintaining data integrity.

Answer

Stores data in key-value pairs

Answer

Supports NoSQL document storage

Question 10

Which THREE of the following are valid methods for handling missing data?

Accepted Answer

Using a placeholder like 'Unknown' for categorical data. Using a placeholder like 'Unknown' for categorical missing data preserves the dataset's structure and allows analysis to proceed without introducing statistical bias. This method is particularly valid for nominal data where the missing category can be treated as a distinct value, enabling downstream operations like one-hot encoding or frequency analysis without distorting the original distribution.

Answer

Ignoring missing values and proceeding with analysis

Answer

Sorting the data to bring missing values to the top

Question 11

A mid-sized e-commerce company stores customer data in a relational database. The database has a table named 'Customers' with columns: CustomerID (primary key), FirstName, LastName, Email, Phone, Address, City, State, ZipCode, and SignUpDate. The company is migrating to a new CRM system that requires a denormalized structure for performance reasons. The new system expects a single table 'CustomerDetails' with columns: CustomerID, FullName (concatenation of first and last name), ContactInfo (JSON object containing email, phone, and address), SignUpDate, and Region (derived from state). The data analyst must design an ETL process to transform the data. During a test run, the analyst notices that some records have missing Phone or Address values. Which of the following is the best approach to handle missing data in the ContactInfo JSON object?

Accepted Answer

Include the missing fields as null in the JSON object.. Representing missing fields as null in the JSON object preserves the data structure and allows downstream systems to explicitly handle null values. This approach maintains data integrity without discarding records or introducing ambiguous placeholder strings that could be misinterpreted as actual data.

Answer

Exclude any record with missing Phone or Address from the migration.

Answer

Set missing values to an empty string in the JSON object.

Answer

Replace missing values with 'N/A' string.

Question 12

Drag and drop the steps for the ETL (Extract, Transform, Load) process in the correct order.

Accepted Answer

Step 1: Extract, Step 2: Cleanse, Step 3: Transform, Step 4: Load, Step 5: Verify. ETL begins with extraction, followed by cleaning, transformation, loading, and verification.

Answer

Step 1: Extract, Step 2: Transform, Step 3: Cleanse, Step 4: Load, Step 5: Verify

Answer

Step 1: Extract, Step 2: Load, Step 3: Transform, Step 4: Cleanse, Step 5: Verify

Answer

Step 1: Cleanse, Step 2: Extract, Step 3: Transform, Step 4: Load, Step 5: Verify

Question 13

Drag and drop the steps to create a data visualization dashboard in the correct order.

Accepted Answer

Plan the dashboard, then Select appropriate charts, then Design the layout, then Build the dashboard, then Test the dashboard. Dashboard creation starts with planning, then chart selection, layout design, building, and testing.

Answer

Plan the dashboard, then Design the layout, then Select appropriate charts, then Build the dashboard, then Test the dashboard

Answer

Plan the dashboard, then Select appropriate charts, then Build the dashboard, then Design the layout, then Test the dashboard

Answer

Select appropriate charts, then Plan the dashboard, then Design the layout, then Build the dashboard, then Test the dashboard

Question 14

Match each data quality dimension to its description.

Accepted Answer

Accuracy: Data correctly reflects real-world values. The correct matches are: Accuracy - data correctly reflects real-world values; Completeness - all required data is present; Consistency - data values are the same across systems; Timeliness - data is available when needed. Common confusions include mixing timeliness with accuracy and consistency with completeness.

Answer

Accuracy: Data is available when needed

Answer

Completeness: Data values are the same across different systems

Question 15

Match each data visualization type to its best use case.

Accepted Answer

Bar chart: Compare quantities across categories. Correct matches: Bar chart compares categories, Line chart shows trends over time, Pie chart shows proportions. Common confusions: swapping time trend with bar chart or proportion with category comparison.

Answer

Bar chart: Show trends over time

Answer

Pie chart: Compare quantities across categories

Answer

Scatter plot: Show distribution of a single variable