> ## Documentation Index
> Fetch the complete documentation index at: https://docs.cognite.com/llms.txt
> Use this file to discover all available pages before exploring further.

# Audit log query reference

> Reference for querying Cognite Data Fusion (CDF) audit logs stored as a Delta Lake table, with Spark query examples, Delta maintenance, and compatibility with Athena, Synapse, and BigQuery.

CDF delivers audit logs as a [Delta Lake](https://delta.io/) table to the cloud storage you configured in [Setting up audit log streaming](/cdf/admin/audit/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](/cdf/admin/audit/about).

## 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](#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

Register the Delta table from your storage location before running any query. The following examples use Apache Spark.

```python theme={"languages":{"custom":["/_languages/kuiper.json","../_languages/kuiper.json"]}}
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

```python theme={"languages":{"custom":["/_languages/kuiper.json","../_languages/kuiper.json"]}}
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.

```python theme={"languages":{"custom":["/_languages/kuiper.json","../_languages/kuiper.json"]}}
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

```python theme={"languages":{"custom":["/_languages/kuiper.json","../_languages/kuiper.json"]}}
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

```python theme={"languages":{"custom":["/_languages/kuiper.json","../_languages/kuiper.json"]}}
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

```python theme={"languages":{"custom":["/_languages/kuiper.json","../_languages/kuiper.json"]}}
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.

```python theme={"languages":{"custom":["/_languages/kuiper.json","../_languages/kuiper.json"]}}
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

```python theme={"languages":{"custom":["/_languages/kuiper.json","../_languages/kuiper.json"]}}
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:

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

```sql theme={"languages":{"custom":["/_languages/kuiper.json","../_languages/kuiper.json"]}}
OPTIMIZE audit_logs;
VACUUM audit_logs RETAIN 7 DAYS;
```

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

### 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](https://docs.delta.io/delta-batch/#data-retention) for details.

**Logical deletion** uses a `DELETE` query:

```sql theme={"languages":{"custom":["/_languages/kuiper.json","../_languages/kuiper.json"]}}
-- Logically delete events older than 365 days
DELETE FROM audit_logs
WHERE timestamp < CURRENT_TIMESTAMP - INTERVAL '365 days';
```

**Physical deletion** uses `VACUUM`:

```sql theme={"languages":{"custom":["/_languages/kuiper.json","../_languages/kuiper.json"]}}
VACUUM audit_logs RETAIN 7 DAYS;
```

<Warning>
  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`.
</Warning>

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

```sql theme={"languages":{"custom":["/_languages/kuiper.json","../_languages/kuiper.json"]}}
-- 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.

## Further reading

* [About CDF audit logs](/cdf/admin/audit/about) — Schema and field definitions
* [Audit log event types reference](/cdf/admin/audit/event-types) — All `serviceName` and `eventType` values
* [Setting up audit log streaming](/cdf/admin/audit/streaming) — Configure delivery to your cloud storage