Historical Snapshots

Historical Snapshots capture the state of your cloud data at regular intervals, giving you the ability to track how your infrastructure changes over time. Use them for trend analysis, compliance auditing, incident investigation, and understanding resource lifecycle.

How It Works

CloudQuery automatically creates daily snapshots of your tables and stores them in dedicated snapshot_ tables. Snapshots are taken daily, even when no sync is running, so there are no gaps in your historical record.

Each snapshot row includes a _cq_snapshot_time column that records when the data was captured. Snapshot tables automatically adapt to schema changes, always matching the latest integration version.

Table Naming Convention

Snapshot tables follow the pattern snapshot_[original_table_name]:

Original table	Snapshot table
`aws_ec2_instances`	`snapshot_aws_ec2_instances`
`gcp_compute_instances`	`snapshot_gcp_compute_instances`
`azure_storage_accounts`	`snapshot_azure_storage_accounts`

Browsing Snapshots

Open the SQL Console and select the Historical Snapshots tab in the left sidebar to see all available snapshot tables.

Historical Snapshots tab in the SQL Console showing available snapshot tables for time-travel queries

You can query snapshot tables the same way you query any other table, and you can join snapshot tables with regular tables in a single query.

Example Queries

Asset History Tracking

Look up the state of a resource that has since been deleted or modified:


-- 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 resource counts over time to spot growth trends or unexpected changes:


-- 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

Change Detection

Compare two snapshots to find what changed between dates:


-- Find EC2 instances that existed last week but not today
SELECT yesterday.instance_id, yesterday.instance_type, yesterday.region
FROM snapshot_aws_ec2_instances AS yesterday
LEFT JOIN snapshot_aws_ec2_instances AS today
  ON yesterday.instance_id = today.instance_id
  AND toDate(today._cq_snapshot_time) = today()
WHERE toDate(yesterday._cq_snapshot_time) = today() - 7
  AND today.instance_id IS NULL

Historical Reports

Snapshot tables work with Reports, so you can build visualizations that show trends over time. This query shows storage bucket counts by cloud provider per 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;

Storage buckets count over time

SQL Console: run queries against snapshot and live tables
Reports: visualize snapshot query results in dashboards
Policies: combine with snapshots to track compliance drift over time

Next Steps

SQL Console - Write and manage SQL queries
Understanding Platform Views - How the platform creates queryable views
Reports - Build reports from snapshot queries