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

Question 1

A data engineer is designing a system to store raw sensor data from thousands of IoT devices. The data will be used later for various analytics projects, but the schema is not yet defined. Which storage solution is most appropriate?

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

Accepted Answer

Data lake. A data lake stores raw data in its native format (e.g., S3, ADLS) without requiring a predefined schema, making it suitable for IoT data.

Answer

Data mart

Answer

Data warehouse

Answer

Relational database

Question 2

Which database index type is most commonly used for exact-match lookups and range queries in a B-tree structure?

Accepted Answer

B-tree index. A B-tree index is the correct answer because it maintains sorted data in a balanced tree structure, enabling both exact-match lookups (via equality searches) and efficient range queries (via ordered traversal of leaf nodes). This dual capability makes it the standard index type in relational databases like MySQL, PostgreSQL, and Oracle for general-purpose querying.

Answer

Hash index

Answer

Clustered index

Answer

Bitmap index

Question 3

A data analyst needs to retrieve current weather data from a third-party service. The service provides an endpoint that returns data in JSON format over HTTP. Which data source type is being used?

Accepted Answer

API. The data analyst is retrieving data from a third-party service via an HTTP endpoint that returns JSON. This is the classic definition of an API (Application Programming Interface) — specifically a RESTful web API — which allows programmatic access to structured data over HTTP using standard methods like GET. The JSON format confirms it is an API response, not a file or stream.

Answer

Streaming data

Answer

Flat file

Answer

Web scraping

Question 4

A DBA wants to improve query performance on a large table that is frequently filtered on two columns: department_id and hire_date. The table has millions of rows. Which index strategy would be most effective?

Accepted Answer

Create a composite B-tree index on (department_id, hire_date). A composite B-tree index on (department_id, hire_date) is most effective because it allows the database to satisfy equality and range predicates on both columns in a single index scan. B-tree indexes are optimized for high-cardinality columns and support efficient multi-column filtering when the leading column matches the query's equality condition, followed by the range condition on hire_date.

Answer

Create a bitmap index on hire_date

Answer

Create a hash index on department_id only

Answer

Create two separate B-tree indexes, one on each column

Question 5

Which stage of the data lifecycle involves converting raw data into a usable format, such as cleaning or validating?

Accepted Answer

Processing. Processing is the stage where raw data is transformed into a usable format through cleaning, validation, normalization, or aggregation. This step ensures data quality and consistency before analysis or storage, directly matching the question's description.

Answer

Archival

Answer

Ingestion

Answer

Storage

Question 6

A data governance team is implementing a program to ensure consistent definitions and quality of customer data across the organization. They assign a senior manager to be accountable for the data asset. Which role does this manager fulfill?

Accepted Answer

Data owner. The data owner is the senior manager accountable for a specific data asset, including its quality, definition, and compliance. In the DA0-001 context, the data owner has ultimate responsibility for the data, not just day-to-day management. This role ensures consistent definitions and quality across the organization, aligning with the governance team's objectives.

Answer

Data analyst

Answer

Data custodian

Answer

Data steward

Question 7

A company needs to store user session data for a web application. Each session has a unique session ID, and the data must be retrieved very quickly by session ID. The data does not require complex relationships or transactions. Which type of NoSQL database is most appropriate?

Accepted Answer

Key-value store (e.g., Redis). A key-value store like Redis is the most appropriate choice because it is optimized for extremely fast lookups by a unique key (session ID) and does not require complex relationships or transactions. Redis stores data in memory, providing sub-millisecond retrieval times ideal for session management, and supports built-in expiration (TTL) to automatically clean up stale sessions.

Answer

Wide-column store (e.g., Cassandra)

Answer

Document store (e.g., MongoDB)

Answer

Graph database (e.g., Neo4j)

Question 8

A data analyst is performing a join between two tables: 'employees' and 'departments'. The 'employees' table has a foreign key 'dept_id' referencing the 'departments' table. Which two join types would include all rows from the 'employees' table, regardless of whether there is a matching department? (Select TWO)

Accepted Answer

LEFT JOIN. LEFT JOIN and RIGHT JOIN (if reversed) can include all rows from the left table. Specifically, LEFT JOIN includes all rows from the left table. FULL OUTER JOIN also includes all rows from both tables, but the question says 'all rows from the employees table' – that is satisfied by LEFT JOIN and also by FULL OUTER JOIN (which includes all from employees). However, the correct answer set: LEFT JOIN and FULL OUTER JOIN. The question says 'include all rows from the employees table' – RIGHT JOIN does not guarantee that unless employees is on the right side. So the correct two are LEFT JOIN and FULL OUTER JOIN.

