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

Question 1

A data analyst is working with a relational database that contains a table of customer orders. To optimize query performance for a report that filters by order date and customer ID, the analyst wants to create an index. Which type of index would be most effective for queries that filter on both columns?

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

Accepted Answer

Composite index on (order_date, customer_id). A composite B-tree index on (order_date, customer_id) allows the database to efficiently satisfy equality and range predicates on both columns in a single index scan. B-tree indexes support ordered traversal and range lookups, making them ideal for date-based filtering combined with an equality filter on customer_id. This index structure minimizes the number of rows scanned by leveraging the index's leading column for the date range and the second column for the customer ID match.

Answer

B-tree index on order_date

Answer

Hash index on customer_id

Answer

Clustered index on order_id

Question 2

An organization uses a data warehouse for analytics. The data team wants to load data from source systems into the warehouse. They choose to load raw data first and then perform transformations within the warehouse. Which approach are they using?

Accepted Answer

ELT. ELT (Extract, Load, Transform) involves extracting data, loading it into the target system (e.g., data warehouse), and then transforming it there. This is common with modern cloud warehouses like Snowflake or BigQuery that handle transformations efficiently.

Answer

Data lake

Answer

Data mart

Answer

ETL

Question 3

A company is designing a database for an e-commerce application that requires high transaction throughput and must guarantee that each transaction is processed atomically. Which property of ACID ensures that a transaction is either fully completed or not executed at all?

Accepted Answer

Atomicity. Atomicity guarantees that a transaction is treated as a single unit; it either completes entirely or is rolled back, preventing partial updates.

Answer

Isolation

Answer

Durability

Answer

Consistency

Question 4

A data analyst needs to combine data from two tables: one containing customer information and another containing order details. The analyst wants to include all customers, even those who have not placed any orders. Which type of join should be used?

Accepted Answer

LEFT JOIN. A LEFT JOIN returns all rows from the left table (customers) and matching rows from the right table (orders). If a customer has no orders, the order columns will contain NULLs. This satisfies the requirement to include all customers, even those without orders.

Answer

FULL OUTER JOIN

Answer

INNER JOIN

Answer

RIGHT JOIN

Question 5

A data governance team is establishing policies to ensure data quality. They define rules for data accuracy, completeness, and consistency. Which data governance function is primarily responsible for defining and enforcing these rules?

Accepted Answer

Data quality management. Data quality management is the function that sets standards and processes to ensure data is accurate, complete, and consistent. Data stewardship often involves implementing these rules, but the overall responsibility lies with data quality management.

Answer

Data stewardship

Answer

Data ownership

Answer

Master data management

Question 6

A data architect needs to store raw data from various sources, including social media feeds and log files, for future analysis. The data may be used for machine learning and ad-hoc queries. Which storage solution is most appropriate for storing raw data in its native format?

Accepted Answer

Data lake. A data lake is designed to store raw data in its native format, including unstructured and semi-structured data from sources like social media feeds and log files. It supports schema-on-read, making it ideal for future machine learning and ad-hoc queries without requiring upfront transformation. This aligns directly with the requirement to preserve raw data for flexible analysis.

Answer

Data mart

Answer

Relational database

Answer

Data warehouse

Question 7

A database administrator is designing a normalized database to reduce data redundancy. They have a table with columns: OrderID, ProductID, ProductName, and Quantity. The table is currently in 1NF. To move to 2NF, which issue must be resolved?

Accepted Answer

ProductName depends only on ProductID, causing a partial dependency. To move from 1NF to 2NF, the table must have no partial dependencies. A partial dependency occurs when a non-key attribute depends on only part of a composite primary key. Here, the composite key is (OrderID, ProductID). ProductName depends only on ProductID, not on the full key, so it is a partial dependency. Option A (repeating groups) is a violation of 1NF, not 2NF, and the table is already in 1NF. Option C is incorrect because Quantity depends on both OrderID and ProductID (it is fully functionally dependent on the composite key). Option D is incorrect because a transitive dependency (where a non-key attribute depends on another non-key attribute) is a 3NF issue, not 2NF. Therefore, the correct answer is B.

Answer

The table has repeating groups

Answer

Quantity depends on both OrderID and ProductID

Answer

The table has a transitive dependency

Question 8

