Serving company signals from ClickHouse

A product that shows company signals runs three kinds of query: the last twenty changes on an account, the accounts that added a technology this month, and the highest intent scores for a topic this week. In ClickHouse the ordering key of a table decides which of those reads little data, and ClickHouse's guidance is to define it when the table is created. This guide chooses engines and ordering keys for the Fokals event and weekly tables, shows the product query each key serves and sets out a loading pattern that keeps the results correct.

Fokals is delivered direct, by REST API and as bulk files in CSV, JSON or JSON Lines, which you load into ClickHouse with its own formats and clients. If the rows then appear inside your product, the agreement you sign must cover embedding in a product or redistribution; what a data licence covers sets out the three uses.

The shape of the data picks the engine

ClickHouse's documentation describes it as a column-oriented SQL database management system for online analytical processing. A table of the MergeTree family stores each insert as a part that a background process merges. The ORDER BY clause is the sorting key and, unless you set PRIMARY KEY, the primary key too. The index is sparse, with one entry for each granule of 8,192 rows by default. Fokals tables fall into three kinds.

The ClickHouse guidance on primary keys says to put first the columns most used in query filters, and its worked example orders a low-cardinality column ahead of a date. It also says an ordering key must be defined when the table is created. The sparse primary index guide shows ordering columns by ascending cardinality improving both compression and index use. So the keys above come from the queries in this guide, not from the column order of the files.

The two pieces of advice pull against each other for company_id, which is high in cardinality. The first says to lead with it, because every account timeline filters on it. The second says to lead with a column such as category, which has few values. The timeline wins, since it is the query a user waits for, and the query by technology gets its own ordering below. The weekly table follows the second piece of advice, because a ranked list always filters on a topic first.

Events: a timeline per account

The REST feeds carry an integer id on every row of /changes and /signals. If you load files, use as the key a tuple you have tested for uniqueness on your sample. The page order keys in the API reference are a starting point: for Technology Changes they are observed_at, domain, category, key and change.

CREATE TABLE company_tech_events
(
    id          UInt64,
    observed_at DateTime64(6, 'UTC'),
    company_id  UUID,
    domain      String,
    category    LowCardinality(String),
    `key`       String,
    change      LowCardinality(String),
    technology_name     LowCardinality(String),
    technology_category LowCardinality(String),
    `before`    String,
    `after`     String,
    loaded_at   DateTime DEFAULT now()
)
ENGINE = ReplacingMergeTree(loaded_at)
PARTITION BY toYYYYMM(observed_at)
ORDER BY (company_id, observed_at, id)
SETTINGS deduplicate_merge_projection_mode = 'rebuild';

The choices, one by one. ReplacingMergeTree treats rows with the same ORDER BY tuple, not the same PRIMARY KEY, as duplicates and keeps the one with the highest version, here loaded_at; that is why id sits in the key. DateTime64(6, 'UTC') keeps the microseconds Fokals writes in observed_at, so a bookmark you send back as since is the value you received. ClickHouse's data type guidance prefers the coarsest datetime type that meets your queries, so use DateTime if you never resume from an exact value.

The other columns follow the same guidance. category and change hold a dozen values at most, and the 6,283 recognised technologies in 68 categories behind technology_name and technology_category are below the 10,000 or so distinct values under which the LowCardinality page says ClickHouse mostly shows higher efficiency. The data type guidance also advises default values over Nullable, so an absent before is an empty string. before and after stay as JSON text, as the files carry them. The JSON type, which its page marks production ready from version 25.3 of the open-source release, is the alternative if you query inside them.

The monthly partition is there for housekeeping only. The partitioning guidance calls partitioning a data management technique, not a query optimisation, and advises fewer than about 100 to 1,000 distinct values. Check what your agreement says about loaded data when it ends: dropping a partition is a single metadata operation.

Three product queries

The account timeline reads one company's granules, because company_id leads the key.

SELECT observed_at, category, `key`, change
FROM company_tech_events
WHERE company_id = {company_id:UUID}
ORDER BY observed_at DESC
LIMIT 20;

The adopters query filters on category, key and change, which are not at the front of the key, so it would read far more than it returns. Give it its own ordering with a projection, a hidden table with a different row order that ClickHouse chooses when it reads less data. A SELECT * projection reorders the whole table, so add one per access path.

