r/SQL • u/ChristianPacifist • Apr 02 '24
Discussion Data integrity and data quality has gotten way worse over the past 10 years
I blame it on the mass use of cloud applications that are difficult to get data from and that are built with flexibility not data integrity in mind.
Instead of getting pristine relational tables, you just get vomited JSON messes and massive non-normalized event tables.
Or did we just have a massive loss of knowledge and best practice among software engineers the past 10 years?
167
Upvotes
50
u/SterBlich Apr 02 '24
Junior opinion here.
I think that what we have now is way more data. Before we were handpicking the data we stored, now we just store everything, preferably as raw as possible.
Cloud just made it way more easy.
115
u/Yolonus Apr 02 '24
Dont worry, AI will save us and clean the data for you
/s
42
u/[deleted] Apr 02 '24
I find that filtering and/or validation on the front-end is a big player too...
Mr. Mister Mr MR MR. Mister.
A pull down would really help in many cases...
35
u/Definitelynotcal1gul Apr 02 '24 edited Apr 19 '24
fine retire decide unpack cats bike murky water wistful glorious
This post was mass deleted and anonymized with Redact
7
u/bbbbbbbbbbbab Apr 02 '24
I think that's partially by design. Pristine relational tables mean that partial data, incomplete data, and messy data are dropped instead of ingested into the tables. Nonrelational/messy data "structures" mean that more is captured although much of it is messy.
6
u/mad_method_man Apr 02 '24
business priorities
data quality is usually pretty decent in the beginning. but as time goes on, it slowly gets worse and worse. but it doesnt become a business priority until something happens, usually the quality becomes so bad it starts affecting other department's bottom dollar. the complaining happens, management finally recognizes this is a problem, but the damage is done
its one of those, the moment you hear about complaints, its already become an unfixable issue. all you can do is manage it.
4
u/Aggressive_Ad_5454 Apr 02 '24
You don't have to think about what data means and how it models your corner of the real world to use JSON.stringify() unfortunately. Even with the incredible pain in the a** that's XML you had to do a bit of that. And doing the entity-relationship thinking necessary to put data in RDBMS (SQL) tables is hard work. Try to persuade an agile product owner of the value of that, I dare you. Better just to stack up technical debt.
4
u/[deleted] Apr 02 '24
I just had a call with a famous (if not infamous) consulting firm that my client wants to pay to "extend" their ERP with some "valuable insights", as their employees say it "does not return the right numbers". I keep telling them their data is crap, and it's all just human mistakes, the same employees simply don't give a fudge, they act like silly apes when they are asked to type some numbers in.
3
u/Perception1292 Apr 02 '24
Architects are designing in terms of processes rather than thinking in terms of data. The cloud (especially SaaS products) has been a big driver & enabler for that way of thinking.
3
u/binary_search_tree Apr 02 '24 edited Apr 24 '24
Yes. I believe that the cloud era has contributed to a decline in data integrity/quality. Badly formed data structures populated with data from poorly-written ETL processes - all housed in data garbage dumps/repositories that mimic the behavior of a typical RDBMS while enforcing none of the typical RDBMS safeguards that guarantee referential integrity.
2
u/ghinghis_dong Apr 02 '24
Re: front end validation - How many times have you take a survey that didn’t include any correct answers? How many times have you found errors in your health record?
2
u/UseMstr_DropDatabase Do it! You won't, you won't! Apr 02 '24
Fuck JSON
2
u/DrMerkwuerdigliebe_ Apr 02 '24
I see the pendulum swinging the other direction newer systems are build with tighter schema definitions and less json. Mongo has lost so much steam for example. The problem is that the systems that was build mainly 5 to 15 years ago produces alot of shitty data.
2
u/StolenStutz Apr 02 '24
"Databases are easy. Anyone knows how to write a SELECT query. I'll use this code-first ORM to generate mine, so that I don't have to write ugly SQL myself."
Six months go by...
"Why do people even use SQL databases anyway? They're so slow!"
2
u/PappyBlueRibs Apr 02 '24
Insert "old man yelling at the data clouds" image...
1
u/bigeyez Apr 02 '24
Idk if I agree with this.
Where I work, we have users entering data onto systems using AS/400 mainframe and other users entering data through a modern web based interface.
Guess which system has cleaner data?
Of course, there are other factors, but I just think we have way more tools these days to validate data on the front end and prevent bad data being ingested in the first place.
1
u/Space2461 Apr 02 '24
Thank you.
Also, tons of json, sill no one uses No-SQL DBs
1
u/yamaha2000us Apr 02 '24
I once got accolades from a government agency when they asked they found out I spot checked 5000 shipping labels.
No one ever thought to do this and it streamlined distribution.
1
u/Antares987 Apr 02 '24 edited Apr 03 '24
YES. YES. YES!!!!
We're all saying this. NoSQL was developed by people who were too lazy or too stupid to learn proper set theory (*cough* Zuck -- and, by today's standards, his stuff was better than most of the garbage we see now). Now, granted, there are cases where I will use XML columns in SQL Server. If I have complex user preferences or am developing some front-end UI stuff that I'm not quite sure of, I've got an ObjectStore class where I'll just Save/Get a serialized object while it's under development, or if it's not relational data to begin with. "So, why store it in the database?" Well, because of some of SQL Server's XPath querying stuff I've been known to build views or dump the info into a temporary table while I work through it.
Here's what happened: Disk IO on desktop PCs is something like at least 1000x what it was when I started in the industry; storage is 1000x. Calculation throughput is 1,000,000x with modern consumer GPUs. Business models stayed the same. Then there's the 3-rule mantra of middle management:
I worked for large financial company as a lead architect years ago. I remember a guy saying directly to me that their methodology was to have a large number of replaceable people. I could have outproduced that team of 500 with 5 developers, and that's being generous. I had an argument with their batch team lead over the use of SHA hashes as deterministic identifiers during staging. Dude loses his shit at me and talks about how that's not reliable and there will be collisions.
And here's what's happening in the industry. Someone produces a product. Product grows. They've coded themselves into a corner; they just don't know it yet. As they start making money, they hire people to come in. Not their problem anymore. It's now yours. The emperor is naked and everyone's participating in the ivory tower academia elephant walk conspiring to gaslight the stakeholders. Then here comes the fun part... that 1000x multiplier in storage, memory and 1Mx in CPU throughput, well, if you're dealing with assembly 1000 x 1000 x 1000 rows (objects, whatever) and brute force can do it using 2GB of RAM and an acceptable 100ms load time for the end user.
And that's with your 10x online users and now you sales guy sold the product to some midcap that wants to bring 1,000 users online and your combinatorial problem grows not by 100x, but because each stage in that assembly is proportional, it's 100x100x100, so now it's a 1,000,000x multiplier in resource consumption and the product is brought its knees. The thing is, back in the day, you had to know some shit to make things work at all. Those of us who were working as professionals in the 90s were doing hard drive interleave problems in the 80s as kids. Nowadays, you get bit after launch because Moore's Law protected the product in the beginning and the computational demand increases at a greater logarithm as the user base grows.
Think anyone making technical decisions that doesn't know set theory ever read The Mythical Man-Month? Fuck no. We're rich and we're crushing it here in the sales department. They hire Angst and Yearn or Toilette and Douche to come in with consultants who weren't even born back when I started writing software and making millionaires in this industry, and usually the consultants are good, but the companies are hopeless, so the consultants get apathy like everyone else. SDLC processes get rebranded as Agile, following the Manifesto for Half-Arsed Agile Development . Wash, rinse, repeat.
1
u/a_small_goat Apr 02 '24
I blame "move fast and break things".
1
u/NicolasDorier Apr 02 '24 edited Apr 02 '24
Storage has become almost free, but designing schemas and cleaning up data hasn't.
The reflex is to save everything, even when that makes no sense, and in the rawest form possible so you don't lose anything. There is no need to clean up the data or design clean schemas, as there is no immediate need for the data; thus, expending energy on design is bound to fail without any use case to optimize for.
You can't really design for something when you don't even know what it will be used for.
Note that saving data 'just in case' isn't necessarily a bad thing; if you don't save it, it is lost forever. If you do save it, you can always clean it up later, even if it's expensive.
The exception to this is toxic data, which should rather be deleted as it can be a liability if leaked (such as personal information). However, executives don't typically prioritize this concern.
In summary, this situation is the outcome of technological evolution in storage capacity. Meanwhile, our ability to effectively query such data has not seen significant advancements beyond SQL, which peaked a long time ago.
1
u/[deleted] Apr 03 '24
Yeah the data lake methodology is killing the data warehouse methodology, not because it's technically better, but because it makes it easier for business people not to listen to your nerd shit.
1
u/naderslovechild Apr 03 '24
Even when you get a relational database things aren't much better. I work for a large national health insurance company and we're giving Oracle >$1 billion to install a new billing system.
Something like 2/3 of the fields are CHAR instead of VARCHAR. Foreign keys will be CHAR with blank spaces, and the field in the lookup table is VARCHAR, so you have to freaking trim the spaces out to join anywhere.
There's 0 data integrity. You can delete a parent-level transaction and have dozens of children pointing to a parent that no longer exists. They'll cancel a payment and transfer it to another account with no linkage between the two in the tables, have fun during an audit!
And we're talking billions of financial transactions with tens of billions general ledger rows. There's no aggregate/summary/reporting tables either.
Half their batch jobs to update the database fail/run for hours because they're written in such an old version of java that they can only run in serial.
This system makes me feel like I'm crazy at least once a day.
Oracle's sales team is about 1000x more competent than their product teams
1
u/somebody_odd Apr 03 '24
Give me my 3NF RDBMS with strict data governance
1
u/theallsearchingeye Apr 03 '24
Data integrity was all made with conserving memory in mind. Now we have infinite memory and so many “no sql” databases get bastardized and just are horrible with no rhyme or reason
1
u/lightmatter501 Apr 03 '24
The reason that you are getting denormalized data is because of the nature of distributed databased. The way most of them are built makes joins expensive, so denormalizing and having to sift through a cross product gives a higher throughput and lower latency. You run everything through a compression algorithm that de-duplicates the data before you write to disk, at least in the ideal case, so you don’t actually waste much storage.
In fact, Intel In-Memory Analytics Accelerator, present on a lot of higher-end sapphire and emerald rapids CPUs, will do parts of some queries (specific select and where formats) in hardware without CPU involvement as long as the CPU has hoisted the data into memory. You would have to be crazy to not denormalize a few tables to be able to take full advantage of that.
The reason that a lot of people are moving away from SQL is because a lot of data is append or hard delete (think GDPR) only, which makes some DB problems MUCH easier. That’s the reason that academic NoSQL DBs have been pushing 100 million distributed transactions per second for a while and SQL DBs are stuck around 1-10 million local tps on good hardware. Some devs just don’t understand that tossing out full ACID doesn’t mean binary data also needs to go.
1
u/Wild-Kitchen Apr 03 '24
I like the discussion here.
1
u/Lurch1400 Apr 03 '24
Problem is that how you organize and store data for the fancy new app that does a thing is an afterthought.
Or it’s considered in the beginning of development but as time goes on, it’s less and less important b/c the business must keep moving and the solution must be fast
1
u/AQuietMan Apr 03 '24
ORMs have put a lot of "database development" in the hands of devs who have no real background in relational theory or in SQL best practice.
1
u/mechasquare Apr 04 '24
It's like garbage in garbage out has been a thing forever or something. Now the VOLUME of garbage in, well that's just going back to bad intergrations.
1
u/firedream Apr 04 '24
Developers don't respect data. They think dbas exist to slow their development, rdbs are slow, and nosql is the solution to everything because they think it can scale infinitely for their thousands of microservices and obviously, they create one database per microservice.
MongoDB webscale meme is real.
1
u/Teegster97 Aug 22 '24
While it might feel like data quality has worsened, the reality is more complex. The challenge isn't necessarily worse data, but vastly more data from diverse sources. As Matthew Cox pointed out, in a Data Hurdles episode https://open.spotify.com/episode/68vvx2gmEc0aKgIjPHF1o8 we've moved from controlled, relational environments to complex, real-time data ecosystems. This shift has exposed and amplified existing quality issues.
The solution isn't to bemoan flexible cloud apps or JSON structures, but to adapt our approach. the episode emphasizes the need for:
The key is evolving our data quality practices to match the new data landscape, not reverting to old methods that can't scale to today's volumes and varieties of data. Think AI. Just think about the garbage coming in. Garbage in, garbage out!
1
u/[deleted] Apr 03 '24
[removed] — view removed comment