A company uses a NoSQL document database to store product catalogs. Each product document includes fields like product_id, name, category, and price. The operations team frequently queries by product_id and by category. Which type of NoSQL database is being used, and what should be created to optimize queries by category?

Accepted Answer

Document database; create an index on category. The question explicitly states a document database is used, and the operations team frequently queries by category. In a document database like MongoDB, creating an index on the category field optimizes these queries by allowing the database to quickly locate documents without scanning every document in the collection. This is the standard approach for improving query performance on non-primary-key fields in document stores.

Answer

Key-value store; create a secondary index on category

Answer

Graph database; create a relationship between products

Answer

Wide-column store; create a column family for category

Question 9

A data analyst needs to share a weekly sales report with the marketing team. The report includes aggregated data from the data warehouse. To simplify access, the analyst creates a virtual table that encapsulates the complex query. Which database object should the analyst create?

Accepted Answer

View. A view is a virtual table that encapsulates a complex query, allowing users to access aggregated data without needing to understand the underlying SQL. In this scenario, the analyst creates a view to simplify access to the weekly sales report, as it presents pre-defined, aggregated data from the data warehouse as if it were a table.

Answer

Trigger

Answer

Stored procedure

Answer

Index

Question 10

A data analyst is extracting data from a web page using web scraping techniques. The data will be used for market research. Which TWO of the following are common challenges associated with web scraping?

Accepted Answer

Legal and ethical restrictions. Web scraping often involves accessing data that may be protected by copyright, terms of service, or privacy regulations such as GDPR or the Computer Fraud and Abuse Act (CFAA). Even if data is publicly accessible, repurposing it for market research without permission can lead to legal liability or ethical violations, making this a fundamental challenge.

Answer

Limited API rate limits

Answer

High latency of data transfer

Answer

Inconsistent data formatting

Question 11

A data warehouse team is considering moving from an ETL to an ELT approach. Which THREE of the following are advantages of ELT over ETL?

Accepted Answer

Reduces data loading time because transformations are done after loading. In ELT, data is loaded into the data warehouse first and transformations are applied afterward. This reduces the initial loading time since no transformation processing occurs during the load phase, allowing raw data to be ingested more quickly.

Answer

Requires less storage space in the data warehouse

Answer

Ensures data is cleaned before loading

Question 12

A data analyst is working with a dataset that includes customer names, email addresses, and purchase history. The analyst wants to ensure that each customer is uniquely identified. Which TWO database concepts should be used to enforce uniqueness and link related data?

Accepted Answer

Foreign key. A primary key uniquely identifies each row in a table, ensuring no duplicate customer records. A foreign key links related data across tables by referencing the primary key of another table, enforcing referential integrity. Together, they guarantee uniqueness and enable relational joins between customer and purchase history tables.

Answer

Normalization

Answer

View

Answer

Index

Question 13

A large online retailer stores customer orders in a PostgreSQL database. Each order has a unique order ID, and the database is normalized to 3NF. Which type of data is this?

Accepted Answer

Structured data. The data is structured because it resides in a normalized PostgreSQL database with a unique order ID and conforms to a fixed schema (3NF). Structured data is organized into rows and columns with defined data types, enabling efficient SQL querying and ACID compliance. PostgreSQL's relational model enforces this structure through tables, constraints, and indexes.

Answer

Semi-structured data

Answer

Unstructured data

Answer

Metadata

Question 14

A data analyst receives a file with the extension .json. This file contains product information with attributes that vary between records. How should this file be classified?

Accepted Answer

Semi-structured data. A JSON file with varying attributes per record is a classic example of semi-structured data. Unlike strictly structured data (e.g., a relational table with fixed columns), JSON allows each object to have a different set of key-value pairs, making it schema-flexible. This self-describing nature, where metadata is embedded within the data itself, is the defining characteristic of semi-structured formats.

Answer

Structured data

Answer

Transactional data

Answer

Unstructured data

Question 15

A company uses an OLTP system for processing customer transactions. Which characteristic is most important for this system to ensure that each transaction is processed reliably, even if multiple users access the system simultaneously?

Accepted Answer

It follows ACID properties. ACID properties (Atomicity, Consistency, Isolation, Durability) are essential for OLTP systems to ensure reliable transaction processing.

Answer

It uses a columnar storage format

Answer

It stores data in a denormalized schema

Answer

It supports complex analytical queries