Implementation:Langfuse Langfuse ClickHouse Dev Tables Script

Knowledge Sources	Langfuse
Domains	Database, ClickHouse, Development Infrastructure
Last Updated	2026-02-14 00:00 GMT

Overview

A Bash script that creates experimental ClickHouse development tables and backfills them with data from the existing observations and traces tables.

Description

This script manages the creation and population of ClickHouse tables that are still in experimental/development status and not yet part of the official migration system. It is intended for local development setups.

The script performs two main operations:

1. Table Creation:

• observations_batch_staging -- A staging table for batch processing of observations. Uses ReplacingMergeTree engine with 3-minute partitions on s3_first_seen_timestamp. Partitions are automatically expired after 12 hours via TTL. This table is designed to efficiently batch-merge observations with trace data into the events table. (See LFE-7122.)
• events -- A new denormalized events table intended to eventually replace the separate observations and traces tables. Uses ReplacingMergeTree engine partitioned by month (toYYYYMM(start_time)), with a primary key of (project_id, start_time, xxHash32(trace_id)) and sampling by xxHash32(trace_id). (See LFE-5394.) This table contains a rich schema including trace properties, observation details, usage/cost metrics with JSON materialized columns, metadata stored using the "German Strings" pattern for prefix matching, experiment properties, and source telemetry attributes. Bloom filter and minmax indexes are defined for efficient querying.

2. Data Backfill:

• Truncates the events table and repopulates it by joining data from the existing observations and traces ClickHouse tables.
• First inserts observation-level events (joined with trace data for tags, release, user_id, etc.).
• Then inserts trace-level events as root spans (where the trace itself becomes a SPAN event).

The script requires the clickhouse CLI binary and the following environment variables: CLICKHOUSE_MIGRATION_URL, CLICKHOUSE_USER, CLICKHOUSE_PASSWORD, and optionally CLICKHOUSE_DB (defaults to "default").

Usage

Use this script when:

• Setting up a local development environment that needs the experimental events table.
• Resetting ClickHouse development tables after schema changes.
• Running as part of pnpm run dx, pnpm run dx-f, or pnpm run ch:reset workflows.
• Directly via pnpm run ch:dev-tables from the packages/shared/ directory.

Code Reference

Source Location

• Repository: Langfuse
• File: packages/shared/clickhouse/scripts/dev-tables.sh
• Lines: 1-441

Signature

#!/bin/bash
# Development-only ClickHouse table creation script
# Usage: pnpm run ch:dev-tables (from packages/shared/)

# Environment variables required:
#   CLICKHOUSE_MIGRATION_URL - clickhouse://host:port
#   CLICKHOUSE_USER
#   CLICKHOUSE_PASSWORD
#   CLICKHOUSE_DB (optional, defaults to "default")

# Creates tables:
#   observations_batch_staging - 3-minute partitioned staging table with 12h TTL
#   events - Denormalized events table (ReplacingMergeTree, monthly partitions)

# Then backfills events from observations + traces tables

Import

# Loads environment variables from project root .env file:
[ -f ../../.env ] && source ../../.env

I/O Contract

Inputs

Name	Type	Required	Description
CLICKHOUSE_MIGRATION_URL	Environment variable (string)	Yes	ClickHouse connection URL in format clickhouse://host:port
CLICKHOUSE_USER	Environment variable (string)	Yes	ClickHouse authentication username
CLICKHOUSE_PASSWORD	Environment variable (string)	Yes	ClickHouse authentication password
CLICKHOUSE_DB	Environment variable (string)	No	Target database name (defaults to "default")

Outputs

Name	Type	Description
observations_batch_staging table	ClickHouse table	Staging table for batch observation processing with 3-minute partitions and 12-hour TTL
events table	ClickHouse table	Denormalized events table populated from observations and traces data
stdout	Console output	Progress messages indicating table creation and data population status

Usage Examples

# Run from packages/shared/ directory
pnpm run ch:dev-tables

# Or as part of full development reset
pnpm run dx

# Or as part of ClickHouse reset
pnpm run ch:reset