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

Question 1

A company needs to store raw, unprocessed data from IoT sensors for future machine learning experiments. The data is in various formats and schemas are 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 is the correct choice because it stores raw, unprocessed data in its native format (structured, semi-structured, or unstructured) without requiring a predefined schema. This aligns perfectly with the need to ingest IoT sensor data in various formats for future machine learning experiments, where schemas are not yet defined. Unlike data warehouses or data marts, a data lake supports schema-on-read, allowing the data to be transformed and queried later as needed.

Answer

Data mart

Answer

Data warehouse

Answer

Operational database

Question 2

A financial application requires fast query performance for aggregations on large historical datasets. The schema has many lookup tables. Which schema design is most efficient for this workload?

Accepted Answer

Star schema. The star schema is most efficient for this workload because it denormalizes lookup tables into dimension tables, reducing the number of joins required for aggregations. This design optimizes query performance for large historical datasets by enabling faster full table scans and simpler query plans, which is critical for financial applications needing rapid aggregations.

Answer

Snowflake schema

Answer

Wide table

Answer

Third normal form (3NF)

Question 3

A database table has columns: OrderID (primary key), ProductID, CustomerID, CustomerName, OrderDate, ProductName. All products are purchased only by the customer who placed the order. Which normal form violation exists if CustomerName depends on CustomerID?

Accepted Answer

Third normal form (3NF). The table violates Third Normal Form (3NF) because CustomerName depends on CustomerID, which is not a candidate key (the primary key is OrderID). 3NF requires that every non-key attribute be non-transitively dependent on the primary key; here, CustomerName is transitively dependent on OrderID via CustomerID. Since CustomerID is a non-key attribute (it is not part of the primary key), this transitive dependency breaks 3NF.

Answer

Boyce-Codd normal form (BCNF)

Answer

Second normal form (2NF)

Answer

First normal form (1NF)

Question 4

A data engineer needs to store logs from web servers that have varying fields. The logs are in JSON format. Which data type describes this JSON data?

Accepted Answer

Semi-structured data. JSON data with varying fields is classified as semi-structured data because it has organizational properties (key-value pairs, nested structures) but does not conform to a rigid schema like a relational table. The logs from web servers may have different fields per record, which is a hallmark of semi-structured data, as it allows flexibility while still being self-describing.

Answer

Binary data

Answer

Structured data

Answer

Unstructured data

Question 5

A retail company processes daily transactions. The current system transforms data before loading it into the data warehouse. The volume is growing rapidly, and they want to load raw data first to reduce processing time. Which approach should they adopt?

Accepted Answer

ELT (Extract, Load, Transform). (ELT) because the company wants to load raw data first and then transform it later, reducing initial processing time. ELT leverages the power of modern data warehouses to perform transformations after loading, which is ideal for rapidly growing volumes of raw transaction data.

Answer

Change data capture (CDC)

Answer

ETL (Extract, Transform, Load)

Answer

Data replication

Question 6

A table Orders has OrderID (primary key), CustomerID, and CustomerEmail. During analysis, it is found that CustomerID uniquely identifies CustomerEmail. Which normal form is violated if both CustomerID and CustomerEmail are stored in this table?

Accepted Answer

Third normal form (3NF). The table violates Third Normal Form (3NF) because CustomerEmail is transitively dependent on CustomerID, which is not a candidate key. In 3NF, every non-key attribute must depend only on the primary key (OrderID), not on another non-key attribute. Since CustomerID uniquely identifies CustomerEmail, CustomerEmail depends on CustomerID, not directly on OrderID, creating a transitive dependency.

Answer

Second normal form (2NF)

Answer

No violation

Answer

First normal form (1NF)

Question 7

A company requires real-time masking of credit card numbers for customer support agents while allowing full access for accountants. Which technique should be implemented?

Accepted Answer

Dynamic data masking. Dynamic data masking (DDM) applies masking rules at query runtime based on user privileges, allowing accountants full access while customer support agents see only masked credit card numbers. Unlike static masking, DDM does not alter the underlying stored data, making it ideal for real-time, role-based obfuscation without duplicating or transforming the database.

Answer

Tokenization

