7 ms·

Introducing dbt + Materialize

109 points by jldlaughlin 6y ago

andrenotgiant 6y ago
Relevant related post: A data pipeline is just a materialized view: https://nchammas.com/writing/data-pipeline-materialized-view https://nchammas.com/writing/data-pipeline-materialized-view
- jldlaughlin 6y ago
  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!
  - touisteur 6y ago
    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.
  - sitkack 6y ago
    So it is CTEs all the way down, a bag of dags. A raw table is just a view over the raw data, a cooked view is just a view of views, repeat. Do you use any of the ideas of Noria? Cadence? This is great, but at what COST?!
Nican 6y ago
Materialize 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.
- glogla 6y ago
  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 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.
  - jldlaughlin 6y ago
    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/#upsert-envelope-details 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 https://materialize.com/docs/sql/idioms/#top-k-by-group) that is hopefully a bit more clear.
    - glogla 6y ago
      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!
      - jldlaughlin 6y ago
        No apologies necessary, we're an active work in progress! (And, hopefully, moving quickly!)
- benesch 6y ago
  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 would be quite expensive, because the arrival of a new value will change the ordering of all values that sort greater than the new value. 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 [0]: https://github.com/TimelyDataflow/differential-dataflow https://github.com/TimelyDataflow/differential-dataflow
  - Nican 6y ago
    Thank you for taking your time to write that up. To illustrate my point a little more: Suppose a customer has several resources, and each resource has several metrics. From my understanding, Materialized could be used to have an aggregated view of metrics per customer. The problem is that resources can also be migrated between customers. When a resource migrates between customers, the whole history of the customer changes. This could cause huge updates depended on how many resources are moved, or how many metrics per resource are being collected. I have a conundrum between doing the "customer-resource join" late, and causing huge CPU cost when running queries. Or making aggregates early, and then having huge Disk cost when migrating resources. At the moment, we just have daily jobs that aggregates the TBs of customer data daily, because there is no way to do the joins in real-time. Is Materialize designed to be able to handle something like this?
d_watt 6y ago
Materialize 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).
- albertwang 6y ago
  Nothing we can share publicly at the moment yet, but if you reach out and chat, we're more than happy to give you some numbers that I think will address what you're looking for!
efangs 6y ago
Is it possible to backfill a materialized view? I'm maybe confused, but does this potentially replace a traditional data lake or is it specifically for streaming applications?
- jldlaughlin 6y ago
  It is! We recently added support for S3 sources [0], which you could use to backfill data and union with a stream. To your other question, we're currently well-suited for streaming applications. Moving forward, as we add support for features like persistence, we could certainly replace at least parts of a traditional data lake. [0]: https://materialize.com/docs/sql/create-source/json-s3/ https://materialize.com/docs/sql/create-source/json-s3/
jdotjdot 6y ago
I have been waiting for this since the moment I first read about Materialize a year or two ago. I think there's still a lot of work to be done, but at heart, if you can pair technology like Materialize with an orchestration system like dbt, you can use dbt to keep your business logic extremely well organized, yet have all of your dependent views up to date all of the time, and use dbt even to use the same analytical layered views both for analytical AND operational purposes. The biggest issue I see is that it requires you to be all-in on Materialize, and as a warehouse (or as a database for that matter), it's surely not as mature as Snowflake or Postgres.
- arjunnarayan 6y ago
  Thank you for your kind words! We indeed have plenty of work to be done (and are thus hiring)! I'm curious however why you think this requires you to be all-in on Materialize. As you said better than I could have, dbt is amazing at keeping your business logic organized. Our intention is very much for dbt to standardize the modeling/business logic layer which allows you to use multiple backends as you see fit in a way that shares the catalog layer cleanly. Our hope is that you have some BigQuery/Snowflake job that you're tired of running up the bill hitting redeploy 5 times a day, and you can cleanly port that over to Materialize with little work because the adapter is taking care of any small semantic differences in date handling, or null handling, etc. So Materialize sits cleanly side-by-side with Snowflake/BigQuery, and you're choosing whether you want things incrementally maintained with a few seconds of latency by Materialize, or once a day by the batch systems. My view is you're likely going to want to do data science with a batch system (when you're in "learning mode" you try and keep as many things fixed, including not updating the dataset), and then if the model becomes a critical automated pipeline, rather than rerunning the model every hour and uploading results to a Redis cache or something, you switch it over to Materialize, and don't have to every worry about cache invalidation.
  - abrazensunset 6y ago
    In that situation (dual usage modes) I think I'd rather have the primary data store be Materialize, and just snapshot Materialize views back to your warehouse (or even just to an object store). Then you could use that static store for exploration/fixed analysis or even initial development of dbt models for the Materialize layer, using the Snowflake or Spark connectors at first. When something's ready for production use, migrate it to your Materialize dbt project. The way dbt currently works with backend switching (and the divergence of SQL dialects with respect to things like date functions and unstructured data), maintaining the batch and streaming layers side by side in dbt would be less wasteful than the current paradigm of completely separate tooling, but still a big source of overhead and synchronization errors. If the community comes up with a good narrative for CI/CD and data testing in flight with the above, I don't think I'd even hesitate to pull the trigger on a migration. The best part is half of your potential customers already have their business logic in dbt.
esteer 6y ago
AWS launched a feature last re:Invent for materialized views - https://aws.amazon.com/glue/features/elastic-views/ https://aws.amazon.com/glue/features/elastic-views/
victor106 6y ago
If I have a batch file in EDI format how can I use DBT and/or Materialize?

109 points by jldlaughlin 6y ago

Relevant related post: A data pipeline is just a materialized view: https://nchammas.com/writing/data-pipeline-materialized-view https://nchammas.com/writing/data-pipeline-materialized-view

jldlaughlin 6y ago
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!
- touisteur 6y ago
  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.
- sitkack 6y ago
  So it is CTEs all the way down, a bag of dags. A raw table is just a view over the raw data, a cooked view is just a view of views, repeat. Do you use any of the ideas of Noria? Cadence? This is great, but at what COST?!

Nican 6y ago

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

glogla 6y ago
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 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.
- jldlaughlin 6y ago
  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/#upsert-envelope-details 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 https://materialize.com/docs/sql/idioms/#top-k-by-group) that is hopefully a bit more clear.
  - glogla 6y ago
    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!
    - jldlaughlin 6y ago
      No apologies necessary, we're an active work in progress! (And, hopefully, moving quickly!)
benesch 6y ago
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 would be quite expensive, because the arrival of a new value will change the ordering of all values that sort greater than the new value. 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 [0]: https://github.com/TimelyDataflow/differential-dataflow https://github.com/TimelyDataflow/differential-dataflow
- Nican 6y ago
  Thank you for taking your time to write that up. To illustrate my point a little more: Suppose a customer has several resources, and each resource has several metrics. From my understanding, Materialized could be used to have an aggregated view of metrics per customer. The problem is that resources can also be migrated between customers. When a resource migrates between customers, the whole history of the customer changes. This could cause huge updates depended on how many resources are moved, or how many metrics per resource are being collected. I have a conundrum between doing the "customer-resource join" late, and causing huge CPU cost when running queries. Or making aggregates early, and then having huge Disk cost when migrating resources. At the moment, we just have daily jobs that aggregates the TBs of customer data daily, because there is no way to do the joins in real-time. Is Materialize designed to be able to handle something like this?

d_watt 6y ago

Materialize 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).

