Business Knowledge Is Still King: Agentic Data Analysis Without a Data Team

Initial Query: Outline the Questions and Share the Data Sources

So what are usually the steps when you analyze data, pretending to be a domain expert, an energy analyst at a Swiss utility, who knows how to buy electricity ahead on the day-ahead market and knows the yearly forecasting errors in the shoulder season, when heating kicks in?

To bring you along on the journey of being a data expert in Swiss temperature, these are the data points that are publicly available. Three sources with different formats: MeteoSwiss SMN (weather stations) + Swissgrid Excel + cantons GeoJSON:

With data from:

• Stations meta: 159 stations, 24 cantons plus Liechtenstein, WGS84 lat/lon included (WGS84 is a global standard geodetic reference system used in GIS, without needing polygon data).
• Bern (capital city of Switzerland) has a 10-min history 2020-2025: 315,648 rows per station per decade file. With 159 stations, that is about 50M rows for 10-min data if all are loaded.
• Bern daily since 1864: 59,170 rows (data for 162 years)
• The Swissgrid Swiss Energy overview has 2026 with 26,204 15-min rows, Jan 1 → end Sept.
• We can join data spatially with stations → cantons. The temp-vs-energy correlation we have: April -0.72, July +0.24 (heating vs. cooling).

So with the overview and the knowledge of us being the expert, we can ask something like this in natural language:

I'd like to understand how strongly temperature drives Swiss electricity consumption this year. I have two data sources. Swissgrid publishes the "Energieübersicht Schweiz" as an Excel file every month, you'll find it here: https://www.swissgrid.ch/dam/jcr:805e525c-44fe-4701-a227-6144193257ac/EnergieUebersichtCH-2026.xlsx. The sheet "Zeitreihen0h15" contains 15-minute values in kWh. The column I care about is "Summe endverbrauchte Energie Regelblock Schweiz" (end-user consumption), not total consumption, because the total includes pump storage and grid losses. The sheet also has consumption per canton further to the right, but note that Swissgrid groups some cantons together, for example BE with JU and ZH with SH.

For the weather, MeteoSwiss publishes all its automatic stations as open data here: https://data.geo.admin.ch/browser/index.html#/collections/ch.meteoschweiz.ogd-smn. Every station has a folder with daily, hourly and 10-minute files. The daily files are what I need. The parameter for daily mean air temperature at 2 metres is called tre200d0. There's a station list with coordinates and canton in ogd-smn_meta_stations.csv.

Please start with Bern (station BER) as a proxy for the Swiss Plateau. Give me the daily end-user consumption against the daily mean temperature for 2026 so far, and tell me from which temperature downwards consumption starts rising. In our industry we call that the heating threshold, we usually assume around 12 °C. I want to see if that still holds.

Also tell me if anything in the data looks off before you trust it.

As you can see, the query is very specific². That's on purpose, as the domain expert knows exactly what he wants or needs.

Claude will go ahead using all the tools and APIs provided by MotherDuck and try to build what we asked for:

It asks us to create a Flight (a data pipeline in MotherDuck, you see it uses Python with DuckDB, requests, pandas, openpyxl, and numpy). Let's go ahead and allow always. Next, it asks 'to run flight'. Allow always again.

After a couple of minutes, and many iterations by Claude + MotherDuck and the tools mentioned above, it created a first overview within Claude:

It goes on to verify the data. As with all things data, if the numbers are wrong, any dashboard or insight is useless, or even worse, dangerous.

Copy code
def holidays(y):
    e, D = easter(y), dt.timedelta
    return {
        dt.date(y, 1, 1): "Neujahr",
        dt.date(y, 1, 2): "Berchtoldstag",
        e - D(2): "Karfreitag",
        e + D(1): "Ostermontag",
        e + D(39): "Auffahrt",
        e + D(50): "Pfingstmontag",
        dt.date(y, 8, 1): "Bundesfeier",
        dt.date(y, 12, 25): "Weihnachten",
        dt.date(y, 12, 26): "Stephanstag",
    }

What the Agent (Claude in This Case) Found

Now that Claude has run successfully for the first prompt, let's see what it found on top of the artifacts created:

So there are a lot of insights, some of them quite basic, such as holiday and public holiday periods running low, but this is now backed with actual numbers and data. Interestingly, Feb-Sept was 1.3-3.2°C above the 10-year average.

Remember, that was just one prompt, and we didn't specify a lot. Now we can take it from here and go on.

Verify Before Trusting

Unfortunately, in real life, I'm not an expert on this topic, so I have a hard time classifying the result. We'd need an actual expert. But what we can do is re-prompt to ask the model to verify. I did it with:

13.5 °C is above the 12 °C we use on the desk, and you said 2026 was a warm year. Before I show this to anyone: is the threshold shift real, or an artefact of a warm year? Swissgrid's 2025 file is here: https://www.swissgrid.ch/dam/jcr:bbbbadc6-3656-4dfe-b81e-bbb9ad01be79/EnergieUebersichtCH-2025.xlsx. Redo the fit for 2025 and tell me if we land at the same threshold.

The verification said:

Yes, the shift is real, not a warm-year artefact. In 2025, which was close to normal, the same method gives an even higher threshold than in 2026. Bern only (your original question):

Consumption-weighted temperature:

More pointers

• The two years agree. Each year's estimate falls inside the other's range, so they point to the same answer: about 13.5–14.5 °C at Bern.
• 12 °C doesn't hold up. It sat at the edge of the 2026 range. In 2025 it's clearly outside, and in neither year is it the best fit.
• 2025 is a fair test. At Bern, Jan–Sep 2025 was within −1.1 to +2.2 °C of the 10-year normal each month, mostly within ±1 °C. 2026 ran +1.3 to +3.2 °C warmer from February on. The warm year actually produced the lower threshold, the opposite of what an artefact would do.
• The rest of the model is stable. Each degree below the threshold adds 3.8 GWh/day in 2025 and 4.0 in 2026. The warm-weekday base rose from 134 to 137 GWh/day.