Answer

INNER JOIN

Answer

CROSS JOIN

Answer

RIGHT JOIN

Question 9

A data engineer is designing a data pipeline for a retail company. The source system is an OLTP database that records sales transactions. The target is a data warehouse used for reporting. The engineer is evaluating whether to use ETL or ELT. Which three factors would favor using ELT over ETL? (Select THREE)

Accepted Answer

The business analysts need access to raw data for ad-hoc exploration. ELT loads raw data into the warehouse first, allowing business analysts to perform ad-hoc exploration directly on the source data without pre-transformation. This flexibility is a key advantage of ELT over ETL, where transformations are applied before loading.

Answer

The transformation logic requires proprietary functions not available in the warehouse

Answer

Data must be cleansed and validated before loading into the warehouse

Question 10

A university database stores student information in a normalized schema. The 'students' table has a primary key 'student_id'. The 'enrollments' table has a foreign key 'student_id' referencing 'students'. Which two of the following are true about primary and foreign keys? (Select TWO)

Accepted Answer

A foreign key ensures referential integrity between tables. A foreign key enforces referential integrity by ensuring that every value in the foreign key column of the 'enrollments' table matches a valid primary key value in the 'students' table. This prevents orphaned records and maintains consistency across related tables in a normalized relational database.

Answer

A foreign key must have the same name as the primary key it references

Answer

A foreign key can reference a column that is not a primary key

Answer

A table can have multiple primary keys

Question 11

Which of the following is a characteristic of structured data?

Accepted Answer

It conforms to a fixed schema with rows and columns.. Structured data is defined by its adherence to a fixed schema, typically organized into rows and columns within relational databases. This rigid structure enables efficient querying and manipulation using SQL, as each field has a predefined data type and constraints. The correct answer highlights this fundamental characteristic, which distinguishes structured data from semi-structured or unstructured formats.

Answer

It has a flexible schema that can vary per record.

Answer

It cannot be analyzed using SQL.

Answer

It is stored as blobs in a data lake.

Question 12

A company ingests customer clickstream data from its website. The data arrives continuously in JSON format and must be stored for real-time analytics. Which type of data source is being described?

Accepted Answer

Streaming data. The description matches a streaming data source because clickstream data arrives continuously in JSON format and must be stored for real-time analytics. Streaming data sources, such as Apache Kafka or Amazon Kinesis, ingest unbounded data in real time, enabling immediate processing and analytics without batch delays.

Answer

Transactional database

Answer

Flat file

Answer

Data warehouse

Question 13

A data analyst needs to combine customer data from a MySQL transactional database with product data from a MongoDB document store to create a unified view for reporting. The analyst uses a SQL query that joins the tables after extracting data from both sources. Which database concept is being applied?

Accepted Answer

Join. (Join) because the scenario describes combining data from two different sources—MySQL and MongoDB—into a unified view using a SQL query that joins the tables after extraction. This is a classic example of a cross-source join, where data from disparate databases is merged based on a common key, which is the fundamental purpose of a JOIN operation in SQL.

Answer

View

Answer

Stored procedure

Answer

Index

Question 14

An organization is implementing a data warehouse to support business intelligence reporting. The data warehouse must ensure that transactions are processed reliably. Which property guarantees that each transaction is treated as a single, indivisible unit?

Accepted Answer

Atomicity. Atomicity (option C) is the correct property because it ensures that a transaction is treated as a single, indivisible unit of work. In the context of a data warehouse, this means that either all operations within the transaction are committed successfully, or none are applied, preventing partial updates that could corrupt the data. This is a core component of the ACID (Atomicity, Consistency, Isolation, Durability) model, which is fundamental to reliable transaction processing in databases like SQL Server, Oracle, or PostgreSQL.

Answer

Consistency

Answer

Isolation

Answer

Durability

Question 15

Which of the following is an example of semi-structured data?

Accepted Answer

A JSON file. Semi-structured data has tags or markers to separate data elements, like JSON or XML.

Answer

A CSV file without header

Answer

An image file

Answer

A table in a relational database