Logging, Tracing, and Audit

1. Logging, Tracing, and Audit

So far, we've covered cost control and data protection. This video closes out the chapter with a different kind of protection — the ability to prove what happened. Claro's compliance team doesn't just need data to be secure. They need to demonstrate to auditors that they can trace every data access, every failed login, and every procedure execution back to a specific user and time.

2. The Audit Problem

Claro's compliance team receives an audit request: who accessed credit scores in the last ninety days? The security team wants to know which stored procedures ran in the early hours of a specific morning. The compliance officer wants to know if any logins came from outside the corporate network. These questions require a complete, queryable record that must exist before the question is asked.

3. What are Event Tables?

An event table is a special Snowflake table designed to capture log and trace data emitted by Snowflake objects — stored procedures, Snowpark functions, and UDFs. You create it like a regular table, then designate it as the active event table using ALTER ACCOUNT. From that point, log and trace messages are written into it automatically. Because it's a standard Snowflake table, you query it with SQL — no external logging tools required.

4. Log Levels

Log levels control how much detail gets written to the event table. TRACE is most granular. It captures every function entry and exit, useful during development but noisy in production. DEBUG captures diagnostic detail. INFO records normal execution milestones. WARN flags unexpected conditions that didn't stop execution. ERROR records failures. FATAL records critical failures that halted execution. In production, Claro would typically set INFO as the minimum level.

5. Tracing

Tracing captures the execution path through a multi-step process — not individual messages, but how steps connect. If Claro's pipeline calls three stored procedures and a Snowpark function, tracing shows how long each step took and in what order they executed. Trace data writes to the same event table as log data. For complex pipelines, tracing is what lets you pinpoint where time is spent or where execution diverged.

6. Using LOGIN_HISTORY for Auditing

Here we're asking not how many failed logins occurred, but whether any logins came from outside Claro's corporate network. The query filters LOGIN_HISTORY for login events from IP addresses outside the expected corporate range over the last ninety days — who authenticated, from where, using which method, with any failures clearly visible.

7. QUERY_HISTORY for Audit

Here QUERY_HISTORY isn't about finding the slowest queries — it's about finding which queries touched a specific sensitive table. The query filters for any statement referencing the credit scores table in the last ninety days, returning the user, role, warehouse, and exact query text. This is how the compliance team demonstrates to an auditor exactly who accessed sensitive financial data, when, and what they ran.

8. The Complete Audit Trail

These three sources give Claro a complete audit trail. The event table captures the operational record: logs and traces from stored procedures and Snowpark functions. LOGIN_HISTORY covers authentication — who connected, from where, and whether any attempts failed. QUERY_HISTORY captures every SQL statement run against the account. Together they answer what the platform did, who logged in, and what data was touched. This is an audit trail an auditor would expect, entirely queryable from within Snowflake.

9. Let's practice!

Now it's your turn. The exercises ahead will have you building audit queries across LOGIN_HISTORY and QUERY_HISTORY to answer real compliance questions for Claro.

Create Your Free Account

By continuing, you accept our Terms of Use, our Privacy Policy and that your data is stored in the USA.