Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

A Data Pipeline Is a Materialized View

nchammas.com

Post reply on HN
Print view View original

11–20 of 48 posts

Re: A Data Pipeline Is a Materialized View

#11

Post by nchammas »

gregw2 wrote: post #10

Whoever 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.

Author here.

Are you thinking of a specific implementation of materialized views? Most implementations from traditional RDBMSs would indeed be too limiting to use as a general data pipeline building block.

The post doesn't argue that, though. It's more about using materialized views as a conceptual model for understanding data pipelines, and hinting at some recent developments that may finally make them more suitable for more widespread use.

From the conclusion:

> The ideas presented in this post are not new. But materialized views never saw widespread adoption as a primary tool for building data pipelines, likely due to their limitations and ties to relational database technologies. Perhaps with this new wave of tools like dbt and Materialize we’ll see materialized views used more heavily as a primary building block in the typical data pipeline.

Re: A Data Pipeline Is a Materialized View

#12

Post by atwebb »

gregw2 wrote: post #10

Whoever 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.

You can absolutely get complicated data models nailed using views. Some of the views get unwieldly and a bit long but it can be done. The catch is in incrementally loading the aggregate tables or self referencing (even then the underlying views are essentially functions to be included). I scanned the article and have followed materialize.io for a bit and built pipelines that handle what is essentially the awfulness of performantly updating materialized views.

I'm not a master but believe a core piece of truth for data:

Move the data as little as possible

If you can use federated query (cost/performance is acceptable), do so. If you can use materialized views, do so. Data replication has tons of issues, you almost always have the 2 generals problem and reconciliation / recompile procedures. If you don't move the data, the original storage is the source and it is always right.

I think I went way out of responding directly to you, I do think materialize.io and delta lake and declarative pipelines are the solution to 95% of the data problems out there.

I'm speaking conceptually about materialized views as system implementations differ.

Re: A Data Pipeline Is a Materialized View

#13

Post by taeric »

georgewfraser wrote: post #2

Great 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…

It is amusing how often we think adding work to the system will speed it up.

It is interesting when is works. :)

Re: A Data Pipeline Is a Materialized View

#14

Post by jgraettinger1 »

nchammas wrote: post #3
georgewfraser wrote: post #2

Great 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 f…

> I feel like so many of the concepts and patterns I learned about there are being re-expressed today with modern, distributed data tooling

So much this.

I work on Flow [0] at Estuary [1]; you might be interested in what we're doing. It offers millisecond latency continuous datasets that are _also_ plain JSON files in cloud storage -- a real-time data lake -- which can be declaratively transformed and materialized into other systems (RDBMS, key/value, even pub/sub) using precise, continuous updates. It's the materialized view model you articulate, and Flow is in essence composing data lake-ing with continuous map/combine/reduce to make it happen.

I was asked the other day if Flow "is a database" by someone who only wanted a 2-3 sentence answer, and I waffled badly. The very nature of "databases" are so fuzzy today. They're increasingly unboxed across the Cambrian explosion of storage, query, and compute options now available to us. S3 and friends for primary storage; on-demand MPP compute for query and transformation; wide varieties of RDBMSs, key/value stores, OLAP systems, even pub/sub as specialized indexes for materializations. Flow's goal, in this worldview, is to be a hypervisor and orchestrator for this evolving cloud database. Not a punchy elevator pitch, but there it is.

[0] https://estuary.readthedocs.io/en/latest/README.html [1] https://estuary.dev/

Re: A Data Pipeline Is a Materialized View

#15

Post by jgraettinger1 »

corneliusphi2 wrote: post #4
georgewfraser wrote: post #2

Great 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.

Exactly. You can have the amortization benefits of batching without adding extra latency, if you do optimistic pipelining. It's super powerful if your transaction sizes aren't linear, e.g. because you're doing in-memory aggregations.

Gazette consumers use this strategy: https://gazette.readthedocs.io/en/latest/consumers-concepts....

Re: A Data Pipeline Is a Materialized View

#16

Post by nchammas »

jgraettinger1 wrote: post #14
nchammas wrote: post #3

Earlier quoted context omitted.

> 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 f…

> I feel like so many of the concepts and patterns I learned about there are being re-expressed today with modern, distributed data tooling So much this. I work on Flow [0] at Estuary [1]; you might be interested in what we're doing. It offers millisecond latency continuous datasets that are _also_ plain JSON files in cloud storage -- a real-time data lake -- which can be declaratively transformed and materialized in…

Interesting project! (The interactive slides are cool btw.)

Could you share a bit about how engineers express data transformations in Flow?

From a quick look at the docs, it doesn't look like you are using SQL to do this, which is interesting since it bucks the general trend in data tooling.

Re: A Data Pipeline Is a Materialized View

#18

Post by lmm »

nchammas wrote: post #3
georgewfraser wrote: post #2

Great 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 f…

https://www.confluent.io/blog/turning-the-database-inside-ou... might be interesting if you haven't seen it already. The way I see it RDBMSes already had most of the tech, but they wrap it up in an opaque black box that can only be used one way, like those automagical frameworks that generate a full webapp from a couple of class definitions but then fall apart as soon as you want to do something slightly specialised. So the data revolution is really about unbundling all the cool RDBMS tech and moving from a shrinkwrapped database product to something more like a library that gives you full control over what happens when, letting you integrate your business logic wherever it makes sense.

Re: A Data Pipeline Is a Materialized View

#20

Post by gfody »

nchammas wrote: post #9
kthejoker2 wrote: post #7

Earlier quoted context omitted.

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 remembe…

if sql server can't incrementally maintain your view then it doesn't let you index it. that's why there are so many restrictions but on the flip side if you manage to workaround the restrictions then decent update performance is assured.
Post reply on HN