---
name: autodl
description: Use when querying auto-collected CI data from test runs in BigQuery (ci_data_autodl dataset) including risk analysis, disruption, CPU metrics, audit logs, operator state, and retry statistics
---

# Autodl Tables

Query data automatically collected during OpenShift CI test runs, stored in `openshift-ci-data-analysis.ci_data_autodl`. This data is generated by monitor tests and analysis code in `openshift/origin` and uploaded by the ci-data-loader after each job run.

Follow the `foundations` skill for cost safety, caching, and execution workflow.

## When to Use This Skill

- Investigating risk analysis verdicts for job runs
- Analyzing test retry behavior and flake patterns
- Examining kube-apiserver audit log patterns (latency, request counts, watch storms)
- Tracking operator state transitions during test runs
- Correlating CPU usage with test failures
- Understanding DNS disruption during tests
- Checking cluster instance types and configuration

## Common Columns

All autodl tables include three columns added by the ci-data-loader:

| Column | Type | Notes |
|--------|------|-------|
| JobRunName | STRING | Prow job run identifier — join key to other datasets |
| PartitionTime | TIMESTAMP | **Partition column** — always filter on this |
| Source | STRING | Data source identifier |

The `JobRunName` can be used to correlate autodl data with job runs in `openshift-gce-devel.ci_analysis_us.jobs` (match against `prowjob_build_id` or extract from `prowjob_url`).

## Tables

### Risk Analysis

#### `risk_analysis_overall_results`
Overall risk analysis verdict for a job run — the aggregate risk level across all tests.

| Column | Type | Notes |
|--------|------|-------|
| RiskLevel | INTEGER | Numeric risk level |
| RiskName | STRING | Human-readable risk name |
| JobRunTestCount | INTEGER | Total tests in the run |
| JobRunTestFailures | INTEGER | Total test failures |
| NeverStableJob | STRING | Whether this job has ever been stable |
| HistoricalRunTestCount | INTEGER | Historical test count for comparison |

**Use case**: Find job runs with high risk levels, correlate risk verdicts with actual job outcomes.

#### `risk_analysis_test_results`
Per-test risk analysis — the risk level assigned to each individual test based on historical pass rates.

| Column | Type | Notes |
|--------|------|-------|
| TestName | STRING | Full test name |
| TestID | INTEGER | Stable test identifier |
| RiskLevel | INTEGER | Numeric risk level |
| RiskName | STRING | Human-readable risk name |
| CurrentRuns | INTEGER | Recent run count for this test |
| CurrentPasses | INTEGER | Recent pass count |
| CurrentPassPercentage | FLOAT | Recent pass rate |

**Use case**: Identify which tests contributed most to a run's risk assessment, find tests with declining pass rates.

#### `risk_analysis_api_requests`
Metadata about HTTP requests to the Sippy risk analysis API during test runs.

| Column | Type | Notes |
|--------|------|-------|
| RequestCount | INTEGER | Number of API requests made |
| StartTime | TIMESTAMP | When the request started |
| DurationSeconds | FLOAT | Request duration |
| Error | STRING | Error message if request failed |
| BytesRead | INTEGER | Response size |

**Use case**: Debug risk analysis API performance issues or failures.

### Test Execution

#### `retry_statistics`
Per-test retry statistics when tests are retried during a run.

| Column | Type | Notes |
|--------|------|-------|
| TestName | STRING | Full test name |
| RetryStrategy | STRING | Strategy used for retries |
| TotalAttempts | INTEGER | Total attempts made |
| SuccessfulAttempts | INTEGER | Passing attempts |
| FailedAttempts | INTEGER | Failing attempts |
| FinalOutcome | STRING | Final test result |
| TotalDurationMilliseconds | INTEGER | Total time across all attempts |
| MaxRetriesAllowed | INTEGER | Retry limit |
| FirstAttemptDurationMilliseconds | INTEGER | Duration of first attempt |
| AverageAttemptDurationMilliseconds | INTEGER | Average attempt duration |
| JobName | STRING | Prow job name |
| JobType | STRING | periodic, presubmit, postsubmit |
| PullNumber | STRING | PR number (presubmits) |
| RepoName | STRING | GitHub repo |
| RepoOwner | STRING | GitHub org |
| PullSha | STRING | Commit SHA |
| ReleaseImageLatest | STRING | Target release image |
| ReleaseImageInitial | STRING | Initial release image (upgrades) |

