DA0-002 Practice Test 24 — 15 Questions

Question 1

A retail company is integrating sales data from three regional databases into a central data warehouse. The 'product_id' column is defined as an integer in two databases but as a variable-length string in the third. During the ETL process, the analyst must ensure that product_id values are consistent for joining with the product dimension table. Which data transformation should the analyst perform?

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

Accepted Answer

Convert all product_id values to string. The product_id column must have a consistent data type across all sources to perform joins. Converting all values to string is the safest choice because it preserves leading zeros and any alphanumeric characters. Integer conversion could fail or lose information if the string column contains non-numeric values. String conversion ensures compatibility without data loss.

Answer

Apply data normalization to product_id

Answer

Convert all product_id values to integer

Answer

Use data aggregation on product_id

Question 2

A financial analyst publishes a monthly report where a gauge chart shows the current debt-to-equity ratio against a target band. Executives repeatedly misread the gauge, assuming the needle's position alone indicates good or bad performance without checking the target thresholds. Which change would BEST improve accurate interpretation?

Accepted Answer

Replace the gauge with a bullet chart showing the current value as a bar, the target as a marker, and qualitative ranges as background bands.. The executives are misreading the gauge because a needle's angle conveys no inherent good or bad without the reader mentally consulting the target, which they are skipping. A bullet chart solves this by placing the actual value, the target marker, and qualitative bands on one shared linear scale, so the comparison is explicit rather than inferred. Bigger gauges, binary color states, and pie charts each fail to make the target comparison legible.

Answer

Change the gauge's color zones so that the entire arc turns red whenever the ratio exceeds the target, regardless of how far.

Answer

Replace the gauge with a pie chart showing the debt and equity components as two slices of total capital.

Answer

Keep the gauge but enlarge it and add more tick labels around the arc so the needle's position is easier to read precisely.

Question 3

A marketing team wants a single view comparing this quarter's campaign performance against the same quarter last year across five channels, with the ability to drill from channel totals down to individual campaign rows. Which visualization structure best supports both the comparison and the drill path?

Accepted Answer

A matrix with channels as rows and the two quarters as columns, configured with a drill-down hierarchy from channel to campaign.. A matrix handles both demands at once: channels as rows give a stable axis for comparing the two quarter columns side by side, and the built-in row hierarchy lets a user expand a channel to reveal its campaigns. The comparison stays in one visual while the drill path descends the same hierarchy, avoiding a jump to a separate page.

Answer

A treemap sized by campaign spend with channels as top-level rectangles.

Answer

A bar chart of this quarter's channel totals with a separate bar chart of last year's totals placed beside it.

Answer

A line chart plotting campaign rows over time with one line per channel.

Question 4

A financial analyst is preparing a dataset of stock transactions for a machine learning model. The 'transaction_amount' column has a highly skewed distribution with a few extremely large values. The analyst decides to apply a logarithmic transformation to this column. Which statement best describes the effect of this transformation?

Accepted Answer

It reduces the impact of outliers by compressing the scale of large values.. The logarithmic transformation is used to reduce right skewness by compressing the scale of large values. This lessens the leverage of extreme outliers on statistical models without removing them. The transformation is monotonic, so it preserves the order of data points. It does not standardize the data or change its type; it simply alters the distribution shape to be more symmetric.

Answer

It eliminates all outliers from the dataset.

Answer

It normalizes the data to a mean of 0 and standard deviation of 1.

Answer

It converts the data from continuous to categorical.

Question 5

In Looker Studio, a data analyst has two data sources: one with customer IDs and transactions, and another with customer demographics. They need to create a chart that includes fields from both sources. Which feature should they use?

Accepted Answer

Data blending. Data blending in Looker Studio allows you to combine data from two or more separate data sources based on a common key (e.g., customer ID). This enables you to create a chart that uses fields from both the transactions source and the demographics source without needing to merge the data at the database level. Without blending, a single chart can only reference fields from one data source at a time.

Answer

Calculated fields

Answer

Community connectors

Answer

Dashboard filters

Question 6

A retail analytics team loads a nightly CSV export into their warehouse. During validation, the analyst notices that the 'order_date' column, defined as DATE in the target schema, contains values like '2023-13-45' and 'N/A' in several rows. The ETL job currently fails silently on these rows. Which data acquisition and preparation action BEST addresses the root cause while preserving as much data as possible?

Accepted Answer

Apply validation rules that flag invalid dates for quarantine while loading conforming rows, and log rejected records.. Validating at ingestion, quarantining malformed dates, and logging rejects keeps the pipeline resilient while surfacing the true defect. It loads every trustworthy row, gives the team evidence to fix the upstream export, and stops the silent-failure pattern. Changing types or imputing values merely relocates or amplifies the error.

Answer

Change the target column type from DATE to VARCHAR so all incoming values load without error.

Answer

Reject the entire nightly file and request a corrected export from the source system.

Answer

Impute today's date for any row where the order_date value cannot be parsed.

Question 7

A data analyst is preparing a report on customer satisfaction scores for the past quarter. The scores are measured on a scale from 1 to 5, and the analyst wants to show the distribution of scores, including the median and any outliers. Which visualization is most appropriate for this purpose?

Accepted Answer

A box plot showing the median, quartiles, and potential outliers.. A box plot is the most appropriate because it directly visualizes the median, quartiles, and outliers, which are exactly the elements the analyst wants to highlight. It provides a concise statistical summary of the distribution of satisfaction scores, allowing quick identification of central tendency and any unusual values. Other charts do not convey all these aspects effectively.

Answer

A histogram showing the frequency of each score.

Answer

A pie chart showing the percentage of customers in each score category.

Answer

A line chart showing the average satisfaction score over time.

Question 8

