Why a public dataset reported negative $1 billion in revenue

One of our users wanted to analyze camp reservation data. It is public on recreation.gov, so we pulled the historical reservation data into BigQuery. Kudos to recreation.gov for making it public.

First thing we tried: calculate total revenue for 2024.

It came back as negative one billion dollars.

The query was fine. The problem was two rows out of 9.8 million with a placeholder amount of -666,666,660 sitting in the total field. Leave them in and 2024 shows -$1.04 billion. Filter them out and it is +$293 million, about the same as every other year.

A few other gotchas we found in the same table:

• nights is stored as text like 3 days, so casting it to a number quietly drops half the rows
• state is stored as full names, so WHERE state = 'CA' returns nothing, and it looks like California had no bookings

None of these throw an error. The query just runs and hands you a wrong number.

If you have worked with real data pulled from an external source, you know this is the norm, not an exception.

Writing it down as context

So we wrote all of it down as context, so the next time an AI agent queries this data it already knows:

• bound revenue to skip those two junk rows, but keep the real $0 permits and the actual refunds
• parse the leading number out of nights instead of casting it
• state is a full name, so to filter by state you join to the facilities table
• fiscal 2026 is only a partial year, so leave it out of any trend

The same holds for your own warehouse. Connecting Claude to BigQuery or Postgres is the easy part. The answers come back right because of the context underneath: what each column actually holds, and how a metric is really defined.

If you are curious, you can run queries against this same Recreation.gov data yourself, no login needed: try the playground.