Does CrateDB Support Materialized Views? Any Workarounds?

CrateDB
1

I’m exploring CrateDB and found that it supports downsampling using DATE_BIN and the LTTB algorithm. However, I couldn’t find direct support for materialized views where the results are stored in a table for efficient querying.

I came across an integration with dbt (link), but it seems dbt itself doesn’t support materialized views for CrateDB.

Given that many time-series databases offer materialized views, is there a recommended approach to achieve similar functionality in CrateDB? Any best practices or workarounds to persist aggregated/query results for faster access?

Thanks in advance!

1 Like
2

Hi Dennis,
Materialized views are in the roadmap,
in the meanwhile using dbt incremental models with the delete+insert strategy is something that works, you may have been put off by the mention of “delete”, it is only records that need to be updated that are deleted before new versions are inserted, not all the historical data you already have in the table.
Another way to incrementally materialize results is using scheduled jobs to run appropriate INSERT .. SELECT … FROM … ON CONFLICT … DO UPDATE … commands. See Continuous Aggregates for an example.

1 Like
3

Thanks for your prompt response, Hernanc.

Could you please have a look into the below clarifications:

  1. We are considering an on-premise CrateDB deployment. In this case, can we integrate dbt Core with an incremental model as a solution?
  2. Does on-premise CrateDB support automation or scheduled jobs?
  3. Regarding data retention policies, I understand that there is no out-of-the-box support, and we would need to integrate third-party tools like Apache Airflow to manage retention. Could you confirm if this understanding is correct?
2 Likes
4
  1. The on-premise Database is the same as on-cloud.
  2. The scheduled jobs Hernan linked is one of the many features of CrateDB cloud, for on premise one would have to implement it, something like a cron job sending queries to CrateDB http endpoint.
  3. It is the same, as the #2, CrateDB cloud has data retention policies already built in.
2 Likes