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

Question 1

A data analyst needs to combine customer data from two tables: Customers (CustomerID, Name) and Orders (OrderID, CustomerID, Amount). Only customers who have placed at least one order should be included. Which JOIN type should be used?

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

Accepted Answer

INNER JOIN. An INNER JOIN returns only rows where there is a match in both tables. Since the requirement is to include only customers who have placed at least one order, the INNER JOIN on CustomerID will filter out any customer without a matching order record, exactly meeting the condition.

Answer

LEFT JOIN

Answer

FULL OUTER JOIN

Answer

RIGHT JOIN

Question 2

A company uses a data warehouse for reporting. They need to extract data from multiple sources, load it into a staging area, and then transform it before moving to the warehouse. This process is known as:

Accepted Answer

ETL. The process described—extracting data from multiple sources, loading it into a staging area, and then transforming it before moving to the warehouse—is the classic definition of ETL (Extract, Transform, Load). In ETL, transformation occurs after extraction but before loading into the target system, which is exactly what the staging area is used for. This contrasts with ELT, where transformation happens after loading into the warehouse.

Answer

ELT

Answer

Data replication

Answer

Data ingestion

Question 3

Which of the following is a characteristic of a NoSQL document database like MongoDB?

Accepted Answer

Schema-flexible, JSON-like documents. Document databases store data in flexible, JSON-like documents, allowing schema variability.

Answer

Data stored in tables with rows and columns

Answer

Strict schema enforcement

Answer

Support for ACID transactions across multiple documents

Question 4

A data analyst wants to retrieve data from a REST API that returns JSON. Which step is part of the data lifecycle for this activity?

Accepted Answer

Data ingestion. Ingestion is the process of bringing data into a system for further processing.

Answer

Data archival

Answer

Data sharing

Answer

Data deletion

Question 5

A database has a table that violates 2NF because it contains a composite primary key and some attributes depend only on part of that key. Which normal form would be violated next if the table is not addressed?

Accepted Answer

3NF. The table already violates 2NF. If not addressed, it will never satisfy 2NF, but the question asks which normal form would be violated next if the table is not addressed. Since 2NF is already violated, the next normal form in sequence that will be violated is 3NF, because the table will fail to meet 3NF requirements (e.g., transitive dependencies) while still being stuck at a 2NF violation. Therefore, 3NF is the next normal form that would be violated.

Answer

2NF

Answer

BCNF

Answer

1NF

Question 6

Which of the following data sources is most likely to generate streaming data?

Accepted Answer

IoT sensors. Streaming data is continuously generated from sources like IoT sensors, clickstreams, and social media feeds.

Answer

Transactional database

Answer

Flat file

Answer

API

Question 7

A table named Orders has columns OrderID, CustomerID, OrderDate, and TotalAmount. Which column should be the primary key to uniquely identify each order?

Accepted Answer

OrderID. The OrderID column is the correct choice for the primary key because it contains unique values for each order, ensuring that each row can be uniquely identified. A primary key must be unique, non-null, and stable; OrderID satisfies all these requirements, whereas the other columns do not guarantee uniqueness or are subject to change.

Answer

OrderDate

Answer

TotalAmount

Answer

CustomerID

Question 8

A data engineer is designing a data pipeline where raw data is loaded into a cloud data warehouse (Snowflake) and then transformed using SQL. This approach is called:

Accepted Answer

ELT. ELT (Extract, Load, Transform) loads raw data first, then transforms it within the warehouse, which is suitable for modern cloud warehouses.

Answer

ETL

Answer

Data migration

Answer

Data wrangling

Question 9

Which database concept ensures that data in one table corresponds to data in another table, preventing orphan records?

Accepted Answer

Referential integrity. Referential integrity ensures that foreign key values match primary key values in the referenced table.

Answer

Index

Answer

Foreign key

Answer

Primary key

Question 10

A company needs to store data that is highly interconnected, such as social network relationships. Which two database types are best suited for this? (Select TWO.)

Accepted Answer

Document store (e.g., MongoDB). Graph databases excel at storing relationships, and NoSQL databases support flexible schemas for interconnected data.

Answer

Relational database

Answer

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

Answer

Key-value store (e.g., Redis)

Question 11

A data governance team is establishing policies. Which three activities are part of data governance? (Select THREE.)

Accepted Answer

Data quality management. Data quality management is a core activity of data governance because it ensures that data meets defined standards for accuracy, completeness, consistency, and timeliness. Governance policies mandate monitoring and remediation processes to maintain data quality across the organization.

Answer

Data indexing

Answer

Data normalization

Question 12

An organization uses a data warehouse for analytics. Which two characteristics are typical of a data warehouse compared to a data lake? (Select TWO.)

Accepted Answer

Optimized for complex queries and reporting. A data warehouse is optimized for complex queries and reporting (A) because it uses schema-on-write (D), meaning data is transformed and structured before loading to support fast analytical queries. In contrast, a data lake uses schema-on-read and stores raw data. ELT is typical of data lakes, not data warehouses.

Answer

Typically uses ELT process

Answer

Stores raw data in native format

Answer

Supports all data types including unstructured

Question 13

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 14

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 15

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