DA0-001 Data Concepts and Environments Practice Test 3 — 15 Questions

Question 1

A retail company wants to analyze customer purchase patterns over time. The data is stored in a relational database with tables for Customers, Orders, and Products. Which database concept should be used to ensure that each order references a valid customer?

Accepted Answer

Foreign key. A foreign key constraint enforces referential integrity by ensuring that every value in the 'customer_id' column of the Orders table matches a valid primary key value in the Customers table. This prevents orphaned records and guarantees that each order references an existing customer.

Answer

View

Answer

Index

Answer

Primary key

Question 2

A data engineer needs to extract data from a REST API and load it into a data warehouse. The data is received in JSON format. Which data type best describes JSON?

Accepted Answer

Semi-structured. JSON (JavaScript Object Notation) is classified as a semi-structured data type because it uses a flexible, self-describing schema with key-value pairs and nested structures, but does not enforce a rigid tabular schema like relational databases. In the context of extracting data from a REST API, JSON allows for varying fields and hierarchical data, which aligns with the semi-structured category.

Answer

Transactional

Answer

Unstructured

Answer

Structured

Question 3

A company is building a data pipeline to ingest sensor data from IoT devices. The data arrives continuously in small batches and must be processed in real-time for monitoring. Which type of data source best describes this scenario?

Accepted Answer

Streaming data. B is correct because the scenario describes data arriving continuously in small batches that must be processed in real-time for monitoring. This is the defining characteristic of streaming data, which is typically ingested via technologies like Apache Kafka, Amazon Kinesis, or MQTT brokers, enabling low-latency processing and immediate alerting.

Answer

Transactional database

Answer

Web scraping

Answer

Flat file

Question 4

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?

Accepted Answer

Composite index on (order_date, customer_id). Option C is correct because 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 5

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 6

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 7

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 8

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 9

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 10

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 achieve 2NF, the table must be in 1NF and have no partial dependencies, where a non-key column depends on only part of a composite primary key. Here, ProductName depends solely on ProductID, not on the full composite key (OrderID, ProductID), creating a partial dependency. Removing ProductName to a separate Product table resolves this and moves the design to 2NF.

Answer

The table has repeating groups

Answer

Quantity depends on both OrderID and ProductID

Answer

The table has a transitive dependency

Question 11

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 12

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 13

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. Option B is correct because 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 14

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. Option B is correct because 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 15

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