As an FYI using a tool like DBT solves this problem. As someone who was not a data engineer I was not familiar, there were tools like this

dbt is great, but I'm not sure it's appropriate to manage objects in a production transactional database. It's designed for analytical, columnar databases.

In case you're not familiar with DBT, it helps with the "transform" in ELT https://docs.getdbt.com/docs/introduction.

I have this problem. What is DBT?

I believe this: https://docs.getdbt.com/docs/introduction

Thank you for this. I had no idea this existed either and it may solve some points for us on MySQL where we've really been yearning for materialized views... which I'm now reading my be a foot gun.

> The main problem with views for this use case in practice is that they ossify your schema. Views and matviews are effectively a dependency tree, and many common types of schema evolution become substantally more difficult when the system forces you to wrap your DDL in a series of view drop/recreation steps.

You're not wrong, especially with the second part. I.e., deeply nested or convoluted dependencies between views can definitely make it awkward or painful to make adjustments near the root of the tree.

When I started this reply I was going to say "I hear you, but it's not an issue I run into very often". But that's not true. I've actually been burned by that moderately often, and have sometimes avoided or redesigned the root-level table change to avoid having to propagate all those changes to the rest of the dependency tree.

That said, in my experience (also mostly with postgres for this context) I feel like that's usually been more of a developer laziness issue (my own laziness that is), rather than an "ossified schema" issue. It's definitely a PITA when some simple change is going to break a dozen inter-connected views, but that's a coding issue not a deployment issue almost all of the time.

To be fair I don't really use matviews very often, but for true basic views I am guessing that the actual execution of the DDL to rebuild changed views is manageable in all but the most extreme cases. Even then there _should_ be a maintenance window of some sort available.

Thinking this thru a little bit, I believe the "anti-pattern" you're warning against isn't really views themselves but deeply nested/interconnected views (views that query other views, etc). I use views often (for this logical-delete type idiom for example) and I have rarely regretted it. I have often regretted creating complicated view-base dependency trees however, so I think I'm wholeheartedly in agreement on that point.

To avoid an outage, have you tried fronting the matview with an additional view to allow hot-swapping?

it is very dangerous to have dependency on materialized view - it is a poor architectural decision from DBA to do that.

if you want view depending on mat view - materialize it yourself in a table, and refresh it yourself controllably.

Aren’t you just saying that materialized views should not be used? I’m pretty sure that was my original point.