**Use case**: Analyze retry effectiveness, find tests that always fail on first attempt but pass on retry, measure retry overhead.

#### `run_suite_options`
Configuration used to run the test suite.

| Column | Type | Notes |
|--------|------|-------|
| ClusterStability | STRING | Cluster stability mode |
| RandomSeed | INTEGER | Random seed for test ordering |
| WorkerNodes | INTEGER | Number of worker nodes |
| TotalNodes | INTEGER | Total cluster nodes |
| Parallelism | INTEGER | Test parallelism level |

**Use case**: Correlate test failures with cluster size or parallelism settings.

#### `high_cpu_e2e_tests`
Tests that overlapped with high CPU usage intervals.

| Column | Type | Notes |
|--------|------|-------|
| TestName | STRING | Full test name |
| Success | INTEGER | 1 = pass, 0 = fail |

**Use case**: Find tests that fail due to high CPU or cause high CPU on the cluster.

#### `duration-metrics`
Named duration metrics (install time, upgrade time, etc.).

| Column | Type | Notes |
|--------|------|-------|
| name | STRING | Metric name (e.g. "install", "upgrade") |
| duration | INTEGER | Duration in milliseconds |

**Use case**: Track install/upgrade duration trends across releases and platforms.

### Kube-APIServer Audit Analysis

#### `audit_latency_counts`
Histogram-bucketed latency counts for kube-apiserver requests, from audit logs.

| Column | Type | Notes |
|--------|------|-------|
| LatencyType | STRING | Type of latency measurement |
| Resource | STRING | API resource |
| Verb | STRING | HTTP verb |
| Bucket | FLOAT | Latency bucket threshold |
| Count | INTEGER | Requests exceeding this bucket |

**Use case**: Identify API resources with high latency, find slow verbs, detect apiserver performance regressions.

#### `audit_resource_requests_per_user`
API request counts per user/service-account, from audit logs.

| Column | Type | Notes |
|--------|------|-------|
| User | STRING | User or service account (cleaned) |
| Resource | STRING | API resource |
| Verb | STRING | HTTP verb |
| HttpStatus | INTEGER | Response status code |
| RequestCount | INTEGER | Number of requests |

**Use case**: Find noisy controllers, identify unexpected API callers, detect request storms.

#### `operator_watch_requests`
Watch request counts per operator against the kube-apiserver, from audit logs.

| Column | Type | Notes |
|--------|------|-------|
| ControlPlaneTopology | STRING | e.g. "HighlyAvailable", "SingleReplica" |
| PlatformType | STRING | Cloud platform |
| Operator | STRING | Operator name |
| WatchRequestCount | INTEGER | Number of watch requests |

**Use case**: Detect watch storms, find operators with excessive watch counts, compare across topologies.

### Cluster Health

#### `operator_state_metrics`
ClusterOperator state transitions (Available, Progressing, Degraded) during the test run.

| Column | Type | Notes |
|--------|------|-------|
| Operator | STRING | ClusterOperator name |
| State | STRING | "Available", "Progressing", "Degraded" |
| Count | INTEGER | Number of transitions |
| TotalSeconds | FLOAT | Total time in this state |
| MaxIndividualDurationSeconds | FLOAT | Longest single period in this state |

**Use case**: Find operators that flap between states, track degraded duration across releases.

#### `dns_disruption_stats`
DNS disruption summary during the test run.

| Column | Type | Notes |
|--------|------|-------|
| IntervalCount | INTEGER | Number of disruption intervals |
| TotalDurationSeconds | INTEGER | Total disruption time |

**Use case**: Track DNS disruption trends, correlate with network configuration variants.

#### `node_cpu_usage_timeline`
Time-series per-node CPU usage sampled during the test run.