On a ReplacingMergeTree table a projection needs one more decision, which is why the table above ends with a setting. The projection statements page says that since version 24.8 the setting deduplicate_merge_projection_mode decides what happens when a merge removes rows from such a table. The default, throw, raises an exception, drop drops the affected projection parts and rebuild rebuilds them. rebuild lets deduplicating merges succeed and keeps the projection consistent with the table.

ALTER TABLE company_tech_events
    ADD PROJECTION by_technology (SELECT * ORDER BY category, `key`, change, observed_at);
ALTER TABLE company_tech_events MATERIALIZE PROJECTION by_technology;

SELECT company_id, domain, technology_name, observed_at
FROM company_tech_events
WHERE category = 'technology' AND `key` = {technology:String} AND change = 'added'
  AND observed_at >= now() - INTERVAL 30 DAY
ORDER BY observed_at DESC;

The periods table serves the trend chart on the same account page. With ORDER BY (company_id, day), ninety days of one company's hiring is a single contiguous range, and a plain MergeTree is enough because a closed day is written once.

SELECT day, open_postings, new_postings, closed_postings
FROM company_hiring_daily
WHERE company_id = {company_id:UUID} AND day >= today() - 90
ORDER BY day;

New postings count roles opened after a company's first observation, so the first day of a company's series can show open postings and none new. That is the baseline.

An added row is a dated change in a technology. A first observation is a baseline and writes none, so the count is not inflated by companies entering the index. The marketing stack dataset describes the events and their categories.

Weekly scores serve ranked lists. Fokals writes a week once after it closes, so nothing is replaced, and the key puts the 69 topics first.

CREATE TABLE company_intent_weekly
(
    week_start     Date,
    company_id     UUID,
    topic          LowCardinality(String),
    topic_group    LowCardinality(String),
    intent_version LowCardinality(String),
    score          Float32,
    surge          Bool,
    signals        UInt16,
    evidence       String
)
ENGINE = MergeTree
ORDER BY (topic, week_start, company_id);

SELECT company_id, score
FROM company_intent_weekly
WHERE topic = {topic:String} AND week_start = {week:Date} AND surge
ORDER BY score DESC
LIMIT 50;

A surge flags a score that at least doubles the company's own twelve-week average for the topic. The intent dataset explains the score. A per-account history of scores needs company_id first, so add a projection ordered by company_id, week_start and topic the same way.

Loading without duplicates

ClickHouse recommends inserting in batches of at least 1,000 rows and ideally 10,000 to 100,000, because many small synchronous inserts create many parts, and it offers asynchronous inserts as a server-side buffer. The API reference caps a list page at 200 rows and an export page at 1,000, so collect several pages before each insert. Fokals files read with the CSVWithNames and JSONEachRow formats; the CSVWithNames page describes mapping columns by header and skipping unknown ones.

The table can be its own bookmark. Before each run, SELECT max(observed_at) FROM company_tech_events gives the since for the next call to the feed, so the loader needs no separate state store. The incremental sync use case covers the loop around it.

Resuming a feed from its last observed_at repeats rows at the boundary. ReplacingMergeTree removes them only during background merges, at a time you cannot plan, and the engine does not guarantee the absence of duplicates. Until a merge, read with FINAL or group by the key with an aggregate; the deduplication guide lists both and insert_deduplication_token, which stops a retried batch being inserted twice even though loaded_at defaults to now().

Keep two clocks. observed_at is when Fokals saw the change; loaded_at is when you did. Filter on observed_at for point-in-time questions and use loaded_at only for your own audit.

Where this design stops

A key serves the queries it was chosen for, and a new access path needs a projection or a second table, so test the product queries on a sample before loading everything. Fokals daily and weekly tables are not revised, so nothing in them needs updating in place. Keep intent_version as a column, and in the key of any cache or precomputed result, because a score under one version is not comparable with a score under another and a breaking change ships as a new version.

The cadence differs by table: marketing-stack changes arrive daily to weekly, signals daily, hiring daily and intent scores weekly. Show the observation date or the week, not a live indicator. Fokals is company-level data throughout, so every lookup keys on the company ID. For the filter and alert features built on them, see technographic filters in a prospecting tool and company change alerts.

Frequently asked questions

The queries and code on this page are examples to adapt. Test them in your own environment before you rely on them.

What this page says about the products it names was checked against their public documentation on 4 October 2026. Product and company names are trademarks of their owners. Fokals is not affiliated with them or endorsed by them.