AWS Big Data Blog

Long-term Amazon Redshift observability with Datadog and Amazon S3 Tables

Building long-term observability for Amazon Redshift starts with a question: what happened in your data warehouse last month, last quarter, or last year? Amazon Redshift captures operational history across more than 25 system views. These views range from query execution details in SYS_QUERY_HISTORY and full query text in SYS_QUERY_TEXT to connection activity, user changes, workload management events, and resource consumption metrics. Together, these views form a detailed record of the operations running inside your Amazon Redshift clusters and Amazon Redshift serverless workgroups. Amazon Redshift retains this data for 7 days. After that window closes, the history is gone. You lose the ability to audit past activity, investigate slow-moving regressions, or satisfy compliance requirements that can span months or years.

The system table integration with Amazon S3 Tables, a capability of Amazon Redshift, changes this. When you enable the integration, Amazon Redshift automatically and continuously delivers system table data as Apache Iceberg tables to Amazon S3 Tables in your account. You own the data, you set the retention policy, and S3 Tables handles compaction and snapshot maintenance. You can query the exported data with Amazon Redshift, Amazon Athena, or any Iceberg-compatible engine. There’s no charge for delivery. You pay only for S3 Tables storage, requests, maintenance, and the engine you use to read the data.

AWS and Datadog collaborated to make this exported data actionable for observability. Datadog, an AWS Specialization Partner and AWS Marketplace Seller, provides an observability and security platform for cloud applications. Datadog Data Observability helps data teams improve the reliability of data for analytics and AI. It combines quality monitoring for warehouses such as Amazon Redshift with jobs monitoring for pipelines that run on AWS Glue, Amazon EMR, Apache Airflow, and dbt. It then connects them with lineage that spans from source pipelines to downstream dashboards. By reading query history directly from S3 Tables, Datadog turns it into column-level lineage, query analysis, and context for quality alerts, without adding load to your warehouses to collect it. For AWS managed monitoring of Amazon Redshift metrics and logs, you can also use Amazon CloudWatch. Datadog Data Observability is billed separately by Datadog.

In this post, we show how to configure Datadog Data Observability to read Amazon Redshift query history from S3 Tables. We walk through granting Datadog read access and using the query history for column-level lineage, query analysis, and data quality monitoring.

Solution overview

Datadog Data Observability uses Amazon Redshift query history to build column-level lineage, analyze query performance, and give context to data quality alerts. Previously, Datadog collected this history by running read-only SQL against each warehouse every hour. With this solution, Datadog reads the same history from S3 Tables instead. The following diagrams show the solution at a high level and compare query history collection before and after the integration.

Datadog Data Observability reads Amazon Redshift query history from Amazon S3 Tables for long-term observability

Figure 1: High-level solution overview

Comparison of query history collection: before the integration Datadog runs hourly read-only SQL on each Amazon Redshift warehouse; after, it reads the same history from Amazon S3 Tables without querying the warehouse

Figure 2: Query history collection before and after the S3 Tables integration

This approach offers the following benefits:

  • Retention beyond 7 days. Query history remains in your account for as long as you choose to retain it. Because history is kept in S3 rather than expiring after 7 days in the cluster, Datadog can backfill any collection gap when it resumes. You don’t lose queries or lineage. Your audit and compliance teams also keep a long-term record of query activity in your own account.
  • No added load on the warehouse. Datadog reads the Iceberg tables through the S3 Tables Iceberg REST endpoint. Collecting query history doesn’t run queries on your clusters or workgroups or consume their compute.
  • One view across warehouses. Datadog combines query history from every connected cluster and workgroup into a single lineage graph, together with other connected tools such as dbt, Apache Airflow, Tableau, Looker, and Power BI.

The following diagram illustrates the solution architecture.

Amazon Redshift delivers system table data to Amazon S3 Tables, and Datadog reads it through the S3 Tables Iceberg REST endpoint

Figure 3: Solution architecture

