Audit log query reference

CDF delivers audit logs as a Delta Lake table to the cloud storage you configured in Setting up audit log streaming. This reference covers the delivery model, query examples for common Delta Lake-compatible tools, Delta maintenance, and how to keep the table intact. For delivery timing and the log schema, see About CDF audit logs.

Delivery model

Audit logs are written as a Delta Lake table in storage you own. Your stream is scoped to your organization or to specific projects within it. Cognite creates the table when the stream is first set up and appends new events in batches. Cognite does not modify existing events.

Partitioning — The table is partitioned by orgName, year, and month. Most streams contain a single CDF organization. If your stream covers multiple organizations, filter with orgName = 'your-org' in WHERE clauses so the query engine applies partition pruning and scans less data.
Write cadence — Batches flush when a buffer reaches its size threshold or when a maximum time since the last successful write is reached, whichever comes first. The maximum interval is 60 minutes.
Durability — Each flush is a Delta commit. Failed or incomplete writes do not appear in your bucket.
Table properties — Cognite sets properties such as target file size and deleted file retention when the table is created. Verify the table configuration before running maintenance.

You control ongoing Delta operations such as OPTIMIZE and VACUUM. See Delta maintenance for recommended commands and schedule.

Query examples

The Delta Lake table can be queried with any compatible tool. Common options include:

Amazon Athena — for AWS-hosted storage
Azure Synapse Analytics — for Azure-hosted storage
Google BigQuery — for GCP-hosted storage
Apache Spark — for any storage, shown in the examples below

from delta.tables import DeltaTable
from pyspark.sql import SparkSession

spark = SparkSession.builder \
    .appName("AuditLogQueries") \
    .config("spark.sql.extensions", "io.delta.sql.DeltaSparkSessionExtension") \
    .config("spark.sql.catalog.spark_catalog", "org.apache.spark.sql.delta.catalog.DeltaCatalog") \
    .getOrCreate()

# Azure Blob Storage
spark.sql("""
    CREATE TABLE IF NOT EXISTS audit_logs
    USING DELTA LOCATION 'wasbs://{CONTAINER}@{STORAGE_ACCOUNT}.blob.core.windows.net/{PATH_TO_TABLE}'
""")

# Amazon S3
# spark.sql("""
#     CREATE TABLE IF NOT EXISTS audit_logs
#     USING DELTA LOCATION 's3://{BUCKET}/{PATH_TO_TABLE}/'
# """)

# Google Cloud Storage
# spark.sql("""
#     CREATE TABLE IF NOT EXISTS audit_logs
#     USING DELTA LOCATION 'gs://{BUCKET}/{PATH_TO_TABLE}/'
# """)

Find all actions by a specific principal

df = spark.sql("""
    SELECT timestamp, orgName, projectName, serviceName, eventType, responseCode
    FROM audit_logs
    WHERE orgName = 'your-org'
      AND principalId = 'a3b7c9d2-e5f1-4820-b6a3-12f4e8c01d99'
    ORDER BY timestamp DESC
""")
df.show()

Find all events in a time window

Filter using the year and month partition columns to apply partition pruning.

df = spark.sql("""
    SELECT timestamp, principalId, serviceName, eventType, responseCode
    FROM audit_logs
    WHERE orgName = 'your-org'
      AND year = 2024 AND month = 11
    ORDER BY timestamp DESC
""")
df.show()

Find failed requests

df = spark.sql("""
    SELECT timestamp, principalId, serviceName, eventType, responseCode
    FROM audit_logs
    WHERE orgName = 'your-org'
      AND responseCode >= 400
    ORDER BY timestamp DESC
""")
df.show()

Count failures per principal

df = spark.sql("""
    SELECT principalId, eventType, responseCode, COUNT(*) AS failureCount
    FROM audit_logs
    WHERE orgName = 'your-org'
      AND responseCode >= 400
    GROUP BY principalId, eventType, responseCode
    ORDER BY failureCount DESC
""")
df.show()

Find authentication events

df = spark.sql("""
    SELECT timestamp, principalId, requestParameters, responseCode
    FROM audit_logs
    WHERE orgName = 'your-org'
      AND serviceName = 'AUTHENTICATION'
    ORDER BY timestamp DESC
""")
df.show()

Find group changes

Monitor IAM events that create or delete groups.

df = spark.sql("""
    SELECT timestamp, principalId, eventType, requestParameters, responseCode
    FROM audit_logs
    WHERE orgName = 'your-org'
      AND serviceName = 'IAM'
      AND eventType IN ('CREATE_GROUPS', 'DELETE_GROUPS')
    ORDER BY timestamp DESC
""")
df.show()

Summarize activity by service

df = spark.sql("""
    SELECT serviceName, eventType, COUNT(*) AS requestCount
    FROM audit_logs
    WHERE orgName = 'your-org'
    GROUP BY serviceName, eventType
    ORDER BY requestCount DESC
""")
df.show()

Delta maintenance

Delta Lake maintenance requires two operations: storage optimization and data retention.

Recommended schedule

