DA0-002 Practice Test 21 — 15 Questions

Question 1

A data analyst needs to extract data from a transactional database and load it into a data warehouse for reporting. Which process typically transforms the data before loading it into the warehouse?

Accepted Answer

ETL. ETL (Extract, Transform, Load) transforms data before loading it into the warehouse, which is common for traditional data warehousing.

Answer

Data virtualization

Answer

ELT

Answer

Data replication

Question 2

Which of the following data types best describes a JSON file containing customer orders with varying fields per record?

Accepted Answer

Semi-structured data. JSON is a semi-structured data format because it allows flexible schema where fields can vary between records.

Answer

Unstructured data

Answer

Structured data

Answer

Relational data

Question 3

Which of the following best describes a data mart?

Accepted Answer

A subject-specific subset of a data warehouse. A data mart is a subject-specific subset of a data warehouse, focused on a particular department or function.

Answer

A repository for raw, unprocessed data

Answer

An OLTP system for transaction processing

Answer

A tool for extract, transform, and load processes

Question 4

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 5

A data engineer is designing a system to handle high-velocity clickstream data from a website. The system must allow low-latency writes and support key-value lookups. Which type of database is most appropriate?

Accepted Answer

Key-value store (e.g., Redis). A key-value store like Redis is optimized for high-velocity writes and low-latency key-value lookups, making it ideal for clickstream data.

Answer

Graph database (e.g., Neo4j)

Answer

Document store (e.g., MongoDB)

Answer

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

Question 6

A data analyst needs to combine rows from two tables based on a related column, but only wants rows that have matching values in both tables. Which join type should the analyst use?

Accepted Answer

INNER JOIN. INNER JOIN returns only rows with matching values in both tables, which matches the requirement.

Answer

RIGHT JOIN

Answer

FULL OUTER JOIN

Answer

LEFT JOIN

Question 7

A data analyst wants to sample a large dataset of customer transactions. Which TWO sampling methods are probability-based and ensure every element has a known chance of being selected? (Select TWO.)

Accepted Answer

Simple random sampling. Simple random sampling and stratified sampling are probability-based methods where each element has a known probability. Systematic sampling is also probability-based but the question asks for TWO; convenience sampling is non-probability, and cluster sampling is probability-based but the question likely expects the two most common.

Answer

Convenience sampling

Answer

Systematic sampling

Answer

Cluster sampling

Question 8

A data analyst is writing a query to rank products by total sales amount within each category. They want ties to have the same rank and no gaps in the ranking sequence. Which window function should they use?

Accepted Answer

DENSE_RANK(). DENSE_RANK() assigns the same rank to ties and does not skip subsequent ranks. RANK() also assigns same rank to ties but skips numbers, creating gaps.

Answer

ROW_NUMBER()

Answer

RANK()

Answer

NTILE()

Question 9

A data analyst is evaluating data quality for a customer database. Which TWO dimensions of data quality are most directly affected by duplicate customer records?

Accepted Answer

Accuracy. Duplicates reduce accuracy (records may be incorrect) and uniqueness (each entity should appear once).

Answer

Consistency

Answer

Timeliness

Answer

Completeness

Question 10

You are analyzing sales data and need to calculate the moving average of monthly sales over the previous 3 months for each month. Which type of function is best suited for this task?

Accepted Answer

Window function with OVER(). Window functions, specifically using OVER() with ORDER BY and a frame specification, can compute moving averages. Aggregate functions alone cannot access previous rows without a self-join. String and date functions are irrelevant.

Answer

String function

Answer

Aggregate function with GROUP BY

Answer

Date function

Question 11

A data analyst is performing data profiling on a customer dataset. Which metric would best reveal the number of distinct values in the 'state' column?

Accepted Answer

Cardinality. Cardinality refers to the number of unique values in a column, which directly indicates distinct states.

Answer

Mean

Answer

Row count

Answer

Null count

Question 12

A data analyst needs to sample 10% of customers from each of three regions (North, South, Central) to ensure proportional representation. Which sampling method should be used?

Accepted Answer

Stratified sampling. Stratified sampling divides the population into strata (regions) and samples proportionally from each. Simple random sampling would not guarantee proportional representation. Systematic sampling selects every kth element. Cluster sampling selects entire groups randomly.

Answer

Systematic sampling

Answer

Cluster sampling

Answer

Simple random sampling

Question 13

A data analyst wants to identify customers whose last name starts with 'Mc' from the 'customers' table. Which WHERE clause condition should be used?

Accepted Answer

last_name LIKE 'Mc%'. The LIKE operator with '%' wildcard matches any sequence of characters after 'Mc'.

Answer

last_name LIKE 'Mc_'

Answer

last_name IN ('Mc%')

Answer

last_name = 'Mc%'

Question 14

A data analyst is exploring a sales dataset and wants to identify columns that are likely to be foreign keys. Which TWO characteristics would indicate a foreign key?

Accepted Answer

The column name ends with '_id'. Foreign keys typically match primary keys in another table and have a name suggestive of the relationship.

Answer

The column contains NULL values

Answer

The column is of integer data type

Answer

The column has a UNIQUE constraint

Question 15

A data analyst is working with a sales table that contains columns: sale_id, product_id, sale_date, and amount. They need to calculate a 7-day moving average of sales amount for each product, ordered by sale_date. Which window function syntax should they use?

Accepted Answer

AVG(amount) OVER (PARTITION BY product_id ORDER BY sale_date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW). A moving average requires averaging over a frame of rows. Using AVG() with an ORDER BY in the OVER clause and a frame specification (ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) calculates the 7-day moving average.

Answer

AVG(amount) OVER (PARTITION BY product_id ORDER BY sale_date)

Answer

AVG(amount) OVER (ORDER BY sale_date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW)

Answer

SUM(amount) OVER (PARTITION BY product_id ORDER BY sale_date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW)