Snowflake Data Activity History

The Snowflake data activity history serves as an essential component in modern data infrastructure, providing detailed insights into data access and usage patterns. Recent findings from ENISA’s Threat Landscape report underscore its importance: ransomware attacks increased by 288% in 2023, with 73% of incidents involving data exfiltration before encryption. Organizations leverage Snowflake‘s activity history capabilities to monitor data access, detect potential security threats, and maintain compliance with industry regulations. This comprehensive guide explores how to leverage and optimize your Snowflake data activity history capabilities for maximum security and efficiency.

What Is Snowflake Data Activity History?

Snowflake Data Activity History refers to the tracking of all data interactions within a Snowflake environment. This includes who accessed or modified data, when these actions occurred, and what kind of changes were made. Activity history plays a critical role in auditing, compliance, and security, particularly when dealing with sensitive data.

Snowflake’s native Data Activity History functionality helps businesses keep a detailed log of events, enabling you to track user actions, such as data retrieval, insertion, deletion, or modifications. This is invaluable when conducting audits, ensuring compliance, or investigating potential security incidents.

Native Features for Tracking Data Activity History in Snowflake

Snowflake’s native tools offer a range of options to track data activity within the platform. These tools include SQL features, views, and stored procedures, making it easier for data administrators to capture and analyze user activity. Let’s take a look at how you can use these tools to track Snowflake Data Activity History effectively.

1. Snowflake Views for Data Activity History

One of the most efficient ways to access data activity history in Snowflake is through its INFORMATION_SCHEMA views. These views provide details about various operations performed within your Snowflake environment. Some key views for tracking activity include:

• QUERY_HISTORY: This view allows you to track the queries executed in the Snowflake environment, including the time, user, and execution status.
• LOGIN_HISTORY: This view logs all user login attempts, helping you monitor who has accessed the system.
• ACCESS_HISTORY: This view logs all access to tables, files, and other objects within Snowflake, providing details on who accessed what data and when.

To retrieve the data from these views, you can use SQL queries. For example, the following SQL query retrieves information about queries executed within the last 24 hours:

SELECT 
    query_id, 
    user_name, 
    query_text, 
    start_time, 
    end_time
FROM 
    SNOWFLAKE.ACCOUNT_USAGE.QUERY_HISTORY
WHERE 
    start_time > DATEADD(DAY, -1, CURRENT_TIMESTAMP)
ORDER BY 
    start_time DESC;

This will return a list of queries executed in the last 24 hours, with relevant details such as the user who ran the query, the query text, and the execution duration.

2. Snowflake Stored Procedures for Auditing

Stored procedures in Snowflake can automate and extend the monitoring process by creating custom auditing solutions. You can write stored procedures that monitor specific tables, track changes, and log activities to custom tables. Here’s an example of how you might create a stored procedure to track updates on a sensitive data table:

CREATE OR REPLACE PROCEDURE audit_sensitive_data_updates()
RETURNS STRING
LANGUAGE SQL
AS
$$
BEGIN
    -- Insert update activity into audit log
    INSERT INTO sensitive_data_audit_log (user_name, action, timestamp)
    SELECT CURRENT_USER(), 'UPDATE', CURRENT_TIMESTAMP()
    FROM sensitive_data_table
    WHERE last_updated > CURRENT_TIMESTAMP() - INTERVAL '1 DAY';
    RETURN 'Audit completed successfully';
END;
$$;

This stored procedure monitors updates to a table containing sensitive data and logs the activity, including the user name, action type, and timestamp.

3. Snowflake CLI for Activity Monitoring

For users who prefer working with command-line interfaces (CLI), Snowflake provides Snowflake CLI to interact with your Snowflake environment programmatically. You can use it to query activity history and automate certain monitoring tasks.

To query the activity history via the Snowflake CLI, you can use the following command:

snowsql -q "SELECT * FROM SNOWFLAKE.ACCOUNT_USAGE.QUERY_HISTORY 
WHERE start_time > CURRENT_TIMESTAMP - INTERVAL 1 DAY"

