Introducing dbt + Materialize
materialize.com
Introducing dbt + Materialize
1–10 of 26 posts
Re: Introducing dbt + Materialize
#2Relevant related post: A data pipeline is just a materialized view: https://nchammas.com/writing/data-pipeline-materialized-view
Re: Introducing dbt + Materialize
#3Relevant related post: A data pipeline is just a materialized view: https://nchammas.com/writing/data-pipeline-materialized-view
Neat! We're totally on the same page--incremental view maintenance not only makes materialized views a useful building block for data pipelines, it can make them much simpler, too!
Re: Introducing dbt + Materialize
#4Materialize is a really interesting solution, and I love for what it stands.
But the documentation is missing more details about the architecture overview. A single update could cause many gigabytes of data to shift on the materialize view, and I do not understand how Materialize would handle that scale.
Re: Introducing dbt + Materialize
#5Materialize seems really, really interesting. To the point where my crotchety old man senses are telling me that I should be careful about getting too excited about new technology.
Are there any interesting case studies of people pushing the edge of what's possible? For my use case, I have max throughput of 10k updates/second potentially materialized into tens of thousands of different views (with some good partition keys available, if needed).
Re: Introducing dbt + Materialize
#6Materialize is a really interesting solution, and I love for what it stands. But the documentation is missing more details about the architecture overview. A single update could cause many gigabytes of data to shift on the materialize view, and I do not understand how Materialize would handle that scale.
At the moment, it does it by not allowing updates at all.
It also forbids some parts of SQL that could get you update-like functionality, like appending new versions of records to a stream and then running
select * from (select *, row_number() over (partition by pk order by updated_at desc) from stream) where row_number = 1
(Boy I wish there was less awkward way to do this.)
EDIT: I'd love to ship bunch of data from few CDCs to Materialize and then run realtime reporting on that but without updates or window functions, Materialize can't do that just yet.
EDIT2: The part about updates isn't true, see below.
Re: Introducing dbt + Materialize
#7Relevant related post: A data pipeline is just a materialized view: https://nchammas.com/writing/data-pipeline-materialized-view
Neat! We're totally on the same page--incremental view maintenance not only makes materialized views a useful building block for data pipelines, it can make them much simpler, too!
Mmmh I've been thinking a lot about generated/computed fields as I wanted to use them in pg. They were introduced in pg12 but only materialized.
Re: Introducing dbt + Materialize
#8Materialize is a really interesting solution, and I love for what it stands. But the documentation is missing more details about the architecture overview. A single update could cause many gigabytes of data to shift on the materialize view, and I do not understand how Materialize would handle that scale.
At the moment, it does it by not allowing updates at all. It also forbids some parts of SQL that could get you update-like functionality, like appending new versions of records to a stream and then running select * from (select *, row_number() over (partition by pk order by updated_at desc) from stream) where row_number = 1 or something. (Boy I wish there was less awkward way to do this.) EDIT: I'd love to ship bunch…
We (I work at Materialize) actually do support updates! Our CDC sources support them, as well as any source using an UPSERT envelope (more info here: https://materialize.com/docs/sql/create-source/text-kafka/#u...).
As per your second point, I have a less awkward way for you! Materialize supports a top-k idiom (https://materialize.com/docs/sql/idioms/#top-k-by-group) that is hopefully a bit more clear.
Re: Introducing dbt + Materialize
#9Materialize is a really interesting solution, and I love for what it stands. But the documentation is missing more details about the architecture overview. A single update could cause many gigabytes of data to shift on the materialize view, and I do not understand how Materialize would handle that scale.
What you're asking about is the magic at the heart of Materialize. We're built atop an open-source incremental compute framework called Differential Dataflow [0] that one of our co-founders has been working on for ten years or so.
The basic insight is that for many computations, when an update arrives, the amount of incremental compute that must be performed is tiny. If you're computing `SELECT count(1) FROM relation`, a new row arriving just increments the count by one. If you're computing a `WHERE` clause, you just need to check whether the update satisfies the predicate or not. Of course, things get more complicated with operators like `JOIN`, and that's where Differential Dataflow's incremental join algorithms really shine.
It's true that there are some computations that are very expensive to maintain incrementally. For example, maintaining an ordered query like
SELECT * FROM relation ORDER BY col
Materialize can still be quite a useful tool here, though! You can use Materialize to incrementally-maintain the parts of your queries that are cheap to incrementally maintain, and execute the other parts of your query ad hoc. This is in fact how `ORDER BY` already works in Materialize. A materialized view never maintains ordering, but you can request a sort when you fetch the contents of that view by using an `ORDER BY` clause in your `SELECT` statement. For example:
CREATE MATERIALIZED VIEW v AS SELECT complicated FROM t1, t2, ... -- incrementally maintained
SELECT * FROM v ORDER BY col LIMIT 5 -- order and limit computed ad hoc, but still fast
Re: Introducing dbt + Materialize
#10Earlier quoted context omitted.
At the moment, it does it by not allowing updates at all. It also forbids some parts of SQL that could get you update-like functionality, like appending new versions of records to a stream and then running select * from (select *, row_number() over (partition by pk order by updated_at desc) from stream) where row_number = 1 or something. (Boy I wish there was less awkward way to do this.) EDIT: I'd love to ship bunch…
We (I work at Materialize) actually do support updates! Our CDC sources support them, as well as any source using an UPSERT envelope (more info here: https://materialize.com/docs/sql/create-source/text-kafka/#u... ). As per your second point, I have a less awkward way for you! Materialize supports a top-k idiom ( https://materialize.com/docs/sql/idioms/#top-k-by-group ) that is hopefully a bit more clear.
Right, I saw no update in the SQL keyword list, and saw some (I guess old now) talk about Materialize where the speaker mentioned no updates, sorry!