-
Step 1: Write the output contract
What to do: List the required tables, columns, data types, calculations, joins, filters, time period, and final delivery format. Include the business meaning of each important field instead of relying on column names alone.
Success looks like: Another analyst can read the specification and describe what a correct result should contain.
Common mistake to avoid: Do not ask AI to “clean this data” without defining what clean means for the project.
-
Step 2: Inventory and profile the sources
What to do: Identify every workbook, document, scan, or export involved, then inspect headers, row counts, duplicate identifiers, date ranges, blank fields, and inconsistent types. Keep missing values distinct from zeros and record any suppressed or unavailable periods.
Success looks like: You know which source is authoritative for each output field and which files require special handling.
Common mistake to avoid: Never silently replace missing observations with zero or interpolate a period that was not provided.
-
Step 3: Ask AI to propose the transformation plan
What to do: Give AI the source schema and output contract, then request a step sequence for importing, promoting headers, changing types, removing duplicates, joining tables, handling exceptions, and aggregating results. Ask it to state assumptions before generating code or formulas.
Success looks like: Each transformation has a clear purpose, an input, an output, and a reason it belongs in the sequence.
Common mistake to avoid: Do not accept a plausible-looking query if the join keys, grain, or filter behavior has not been explained.
-
Step 4: Build the query in small testable stages
What to do: Implement the workflow in checkpoints such as source import, normalization, joins, business rules, aggregation, and output shaping. Test each stage with row counts, sample records, and totals before moving to the next stage.
Success looks like: A failed result can be isolated to one stage rather than requiring a complete rebuild.
Common mistake to avoid: Avoid a single opaque step that combines cleaning, joining, calculation, and formatting.
-
Step 5: Add exception and data-quality handling
What to do: Define behavior for blank identifiers, duplicate records, malformed dates, missing quarters, unexpected categories, and conflicting source values. Produce an exception table or audit-trail tab instead of hiding rejected rows.
Success looks like: The workflow shows what it included, what it excluded, and why.
Common mistake to avoid: Do not use error-removal steps that discard evidence without preserving the affected records.
-
Step 6: Create the reporting layer
What to do: Build the final workbook, dashboard, or report from the validated output tables. Use formulas, roll-ups, variance calculations, and charts that answer the original question without changing the underlying transformations.
Success looks like: A stakeholder can understand the result while a technical reviewer can follow it back to the source.
Common mistake to avoid: Do not format a visual discrepancy away or treat presentation totals as a substitute for source validation.
-
Step 7: Run an independent audit
What to do: Recompute key numbers independently, compare them with references, and trace material values to their exact source file, row, and field. Energent Audit is designed to audit work produced by other AI systems and return a pass/fail verdict with evidence.
Success looks like: The report has a reviewable chain from output to source and any failure has a documented explanation.
Common mistake to avoid: Do not use the same generation step as the only reviewer of its own result.
-
Step 8: Save the corrected workflow
What to do: Name the workflow, preserve the tested rules, document the source assumptions, and schedule the next run with the same checks. When a correction is discovered, update the reusable process so the correction becomes a persistent audit rule.
Success looks like: The next file set can be processed consistently without recreating the original prompt sequence.
Common mistake to avoid: Do not save only the final workbook while losing the transformation logic and validation history.
