Version Controlling the Query Behind an Excel Report

The finance director asks why September revenue is 40,000 lower in this month's pack than it was in last month's. The analyst who wrote the query has changed teams. The SQL lives in an email from last spring, in a file called final_v3_FIXED.sql, and in a data connection inside a workbook that three people have edited. Nobody can say what changed, or when, or why. This is a solvable problem, and the solution is the one software teams settled on decades ago: put the query under version control. This article shows a repository layout, the habits that make the history useful, and how to tie each workbook back to a commit. It applies to every report in building Excel reports from SQL queries people trust.

What belongs in the repository

Everything needed to rebuild a report from scratch:

• The SQL files, one per query.

• The generator that runs them and writes the workbook.

• A configuration file with report names, sheet names and schedules, but no passwords.

• Tests and a small fixture dataset.

• A short README for the humans maintaining it.

What does not belong: credentials (use environment variables or a secret store), the output workbooks (they are build products, and they may contain personal data) and large data extracts. Add *.xlsx and .env to .gitignore on day one.

reports/
  sql/
    monthly_sales.sql
    revenue_by_region.sql
    ar_ageing.sql
  build_report.py
  config.toml
  tests/
    fixtures/seed.sql
    test_monthly_sales.py
  README.md
  .gitignore

A report with several sheets has one SQL file per sheet, named after the sheet. Splitting like that keeps diffs small and lets a reviewer read one query at a time.

Write SQL that diffs well

A version history is only as useful as the diffs it holds. A few habits make the SQL friendly to line-based tools.

One column per line in the select list, one condition per line in the WHERE. A change to one column is a one-line diff.

Consistent formatting. Agree on keyword case and indentation, and use a formatter such as sqlfluff or pg_format in a pre-commit hook so style arguments never reach review.

Parameters, not literals. The period is %(start)s and %(end)s, bound at run time. If the SQL contains '2026-09-01', every month produces a commit that changes the date, and the real changes are lost in the noise.

Comments that explain rules. A comment on the line that excludes test accounts, with a pointer to the decision, is worth more than the same words in a wiki that nobody opens.

-- Revenue = invoiced amount net of credit notes, by invoice date.
-- Test accounts are excluded (decision: finance, 2026-03-12).
SELECT
  date_trunc('month', i.invoiced_at)::date AS month,
  c.region,
  sum(i.amount - coalesce(cn.amount, 0))   AS net_revenue
FROM invoices i
LEFT JOIN credit_notes cn ON cn.invoice_id = i.id
JOIN customers c          ON c.id = i.customer_id
WHERE i.invoiced_at >= %(start)s
  AND i.invoiced_at <  %(end)s
  AND c.is_test = false
GROUP BY 1, 2
ORDER BY 1, 2;

Commit messages and review

Treat a change to a report query as a change to a published number, because it is. The commit message should say what changed and why, in business words, and note whether published figures move.

Exclude pending credit notes from net revenue

Finance confirmed pending credit notes should not reduce revenue until
approved. Affects Sept 2026 onward; August 2026 pack is unchanged.
Net revenue for September falls by 41,300 compared with the old query.

Use pull requests for changes to anything that feeds a pack or a filing. The reviewer reads the diff, asks whether the totals changed, and checks that the README of the report was updated if a definition moved. A second pair of eyes catches the join that now doubles a row more reliably than any test.

Separate commits for separate purposes: a formatting change must not hide a logic change. Reformatting the whole file in the same commit as a one-line fix makes the fix invisible.

Tag the definition changes

Most queries change slowly, and a few changes matter a great deal. When a definition changes, tag the commit with a name that the business recognizes: revenue-def-2026-10. The tag marks the boundary between two meanings of the same word. It lets you answer "which definition did the August pack use?" by checking out the tag before it.

For comparability, keep a note in the report's README sheet: "Revenue definition changed on 2026-10-01; prior periods restated to match" or "not restated". Choose and say which (producing a monthly management pack from SQL covers restatements).

Test the query

A query can be tested with a small fixture database, built from a SQL file of inserts, in a throwaway PostgreSQL or SQLite instance. The tests assert the shape and a few hand-computed values.

def test_net_revenue_excludes_test_accounts(db):
    rows = db.execute(open("sql/monthly_sales.sql").read(),
                      {"start": "2026-09-01", "end": "2026-10-01"}).fetchall()
    by_region = {r["region"]: r["net_revenue"] for r in rows}
    assert by_region["north"] == 1200.00
    assert "test" not in by_region

Fixtures include the awkward cases: a refund in the next month, an order at 23:59 on the last day, a customer with two addresses. A test that runs in seconds on every commit protects the report from the quiet breakages that come with schema changes. Pair it with the checks that run on the real data every month, such as tie-outs to the ledger (testing a generated Excel report).

Write the commit into the workbook

The link from a file to its source is the commit hash. Have the generator read it and put it on the README sheet, as in putting a README sheet into every generated workbook:

import subprocess
commit = subprocess.run(["git", "rev-parse", "--short", "HEAD"], capture_output=True, text=True).stdout.strip()

Ensure the working tree was clean when the report was built; otherwise the hash does not describe what ran. A simple guard refuses to build a distributed report from a dirty tree:

dirty = subprocess.run(["git", "status", "--porcelain"], capture_output=True, text=True).stdout.strip()
if dirty and not os.environ.get("ALLOW_DIRTY"):
    raise SystemExit("working tree has uncommitted changes")

With the hash in the workbook, the director's question has an answer: check out the September pack's hash and the October pack's hash and run git diff between them, limited to sql/.

Where the queries run

Run production reports from a tagged release or the main branch on a server, not from an analyst's laptop. A scheduled job pulls the repository, runs the tests and builds the pack. If a hotfix is needed, make it in a branch, review it, merge it and rebuild. The rule that the file on the server always equals a commit prevents the situation where the pack was built with an edit nobody saw.

Keep database credentials out of the repository by reading them from the environment. Give the job a read-only role. Store the build logs with the commit hash and the check results, so an auditor can see what ran.

Moving existing reports in

Most teams start with queries already living in workbooks as data connections or in Power Query steps. Moving them is a one-time job: open the connection properties, copy the SQL into a file, parameterize the dates, and rebuild the workbook from the generator. Compare the new output to the old workbook to the cent before switching over. Power Query's M code can also be exported and stored; it is text, and it diffs (reading the M code Power Query writes shows where to find it). Treat the old workbook as read-only history once the new build is verified.

When version control alone is not the answer

Version control records what changed and does not decide what is right. A reviewed query can still define revenue badly. It does not replace agreement on definitions with the business, and a data dictionary that outlives the analyst helps there. It does not capture changes in the data itself: a corrected invoice changes the report without any commit, and only a snapshot or a restatement note covers that. And it does not suit the very small case, a one-off analysis nobody will run twice, where a dated file beside the output is enough. The Pro Git book is the standard reference for the mechanics, and sqlfluff documents the SQL formatter and linter mentioned above.