Query the session archive

Query archived Omnia sessions with SQL in Snowflake, BigQuery, ClickHouse, Athena or DuckDB, reading the Parquet files in your bucket directly.

Prerequisites

• The compaction job has archived sessions to your S3, GCS or Azure Blob bucket. The job archives sessions last updated more than warmStore.retentionDays days ago (7 when the policy sets none). See Session retention.
• Your SQL engine has read access to the bucket.
• You know the layout: sessions/year=YYYY/month=MM/day=DD/agent=<name>/part-NNNN.parquet, partitioned by the session’s creation date. See Session archive format for the partitions, manifest and every column.

Point the file pattern at *.parquet; the bucket also holds sessions/_manifest.json.

Turn on the archive with Helm

The compaction job archives when the policy it mounts has coldArchive.enabled, and writes to the bucket under sessionRetention.compaction.coldArchive:

sessionRetention:
  defaultPolicy:
    coldArchive:
      enabled: true
      retentionDays: 365
  compaction:
    enabled: true
    coldArchive:
      backend: s3            # s3, gcs or azure
      bucket: my-omnia-archive
      region: us-east-1      # required for s3
      endpoint: ""           # set for MinIO and other S3-compatible stores

For Azure Blob, set backend: azure, put the container name in bucket, and set coldArchive.azure.accountName. Name a Secret in coldArchive.azure.accountKeySecret to authenticate with the account key; leave it empty to use workload identity. For S3 and GCS, grant the bucket to the compaction ServiceAccount through sessionRetention.compaction.serviceAccount.annotations (IRSA or Workload Identity).

The chart refuses to render when the default policy archives and compaction.coldArchive.bucket is empty.

Convert nanosecond timestamps

The timestamp columns (created_at, updated_at, expires_at, ended_at) are int64 nanoseconds since the Unix epoch. Convert them in your query:

