4 ms·

1.If you rely on an ETL to automagically remove duplicates you are lying to yourself because you treat external symptoms, the illness is the underlying query an

1.If you rely on an ETL to automagically remove duplicates you are lying to yourself because you treat external symptoms, the illness is the underlying query and hiding this fact can only lead to more headaches later on. 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.

barrkel 7y ago
I think you misunderstood me on the duplicates. I'm talking about how easy it is to e.g. left join an attribute from another table and under-qualify the association so that you accidentally pull in unwanted rows. It's not that you have duplicates in the data; the data is correct and the model is correct. It's rather that the query is incorrect and the set of tests, owing to them typically being written by the same person who wrote the code, doesn't populate the association table with more rows than expected. 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.
- Twisell 7y ago
  It's actually worst than that because cartesian product is the default mode of SQL. JOIN clauses are already a kind of mitigation that force you to be more explicit. 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 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.
  - barrkel 7y ago
    Yes, I know, everybody knows. My point is that SQL doesn't help you catch those errors where your join clause doesn't match rows uniquely. You can know that the product is the default and still forget that extra bit in the predicate.

barrkel 7y ago

I think you misunderstood me on the duplicates. I'm talking about how easy it is to e.g. left join an attribute from another table and under-qualify the association so that you accidentally pull in unwanted rows. It's not that you have duplicates in the data; the data is correct and the model is correct. It's rather that the query is incorrect and the set of tests, owing to them typically being written by the same person who wrote the code, doesn't populate the association table with more rows than expected. 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.

Twisell 7y ago
It's actually worst than that because cartesian product is the default mode of SQL. JOIN clauses are already a kind of mitigation that force you to be more explicit. 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 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.
- barrkel 7y ago
  Yes, I know, everybody knows. My point is that SQL doesn't help you catch those errors where your join clause doesn't match rows uniquely. You can know that the product is the default and still forget that extra bit in the predicate.