Skip to Content
PlatformAdvanced TopicsUnderstanding Platform Views

Understanding Platform Views

When you query data on CloudQuery Platform (whether in the Asset Inventory, the SQL Console, or through Policies), you’re querying views, not the raw synced tables directly. Understanding how these views work helps when writing SQL queries, debugging sync results, or configuring data access controls.

Platform views only apply to the default ClickHouse destination. If you sync to a custom destination (e.g. PostgreSQL or BigQuery), views are not created and you query the tables directly. See Syncs for more on write modes with custom destinations.

As described in Syncs, the platform stores synced data in tables prefixed with raw_ and creates views on top of them. This page explains how those views are constructed.

Cloud Assets View

When a sync runs on CloudQuery Platform, the sync is configured (through use of a transformer) to prefix all output tables with raw_ (e.g., raw_aws_ec2_instances, raw_gcp_compute_instances). Each raw table row includes a _cq_sync_group_id, a unique identifier for the sync run that produced it.

As tables complete syncing, the platform builds a snapshot table that combines and normalizes data from all raw tables into a single schema. This snapshot contains columns like cloud, account, name, region, resource_type, and tags, providing a consistent structure regardless of the source integration. Historical (full) tables and incremental tables are handled differently:

  • Historical tables (the majority of tables) are filtered to only the latest complete _cq_sync_group_id, so stale data from previous syncs is excluded.
  • Incremental tables include all rows, since incremental tables only ever add new data.

During this process, rows are deduplicated so that only the most recent version of each record (by _cq_id) is included.

Finally, the cloud_assets view is atomically pointed at the new snapshot table. Old snapshot tables are cleaned up automatically. This atomic swap means you always query a complete, consistent dataset, never partial sync results.

Diagram showing how raw tables flow through cloud_assets_historical and cloud_assets_incremental into the unified cloud_assets view

For example, you can query the cloud_assets view directly in the SQL Console:

SELECT account, resource_type, name, tags
FROM cloud_assets
WHERE resource_type = 'aws_ec2_instances'

This returns only the latest snapshot of EC2 instances, even though the underlying raw_aws_ec2_instances table may contain data from multiple syncs.

Table Views

Table views follow the same pattern but are simpler. For every raw table, the platform creates a view with the original table name (e.g., raw_aws_ec2_instances becomes aws_ec2_instances).

Behind the scenes, the platform:

  1. Creates a new snapshot table from the raw table, filtered to the latest complete _cq_sync_group_id and deduplicated.
  2. Atomically replaces the view to point at the new snapshot.
  3. Drops the old snapshot table.

This keeps data consistent during syncs and switches atomically, while still allowing records to be appended efficiently into ClickHouse.

Diagram illustrating how table views are defined. Diagram illustrating how table views are defined.

What Happens During a Sync?

While a sync is in progress, queries against views continue to return the previous complete snapshot. The view only switches to include new data once a table’s sync is fully complete and the new snapshot is built. This means you never see partial or in-progress data.

Next Steps

Last updated on
Custom ColumnsPerformance Tuning