How to find base tables used in Amazon Redshift materialized views and vice versa?

You may often encounter following scenarios when working with materialized views.

• Find lineage of materialized views all the way to base tables for auditing, compliance.
• Build materialized view refresh schedules which dependent on one another and further dependency on loads to underlying base tables.
• Make changes to a materialized view by updating underlying base table. And then test, deploy dependent objects together

In this post, I will provide SQLs which will help you to identify the base tables under materialized view and vice versa. These queries work in both Redshift Provisioned and Serverless modes.

NOTE: There is a system view STV_MV_DEPS, which is helpful to find dependency between materialized views. However you will not be able to find base tables in this view.

Solution:

Let’s consider that you need to find base tables of a specific MV called “mv_trades_exchanges”. As a first step, you need find the object ID of that MV. Below is the SQL to run to find the object ID.

select oid from pg_class where relname = 'mv_trades_exchanges';

The oid returned from above query should be used in below query to find all the base tables used in the materialized view. In my example, the oid returned is 383437.

SELECT distinct mv.oid mv_oid, mv.relname mv_name, bt.oid base_table_oid, bt.relname base_table_name
FROM pg_class bt, pg_class mv, pg_depend d
WHERE d.objid = 383437-- mv oid
and d.refobjid = bt.oid -- join to get the names of referenced base tables in MV
and d.objid = mv.oid -- join to get the name of MV
and d.refclassid = 1259; -- oid of the catalog table that contains the referenced ob

The output would look like below

If you would like find materialized views dependent on as specific base table, then please use below query. In this example, I used ‘trades’ table. You need to replace with your base table name.

SELECT distinct mv.oid mv_oid, mv.relname mv_name, bt.oid base_table_oid, bt.relname base_table_name
FROM pg_class bt, pg_class mv, pg_depend d
WHERE bt.relname = 'trades' -- inject base table name
and d.refobjid = bt.oid -- join to get the names of referenced base tables in MV
and d.objid = mv.oid -- join to get the name of MV
and d.refclassid = 1259;

The output would look like below.

Finally, if you would like to get a list of MVs and corresponding base tables, then please use below SQL. You need to run this SQL as superuser to view all the relations in your database.

SELECT distinct mv.oid mv_oid, mv.relname mv_name, bt.oid base_table_oid, bt.relname base_table_name,d.refclassid
FROM pg_class bt, pg_class mv, pg_depend d,pg_catalog.pg_namespace n
WHERE d.refobjid = bt.oid -- join to get the names of referenced base tables in MV
and d.objid = mv.oid -- join to get the name of MV
and mv.relnamespace = n.oid
and d.refclassid = 1259
and n.nspname not in ('pg_catalog','information_schema','pg_toast','pg_automv');

The output would look like below.

Hope you enjoyed reading the post. Please feel free to reach me if you have any questions. Thank you.