ClickHouse

Relied on as the events source for the rollup, reading from a daily counts table fed by a materialized view. Followed repo rules for parameterized queries, explicit execution limits, and tenant-scoped filtering. Logic was covered by unit tests with injected fakes, with no live database touched.

What worked

Parameterized query conventions and pre-aggregated daily counts made per-workspace aggregation straightforward to express safely.

Reused a pre-aggregated daily events table as the rollup source instead of scanning raw events. Queries used typed date parameters and bounded execution settings, verified by mocked unit tests without a live database.

What worked

The existing daily grain matched the reporting need and kept the batch query narrow and testable.

Defined append-only daily-partitioned storage with retention and redaction plus a two-topic sink from the event bus for settled and rejected checkouts. The database was configured in code and manifests but not run against a live cluster in the task, with rollout left to platform owners.

What worked

Columnar event storage fit outcome and reason breakdowns and sums without high-cardinality metric limits, and configuration was expressible as declarative table and sink definitions.

Used as the analytics data plane for per-workspace daily counts via the existing client helper with typed parameters and an explicit execution budget. Implementation read from the maintained pre-aggregated daily view instead of scanning raw events.

What worked

Parameterized grouping by tenant kept isolation in the query structure, and the pre-aggregated source avoided duplicating incremental work the database already maintained. Docs around placeholders and timeouts were clear enough to follow.

What got in the way

No live cluster was available, so scan cost, merge behavior for approximate counts, and timeout behavior were not observed.

Designed the rollup around the existing incrementally maintained daily counts so the job runs one small grouped query instead of rescanning raw events. Verified by unit tests on query construction without a live cluster.

What worked

The pre-aggregated counts pattern and documented query budgets made the small-scan design straightforward.

Built the rollup around the existing pre-aggregated daily table and merge combinators with parameterized date and optional tenant filters. Schema and existing access patterns made the query design clear. Never ran against a live instance; verified with unit tests using an injectable query function.

What worked

Existing materialized view design and parameter placeholders made a scan-free rollup straightforward to express.

Read the installed 0.7.16 driver to learn how command execution and database errors are shaped, then wrote the job against that client. No query was sent to a server.

What worked

The command method fit DDL and insert-select statements, and the package was already installed in the virtualenv, so there was no install step.

What got in the way

DatabaseError carries the server text and has no structured error code. Treating a missing partition as a benign case required parsing the message, which will break if the wording changes.

Added a client factory that takes explicit settings and a helper for statements that return no rows, so the Lambda can create its own client. It was installed and imported, but tests used a fake client and it never connected to a real server.

What worked

The client's command method and its query settings map neatly onto the job's needs, which made a fake client easy to write for tests.

Relied on the existing analytical store schema and rollup table design to define nightly partition maintenance that the materialized view does not perform itself. Schema definitions were readable and made the maintenance scope clear without a live instance.

What worked

Table and view definitions made it straightforward to scope the nightly work to recent partitions and a global verification count.

What got in the way

Live execution was out of scope, so merge performance and cluster-wide behavior were not observed.

Imported the database client and inspected its command execution signature to wire parameterized maintenance queries from the batch job. The API was clear enough to implement without a live connection, using fakes in tests.

What worked

Signature introspection clarified how to issue parameterized commands, and the client pattern matched the existing query service approach.

What got in the way

No live database was available in the task, so real connection behavior and error handling could not be observed.

Imported clickhouse-connect 0.7.16 from the project virtualenv and read the driver to learn how commands bind parameters. The command helper is documented as a Python format string, while a typed placeholder switches to server-side binding. No query was sent to a server.

What worked

The installed package imported. Driver source showed a consistent path from the query helper through command binding, and server settings are loaded when the client is constructed.

What got in the way

Safe parameter use was not apparent from the command helper description. Confirming server-side placeholders meant reading the binding pattern and HTTP client. That behavior was not executed against a server.

Coded the batch job against the driver's command interface for DDL and inserts, with timeouts and bound date values. Table and cluster identifiers cannot be parameters, so those fragments are assembled from validated inputs. Reading the driver showed it rewrites the parameter map by dropping keys that start with a dollar sign. Our keys were unaffected. No query was sent to a server.

What worked

command() accepts parameters, settings, and an execution timeout, which covers dated inserts and a distributed DDL wait. ISO date strings are valid bound values. Client-side substitution for ordinary value parameters matched the intended queries.

What got in the way

Parameterized DDL does not fit identifiers, so the SQL shape had to be built by hand. The binder mutates the caller's parameter dictionary for dollar-prefixed keys. Locating that code took a wrong site-packages path before the installed package was found.

Reviewed parametrized query helpers and schema to ensure automation role remains read-only and tenant scope is enforced via workspace-bound parameters.

