Practical guide
AI fluency for data analysts: verify a join before reporting
Use a small fictional dataset to test row grain, duplicated measures and a reproducible analytical handoff.
A data analyst can use AI to write transformations, but must verify that those transformations preserve the intended meaning of the data. This original exercise focuses on joining orders to line items without duplicating order-level measures. Its small fictional dataset makes the error inspectable by hand, so the review does not depend on trusting generated code or a plausible dashboard.
Write the grain of each input
Describe what one row represents before joining tables.
The fictional orders table has one row per order, while the line-items table has one row per item within an order. An order can therefore match several line items. Write these definitions and identify the join key. Do not assume a field named id has the same meaning in both tables.
Ask AI to explain the expected relationship using the supplied schema. Then inspect a few records yourself. If the pack does not establish uniqueness, test it rather than treating the assistant's explanation as evidence. The intended output grain must also be explicit.
Create a hand-checkable counterexample
Use a tiny dataset to expose duplication before scaling the transformation.
In the exercise, order A has a total of one hundred and two line items; order B has a total of fifty and one line item. The correct sum of the order totals is one hundred and fifty. Joining to line items repeats order A's total twice, so summing that repeated field would produce two hundred and fifty.
These values are synthetic and illustrate the mechanism, not real business results. Ask the participant to predict row counts and totals before executing code. A mismatch between prediction and output is a prompt to investigate, not to adjust the expected answer until the test passes.
Choose a transformation that matches the question
Repair the grain mismatch rather than hiding it with an arbitrary deduplication.
If the question concerns total order value, calculate from the order-level table or use a method that preserves one contribution per order. If it concerns item categories, define how measures should be allocated or calculated at that level. The right transformation depends on the metric, not just the database syntax.
Do not apply a distinct sum of amounts as a generic repair, because separate orders can legitimately share the same amount. Ask AI to propose an approach, then test it with equal-valued orders and multiple items. Keep the metric definition alongside the code.
Check unmatched records and missing values
A correct total on matched rows does not establish that the full population was represented.
Introduce a line item with no matching order and an order with no listed items. Ask how each should appear in the output given the analytical question. Preserve exceptions for review rather than discarding them silently. Record the join type and its effect on row inclusion.
Test missing keys and duplicate order identifiers if they are possible in the supplied pack. The participant should explain which conditions are data-quality issues and which reflect a legitimate relationship. A successful query execution only establishes that the query ran, not that its result answers the intended question.
Hand over a reproducible result and its limitations
The final artifact should let another analyst inspect the definition, transformation and checks.
Include the metric definition, input grain, transformation, expected test outcomes and unresolved exceptions. Keep code or calculation steps available in the format used for the exercise. Explain any remaining limitation in the business summary rather than hiding it only in technical notes.
Review whether the participant caught duplication, tested edge cases and preserved the question's meaning. Keep the conclusion task-specific. The broader data function guide covers research interpretation and metric design; this role exercise concentrates on a concrete transformation failure that can be reproduced and checked.
Sources and scope
NIST addresses AI risk management. Skills England describes workplace AI foundations.
These sources provide background, not endorsement of this exercise. The worked example and suggested review method are original illustrative guidance. They are not customer results, validated benchmarks or evidence of a particular product capability. Adapt the exercise to the task and use qualified review where consequences require it.
Sources: [1] [2]
Sources
- 1.AI RMF Core · NIST
- 2.AI foundation skills for work benchmark · Skills England