Skip to main content

Give Every Sandbox Its Own ClickHouse Database Branch

This tutorial gives each Signadot sandbox its own branch of a ClickHouse database. ClickHouse has no branch command, so a resource plugin builds one when the sandbox starts: a new database on the same server, whose MergeTree tables are hard-linked to the source's data parts. The branch is ready in under a second and no data is copied. The sandboxed service reads and writes the branch, everyone else keeps using the shared database, and the branch is dropped when the sandbox is deleted.

The demo is a usage-metering API. It records billable events in an events table, a materialized view rolls them up into daily_usage, and the API serves daily counts per customer.

Full source: signadot/examples/clickhouse-branching-tutorial. Plugin: signadot/plugins/clickhouse.

When you need a ClickHouse branch​

On a shared staging ClickHouse, testing a change to your data breaks other people's work:

You're testingOn a shared databaseOn a branch
Ingestion code that writes eventsYour test rows change everyone's countsWrites land only in your branch
A new rollup or materialized viewOther tests read half-migrated tablesYou rebuild it in your branch
A schema migration (ALTER TABLE)Every service on staging sees it at onceOnly your sandbox sees it
A backfill, dedupe or DELETEShared data is gone for everyoneThe source is never touched
Tests that assert exact numbersFlaky, because others write tooNobody else writes to your branch

How the branch is made​

Requests without a routing key reach the baseline usage-api, which uses the analytics database. Requests with the sandbox's routing key reach the sandbox's usage-api, which uses analytics_usage_test: a branch on the same ClickHouse server whose tables share the data parts of analytics. The plugin creates the branch when the sandbox starts and drops it when the sandbox is deleted.

A branch is possible because a MergeTree table stores its data in immutable parts. Inserts, merges and mutations write new parts and never change existing ones, so two tables can share the same part files, and a write to either one adds parts only to that table. The plugin's create step runs three kinds of statements over ClickHouse's HTTP interface:

create-branch.sh (simplified)
# 1. A new database for the branch, on the same server as the source.
ch "CREATE DATABASE $BRANCH COMMENT 'Signadot sandbox branch'"

# 2. For each MergeTree table: copy the schema, then attach the source's partitions.
# On the same disk this makes hard links, one per file, so no data is copied.
ch "CREATE TABLE $BRANCH.$t AS $SOURCE_DATABASE.$t"
ch "ALTER TABLE $BRANCH.$t ATTACH PARTITION ID '$p' FROM $SOURCE_DATABASE.$t"

# 3. Materialized views, rewritten to read and write the branch's tables.

The full script is create-branch.sh. The plugin outputs the branch's name, <database>_<sandbox name>, and the sandbox spec passes it to the forked service as CLICKHOUSE_DATABASE. The service (app.py) has no Signadot code: it uses whichever database that variable names. The plugin only replaces or drops databases that carry the Signadot sandbox branch comment, so it never touches a database that merely shares a branch's name.

Before you begin​

Prerequisites
  • A Kubernetes cluster with the Signadot Operator installed.
  • The Signadot CLI, logged in with signadot auth login.
  • kubectl pointed at that cluster, git and jq.
  • Docker, and a container registry your cluster can pull from.

1. Build the image​

Build the usage-api image, push it, and point the manifest at it:

git clone https://github.com/signadot/examples.git
cd examples/clickhouse-branching-tutorial

export IMAGE=docker.io/your-user/clickhouse-branching-demo:latest # your registry
docker build -t $IMAGE .
docker push $IMAGE
sed -i.orig "s|image: clickhouse-branching-demo:latest|image: $IMAGE|" k8s/usage-api.yaml
Any registry your cluster can pull from works

docker.io/your-user/... is a placeholder. When you build images of your own services this way, push them to a private repository and give the cluster pull access to it, for example with an image pull secret.

If your cluster's nodes have a different CPU architecture than your machine (for example an Apple silicon Mac and an x86 cluster), add --platform linux/amd64 to docker build. On a local cluster that runs images straight from your Docker daemon, such as Docker Desktop, you can skip the push.

