azure data factory - Using PostgreSQL Materialized Views

Question

I am working on creating a denormalized table from my normalized tables(let's say I have users, orders and items). I've got some processes for loading data into my normalized tables that involves dropping them and copying in data from another source. And I have created a query for my denormalized table like:

SELECT itemid, itemname, orderid, userid, username
FROM items
LEFT JOIN orders USING (orderid)
LEFT JOIN users USING (userid)

In order to update this denormalized table I would either have to reload it every time, or design some sort of change tracking. Or I could create a materialized view and just refresh it periodically! However I have noticed this comes with a caveat when I'm reloading the normalized tables, I cannot drop them because the views depend on them.

I think I have two options

1. To truncate instead of drop in the reloading step
2. To create a copy of the materialized view that is a table. I'm partial to #2, but that copy activity, run frequently might be pretty heavy once I get more and more rows.

Does anyone have any recommendations on how to better handle this?

Using PostgreSQL 11.6 and Azure Data Factory for orchestration.

Answer 1

等待大神答复