The workflow consists of the following steps:

  1. Amazon Redshift periodically writes completed query records from SYS_QUERY_HISTORY and SYS_QUERY_TEXT to the AWS managed aws-redshift table bucket in Amazon S3 Tables. An isolated background process handles these writes, separate from your production workloads.
  2. Datadog uses the AWS Identity and Access Management (IAM) role from your Datadog AWS integration to read new rows through the S3 Tables Iceberg REST endpoint. Because data is delivered in batches at a fixed frequency, Datadog tracks the last batch it read, so no batch is skipped or read twice.
  3. Datadog masks sensitive literals in the query text, parses each query into column-level lineage, and groups similar queries into normalized query profiles.
  4. Datadog still connects to Amazon Redshift for table metadata, such as schemas and row counts, and to run the data quality monitors that you configure.

Prerequisites

To implement this solution, you need the following:

  1. An AWS account with one or more Amazon Redshift provisioned clusters (RG or RA3 node types) or Amazon Redshift Serverless Workgroup.
  2. The system table integration with Amazon S3 Tables enabled for each warehouse you want to monitor. At minimum, enable sys_query_history and sys_query_text. Datadog reads per-warehouse exports, so set the granularity to cluster for provisioned clusters or workgroup for Serverless namespaces. To enable the integration, see Enabling system table integration with Amazon S3 Tables.
  3. The Amazon S3 Tables integration with the AWS Glue Data Catalog enabled in your account and Region. This is a one-time setup step. If you already integrated S3 Tables with the AWS Glue Data Catalog, no further action is needed. For instructions, see Integrating Amazon S3 Tables with AWS analytics services.
  4. An IAM principal with the following permissions:
    • redshift:EnableLogging (provisioned) or redshift-serverless:UpdateNamespace (Serverless).
    • s3tables:CreateTableBucket, s3tables:PutTableBucketEncryption, and s3tables:PutTableBucketPolicy.
  5. The AWS Command Line Interface (AWS CLI) installed and configured.
  6. A Datadog account with the Datadog AWS integration configured and your warehouse connected to Datadog Data Observability for Amazon Redshift.
  7. Access to the Datadog Data Observability S3 Tables preview. To request access, contact your Datadog account team.

Note: Plan your encryption strategy before enabling the integration. If you encrypt the tables with a customer managed AWS Key Management Service (AWS KMS) key, you can’t change the key after the tables are created. If you plan to consolidate warehouses later, choose a shared AWS KMS key from the start.

Grant Datadog read access to the system tables

Datadog reads the system tables with the IAM role that your Datadog AWS integration uses. Complete the following steps:

  1. On the IAM console, in the navigation pane, choose Roles, then choose the role used by your Datadog AWS integration.
  2. Choose Add permissions, then choose Create inline policy.
  3. Choose the JSON tab and enter the following policy, replacing <region> and <account-id> with your values:
{
    "Version": "2012-10-17",
    "Statement": [
        {
            "Sid": "DatadogReadRedshiftSystemTableBucket",
            "Effect": "Allow",
            "Action": "s3tables:GetTableBucket",
            "Resource": "arn:aws:s3tables:<region>:<account-id>:bucket/aws-redshift"
        },
        {
            "Sid": "DatadogReadRedshiftSystemTables",
            "Effect": "Allow",
            "Action": [
                "s3tables:GetTableMetadataLocation",
                "s3tables:GetTableData"
            ],
            "Resource": "arn:aws:s3tables:<region>:<account-id>:bucket/aws-redshift/table/*"
        }
    ]
}
  1. Enter a name for the policy, for example DatadogRedshiftSystemTablesRead, and choose Create policy.

Note: If you encrypted the tables with a customer managed AWS KMS key, also grant the role kms:Decrypt permission on that key.

Use S3 Tables as the query history source in Datadog

During the preview, your Datadog account team enables S3 Tables as the query history source for your Datadog organization. Datadog then reads query history for each connected Amazon Redshift warehouse from S3 Tables instead of from the warehouse. Datadog tracks its position in the query history for each warehouse, so switching between sources doesn’t skip or duplicate queries.

