Keeping a warehouse in step with incremental API sync

A daily incremental sync moves only what is new, can run twice without harm and resumes where it stopped after a failure. It takes five decisions: what the bookmark is, which key each table is upserted on, how fast to read, what a first load does differently from a daily run, and which checks tell you the pipeline has gone quiet. This guide makes them for a warehouse that receives Fokals data through the REST API, and says where the bulk exports are the better route.

It is written for the engineer who owns the load. The loading syntax differs between warehouses, so the SQL below is generic and you will adapt the dialect.

Three kinds of table, three ways to load

The data dictionary describes the grain of every Fokals table, and the grain decides how a repeated pull behaves.

• Closed periods. Hiring Activity (company_hiring_daily), Sales Team Metrics (company_sales_weekly), Intent Scores (company_intent_weekly) and Market Series (market_series) hold one row for each company and closed period, or for each series and as-of date. A row is written once after its period closes and never changed, so loading it twice is harmless.
• Events. Technology Changes (company_tech_events), Company Signals (company_signals), Company News (company_news) and Company Funding (company_funding) record something that happened, each with the time it was observed. The first observation of a website sets a baseline and writes no events.
• State. Job Postings (job_postings), Technology Stack (company_technologies) and the listed securities of the company index (listed_securities) describe how something stands now. A posting gains a closed_at, a technology gains a missing_since, a listing becomes delisted. The later row replaces the earlier one.

Give each table the key that matches its grain.

Each key follows the dictionary's description of the grain: one row per company and closed day, per company, topic and closed week, one listing per FIGI. The dictionary names no id column for events and signals, so those keys are composed from the columns that describe them. Count rows against distinct keys on a sample before you trust one.

The bookmark

Feeds use cursor pagination and run oldest first from a time you choose. A client that walks forward and keeps the last cursor it holds therefore has a bookmark that survives a restart. Check in the API reference that the endpoint you sync runs oldest first, because a list that runs newest first cannot be walked forward from a bookmark. Keep one row per feed.

create table sync_state (
  feed        text primary key,
  start_time  timestamptz not null,
  cursor      text,
  updated_at  timestamptz not null default now()
);

Three rules protect it. Save it in the same transaction as the rows it covers, so a crash cannot advance one without the other. Treat a cursor as valid only for the request that produced it, and start again from start_time if you change the endpoint or a filter. Keep the cursor you held when the feed ran dry: the next run re-reads at most one page, and the upsert absorbs it.

The loop is short. In this sketch fetch_page stands for one API request, and its parameters are the ones the API reference lists.

def run(feed):
    state = load_state(feed)              # state.cursor is None on the first run
    cursor = state.cursor
    while True:
        rows, next_cursor = fetch_page(feed, state.start_time, cursor)
        with warehouse.transaction():
            upsert(feed, rows)
            if next_cursor:
                save_cursor(feed, next_cursor)
        if not next_cursor:
            return
        cursor = next_cursor

Upserts that can run twice

Load each page into a staging table and merge it, so that a page which fails halfway leaves nothing behind. A closed day or an event is a record of something already seen, so merge it with do nothing on conflict, and confirm on your sample that a repeated row comes back identical. Rows that describe a state, such as a posting, replace the earlier version.

-- closed period or event: a repeat changes nothing
insert into company_hiring_daily
select * from stage_company_hiring_daily
on conflict (company_id, day) do nothing;

-- state: the later row describes the posting as it now stands
insert into job_postings
select * from stage_job_postings
on conflict (posting_id) do update set
  last_seen_at  = excluded.last_seen_at,
  closed_at     = excluded.closed_at,
  labels        = excluded.labels,
  label_version = excluded.label_version;

Lists and objects arrive as JSON, in a single cell of a CSV file: labels, by_function, tech_mentions, evidence, detail. Land them in a JSON column and expose the keys you use through a view. The keys vary by label version, because jobs-v1 postings carry fewer labels than jobs-v2, so a new key then changes the view and leaves the table alone. Times are UTC: store them as UTC timestamps, and keep day and week_start as dates so that a warehouse in another time zone cannot shift a day.

Keep label_version and reconstructed as columns. The first says which frozen version produced a label, so a new version arrives beside the old one instead of replacing it unseen. The second is true for a period written more than seven days after it closed, and that is the case that matters for a bookmark: such a period carries a day earlier than rows you already hold. A gap check, described below, is how you know that you hold every period. The guide to point-in-time data for back-tests covers why these tables are written once.

Pace against the rate limits

Each key has a limit by the minute and a limit by the day. Plan against both as a budget instead of meeting them as errors.

1. Estimate the requests before a backfill: the rows to load divided by the page size, summed over the feeds. If the total exceeds the daily limit on your key, spread the backfill over several days or load the early periods from the bulk exports.
2. Pace by the minute limit, with a margin. A loop that runs flat out reaches the limit within seconds and then fails in ways that are hard to read in a log.
3. On a refusal, wait and retry with a growing pause. When the daily limit is spent, stop, keep the cursor and let the next run continue.
4. Do not run a backfill and the daily job on one key at once, because they draw on one budget. Run the backfill first, or give the daily job priority and let the backfill use what is left.

Backfill and daily run

A backfill is the daily run started a long way back. Both read the same feeds and write through the same upserts, and they differ in where they start and what they load first.

Backfill. Set start_time to the first period you need. Walk each feed from start_time and save the cursor after every page, so that a failure costs one page, or load the early periods from the bulk exports in slices of a week or a month. Load the state tables for the companies you track before the events, because events say what changed after a baseline and not what was there. When you add companies to your list later, repeat that for them: a company's first observation sets a baseline and writes no events, so its first weeks carry state and changes begin after the baseline.

Daily run. Run after the UTC day has closed, and pull everything after the bookmark instead of asking for yesterday by name. A job that asks for one named day fails the first time that day is late, while a job that asks for everything after the bookmark finds it on the next run. Weekly tables need no job of their own: company_intent_weekly and market_series appear after their Monday to Sunday UTC week closes, and the daily run collects them when they exist.

Checks that catch a silent failure

A load that succeeds and returns nothing looks healthy, so alert on lag as well as on errors.

select
  max(day)                    as latest_day,
  current_date - 1 - max(day) as days_behind,
  count(distinct day)         as days_held,
  max(day) - min(day) + 1     as days_spanned
from company_hiring_daily;

A days_behind above two, or days_held below days_spanned, means a missing period. Request it again by its dates from the bulk export for a period, and run the same check on weekly tables against week_start. For files, keep the manifest with the load: it names the period, label versions and licence, so comparing its period with the one you asked for catches a short export. A count of rows by label_version shows when a new version reaches your data. A breaking change ships as a new version with at least 90 days' notice, so it should never be a surprise.

Scheduling and monitoring

The data is delivered direct: you pull the API and the bulk exports on a schedule you set, so scheduling, retries and monitoring sit in your pipeline, where your own alerting reaches them. Datasets are refreshed daily or weekly, so one run a day keeps the closed-period datasets current, and the checks above tell you when a period is missing.

How Fokals delivers it

The REST API has 25 endpoints and returns JSON, with one bearer key per client carrying scopes, limits by the minute and by the day, and cursor pagination. The bulk exports return JSON, JSON Lines or CSV with a manifest. Both are described on the delivery page, and the guide to API versus bulk files sets out when each fits. A sync built on feeds is also the base of the change alerts a product shows its users, which add deduplication and wording on top of the bookmark.

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.