This command fetches the query history for the past 24 hours, similar to the SQL example above but executed through the CLI.

Implementing DataSunrise for Enhanced Monitoring

While Snowflake provides powerful native tools for data activity history, third-party solutions like DataSunrise can add an extra layer of security and ease of management. DataSunrise is a robust database security suite designed to centralize control over all data masking, auditing, and monitoring rules. It enhances Snowflake’s capabilities by providing detailed insights into data access, as well as protecting sensitive data from unauthorized access.

How to Create a DataSunrise Instance and View Data Activity History

Assuming DataSunrise is already installed, you can create a new instance by following the platform’s setup instructions. The process is also shown below:

1. Create Snowflake Instance in DataSunrise



2. Configure an Audit Rule for monitoring



3. You can check audit events in the “Transactional Trails” section

With DataSunrise, you can easily track Snowflake Data Activity History by integrating it with your Snowflake environment, enabling you to:

• Monitor who accessed what data.
• Track modifications made to sensitive data.
• Set up alerts for unauthorized access attempts.

Additionally, DataSunrise helps enforce security policies across your entire Snowflake instance, ensuring a consistent and unified approach to data activity monitoring and protection.

Benefits of Using DataSunrise for Data Activity History and Security

DataSunrise provides a comprehensive security suite that offers several benefits:

• Centralized Control: DataSunrise allows centralized management of data masking, auditing, and access control policies, making it easier to manage security at scale.
• Uniform Security Policies: You can define uniform data protection rules that apply across all environments, helping maintain compliance and data integrity.
• Real-Time Monitoring: DataSunrise provides real-time activity monitoring, so you can identify and respond to potential threats as they occur.
• Enhanced Auditing: Detailed auditing features ensure compliance with industry regulations, helping you track sensitive data access across your Snowflake environment.

Best Practices for Data Activity Monitoring

For effective data activity monitoring in Snowflake environments, organizations need to focus on four key areas:

Systematic Audit Reviews Organizations should establish systematic processes for monitoring data access and usage. This involves regular examination of access patterns, with particular attention to failed login attempts and unusual data access behaviors. Security teams should schedule weekly reviews to identify potential threats and analyze user behavior patterns across all databases and schemas.

Comprehensive Compliance Documentation Organizations face increasingly complex regulatory requirements in today’s data landscape. This necessitates detailed record-keeping of all data access and modifications. Teams must maintain comprehensive logs that track policy changes, data access patterns, and system modifications. Monthly compliance reports should document all significant events and demonstrate adherence to regulatory frameworks like GDPR, HIPAA, or SOX.

Performance Optimization Maintaining efficient monitoring systems requires careful optimization of resources and processes. Organizations should implement strategic archiving of historical audit data to prevent performance degradation. This includes setting up appropriate retention policies and optimizing monitoring queries through efficient indexing and filtering mechanisms.

Security Tool Integration Organizations should consider implementing third-party solutions like DataSunrise to enhance their monitoring capabilities. These specialized monitoring tools complement native Snowflake features by providing advanced filtering mechanisms and real-time alerting systems. By integrating such tools, organizations can achieve more comprehensive visibility into their data environment while streamlining compliance management processes.

Conclusion

Snowflake’s native Data Activity History features provide robust tools for tracking and auditing data activity in real time. By leveraging views, stored procedures, and the Snowflake CLI, businesses can gain deep insights into data interactions, enhancing security and compliance efforts. For those looking to take their data monitoring to the next level, DataSunrise offers a comprehensive security suite that enables centralized control over data activity, ensuring sensitive data is protected and managed according to best practices.

With Snowflake’s powerful native features combined with DataSunrise’s advanced security tools, you can ensure that your data remains secure, compliant, and well-monitored.

For more information on DataSunrise’s flexible, cutting-edge tools for database security, visit DataSunrise website and schedule an online demo to explore our audit and compliance features in action.