Engine	Expression
Snowflake	TO_TIMESTAMP_NTZ(created_at / 1000000000)
BigQuery	TIMESTAMP_MICROS(DIV(created_at, 1000))
ClickHouse	fromUnixTimestamp64Nano(created_at)
Athena / Trino	from_unixtime(created_at / 1000000000)
DuckDB	make_timestamp(created_at // 1000)

The messages_json column holds the conversation as a JSON string. Each engine’s JSON functions parse it in the queries below.

Query from Snowflake

Create an external stage pointing at your bucket, then an external table over the Parquet files:

CREATE OR REPLACE STAGE omnia_archive_stage
  URL = 's3://my-omnia-archive/sessions/'
  STORAGE_INTEGRATION = my_s3_integration
  FILE_FORMAT = (TYPE = PARQUET);

CREATE OR REPLACE EXTERNAL TABLE omnia_sessions
  WITH LOCATION = @omnia_archive_stage
  PATTERN = '.*[.]parquet'
  FILE_FORMAT = (TYPE = PARQUET)
  AUTO_REFRESH = TRUE;

Query the conversation content using VARIANT:

SELECT
  VALUE:id::STRING                                              AS session_id,
  VALUE:agent_name::STRING                                      AS agent,
  TO_TIMESTAMP_NTZ(VALUE:created_at::NUMBER / 1000000000)       AS created_at,
  VALUE:total_input_tokens::NUMBER                              AS input_tokens,
  VALUE:estimated_cost_usd::FLOAT                               AS cost_usd,
  PARSE_JSON(VALUE:messages_json::STRING)                       AS messages
FROM omnia_sessions
WHERE VALUE:agent_name::STRING = 'my-agent'
  AND VALUE:created_at::NUMBER >= DATE_PART('epoch_nanosecond', '2026-04-01'::TIMESTAMP);

For high-frequency BI dashboards where external table read cost becomes noticeable, use Snowpipe to load the files into a native table on an S3 ObjectCreated event.

Query from BigQuery

Create a Hive-partitioned external table:

CREATE OR REPLACE EXTERNAL TABLE `my_project.omnia.sessions`
  WITH PARTITION COLUMNS (
    year INT64,
    month INT64,
    day INT64,
    agent STRING
  )
  OPTIONS (
    format = 'PARQUET',
    hive_partition_uri_prefix = 'gs://my-omnia-archive/sessions/',
    uris = ['gs://my-omnia-archive/sessions/year=*/month=*/day=*/agent=*/*.parquet']
  );

Partition pruning works automatically because BigQuery recognizes the year=/month=/day=/agent= segments:

SELECT
  id,
  agent_name,
  TIMESTAMP_MICROS(DIV(created_at, 1000)) AS created_at,
  total_input_tokens,
  estimated_cost_usd,
  JSON_EXTRACT_ARRAY(messages_json, '$') AS messages
FROM `my_project.omnia.sessions`
WHERE year = 2026 AND month = 4
  AND agent_name = 'my-agent';

For native-table performance, use BigQuery Data Transfer Service to ingest the same files into a partitioned managed table.

Query from ClickHouse

ClickHouse reads S3 Parquet natively via the s3 table function:

SELECT
  id,
  agent_name,
  fromUnixTimestamp64Nano(created_at) AS created_at,
  total_input_tokens,
  estimated_cost_usd,
  JSONExtractArrayRaw(messages_json)  AS messages
FROM s3(
  'https://my-omnia-archive.s3.us-east-1.amazonaws.com/sessions/year=2026/month=04/**/*.parquet',
  'AKIA...', 'SECRET...',
  'Parquet'
)
WHERE agent_name = 'my-agent';

Or create a persistent table using the S3 engine:

CREATE TABLE omnia_sessions
ENGINE = S3(
  'https://my-omnia-archive.s3.us-east-1.amazonaws.com/sessions/**/*.parquet',
  'AKIA...', 'SECRET...',
  'Parquet'
);

Query from Athena

Athena reads Parquet on S3 via a Glue-registered external table:

CREATE EXTERNAL TABLE omnia_sessions (
  id                   STRING,
  agent_name           STRING,
  namespace            STRING,
  workspace_name       STRING,
  status               STRING,
  created_at           BIGINT,
  updated_at           BIGINT,
  expires_at           BIGINT,
  ended_at             BIGINT,
  message_count        INT,
  tool_call_count      INT,
  total_input_tokens   BIGINT,
  total_output_tokens  BIGINT,
  estimated_cost_usd   DOUBLE,
  tags                 STRING,
  state                STRING,
  last_message_preview STRING,
  messages_json        STRING
)
PARTITIONED BY (year INT, month INT, day INT, agent STRING)
STORED AS PARQUET
LOCATION 's3://my-omnia-archive/sessions/'
TBLPROPERTIES ('parquet.compression' = 'SNAPPY');

MSCK REPAIR TABLE omnia_sessions;

Then query with partition pruning:

SELECT
  id,
  agent_name,
  from_unixtime(created_at / 1000000000) AS created_at,
  CAST(json_extract(messages_json, '$') AS ARRAY<JSON>) AS messages
FROM omnia_sessions
WHERE year = 2026 AND month = 4
  AND agent_name = 'my-agent';

Query from DuckDB

For local / ad-hoc exploration, DuckDB reads Parquet on object storage directly:

INSTALL httpfs;
LOAD httpfs;

SET s3_region = 'us-east-1';
SET s3_access_key_id = 'AKIA...';
SET s3_secret_access_key = 'SECRET...';

SELECT
  id,
  agent_name,
  make_timestamp(created_at // 1000)                 AS created_at,
  total_input_tokens,
  estimated_cost_usd,
  json_extract(messages_json, '$')::JSON             AS messages
FROM read_parquet('s3://my-omnia-archive/sessions/**/*.parquet', hive_partitioning = true)
WHERE year = 2026 AND month = 4
  AND agent_name = 'my-agent';

DuckDB infers the year, month, day, and agent partition columns automatically when hive_partitioning = true is set.

Verify

Run the engine’s query with a filter on one agent. It returns one row per archived session of that agent, with messages holding the parsed conversation.

Troubleshooting

The query returns no rows

A session is archived only after the compaction CronJob runs and the session is older than the warm retention. With cold archive disabled, the same job deletes expired sessions and keeps no copy. Check that the bucket holds .parquet objects under sessions/.

The engine fails on _manifest.json

Restrict the file pattern to *.parquet, as the queries above do.

Athena returns no rows for new days

Run MSCK REPAIR TABLE omnia_sessions; again to register the new partitions.

Related pages

• Session archive format: layout, partitions and Parquet schema
• Session retention: how sessions reach the archive and when cold data is purged
• SessionRetentionPolicy CRD: warm and cold retention fields