A Data Pipeline Is a Materialized View
nchammas.com
A Data Pipeline Is a Materialized View
1–10 of 48 posts
Re: A Data Pipeline Is a Materialized View
#2Great article, one quibble: there isn’t really a clear dividing line between batch and streaming. If you process data one row at a time, that is clearly a streaming pipeline, but most systems that call themselves streaming actually process data in small batches. From a user perspective, it’s an implementational detail, the only thing you care about is the latency target.
Nearly all data sources, including the changelogs of databases, are polling-based APIs, so you’re getting data from the source in (small) batches. If your goal is to put this data into a data warehouse like Snowflake, or a system like Materialize, the lowest latency thing you can do is just immediately put that data into the destination. I sometimes see people put a message broker like Kafka in the middle of this process, thinking it’s going to imbue the system with some quality of streamyness, but this can only add latency. People are often surprised that we don’t use a message broker at Fivetran, but when you stop and think about it there’s just no benefit in this context.
Re: A Data Pipeline Is a Materialized View
#3Great article, one quibble: there isn’t really a clear dividing line between batch and streaming. If you process data one row at a time, that is clearly a streaming pipeline, but most systems that call themselves streaming actually process data in small batches. From a user perspective, it’s an implementational detail, the only thing you care about is the latency target. Nearly all data sources, including the changel…
> If you process data one row at a time, that is clearly a streaming pipeline, but most systems that call themselves streaming actually process data in small batches. From a user perspective, it’s an implementational detail, the only thing you care about is the latency target.
Author here. 100% agreed.
As an aside, I just came across your post about how Databricks is an RDBMS [0]. I recently wrote a similar article from a slightly more abstract perspective [1].
Having worked heavily with RDBMSs in the first part of my career, I feel like so many of the concepts and patterns I learned about there are being re-expressed today with modern, distributed data tooling. And that was part of my inspiration for this post about data pipelines.
Re: A Data Pipeline Is a Materialized View
#4Great article, one quibble: there isn’t really a clear dividing line between batch and streaming. If you process data one row at a time, that is clearly a streaming pipeline, but most systems that call themselves streaming actually process data in small batches. From a user perspective, it’s an implementational detail, the only thing you care about is the latency target. Nearly all data sources, including the changel…
I'd say that the difference was the systems behavior in the presence of IO, and it's pretty important in my experience. Micro-batching systems hold up processing while waiting for IO but proper streaming implementations continue using cpu for elements at other points in the stream, very roughly.
Re: A Data Pipeline Is a Materialized View
#5As someone who basically sticks everything possible into Postgres, this is interesting! Streaming tools don't automatically cache things you need? I guess it's about time they do! Postgres, for instance, has a robust LRU mechanism that deals with OLTP quite competently. OLAP too if your indices are thought-out.
Also, although built-in materialized views don't allow partial updates in Postgres, you can get a similar thing with normal tables and triggers. Hashrocket discussed that strategy here-- https://hashrocket.com/blog/posts/materialized-view-strategi... .
Re: A Data Pipeline Is a Materialized View
#6As someone who basically sticks everything possible into Postgres, this is interesting! Streaming tools don't automatically cache things you need? I guess it's about time they do! Postgres, for instance, has a robust LRU mechanism that deals with OLTP quite competently. OLAP too if your indices are thought-out. Also, although built-in materialized views don't allow partial updates in Postgres, you can get a similar t…
Of the traditional RDBMSs, I believe Oracle has the most comprehensive support for materialized views, including for incremental refreshes [0].
As early as 2004, developers using Oracle were figuring out how to express complex constraints declaratively (i.e. without using application code or database triggers) by enforcing them on materialized views [1].
It's quite impressive, but this level of sophistication in what materialized views can do and how they are used does not seem to have spread far beyond Oracle.
[0]: https://docs.oracle.com/database/121/DWHSG/refresh.htm#DWHSG...
[1]: https://tonyandrews.blogspot.com/2004/10/enforcing-complex-c...
Re: A Data Pipeline Is a Materialized View
#7As someone who basically sticks everything possible into Postgres, this is interesting! Streaming tools don't automatically cache things you need? I guess it's about time they do! Postgres, for instance, has a robust LRU mechanism that deals with OLTP quite competently. OLAP too if your indices are thought-out. Also, although built-in materialized views don't allow partial updates in Postgres, you can get a similar t…
Of the traditional RDBMSs, I believe Oracle has the most comprehensive support for materialized views, including for incremental refreshes [0]. As early as 2004, developers using Oracle were figuring out how to express complex constraints declaratively (i.e. without using application code or database triggers) by enforcing them on materialized views [1]. It's quite impressive, but this level of sophistication in what…
SQL Server has indexed views, which is "real time" refresh since it's a separate clustered index that's written to at the same time as the base tables.
Re: A Data Pipeline Is a Materialized View
#8The Grand Unified Programming Theory: The Pure Function Pipeline Data Flow with principle-based Warehouse/Workshop Model
https://github.com/linpengcheng/PurefunctionPipelineDataflow
Re: A Data Pipeline Is a Materialized View
#9Earlier quoted context omitted.
Of the traditional RDBMSs, I believe Oracle has the most comprehensive support for materialized views, including for incremental refreshes [0]. As early as 2004, developers using Oracle were figuring out how to express complex constraints declaratively (i.e. without using application code or database triggers) by enforcing them on materialized views [1]. It's quite impressive, but this level of sophistication in what…
SQL Server has indexed views , which is "real time" refresh since it's a separate clustered index that's written to at the same time as the base tables. https://www.sqlshack.com/sql-server-indexed-views/
Do you know how sophisticated SQL Server is about updating indexed views? How granular are the locks when, for example, an indexed aggregate is updated? That will have a big impact on write performance when there are many concurrent updates.
A long time ago, I tried to use SQL Server indexed views to maintain a bank account balances table based on transaction history [0].
I forget what I ended up doing, but I remember that one of the downsides of using indexed views was that they didn't support any constraints. There are many restrictions on what you can and can't put in a SQL Server indexed view [1].
In this regard, I think Oracle has a more mature materialized view offering, though I personally haven't used Oracle much and don't know how well their materialized views work in practice.
[0]: https://dba.stackexchange.com/q/5608/2660
[1]: https://docs.microsoft.com/en-us/sql/relational-databases/vi...
Re: A Data Pipeline Is a Materialized View
#10Whoever wrote this hasn't worked on medium-complicated data pipelines / ETL logic.
It's pretty non-trivial to try to make an effective-dated slowly changing dimension with materialized views.
A good tool makes the medium-difficulty stuff easy, and the complicated stuff possible. Materialized views do only the former.
I would love to be wrong about this.