Snowflake — <redacted> queries

When a query, executed by other users, results in an error, you will see <redacted> instead of the actual SQL Statement. The error is visible, but not the SQL Statement itself.

Why would I want this<redacted> to be displayed, instead of the SQL statement itself?

In the 2023–01 update bundle, this feature was introduced: “The query history redacts the content of a query that fails due to a syntax or parsing error. The query text is replaced with <redacted>”. This was introduced for security reasons, not being able to see a query that ran into an error, possibly displaying sensitive information.

The user itself could see the query instead of <redacted> but other users looking into the QUERY_HISTORY would only see <redacted>. The query error would still be visible for others but not the query itself.

Let’s assume we have a requirement where we want to see the SQL statement that runs into an error, such as an export of failed queries to an automated analysis and monitoring system.

Is there a way to fix this, other than asking the executing user itself to have a look ? Perhaps this might not even possible when this is a service-account type of user that executes queries from your ETL tool.

Yes, there is an easy way to fix this, by setting a parameter called ENABLE_UNREDACTED_QUERY_SYNTAX_ERROR on either the ACCOUNT or USER level. First, let’s have a look what the current setting of this parameter is:

SHOW PARAMETERS LIKE '%ENABLE_UNREDACTED_QUERY_SYNTAX_ERROR%' IN ACCOUNT;

We can execute this command to make sure a specific user can see the SQL Statement:

ALTER USER abc123 SET ENABLE_UNREDACTED_QUERY_SYNTAX_ERROR=TRUE;

If we now log in as user abc123 we will see this:

When we log in as another user, it still shows <redacted>

This way, we can enable this parameter for the user that in our case reads the SQL Statements with errors from Snowflake for further analysis.

Alternatively, we can fix this behaviour for our entire Snowflake account:

ALTER ACCOUNT SET ENABLE_UNREDACTED_QUERY_SYNTAX_ERROR=TRUE;

One note to wrap this up: if you cannot use the ACCOUNTADMIN role (which has the AUDIT privilige required to set this parameter) you need to do some extra things: the AUDIT privilege then needs to be granted to a role that you can access. If you need help with this, feel free to send me a message.

Links: the 2023–01 bundle description

The parameter description