I'm now officially a bleeding edge DevOps with 10 years expertise on the brand new "old school" ELT (Extract,Load, Transform).
LoL
I'm now officially a bleeding edge DevOps with 10 years expertise on the brand new "old school" ELT (Extract,Load, Transform).
LoL
Long scripts of SQL with intermediate steps (e.g. building temp tables) are hard to reason about and debug, and especially painful when they fail only in rare production scenarios.
Developers find it a lot easier to reason on an item by item basis, and not on a set or batch basis, and it's very tricky to get error handling and transactions right on a batch basis unless you want to fail the whole batch.
Putting all the work on the database also means you need to scale up that layer. From another POV, it can be simpler to have cheap point lookup, secondary indexes in stores that are tuned for the specifics of the queries, and get performance back by scaling out the compute.
2.Testing for duplicate is a SQL one-liner
3.If your data model is well conceived you can actually unwanted 1-n relationship to be inserted in the first place. Yeah sharding database is coming late to the game but ACID compliance is a killer feature. Code to manage inconsistencies in NoSQL is way less trivial as far as I know... unless it's ok to have an inconsistent database... Guess it depend on use case but ACID seems nice for any important dataset.
I mention this because it's a bug I've seen crop up several times in production. When you write 'join' you're typically fetching data to add extra columns to your result set; it's relatively unusual to want a row product in the absence of group by & aggregates, or an ORM doing eager loading of a detail table. But nothing in SQL requires you to state your product expectations on the join, apart from left/right outer vs inner join. I personally think SQL could do with forcing people to be more explicit on this point.
I fully agree on the inconsistencies in NoSQL; I think the most sensible way to use NoSQL is as a document store without relations, and keep the relations somewhere else. Manually maintaining invariants (especially of the denormalized bidirectional kind that some stores require) in an environment with less than complete transaction support isn't my idea of fun.
With modern SQL even if you only mention no table at all in the FROM clause you can get "duplicate" if you use set returning function (see: https://www.postgresql.org/docs/current/functions-srf.html).
This is both why you should be careful and why this is a really powerful tool to manipulate datasets. But usually "everything is a cartesian product" is one of the first (and yes very important) thing you learn of SQL.