Parsing SQL queries in Redshift for table and column audit logging

September 21, 2023

This is the fourth article in our series on parsing SQL in different database technologies and SQL dialects. We explored SQL parsing on Snowflake, MS SQL Server, and Oracle in our earlier blog posts. We cover SQL parsing on Redshift in this blog post.

We provide practical examples of deconstructing SQL from the Redshift query history. Additionally, we will present some code that utilises the FlowHigh SQL parser SDK to programmatically parse SQL from Redshift. The parsing of Redshift can be automated using the SDK.

In another post on the Sonra blog, I go into great depth on the use cases of using an SQL parser for data engineering and data governance. This article is a complete deep dive into SQL parsing, how it works and how you can use it for use cases in data engineering and data governance.

One example for a use case would be table and column audit logging. Audit logging refers to the detailed recording of access and operations performed on specific tables and columns in a database including execution of SQL queries. Such logging can be essential for ensuring security, compliance with regulatory standards, and understanding data access patterns.

SQL parser for Redshift

Sonra has created an online SQL parser. Our vision is to be SQL dialect agnostic. The parser also works with Redshift. It is called FlowHigh. This SaaS platform includes a UI for manual SQL parsing as well as an SDK for managing bulk SQL parsing requirements or automating the process. We demonstrate FlowHigh’s capabilities by parsing the query history of Redshift.

Let’s look at both options starting with the SDK for automating SQL parsing.

Programmatically parsing the Redshift query history with the FlowHigh SDK

Redshift Serverless has a table called “sys_query_history”. It contains data about the SQL query that was run. The executed query can be obtained from this table and sent to FlowHigh for analysis.

The Redshift query history

is also store in other system tables such as STL_QUERY.

To get the text of the query and the users who ran it, we can use the query below.

The python code in the next section shows how the query history is pulled from Redshift and processed using the FlowHigh SDK:

We used Redshift Serverless to parse the queries

Analysing the output of the FlowHigh SQL parser

The FlowHigh SQL parser for Redshift processes incoming SQL queries and outputs the results in either Redshift JSON or XML format. For example, from our collected query history, the parser generates a comprehensive JSON or XML representation of the SQL query. This detailed output includes data on filter criteria, retrieved columns, aliases used, join conditions, tables, and other clauses of the SQL command.

Let’s go through an example

The SQL parser

also supports other DML and DDL statements such as CREATE, INSERT, UPDATE, MERGE etc.

Let’s look at the XML version of the FlowHigh SQL parser for the above SQL statement. The output of XML is slightly more condensed than the JSON message.

The XML output generated by the FlowHigh SQL parser for Redshift provides an in-depth analysis of the SQL statement.

Tables and columns

This includes a full list of table names, associated join conditions, and other essential attributes that can be derived from the XML structure. From our example, FlowHigh points out attributes such as salesid, pricepaid, eventname, and catname which are obtained from tables named sales, event, and category.


Additionally, two inner joins are identified in this query. The first join condition dictates that the eventid from the sales table matches the eventid from the event table. The second join condition confirms that the catid from the event table aligns with the catid from the category table.


The query aggregates data based on the salesid column from the sales table.

You can find the GROUP BY in the aggregation section of the XML output

Internally the column salesid is referenced as C1, which can be looked up from the query hierarchy <DBOHier> at the end of the XML message.


The query incorporates a filter condition on the pricepaid column from the sales table. Specifically, it selects records where the pricepaid value is greater than 500.You can find the FILTER in the filter section of the XML output.

Internally the column pricepaid is referenced as C2, which can be looked up form the query hierarchy <DBOHier> at the end of the XML message.


The results of the query are sorted in descending order based on the saletime column from the sales table. This ordering ensures that the most recent sales transactions, according to their time, appear at the top of the output, allowing users to quickly identify the latest transactions.

You can find the col used in order by in the sort section of the XML output.

Internally the column saletime is referenced as C9, which can be looked up from the query hierarchy <DBOHier> at the end of the XML message.

While Redshift may not always provide granular details about specific tables and columns in a query, FlowHigh supplements this information. It not only identifies the tables and columns but also zeroes in on the columns involved in join operations.

FlowHigh User Interface for SQL parsing

You can also access the FlowHigh SQL parser through the web based user interface. The below figure shows how FlowHigh provides the information about tables in a SQL query by grouping them into types of tables.

When we select a table name, it reveals the associated column names. For instance, by selecting the ‘SALES’ table, we can view its corresponding column names.

Likewise FlowHigh can be used to get columns used in a where conditions ,order by, group by and joins in the SQL query.

Columns used in GROUP BY / ORDER BY clause

This figure shows how FlowHigh can be used to filter out the columns used in order by and group by clause.

Filter Columns

Columns in join conditions

Table audit logging in Redshift

Audit logging in a database refers to the practice of keeping a detailed record (audit trail) of operations, transactions, or changes made to objects in the database such as tables, views, and stored procedures etc.

What is table audit logging?

Audit logging at the table level in a database focuses on capturing activities or changes related to individual tables. It concentrates on tables rather than a broad range of database objects like views, stored procedures, triggers, etc.

Table-level audit logging can record various activities, including:

  • Data Modification: Tracks operations like INSERT, UPDATE, and DELETE, capturing when these operations occurred, by whom, and sometimes even the specific data that was changed.
  • Data Access: Records when specific tables are accessed using SELECT queries.
  • Schema Changes: Captures alterations to the table structure itself, such as when columns are added, modified, or deleted.
  • Permission Changes: Records changes to the permissions or roles associated with specific tables.

Why do you need table audit logging?

There are various use cases for auditing access and changes to tables:

  • Security: To detect unauthorised access or changes to critical data tables.
  • Compliance: Regulatory requirements may require logging for specific tables containing sensitive or personally identifiable information.
  • Forensics: In the event of anomalies or issues, audit logs can help trace back the series of events leading to the problem.
  • Accountability: Ensures that individuals or systems are held accountable for their actions and changes made to the data.
  • Identify usage patterns I: Table audit logging can identify tables that are queried frequently or queried together.
  • Identify usage patterns II: You can identify tables that are not queried at all or only queried infrequently. These are candidates for archiving or dropping.

Table audit logging in Redshift


Unlike other databases such as Snowflake, Databricks, SQL Server, Oracle etc. Redshift does not provide metadata about what tables were accessed in an SQL query or DML statement.

Getting to the bottom of who accessed a table or any other objects is not straightforward.

We tried a few things but did not find a good native way to access a table audit log.

As we have learned, the query text itself is stored in various tables, e.g. QUERYTXT in sys_query_history

We tried to cross join STL_QUERY to PG_TABLE, which is an information schema table that lists all the tables of your Redshift instance. However, we ran into other limitations.

One other approach that we did not try is to use the details of the explain plan in STL_SCAN to cross join against the text of the query in STL_QUERY. However, this seems like a bad hack rather than a proper solution

Table and column audit logging in Redshift with FlowHigh

FlowHigh to the rescue. The most straightforward way to accomplish our goal of column and table logging on Redshift is to parse the QUERYTXT from STL_QUERY.

This is easy with the FlowHigh SQL parser.

We used the FlowHigh SDK to write a bit of code to scan the Redshift query history in STL_QUERY and parse out any tables and columns that are part of the queries recorded in QUERYTXT.

You can try it out yourself and register for the FlowHigh.