CompTIA Data+ DA0-002 · Free study guide

Objective 3.3 — Troubleshoot analysis issues

Troubleshooting replaces a vague symptom with tested evidence. “The dashboard is wrong” could mean stale data, a changed definition, failed connection, duplicated rows, security filters, or a corrupt source. Guessing can hide the symptom while preserving the cause. Use a repeatable process: confirm the problem, narrow its scope, test one layer at a time, repair the cause, and verify the result.

Preserve the failing input, query, error, and relevant logs before changing anything.

Use an evidence-first sequence

A reliable troubleshooting sequence is:

1. Define expected and observed behavior. Record the metric, value, period, filters, user, and time of the report.
2. Reproduce the symptom. Determine whether it occurs for one user, one data slice, one environment, or every run.
3. Identify recent changes. Check deployments, schema changes, credentials, source refreshes, and business-rule revisions.
4. Isolate the layer. Test connectivity, source data, transformation logic, query output, and presentation separately.
5. Form and test one hypothesis. Use a small, known input and change one variable at a time.
6. Apply the smallest defensible repair. Avoid broad changes that make cause and effect unclear.
7. Verify and monitor. Reconcile totals, test edge cases, confirm with the reporter, and watch the next scheduled run.
8. Document the cause. Record evidence, impact, repair, and prevention so the next incident starts with knowledge.

Diagnose connectivity and authentication failures

Connectivity means the client can reach the service. Authentication proves an identity. Authorization determines what that identity may do. These are related but distinct layers.

A timeout or name-resolution error suggests network path, DNS, endpoint, port, firewall, VPN, proxy, or service availability. A certificate error points toward TLS configuration, hostname mismatch, or an expired certificate. An “invalid credentials” response suggests a secret, token, account, or authentication-method problem. A “permission denied” response means identity may be valid but lacks authorization to the database, schema, table, row, or operation.

Start with the exact error and a minimal connection test. Verify endpoint and environment before rotating credentials. Check expiration, account lock, and policy changes. Never log passwords, access tokens, or full connection strings.

Investigate user-reported discrepancies

When a user reports a wrong number, first capture both numbers and how each was produced. Compare:

• Metric definitions, units, and inclusion rules
• Date range, time zone, and as-of timestamp
• Filters, parameters, and row-level access
• Source system and refresh time
• Level of detail and aggregation grain
• Treatment of nulls, cancellations, returns, and duplicates
• Rounding and currency conversion

A lower count for one user may reflect row-level security. Daily totals can differ because of time zones, and reports can disagree because one counts orders while another counts order lines. A confusing default filter or undocumented definition still needs correction.

Separate SQL syntax from logic errors

A syntax error prevents a query from being parsed or executed. Common causes include missing commas or parentheses, misspelled keywords, invalid aliases, and functions unsupported by that SQL dialect. The error message and vendor documentation often identify the failing location.

A logic error allows the query to run but returns the wrong result. These are more dangerous because success can look like correctness. Common causes include:

• An inner join that silently removes unmatched records
• A one-to-many join that multiplies a measure
• A filter in WHERE that changes the effect of an outer join
• Missing parentheses around mixed AND and OR conditions
• Counting rows when the question requires distinct entities
• Aggregating at the wrong grain
• Comparing incompatible types or mishandling null
• Filtering before or after aggregation at the wrong stage

Read the query in stages and reduce it to a known case. Compare row counts and key uniqueness after each join. DISTINCT is not a root-cause fix unless distinct rows are the defined output.

Detect corrupt or unexpectedly shaped data

Data can be readable and still violate its expected shape. A delimited file may change separator, quoting, encoding, column order, or header names. A JSON response may nest a field that was previously scalar. A numeric column may begin containing text, a file may be truncated, or a source may add columns without warning.

Validate files and tables before analysis. Useful checks include row count, column count, required fields, data types, uniqueness, null rate, plausible ranges, referential integrity, file size, and control totals. Compare these checks with a prior successful run and with source-system totals.

Quarantine malformed input rather than coercing every failure to null. Preserve the original; a checksum or controlled retransmission can distinguish source corruption from transfer failure.

Use logs, source validation, and trusted references

Good logs identify the run, time, environment, version, and result. Capture job identifiers, source files, stage row counts, duration, status, sanitized errors, and correlation identifiers without exposing secrets.

Validate the source independently. If it is correct but the transformed table is wrong, inspect acquisition or transformation. If both are correct, inspect semantic calculations, filters, cache, and presentation.

Use vendor documentation and release notes for syntax, errors, configuration, and version changes. Communities can suggest hypotheses, but confirm workarounds for the actual version in a safe environment.

Worked scenario: revenue jumps after a refresh

A sales dashboard shows revenue 12% above the finance ledger after its morning refresh. The analyst records the dashboard filters, UTC refresh time, and both totals. The discrepancy affects every user, so row-level security is unlikely. Source validation shows the ledger extract matches finance, and ingestion logs show the expected file and row count.

The analyst compares row counts through the query. Orders remain unique until they are joined to order lines and then to a product-tag table. Products can have several tags, so each order line is repeated once per tag. The query sums revenue after this many-to-many join. The dashboard did not fail because of stale data or rounding; its aggregation grain changed.

The repair applies the tag condition with an EXISTS semi-join, so a matching product line is retained once even when the product has several qualifying tags. An equivalent repair could join to a deduplicated set of eligible product IDs. A small test set with known orders now reconciles exactly. The analyst checks totals with and without tag filters, asks finance to confirm the corrected result, monitors the next refresh, and records the join-multiplication cause. Aggregating before the same one-to-many join or adding DISTINCT to final rows would not prove that each line contributed revenue only once.

Common exam traps

• Changing several layers at once before reproducing the problem.
• Treating authentication and authorization as the same failure.
• Rotating a credential when the endpoint or environment is wrong.
• Assuming a successful query must be logically correct.
• Using DISTINCT as a universal repair for join multiplication.
• Blaming a user before comparing filters, definitions, refresh times, and access scope.
• Cleaning a corrupt input in place and losing the original evidence.
• Logging secrets or sensitive rows during diagnosis.
• Trusting a community workaround without checking vendor documentation and version compatibility.
• Verifying only that an error disappeared rather than reconciling the repaired output.

Readiness checklist

• I can state expected and observed behavior with filters, time, user, and environment.
• I can narrow a problem by user, data slice, run, layer, and recent change.
• I can distinguish connectivity, authentication, and authorization failures.
• I can compare definitions, time zones, refreshes, grains, filters, and access when users report discrepancies.
• I can separate SQL syntax errors from logic errors that return plausible results.
• I can detect join multiplication, wrong-grain aggregation, null mistakes, and misplaced filters.
• I can validate file shape, schema, row counts, key rules, and control totals.
• I can use sanitized logs and independent source validation to locate the failing layer.
• I can use vendor documentation and communities appropriately.
• I can verify a repair against known results and document how recurrence will be detected.