2. Deploy ClickHouse and the usage API​

Start ClickHouse, load the analytics schema with 300,000 seed events from the last 59 days (three monthly partitions), then start usage-api:

kubectl create namespace clickhouse-demo
kubectl apply -f k8s/clickhouse.yaml
kubectl -n clickhouse-demo wait --for=condition=available deploy/clickhouse --timeout=300s
kubectl -n clickhouse-demo exec -i deploy/clickhouse -- \
clickhouse-client --password demo-password --multiquery < clickhouse/schema.sql

kubectl apply -f k8s/usage-api.yaml
kubectl -n clickhouse-demo wait --for=condition=available deploy/usage-api --timeout=300s

Connect your machine to the cluster, so that cluster service names resolve locally (this asks for your password):

export CLUSTER=my-cluster  # your cluster's name, from: signadot cluster list
signadot local connect --cluster=$CLUSTER

Define two shortcuts, one for the API and one for ClickHouse's HTTP interface, and read today's usage for the customer acme:

API=http://usage-api.clickhouse-demo.svc:8080
ch() { curl -sS -u default:demo-password http://clickhouse.clickhouse-demo.svc:8123 --data-binary "$1"; }

curl -s $API/usage/acme | jq -c '.database, .days[-1]'
Output
"analytics"
{"day":"2026-11-18","events":290}

Your count will differ, because it depends on the time of day you loaded the seed data. Run the remaining steps in this same shell: they use $CLUSTER, $API, ch and, from step 4, $ROUTING_KEY.

3. Install the resource plugin​

The clickhouse-branch plugin runs in the signadot namespace and reads the ClickHouse user and password from a Secret named clickhouse-credentials there. Create the Secret, clone signadot/plugins next to the examples repository, point the plugin at this tutorial's ClickHouse, and install it:

kubectl -n signadot create secret generic clickhouse-credentials \
--from-literal=username=default --from-literal=password=demo-password

git clone https://github.com/signadot/plugins.git ../../signadot-plugins
cd ../../signadot-plugins/clickhouse
sed -i.orig "s|http://clickhouse.default.svc:8123|http://clickhouse.clickhouse-demo.svc:8123|" plugin.yaml
signadot resourceplugin apply -f plugin.yaml
cd -
Output
Created resource plugin clickhouse-branch@0.0.0

4. Create a sandbox with its own branch​

The sandbox spec requests a branch of analytics from the plugin and gives its name to the forked usage-api as CLICKHOUSE_DATABASE:

signadot/usage-api-sandbox.yaml
name: "@{name}"
spec:
description: usage-api with its own branch of the analytics ClickHouse database
cluster: "@{cluster}"
labels:
demo: clickhouse-branching
resources:
- name: analytics
plugin: clickhouse-branch
params:
database: analytics
# "all", or how many of each table's newest partitions to attach (e.g. "1").
partitions: "@{partitions}"
forks:
- forkOf:
kind: Deployment
namespace: clickhouse-demo
name: usage-api
customizations:
env:
- name: CLICKHOUSE_DATABASE
valueFrom:
resource:
name: analytics
outputKey: createbranch.database

Create the sandbox and save its routing key:

signadot sandbox apply -f signadot/usage-api-sandbox.yaml \
--set name=usage-test --set cluster=$CLUSTER --set partitions=all
export ROUTING_KEY=$(signadot sandbox get usage-test -o json | jq -r .routingKey)

The sandbox is ready in about 10 seconds. The plugin's create step takes under a second; most of the time goes to starting the forked pod. Compare the two databases:

ch "SELECT database, table, groupArray(partition_id) AS partitions, sum(rows) AS rows
FROM system.parts WHERE database LIKE 'analytics%' AND active
GROUP BY database, table ORDER BY table, database FORMAT PrettyCompact"
Output
   ┌─database─────────────┬─table───────┬─partitions───────────────────┬───rows─┐
1. │ analytics │ daily_usage │ ['202609','202610','202611'] │ 180 │
2. │ analytics_usage_test │ daily_usage │ ['202609','202610','202611'] │ 180 │
3. │ analytics │ events │ ['202609','202610','202611'] │ 300000 │
4. │ analytics_usage_test │ events │ ['202609','202610','202611'] │ 300000 │
└──────────────────────┴─────────────┴──────────────────────────────┴────────┘

The branch has every partition and every row. Now check that none of it was copied. List the inode and link count of each events part file in both databases:

kubectl -n clickhouse-demo exec deploy/clickhouse -- stat -c '%i %h %n' $(
ch "SELECT path || 'data.bin' FROM system.parts
WHERE database IN ('analytics', 'analytics_usage_test') AND table = 'events' AND active
ORDER BY name, database")
Output
2234426 2 /var/lib/clickhouse/store/72e/72e59fa2-…/202609_3_3_0/data.bin
2234426 2 /var/lib/clickhouse/store/177/177df6fb-…/202609_3_3_0/data.bin
2234412 2 /var/lib/clickhouse/store/72e/72e59fa2-…/202610_2_2_0/data.bin
2234412 2 /var/lib/clickhouse/store/177/177df6fb-…/202610_2_2_0/data.bin
2234398 2 /var/lib/clickhouse/store/72e/72e59fa2-…/202611_1_1_0/data.bin
2234398 2 /var/lib/clickhouse/store/177/177df6fb-…/202611_1_1_0/data.bin

Each pair has the same inode (first column), so the two paths are the same file on disk, and each file has two links (second column): one from analytics, one from the branch.

5. Write test data through the sandbox​

Requests with the sandbox's routing key go to the sandbox's usage-api. Record three events for acme through it, then read today's count from both versions:

curl -s -X POST $API/events -H "baggage: sd-routing-key=$ROUTING_KEY" \
-H 'Content-Type: application/json' \
-d '[{"customer": "acme"}, {"customer": "acme"}, {"customer": "acme", "event": "export"}]'

curl -s $API/usage/acme -H "baggage: sd-routing-key=$ROUTING_KEY" | jq -c '.database, .days[-1]'
curl -s $API/usage/acme | jq -c '.database, .days[-1]'
Output
{"database":"analytics_usage_test","inserted":3}
"analytics_usage_test"
{"day":"2026-11-18","events":293}
"analytics"
{"day":"2026-11-18","events":290}

The insert went into the branch's events table, and the branch's materialized view added it to the branch's daily_usage. The baseline still reports 290.

6. Make a destructive change in the branch​

Delete all of acme's usage in the branch, the kind of change a backfill or cleanup job makes:

ch "ALTER TABLE analytics_usage_test.daily_usage DELETE WHERE customer = 'acme'
SETTINGS mutations_sync = 1"

curl -s $API/usage/acme -H "baggage: sd-routing-key=$ROUTING_KEY" | jq -c '.database, .days'
curl -s $API/usage/acme | jq -c '.database, (.days | length)'
Output
"analytics_usage_test"
[]
"analytics"
7

The mutation wrote new parts for the branch's table. The shared part files, and the analytics database, are unchanged.

7. Delete the sandbox​

signadot sandbox delete usage-test
ch "SHOW DATABASES LIKE 'analytics%'"
ch "SELECT count() FROM analytics.events"
Output
analytics
300000

The plugin's delete step dropped analytics_usage_test, which removed only the branch's links. analytics still has all 300,000 events, and its part files are back to one link each.

8. Optional: branch only the newest partitions​

Set partitions to how many of each table's newest partitions to attach. A branch keeps the parts it attached on disk for as long as it exists, even after the source merges or drops them, so for large tables, attach only the data your tests need:

signadot sandbox apply -f signadot/usage-api-sandbox.yaml \
--set name=usage-latest --set cluster=$CLUSTER --set partitions=1
ch "SELECT table, groupArray(partition_id) FROM system.parts
WHERE database = 'analytics_usage_latest' AND active GROUP BY table"
signadot sandbox delete usage-latest
Output
daily_usage	['202611']
events ['202611']

Both tables are partitioned by month, so the branch gets the current month of each and the two tables stay consistent. The plugin picks the newest partitions by sorting partition IDs, which suits time-based partition keys such as toYYYYMM(ts).

9. Clean up​

Remove the plugin, its Secret and the demo, and disconnect:

signadot resourceplugin delete clickhouse-branch
kubectl -n signadot delete secret clickhouse-credentials
kubectl delete namespace clickhouse-demo
signadot local disconnect

Use it with your own service​

  1. Give the plugin a ClickHouse user. Create a user that can create and drop databases and read system.databases, system.tables and system.parts, rather than reusing an admin user. Store its name and password in the clickhouse-credentials Secret in the signadot namespace.
  2. Install the plugin. In the plugin's plugin.yaml, set CLICKHOUSE_URL to your server's HTTP endpoint (port 8123 by default), then run signadot resourceplugin apply -f plugin.yaml.
  3. Read the database name from configuration. Your service must take its ClickHouse database from a setting you can override per workload, such as an environment variable.
  4. Request a branch in the sandbox spec. Add the plugin under resources with your database name, and set the fork's variable from the createbranch.database output, as signadot/usage-api-sandbox.yaml does in step 4. In CI, also set the fork's image to the pull request's image (see CI integrations).

The plugin's inputs and output:

NameKindDescription
databaseParameterThe database to branch, such as analytics.
partitionsParameterOptional. all (the default), or how many of each table's newest partitions to attach.
createbranch.databaseOutputThe branch's database name: <database>_<sandbox name>, with hyphens turned into underscores.

Frequently asked questions​

Does branching a ClickHouse database copy the data?​

No. ALTER TABLE ... ATTACH PARTITION ... FROM between two tables on the same disk creates hard links to the source table's part files. A branch uses new disk space only for what it writes, plus the source parts it keeps alive after the source merges or drops them.

How long does it take to create a ClickHouse branch?​

The plugin's create step takes under a second for this tutorial's 300,000 rows. Its time grows with the number of tables and partitions, not with the size of the data, because it makes one hard link per file. The whole sandbox is ready in about 10 seconds, most of it spent starting the forked pod.

Can a test in the sandbox change the shared ClickHouse database?​

Not through its branch. MergeTree parts are immutable: inserts, ALTER TABLE ... DELETE, UPDATE and schema changes in the branch write new parts for the branch's tables only, and the files the branch shares with the source never change.

What happens to the ClickHouse branch when the sandbox is deleted?​

The resource plugin's delete step drops the branch database. That removes only the branch's links to the shared parts, so the source database keeps all of its data. The plugin drops only databases that carry its "Signadot sandbox branch" comment, so it never drops a database it did not create.

Does ClickHouse need the sandbox's routing key?​

No. The sandboxed service connects to its own branch database, so the routing key never reaches ClickHouse. Signadot routes requests with the routing key to the sandboxed service, and that service picks the branch through its CLICKHOUSE_DATABASE environment variable.

Which ClickHouse tables and views does the plugin copy?​

It copies MergeTree-family tables, such as MergeTree, ReplacingMergeTree and SummingMergeTree, with their data, and it recreates materialized views so that they read and write the branch's tables. A materialized view must write to a table named with TO. Plain views, dictionaries and tables with other engines are not copied.

Does it work with ClickHouse Cloud or a replicated ClickHouse cluster?​

The plugin targets a single self-managed ClickHouse server, where ATTACH PARTITION ... FROM hard-links parts on a local disk. A replicated cluster also needs ON CLUSTER and its own replication path for each branch's tables, which the plugin does not set up. ClickHouse Cloud is not covered.

See also​