Analyze query history in Datadog Data Observability

Query history feeds three parts of Datadog Data Observability:

  • Lineage. Shows how data moves between tables, jobs, and dashboards.
  • Query analysis. Shows who produces and consumes that data, and at what cost.
  • Quality monitoring. Uses both to decide which tables to watch and to explain each alert.

Datadog builds all three from the same query history, so they work the same way whether the history comes from the warehouse or from S3 Tables.

The examples in this section use an Amazon Redshift table named analytics.fct_orders. A nightly job loads it from staging.stg_orders, and a Tableau dashboard reports revenue from it.

Note: The figures use a sample environment, so they show a database named prod and a table named int_orders_enriched instead of analytics.fct_orders.

Column-level lineage

Datadog lineage view anchored on a table, with upstream tables and jobs on the left and downstream tables and dashboards on the right, grouped by column

Figure 4: End-to-end lineage upstream of, through, and downstream of the prod database

Datadog parses each query in the history into column-level lineage. To explore it, complete the following steps:

  1. Choose Data Observability, then Lineage.
  2. Search for prod to anchor the graph on the database. Figure 4 shows the result: source tables such as orders flow through the raw, staging, and intermediate schemas into marts. To focus on a single table such as users, search for that table instead.
  3. Review upstream tables and jobs to the left, and downstream tables and dashboards to the right. An Upstream issue label marks assets affected by a problem further upstream.
  4. To see column-level lineage, expand a table to show its columns and how each one flows into downstream tables. To trace a single column from source to dashboard, change the asset type to Column and search for it.

In addition to Amazon Redshift, the lineage graph supports other sources. When you connect other integrations, upstream jobs from Apache Airflow or dbt and downstream dashboards from Tableau, Looker, or Power BI appear in the same graph. Datadog builds lineage from the query history in the time window you choose, so widen the window to include jobs that run weekly or monthly.

Two common lineage workflows are:

  • Root cause analysis. Anchor on a broken table or dashboard, set the downstream depth to 0, and step upstream. Tables flagged by a quality monitor show their status on the graph, so you can see where a problem starts.
  • Impact analysis. Before you change or drop a column, anchor on it, set the downstream depth to the maximum, and switch to the List view to see every table and dashboard that depends on it.

Query analysis

Query analysis turns your Amazon Redshift query history into trends, so you can catch queries that slow down or scan more data over weeks and months, not just the last 7 days. Figure 5 shows the result for the last question: an INSERT run by the etl_svc user.

Datadog Queries page showing a normalized INSERT pattern run by the etl_svc user, with charts for query count, execution time, average duration, bytes scanned, and bytes spilled over the past week

Figure 5: Query count, execution time, and data scanned for a normalized INSERT pattern

To explore query patterns:

  1. Choose Data Observability, then Queries, and choose Amazon Redshift.
  2. Review normalized query patterns. For each pattern, Datadog shows how often it runs, how long it takes, and how much data it scans.
  3. Group patterns by query, cluster, user, database, or statement type.
  4. Filter by the tables they read or write.

Because Datadog combines the query history from every connected cluster and workgroup, you can answer the following questions across your whole Amazon Redshift footprint:

  • Which query patterns take the longest or read the most data?
  • Which users or service accounts run the most expensive queries?
  • Which queries write to analytics.int_orders_enriched, and how has their runtime changed over time?

Quality monitoring

Quality monitors alert you when an Amazon Redshift table is not updated, changes unexpectedly in volume, or contains values that fall outside its normal range. Lineage then shows the upstream cause and the downstream impact.

Datadog anomaly monitor for orders per hour by traffic source, with two of four series alerting and the current value of 628 below the expected lower bound of 1,027

Figure 6: An anomaly monitor on orders per hour in Datadog

Data Observability monitors track the following metrics:

  • Freshness
  • Row count
  • Column-level metrics such as nullness and uniqueness
  • Custom SQL metrics

