Using native incremental materialized view for model persistence instead for database that support it

Feature Requests
1

Hi Metabase Team!

As titled, I’m curious, is there any technical blocker that makes it hard to do so? Given that most popular data warehouses do support IVM natively, i think that is technically more elegant solution. Freshness is guaranteed by the underlying database, rather than relying on Metabase to manually refreshes the view from scratch after certain period.

If it’s technically feasible, would you be open for contribution? I would love to give it a try if it’s not too much.

Thanks :heart:

2

Hi, we shipped transforms for this, have you tried it?

3

I’ve only hit one problem in Postgres. Not sure if it only affects Postgres or not.

In a MV, you can’t define a column as nullable or not. That means that when you have a filter that excludes (<>), Metabase puts in a or is null which prevents an index being used efficiently.

That said, you still get much better control than you do with materialising a model in Metabase and at least you can have some indexes.

1 Like
4

Hi dragonsahead,

Thanks for the reply. I have checked it, and definitely something that could solve this. My question is, why such feature lives in a such disaggregated place from the main app (i.e. data-studio), and at the moment, seems like there’s no way to import/convert metabase question from main-app → data studio, am i right? The only way to do this seems to manually copy–and-paste it, which then, we will have to update all the downstream dependencies for that question. I wish it could be more seamless integration.

5

Yeah, despite all the negative side of MV, i think this kind of things better to be handed down to the underlying datastore. I know Metabase tried to create a easier abstraction for every datastore, but that means we throw away some of the datastore-specific optimization as well

6

They’re not mutually exclusive. I have fact/dimension tables mostly as MV. I them define the joins and meta data in Metabase. Makes it much easier for end users as it means they never have to worry about database joins.

The data studio bit used to be part of the Admin panel (most of it is still in there). The rest is quite new, so definitely a work in progress. I’m not using it yet, but will be keeping an eye on developments. Personally, I prefer to keep the layers separate.