DA0-002 Data Concepts and Environments Practice Test 1 — 15 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.