Maintaining materialized views with Ecto

Chat / Discussions
, , ,
1

Hi! :wave:

I was thinking about this today and wanted to collect some feedback…

I have been working with an OLAP database for the past year and using DBT for data transformation, and although it’s a great tool, having a separate data pipeline can be a handful to maintain. To quickly iterate and validate ideas, I always tend to lean towards using materialized views (essentially what tools like DBT do to consolidate queries).
However, reading and maintaining a complex materialized view on a migration file is not the best experience.

That being said, one way I was thinking of improving the maintaining burden of materialized views is to make the code declarative, as separate files in priv/repo. Ideally, the materialized views should always run last, guaranteeing that DB is in the correct state.

A few thoughts so far:

  • Migrations are probably not the best place to maintain this code since it’s imperative/ incremental and time-based.
  • Sourcing external files from migration is asking for trouble because you’ll end up with an out-of-sync migration (eg: relying on columns added later on)

Have you guys tried doing something similar?

2

I was creating them at runtime for a while. I would track them in the db and remove them after a period of non-use and update them (but not too frequently) if the backing data changed.

3

Currently have it in migration files.

I roll back and fudge the timestamp if I need to or move it to other migration files. I only had to fudge timestamp once and I have 3 or so total materialized views.

I did rollback several time to merge/combine some migration files together too and refactor.

I don’t have crazy materialized views though.

My seed.exs is where I read and insert my csv data into tables and refresh my views.

I looked into DBT, it seems like your solution sounds good.

DBT looks interesting.

Especially for reading and managing views, having it in separate folder so you can get to them make a lot of sense.