Each monitor can use anomaly detection, which learns a metric’s trends and seasonality so that you don’t need to set a threshold for every table. Figure 6 shows an anomaly monitor on orders per hour, grouped by traffic source. When the metric falls outside its expected range, Datadog flags the likely upstream cause and the downstream assets at risk. Schema change monitors (currently in preview) alert when an Amazon Redshift table or column is added, removed, or renamed, or when a column type changes.

Lineage also helps you decide what to monitor. For example, add a relation filter to monitor every table upstream of a specific dashboard.

Quality monitoring runs aggregate queries in Amazon Redshift, such as COUNT(*) and MAX(<timestamp_column>), and sends only the resulting values to Datadog, not rows from your tables. To protect sensitive columns, you can use Amazon Redshift dynamic data masking.

Example: Trace a stale dashboard to its cause

The following steps show how these features work together when the revenue dashboard stops updating:

  1. A freshness monitor on analytics.fct_orders alerts in Slack that the table hasn’t updated since its usual 02:00 load.
  2. On the Lineage page, you anchor on fct_orders. The monitor on the upstream table staging.stg_orders is healthy, so the source data arrived and the problem is in the step that builds fct_orders. The graph also shows that the Tableau revenue dashboard depends on fct_orders.
  3. On the Queries page, you filter to queries that write to analytics.fct_orders. The nightly INSERT pattern run by the etl_svc user shows failed runs starting at 02:00.
  4. You fix the job and, before you rerun it, use impact analysis to list the downstream tables and dashboards whose owners you notify.

Clean up

To stop writing system table data to S3 Tables, for a provisioned cluster, run the following command:

aws redshift disable-logging \
    --cluster-identifier <cluster-identifier> \
    --log-destination-type s3table \
    --log-exports sys_query_history sys_query_text

For an Amazon Redshift Serverless namespace, run the following command:

aws redshift-serverless update-namespace \
    --namespace-name <namespace-name> \
    --log-destination-type s3table \
    --s3-table-action Disable \
    --s3-table-names sys_query_history sys_query_text

Before you run the preceding commands, ask your Datadog account team to switch the query history source back to the warehouse. Datadog then resumes collecting query history from your warehouse where it left off, so lineage and query analysis continue without a gap.

Disabling the integration stops new deliveries but keeps the data that was already written. Amazon Redshift doesn’t deliver system table data to S3 Tables while the integration is disabled, so your long-term record in S3 Tables won’t include that period. Re-enabling the integration doesn’t backfill that period, and after 7 days, Amazon Redshift no longer retains those queries in the warehouse either. To stop incurring storage charges, delete the tables. Deleting the tables permanently removes the retained data, and Amazon Redshift doesn’t recreate them automatically.

Conclusion

In this post, we showed how to configure Datadog Data Observability to read Amazon Redshift query history from Amazon S3 Tables. With this integration, you can:

  • Retain query history beyond the 7-day in-cluster limit for auditing and compliance.
  • Collect query history without adding load to your warehouses.
  • Analyze lineage and query activity across all your clusters and workgroups in one place.

AWS and Datadog built this integration together so you can use Amazon Redshift system table data in your existing observability workflows.

If you’re new to Datadog, you can sign up for a free trial. If you have questions or suggestions, leave them in the comments.

Additional resources


About the authors

Raghu Kuppala

Raghu Kuppala

Raghu is an Analytics Specialist Solutions Architect experienced working in the databases, data warehousing, and analytics space. Outside of work, he enjoys trying different cuisines and spending time with his family and friends.

Raza Hafeez

Raza Hafeez

Raza is a Senior Product Manager, Technical at Amazon Redshift. He has 15+ years of experience building and optimizing enterprise data warehouses and is passionate about making cloud analytics accessible and cost-effective for customers of all sizes.

Kevin Hu

Kevin Hu

Kevin helps lead the Data Observability product at Datadog. He was previously co-founder and CEO of Metaplane, a data observability company acquired by Datadog.

Todd Pollak

Todd Pollak

Todd is a Staff Software Engineer at Datadog, working on the Data Observability product. He was previously Head of Engineering at Metaplane.