# Datasets: schemas and identity

A dataset schema defines the columns, types, nullability, and identity of a
project table. The schema is fixed when the dataset is created; rows are
validated against it on every push.

## Column kinds

<table>
<thead>
  <tr>
    <th>
      Kind
    </th>
    
    <th>
      Stored as
    </th>
    
    <th>
      Accepted values
    </th>
  </tr>
</thead>

<tbody>
  <tr>
    <td>
      <code>
        COLUMN_KIND_TEXT
      </code>
    </td>
    
    <td>
      <code>
        TEXT
      </code>
    </td>
    
    <td>
      Any UTF-8 string.
    </td>
  </tr>
  
  <tr>
    <td>
      <code>
        COLUMN_KIND_INTEGER
      </code>
    </td>
    
    <td>
      <code>
        BIGINT
      </code>
    </td>
    
    <td>
      64-bit signed integer.
    </td>
  </tr>
  
  <tr>
    <td>
      <code>
        COLUMN_KIND_NUMBER
      </code>
    </td>
    
    <td>
      <code>
        DOUBLE
      </code>
    </td>
    
    <td>
      Double-precision number.
    </td>
  </tr>
  
  <tr>
    <td>
      <code>
        COLUMN_KIND_BOOLEAN
      </code>
    </td>
    
    <td>
      <code>
        BOOLEAN
      </code>
    </td>
    
    <td>
      <code>
        true
      </code>
      
       or <code>
        false
      </code>
      
      .
    </td>
  </tr>
  
  <tr>
    <td>
      <code>
        COLUMN_KIND_TIMESTAMP
      </code>
    </td>
    
    <td>
      <code>
        TIMESTAMP
      </code>
    </td>
    
    <td>
      Timestamp with microsecond precision.
    </td>
  </tr>
</tbody>
</table>

