> ## 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. # Create FactTable > Create a new fact table. A fact table is a description of a dataset, specifically, it describes which columns correspond to entities, dimensions and measurements. It is the basis for describing metrics. ## OpenAPI ````yaml /api/metrics/api/fact-table.openapi.json post /v1/factTables openapi: 3.1.0 info: title: Confidence Fact-table API version: 1.0.0 description: API documentation for fact-table servers: [] security: [] paths: /v1/factTables: post: summary: Create FactTable description: |- Create a new fact table. A fact table is a description of a dataset, specifically, it describes which columns correspond to entities, dimensions and measurements. It is the basis for describing metrics. operationId: FactTable.create parameters: - name: factTableId in: query required: false description: >- The id to the give the created fact table. If a not set a random one will be generated. schema: type: string requestBody: content: application/json: schema: type: object properties: name: type: string description: |- Unique identifier of the fact table. For example: `factTables/0smva5nxuhv4yts6paxt` sql: type: string description: >- A data warehouse-specific query to get the data of the fact table. displayName: type: string description: Human friendly name of the fact table. description: type: string description: Human readable description of the fact table. timestampColumn: type: object properties: name: type: string description: Name of the column. type: type: string enum: - COLUMN_TYPE_UNSPECIFIED - COLUMN_TYPE_STRING - COLUMN_TYPE_INT32 - COLUMN_TYPE_INT64 - COLUMN_TYPE_DOUBLE - COLUMN_TYPE_BOOLEAN - COLUMN_TYPE_DATE - COLUMN_TYPE_TIMESTAMP - COLUMN_TYPE_BYTES - COLUMN_TYPE_TIMESTAMP_WITHOUT_TIMEZONE - COLUMN_TYPE_DECIMAL - COLUMN_TYPE_HLL_SKETCH description: >- Type of the column, will be converted to a data warehouse-specific type. repeated: type: boolean description: Determined whether the column is an array. required: - name description: >- The column of the fact table that represents the time the event occurred. entities: type: array items: type: object properties: column: type: object properties: name: type: string description: Name of the column. type: type: string enum: - COLUMN_TYPE_UNSPECIFIED - COLUMN_TYPE_STRING - COLUMN_TYPE_INT32 - COLUMN_TYPE_INT64 - COLUMN_TYPE_DOUBLE - COLUMN_TYPE_BOOLEAN - COLUMN_TYPE_DATE - COLUMN_TYPE_TIMESTAMP - COLUMN_TYPE_BYTES - COLUMN_TYPE_TIMESTAMP_WITHOUT_TIMEZONE - COLUMN_TYPE_DECIMAL - COLUMN_TYPE_HLL_SKETCH description: >- Type of the column, will be converted to a data warehouse-specific type. repeated: type: boolean description: Determined whether the column is an array. required: - name description: The column that holds the identifier of the entity. entity: type: string description: The resource name of the entity. required: - column - entity description: >- Mapping between columns and the entities involved in the event. measures: type: array items: type: object properties: column: type: object properties: name: type: string description: Name of the column. type: type: string enum: - COLUMN_TYPE_UNSPECIFIED - COLUMN_TYPE_STRING - COLUMN_TYPE_INT32 - COLUMN_TYPE_INT64 - COLUMN_TYPE_DOUBLE - COLUMN_TYPE_BOOLEAN - COLUMN_TYPE_DATE - COLUMN_TYPE_TIMESTAMP - COLUMN_TYPE_BYTES - COLUMN_TYPE_TIMESTAMP_WITHOUT_TIMEZONE - COLUMN_TYPE_DECIMAL - COLUMN_TYPE_HLL_SKETCH description: >- Type of the column, will be converted to a data warehouse-specific type. repeated: type: boolean description: Determined whether the column is an array. required: - name description: >- The column that holds the measurements that belong to the measure. displayName: type: string description: Human friendly name of the measure. unit: type: object properties: baseUnitMultiplier: type: number description: >- The multiplier to convert the unit to the base unit. For example, if the underlying data is in hours, the multiplier would be 1/3600. baseUnit: type: string enum: - BASE_UNIT_UNSPECIFIED - NONE - PERCENT - SECOND - BYTE - BIT description: A standardized base unit. currencyCode: type: string description: >- The ISO 4217 code for the currency. For example 'USD'. customUnit: type: string description: A custom unit. For example 'Click'. entity: type: string description: The unit is an entity like 'User'. factTable: type: string description: The unit is a fact event like 'Conversion'. description: The unit of measure. declaredType: type: string enum: - COLUMN_TYPE_UNSPECIFIED - COLUMN_TYPE_STRING - COLUMN_TYPE_INT32 - COLUMN_TYPE_INT64 - COLUMN_TYPE_DOUBLE - COLUMN_TYPE_BOOLEAN - COLUMN_TYPE_DATE - COLUMN_TYPE_TIMESTAMP - COLUMN_TYPE_BYTES - COLUMN_TYPE_TIMESTAMP_WITHOUT_TIMEZONE - COLUMN_TYPE_DECIMAL - COLUMN_TYPE_HLL_SKETCH description: >- User-declared type when the warehouse type needs interpretation. For example, set to COLUMN_TYPE_HLL_SKETCH when the warehouse returns BYTES but the column contains HLL sketches. required: - column description: >- Mapping between columns of the underlying table and measures. dimensions: type: array items: type: object properties: name: type: string description: Name of the column. type: type: string enum: - COLUMN_TYPE_UNSPECIFIED - COLUMN_TYPE_STRING - COLUMN_TYPE_INT32 - COLUMN_TYPE_INT64 - COLUMN_TYPE_DOUBLE - COLUMN_TYPE_BOOLEAN - COLUMN_TYPE_DATE - COLUMN_TYPE_TIMESTAMP - COLUMN_TYPE_BYTES - COLUMN_TYPE_TIMESTAMP_WITHOUT_TIMEZONE - COLUMN_TYPE_DECIMAL - COLUMN_TYPE_HLL_SKETCH description: >- Type of the column, will be converted to a data warehouse-specific type. repeated: type: boolean description: Determined whether the column is an array. required: - name description: Mapping between columns and dimensions of the entities. factDataDeliveredUntilTime: type: object description: >- The current maximum time point where there is available data for the fact table. dataDeliveredUntilUpdateStrategyConfig: type: object properties: disableEmptyPartitions: type: boolean description: If set to true, empty partitions are not allowed. strategy: type: string enum: - STRATEGY_UNSPECIFIED - MANUAL - AUTOMATIC description: The strategy to use automaticUpdateConfig: type: object properties: incrementDuration: type: object description: The size of the window to increment with commitDelay: type: object description: >- How long the window must be consistent before committing changeThreshold: type: object properties: value: type: string description: |- The decimal value, as a string. The string representation consists of an optional sign, `+` (`U+002B`) or `-` (`U+002D`), followed by a sequence of zero or more decimal digits ("the integer"), optionally followed by a fraction, optionally followed by an exponent. The fraction consists of a decimal point followed by zero or more decimal digits. The string must contain at least one digit in either the integer or the fraction. The number formed by the sign, the integer and the fraction is referred to as the significand. The exponent consists of the character `e` (`U+0065`) or `E` (`U+0045`) followed by one or more decimal digits. Services **should** normalize decimal values before storing them by: - Removing an explicitly-provided `+` sign (`+2.5` -{">"} `2.5`). - Replacing a zero-length integer value with `0` (`.5` -{">"} `0.5`). - Coercing the exponent character to lower-case (`2.5E8` -{">"} `2.5e8`). - Removing an explicitly-provided zero exponent (`2.5e0` -{">"} `2.5`). Services **may** perform additional normalization based on its own needs and the internal decimal implementation selected, such as shifting the decimal point and exponent value together (example: `2.5e-1` {"<"}-{">"} `0.25`). Additionally, services **may** preserve trailing zeroes in the fraction to indicate increased precision, but are not required to do so. Note that only the `.` character is supported to divide the integer and the fraction; `,` **should not** be supported regardless of locale. Additionally, thousand separators **should not** be supported. If a service does support them, values **must** be normalized. The ENBF grammar is: DecimalString = [Sign] Significand [Exponent]; Sign = '+' | '-'; Significand = Digits ['.'] [Digits] | [Digits] '.' Digits; Exponent = ('e' | 'E') [Sign] Digits; Digits = \{ '0' | '1' | '2' | '3' | '4' | '5' | '6' | '7' | '8' | '9' }; Services **should** clearly document the range of supported values, the maximum supported precision (total number of digits), and, if applicable, the scale (number of digits after the decimal point), as well as how it behaves when receiving out-of-bounds values. Services **may** choose to accept values passed as input even when the value has a higher precision or scale than the service supports, and **should** round the value to fit the supported scale. Alternatively, the service **may** error with `400 Bad Request` (`INVALID_ARGUMENT` in gRPC) if precision would be lost. Services **should** error with `400 Bad Request` (`INVALID_ARGUMENT` in gRPC) if the service receives a value outside of the supported range. description: >- The relative threshold for the window to the same. If set to 0.01 it means that if (new_window - old_window) / new_window {"<"} 0.01 the window will be considered the same. required: - commitDelay description: Automatic window increments dailyUpdateConfig: type: object properties: maxZeroDuration: type: object description: >- Maximum amount of time to wait for a new partition when its row count is 0, the partition will be considered empty and Confidence will move on to the next one. Default is 24 hours. description: >- Specifies that the data is written daily. Confidence will detect that data has been written hourlyUpdateConfig: type: object properties: maxZeroDuration: type: object description: >- Maximum amount of time to wait for a new partition when its row count is 0, the partition will be considered empty and Confidence will move on to the next one. Default is 6 hours. description: >- Specifies that the data is written hourly. Confidence will detect that data has been written required: - strategy description: >- The strategy for updating `fact_data_delivered_until_time` in the fact table. labels: type: array items: type: object additionalProperties: type: string description: Custom labels for this fact table. owner: type: string description: >- The owner of the resource. If not set will default to the creator. skipSqlPreview: type: boolean description: >- If set to true, skips the SQL preview query (and goes directly into TABLE_STATE_ACTIVE). When true, all columns must have their types explicitly specified. required: - sql - displayName - timestampColumn - entities required: true responses: '200': description: Returns a [FactTable](/api-reference/schemas/facttable). content: application/json: schema: $ref: '#/components/schemas/confidence.metrics.v1.FactTable' security: - admin: [] servers: - url: https://metrics.eu.confidence.dev - url: https://metrics.us.confidence.dev - url: https://metrics.confidence.dev components: schemas: confidence.metrics.v1.FactTable: title: FactTable type: object description: >- A set of events from some business process, for example, a sale occurred. properties: name: type: string description: |- Unique identifier of the fact table. For example: `factTables/0smva5nxuhv4yts6paxt` sql: type: string description: A data warehouse-specific query to get the data of the fact table. displayName: type: string description: Human friendly name of the fact table. description: type: string description: Human readable description of the fact table. timestampColumn: type: object properties: name: type: string description: Name of the column. type: type: string enum: - COLUMN_TYPE_UNSPECIFIED - COLUMN_TYPE_STRING - COLUMN_TYPE_INT32 - COLUMN_TYPE_INT64 - COLUMN_TYPE_DOUBLE - COLUMN_TYPE_BOOLEAN - COLUMN_TYPE_DATE - COLUMN_TYPE_TIMESTAMP - COLUMN_TYPE_BYTES - COLUMN_TYPE_TIMESTAMP_WITHOUT_TIMEZONE - COLUMN_TYPE_DECIMAL - COLUMN_TYPE_HLL_SKETCH description: >- Type of the column, will be converted to a data warehouse-specific type. repeated: type: boolean description: Determined whether the column is an array. required: - name description: >- The column of the fact table that represents the time the event occurred. entities: type: array items: type: object properties: column: type: object properties: name: type: string description: Name of the column. type: type: string enum: - COLUMN_TYPE_UNSPECIFIED - COLUMN_TYPE_STRING - COLUMN_TYPE_INT32 - COLUMN_TYPE_INT64 - COLUMN_TYPE_DOUBLE - COLUMN_TYPE_BOOLEAN - COLUMN_TYPE_DATE - COLUMN_TYPE_TIMESTAMP - COLUMN_TYPE_BYTES - COLUMN_TYPE_TIMESTAMP_WITHOUT_TIMEZONE - COLUMN_TYPE_DECIMAL - COLUMN_TYPE_HLL_SKETCH description: >- Type of the column, will be converted to a data warehouse-specific type. repeated: type: boolean description: Determined whether the column is an array. required: - name description: The column that holds the identifier of the entity. entity: type: string description: The resource name of the entity. required: - column - entity description: Mapping between columns and the entities involved in the event. measures: type: array items: type: object properties: column: type: object properties: name: type: string description: Name of the column. type: type: string enum: - COLUMN_TYPE_UNSPECIFIED - COLUMN_TYPE_STRING - COLUMN_TYPE_INT32 - COLUMN_TYPE_INT64 - COLUMN_TYPE_DOUBLE - COLUMN_TYPE_BOOLEAN - COLUMN_TYPE_DATE - COLUMN_TYPE_TIMESTAMP - COLUMN_TYPE_BYTES - COLUMN_TYPE_TIMESTAMP_WITHOUT_TIMEZONE - COLUMN_TYPE_DECIMAL - COLUMN_TYPE_HLL_SKETCH description: >- Type of the column, will be converted to a data warehouse-specific type. repeated: type: boolean description: Determined whether the column is an array. required: - name description: >- The column that holds the measurements that belong to the measure. displayName: type: string description: Human friendly name of the measure. unit: type: object properties: baseUnitMultiplier: type: number description: >- The multiplier to convert the unit to the base unit. For example, if the underlying data is in hours, the multiplier would be 1/3600. baseUnit: type: string enum: - BASE_UNIT_UNSPECIFIED - NONE - PERCENT - SECOND - BYTE - BIT description: A standardized base unit. currencyCode: type: string description: The ISO 4217 code for the currency. For example 'USD'. customUnit: type: string description: A custom unit. For example 'Click'. entity: type: string description: The unit is an entity like 'User'. factTable: type: string description: The unit is a fact event like 'Conversion'. description: The unit of measure. declaredType: type: string enum: - COLUMN_TYPE_UNSPECIFIED - COLUMN_TYPE_STRING - COLUMN_TYPE_INT32 - COLUMN_TYPE_INT64 - COLUMN_TYPE_DOUBLE - COLUMN_TYPE_BOOLEAN - COLUMN_TYPE_DATE - COLUMN_TYPE_TIMESTAMP - COLUMN_TYPE_BYTES - COLUMN_TYPE_TIMESTAMP_WITHOUT_TIMEZONE - COLUMN_TYPE_DECIMAL - COLUMN_TYPE_HLL_SKETCH description: >- User-declared type when the warehouse type needs interpretation. For example, set to COLUMN_TYPE_HLL_SKETCH when the warehouse returns BYTES but the column contains HLL sketches. required: - column description: Mapping between columns of the underlying table and measures. partitionedUpdateStrategyState: type: object properties: currentPartitionLastCheckTime: type: object existsCheckDuration: type: object state: type: string enum: - STATE_UNSPECIFIED - NEXT_PARTITION_IS_IN_THE_FUTURE - PARTITIONS_USUALLY_ARRIVE_LATER - PARTITION_DOES_NOT_EXIST usualLateness: type: object count: type: integer dimensions: type: array items: type: object properties: name: type: string description: Name of the column. type: type: string enum: - COLUMN_TYPE_UNSPECIFIED - COLUMN_TYPE_STRING - COLUMN_TYPE_INT32 - COLUMN_TYPE_INT64 - COLUMN_TYPE_DOUBLE - COLUMN_TYPE_BOOLEAN - COLUMN_TYPE_DATE - COLUMN_TYPE_TIMESTAMP - COLUMN_TYPE_BYTES - COLUMN_TYPE_TIMESTAMP_WITHOUT_TIMEZONE - COLUMN_TYPE_DECIMAL - COLUMN_TYPE_HLL_SKETCH description: >- Type of the column, will be converted to a data warehouse-specific type. repeated: type: boolean description: Determined whether the column is an array. required: - name description: Mapping between columns and dimensions of the entities. state: type: string enum: - TABLE_STATE_UNSPECIFIED - TABLE_STATE_CREATING - TABLE_STATE_ACTIVE - TABLE_STATE_FAILED - TABLE_STATE_UPDATING - TABLE_STATE_DELETED description: Current state of the fact table. error: type: object properties: message: type: string description: Overall description of the error. details: type: array items: type: string description: Specific details of the error. description: Indicates whether an errors has occurred. factDataDeliveredUntilTime: type: object description: >- The current maximum time point where there is available data for the fact table. dataDeliveredUntilUpdateStrategyConfig: type: object properties: disableEmptyPartitions: type: boolean description: If set to true, empty partitions are not allowed. strategy: type: string enum: - STRATEGY_UNSPECIFIED - MANUAL - AUTOMATIC description: The strategy to use automaticUpdateConfig: type: object properties: incrementDuration: type: object description: The size of the window to increment with commitDelay: type: object description: How long the window must be consistent before committing changeThreshold: type: object properties: value: type: string description: |- The decimal value, as a string. The string representation consists of an optional sign, `+` (`U+002B`) or `-` (`U+002D`), followed by a sequence of zero or more decimal digits ("the integer"), optionally followed by a fraction, optionally followed by an exponent. The fraction consists of a decimal point followed by zero or more decimal digits. The string must contain at least one digit in either the integer or the fraction. The number formed by the sign, the integer and the fraction is referred to as the significand. The exponent consists of the character `e` (`U+0065`) or `E` (`U+0045`) followed by one or more decimal digits. Services **should** normalize decimal values before storing them by: - Removing an explicitly-provided `+` sign (`+2.5` -{">"} `2.5`). - Replacing a zero-length integer value with `0` (`.5` -{">"} `0.5`). - Coercing the exponent character to lower-case (`2.5E8` -{">"} `2.5e8`). - Removing an explicitly-provided zero exponent (`2.5e0` -{">"} `2.5`). Services **may** perform additional normalization based on its own needs and the internal decimal implementation selected, such as shifting the decimal point and exponent value together (example: `2.5e-1` {"<"}-{">"} `0.25`). Additionally, services **may** preserve trailing zeroes in the fraction to indicate increased precision, but are not required to do so. Note that only the `.` character is supported to divide the integer and the fraction; `,` **should not** be supported regardless of locale. Additionally, thousand separators **should not** be supported. If a service does support them, values **must** be normalized. The ENBF grammar is: DecimalString = [Sign] Significand [Exponent]; Sign = '+' | '-'; Significand = Digits ['.'] [Digits] | [Digits] '.' Digits; Exponent = ('e' | 'E') [Sign] Digits; Digits = \{ '0' | '1' | '2' | '3' | '4' | '5' | '6' | '7' | '8' | '9' }; Services **should** clearly document the range of supported values, the maximum supported precision (total number of digits), and, if applicable, the scale (number of digits after the decimal point), as well as how it behaves when receiving out-of-bounds values. Services **may** choose to accept values passed as input even when the value has a higher precision or scale than the service supports, and **should** round the value to fit the supported scale. Alternatively, the service **may** error with `400 Bad Request` (`INVALID_ARGUMENT` in gRPC) if precision would be lost. Services **should** error with `400 Bad Request` (`INVALID_ARGUMENT` in gRPC) if the service receives a value outside of the supported range. description: >- The relative threshold for the window to the same. If set to 0.01 it means that if (new_window - old_window) / new_window {"<"} 0.01 the window will be considered the same. required: - commitDelay description: Automatic window increments dailyUpdateConfig: type: object properties: maxZeroDuration: type: object description: >- Maximum amount of time to wait for a new partition when its row count is 0, the partition will be considered empty and Confidence will move on to the next one. Default is 24 hours. description: >- Specifies that the data is written daily. Confidence will detect that data has been written hourlyUpdateConfig: type: object properties: maxZeroDuration: type: object description: >- Maximum amount of time to wait for a new partition when its row count is 0, the partition will be considered empty and Confidence will move on to the next one. Default is 6 hours. description: >- Specifies that the data is written hourly. Confidence will detect that data has been written required: - strategy description: >- The strategy for updating `fact_data_delivered_until_time` in the fact table. schemaValidationJob: type: string description: >- Resource name of the sql job that was used to validate the schema of the fact table. createTime: type: object description: Time when the fact table was first created. updateTime: type: object description: Time when the fact table was last updated. deleteTime: type: object description: Time when the fact table table was deleted. labels: type: array items: type: object additionalProperties: type: string description: Custom labels for this fact table. creator: type: string description: Reference to the identity that created this fact table. updater: type: string description: Reference to the identity that last updated this fact table. owner: type: string description: The owner of the resource. If not set will default to the creator. systemCreated: type: boolean description: If this fact table is an automatically created fact table. required: - sql - displayName - timestampColumn - entities - state - schemaValidationJob - createTime - updateTime - creator - updater securitySchemes: admin: type: http scheme: bearer ````