| Column | Type | Notes |
|--------|------|-------|
| Timestamp | TIMESTAMP | Sample time |
| NodeName | STRING | Node name |
| NodeRole | STRING | master, worker, etc. |
| CPUUsage | FLOAT | CPU usage percentage |

**Use case**: Correlate CPU spikes with test failures, identify resource-starved nodes.

#### `interval_duration_sum`
Total duration of monitor intervals by source type.

| Column | Type | Notes |
|--------|------|-------|
| IntervalSource | STRING | "MetricsEndpointDown", "CPUMonitor" |
| TotalDurationSeconds | INTEGER | Total duration |

**Use case**: Track metrics endpoint availability, CPU monitoring coverage.

#### `cluster_instance_types`
Cloud instance types used by nodes in the cluster (AWS, Azure, GCP).

| Column | Type | Notes |
|--------|------|-------|
| Platform | STRING | Cloud provider |
| Region | STRING | Cloud region |
| Zone | STRING | Availability zone |
| Role | STRING | Node role |
| InstanceType | STRING | Instance type (e.g. m5.xlarge) |
| Suite | STRING | Test suite |

**Use case**: Correlate failures with instance types, track what hardware CI uses.

## Query Examples

### Find high-risk job runs in the last week

```sql
SELECT
  JobRunName,
  RiskLevel,
  RiskName,
  JobRunTestCount,
  JobRunTestFailures
FROM `openshift-ci-data-analysis.ci_data_autodl.risk_analysis_overall_results`
WHERE PartitionTime >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 7 DAY)
  AND RiskLevel >= 3
ORDER BY RiskLevel DESC, JobRunTestFailures DESC
```

### Find tests with worst retry rates

```sql
SELECT
  TestName,
  COUNT(*) AS total_runs,
  COUNTIF(TotalAttempts > 1) AS runs_with_retries,
  ROUND(COUNTIF(TotalAttempts > 1) * 100.0 / COUNT(*), 1) AS retry_pct,
  ROUND(AVG(IF(TotalAttempts > 1, TotalAttempts, NULL)), 1) AS avg_attempts_when_retried,
  COUNTIF(FinalOutcome = 'passed') / COUNT(*) AS final_pass_rate
FROM `openshift-ci-data-analysis.ci_data_autodl.retry_statistics`
WHERE PartitionTime >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 7 DAY)
GROUP BY TestName
HAVING total_runs >= 5
ORDER BY retry_pct DESC
```

### Find operators with most degraded time

```sql
SELECT
  Operator,
  COUNT(*) AS run_count,
  AVG(TotalSeconds) AS avg_degraded_seconds,
  MAX(MaxIndividualDurationSeconds) AS worst_degraded_seconds
FROM `openshift-ci-data-analysis.ci_data_autodl.operator_state_metrics`
WHERE PartitionTime >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 7 DAY)
  AND State = 'Degraded'
  AND Count > 0
GROUP BY Operator
ORDER BY avg_degraded_seconds DESC
```

### Find noisiest API callers

```sql
SELECT
  User,
  Resource,
  Verb,
  SUM(RequestCount) AS total_requests
FROM `openshift-ci-data-analysis.ci_data_autodl.audit_resource_requests_per_user`
WHERE PartitionTime >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 7 DAY)
GROUP BY User, Resource, Verb
ORDER BY total_requests DESC
LIMIT 20
```

## Data Pipeline

The autodl data pipeline works as follows:

1. Monitor tests in `openshift/origin` collect data during test execution
2. Data is written as `*autodl.json` files in job artifacts
3. The ci-data-loader picks up these files and uploads to BigQuery
4. New columns in schemas are auto-added to existing tables; removed columns are never deleted (cross-release integrity)

Source code for all table definitions is in `openshift/origin`:
- Data loader framework: `pkg/dataloader/types.go`
- Individual monitor tests: `pkg/monitortests/` subdirectories
- Risk analysis: `pkg/riskanalysis/cmd.go`
- Retry statistics: `pkg/test/ginkgo/retries.go`
- Suite options: `pkg/test/ginkgo/cmd_runsuite.go`
- Duration metrics: `pkg/e2eanalysis/e2e_analysis.go`