Operation	Typical schedule	When to run more often
`OPTIMIZE`	Weekly	If file counts grow large, queries slow down, or queries read many small files
`VACUUM`	Weekly, after `OPTIMIZE` in the same maintenance window	If storage cost from orphaned files is high and your retention policy allows earlier physical deletion
Review table properties	Quarterly or when compliance rules change	When you change legal retention requirements or need longer time travel

Tune the schedule to your data volume and internal policies. A low-traffic organization may be fine with bi-weekly maintenance; a high-volume organization may need daily OPTIMIZE on recent partitions only.

Order of operations

Always run maintenance in this order:

OPTIMIZE — merge small Parquet files created by frequent appends.
VACUUM — remove files that are no longer referenced by the Delta log.

OPTIMIZE audit_logs;
VACUUM audit_logs RETAIN 7 DAYS;

Delta Lake supports automated optimization through optimized write and auto-compaction. Cognite recommends scheduled offline optimization instead, tuned to your data volume and needs.

Data retention

Delta differentiates between logical and physical retention. Data must be deleted logically before Delta removes the underlying files. This lets you recover data before files are permanently erased. See the Delta Lake data retention documentation for details. Logical deletion uses a DELETE query:

-- Logically delete events older than 365 days
DELETE FROM audit_logs
WHERE timestamp < CURRENT_TIMESTAMP - INTERVAL '365 days';

Physical deletion uses VACUUM:

VACUUM audit_logs RETAIN 7 DAYS;

The RETAIN period must be greater than or equal to the table’s delta.deletedFileRetentionDuration setting. Setting RETAIN below your required policy may permanently delete files needed for time travel or forensic investigation. Always specify the retention duration explicitly when using VACUUM.

Default table configuration

Cognite sets the following Delta properties when creating the table. Verify or reapply them before running OPTIMIZE or VACUUM if your maintenance depends on custom values.

Property	Default value	Description
`delta.deletedFileRetentionDuration`	`interval 7 days`	Logically deleted data becomes eligible for physical deletion after 7 days. A subsequent `VACUUM` permanently deletes these files.
`delta.targetFileSize`	`512 MB`	File size target for the `OPTIMIZE` command.

To adjust a property:

-- Example: increase physical data retention to 30 days
ALTER TABLE audit_logs SET TBLPROPERTIES (
  'delta.deletedFileRetentionDuration' = 'interval 30 days'
);

Data integrity

Cognite’s compatibility guarantees apply at the Delta Lake table level. Use a Delta-compatible reader (such as DuckDB, Apache Spark, or Microsoft Fabric) and do not depend on the structure, naming, or location of individual .parquet files.

Do not modify the Delta log

The Delta log in the _delta_log directory contains the metadata required to read the table.

Partial deletion — Removing individual log files makes the table state inconsistent. Queries may fail or return incorrect results.
Total deletion — If the entire directory is deleted, the storage path is no longer recognized as a Delta table. Cognite generates a new log as it continues to append data, starting at version 0. Data written before the deletion remains on disk but is invisible to the new table.
Manual edits — Modifying the log corrupts table history and makes VACUUM unsafe because the system can no longer determine which files are active.

Do not modify Parquet files manually

Parquet files in the table directory are managed by the Delta transaction log.

Manual deletion — The log still references deleted files, causing queries to fail with FileNotFoundException.
Manual addition — Files added outside the transaction log are invisible to Delta Lake.
Manual moves or renames — Disrupt the mapping between the log and physical files and can cause OPTIMIZE or VACUUM to fail.

To remove data safely, use DELETE followed by VACUUM.

Storage tiering

Delta Lake requires frequent access to both the transaction log and data files. Manage storage tiers as follows: Delta log — hot storage only The _delta_log directory is read every time the table is opened or queried. Keep it on your provider’s most performant storage tier. Data files — cold storage optional Parquet data files can use lower-cost, infrequent-access tiers if you query older data less often. Archive tiers — not supported Archive-class tiers (for example, Azure Archive or AWS S3 Glacier) take data offline and require rehydration before reads. Delta Lake expects all referenced files to be immediately available. Moving any part of an active Delta table to an archive tier causes query failures.

Administration

Audit log query reference

Delivery model

Query examples

Find all actions by a specific principal

Find all events in a time window

Find failed requests

Count failures per principal

Find authentication events

Find group changes

Summarize activity by service

Delta maintenance

Recommended schedule

Order of operations

Data retention

Default table configuration

Data integrity

Do not modify the Delta log

Do not modify Parquet files manually

Storage tiering

Further reading

​Delivery model

​Query examples

​Find all actions by a specific principal

​Find all events in a time window

​Find failed requests

​Count failures per principal

​Find authentication events

​Find group changes

​Summarize activity by service

​Delta maintenance

​Recommended schedule

​Order of operations

​Data retention

​Default table configuration

​Data integrity

​Do not modify the Delta log

​Do not modify Parquet files manually

​Storage tiering

​Further reading

Delivery model

Query examples

Find all actions by a specific principal

Find all events in a time window

Find failed requests

Count failures per principal

Find authentication events

Find group changes

Summarize activity by service

Delta maintenance

Recommended schedule

Order of operations

Data retention

Default table configuration

Data integrity

Do not modify the Delta log

Do not modify Parquet files manually

Storage tiering

Further reading