My first Audi pipeline lied to me

Why I keep coming back to this project

I've been an Audi fan for years. Somewhere in my head there's a version of me driving an S5 to wherever the road goes, and until that happens I spend more time than I'd like to admit in the Audi subreddits. Owners there post about everything. Someone's engine is misfiring on the highway. Someone just put new wheels on their A4. Someone bought a wrecked RS7 at an auction and wants to know what they've gotten themselves into. Read one post at a time it's noise, but read thousands of them together and it starts to look like the most honest product feedback a car brand could ask for.

So last year I built a pipeline to turn that noise into answers. An Azure Function pulled posts from r/Audi and nine model subreddits every month and dropped them into Blob Storage. Data Factory moved them into Azure SQL, and stored procedures pushed them through bronze, silver and gold layers. At the end I built some dashboards in Tableau and wrote a README about what I'd found. I was proud of it.

Then my Azure subscription got cancelled, and the whole thing disappeared. The function, the storage, the database, all of it. What survived was a handful of messy CSV backups sitting in my Google Drive and a GitHub repo full of scripts pointing at servers that no longer exist.

You might know my pattern by now. I start something, it gets hard or it breaks, and I move on to the next shiny thing. Web development, Android, data science, a bit of blockchain. A dead pipeline is the perfect excuse to do that again. This time I decided to rebuild it instead, and finishing it is the whole point of this post.

Rebuilding without the cloud

I didn't want to pay for Azure again just to resurrect a portfolio project, so I rebuilt the same flow locally with Python and DuckDB. DuckDB is a database that runs inside a single file on your laptop, which means the whole pipeline now runs with one command and no servers. The design stayed the same, because the design was never the problem.

The bronze layer keeps every post exactly as the API returned it, and if the same post shows up in more than one pull, I keep the copy with the most comments because it's usually the latest. The silver layer joins each title to its body text, converts Reddit's Unix timestamps into real dates and removes duplicates. It also tags every post with the parts of the car it mentions, the model, the model year and the mileage when the owner says it. The gold layer splits posts into the ones I could categorise and the ones I couldn't, and builds the tables the dashboard reads. In total that came to 2,171 unique posts with 26,486 comments between them.

The bug that flipped my headline

In my original README, one of my proudest findings was that HVAC problems got the most engagement of any issue on the Audi subreddits. Air conditioning, heaters, blower motors. I even wrote a little "business sense" paragraph about it. It was wrong.

To sort posts into categories, my code checked whether any keyword from a list appeared in the text. The HVAC list included "ac", as in AC. The problem is that my check looked for those two letters anywhere in the post, so every owner who backed into something, took their car to the track or mentioned anything in the back seat was quietly counted as an air conditioning complaint. Try it yourself below.

The fix was small. Keywords now have to match as whole words, so "track" no longer contains an air conditioner. After that change HVAC dropped to 33 posts with a very ordinary 7.6 comments per post. The finding didn't just weaken, it disappeared.

That wasn't the only one. Category names with commas in them, like "Electrical, Lighting, Telematics", were being split apart in my gold views and counted as three separate issues. Posts about the TT RS were being tagged as a plain TT, because my model list was checked in a fixed order and stopped at the first name it found. None of these threw an error. The pipeline ran perfectly every time and produced confident, clean, wrong numbers. That's the lesson I'm keeping from this whole project: a pipeline that runs is not the same as a pipeline that's right.

What the data actually says

With the bugs fixed, the picture changed in ways I find more interesting than the original. Engine problems are the constant. They show up in 368 posts, more than any other category, and they made up between 11% and 18% of every single week of data. Electrical and lighting comes second and the transmission comes third.

But how often something comes up and how much people care about it are two different questions. Flip the chart below between posts and replies and watch the order change.

Hybrid and electric is only the seventh most common topic, but it pulls the most replies at 19.8 comments per post. It also has the highest median, so it isn't one viral thread doing all the work. The biggest of those threads were news that the RS6 had been cancelled and the RS3 would be discontinued. Audi owners have strong feelings about where the brand is heading, and they show it in the comments.

The models tell a similar story. The A4 and A6 are discussed more than anything else, but they average only five or so comments per post. Most of those are quick questions with quick answers. The RS cars are the opposite.

RS7 posts average 108 comments each from just 14 posts. Everyday cars bring questions and performance cars bring conversations. If I were doing marketing for a dealership, I'd treat those as two completely different audiences.

What the Reddit API doesn't tell you

Here's the part I didn't understand the first time around. Reddit's API only returns about 1,000 posts per listing, so every pull mostly captures the last few weeks. My first README talked confidently about trends since 2023. The backups I could actually recover are clustered in August and September 2025, so anything I say about change over time comes from about six weeks.

To keep that from misleading anyone, including me, I stopped charting raw weekly counts and charted each issue's share of that week's posts instead. A week where the API happened to return more posts no longer looks like a spike in problems.

There's one more thing the data is honest about. About 44% of posts don't match any issue keyword at all, and those posts actually average more comments than the rest. The most commented post in the whole dataset, at 990 comments, is the wrecked RS7 bought at auction. It's a story, not a fault code, and keyword rules can't read stories. That's the clearest sign of what the next version needs.

What's next

I have a bigger master backup that the tools I used couldn't load in one go, and adding it should turn the six week snapshot into a real trend. After that I want to replace the keyword rules with a proper text classifier so the uncategorised 44% can finally be sorted. Then I'll bring back scheduled pulls so the dataset grows every month instead of sitting still.

But the most important next step already happened. I finished something I could easily have abandoned. The pipeline runs, the numbers are right, and when they weren't, I found out and said so. That's the kind of engineer I'm trying to become.