In SQL, these appear as `TEXT`, `BIGINT`, `DOUBLE`, `BOOLEAN`, and `TIMESTAMP`.
See [SQL reference](/datasets/sql#result-types) for how query result types are
mapped.

## Schema rules

<table>
<thead>
  <tr>
    <th>
      Rule
    </th>
    
    <th>
      Detail
    </th>
  </tr>
</thead>

<tbody>
  <tr>
    <td>
      Column names
    </td>
    
    <td>
      ASCII letters, digits, and underscores, starting with a letter or underscore. Quote names that are not lowercase in SQL.
    </td>
  </tr>
  
  <tr>
    <td>
      Uniqueness
    </td>
    
    <td>
      Column names are unique case-insensitively.
    </td>
  </tr>
  
  <tr>
    <td>
      Reserved names
    </td>
    
    <td>
      <code>
        __ingested_at
      </code>
      
       and <code>
        __deleted
      </code>
      
       are reserved.
    </td>
  </tr>
  
  <tr>
    <td>
      Column order
    </td>
    
    <td>
      Significant: ingest and result mapping use schema order.
    </td>
  </tr>
  
  <tr>
    <td>
      Nullable
    </td>
    
    <td>
      A <code>
        NULL
      </code>
      
       written to a non-nullable column fails the push.
    </td>
  </tr>
  
  <tr>
    <td>
      Column types
    </td>
    
    <td>
      Fixed after creation; the Hub dialog states this explicitly.
    </td>
  </tr>
  
  <tr>
    <td>
      Dataset names
    </td>
    
    <td>
      Start with a letter; use letters, digits, and dashes (for example <code>
        live-hosts
      </code>
      
      ). Dataset tables and saved views share one namespace per project, so names must be unique case-insensitively across both.
    </td>
  </tr>
  
  <tr>
    <td>
      Name length
    </td>
    
    <td>
      Up to 255 characters.
    </td>
  </tr>
  
  <tr>
    <td>
      Description
    </td>
    
    <td>
      Up to 1024 characters.
    </td>
  </tr>
</tbody>
</table>

Dataset names with dashes must be double-quoted in SQL:
`SELECT * FROM "live-hosts"`.

## Identity and reconciliation

Identity columns tell the dataset which observations describe the same logical
record. With identity configured, the current state keeps only the latest
observation per identity (by `__ingested_at`). Without identity columns, every
pushed row is a separate record.

Use the smallest stable key:

<table>
<thead>
  <tr>
    <th>
      Dataset
    </th>
    
    <th>
      Identity
    </th>
  </tr>
</thead>

<tbody>
  <tr>
    <td>
      Subdomains
    </td>
    
    <td>
      <code>
        host
      </code>
      
       (optionally <code>
        program
      </code>
      
       if the same host appears in several programs)
    </td>
  </tr>
  
  <tr>
    <td>
      Live hosts
    </td>
    
    <td>
      <code>
        url
      </code>
    </td>
  </tr>
  
  <tr>
    <td>
      Findings
    </td>
    
    <td>
      <code>
        fingerprint
      </code>
    </td>
  </tr>
  
  <tr>
    <td>
      DNS history
    </td>
    
    <td>
      usually no identity — every observation is meaningful
    </td>
  </tr>
</tbody>
</table>

Identity is part of the schema, not the push:

```json
{
  "columns": [
    { "name": "host", "kind": "COLUMN_KIND_TEXT", "nullable": false, "identity": true },
    { "name": "program", "kind": "COLUMN_KIND_TEXT", "nullable": false, "identity": true },
    { "name": "ip", "kind": "COLUMN_KIND_TEXT", "nullable": true },
    { "name": "resolved", "kind": "COLUMN_KIND_BOOLEAN", "nullable": true }
  ]
}
```

## Creating a dataset

The Hub's **New dataset** dialog covers the common case and creates datasets
**without identity columns**, which is correct for append-only history and for
tables where every row is distinct.

When you need reconciliation, create the dataset with the API or CLI and mark
the identity columns:

```bash
bh dataset create \
  --project-id "$PROJECT_ID" \
  subdomains \
  --description "Resolved subdomains per program" \
  --schema-json schema.json
```

`schema.json` uses the proto JSON shape:

```json
{
  "columns": [
    { "name": "host", "kind": "COLUMN_KIND_TEXT", "nullable": false, "identity": true },
    { "name": "program", "kind": "COLUMN_KIND_TEXT", "nullable": false, "identity": true },
    { "name": "ip", "kind": "COLUMN_KIND_TEXT", "nullable": true },
    { "name": "resolved", "kind": "COLUMN_KIND_BOOLEAN", "nullable": true }
  ]
}
```

The same shape is the `schema` field of `CreateDatasetRequest` — see
[API and CLI](/datasets/api-cli).

## Current state and stale rows

The service keeps every push as append-only observations and derives the
current state per identity. The relation also exposes two reserved columns:

<table>
<thead>
  <tr>
    <th>
      Column
    </th>
    
    <th>
      Meaning
    </th>
  </tr>
</thead>

<tbody>
  <tr>
    <td>
      <code>
        __ingested_at
      </code>
    </td>
    
    <td>
      When the observation was recorded.
    </td>
  </tr>
  
  <tr>
    <td>
      <code>
        __deleted
      </code>
    </td>
    
    <td>
      Whether the record is stale. Current-state queries exclude stale rows.
    </td>
  </tr>
</tbody>
</table>

`upsert` leaves identities that are absent from a push untouched. `sync`
declares the push authoritative, so identities missing from a full snapshot are
marked stale. `DeleteDatasetRows` accepts the same current-state semantics:
soft deletion writes `__deleted = true` tombstones and hard deletion rewrites
the stored files. See [Overview](/datasets/overview#current-state-and-history).

In SQL, you can inspect the history directly:

```sql
SELECT "host", "__ingested_at", "__deleted"
FROM dataset
```

## Changing a schema

Column names and types cannot be changed after creation, and the update API only
changes the dataset name and description. To change the shape:

1. Create a new dataset with the desired schema.
2. Backfill or re-push the rows.
3. Point workflows and views at the new dataset name.
4. Delete the old dataset when you no longer need its history.

Deleting a dataset removes its stored files. Views that referenced it fail on
their next execution and are marked invalid.

## Limits

<table>
<thead>
  <tr>
    <th>
      Limit
    </th>
    
    <th>
      Value
    </th>
  </tr>
</thead>

<tbody>
  <tr>
    <td>
      Datasets per project
    </td>
    
    <td>
      100
    </td>
  </tr>
  
  <tr>
    <td>
      Columns per dataset
    </td>
    
    <td>
      No fixed server limit; keep schemas narrow for query speed
    </td>
  </tr>
  
  <tr>
    <td>
      Column name length
    </td>
    
    <td>
      255 characters in the Hub UI
    </td>
  </tr>
  
  <tr>
    <td>
      Description length
    </td>
    
    <td>
      1024 characters
    </td>
  </tr>
</tbody>
</table>

## Next steps

- [Pushing dataset rows](/datasets/workflows): push rows with `step.update_dataset`.
- [SQL reference](/datasets/sql): query the schema you just designed.
- [Structured filters](/datasets/filters): type-aware filtering without SQL.
- [Saved views](/datasets/views): reusable queries across datasets.
- [API and CLI](/datasets/api-cli): create and inspect datasets programmatically.