DA0-002 Practice Test 15 — 15 Questions

Question 1

An organization is acquiring data from an external vendor. The vendor provides a flat file with inconsistent delimiters and missing values. Which step should be performed first in data acquisition?

Accepted Answer

Data profiling. Data profiling is the first step because it examines the data to understand its structure, quality, and issues (like inconsistent delimiters and missing values) before any further processing. Option A (Data integration) is wrong because integration combines data from multiple sources and should follow profiling. Option C (Data transformation) is wrong because transforming data requires first understanding its current state through profiling. Option D (Data cleansing) is wrong because cleansing is performed after profiling identifies the issues.

Answer

Data integration

Answer

Data transformation

Answer

Data cleansing

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 data analyst is validating a dataset acquired from an external source. Which TWO actions are appropriate for data quality assessment?

Accepted Answer

Check for missing values in critical fields. Option A is correct because checking for missing values in critical fields is a core data quality assessment step that identifies nulls or gaps in essential attributes, which can skew analysis or break downstream processing. Option C is correct because validating data format against the expected schema confirms that each column's data type, structure, and constraints (e.g., date formats, numeric ranges, string lengths) match requirements, catching inconsistencies from the external source. Option B is not appropriate because deleting rows with null values without review can silently discard valid records and hide data quality issues rather than assessing them. Option D is wrong because loading unvalidated data directly into production risks propagating errors and corrupting downstream systems. Option E is wrong because transforming data to match the target system without verification skips the assessment step and can mask or introduce quality problems.

Answer

Delete any rows with null values without review

Answer

Immediately load all data into production

Answer

Transform data to match target system without verification

Question 4

An analyst is creating a data story about the decline in customer satisfaction scores. The data shows that satisfaction dropped after a software update. Which narrative structure best helps the audience understand the cause and effect?

Accepted Answer

Begin with the software update event, then show satisfaction before and after. It follows a chronological cause-and-effect narrative, starting with the software update event and then showing satisfaction scores before and after. This structure directly maps to the audience's need to understand the causal relationship, as it highlights the intervention point and the resulting change in the metric. In data storytelling, this is known as the 'before-and-after' or 'change-over-time' narrative, which is most effective for demonstrating impact.

Answer

Start with the current scores, then show historical trend

Answer

Present all data points without a story

Answer

Use a pie chart of satisfaction categories

Question 5

A data analyst is building a self-service reporting environment. Which of the following is the primary benefit of this approach?

Accepted Answer

It reduces the number of ad hoc report requests to the analytics team.. Self-service reporting empowers business users to build and run their own reports against governed data, which directly reduces the volume of ad hoc report requests that would otherwise be funneled to the analytics team. The core value proposition is democratizing data access while freeing skilled analysts for higher-value work. This is the primary benefit cited in BI maturity models and analytics enablement frameworks.

Answer

It ensures all reports use the same data source.

Answer

It automatically generates executive summaries.

Answer

It improves data security by limiting access.

Question 6

What is the primary purpose of the HAVING clause in the query shown?

Accepted Answer

Filter groups after aggregation. The HAVING clause is used to filter groups after the GROUP BY clause has aggregated the data. In SQL, WHERE filters individual rows before aggregation, while HAVING applies conditions to the results of aggregate functions like SUM, COUNT, or AVG. Option D is correct because the query uses HAVING to restrict which grouped results appear in the final output.

Answer

Sort the results in descending order

Answer

Join two tables

Answer

Filter rows before grouping

Question 7

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 8

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 9

A financial institution needs to acquire credit transaction data from multiple sources while ensuring compliance with data privacy regulations. What is the most critical step?

Accepted Answer

Data anonymization during extraction. Data anonymization during extraction is the most critical step because it ensures that personally identifiable information (PII) is irreversibly masked or removed before the data enters the processing pipeline, directly addressing compliance with regulations such as GDPR and PCI DSS. Without this step, even if other measures are applied later, the initial exposure of sensitive data violates privacy mandates and increases breach risk.

Answer

Data replication for redundancy

Answer

Data enrichment with external sources

Answer

Data compression for storage

Question 10

A business analyst is creating a dashboard to monitor sales performance. The primary goal is to quickly identify which regions are underperforming compared to the target. Which design principle is most important to apply?

Accepted Answer

Visual hierarchy. Visual hierarchy ensures that the most important metrics (like underperforming regions) are prominently displayed, drawing the user's attention first.

Answer

Data-ink ratio

Answer

Appropriate precision

Answer

Consistent color coding

Question 11

An analyst needs to compute a running total of sales for each department, ordered by date. Which window function is most appropriate?

Accepted Answer

SUM(sales) OVER (PARTITION BY department ORDER BY date). The running total of sales for each department, ordered by date, requires a window function that sums sales over a partition by department and orders by date. SUM(sales) OVER (PARTITION BY department ORDER BY date) computes a cumulative sum within each department as the date progresses, which is exactly a running total.

Answer

ROW_NUMBER() OVER (PARTITION BY department ORDER BY date)

Answer

SUM(sales) OVER (ORDER BY date)

Answer

LAG(sales, 1) OVER (PARTITION BY department ORDER BY date)

Question 12

In A/B testing, which factor is increased by having a larger sample size?

Accepted Answer

Statistical power. Statistical power is the probability of correctly rejecting a false null hypothesis (i.e., detecting a true effect). Increasing sample size reduces the standard error, making it easier to detect a true effect and thus increasing power. This is a fundamental principle in hypothesis testing and A/B testing.

Answer

P-value

Answer

Effect size

Answer

Type I error rate

Question 13

A data analyst is evaluating data quality issues during acquisition. Which TWO issues are most likely to arise from merging data from different sources? (Select exactly 2)

Accepted Answer

Duplicate records. Option B (Duplicate records) is correct because merging data from multiple sources commonly produces the same entity appearing in more than one source, and without deduplication via matching keys or fuzzy matching, redundant rows inflate counts and skew analysis. Option E (Formatting inconsistencies) is correct because different sources often represent the same field differently — for example dates as MM/DD/YYYY versus ISO 8601 YYYY-MM-DD, or units in metric versus imperial — requiring normalization before the data can be combined reliably. Option A (User access permissions) is an authorization/security concern rather than a data quality issue arising from the merge itself. Option C (Slow network speed) is a performance/infrastructure factor that affects transfer time, not the quality of the merged data. Option D (High storage cost) is a cost/resource consideration, not a data quality defect introduced by combining sources.

Answer

User access permissions

Answer

Slow network speed

Answer

High storage cost

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. 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 finds that the "Age" column contains values like "N/A", "unknown", and negative numbers. Which data quality dimension is primarily affected?

Accepted Answer

Validity. Validity refers to whether data values conform to the defined format, type, and allowable range for a field. 'N/A', 'unknown', and negative numbers in an Age column violate the expected numeric, non-negative domain, so validity is the primary dimension affected.

Answer

Accuracy

Answer

Consistency

Answer

Completeness