thiagomajesk
Maintaining materialized views with Ecto
Hi! 
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?
First Post!
cmo
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.
Last Post!
mythicalprogrammer
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.
Popular in Discussions
Other popular topics
Latest on Elixir Forum
Sponsor Spotlight
Producing high quality Elixir screencasts since 2017.
Categories:
Sub Categories:
Forums
Popular Tags
- #ecto
- #liveview
- #troubleshooting
- #learning-elixir
- #deployment
- #library
- #erlang
- #testing
- #genserver
- #mix
- #absinthe
- #remote-other
- #otp
- #plug
- #how-to-question
- #macros
- #postgres
- #channels
- #elixirconf
- #exunit
- #discussion
- #code-sync
- #javascript
- #podcasts
- #onsite
- #dialyzer
- #docker
- #authentication
- #umbrella
- #full-time-contract
- #podcasts-by-brainlid
- #ecto-query
- #elixir-ls
- #phoenix_html
- #iex
- #blog-post
- #graphql
- #genstage
- #ai
- #websockets
- #supervisor
- #elixirconf-us
- #advent-of-code
- #distillery
- #processes
- #forms
- #api
- #metaprogramming
- #security
- #hex
Our Sponsors
Build Elixir applications with speed and confidence.
Supporting innovation across the BEAM ecosystem.
We build reliable cloud platforms for business-critical systems.
Catch errors, track performance, monitor hosts and more.
Error tracking for Elixir devs who love to ship. Start your free account.
Develop your skills with books, videos, and courses.
Producing high quality Elixir screencasts since 2017.
Enabling companies to succeed by building software people love.
The team behind Membrane, Popcorn, LiveDebugger. Available for hire.
Courses that'll move you from confusion to "Aha, now I get it!"