Snowflake // How to count records for each view in given schema for DEV and PROD database

Question

I would like to compare the records count for each view in given schema between the databases: DEV and PROD. The goal is to verify if the corresponding views (in DEV and PROD) have the same record count.

Let say we have something like this:

Database_DEV.Schema.View01

Database_DEV.Schema.View02

Database_DEV.Schema.View03

and

Database_PROD.Schema.View01

Database_PROD.Schema.View02

Database_PROD.Schema.View03

And as a result I want to have a table like:

ViewName	Database_DEV	Database_PROD
View01	345	345
View02	450	450
View03	555	666

The view name should be taken dynamically from INFORMATION_SCHEMA table:

SELECT TABLE_NAME
FROM Database_PROD.INFORMATION_SCHEMA.TABLES
WHERE 1=1
AND TABLE_CATALOG = 'Database_PROD'
AND TABLE_TYPE = 'VIEW'
AND TABLE_SCHEMA = 'Schema'

I assume that I can get the result using cursor?

Thanks for any help.

Answer 1

I assume that I can get the result using cursor?

You could, but it would probably be easier to use a SQL generator and UNION ALL.

The SQL generator would look like this (change pattern to suit):

SELECT listagg('select \'' || TABLE_NAME || '\' as VIEW_NAME, (select count(*) from DB_PROD.PROD.' || TABLE_NAME || ') as PROD_COUNT, (select count(*) from DB_DEV.DEV.' 
               || TABLE_NAME  || ') as DEV_COUNT\n', 'UNION ALL\n') as SQL_TO_RUN
FROM INFORMATION_SCHEMA.TABLES
WHERE 1=1
AND TABLE_CATALOG = 'DB_PROD'
AND TABLE_TYPE = 'VIEW'
AND TABLE_SCHEMA = 'PUBLIC'
;

The SQL generator will generate a single row and column to copy and paste or use in a script. It will look like this:

select 'MY_VIEW' as VIEW_NAME, (select count(*) from DB_PROD.PROD.MY_VIEW) as PROD_COUNT, (select count(*) from DB_DEV.DEV.MY_VIEW) as DEV_COUNT
UNION ALL
select 'MY_VIEW123' as VIEW_NAME, (select count(*) from DB_PROD.PROD.MY_VIEW123) as PROD_COUNT, (select count(*) from DB_DEV.DEV.MY_VIEW123) as DEV_COUNT
UNION ALL
select 'MY_VIEW456' as VIEW_NAME, (select count(*) from DB_PROD.PROD.MY_VIEW456) as PROD_COUNT, (select count(*) from DB_PROD.DEV.MY_VIEW456) as DEV_COUNT
UNION ALL
select 'MY_VIEW456' as DB_PROD.PROD.VIEW_NAME, (select count(*) from MY_VIEW789) as PROD_COUNT, (select count(*) from DB_DEV.DEV.MY_VIEW789) as DEV_COUNT

Running it produced output like this:

VIEW_NAME	PROD_COUNT	DEV_COUNT
MY_VIEW	25	25
MY_VIEW123	25	25
MY_VIEW456	25	25
MY_VIEW789	50	50

You could even have the SQL generator add another column that compares the PROD_COUNT and DEV_COUNT and report true or false if they match or do not match.