Redshift

🚧

Redshift Serverless

Redshift Serverless does not currently have a mechanism to see full Query Logs. Therefore, features that utilize Query Logs in Monte Carlo (including Table Freshness and Lineage) are not yet available. The AWS Redshift Serverless team is working on exposing Query Logs and Monte Carlo will provide full support for Redshift Serverless when they are made available. Please reach out if you have any further questions.

This guide explains how to create a read-only service account for Monte Carlo on Redshift.

To review all steps necessary to integrate a data warehouse with Monte Carlo, please see here.

Creating a service account

📘

Admin credentials required

To make the read only service user for Monte Carlo, you will need admin credentials to your Redshift cluster.

Please use the following SQL snippet to create a service account for Monte Carlo:

CREATE USER <username> PASSWORD ‘<password>’ SYSLOG ACCESS UNRESTRICTED
GRANT SELECT ON svv_table_info to <username>
GRANT SELECT ON ALL TABLES IN SCHEMA <schema_name> [, …] to <username>
GRANT USAGE ON SCHEMA <schema_name> [, …] to <username>
GRANT USAGE ON SCHEMA <external_schema> [, …] to <username>
ALTER DEFAULT PRIVILEGES FOR USER <user_creating_tables> [, …] IN SCHEMA <schema_name> [, …] GRANT SELECT ON TABLES TO <username>

Before using the snippet, you will need to populate the following:

• <username>: Service account username, typically "monte_carlo".
• <password>: Strong password to be used by the service account user.
• <schema_name>: Redshift schema(s) to be monitored. Please note you can provide a list of schemas in a single statement.
• <external_schema>: Redshift Spectrum schema(s) – where external tables are defined – to be monitored. Please note you can provide a list of schemas in a single statement.
• <user_creating_tables>: Redshift user(s) that create/update tables and external tables, typically the user(s) used by your ETL jobs. Please note you can provide a list of such users in a single statement.

Please note:

• syslog permissions allow Monte Carlo to to read query logs from all users.
• select permissions on svv_table_info allow Monte Carlo to to read metadata about tables in your warehouse.
• select and usage permissions on schemas allow Monte Carlo to track health for tables in the given schemas.
• usage permissions on external schemas allow Monte Carlo to to track health for external tables in the given schemas.
• Default privileges set for ETL users make sure that Monte Carlo maintains access to tables as ETL jobs run and update/create tables.

To limit collection by schema, use Project and Dataset Filtering