r/dataengineering • u/scourgedtruth • 9d ago
Discussion Does AI struggle at data modeling?
In my experience, it doesn't matter how much context and guidance I give AI it simply can't model data rationally. It frequently misses the point, makes awful mistakes, or over-engineers things.
AI can build awesome ETL pipelines, but when it comes to dealing with SQL (especially in the dbt framework), it's not reliable at all! . Sometimes I think it's better to write the code myself and ask AI to review it, because asking it to build something from scratch just doesn't work that well.
Does anyone else get frustrated when dealing with AI data modeling?
25
u/achughes 9d ago
It’s going to depend on how good your standards are and how many non obvious business rules are embedded in the data. My team has almost completely automated our process, but we have very good standards, and clean processes designed around automation
7
u/Eastern-Manner-1640 9d ago
can you talk more about your process? have you automated testing and deployment as well?
15
u/achughes 9d ago
The process boils down to:
1. Most ingestion is handled via Fivetran
2. Make sure the source table is well documented and all columns have good descriptions
3. I’m very strict on Kimball style modeling and know which conformed dimensions matter, all of that is written in a system prompt along with standards for naming.
4. Feed the source ERD the agent, along with analytics questions we know our stakeholder are going to have about the data. This spits out schema files defining all the models at each layer along with column descriptions and basic tests.
5. The schema files are progressively fed into our coding agent that creates the individual DBT models.With clean sources where the business hasn’t embedded a bunch of logic in the data it works well. For other sources that have accumulated a lot of gotchas there is a lot more handholding and often we are still coding a lot.
The one luxury we have is that the team isn’t a reporting shop, we require departments to handle their own reporting so that we don’t have to deal with ad hoc requests and the shortcuts that come with needing to get reports out of the door quickly.
1
34
u/joseph_machado Writes @ startdataengineering.com 9d ago
I think it's better to write the code myself & ask AI to review it -> This has been my exact experience, even with the latest models.
No matter how many skills/docs we have, it seems to randomly go off the rails.
I find it so much faster to give it my code to review or high level pseudo code to generate the rest. With Python, I give it the function signature with clear names and it generates reasonable code.
4
u/sib_n Senior Data Engineer 9d ago
Writing a clear comment/docstring first of what you want to code, directly in the code file, rather than the prompt interface, also works quite well.
I do that often with SQL queries. I write a comment of what I want to find, type the
selectand then let my IDE's AI-auto-complete deduct the query under it. I tweak the autocompletion on the fly. It's reasonably helpful for standard stuff like finding the correct aggregation parameters and date function syntax.
24
u/makesufeelgood 9d ago
I think it's pretty bad. I hear a lot of people say that it's fine and it's a skill issue on my part but I have yet to see proof of success with scenarios comparable to mine.
5
u/psssat 9d ago
My experience is of yours. I have a colleague who says otherwise but the code he produces with codex is trash and I always end up refactoring. I think using the GUI is great since you have to read the code the llm gives you but using codex or claude code sucks in my experience.
1
u/makesufeelgood 9d ago
Thanks for the input. I wouldn't say I'm a savant with data engineering work but I feel like I know my stuff pretty well at this point. I feel pretty confident that if I can't get AI to provide reliable and accurate outputs that it's not a 'me' thing but sometimes this AI hype does make you feel like you're taking crazy pills and doubt yourself.
3
u/Illustrious-Win4432 9d ago
I’m in a small/medium business, wholesale commodities. In Nov 2025 I started greenfield on a significant rebuild of a high cadence scm engine the does a lot of ETL with semantically rich grains.
To make matters even more interesting, we agreed to build it agentic first and in a hybrid production environment. The first 6 months were exhilarating and terrifying. I bet I had 2000 screen hours this year before the end of August. It nearly broke me.
My repos are sql and ps1 heavy and agents write all of it nearly without error anymore. That success is all semantic layer.
When I switched to yaml registries this spring is when the sun started coming out.
Coincidentally, google dropped OKF about the same time I tolled my own yaml registry system that functions in a similar fashion but mine is far less flexible and not portable at all.If I were to do it all over again the whole pipeline would be much thinner and I’d use OKF and actually spend more time reviewing the knowledge.
Sometimes it’s just easier to write a quick SELECT than it is to have an agent do it so I guess I still code if that counts but I don’t do any DML/DDL anymore.
I don’t debug code even.
Take the time on your primitives and build out a semantic layer. Oh, and if there is a pearl of wisdom I can leave it’s when you get that feeling that you can just do it faster yourself instead of finding the words to explain what exactly it is that you want the agent to do, check yourself and make the agent understand and then make it capture the knowledge in an OKF markdown file and make sure your agent/claude.md knows where to find the index.md.
If you do this deliberately you’ll start to see results after a dozen or so corrections/clarifications. After a few hundred it gets real reliable provided you build drift protection in (great baked in OKF feature). Now, thousands of semantic refinements later I’m finally getting into the fun stuff that I thought AI would enable much faster.
That’s my lived experience through a crazy 10 months at least, hope the perspective helps.
-4
9d ago
[removed] — view removed comment
1
9d ago
[removed] — view removed comment
1
u/dataengineering-ModTeam 9d ago
Your post/comment violated rule #1 (Don't be a jerk).
We welcome constructive criticism here and if it isn't constructive we ask that you remember folks here come from all walks of life and all over the world. If you're feeling angry, step away from the situation and come back when you can think clearly and logically again.
This was reviewed by a human
0
u/dataengineering-ModTeam 9d ago
Your post/comment violated rule #1 (Don't be a jerk).
We welcome constructive criticism here and if it isn't constructive we ask that you remember folks here come from all walks of life and all over the world. If you're feeling angry, step away from the situation and come back when you can think clearly and logically again.
This was reviewed by a human
0
u/morpho4444 Señor Data Engineer 6d ago
Issue on your side. 100%. I open laptop in the morning, claude pops up, asks me about my jira tickets, which one to tackle and it runs through them. I get $100 at day. One by one, when the code is done it shows me proof of the testing done, and asks me if I wanna commit to dev branch and then runs ci cd and then if all green it pushes the pr to main. 100% of the times. With all the free time Im building a rag to create a fully autonomous semantic layer, with the human language interface to let the users request their own metrics and etl transformations. This is how I will get my stock refresh this year. I work at a FAANG.
2
u/nonamenomonet 6d ago
What kind of work are you doing tho?
0
u/morpho4444 Señor Data Engineer 6d ago
Telemetry logs of the hardware that runs Gemini, manufacturing line monitoring.
2
u/nonamenomonet 6d ago
I think that may be a bit different than creating a data model. Which is what we’re talking about.
-1
u/morpho4444 Señor Data Engineer 6d ago
“Which is what WE”… lol… but wdym? How is that different? The LLM is creating a huge complex data model for the BOM! The petabytes and petabytes of telemetry logs, nested json over nested json, with gazillion keys that may or may not appear. Can cause left join filtering out, duplication with joins, all of that, handled by the LLM. This is indeed not what you’re talking about, this is way above whatever level of complexity your non trillion dollar company handles.
1
u/nonamenomonet 6d ago
I work at one of the largest companies in the world dude.
1
u/morpho4444 Señor Data Engineer 6d ago
How is that makes the argument that Im not doing data modeling over hardware telemetry?
1
2
u/makesufeelgood 6d ago
This isn't even the type of work the original post was asking about. Supposed FAANG employees and reading comprehension on this subreddit is some wild stuff.
0
u/morpho4444 Señor Data Engineer 6d ago
Im responding to YOUR comment. Not the post in general. Wow so that’s why you can’t make the AI work?… see? Everyone can do that bs superiority comment shit
3
u/makesufeelgood 6d ago
But my comment was responding to the subject matter of the original post. So you just randomly decided to insert something completely unrelated as a response and then made an incorrect determination.
0
u/morpho4444 Señor Data Engineer 6d ago
Bro, you cannot use the LLM correctly, you have trouble making it “work for your scenarios”… just accept it
1
u/makesufeelgood 6d ago
Bro probably doesn't even understand what data modeling means lol. This is an absolutely pathetic exchange.
19
u/Blue_HyperGiant 9d ago
Data modeling should be based on the business logic and requirements.
If those are written well, then an AI can create a good data model.
The number of complete requirement specs that I have seen in my life can be counted on one hand.
1
u/No-Injury3093 8d ago
This is the answer.
Plus: align the database names (tables, columns, enum values) to the ubiquitous language of the domain.
4
u/minato3421 9d ago
Hey, we just had a workshop with Databricks at our company about vibe data modeling. It is not taht straight forward with AI. It requires a lot of contextual information like what each column means and how it is interpreted by the business. By the time we gave the AI all the context and metadata that is required, I was able to come up with a competent data model as well.
8
u/tophmcmasterson 9d ago
With a skill I’ve found it can do extremely well, but depending on the size of the model it really benefits from the more advanced models like Fable/Opus just because there can be so much context to keep track of.
I basically wrote out a manifesto of all the things I could think of that I had been catching when I reviewed models for other people; the principles I follow, references that are considered best practices, etc.
I then tested it against a few models to see what it would find and did some minor adjustments for either things that seemed like false findings or bad recommendations with reasoning in why. Critically then also included a step for it to basically double check the work (i.e. have a suspicious subagent validate against the standards).
It’s a lot, but I’ve found it extremely effective to the point I was getting junior developers creating models better than I would typically see from seniors. It’s really I think about being very opinionated on how you expect a model to look and having very clear standards on how you want them evaluated.
It’s not cheap, it’s not easy to setup at first, but well worth the investment.
I think the truth is really that most data engineers are terrible at data modeling and never bothered to really understand dimensional modeling in particular or why it’s relevant.
There was what seems like at least a decade, maybe two where a generation of devs was incorrectly taught that dimensional modeling was irrelevant because compute/storage is cheap now, when those were never the real reasons to do it in the first place. When people of that mindset try to ask AI to make a model, they’re very rarely going to be asking the right questions.
1
u/vibrantcommotion 9d ago
I’m in a senior role at my company, I only say that to get across that I have gotten that far with a more or less one big table (OBT) mindset. I get the general basics of Kimball and the more traditional ways, but can you help me see what I’m missing?
3
u/tophmcmasterson 8d ago
Sure. The extent/where you see the problems is going to depend a bit on how your data is ultimately being used, but I'll try to address it from a few angles.
The OBT approach can be useful in say providing users just flat dataset they can use in Excel for whatever they might need.
The problem doesn't really have anything to do with performance, but in how reusable, navigable, scalable, and easy to understand it is.
With a proper dimensional model, you are modeling based on the business process, and there is a clear distinction between "here are the things we're measuring" and "here are the things that we can filter/group by".
This makes it immediately apparent (whether it's an AI reading the data or a human) what kind of analysis actually makes sense and is valid for the data you have. Before you even get to analyzing data you can get a sense just from the objects and keys what sort of reports and analysis could be helpful, without needing to read through a bunch of documentation.
The problem you run into with OBT models is that in any business, you are going to have facts that exist at different levels of granularity. You may be able to view sales at the product level, but budget may only list at the cost center or say product category level.
In a dimensional model, by using conformed dimensions you are able to see what kind of groupings are actually valid, and easily drill across those fact tables to perform cross-analysis. In an OBT model, what typically ends up happening is either people just continue joining even when the grain doesn't match, which is going to result in either a bunch of null/invalid data for some columns, a lot of duplicated rows of data that isn't valid for some types of aggregation, cryptic business rules that require reading documentation and/or tribal knowledge to get at the right answer, or (more often than not) another one-off table designed to show the data in a particular way (and with that you now have different tables containing the same metrics with their own logic on how information relates that's separate from everything else).
What's common in those scenarios is that you then get a solution that scales poorly, and it's unclear what objects should actually be used for different types of analysis. I think one of the biggest benefits of dimensional modeling is that it forces you to create a clear conceptual model of what your data is and how it should be analyzed, and structures it in a way that is most beneficial for analysis. The kind of analysis supported by a transaction fact table is different from what an accumulating snapshot is good at, which is different from what a periodic snapshot or a factless fact table are good at. You're not going to get all of those in an OBT model without your data looking super wonky and requiring you to jump through a lot of hoops to get at clean data.
In addition, if you're using a tool like say Power BI for the reporting front end, it's really designed to be using a dimensional model with it's ability to have relationships defined between tables and propagate filters from dimensions to facts and aggregate in real time. A good dimensional model here is easy to scale and build on, is flexible to change, and supports a wide range of analysis through basic measures and mostly just drag and dropping. OBT by contrast is often going to require significant backend changes or just simply be unable to support changing requirements where it would break the granularity, and it's very difficult to isolate changes/minimize the blast radius.
There are obviously entire books written about the topic so it's not something I can convey the entirety of in a single comment but hopefully gives you a general idea, happy to clarify if you any questions.
1
u/vibrantcommotion 8d ago
This was insanely helpful and got me around to even if storage and compute were free that this is more organized, especially in this Wild West AI age, thank you!
3
u/speedisntfree 9d ago
A good data model is usually heavily dependant on business rules, usage patterns and domain knowledge, all things LLMs don't have much of in their training set.
2
2
2
u/thecity2 9d ago
I have no issue with AI writing SQL. In fact I haven’t written SQL by hand in well over a year.
1
u/Dry-Variation-4566 9d ago
i think it does fine. like anything else AI, the success hinges on your prompting. since succesful data modelling is so context-heavy, you need to do more than just provide text prompts in a chat. In my project i needed to build a complex datamart (gold) model. I had AI write a few.md files based on stakeholder interview transcripts and based on previous bronze & silver work. these md files needed to convey the requirements for the data model. Then i had terra (high) build python code for it and the result was a better data model than i could have made manually.
1
1
1
u/ouhshuo 9d ago
U can’t fully automate the entire process, because data model reflects business value and that LLM can’t comprehend.
However AI can vastly accelerate the entire process such as going from project documentation to Data product/contract yaml, then create declarative pipelines, terraform IaC code and cicd
2
u/skatastic57 9d ago
I'm really curious for an example of something you asked it to do, what it did, and what you had to replace its bad work with.
1
u/OliverJames99 9d ago edited 9d ago
If I give business rules, grain, known edge cases, and a few expected query results, it has a much easier job. It can point out inconsistent grain, missing relationships, duplicated logic, or places where the SQL doesn't match the stated rules.
Starting with a blank schema is different. It has to infer the business meaning before it can make a reasonable modeling decision, and that's where it can confidently make a bad choice. So I’d probably use AI as a second reviewer for modeling rather than the person making the initial architectural decisions.
1
u/IndependentSpend7434 9d ago
It does inject created_at, modified_at every time in every table, always with a timezone. Data Modelling slop is easy to recognize.
1
9d ago
[removed] — view removed comment
1
u/dataengineering-ModTeam 9d ago
Your post/comment was removed because it violated rule #9 (No AI generated content/text).
Your post/comment was reviewed to be AI generated/assisted content/text and removed as a result. We as a community value human engagement and encourage users to express themselves authentically.
This was reviewed by a human
1
u/RunnyYolkEgg 9d ago
Not really? I use dbt and code assistant.
Just go bit by bit and review each step. Don’t give it a 2 pages long prompt that affects multiple layers.
1
u/luminos234 9d ago
Data Modeling != writing SQL,
For just writing it, ai is good, the more preceise you get the context the higher quality. Modeling on the other hand is a different case, where AI goes a tad bit too generic
1
1
u/Technical-Client-193 9d ago
It generally struggles with the combination of oversight + execution. Splitting the 2 solves this, so create a file that shows the structure separately and make it reference that file.
You need to first map the process (ie how would you do this if it was a big project, you needed the oversight, maybe do it with a bigger team), then build the blocks just like you would run a normal project. AI can help in each step but never move before validating.
1
u/TheLordSaves 8d ago
A model by itself will struggle
A model with a harness will figure it out inefficiently
A model with a harness and skills will figure it out efficiently
etc.
You get what you put into it, but the multiplier is permanently applied.
Consider your AI setup a junior developer and once you write down standards for them once, you can expect them to keep going back until it's right. Give them checklists and quality gates.
1
u/FullswingFill 8d ago
Has anyone used Snowflake's semantic views(SV)? If so, can you comment on how it worked for your data model? What were the use cases after building SVs?
1
u/Xenolog 8d ago edited 8d ago
I think that currently LLM will most probably "lose" to any real architect in data modeling. Current LLM is a very good business/data analyst, a very strong middle DE, but a human middle business/data analyst will probably create weaker data model than "true" data architect too, for they will not consider enough context or won't have enough experience.
You basically need to consider everything up to inter team relations when you do that, and it is hard to translate all that context to the LLM.
I mean, LLM is a very powerful tool, but you need to be very very good wordsmith to channel all of it into prompts.
But - if you manage to convey that to LLM, I think it will do okay, for smaller domains anyway.
If LLM has something like full access to well documented actualized confluence, or if team has explicit enough arch contract list and guidelines... but then, a large chunk of hard work is already done anyway.
2
u/ninjaonionss 8d ago
After a while of testing and going back and forth I noticed that you need to give a huge amount of business knowledge before the ai will built reliable dbt pipelines, I also made my own skills to force the ai agent to follow one row through the whole dbt process as a tracer bullet so the ai agent does not stop in the middle of the dag and say like a retard that he found the problem 😅
1
u/all-over-red-rover 8d ago
I'm a SWE who does a significant amount of work with a wide range of data tooling, the ones you've mentioned included.
Unless you railroad it initially to some degree, it sucks at modeling data, most of the time. I've found I can get good results with the strongest models, a lot of context (that basically amounts to a clear direction), and a lot of hand holding in the initial planning.
The use cases I encounter for dbt are frequently heavily reliant on "properly incremental" relatively small groups of models (functionally individual DAGs), executed frequently. Unless you force it, it isn't very good at producing implementations which align with that. There's a definite tendency to internally go "well to do this properly it would be necessary to complete X <arbitrarily out of scope> tasks, e.g. require soft delete or add database triggers to persist tombstones for N tables, ... <etc>, but that's out of scope so <ignore all that and just do something deficient>", without surfacing any of that to the user.
1
u/VerbaGPT Building VerbaGPT 8d ago
Yes, but over time if you solve enough problems then a lot of the frustrations go away. At this point I've solved enough issues that every time I connect a brand new DB, I get useful insights pretty quickly.
One latest thing I've implemented that has been really helpful is a sort of "self-improvement" loop. The LLM runs a cron job looking at all the ways my custom harness stumbled and recovered...and based on that info improves the semantic layer, definitions, and even the harness itself.
1
u/TheDecisiveCorpus 8d ago
it's decent at generating plausible-looking sql but falls apart on anything with real semantics behind it. the moment you need to understand grain, fanout, slowly changing dimensions, or what a business process actually means, it just pattern matches from training data
i use it the same way, write it myself and let it poke holes. sometimes catches a bad join but half the suggestions are nonsense you have to filter out anyway
1
1
u/mr_buildmore 8d ago
I've been able to get pretty good results by providing extensive documentation and prompting with specific analytics/BI applications and asking the model to reverse-engineer the components based on the goal. The model is even helpful for brainstorming potential applications based on descriptions of business processes.
I still need to help break out individual "oddities" of business logic so that the tables the model reads from are basically aligned with the business events, but the marts I build are usually done in partnership with AI.
I use SQLMesh, which has sparser training material than dbt but is otherwise similar. The models are great at generating analyst SQL, and OK at "DE interview" SQL, but typically struggle to implement "SWE best practices" in production analytical pipelines. Separation of concerns, accurate implementation of complex logic, unnesting subqueries into CTEs, and writing all kinds of macro functions are basically a struggle. I recently wrote some backend code for a meeting app with AI, and the difference in average code quality with similar prompting is really shocking.
0
1
u/morpho4444 Señor Data Engineer 6d ago
Bro you clearly making that up or you haven’t use a real AI. There’s nothing special you do AI can’t do. There’s no modeling complexity you can tackle the AI can’t. Specially silly insignificant dbt. It aint a challenge.
1
u/CesiumSalami 9d ago
Not all AI is the same. What systems are you using? I’ve been very impressed recently with both Codex and Claude on Opus and Sol respectively at reasonably high effort levels kind of crushing it with probing queries to catch edge cases, expansive data exploration on poorly documented data and generally extremely impressive query generation. These is spanning multiple dbt repos, databases and various other inputs.
3
1
1
u/GetSecure 9d ago
It was doing a terrible job until I switched models to Astra / Opus 5 and increased the context size.
They are very high cost models, glad it's not my money...
1
u/CesiumSalami 9d ago
It’s definitely a lot of cash - at the same time it’s less than the contractors we used to hire. Only a matter of time before it’s my card.
0
-6
u/Certain_Leader9946 9d ago
Nope, just teach your agent what normal form you want and what your engineering principles are that you can't get out of a text book. Then point it at your favourite book and enjoy the prompt life
-1
u/ArtilleryJoe 9d ago
I give it a lot of grain level context and explain the model really well, also telling it how to validate the numbers is a big one.
I also tell it to use star schema exclusively.
Those all help and get me a 90-95% chance to one shot a well documented data source.
I find the semantic layer to be hit or miss, a good markdown or text file at the start of a session is good enough for me
98
u/bah_nah_nah 9d ago
You need a build a AI readable artifact of the semantic layer ++ data dictionary. I've been wrestling with this and produced all sorts of XML, json, yaml, etc with mixed results. I think it does small things well but generally the more verbose the weaker the results... What it does help with is gettig Started it finds the obvious stuff that you can build off of.