Suppose you are working at ABC Financial Company, and it has a strict requirement of keeping the history of user activity on their Redshift cluster for the recent one month. As a Redshift administrator.

Submitted by devraj on 06/13/2021 - 18:14

what solution would you recommend/implement? Or what are the different ways of tracking user activity on the Redshift cluster?

Amazon Redshift provides the following three logging options:

  • Audit logs: Stored in Amazon Simple Storage Service (Amazon S3) buckets
  • STL tables: Stored on every node in the cluster
  • AWS CloudTrail: Stored in Amazon S3 buckets Audit logs and STL tables record database-level activities, such as which users who logged in and when. These tables also record the SQL activities and the time at which these users performed them. Redshift system tables do not store data for more than 3–5 days. It is recommended to create a custom script to incrementally copy the data from some important system tables to permanent user-defined tables to preserve the history of system tables. CloudTrail tracks the activities performed at the service/API level.