Tools and resources for fixing an analysis

Why this matters

When an analysis breaks, it either stops with an error or, worse, keeps running and produces a wrong answer. Fixing it is a skill with its own tools and habits: reading what the system is telling you, knowing where definitions live, asking the right person, finding what changed, and shrinking the problem until the cause is visible.

The exam's objective here is to use tools and resources to resolve analysis problems. This lesson covers error messages and query plans, documentation and data owners, peer review, version history, and isolating a problem with smaller data.

The lesson

Reading error messages and query plans

An error message is the most direct help a system gives, and it is often skimmed rather than read. Most messages say three useful things: what went wrong ("no such column", "division by zero", "invalid date format"), where (a line number, a column name, a row), and sometimes why.

Common analytical errors fall into a few groups:

• Syntax errors: a missing comma, an unclosed bracket, a misspelled keyword. The message usually points at or just after the problem.
• Reference errors: a table or column that does not exist, often because of a typing mistake, a renamed field or the wrong database.
• Type errors: comparing text with a number, or calculating with a value that is not numeric.
• Constraint and permission errors: a duplicate key, a value out of range, or an account not allowed to read a table.
• Resource errors: a query that runs out of memory or times out.

For slow queries there is a second tool: the query plan. Most databases can show how they intend to run a query, through a command such as EXPLAIN. The plan shows whether the database will scan a whole table or use an index, the order it will join tables, and where the cost lies. A full scan of a huge table to find a handful of rows, or a join producing millions of intermediate rows, explains a slow query and points to the fix: a filter, an index, or a restructured join.

Error messages and plans can be searched: the exact text of an error, pasted into a search engine or the product's documentation, usually finds others who have met it.

Documentation, data dictionaries and asking the data owner

Many analysis problems are not errors in code but misunderstandings of data. Resolving them needs information that is not in the data itself.

Product documentation explains how functions behave: how a tool handles NULLs, which date formats it accepts, how it rounds. A function that seems to give the wrong answer is often behaving exactly as documented.

A data dictionary explains what each field means, its type, its allowed values and its source. "Why are some order totals negative?" may be answered in one line: "negative totals are refunds".

Metadata and lineage records show where a field came from and what transformed it, which is the fastest route to finding which step introduced a problem.

When documentation is missing or unclear, the data owner or data steward is the resource: the person or team responsible for the data. They know the business rules, the historical changes and the known problems that no document records. Asking early, with a specific question and an example record, saves more time than any amount of guessing.

Peer review and pairing on an analysis

The person who wrote an analysis is the worst placed to find its errors, because they read what they meant rather than what they wrote. A second pair of eyes is one of the most effective quality tools there is.

Peer review means another analyst checks the work before it is released: the logic, the query, the definitions, the numbers and the conclusions. A useful review focuses on the things most likely to be wrong: joins and their row counts, filters, definitions of measures, handling of missing values, and whether the conclusion follows from the evidence.

Pairing, working on a problem together in real time, is especially effective for debugging, because explaining the problem aloud often reveals it. Even explaining it to someone who knows nothing about the data, or describing it in writing step by step, can have the same effect.

Review is more effective when the work is reproducible: a reviewer who can run the code and see the intermediate results can check far more than one looking at a final chart.

Version history: what changed since it last worked

When an analysis that used to work stops working, or starts giving different numbers, the most productive question is: what changed?

Three kinds of change are common. The code changed: a query was edited, a filter added, a definition updated. The data changed: new records, a schema change, a renamed field, a late load. The environment changed: a library was upgraded, a tool updated, a connection moved.

Version control answers the first directly: comparing the current code with the last version that worked shows every line that changed and who changed it. Pipeline and load logs answer the second: when the data was last refreshed, how many rows arrived, and whether anything failed. A record of environment versions, such as a requirements file, answers the third.

A systematic way to use history is to find the last known good state and the first known bad state, then narrow the gap between them, testing changes one at a time until the one that caused the problem is found.

Isolating a problem with a smaller data set

Large data hides problems: a query takes minutes to run, and the few rows that cause an error are lost among millions. Making the problem smaller makes it visible.

Useful techniques include:

• Running on a sample, such as one day or one customer, so each attempt takes seconds.
• Finding a minimal example: the smallest set of rows that still reproduces the problem, often only two or three.
• Checking intermediate results: running a multi-step analysis one step at a time and checking row counts and totals after each, to find the step where the numbers first go wrong.
• Removing parts: taking out joins, filters or calculations one at a time until the problem disappears, which identifies the part that caused it.
• Testing with known answers: running the logic on a tiny data set whose correct result can be calculated by hand.

Once the cause is found and fixed on the small case, the fix is checked on the full data, and the minimal example can become a test that stops the problem from returning.