A data analyst has a time series of monthly sales data. They observe that sales are consistently higher every December and lower every January. Which component of time series does this pattern represent?

Accepted Answer

Seasonality. A pattern that repeats every December (high) and every January (low) is tied to the calendar year, which is the defining characteristic of seasonality. Seasonal components repeat at fixed, known periods — here, an annual cycle with monthly data.

Answer

Irregular

Answer

Cyclical

Answer

Trend

Question 9

A data analyst is assessing the quality of a newly acquired dataset from an external source. The analyst needs to evaluate two dimensions of data quality that directly affect the dataset's fitness for use in analysis. Which two dimensions should the analyst prioritize? (Choose two.)

Accepted Answer

Completeness. Completeness and accuracy are fundamental data quality dimensions that directly affect the validity of analysis. Completeness ensures no critical data is missing, while accuracy ensures the data correctly represents reality. These two dimensions are essential when evaluating an external dataset because they determine whether the data can be trusted for decision-making. Other dimensions like accessibility, portability, and scalability are more about operational aspects.

Answer

Accessibility

Answer

Portability

Answer

Scalability

Question 10

A retail analytics team is acquiring point-of-sale transaction files from 40 different store locations. Each store exports its nightly file with a different column order and slightly different header names (for example, 'TxnID' vs 'Transaction_ID' vs 'TransNo'). The analyst must load all files into a single normalized staging table before transformation. Which data preparation approach best addresses this inconsistency at acquisition time?

Accepted Answer

Create a source-to-target mapping for each store's file layout and apply a schema-on-read extraction that renames and reorders columns into a canonical staging schema.. The core issue is structural inconsistency across many source files, so the fix belongs at the acquisition boundary: define a canonical staging schema and map each source layout into it. Schema-on-read mapping handles renamed and reordered columns without changing upstream systems or delaying ingestion, and it creates documented lineage. Approaches that defer reconciliation to queries or force upstream re-exports either spread complexity or block progress.

Answer

Concatenate all raw files into one text file and parse columns positionally using the order from the largest store.

Answer

Instruct each store to re-export its file using the corporate header standard before the nightly transfer begins.

Answer

Load each file as-is into separate tables and let the BI tool join them by matching column names at query time.

Question 11

A data analyst is preparing a dataset for analysis and needs to combine data from two tables: 'orders' and 'customers'. The 'orders' table contains a 'customer_id' column, and the 'customers' table contains a 'customer_id' column as the primary key. The analyst wants to include all orders, even those without matching customers. Which type of join should be used?

Accepted Answer

LEFT JOIN. To preserve all rows from the orders table regardless of matching customers, a LEFT JOIN is appropriate. It keeps every order and appends customer details when available. Other join types either exclude unmatched orders or include extra unmatched customers, which is not desired.

Answer

RIGHT JOIN

Answer

INNER JOIN

Answer

FULL OUTER JOIN

Question 12

A retail analytics team needs to load a 40 GB CSV file of point-of-sale transactions into a cloud data warehouse nightly. The file is generated as a single object by an upstream system, and the team must minimize load time. Which approach best addresses the load performance bottleneck?

Accepted Answer

Split the file into multiple smaller files and load them in parallel.. The correct approach is to split the large file into multiple smaller files and load them in parallel. This leverages the distributed architecture of modern data warehouses, enabling concurrent ingestion and significantly reducing overall load time. Other options either do not address the parallelism bottleneck or introduce additional processing overhead that worsens performance.

Answer

Convert the CSV to JSON and load it as a semi-structured format.

Answer

Compress the file using gzip and load the single compressed file.

Answer

Load the file into a staging table using row-by-row inserts.

Question 13

A data analyst is integrating data from two source systems into a single customer dataset. Source A uses a customer ID format like 'CUST-12345', while Source B uses '12345'. Additionally, Source A records dates in 'MM/DD/YYYY' format, while Source B uses 'YYYY-MM-DD'. Which two data preparation tasks are essential to ensure the integrated dataset is consistent and usable? (Choose two.)

Accepted Answer

Convert all date values to a single standardized format.. The key challenges are inconsistent customer ID formats and date formats across sources. Standardizing both ensures records can be matched and time-based analysis is accurate. Encryption, missing value removal, and aggregation do not directly solve these format inconsistencies and may introduce other issues.

Answer

Apply encryption to all customer ID fields.

Answer

Remove all records with missing values.

Answer

Aggregate the data by customer to reduce row count.

Question 14

While profiling a customer dataset, an analyst finds that the 'country' column contains values including 'USA', 'United States', 'U.S.A.', and 'US' for the same nation, plus 'usa ' with trailing whitespace. Reports grouped by country show fragmented counts. Which preparation step resolves this issue?

Accepted Answer

Apply standardization by trimming whitespace and mapping all variants to a single canonical country code.. The column is not missing data; it holds the same nation under several spellings and spacing. Standardizing with trimming plus a canonical mapping such as an ISO code unifies those variants so grouped counts consolidate correctly. Deleting rows, widening the column, or imputing a single value either destroys data or leaves the fragmentation untouched.

Answer

Impute the mode of the column for every row to make all values identical.

Answer

Remove all rows containing any country value that is not 'USA' to enforce consistency.

Answer

Increase the column length to accommodate the longest variant string.

Question 15

A retail company generates a daily PDF report showing the previous day's sales by region and product category. The report is automatically emailed to store managers at 6:00 AM. Which type of report is this?

Accepted Answer

Scheduled report. A scheduled report is generated automatically at a predefined time (6:00 AM daily) and delivered to recipients (store managers) via email. This matches the definition of a scheduled report, which runs on a recurring basis without manual intervention.

Answer

Self-service report

Answer

Ad hoc report

Answer

Operational report