albertwang 6y ago
Nothing we can share publicly at the moment yet, but if you reach out and chat, we're more than happy to give you some numbers that I think will address what you're looking for!

efangs 6y ago

Is it possible to backfill a materialized view? I'm maybe confused, but does this potentially replace a traditional data lake or is it specifically for streaming applications?

jldlaughlin 6y ago
It is! We recently added support for S3 sources [0], which you could use to backfill data and union with a stream. To your other question, we're currently well-suited for streaming applications. Moving forward, as we add support for features like persistence, we could certainly replace at least parts of a traditional data lake. [0]: https://materialize.com/docs/sql/create-source/json-s3/ https://materialize.com/docs/sql/create-source/json-s3/

jdotjdot 6y ago

I have been waiting for this since the moment I first read about Materialize a year or two ago. I think there's still a lot of work to be done, but at heart, if you can pair technology like Materialize with an orchestration system like dbt, you can use dbt to keep your business logic extremely well organized, yet have all of your dependent views up to date all of the time, and use dbt even to use the same analytical layered views both for analytical AND operational purposes. The biggest issue I see is that it requires you to be all-in on Materialize, and as a warehouse (or as a database for that matter), it's surely not as mature as Snowflake or Postgres.

arjunnarayan 6y ago
Thank you for your kind words! We indeed have plenty of work to be done (and are thus hiring)! I'm curious however why you think this requires you to be all-in on Materialize. As you said better than I could have, dbt is amazing at keeping your business logic organized. Our intention is very much for dbt to standardize the modeling/business logic layer which allows you to use multiple backends as you see fit in a way that shares the catalog layer cleanly. Our hope is that you have some BigQuery/Snowflake job that you're tired of running up the bill hitting redeploy 5 times a day, and you can cleanly port that over to Materialize with little work because the adapter is taking care of any small semantic differences in date handling, or null handling, etc. So Materialize sits cleanly side-by-side with Snowflake/BigQuery, and you're choosing whether you want things incrementally maintained with a few seconds of latency by Materialize, or once a day by the batch systems. My view is you're likely going to want to do data science with a batch system (when you're in "learning mode" you try and keep as many things fixed, including not updating the dataset), and then if the model becomes a critical automated pipeline, rather than rerunning the model every hour and uploading results to a Redis cache or something, you switch it over to Materialize, and don't have to every worry about cache invalidation.
- abrazensunset 6y ago
  In that situation (dual usage modes) I think I'd rather have the primary data store be Materialize, and just snapshot Materialize views back to your warehouse (or even just to an object store). Then you could use that static store for exploration/fixed analysis or even initial development of dbt models for the Materialize layer, using the Snowflake or Spark connectors at first. When something's ready for production use, migrate it to your Materialize dbt project. The way dbt currently works with backend switching (and the divergence of SQL dialects with respect to things like date functions and unstructured data), maintaining the batch and streaming layers side by side in dbt would be less wasteful than the current paradigm of completely separate tooling, but still a big source of overhead and synchronization errors. If the community comes up with a good narrative for CI/CD and data testing in flight with the above, I don't think I'd even hesitate to pull the trigger on a migration. The best part is half of your potential customers already have their business logic in dbt.

esteer 6y ago

AWS launched a feature last re:Invent for materialized views - https://aws.amazon.com/glue/features/elastic-views/ https://aws.amazon.com/glue/features/elastic-views/

victor106 6y ago

If I have a batch file in EDI format how can I use DBT and/or Materialize?