1000% this Almost every solution I've seen to tooling that is supposed to "avoid data analysts having to learn SQL" is far more costly, requires engineering staffing, performs much slower and sometimes requires the analyst to instead learn some esoteric less powerful language ...

If your job is to slice&dice data all day, and can't be bothered to learn SQL, I don't know what to say.

The data lake problem is specifically due to microservices or SOA. People love to separate customers from orders until they realize that you want to do complex filtering on customers and join it to their orders. Then everyone says "oh crap" when they realize they've created a problem without a great solution.

Yes. Fundamentally there is almost no data that is not relational in some aspect. Any given document, you can virtually instantly start picking out "ok that could be a foreign key...", and if one does not exist, it certainly will before too many sprints go by. And most usages of NoSQL are essentially equivalent to a row within a table, especially given many NoSQL solutions have bugs or performance issues with deeply nested documents (ran into that with SOLR - deep pagination can give incorrect results with nested documents).

Personally I view a true document (not a table row turned into JSON) as being the deeply-nested kind, and ideally generated from the relational data itself, to allow different "dimensionalities" to be represented without needing pivots/windows/analytical queries, and that's very seldom what I see it being used for in practice. Again, most people just have a RDBMS row but stored in JSON.

example: in the "netflix" example, your movies, your actors, your users, your likes, etc are all relational, and then you build a document collection that is good for searching movies, a collection that is good for displaying user data/history/settings, a collection for displaying actors' filmography, etc, but all are generated from the same actual, consistent relational data.

Storing Json data into a highly structured RDBMS table can be problematic if any document contains arrays or nested documents.

I built a new general-purpose data management system that uses key-value stores that I invented to attach meta-data tags to objects. These key-value stores can also be used to create relational tables.

Because each table is basically a columnar store, I can map multiple values to each row key to create a 3D table. It seems ideal for importing Json data where any item in a document can be an array of values. I am trying to figure out how useful this system might be to the average DBA or NoSql user.

See a quick demo at https://www.youtube.com/watch?v=1b5--ibFhWo

what I'm saying is, use the relational DB for OLTP, but export in a JSON document format to NoSQL in whatever document shapes are efficient for various services. And that can be multiple different shapes generated from the same set of relational "ground truth", if various services need different "views" to run efficiently.

The idea is you always have a relational "source of truth" and optimize that for OLTP, but also get the scalability benefits of documents/microservices/etc by having data already pre-coalesced/pre-digested into your correct format(s), so you're not doing complex analytical/window/aggregation queries on the RDBMS for every request. You run the analytical queries once, convert the result to json, and store that in the NoSQL.

Of course you still potentially have some "sync time" between the OLTP and the final commit to all the various nosql collections... unless you hold OLTP locks until everything is synced, which would be excessive. But this goes back to CAP and there's no magic wand for that - you can either put everything inside the RDBMS and take the performance hit, or you can have external nosql read replicas and accept the inconsistency due to the sync time, or you can hold locks until both systems are consistent at the cost of "availability" (updatability).

How different is this from using pg’s jsonb field type? Which is also queryable.

What are the advantages/disadvantages? Or what am I misunderstanding?

To be honest, I haven't played around with the jsonb feature of pg enough to know which is better. I do know that the queries of data in my system average about 10x faster than regular pg tables for the same data set. Also my tables do not need a separate indexing step in order to achieve maximum speed for any query. Do you have a data set in pg you created using jsonb? If you want to try my system, the beta is available for free download at: https://didgets.com/download

Wow, I've previously had exactly same feelings about couple of people from whom I heard "data lakes".

Only thing worse is "data lakehouse"

I couldn't help but laugh the first time I saw that.

Should we go ahead and file copyright on Data Ocean and Data Beachhouse?

Careful, if you build wrong you'll get wiped out by a data hurricane.

“We used to dream about having our data in a warehouse! We had to have our data in a lake!”

I always wonder, who looks at their pricing model and decides "Paying multiple dollars to query on a few GB of data sounds reasonable"?

Seriously? A $10k dedicated server would have a payback period measured in minutes.

I don't think it's always that easy. I work on some large databases (1TB+) where even queries utilizing indexed columns still take prohibitively long to run. Sure we could look into partitioning (and we likely might) but then more work is progressively falling on engineering to keep the system running for the standards of analysts but not necessarily the product.

BigQuery in these cases has been very useful. Everything I throw at it returns in seconds. I can also do more text comparison queries that on a standard RDBMS would have required a full text index (which would probably be huge). But with BigQuery even those queries take seconds and I don't have to take production resources to view it.

Sounds like you need a better index :-)

If you need fulltext search, I've enjoyed using Sphinx and Lucene in the past. Is the column you want to do fulltext on 1 TB? 1 TB of RAM in 128 gig DIMMs is <$10k these days, so might as well get at least that much if you're running anything like the bills these guys are talking about.

Importantly it depends on how often these things are run and who has to maintain a server.

The example in the article is 40k/day for querying on 14 GB of data, every second. You can do that on a ~100/month machine or a somewhat modern laptop.

Our entire, hundreds of millions of dollars business with hundreds of millions of orders of historical data can be "explored" by a data scientist on an average macbook.

If your data scientists can't do data science, then that's pretty bad

It depends on the business unit. Some don't have the ability to run a technology group to run a $10K dedicated server.

Query bills of $40k a day will pay for a lot of staff, or a lot of outside expertise to administer it for you.

I feel like for 1.4 million USD a year I could solve that problem for them.

But they have the budget to run $10K of queries a day?

The person who doesn't have to pay the bill.

I think it’s worth mentioning that tinybird doesn’t necessarily charge per query, the billing model seems more geared towards API usage, though queries are the underpinning.

Also it’s not unreasonable to see people spending $10K+ a day in Snowflake because bad practices just like this.

I heard at a certain Professional Social Network company that some of their analysts would slam their db cluster with inefficient queries until it rebooted, and first one to rerun their query would finally get a result.

Learn SQL ffs.

>Avoid database systems that charge per query

oh my god

i'm currently $13k/mo in dynamodb costs because of this whereas the same requirements sql database costs $2k/mo