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

> Learn how to create fact tables using the API.

export const HowToSchema = ({name, description, steps = []}) => {
  const schema = {
    "@context": "https://schema.org",
    "@type": "HowTo",
    name,
    description
  };
  if (steps.length > 0) {
    schema.step = steps.map((s, i) => ({
      "@type": "HowToStep",
      position: i + 1,
      name: typeof s === "string" ? s : s.name,
      text: typeof s === "string" ? s : s.text || s.name
    }));
  }
  return <script type="application/ld+json" dangerouslySetInnerHTML={{
    __html: JSON.stringify(schema)
  }} />;
};

<HowToSchema
  name="Create Fact Tables"
  description="Learn how to create fact tables using the API."
  steps={[
{
  name: "Before You Begin",
  text: "Before creating a fact table, ensure you have: - An API access token with appropriate permissions - Created the entities you want to measure",
},
{
  name: "Create a Fact Table",
  text: "Create a fact table with a boolean measurement:",
},
{
  name: "Create a Fact Table with Numeric Measurements",
  text: "For tables with numeric measurements like revenue:",
},
{
  name: "Create a Fact Table with Multiple Entities",
  text: "Track measurements across multiple entities:",
},
{
  name: "Column Types",
  text: "Supported measurement types: - COLUMN_TYPE_BOOLEAN: True/false values - COLUMN_TYPE_INTEGER: Whole numbers",
},
{
  name: "Data Delivery",
  text: "After creation, the fact table enters the CREATING state. Confidence runs a sample query to verify the SQL produces the expected columns, then transitions to either ACTIVE or FAILED.",
},
]}
/>

Create fact tables that contain measurements describing your entities.

## Before You Begin

Before creating a fact table, ensure you have:

* An API access token with appropriate permissions
* Created the entities you want to measure
* Prepared a SQL query that selects measurement rows from your data warehouse
* Identified timestamp, entity, and measurement columns

## Create a Fact Table

Create a fact table with a boolean measurement:

```bash theme={null}
curl -X POST "https://metrics.confidence.dev/v1/factTables" \
  -H "Authorization: Bearer YOUR_ACCESS_TOKEN" \
  -H "Content-Type: application/json" \
  -d '{
    "name": "checkouts",
    "displayName": "Checkouts",
    "sql": "SELECT * FROM confidence.analysis.checkouts",
    "timestampColumn": {
      "name": "date",
      "type": "COLUMN_TYPE_STRING",
      "repeated": false
    },
    "entities": [
      {
        "column": {
          "name": "user_id",
          "type": "COLUMN_TYPE_STRING",
          "repeated": false
        },
        "entity": "entities/user"
      }
    ],
    "measurements": [
      {
        "name": "completed_checkout",
        "type": "COLUMN_TYPE_BOOLEAN",
        "repeated": false
      }
    ],
    "dataDeliveredUntilUpdateStrategyConfig": {
      "dailyUpdateConfig": {}
    }
  }'
```

## Create a Fact Table with Numeric Measurements

For tables with numeric measurements like revenue:

```bash theme={null}
curl -X POST "https://metrics.confidence.dev/v1/factTables" \
  -H "Authorization: Bearer YOUR_ACCESS_TOKEN" \
  -H "Content-Type: application/json" \
  -d '{
    "name": "purchases",
    "displayName": "Purchases",
    "sql": "SELECT * FROM purchases",
    "timestampColumn": {
      "name": "purchase_date",
      "type": "COLUMN_TYPE_STRING",
      "repeated": false
    },
    "entities": [
      {
        "column": {
          "name": "user_id",
          "type": "COLUMN_TYPE_STRING",
          "repeated": false
        },
        "entity": "entities/user"
      }
    ],
    "measurements": [
      {
        "name": "purchase_amount",
        "type": "COLUMN_TYPE_DOUBLE",
        "repeated": false
      },
      {
        "name": "item_count",
        "type": "COLUMN_TYPE_INTEGER",
        "repeated": false
      }
    ],
    "dataDeliveredUntilUpdateStrategyConfig": {
      "dailyUpdateConfig": {}
    }
  }'
```

## Create a Fact Table with Multiple Entities

Track measurements across multiple entities:

```bash theme={null}
curl -X POST "https://metrics.confidence.dev/v1/factTables" \
  -H "Authorization: Bearer YOUR_ACCESS_TOKEN" \
  -H "Content-Type: application/json" \
  -d '{
    "name": "sessions",
    "displayName": "User Sessions",
    "sql": "SELECT * FROM sessions",
    "timestampColumn": {
      "name": "session_start",
      "type": "COLUMN_TYPE_STRING",
      "repeated": false
    },
    "entities": [
      {
        "column": {
          "name": "user_id",
          "type": "COLUMN_TYPE_STRING",
          "repeated": false
        },
        "entity": "entities/user"
      },
      {
        "column": {
          "name": "session_id",
          "type": "COLUMN_TYPE_STRING",
          "repeated": false
        },
        "entity": "entities/session"
      }
    ],
    "measurements": [
      {
        "name": "duration_seconds",
        "type": "COLUMN_TYPE_INTEGER",
        "repeated": false
      }
    ],
    "dataDeliveredUntilUpdateStrategyConfig": {
      "dailyUpdateConfig": {}
    }
  }'
```

## Column Types

Supported measurement types:

* `COLUMN_TYPE_BOOLEAN`: True/false values
* `COLUMN_TYPE_INTEGER`: Whole numbers
* `COLUMN_TYPE_DOUBLE`: Decimal numbers
* `COLUMN_TYPE_STRING`: Text values

## Data Delivery

After creation, the fact table enters the `CREATING` state. Confidence runs a sample query to verify the SQL produces the expected columns, then transitions to either `ACTIVE` or `FAILED`.

## Next Steps

After creating fact tables:

* [Create metrics](./create-metric) to aggregate measurements
* [Create dimension tables](./create-dimension-table) to segment analysis
* Configure experiments to track these measurements