Redshift

Integrating Redshift with Coalesce Quality

This guide will show you how to securely connect Coalesce Quality to your Redshift cluster or Redshift Serverless workgroup. We need this information so we collect metadata about your tables.

To be able to finish this guide, you’ll need the following:
→ Access to modify your Redshift configuration as a superuser

⏱️ Estimated time to finish: 10 minutes.

Data we collect

We read metadata from your Redshift database using the dedicated user described below. Depending on which features you enable in the integration, we ingest:

• Catalog: databases, schemas, tables, views, late-binding views, materialized views, columns, comments, data types, constraints (informational primary / unique / foreign keys).
• SQL definitions: view definitions, materialized view definitions, stored procedure bodies, and UDF bodies. Used for lineage and to detect references between objects.
• Distribution and sort metadata: DISTSTYLE, DISTKEY, SORTKEY (compound / interleaved / auto) per table.
• Table metrics (on by default): row counts and last-modified timestamps from Redshift system views. Automatic monitors are built on them and stop working when Fetch table metrics is off.
• Query logs (opt-in): SYS_QUERY_HISTORY for performance, cost, and lineage signals.
• Freshness from query logs (opt-in): SYS_QUERY_DETAIL for tables touched by recent writes. Requires the sys:monitor role.
• Users (opt-in): the database users with their groups, roles and last login, to tell people from service users in query history.
• External tables: Spectrum external schemas and tables, when present.

Metadata vs. data access. The baseline grants below give Coalesce Quality access to your schema and DDL, never to row data. Catalog, DDL and table metrics come from SVV_* and pg_catalog views. Query logs and freshness come from the SYS_* monitoring views. Coalesce Quality reads table rows only for the custom monitors you set up, which query your tables on each run, and for SQL tests against the audit table.

We connect on a schedule (typically every 30 minutes). On Redshift Serverless each scrape bills at least one minute of compute at the workgroup’s base capacity, so the cost depends on your base RPU setting. On provisioned Redshift our queries run on your existing cluster’s compute and are negligible compared to a regular workload.

Setup Redshift access

Create a dedicated user with sufficient permissions

1. Open the Query Editor in Redshift and make sure the right cluster / workgroup and database are selected.

2. Run the following SQL as a superuser to create a dedicated user and group with the necessary read-only permissions, where <password> is a secret you define for the integration.

   CREATE GROUP synq;
   CREATE USER synq_user PASSWORD '<password>' IN GROUP synq;
   
   -- Catalog metadata views
   GRANT SELECT ON pg_catalog.svv_all_schemas        TO GROUP synq;
   GRANT SELECT ON pg_catalog.svv_redshift_columns   TO GROUP synq;
   GRANT SELECT ON pg_catalog.svv_external_schemas   TO GROUP synq;
   GRANT SELECT ON pg_catalog.svv_table_info         TO GROUP synq;
   GRANT SELECT ON pg_catalog.pg_class_info          TO GROUP synq;
   

3. Grant USAGE on each schema you want monitored. This is required even for views that use WITH NO SCHEMA BINDING. Both the schema in which the late-binding view is defined and any schemas it reads from need USAGE.

   GRANT USAGE ON SCHEMA <schema_name> TO GROUP synq;
   

   To grant USAGE on every schema in the database:

   DO $do$
   DECLARE
       sch text;
   BEGIN
       FOR sch IN SELECT nspname FROM pg_namespace
       LOOP
           EXECUTE format($$ GRANT USAGE ON SCHEMA %I TO GROUP synq $$, sch);
       END LOOP;
   END;
   $do$;
   

4. Grant the user visibility into stored procedures, UDFs, and materialized views in the schemas you want monitored. Without these grants, procedure / UDF metadata and materialized view definitions will not be collected.

   GRANT EXECUTE ON ALL FUNCTIONS  IN SCHEMA <schema_name> TO GROUP synq;
   GRANT EXECUTE ON ALL PROCEDURES IN SCHEMA <schema_name> TO GROUP synq;
   ALTER DEFAULT PRIVILEGES IN SCHEMA <schema_name>
       GRANT EXECUTE ON FUNCTIONS  TO GROUP synq;
   ALTER DEFAULT PRIVILEGES IN SCHEMA <schema_name>
       GRANT EXECUTE ON PROCEDURES TO GROUP synq;
   

5. (Optional) Grant access to table data within a schema. Required if you want Coalesce Quality to run monitors on your raw data, for example Custom SQL monitors:

   GRANT SELECT ON ALL TABLES IN SCHEMA <schema_name> TO GROUP synq;
   ALTER DEFAULT PRIVILEGES IN SCHEMA <schema_name>
       GRANT SELECT ON TABLES TO GROUP synq;
   

Grant access to query history and users (recommended)

Redshift shows a regular user only its own rows in the SYS_* monitoring views. Grant the predefined sys:monitor role so the user sees every user’s rows, on provisioned clusters and Serverless alike:

GRANT ROLE sys:monitor TO synq_user;

It is needed for:

