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.
This section provides technical specifications and reference information for assignment tables, fact tables, and dimension tables.
For conceptual explanations of tables, see 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
Continuously updates data based on incremental checks:{
"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
Updates once per day at a scheduled time:{
"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 resultstype: 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:
{
"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
