how do modern data warehouses tackle frequent small writes? esp. when streaming data is one of the sources? [closed]

Question

So for many days, I had a question in mind.

How do modern data warehouses tackle frequent small writes? esp. when streaming data is one of the sources?
e.g. Kafka/Kinesis => DW(Snowflake, Teradata, Oracle ADW, etc)

I was under the impression that since data warehouse tables are typically highly denormalized and columnar (for quick performance for reporting queries to avoid joins) they are slow for frequent small writes, but have good performance for reporting style SELECT statements. Hence the concept of bulk nightly uploads from OLTP data sources to OLAP data warehouses.

• What has changed in the modern DW internal architecture?
• Is there a staging area within DW itself, where data lands and then it is aggregated, stats are collected and then denormalized before it finally rests into actual DW tables powering the reporting?

I am interested in knowing how does it internally works at a high level.

I know this is a basic question, but this is my understanding from my school days hence I am pretty sure it is out of date.

Answer 1

As mustaccio already indicated, frequent small updates and high-volumes querying are fundamentally incompatible.

The main reason is that "frequent small updates" is a characteristic of an operational database, and that typically requires the DBMS to do whatever it takes (e.g. locking) to guard data integrity, and that typically gets in the way of the high-volumes querying that is typical of DW kind of use. (You could do the querying in 'disregard integrity' mode (e.g. Read Uncommitted isolation), and then the issue is somewhat different because then it's only a matter of sufficient memory and processors but of course that comes at the expense of dependability of the results.)

Locking schemes like MVCC theoretically address the issue, but they can be resource hogs of their own so they don't really address the problem, merely shift it elsewhere.

Architectures like IDAA (Integrated Data Analytics Accelerator) put the entire DW and the offloading to it under exclusive control of the DBMS. They work, but it does mean you never really know very precisely what data you're looking at when you're using the DW part.

So not only are you unlikely to find a DBMS with the capability you describe today, you'll also be unlikely to see one emerge in the short-to-mid-term future.

Answer 2

Data warehouse is a concept, not a product, so the primary question, "how do modern data warehouses tackle <whatever>" is this: however you design them.

To date, in my opinion, none of the major DBMSes are able to simultaneously allow fast trickle inserts and high performance analytic queries. Vertica and ClickHouse come quite close, by way of various limitations; neither can handle updates efficiently.

In-memory buffering, staging areas, "data•lake•houses" are all answers to the main design question; you'll find your own answer or buy [into] one of those that are already on the market.