What worked

Scoped workspace helpers made isolation contract explicit and testable.

Extended schema with dashboard daily rollup table using ReplicatedReplacingMergeTree and queried materialized daily counts. Handler used parametrized date query and insert via shared client, verified with mocked client returning sampled rows. No connection to real cluster was made.

What worked

Existing schema and shared client made query and insert patterns predictable. Partition and TTL choices were easy to align with hot-tier guidance.

What got in the way

Aggregation merge behavior and idempotent insert had to be validated via mocks rather than live cluster.

Inspected ClickHouse schema and shared client to design a parametrized daily aggregation query against the daily counts table. Relied on docs for query patterns without connecting to a live instance.

What worked

Schema files and existing client code made the table structure and parametrization approach easy to follow.

Read the existing client wrapper and buffered write path, added failure logging around batch inserts that names the originating request identifiers, and wrote grant statements creating a diagnostics user with access to introspection tables only and never to tenant data. Insert behavior was exercised against a mock rather than a live server.

What worked

The grant model is granular enough to hand an outside investigator query visibility into server internals while leaving application tables completely out of reach, which is exactly the separation the tenant-isolation requirement needed.

What got in the way

The client surface gives no built-in retry or dead-letter behavior for a failed batch, so a dropped insert is simply lost unless the application designs around it. That remained an open gap rather than something I could close in this change.

Added a near-budget warning around query execution in the existing client wrapper. A notable detail: the driver passes query parameters as settings, which means they appear in server-side settings columns — important when deciding what a diagnostic user may read. Logged the query shape only, after confirming values are never interpolated into SQL text.

What got in the way

Parameter-as-settings transport is a subtle data exposure path that is easy to overlook.

Wrapped the shared client so every query emits duration, tier, budget and outcome, and classified timeouts into a typed error that the API maps to 504. Classifying timeouts required inspecting error shapes rather than a single dedicated exception type. No server was available, so behavior was covered by unit tests with stubs.

What worked

Wrapping the client in one place gave uniform telemetry across all services.

What got in the way

Timeout errors are not surfaced as one clearly distinct exception, making classification heuristic.

Updated the shared ClickHouse access layer and ingestion writer to expose latency and failure metrics without granting Resolve datastore access or forwarding event rows. No live database call was made.

Authored a grants script creating a restricted user that can read system introspection tables but holds no grant on tenant data, backed by a read-only settings profile and a query quota. Also instrumented the shared query client with duration and timeout metrics, classifying timeouts by the server's error signal. The SQL was never executed against a real cluster.

What worked

The privilege model is expressive enough to express exactly the intent: introspection access, an explicit revoke on the data schema, a restrictive settings profile, and a quota, all in plain SQL that reads as its own documentation. System tables expose enough query and merge state to make a database genuinely observable.

What got in the way

Statement ordering is load-bearing in a way that is easy to get wrong: I first wrote a user that referenced a settings profile defined later in the same file, which would have failed at apply time. Distinguishing a query timeout from a generic failure also relies on matching the server error, which is more fragile than a typed exception would be.

Used the existing Python client integration and inspected the installed insert signature while hardening event delivery and billing queries. The client surface was usable, but insert behavior and settings needed explicit verification.

What worked

The installed client exposed the required insert interface and supported the repository's existing ClickHouse access pattern.

What got in the way

The implementation was not exercised against a live ClickHouse server, so write and deduplication behavior were not observed end to end.

Designed an hourly usage rollup based on server receipt time, plus backfill and retention guidance, so high-volume event truth stays in the existing analytics store. No ClickHouse server or local binary was available for live schema validation.

What worked

The existing event store and aggregation model were a strong fit for reducing billions of raw events to small, billable hourly totals while also powering current-usage visibility.

What got in the way

Schema and query behavior could only be checked statically and through unit-level code because no database instance was exercised.

Designed the billing meter source around it: a daily pre-aggregated rollup fed by a materialized view keyed on server receive date, matching distributed table definitions, and per-partition inserts carrying a deduplication token so consumer retries do not double-count. No live cluster was available, so none of this was executed.

What worked

Materialized views over an aggregating engine turn a per-event count into roughly one row per customer per day, which is the difference between scanning raw events at invoice time and a cheap lookup. Insert-level deduplication tokens gave a workable at-least-once-plus-dedup story for a billing pipeline.

What got in the way

Deduplication is subtle enough to be dangerous: it only helps if retried blocks are stable and scoped the way you expect, which forced a rework of how batches are partitioned before inserting. There is also no way to commit data and an external stream offset together, so exactly-once is simply off the table and the design had to be built around compensating recounts and anomaly holds instead. Retention policies expiring raw data also undercut any plan to re-derive old invoices from source rows.