A Data Pipeline Is a Materialized View • Hacker News Board

Re: A Data Pipeline Is a Materialized View

#41

Post by biellls » Mon, Feb 22, 2021, 6:13 PM UTC

snidane wrote: post #25

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…

Ultimatelly there will never be a pure streaming system processing, one record at a time, in real world. Any such system contains a busy loop somewhere inside polling the source, say each 100ms, and unless it shares a lock with the source system, it will never guarantee that there won't be more items in the source queue within those 100ms intervals. Therefore all such systems are at best (micro)batch systems. Also st…

How would this look like? Specifically, how do you know if something has been deleted? Do you compare the primary keys in your materialized view (the last snapshot you have of the data) with the source data to know what changed? Isn't that really hard to do if they're not in the same database?

In real life most people prefer taking a full snapshot each day because they don't have good solutions to these problems in batch systems (CDC is another story).

Re: A Data Pipeline Is a Materialized View

#42

Post by snidane » Mon, Feb 22, 2021, 6:55 PM UTC

biellls wrote: post #41

snidane wrote: post #25

Earlier quoted context omitted.

Ultimatelly there will never be a pure streaming system processing, one record at a time, in real world. Any such system contains a busy loop somewhere inside polling the source, say each 100ms, and unless it shares a lock with the source system, it will never guarantee that there won't be more items in the source queue within those 100ms intervals. Therefore all such systems are at best (micro)batch systems. Also st…

How would this look like? Specifically, how do you know if something has been deleted? Do you compare the primary keys in your materialized view (the last snapshot you have of the data) with the source data to know what changed? Isn't that really hard to do if they're not in the same database? In real life most people prefer taking a full snapshot each day because they don't have good solutions to these problems in b…

Source data should not experience deletes or updates, otherwise backfills will not work. Deletes can be handled by mirroring source data. Updates are difficult and will need a full CDC system to capture them. Better is to negotiate with data provider to send data updates as appends and never to delete from history.

The whole point of ETL is to bring data from one database to another. The comparison of source and destination primary keys can be done in python outside of db. And should be done on entire partitions instead of individual rows. Eg. you only consider which 'day' partitions have been loaded, not which rows have been loaded.

Re: A Data Pipeline Is a Materialized View

#43

Post by glogla » Mon, Feb 22, 2021, 7:39 PM UTC

snidane wrote: post #42

biellls wrote: post #41

Earlier quoted context omitted.

How would this look like? Specifically, how do you know if something has been deleted? Do you compare the primary keys in your materialized view (the last snapshot you have of the data) with the source data to know what changed? Isn't that really hard to do if they're not in the same database? In real life most people prefer taking a full snapshot each day because they don't have good solutions to these problems in b…

Source data should not experience deletes or updates, otherwise backfills will not work. Deletes can be handled by mirroring source data. Updates are difficult and will need a full CDC system to capture them. Better is to negotiate with data provider to send data updates as appends and never to delete from history. The whole point of ETL is to bring data from one database to another. The comparison of source and dest…

That kind of approach is fine for special cases like time series or logs or events, but "no updates or deletes" is never going to be true for arbitrary data.

"Negotiating with data provider" is never going to happen - SAP or IBM or whatever vendor of whatever you're integrating is not going to change how their systems work to make your life easier - more likely they would use it as an opportunity to pitch their reporting solution instead.

Meaning from simple data movement, you get need for CDC on source end, then the simple incremental movement, then deduplication on target end - and that one is pretty computationally expensive.

For small data and low refresh frequencies (like singular gigabytes in source size, so hundreds of megabytes in columnar, updated daily), this dance might not be worth it compared to daily full snapshots.

I wish you were right though, my life would be hella easier.

Re: A Data Pipeline Is a Materialized View

#44

Post by smknappy » Mon, Feb 22, 2021, 7:45 PM UTC

Great post! Just heard about this from one of our customers who slacked me with "He is describing Ascend.io!" :-)

Having spent 15+ years writing big data pipelines and building teams who do the same, I couldn't agree more... the conceptual model we're all quite comfortable with is this notion of cascading, materialized views. The challenge, however, is that they are expensive to maintain in a big data pipeline context -- paid either in system resource cost, or developer cost. The only reasonable way to achieve this is a fundamental shift away from imperative pipelines, and to declarative orchestration (a few folks mention this as well). We've seen this in other domains with technologies like React, Terraform, Kubernetes, and more to great success.