• Fetch query logs: SYS_QUERY_HISTORY and SYS_QUERY_TEXT. Without it the query history holds only the queries Coalesce Quality ran itself. ALTER USER synq_user SYSLOG ACCESS UNRESTRICTED works for this too.
• Estimate freshness of tables from SYS_QUERY_DETAIL view: SYS_QUERY_DETAIL.
• Fetch users and service accounts: each user’s roles from SVV_USER_GRANTS and last login from SYS_CONNECTION_LOG. The ACCESS SYSTEM TABLE permission, granted to a role the user has, works for this too. The users and their groups are listed without either.

Whitelist the Coalesce Quality IP

For the complete list of IP addresses by region, see Coalesce Quality IP Whitelist.

1. From Redshift, navigate to Workgroup configuration and click your default workgroup.

   

2. Under Network and security click Edit and check the Turn on Publicly accessible checkmark. Then click Save changes.

   

3. Close the window to return to the workgroup view and click your VPC security group.

   

4. Under the Inbound rules tab click Edit inbound rules, then Add rule:

   
   • Type: Redshift (port 5439)
   • Source: Custom with 34.105.135.39 (EU), 35.238.250.82 (US) or 35.189.4.22 (AU)

5. Click Save rules.

Database, dataset and host name

1. Take a note of your database name. It can be found in the Query Editor.

   

2. Go to Workgroup configuration and select your workgroup. Take a note of your JDBC URL.

   

Input data in the Coalesce Quality UI

Integration name

For example Redshift.

Database

The name of your database (e.g. analytics_prod).

Host

The hostname of your workgroup endpoint (e.g. my-workgroup.123456789012.eu-central-1.redshift-serverless.amazonaws.com or my-cluster.us-east-1.redshift.amazonaws.com).

To find the host: open Workgroup configuration, copy the Endpoint, and strip the trailing :5439/<dbname> part.

Port

Default is 5439.

User and password

synq_user and the password you set in step 2 above.

Audit table name (optional)

Fully qualified name (schema.table) of the table where Coalesce Quality should write SQL test audit logs, for example analytics.quality_sql_test__audit. Coalesce Quality creates the table if it does not exist. Leave empty if you only run metadata monitors and don’t execute custom SQL tests against the warehouse.

SSH Tunnel (optional)

If your Redshift workgroup is private and only reachable through a bastion host, click Add SSH tunnel and fill in the user, hostname and port of the bastion (22 unless you run SSH elsewhere). Leave this empty for clusters reachable via the public endpoint (the standard setup described above).

Coalesce Quality authenticates to the bastion with an RSA key pair it generates when you save the integration. Only the public half leaves the platform; it is shown under SSH tunnel key pair at the bottom of the integration’s page. Add it as a line in the ~/.ssh/authorized_keys file of the bastion user you entered. The bastion refuses the connection until that line is in place, so testing the connection before then fails.

Rotating the tunnel key pair

The same SSH tunnel key pair section replaces the key. This needs the Admin role.

Generate a new key pair takes effect immediately, and the key it replaces keeps being offered for a week, so the tunnel carries on working while you install the new one. The section shows the previous public key and the date it stops being offered.

Add the new public key to authorized_keys, then delete the line holding the previous one. Removing that line revokes the old key. Coalesce Quality has no access to your bastion, so a key left in the file stays usable after the week is over.

Only the most recent replacement is kept, so generating a second key pair while a previous key is still being offered retires that one straight away. Finish installing one key before generating the next.

Data collection options

These switches control what Coalesce Quality reads beyond catalog metadata:

• Fetch query logs (recommended): reads SYS_QUERY_HISTORY. Required for proactive anomaly-monitor training, unused-table detection, and cost insights. Adds a small recurring query cost, typically a few dollars per month on Serverless. Needs the sys:monitor role (see Grant access to query history and users).
• Fetch table metrics (required for automatic monitors): on by default. Every 30 minutes, reads the row count and size of each table from SVV_TABLE_INFO. Automatic freshness and volume monitors stop working when it is off.
• Estimate freshness of tables from SYS_QUERY_DETAIL view: derives a per-table last-modified timestamp from query history. Needs the sys:monitor role.
• Use SHOW TABLE to get DDLs of tables: reads the full CREATE TABLE statement of every table with SHOW TABLE, including its distribution and sort keys. It runs one statement per table, about 40 ms each, so a large database takes longer to ingest. Views get their definition either way.
• Fetch users and service accounts (optional): shown once query logs are on. Once a day, lists the database users, so query history shows whether each one is a person or a service.

Catalog scope and Metrics scope restrict which schemas and tables are ingested into the catalog, and which of those are scanned for metrics. Leave both empty to ingest everything the user can see.

On-premise agent

On the on-premise agent, a redshift connection takes use_show_create_table: true for Use SHOW TABLE to get DDLs of tables and freshness_from_query_logs: true for Estimate freshness of tables from SYS_QUERY_DETAIL view. Both are off unless you set them. port defaults to 5439.

In some cases, permission grants can take a while to propagate. If you keep getting a Connect failed error after double-checking your details, try again after a minute.