> ## 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](https://confidence.spotify.com/docs/reference/api/metrics/concepts.md#tables) in the Metrics Concepts page.

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

**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

**Daily**

Updates once per day at a scheduled time:

```json theme={null}
{
  "strategy": "DAILY",
  "dailyUpdateConfig": {}
}
```

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