I've written about this in tldr form @ https://www.ascend.io/data-engineering/, namely the evolution from ETL, to ELT, to (ETL)+, to Declarative. A also gave a more detailed tech talk on this topic @ https://www.youtube.com/watch?v=JcVTXC0qPwE.

For those who are interested in a longer white paper on data orchestration methodologies, namely imperative vs declarative, this is a good read: https://info.ascend.io/hubfs/Whitepapers/Whitepaper-Orchestr...

Re: A Data Pipeline Is a Materialized View

#45

Post by smknappy » Mon, Feb 22, 2021, 7:48 PM UTC

snidane wrote: post #30

endymi0n wrote: post #29

Earlier quoted context omitted.

https://www.getdbt.com/ comes extremely close in my eyes and even tackles the documentation and infra-as-code aspect. We went all in half a year ago and never looked back.

DBT is interesting, but is far from what I'm describing. 1. It is only for structured SQL, not for arbitrary data. I can't use it to unpack raw zipped data for example 2. It couples logic for data transformation and view state management. Actually it makes you do it yourself, so it doesn't really help at all. You'll get burned by storing view state together with your data, eg. when a batch increment contains no data.…

Have a look @ https://www.ascend.io -- addresses the issues you highlight: 1. SQL + Python + Java + Scala 2. state management fully automated 3. automatic incremental materialized views

Re: A Data Pipeline Is a Materialized View

#46

Post by glogla » Mon, Feb 22, 2021, 8:49 PM UTC

snidane wrote: post #25

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…

Ultimatelly there will never be a pure streaming system processing, one record at a time, in real world. Any such system contains a busy loop somewhere inside polling the source, say each 100ms, and unless it shares a lock with the source system, it will never guarantee that there won't be more items in the source queue within those 100ms intervals. Therefore all such systems are at best (micro)batch systems. Also st…

I think something like singer.io can be "microframework of ETL" or rather ELT which is a better idea anyway. But of course, it has its own challenges.

Re: A Data Pipeline Is a Materialized View

#47

Post by snidane » Mon, Feb 22, 2021, 9:18 PM UTC

glogla wrote: post #43

snidane wrote: post #42

Earlier quoted context omitted.

Source data should not experience deletes or updates, otherwise backfills will not work. Deletes can be handled by mirroring source data. Updates are difficult and will need a full CDC system to capture them. Better is to negotiate with data provider to send data updates as appends and never to delete from history. The whole point of ETL is to bring data from one database to another. The comparison of source and dest…

That kind of approach is fine for special cases like time series or logs or events, but "no updates or deletes" is never going to be true for arbitrary data. "Negotiating with data provider" is never going to happen - SAP or IBM or whatever vendor of whatever you're integrating is not going to change how their systems work to make your life easier - more likely they would use it as an opportunity to pitch their repor…

We are probably refering to different scenarios. When purchasing data for analytics, data providers are usually sophisticated enough to know not to modify their data history. With new ones, data delivery format can be negotiated.

Data providers usually wait for a day or something worth of data to collect before validating and releasing it to customers.

For integrating some OLTP database updating in real time on the other hand, yes you will need CDC.

---

Most of data engineering is just incrementally adding new data to existing corpus and then running a big batch job to dedup, sort or partition. This last step surely is computationally expensive, but at least it is conceptually simple and can be solved by throwing hardware at it. The first part of incremental updates is what imo causes more troubles.

Re: A Data Pipeline Is a Materialized View

#48

Post by gunnarmorling » Tue, Feb 23, 2021, 12:10 AM UTC

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…

> there’s just no benefit in this context

I'd beg to differ; having a message broker as part of the streaming pipeline allows to set up multiple consumers (e.g. Snowflake and Elasticsearch and something else). Also, depending on the settings, you can replay streams from the beginning.

That's why we see >90% of Debezium users running with Kafka or alternatives. There is a group of point-to-point users (e.g. caching use cases) mostly via Debezium engine, but it's a small minority.