DA0-002 Data Concepts and Environments — 25 Questions

Question 1

A data analyst is reviewing the error log from a nightly batch load. What is the most likely cause of the error?

Accepted Answer

A row with the same primary key was already loaded in a previous batch.. In nightly batch loads, data is typically inserted into a target table. If a row with a primary key that already exists is attempted to be inserted, the database will raise a primary key constraint violation error. This is the most likely cause of such errors because batch loads often assume unique data but duplicates can occur when previous loads have already inserted those rows.

Answer

The data type of order_id is incorrect.

Answer

The source and target schemas are mismatched.

Answer

The order_id field contains null values.

Question 2

A data analyst needs to combine customer information from a CRM table and order information from an orders table, returning only customers who have placed at least one order. Which type of join should the analyst use?

Accepted Answer

INNER JOIN. An INNER JOIN between the CRM table and the orders table returns only rows where there is a match in both tables based on the join key (e.g., customer ID). This satisfies the requirement to return only customers who have placed at least one order, because any customer without an order in the orders table will be excluded from the result set.

Answer

RIGHT JOIN

Answer

FULL OUTER JOIN

Answer

LEFT JOIN

Question 3

A company has a large data warehouse running on Snowflake. They receive daily CSV files from multiple sources and load them directly into the warehouse, then run SQL transformations to clean and aggregate the data. Which data integration approach does this describe?

Accepted Answer

ELT. This describes ELT (Extract, Load, Transform) because the raw CSV files are first loaded directly into Snowflake, and then SQL transformations are applied within the warehouse. Unlike ETL, where data is transformed before loading, ELT leverages Snowflake's compute power to perform transformations after ingestion, which is efficient for large-scale batch processing.

Answer

Data streaming

Answer

ETL

Answer

CDC

Question 4

A retail company stores customer purchase history in a relational database. The database contains a table 'transactions' with columns: transaction_id, customer_id, product_id, quantity, price, and transaction_date. A data analyst needs to create a report that shows total revenue per customer for the last quarter. Which data concept describes the relationship between customer_id and total revenue?

Accepted Answer

Derived attribute. Total revenue is calculated by summing (quantity * price) for each customer, making it a derived attribute because it is computed from existing stored data (quantity and price) rather than stored directly. In the context of the 'transactions' table, customer_id is a stored key, but total_revenue is not stored; it is derived via aggregation, which matches the definition of a derived attribute in database design.

Answer

Foreign key

Answer

Composite attribute

Answer

Atomic attribute

Question 5

A healthcare database stores patient records. Each patient has a unique patient_id, and the database includes a table 'visits' with visit_id, patient_id, visit_date, and diagnosis_code. To ensure data integrity, which constraint should be applied to the patient_id column in the 'visits' table?

Accepted Answer

Foreign key. A foreign key constraint ensures that patient_id in visits references a valid patient_id in the patient table. Option A is wrong because a unique constraint prevents duplicate values but allows one NULL, and does not enforce referential integrity. Option C is wrong because a primary key ensures uniqueness and serves as the table's identifier, but it does not enforce relationships between tables. Option D is wrong because a check constraint validates values based on a condition, not referential integrity.

Answer

Unique constraint

Answer

Primary key

Answer

Check constraint

Question 6

A data engineer is designing a data warehouse for a multinational corporation. The company has sales data from different regions with varying currencies and date formats. To ensure consistency, which data concept should be applied to standardize the data before loading into the warehouse?

Accepted Answer

Data transformation. Data transformation is the correct concept because it involves converting data from source formats (e.g., different currencies and date formats) into a consistent, standardized format before loading into the data warehouse. This process includes applying conversion rules, such as using ISO 8601 for dates and a single base currency (e.g., USD) with exchange rate tables, ensuring uniformity across all regional data. Without transformation, the warehouse would contain incompatible data types, breaking referential integrity and analytical queries.

Answer

Data cleansing

Answer

Data profiling

Answer

Data masking

Question 7

An e-commerce company uses a star schema for its data warehouse. The fact table 'sales_fact' contains foreign keys to dimension tables: customer_dim, product_dim, time_dim, and store_dim. A business user wants to know the total sales for each product category in the last month. Which join operation is required to retrieve this data?

Accepted Answer

Inner join between fact table and dimension tables. To retrieve total sales for each product category, you need to join the fact table with the product dimension table to map product keys to categories, and with the time dimension table to filter on the last month. An inner join is correct because it returns only rows where matching keys exist in both tables, which is the standard approach for star-schema queries where all required dimension attributes are present. This ensures that only valid sales transactions with corresponding product and time entries are included in the aggregation.

Answer

Self-join on the fact table

Answer

Cross join between fact and dimension tables

Answer

Left outer join between fact and dimension tables

Question 8

A data analyst is working with a dataset containing customer information. The dataset includes a column 'full_name' which stores first and last names together. To perform analysis on first names separately, which data concept describes the process of splitting 'full_name' into 'first_name' and 'last_name'?

Accepted Answer