Full-year 2025 isn't the number to quote. It gives 15.2 °C at Bern, but 22–31 December runs 15–29 GWh/day below the model. The model doesn't know about the Christmas shutdown, so those cold days pull the threshold up. Same-window comparison is the fair one.

So we see, it corrected itself. That's why it's always worth verifying manually or just asking another question, to understand better what the number means, taking some samples, and then re-running some automated tests or following up.

Here it found a bug with the timestamp:

The 2025 file stores timestamps as text (13.01.2025 00:15), while 2026 uses real Excel dates. My first 2025 run read 5 March as 3 May and lost 61% of the rows. I cancelled it before it wrote anything. The Flight now parses dates only in that explicit format, and stops before fitting if a timestamp fails to parse, a day is missing or time runs backwards. The 2026 results aren't affected: that file has proper dates and passed every completeness check. I've recorded this in the data guide.

What Did We Learn?

What did we learn? All of this goes to show that real insight and business knowledge are still king (and probably will be forever), wherever you work. Someone needs to take responsibility and have the conviction to drive an issue or a point, and that will always be the domain expert on a topic. Also, all of these "triggered verifications" are non-deterministic, so if you run it 2-3 more times, it might find different bugs or things to fix, which again shows that models are best used with a human in the driver's seat.

Let's See It on a Map: Spatial Joins, Polygons, and Map Visualization

But now let's do some more fun analysis. Let's put it on a map. All data must land on a map to be a proper data analysis, right? 😉

As it suggests during the process, we do weighted temperatures across stations. As I want to see it on a map with GIS capabilities (as I know there is longitude and latitude data in there), we can prompt:

You suggested a weighted temperature across stations instead of Bern only. Agreed. Use one representative station per canton, weight each canton by its share of consumption in the Swissgrid canton columns, and skip stations above 1500 m, nobody heats a home up there. Then show me the stations on a map of Switzerland with the canton outlines, coloured by this year's mean temperature. Canton outlines you can take from here: https://raw.githubusercontent.com/codeforgermany/click_that_hood/main/public/data/switzerland.geojson

Note: the outlines are rough, trust the canton column in the station list.

The outcome looks like this inside Claude:

And the response:

With the consumption-weighted temperature, the heating threshold comes out at about 14 °C, so 12 °C fits even less well than with Bern alone. The Flight now builds this temperature on every run, and the map above shows the 22 stations it uses.

Outcomes:

• Better fit: the weighted temperature explains consumption better than Bern alone, with R² rising from 0.924 to 0.936.
• 12 °C: it now falls outside the plausible range unless sunshine is in the model, and even then it sits at the edge.
• Why the threshold rose: the weighted temperature runs about 0.6 °C warmer than Bern on average, because Geneva, Lausanne, Basel, Sion and Lugano carry real weight. That explains most of the shift.
• What the threshold depends on: the threshold depends on which temperature you measure against, so compare it with your 12 °C rule using the same temperature reference. If your 12 °C rule comes from the Swiss heating-degree-day convention (20/12), that's a building-standard definition rather than a fitted value, which would explain part of the gap.

And so on... you see, it found another gap, and depending on the data being correct or not, we can now take new directional steps, which we didn't know beforehand, maybe even as domain experts. The data is always different from what we think.

The insights are interesting, but we want to focus on the features and the workflow here (if you are interested, you can always run it yourself with one prompt :)

I asked Claude to create a Dive in MD for me:

Can you create the map inside MotherDuck Dives please?

This is the interactive Dive within MotherDuck, ready to share with the team, versioned and able to change status. Nice, isn't it?

Also available in the dive gallery.

You get a presentation link that you can share now with the team.

Lastly: Automate, Real-Time Query, Create a Dashboard, and Automate It

This would be the last query. After everything is verified and looking good, you want to have it updated automatically, and you want a dashboard to share with a link.

This could be a prompt for the above:

What's Bern doing right now, has it dropped below 13 °C in the last 24 hours? I want the 10-minute values, not daily averages.

Then put everything into one dashboard for the trading desk: the threshold chart, the map, and the live Bern temperature at the top. Add a button that pulls fresh data, and make it update itself every morning at 7 before the desk opens.

The outcome:

Yes. Bern has been below 13 °C for the entire last 24 hours: all 144 ten-minute readings.

• Now: 8.6 °C at 12:00 today, warming from the overnight low.
• Closest it came: 12.4 °C yesterday around 13:00–14:00.
• Overnight low: 3.3 °C between 02:00 and 04:00.
• Last time at or above 13 °C: Tuesday 20 October at 18:30.

The dashboard is ready: 'Trading desk: weather and Swiss power demand'. Its data queries return correct live values; I couldn't see the rendered page itself, so give it a quick look before the desk does.

This is the dashboard, with the above map at the bottom: Dashboard with the live Bern panel at the top, the threshold chart, the station map, and two buttons: pull fresh data and full rebuild. It auto-refreshes daily, Monday to Friday. See public Dive and interactive dashboard.

What Else You Can Do: Pivot Tables

The power of this approach is also that now you have the data, the code, and everything inside MotherDuck in the format of a DuckDB database, and the transformation from the source is directly in plain Python code, meaning you can run it anytime and update the link to a newer source.

But you can also explore the data further, as we have it in MotherDuck, doing the classic Pivot Table analysis. It seems old-fashioned, but domain experts love using it to explore the data interactively: Pivot table of power in kWh per canton (state) in Switzerland.