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

# Derived Columns

> Add columns to an External Table that Pinot computes from the Parquet source columns, such as a day bucket from a timestamp. Covers how to declare them, when they're computed, what you can index, and the validation errors you can hit.

> [!WARNING]
>
> This feature requires StarTree Cloud 0.18.0 or later. Earlier releases reject derived columns on External Tables.

A derived column is a column that isn't in the source Parquet files. Pinot computes it from columns that are, using a transform function such as `dateTrunc`, `upper` or `times`. A common use is folding a timestamp into a coarser bucket you can group by, or rebuilding a single value from several partition columns.

Once it exists, a derived column behaves like any other Pinot column. You can filter on it, group by it, and put indexes on it.

## How it works

An External Table normally reads only the Parquet footers when it onboards a file. Derived columns keep it that way.

1. **Onboarding.** The segment built for each file leaves the derived columns out, so onboarding still reads only the footer. Adding derived columns doesn't make onboarding slower.
2. **Segment load.** When a server loads the segment, it sees the derived column as missing, reads the source columns it needs from the Parquet file, and computes the values.
3. **Query.** The result is an ordinary Pinot column. It can be dictionary-encoded, indexed and pruned.

The cost moves to segment load. Each derived column reads its source columns from object storage when a server loads the segment, so a table with many derived columns over wide source columns takes longer to come online.

## Declare a derived column

You need two things:

* a `transformConfigs` entry under `ingestionConfig` in the table config, with the column name and the transform;
* a field for the column in the schema, with its data type.

For example, to add a day bucket over a millisecond timestamp:

```json theme={null}
"ingestionConfig": {
  "transformConfigs": [
    {
      "columnName": "event_day",
      "transformFunction": "dateTrunc('DAY', event_ts, 'MILLISECONDS')"
    }
  ]
}
```

And in the schema:

```json theme={null}
"dimensionFieldSpecs": [
  { "name": "event_day", "dataType": "LONG" }
]
```

Declare derived columns only in `ingestionConfig.transformConfigs`. A `transformFunction` set on the schema field itself is rejected, because the External Table load path never reads it.

### Encoding and indexes

Physical columns on an External Table must be `RAW`. A derived column can be `RAW` or `DICTIONARY`, because Pinot builds it itself instead of reading it from Parquet. Add a `fieldConfig` for it as you would on a regular table, and put the [indexes](https://docs.startree.ai/corecapabilities/external-table/indexes.md) you need on it.

## Rules

Table creation and every sync check the following. A table that breaks one fails at create time, with all the errors listed together, not later at segment load.

| Rule | Why |
| - | - |
| The column has a field in the schema. | Pinot needs its data type. |
| `columnName` and `transformFunction` aren't blank, and each column has only one entry. | |
| Every column the transform reads exists, and the transform doesn't read its own output. | A typo would otherwise produce default values silently. |
| The transform parses. | Same reason. |
| The column isn't the table's time column. | Keep the Parquet timestamp as the time column and derive coarser buckets as ordinary columns. |
| The transform doesn't read a column whose forward index is disabled. | The server has nothing to read the source values from. |
| Derived columns don't read each other in a cycle. | |
| No `transformFunction` on the schema field. | Use `transformConfigs` instead. The error message says so. |

## Change or remove a derived column

The values are computed when a segment loads, so a change to the transform reaches a segment the next time it loads. Reload the table's segments to apply the change everywhere.

To remove a derived column, drop its `transformConfigs` entry and its schema field, then reload.

## Limits

* A derived column can't be the table's time column.
* A derived column can't drive Iceberg [partition pruning](https://docs.startree.ai/corecapabilities/external-table/partition-pruning.md). The importer skips columns that are the output of an ingestion transform.
* Segments are only as fast to load as their source reads. A derived column over a wide or high-cardinality source column adds that column's read to every segment load.
* The server that computes the column must run 0.18.0 or later. Upgrade servers before you add derived columns.

## Common errors

| Error or symptom | Cause | Fix |
| - | - | - |
| Table creation fails with a message about `transformFunction in the schema` that points at `ingestionConfig.transformConfigs` | The transform is set on the schema field. | Move it to `ingestionConfig.transformConfigs` and remove it from the schema. |
| Table creation fails naming a column with no field spec | The derived column isn't in the schema. | Add a schema field with the right data type. |
| Table creation fails naming an unknown column in the transform | The transform reads a column that doesn't exist, often a typo or a column that isn't in the Parquet files. | Fix the column name. Column names are case-sensitive. |
| Derived column is all default values | The column was added on a server older than 0.18.0, or the segment hasn't loaded since you added it. | Upgrade the server, then reload the segments. |
| Tables take longer to come online after you add derived columns | Each derived column reads its source columns at load. | Expected. Drop derived columns you don't query, or derive from narrower columns. |


This documentation is built and hosted on [Mintlify](https://mintlify.com), a developer documentation platform.