Data normalization. Data normalization is the process of organizing data to reduce redundancy and improve integrity, which includes splitting composite attributes like 'full_name' into atomic values ('first_name', 'last_name'). This aligns with the first normal form (1NF) principle in database design, where each column should contain indivisible values. The data analyst is decomposing a single field into multiple, more granular fields to enable separate analysis.

Answer

Data deduplication

Answer

Data summarization

Answer

Data aggregation

Question 9

A data scientist is building a machine learning model to predict customer churn. The dataset includes both numerical features (age, income) and categorical features (gender, marital status). Which data concept describes the process of converting categorical features into numerical values that can be used by the algorithm?

Accepted Answer

Encoding. Encoding is the correct data concept because it transforms categorical features (like gender and marital status) into numerical representations (e.g., one-hot encoding, label encoding) that machine learning algorithms can process. Unlike feature scaling or dimensionality reduction, encoding directly addresses the incompatibility of non-numeric data with mathematical model operations.

Answer

Data sampling

Answer

Feature scaling

Answer

Dimensionality reduction

Question 10

A company's database has a table 'orders' with columns: order_id, customer_id, order_date, and total_amount. A data analyst needs to identify customers who have placed more than 5 orders in the past year. Which data concept should be used to group orders by customer and count them?

Accepted Answer

Aggregation with GROUP BY. The requirement to count orders per customer requires grouping rows by customer_id and then applying a count function. The GROUP BY clause in SQL aggregates rows that share a common value (customer_id) into summary rows, and the COUNT function tallies the number of orders per group. This is the standard approach for such 'per-customer' aggregations.

Answer

Joining with other tables

Answer

Filtering with WHERE clause

Answer

Sorting with ORDER BY

Question 11

A data analyst receives a dataset with a column 'salary' that contains values like '45,000', '55,000', and '65,000'. The analyst notices that the values are stored as text. Which data concept should be applied to convert the salary column from text to numeric format for analysis?

Accepted Answer

Data type conversion. Data type conversion is the correct concept because the salary values are stored as text (string) but need to be converted to a numeric type (e.g., integer or float) for mathematical operations like aggregation or averaging. In tools like Python (pandas `astype(float)`), SQL (`CAST(salary AS INTEGER)`), or Excel (`VALUE()` function), this explicit conversion ensures the data is treated as numbers, not strings. Without conversion, operations like `SUM` or `AVG` would fail or produce incorrect results.

Answer

Data imputation

Answer

Data validation

Answer

Data normalization

Question 12

Which TWO of the following are characteristics of structured data? (Choose TWO.)

Accepted Answer

Has a defined schema. Structured data is defined by having a predefined schema, which specifies the data types, constraints, and relationships for each field. This schema ensures consistency and allows for efficient querying and validation. Option A is correct because a defined schema is a fundamental characteristic of structured data, as seen in relational database tables where each column has a specific data type and constraints.

Answer

Requires NoSQL databases for storage

Answer

Often contains natural language text

Answer

Cannot be queried using SQL

Question 13

Which THREE of the following are valid data quality dimensions? (Choose THREE.)

Accepted Answer

Completeness. Completeness is a core data quality dimension that measures whether all required data is present. In the context of the DA0-001 exam, completeness ensures that no fields or records are missing, which is fundamental for reliable analysis and reporting.

Answer

Encryption

Answer

Redundancy

Question 14

Which TWO of the following are examples of data transformation? (Choose TWO.)

Accepted Answer

Normalizing data to eliminate redundancy. Data normalization is a transformation process that reorganizes data to reduce redundancy and improve integrity, typically by decomposing tables into smaller, related tables (e.g., achieving 3NF in relational databases). This changes the structure and representation of the data, which is a core example of data transformation.

Answer

Creating a backup of the database

Answer

Generating summary statistics

Answer

Removing duplicate records

Question 15

A financial services company is migrating its customer data from a legacy on-premises relational database to a cloud-based data warehouse. The legacy database uses a denormalized schema with a single table 'customer_master' that contains all customer attributes, including repeated groups for multiple accounts per customer (account1_type, account1_balance, account2_type, account2_balance, etc.). The data warehouse team wants to implement a normalized star schema with separate dimension and fact tables. During the ETL process, the team encounters an error: 'Data truncation: string data right truncation' when loading account_type values into the dim_account table. The account_type column in dim_account is defined as VARCHAR(10), but the source data contains account types like 'SavingsPlus' (11 characters) and 'CheckingPremium' (15 characters). The team must resolve this issue without losing data. Which course of action should the team take?

Accepted Answer

Increase the VARCHAR length of dim_account.account_type to accommodate the longest account type.. Increasing the VARCHAR length of dim_account.account_type to accommodate the longest account type (e.g., VARCHAR(15) for 'CheckingPremium') resolves the data truncation error without data loss. This aligns with the star schema design principle of preserving source data integrity while ensuring the column definition matches the actual data length. The team must avoid truncation or NULL insertion to maintain accurate dimensional attributes for analytics.

Answer

Truncate the account_type values to 10 characters during ETL.

Answer

Change the data type of dim_account.account_type to TEXT.

Answer

Ignore the error and continue loading with NULL values for truncated rows.

Question 16

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?

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 17

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 18

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 19

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 20

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.