This article discusses two types of audit trails, 1) SQL statement execution logs, and 2) session logs for security.

The Snowflake documentation discusses the QUERY_HISTORY functions, which “return query activity within the last 7 days.” Although limited to seven days, these functions provide an easy way to create a SQL log retained indefinitely.

--Create database AUDIT_DB
create database AUDIT_DB;

Next, create a SQL audit table with all available information returnable from the QUERY_HISTORY function.

--Create an audit table
create table "AUDIT_DB"."PUBLIC"."AUDIT_LOG" as
select *
  from table(information_schema.query_history(RESULT_LIMIT=>10000))
order by START_TIME;

Since the QUERY_HISTORY returns up to seven days of SQL statement history, to preserve the log indefinitely this SQL needs to run at least once every N days where N < 7:

--Add new audit items to the table
insert into "AUDIT_DB"."PUBLIC"."AUDIT_LOG"
  select * from table(information_schema.query_history(RESULT_LIMIT=>10000))
  where START_TIME > (select max(START_TIME) as LASTDATE from AUDIT_DB.PUBLIC.AUDIT_LOG)
order by START_TIME;