Skip to main content
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: 
curl -X POST "https://api.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: 
curl -X POST "https://api.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: 
curl -X POST "https://api.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: