> ## Documentation Index
> Fetch the complete documentation index at: https://docs.probalytics.io/llms.txt
> Use this file to discover all available pages before exploring further.

# SQL Guide

> Query Probalytics data directly with SQL

Get direct access to the complete prediction market database for advanced analytics and complex queries.

## Getting Started

Already have credentials? Jump to [Connection Methods](#connection-methods). New to Probalytics? Create ClickHouse credentials in [app.probalytics.io](https://app.probalytics.io) → ClickHouse Credentials section. See [Quickstart](/docs/quickstart#sql-clickhouse) for setup.

**Connection Details**

* **Host**: `clickhouse.probalytics.io`
* **Port**: `9440` (Secure TCP) or `8443` (Secure HTTPS)
* **Database**: `probalytics`
* **Authentication**: Username and password from dashboard

<Note>
  All connections are secured with TLS encryption. Use the `--secure` flag for CLI or enable TLS/SSL in your client.
</Note>

## Quick Exploration

### SQL Workspace (Browser)

The fastest way to explore and test queries. Visit [app.probalytics.io/sql](https://app.probalytics.io/sql) and start querying immediately—no setup required.

<Warning>
  Large result sets will crash your browser. Keep queries focused and use LIMIT to restrict results. For bulk exports or large analytics, use programmatic connections instead.
</Warning>

## Connection Methods

For production applications and large-scale analysis, use one of these programmatic connections:

<Tabs>
  <Tab title="Python - clickhouse-driver">
    Native ClickHouse driver for best performance.

    Install: `pip install clickhouse-driver`

    ```python theme={null}
    from clickhouse_driver import Client

    client = Client(
        host='clickhouse.probalytics.io',
        port=9440,
        user='YOUR_USERNAME',
        password='YOUR_PASSWORD',
        database='probalytics',
        secure=True
    )

    # Test connection
    result = client.execute('SELECT count() FROM markets;')
    print(f"Total markets: {result[0][0]}")
    ```
  </Tab>

  <Tab title="Python - clickhouse-connect">
    HTTP-based client, simpler setup.

    Install: `pip install clickhouse-connect`

    ```python theme={null}
    import clickhouse_connect

    client = clickhouse_connect.get_client(
        host='clickhouse.probalytics.io',
        port=8443,
        username='YOUR_USERNAME',
        password='YOUR_PASSWORD',
        database='probalytics'
    )

    # Test connection
    result = client.query('SELECT count() FROM markets;')
    print(f"Total markets: {result.first_item}")
    ```
  </Tab>

  <Tab title="JavaScript / Node.js">
    Install: `npm install @clickhouse/client`

    ```javascript theme={null}
    import { createClient } from '@clickhouse/client';

    const client = createClient({
      url: 'https://clickhouse.probalytics.io:8443',
      username: 'YOUR_USERNAME',
      password: 'YOUR_PASSWORD',
      database: 'probalytics'
    });

    // Test connection
    const result = await client.query({
      query: 'SELECT count() FROM markets;',
      format: 'JSONEachRow'
    });

    const data = await result.json();
    console.log(`Total markets: ${data[0].count}`);
    ```
  </Tab>

  <Tab title="Go">
    Install: `go get github.com/ClickHouse/clickhouse-go/v2`

    ```go theme={null}
    package main

    import (
      "fmt"
      "github.com/ClickHouse/clickhouse-go/v2"
      "context"
      "crypto/tls"
    )

    func main() {
      conn, _ := clickhouse.Open(&clickhouse.Options{
        Addr: []string{"clickhouse.probalytics.io:9440"},
        Auth: clickhouse.Auth{
          Username: "YOUR_USERNAME",
          Password: "YOUR_PASSWORD",
        },
        TLS: &tls.Config{},
        Settings: clickhouse.Settings{
          "max_execution_time": 300,
        },
      })

      var count uint64
      err := conn.QueryRow(context.Background(),
        "SELECT count() FROM markets;").Scan(&count)
      fmt.Printf("Total markets: %d\n", count)
    }
    ```
  </Tab>

  <Tab title="DBeaver">
    Popular GUI tool for SQL queries and exploration.

    1. Download [DBeaver Community](https://dbeaver.io/download/)
    2. Create new connection → Search "ClickHouse"
    3. Configure:
       * **Server Host**: clickhouse.probalytics.io
       * **Port**: 8443
       * **Database**: probalytics
       * **Username**: From your dashboard
       * **Password**: From your dashboard
       * **SSL**: Enable SSL/TLS in connection settings
    4. Test connection → Browse and query tables
  </Tab>

  <Tab title="CLI - clickhouse-client">
    Quick exploration and ad-hoc queries.

    Install: [ClickHouse Docs](https://clickhouse.com/docs/en/install)

    ```bash theme={null}
    clickhouse client --host=clickhouse.probalytics.io --secure --port=9440 --user=YOUR_USERNAME --password=YOUR_PASSWORD --database=probalytics
    ```

    Or run a query directly:

    ```bash theme={null}
    clickhouse client \
      --host clickhouse.probalytics.io \
      --secure \
      --port 9440 \
      --user YOUR_USERNAME \
      --password YOUR_PASSWORD \
      --database probalytics \
      --query "SELECT count() FROM markets;"
    ```
  </Tab>
</Tabs>

## Exploring the Database

### List All Tables

```sql theme={null}
SHOW TABLES FROM probalytics;
```

### Describe Table Structure

```sql theme={null}
DESCRIBE TABLE probalytics.markets;
DESCRIBE TABLE probalytics.fills;
DESCRIBE TABLE probalytics.orderbook_snapshots;
```

### Test Your Connection

```sql theme={null}
SELECT
    'markets' as table_name,
    count() as row_count
FROM markets
UNION ALL
SELECT
    'fills' as table_name,
    count() as row_count
FROM fills
UNION ALL
SELECT
    'orderbook_snapshots' as table_name,
    count() as row_count
FROM orderbook_snapshots;
```

## Understanding ReplacingMergeTree

Tables use the `ReplacingMergeTree` engine for efficient updates and deduplication.

**How it works:**

* New versions of the same row replace old versions
* Deduplication is based on the `indexed_at` column (highest value wins)
* Background merges consolidate duplicates asynchronously
* Queries may return duplicates until merges complete

**The FINAL Keyword**

Use `FINAL` to force immediate deduplication:

```sql theme={null}
-- May include duplicates (fast)
SELECT count() FROM markets;

-- Guaranteed deduplicated (slower)
SELECT count() FROM markets FINAL;
```

<Warning>
  `FINAL` triggers synchronous deduplication which is significantly slower. Use it only when you need guaranteed unique results. For most analytics, skip `FINAL` and rely on background merges.
</Warning>

## Access Tiers & Limits

Your tier determines available data, query limits, and resource quotas:

| Feature                 | Starter        | Pro            | Custom       |
| ----------------------- | -------------- | -------------- | ------------ |
| **Tables**              | markets, fills | markets, fills | All tables   |
| **Orderbook Snapshots** | -              | -              | Early Access |
| **Historical Data**     | Last 30 days   | Full history   | Full history |
| **Queries/hour**        | 100            | 1,000          | Unlimited    |
| **Max Execution Time**  | 5 minutes      | 1 hour         | Unlimited    |
| **Max Memory**          | 4 GB           | 32 GB          | Unlimited    |
| **Max Result Rows**     | 100,000        | 1 billion      | Unlimited    |

<Note>
  Your tier is set when creating ClickHouse credentials in the dashboard. Upgrade your plan to access higher tiers.
</Note>

## Available Tables

### markets

Primary table for prediction market metadata.

* **Available to**: All tiers
* **Records**: One per market across platforms
* **Key fields**: platform, title, description, outcomes, status, market\_type
* See [Tables reference](/docs/sql-guide/tables) for complete schema

### fills

Individual trade fill records on each market.

* **Available to**: All tiers
* **Records**: One per trade executed
* **Key fields**: market\_id, size, price, normalized\_price, taker\_side, taker\_id, maker\_id, fee, timestamp
* **Note**: Partitioned monthly by `timestamp` for performance
* See [Tables reference](/docs/sql-guide/tables) for complete schema

### orderbook\_snapshots

Orderbook depth snapshots per market outcome, written when the book state changes.

* **Available to**: Early Access only
* **Records**: One per outcome per timestamp, containing full bid/ask depth
* **Key fields**: market\_id, outcome, bids, asks, timestamp
* **Note**: Partitioned monthly by `timestamp` for performance
* **Data range**: Polymarket available from November 20, 2025 (high-quality snapshots from February 17, 2026). Kalshi available live from May 8, 2026.
* See [Tables reference](/docs/sql-guide/tables) for complete schema

## Next Steps

<CardGroup cols={3}>
  <Card title="Tables Reference" icon="database" href="/docs/sql-guide/tables">
    Complete field definitions and data types
  </Card>

  <Card title="Common Queries" icon="code" href="/docs/sql-guide/common-queries">
    Ready-to-run query examples
  </Card>

  <Card title="SQL Tips" icon="gauge" href="/docs/sql-guide/tips">
    Performance optimization strategies
  </Card>
</CardGroup>