Building a recurring CSV report that survives next month's file

The recurring report is the least glamorous piece of data work and the one that eats the most time. A file arrives, you do fourteen things to it, you paste some numbers into a deck, and next month you do it again slightly differently because the file changed slightly and you did not notice until the totals looked wrong.

This is a guide to building it so that next month costs ten minutes instead of two hours, and so that when the file does change you find out from a check rather than from a colleague.

What actually drifts, in order of frequency

In my experience these are the changes that break a monthly report, roughly in order of how often they happen. Knowing the list is most of the defence.

Notice the pattern: the changes that break loudly are the cheap ones. The expensive ones are the changes that leave you with a report that looks completely normal and is wrong, which is why the checks in this guide are weighted toward those.

Step one: write down the contract

Before building anything, write down what you are assuming about the file. Not in your head. In a comment, a wiki page, or the name of the first pipeline step.

• Required columns and their types. The five you actually use, not all forty.
• The grain. One row per what? Order, order line, customer-month?
• The date range it should cover and what "this month" means in it.
• Roughly how many rows a normal month produces.
• One control total you can compare against a source outside this file, ideally a number somebody else also reports.

That last one is worth the effort of finding. A total you can check against the finance system, or against a dashboard someone else owns, turns "the pipeline ran" into "the answer is right", and those are very different claims.

Fix it: profile last month's file and write the contract from what is actually in it →

Step two: build the pipeline in layers

A pipeline that mixes cleaning with business logic is a pipeline nobody can debug. Keep them in three separate blocks, in this order, and the whole thing becomes readable.

Layer one: make the file loadable

Encoding, delimiter, skipped preamble rows, date format. These are properties of the export, not of your analysis. Pin them explicitly rather than relying on detection, so that a change here fails loudly instead of being absorbed.

Layer two: make the data trustworthy

Trim whitespace, normalize the categorical spellings, cast the numeric columns, drop the empty rows, deduplicate. Nothing in this layer should depend on what the report is about; it would be the same cleaning for any question.

One habit worth adopting: do not delete rows in this layer, flag them. A step that filters out rows with a missing customer id makes them invisible. A step that adds a quality_issue column lets you count them, and the count is a signal.

Layer three: answer the question

Filters, joins, derived columns, the aggregation. This is the only layer that changes when someone asks a different question, and keeping it last means you can rewrite it without touching the two layers below.

SELECT DATE_TRUNC('month', order_date) AS month,
       region,
       COUNT(DISTINCT order_id)        AS orders,
       SUM(revenue)                    AS revenue
FROM data
WHERE status <> 'cancelled'
GROUP BY 1, 2
ORDER BY 1, 2;

Fix it: build the three layers as pipeline steps you can re-run →

Step three: the four checks, before you send anything

Four numbers, every month, in the same order. This takes two minutes and it is the difference between a report you trust and a report you hope about.

1. Row count against last month

Not an exact match, a plausible one. A monthly export that was 41,200 rows and is now 38,900 is fine. One that is now 12,000 or 91,000 is a question, and the answer is usually a truncated extract or a duplicated append.

2. The date range

SELECT MIN(order_date) AS first_day,
       MAX(order_date) AS last_day,
       COUNT(DISTINCT DATE_TRUNC('day', order_date)) AS days_present
FROM data;

Three things to look at. Does the range match the period you asked for? Is days_present what you expect, allowing for weekends? And is the maximum date suspiciously early, which usually means the extract was run before the period closed.

3. The control total

The number from your contract, compared against its external source. If they disagree, stop. Do not send a report and mention the discrepancy in the email; find it first, because the recipient will not read the caveat and will quote the number.

4. New and disappeared categories

SELECT region, COUNT(*) AS rows
FROM data
GROUP BY region
ORDER BY rows DESC;

Compare that list to last month's. A region that appeared is either a genuine expansion or a spelling variant of one you already have. A region that vanished is either a closure or a filter that started excluding it. Both are worth thirty seconds.

Fix it: diff this month's file against last month's by key column →

The mistake that causes most wrong numbers

Almost every seriously wrong recurring report I have seen came from the same cause: a join or an explode changed the grain, and an aggregate afterwards was computed on the new grain as though it were the old one.

The shape is always similar. You join orders to order lines to get product detail. The order table had one row per order with an order_total. After the join there are four rows per order, each carrying the same total, and SUM(order_total) is now four times too large.

Three rules that prevent it entirely.

• Check the row count immediately after every join. If it grew, the join is one-to-many and you need to know that before the next step.
• Count distinct on the parent key rather than counting rows, once the grain has changed.
• Take MAX, not SUM, of a parent-level measure inside a group. Every row in the group carries the same value, so the maximum is the value.

A one-line assertion is enough to catch it:

SELECT COUNT(*) AS rows, COUNT(DISTINCT order_id) AS orders
FROM data;
-- rows > orders means the grain is line-level. Aggregate accordingly.

Fix it: the exploding guide covers the same trap from the JSON side →

Step four: make it survive you

A monthly report that only one person can run is a liability, and it is usually a liability discovered while that person is on holiday.

Three things make it transferable. Name every pipeline step after what it does in business terms rather than in tool terms: "exclude cancelled orders", not "filter 3". Keep the contract from step one written down next to the pipeline. And record the four checks with last month's values, so the next person has something to compare against rather than a vague sense that the numbers look about right.

A final honesty note about tooling. A saved pipeline you re-run by dropping a file is the right answer for a monthly report on a file somebody emails you, and it is what I use. It is not the right answer for something that must run unattended at 6am on a schedule. That needs a server and a scheduler, and the pipeline you built here is a good specification to hand whoever builds it, because every step is already written as SQL.

Fix it: read out the SQL for each step and take it somewhere else →