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

# Tables Reference

> Technical reference for tables in Confidence Metrics.

This section provides technical specifications and reference information for assignment tables, fact tables, and dimension tables.

<Tip>
  For conceptual explanations of tables, see [Tables](./concepts#tables) in the Metrics Concepts page.
</Tip>

## Column Types

All tables use these column type specifications:

| Type                    | Description       | Example Use                            |
| :---------------------- | :---------------- | :------------------------------------- |
| `COLUMN_TYPE_STRING`    | Text values       | User IDs, country codes, product names |
| `COLUMN_TYPE_INTEGER`   | Whole numbers     | Count values, age, quantity            |
| `COLUMN_TYPE_DOUBLE`    | Decimal numbers   | Revenue, ratings, percentages          |
| `COLUMN_TYPE_BOOLEAN`   | True/false values | Conversion flags, feature enabled      |
| `COLUMN_TYPE_TIMESTAMP` | Date and time     | Event timestamps, created dates        |
| `COLUMN_TYPE_DATE`      | Date only         | Birth dates, enrollment dates          |

## Assignment Tables

### Required Fields

| Field                 | Type          | Description                       |
| :-------------------- | :------------ | :-------------------------------- |
| `timestampColumn`     | Column        | When the assignment occurred      |
| `entityColumnMapping` | ColumnMapping | Which entity was assigned         |
| `exposureKeyColumn`   | Column        | Identifies the experiment/feature |
| `variantKeyColumn`    | Column        | Which variant was assigned        |

### Data Delivery Strategies

<Tabs>
  <Tab title="Automatic">
    Continuously updates data based on incremental checks:

    ```json theme={null}
    {
      "strategy": "AUTOMATIC",
      "automaticUpdateConfig": {
        "incrementDuration": "P13H",
        "commitDelay": "P13H"
      }
    }
    ```

    * **incrementDuration**: How frequently to check for new data (ISO 8601 duration)
    * **commitDelay**: Buffer time before considering data complete
  </Tab>

  <Tab title="Daily">
    Updates once per day at a scheduled time:

    ```json theme={null}
    {
      "strategy": "DAILY",
      "dailyUpdateConfig": {}
    }
    ```
  </Tab>
</Tabs>

## Fact Tables

### Required Fields

| Field             | Type             | Description                                 |
| :---------------- | :--------------- | :------------------------------------------ |
| `name`            | string           | Unique identifier for the table             |
| `displayName`     | string           | Human-readable name                         |
| `sql`             | string           | SQL query selecting fact rows               |
| `timestampColumn` | Column           | When the measurement occurred               |
| `entities`        | ColumnMapping\[] | Entity columns (at least one required)      |
| `measurements`    | Column\[]        | Measurement columns (at least one required) |

### Column Specifications

Each column requires:

* `name`: Column name in query results
* `type`: Column type (from table above)
* `repeated`: Boolean indicating if column contains arrays

### Table States

| State      | Description                             |
| :--------- | :-------------------------------------- |
| `CREATING` | Initial validation in progress          |
| `ACTIVE`   | Ready for use in metrics                |
| `FAILED`   | Validation failed, check error messages |

## Dimension Tables

### Required Fields

| Field                 | Type             | Description                               |
| :-------------------- | :--------------- | :---------------------------------------- |
| `displayName`         | string           | Human-readable name                       |
| `sql`                 | string           | SQL query selecting dimension rows        |
| `timestampColumn`     | Column           | When the dimension value applies          |
| `entityColumnMapping` | ColumnMapping\[] | Entity columns (at least one required)    |
| `dimensions`          | Column\[]        | Dimension columns (at least one required) |

### Dimension Column Types

Dimensions typically use:

* **STRING**: Categorical values (country, platform, tier)
* **BOOLEAN**: Binary attributes (`is_premium`, `is_active`)
* **INTEGER**: Numeric categories (`age_group_code`, `tier_level`)

### Table States

Same as fact tables: `CREATING`, `ACTIVE`, `FAILED`

## Column Mapping Structure

Column mappings connect query results to entities:

```json theme={null}
{
  "column": {
    "name": "user_id",
    "type": "COLUMN_TYPE_STRING",
    "repeated": false
  },
  "entity": "entities/user"
}
```

## Best Practices

### SQL Queries

* **Use SELECT \* sparingly**: Explicitly specify columns for better performance
* **Add WHERE clauses**: Filter data at query time to reduce processing
* **Use partitioning**: Leverage data warehouse partitioning for efficiency
* **Test queries**: Validate SQL in your data warehouse before creating tables

### Data Quality

* **Validate timestamps**: Ensure timestamp columns are in the correct format
* **Handle NULLs**: Decide how to handle NULL values in measurements
* **Check data types**: Verify column types match your schema expectations
* **Monitor failures**: Review failed tables and fix SQL or schema issues