Answer

Static data masking

Answer

Data encryption

Question 8

A hospital's patient records system must process thousands of small transactions per second. Which type of database system is best suited for this workload?

Accepted Answer

OLTP. OLTP (Online Transaction Processing) systems are designed to handle a high volume of small, concurrent transactions with low latency and high concurrency. This makes them ideal for a hospital patient records system that must process thousands of small transactions per second, such as patient check-ins, prescription updates, and billing entries.

Answer

Data mart

Answer

Data warehouse

Answer

OLAP

Question 9

A data quality report shows that 95% of records have all required fields completed, but 20% of the completed fields contain values that are outside valid ranges. Which data quality dimension is most affected?

Accepted Answer

Accuracy. Accuracy measures how well data reflects real-world values or a defined standard. Here, 20% of completed fields contain values outside valid ranges, meaning the data is present but incorrect, directly degrading accuracy. Completeness (95% filled) is high, but the core issue is that the values themselves are wrong, not missing or late.

Answer

Consistency

Answer

Timeliness

Answer

Completeness

Question 10

Which TWO roles are primarily responsible for defining and enforcing data governance policies within an organization?

Accepted Answer

Data steward. (Data steward) is correct because data stewards are responsible for the day-to-day management, quality, and enforcement of data governance policies, including data classification, access controls, and compliance with regulatory standards. Option E (Data owner) is correct because data owners are senior stakeholders who define the governance policies, approve data access decisions, and are accountable for the data assets within their domain.

Answer

Data analyst

Answer

Data architect

Answer

Data custodian

Question 11

Which THREE of the following are NoSQL database types?

Accepted Answer

Document. Document databases, such as MongoDB, store data in flexible, JSON-like documents (BSON in MongoDB's case). This allows for nested structures and schema-less designs, making them a core NoSQL category distinct from relational models.

Answer

Hierarchical

Answer

Relational

Question 12

A data team must implement a data retention policy to reduce storage costs while meeting legal requirements. Which TWO actions best achieve this?

Accepted Answer

Set data retention limits with automated deletion. Archiving old data to cheaper storage reduces primary storage costs, and setting retention limits ensures data is deleted when no longer needed, balancing cost and compliance.

Answer

Use data compression

Answer

Increase primary storage capacity

Answer

Implement data deduplication

Question 13

Refer to the exhibit. A data analyst is trying to understand access permissions for the company data folder. Which statement accurately describes the effective permissions?

Accepted Answer

DataAnalyst can read objects in the production folder except those in the sensitive subfolder.. The exhibit shows an access control policy that grants the DataAnalyst user read permission on the production folder, but includes an explicit deny rule for the sensitive subfolder specified via a path condition. In most access control systems, explicit deny rules take precedence over allow rules, so the deny on the sensitive subfolder overrides the allow on the production folder, effectively blocking read access to objects in the sensitive subfolder while permitting reads elsewhere in the production folder.

Answer

DataAnalyst can read all objects in the production folder, including the sensitive subfolder.

Answer

No one can read from the production folder except DataAnalyst.

Answer

Only DataAnalyst is allowed to read from the entire production folder.

Question 14

Refer to the exhibit. A database administrator notices that queries filtering on both CustomerID and OrderDate are slow. Which single change would most likely improve performance for such queries?

Accepted Answer

Add a composite index on (CustomerID, OrderDate). A composite index on (CustomerID, OrderDate) allows the database to use a single index to filter on both columns, which is more efficient than using separate indexes and combining results.

Answer

Partition the table by OrderDate

Answer

Convert TotalAmount to VARCHAR

Answer

Remove the primary key constraint

Question 15

Refer to the exhibit. A data pipeline is failing to parse this log entry. What is the most likely cause of the error?

Accepted Answer

Unquoted string for country. The log entry uses space as a delimiter. The country field 'United States' contains a space but is not enclosed in quotes. When parsing, the space within the value is misinterpreted as a delimiter, causing the parser to split the field incorrectly. This results in a parsing failure. The unquoted string containing a space is the most direct cause of the error.

Answer

Missing comma between fields

Answer

Incorrect data type for age

Answer

Extra whitespace