I would recommend following these guides when building it in Snowflake, a lot of the work should already be done for you.
Keep in mind, Snowflake’s Snowsight feature is considered a very basic BI layer and would recommend Tableau if you need additional features.
Also, Tableau already has a pre-built dashboard- https://www.tableau.com/blog/monitor-understand-snowflake-account-usage
To get list of all Snowflake Databases:
To get list of all schemas in a database:
But issue with above is we cant get list of all schemas in the whole account or from all databases.
To get all schemas in an account, you can do this (note that it only reports on what the current role has privileges on):
If you want to filter the results, you can use the result_scan immediately after running the show, which is a metadata query. It may look something like this:
select "database_name" as DATABASE_NAME
"name" as SCHEMA_NAME
where SCHEMA_NAME not in ('INFORMATION_SCHEMA') -- optional filter(s)