Historical Snapshots

Historical snapshots allow you to track changes in your cloud infrastructure over time by automatically capturing and storing table data at regular intervals. This feature enables trend analysis, compliance auditing, and historical asset tracking.

Historical snapshots are automatically generated copies of your CloudQuery tables that include a timestamp indicating when the data was captured. These snapshots are stored in special snapshot_ tables that you can query to understand how your infrastructure has changed over time.

Snapshots are taken daily, even when no sync is running, ensuring complete historical coverage without gaps.

Each snapshot includes a _cq_snapshot_time column that records exactly when the data was captured.

Snapshot tables automatically adapt to schema changes, always matching the latest plugin version.

Using Historical Snapshots

Table Naming Convention

Snapshot tables follow the pattern: snapshot_[original_table_name]

Examples:

• aws_ec2_instances → snapshot_aws_ec2_instances

• gcp_compute_instances → snapshot_gcp_compute_instances

• azure_storage_accounts → snapshot_azure_storage_accounts

Browsing Historical Snapshots

You can browse the Historical Snapshots in the "Historical Snapshots" tab in the SQL Console.

You can use the Snapshot tables in the SQL Console the same way you use any other table. You can even combine regular and Snapshot tables in a single query.

Example use cases with queries

Asset History Tracking

Query historical states of resources that no longer exist:

-- Find parameters of a deleted EC2 instance
SELECT * FROM snapshot_aws_ec2_instances 
WHERE instance_id = 'i-0f73af6b372abb8c7' 
AND toDate(_cq_snapshot_time) = toDate('2025-07-08')

Trend Analysis

Track trends of resource count over time:

-- Daily count of EC2 instances
SELECT toDate(_cq_snapshot_time) as day, 
       count(*) as instance_count 
FROM snapshot_aws_ec2_instances 
GROUP BY day 
ORDER BY day

Historical Reports

Historical Snapshots tables are available to reports, allowing you to create perfect visualizations of your queries. Here's an example query showing count of storage buckets by day:

SELECT 
    date,
    map(
        'aws', sumIf(bucket_count, cloud = 'aws'),
        'gcp', sumIf(bucket_count, cloud = 'gcp'), 
        'azure', sumIf(bucket_count, cloud = 'azure')
    ) AS storage_counts
FROM (
    SELECT 
        toString(toDate(_cq_snapshot_time)) AS date,
        'aws' AS cloud,
        count() AS bucket_count
    FROM snapshot_aws_s3_buckets
    GROUP BY date
    
    UNION ALL
    
    SELECT 
        toString(toDate(_cq_snapshot_time)) AS date,
        'gcp' AS cloud,
        count() AS bucket_count
    FROM snapshot_gcp_storage_buckets
    GROUP BY date
    
    UNION ALL
    
    SELECT 
        toString(toDate(_cq_snapshot_time)) AS date,
        'azure' AS cloud,
        count() AS bucket_count
    FROM snapshot_azure_storage_containers
    GROUP BY date
)
GROUP BY date
ORDER BY date;