Setting the Stage
When Opus 5 came out, I saw tweets and posts that touted how good it was at one-shot, long-horizon agentic tasks. Someone tweeted how they were able to build a full video game, replete with high-fidelity graphics and sound, with just one prompt using Opus 5. This got me thinking, “What can Opus 5 or an equivalent model achieve in the data analytics space?”
This past year, I have been heads-down working on context engineering, especially leveraging Ontologies and Graph databases to provide LLMs context as they reason about and process data across a few different startups. I will write about what I learned in a separate post, maybe.
Agentic AI in software engineering has come a long way, and one-shot, long-horizon tasks in the Software SDLC are definitely possible today. I don’t claim to be a software engineer beyond vibe coding, but agentic SDLC is achievable because a software’s requirements are set, and if something is broken, it can be tested, and it throws an error instead of silently failing. But data is hard because of the myriad business rules and the tribal knowledge involved, which the data or the schema cannot fully answer. Data and schema failures are seldom loud; they're frequently silent. I set out to test my hunch with an experiment.
My Experiments with Claude and Data
The Setup
I chose the wholesale distribution sector because it's complex enough to model real data problems and familiar enough to understand easily. A sale can happen via a salesperson/ERP, a web order via the e-commerce portal, or at any of the branches. Source systems include a CRM, an ERP, e-commerce, and POS. Data from each source system arrives either via an ingestion tool from a source table as a Change Data Capture feed complete with an ingestion timestamp, as daily snapshot tables via API, or as daily sales data from every POS till per branch.
I chose dbt as a data processing system and DuckDB as a data warehouse because they're common in the Modern Data Stack, mostly vendor-neutral, and easy to set up and use without a cloud setup, a trial signup, or a paywall.
I set out to build all phases of the Modern Data Stack, including data ingestion, cleansing & transformation, storage, reverse ETL, and activation, in an AI-assisted manner. Where I provide the prompt, required context, and instructions, Claude Opus 5 does everything from setting up the reusable environment to writing the code and tests.
However, I collected enough data points during the ingestion and staging phase to represent the full data lifecycle, so I didn’t feel the need to demonstrate the whole lifecycle in this experiment. I do intend to complete the rest of the lifecycle and present any variations in the results presented here in a subsequent post.
Data was completely generated in an AI-assisted way; all data generation scripts are presented in the accompanying GitHub repo along with the prompts, context, and instructions used in this experiment here - snudurupati/agentic-data-analytics
Experiment 1: Setting up the dbt sources
The first thing I tell Claude is to read the CONVENTIONS.md and the STANDARDS.md, and that should inform you on how things are done in this org. Then I tell it it has four source systems, where the files land, what the source systems are, and that we run a nightly ETL schedule, so we get new data every night. Everything else it needs is in the two markdown files.
Just a personal pet peeve that I have a hard time reading the way Claude Opus 5 writes. So I also specifically instructed it to use Simplified Technical English and avoid any flowery language. Whether it follows that instruction is its own little experiment.
Claude came back, and it had set up four new sources for the ECOM, CRM, ERP, and Point-of-Sale sources and wrote 40 tests to, unprompted. It is reading the files with a wildcard, which is expected. Typically, I would use an ingestion tool to load from object storage directly into the cloud warehouse. But to keep the demo simple, I have asked Claude to use DBT for ingestion, and it used DuckDB’s file blob reader for the purpose.
It used union_by_name, which is smart, and I would not have expected from a junior data engineer, which is smart. What that does is, in the future, if my upstream sends me a file with a missing column, or a differently named column, or the reordered columns, the reader either handles it or fails loudly. Then it is also doing all_varchar on the sources, which is what the standards tell it to do, and Claude made a point of noting it and implementing it. It also added the file name as a source column, which is also smart, because I can find a use for it in staging or further downstream.
It also wrote a freshness check unprompted, and it is not in my standards or my conventions either. It did the right thing anyway, and then found two places where the data and the conventions do not agree, and instead of guessing, it came back and asked me, which is exactly what I would expect from a junior developer. It tells me the website says web sales have no timezone and no city, but the data looks like it has both, and asks me how to handle those.
There was one thing I still wanted to be sure about, so I asked it: if all the branches arrive except one, will the freshness check tell me? The conventions do say a branch can sometimes be delayed, but delayed doesn't mean I am not concerned. If a branch didn't send today’s sales, my sales dashboard is stale or incomplete, and that could skew someone’s decision-making. I would want to warn the relevant department or person so they are at least aware. Claude, instead of just speculating, ran a simulation to see what actually happens in that scenario, which I liked.
And this is where it goes on tangents and starts arguing a few things, asking me where the list of expected branches should come from, offering me options. There is a much easier way to fix this. In the warehouse, I already have all the branches I have so far. I can take those branches, test them against the files that arrived, and see whether a branch is missing from today’s feed. That is a sound enough test to tell me a branch did not arrive. This is where I had to nudge and course-correct Claude, tell it how I would do it, and ask it to implement that test.
Claude Opus 5 took my direction and built it the way I asked. It did that part well: it implemented the test, simulated the scenario, and made sure the test actually catches it. It also wrote a test instead of failing the whole job, which is smart, because if one branch is missing, the job should keep loading whatever is available; we don't want a backlog. It warns instead, and I can take that warning into an observability dashboard and build a workflow that tells the concerned team their sales data is stale. In this case, it took some nudging from me to steer Claude in the right direction.
Experiment 2: Building the staging layer
Now that the sources were built, I moved on to staging and asked it to build everything incrementally. Honestly, POS is the tricky one here because we are not getting incremental data or snapshots from it; we are getting a file per till, per branch, per night.
It built eight staging models and 119 tests. It says the feed type decides incremental, which makes sense. For the ERP, the conventions say the feed only sends changes, so every row is kept and it decides whether the row is an insert, an update, or marked as deleted. CRM and ECOM send a full dump every night, so it does a hash diff to work out the new rows, and if a record stops appearing, which means it was deleted, it records that row as isDeleted = true. This way the data warehouse always records all the history and every change.
Regarding POS, Claude told me it has no feed ingestion timestamp, so there is no column to hold the watermark, which makes sense because we only ever send new sales data. That new sales data also includes refunds, exchanges, and voids, and that is what I wanted to stress.
So I asked it. If it implemented incremental, how did it identify new rows? It told me it identifies a new file, not a new row. Then it told me why it cannot use the other datetime columns, with reasons that all check out. But I was still curious how it handled refunds and exchanges, and Claude dumped a whole bunch of prose on me. The summary is that, per the conventions, refunds and exchanges carry a new transaction ID, so they are handled as new rows, and the downstream job decides how to attribute them back to a branch or calculate metrics based on business logic, which is fair enough. But I still wanted to know what happens to voids, because a void usually carries the same transaction ID as the original, and that could throw things off.
Claude came back with a few things. It said nothing actually breaks right now because we don't have a mart yet; there are no fact or dimension tables; nothing is being reported, so there is no false revenue. That is true. However, a few of its other statements were not so true.
First, it says the conventions state that every till numbers its own sales, a branch runs more than one till, and therefore two tills could produce the same transaction ID. That is incorrect. I wrote the conventions, and they say transactions may repeat across branches, but they say nothing about two tills within the same branch producing the same transaction ID. Claude Code is confidently stating that it is true, and that is the kind of thing you can only catch when you go through everything that Claude says and cross-check everything that Claude Code is building. That's why I asked the question in the first place.
Here is what CONVENTIONS.md actually says on the subject:
The branches do not run the same till software. Tills are from various vendors over 15 years. There is no shared specification, no shared version and no central upgrade. Each till numbers its own sales, and nothing coordinates one branch with another.
Transaction IDs are not unique across branches.
The second thing I caught is that it is building the surrogate key off the grain, a composite key of sorts. That is not right, because it means the surrogate key is only as good as the grain. In this case, if transactions collide, the surrogate key will also collide. Normally, I'd identify a void by seeing the same transaction arrive twice with two separate surrogate keys, which tells me it is a possible void, not a duplicate transaction. Here, because the surrogate key is the same, I can't tell which is the original and which is the void or duplicate. That is a mistake Claude Code clearly made, and as a senior data engineer fact-checking what Claude is building, I could catch it. If I had not, it could have led to silent failures initially and incorrect results, and incorrect decision-making downstream.
One last thing I wanted to check was the immutability of the landing zone, as stated in the conventions and standards. In a landing zone, we keep appending data, and a data engineer should be smart enough to read only the relevant files. Say, I have three years of history; I shouldn't read all three years every day. There is usually a late arrival cutoff, and in this case the conventions clearly say 14 days. Claude didn’t mention implementing any such logic in the ingestion process, so I went ahead and asked it.
Three years from now the landing zone holds tens of thousands of POS files, and in prod the files land on object storage, so is that going to be a problem?
It ran a bunch of measurements on the local file system, which is completely unnecessary. I clearly told it the files land on an object store, and an object store does not behave like a regular local file system, so those measurements were moot. It tells me 10,000 files is roughly 57 seconds, which is purely useless information and a sheer waste of tokens. It does come back and say this scenario has key failure modes on object storage, and that is the important part.
However, it gets the problem right, but the fix is all wrong. It says this is a file count problem, not a data-volume problem. Three years of point-of-sale is roughly a million rows, which is nothing. Ten thousand small files are the real problem, and object storage handles small files worst. At least it knows that, which is good.
But then it offers me three fixes, and one is to partition the files into date-wise Hive-like partitioning. If Claude Opus 5 had ever worked on cloud storage, it would know that hierarchical partitioning on object storage is very inefficient; it kills throughput, and AWS, or any object store, will start rate limiting you. If listing files on object storage is bad, listing hierarchical folders is even worse. Anybody who has done any work in the cloud knows that. Claude, in this case, clearly hasn't run anything in the cloud, so it doesn't know.
Then it tells me to compact my files and convert them to Parquet, which has the same problem, because even converted I still have to read the history. Or add one more stage where I take the landing files and write them to raw Parquet, which is completely unnecessary. The conventions doc clearly states that the late-arrival cutoff is 14 days, so a simple solution is not to read any data older than 14 days during ingestion. I mention this to Claude Opus 5, and it agrees and finally implements the fix.
If it isn’t already clear from the narrative, experiment 2 is where Claude’s performance visibly starts to degrade. Starting from staging, the business rules get progressively complex, and implementing them requires a mix of business process acumen, tribal knowledge from prior experience, and an understanding of possible failure modes; codifying them into a neat, machine-readable context document is no easy feat.
My Key Takeaways
My conclusion to the question ‘Am I a data engineer if Claude does the work?’ is “VERY MUCH YES!“. Claude or another equivalent model like Codex Sol 5.6 is only as good as the data engineer wielding it, because the person here becomes the guide, the architect, the context vault (though much of it can be codified using markdown as demo’ed in experiments), the architect and the quality reviewer.
Across roughly 10 runs, every run produced a brilliant finding as well as a stupid miss. However, the model was consistent across runs on things it could deduce from the data, general data engineering best practices, or context provided in the included standards and conventions documents. The variance between runs was either because the knowledge or business process wasn’t codified in the provided context, or because it was personal experience gained after debugging pipelines over several years.
The former can be easily rectified by codifying it and documenting it for the model to access; the latter can’t be passed to a model through context documents alone.
So the pattern I saw is threefold.
Good conventions and standards give Claude a head start, but Markdown may not be the right medium. It knows your business, it knows your coding standards, and it does a good job of catching edge cases and writing the tests for them. At the same time, even though I provided those standards, Claude still made mistakes, and that is a miss. Can we have a better memory system? I don’t know. Does an ontology and a graph database solve it? I don’t know either, though I have a hunch it doesn’t and that it just complicates things.
A better model tomorrow probably does improve some of this. When it built the surrogate key off the grain, that is simply not a best practice. The more data engineering standards the next model trains on, the less likely Claude Opus 6, or whatever it ends up being called, is to make that particular mistake.
Tribal knowledge cannot be easily codified, and the models cannot be trained on it either. The distinction where I am still required, as a data engineer, as an architect, as someone who cross-checks facts, only comes from years of debugging data pipelines. It is personal experience gained. Can that be codified for the model? We just saw that putting it into a markdown file is not practical, and even if you do, if the file gets too large, Claude will probably gloss over it, exactly like it did in this fairly simple example. So can we train a model on that kind of data instead? Maybe. But each enterprise is a snowflake, with its own standards and its own way of doing business, and unlike software engineering code, that code is not open-sourced, because those standards inherently carry my business knowledge and my business metadata. It is not customary to open source it. That means the training data to teach Claude, OpenAI, or Gemini isn't available today.
And that’s where this tweet that someone wrote comes into play.
AI won’t replace slow data engineers, it’ll replace engineers who don’t understand the data well enough to catch when AI gets it wrong. Speed without judgment is just faster failure. What’s the worst thing an AI tool confidently broke in your pipeline?
Saeed Anwar, @saen_dev
The skill that I bring to the table, the years of experience, is not writing code or making Claude write code, but spotting those mistakes where Claude confidently says that it did the right thing, but from experience I know it did not. And that’s where my expertise as a seasoned data engineer, who has debugged numerous data pipelines, comes into play.
A picture, or these days a video, is better than a thousand words, and honestly I didn’t want to cram 100 screenshots and words into a single blog and still lose some precious context, so I have recorded my experiment as a video. I tried to keep the experiment honest and tried to follow the process that I would personally follow while building a new pipeline from scratch, or rather, the process that I’d have a junior data engineer follow. However, let me know if I should have taken a different approach, or better yet, if you already follow a process that helps you minimize AI errors, please let me know in the comments.
So let me ask you what Saeed asked: what is the worst thing an AI tool confidently broke in your pipeline?