Exploring bulk company data with DuckDB

Before you license a dataset you can test it on the companies you track in an afternoon. This guide loads a Fokals sample file into DuckDB, reads it correctly and runs seven checks that show whether the data fits: the grain, the dates, the null rates, the JSON cells, the label versions, the baselines and which of the companies you track are present.

Fokals is delivered direct, by REST API and as bulk files in CSV, JSON or JSON Lines, which you read into DuckDB with its own readers. Sample data for the companies you track is sent on request, with the data dictionary and methodology. Where the production load goes is a separate decision. DuckDB's home page describes it as an analytical SQL database that can run in-process and reads formats such as CSV and JSON directly. Starting the command line client with duckdb opens an in-memory database, which is enough for a profile.

What a Fokals file looks like

The data dictionary sets the conventions every file follows.

• Files are CSV in UTF-8 with one header row. Lists and objects are JSON in a single cell. The API returns JSON Lines where a file would be CSV.
• Times are UTC. day is a closed UTC day and week_start is the Monday of a Monday-to-Sunday UTC week.
• Every file carries company_id, company and, when the company or its parent is listed, ticker, exchange, mic, isin, lei and figi.
• Daily and weekly rows are written once and never changed. reconstructed=true marks a period written more than seven days after it closed.
• The first observation of a website or job board sets a baseline and is never counted as a change.

Reading the file correctly

DuckDB's read_csv works out the dialect and the column types by sampling the file, and the CSV page lists the options that override it. Two facts matter here. The sniffer samples 20,480 rows by default and tries types in a fixed order, from NULL, BOOLEAN, TIME, DATE, TIMESTAMP, TIMESTAMPTZ and BIGINT to DOUBLE, with VARCHAR as the fallback, as its auto-detection page lists. And identifiers are labels, not numbers: a CIK such as the example 0000123456 in the API reference, or a ticker made only of digits, looks numeric and may be read as an integer, which loses its leading zeros. So read once as text, then again with the types you want.

-- Pass 1: every column as text, to see the values as they arrive
CREATE TABLE hiring_raw AS
SELECT * FROM read_csv('sample/company_hiring_daily.csv', all_varchar = true);

SUMMARIZE hiring_raw;

all_varchar = true skips type detection. SUMMARIZE then reports each column's approx_unique and null_percentage before any cast can hide a malformed value.

-- Pass 2: scan the whole file and keep identifiers as text
CREATE TABLE hiring AS
SELECT * FROM read_csv(
  'sample/company_hiring_daily.csv',
  sample_size = -1,
  types = {'ticker': 'VARCHAR', 'mic': 'VARCHAR', 'isin': 'VARCHAR', 'lei': 'VARCHAR', 'figi': 'VARCHAR'}
);

DESCRIBE hiring;

A JSON Lines file reads with read_ndjson, or with read_json and format = 'newline_delimited'. The JSON loading page says compression is detected from the file extension and that maximum_object_size defaults to 16,777,216 bytes. Several files read as one table when you pass a glob, with union_by_name = true to align columns by name and filename = true to keep each row's source file.

SELECT filename, count(*) AS row_count
FROM read_csv('sample/company_hiring_daily_*.csv', union_by_name = true, filename = true)
GROUP BY filename
ORDER BY filename;

Find faulty rows, do not skip them

For a profile, avoid ignore_errors = true, which skips the rows that fail to parse, the very rows you are looking for. The faulty files page names six structural errors, among them missing columns, too many columns, an unquoted value and a line longer than the default 2,097,152 bytes. It also describes store_rejects = true, which records each rejected row in a reject_errors table. Read the whole file once with it and query the table. An empty reject_errors after a full read is evidence that the file is well formed.

CREATE TABLE postings_raw AS
SELECT * FROM read_csv('sample/job_postings.csv', all_varchar = true, store_rejects = true);

FROM reject_errors;

Seven checks before you license

Each check is a query and a reading. The examples use Hiring Activity (company_hiring_daily) and Job Postings (job_postings), loaded as hiring and postings in the same two passes; the same shapes fit the other tables.

1. Grain and join keys. A daily table has one row per company and closed UTC day. An empty result is the pass; any row is a delivery fault to raise before you buy. Then look at the identifier you will join on: a brand or subsidiary carries the identifiers of its listed parent, so several company_id values can share one isin. That is by design, and when you join to holdings you sum by isin and day rather than expecting one row.

SELECT company_id, day, count(*) AS copies
FROM hiring
GROUP BY company_id, day
HAVING count(*) > 1;

SELECT isin, count(DISTINCT company_id) AS company_ids
FROM hiring
WHERE isin IS NOT NULL
GROUP BY isin
HAVING count(DISTINCT company_id) > 1;

2. Period. day should span the period you asked for and end close to the date the file was made. A gap between the first and last day is a question for Fokals. Count the rows flagged reconstructed too: a period written more than seven days after it closed was not available on the days it describes, which matters for back-tests.

SELECT min(day) AS first_day, max(day) AS last_day,
       count(DISTINCT day) AS days_present,
       date_diff('day', min(day), max(day)) + 1 AS days_expected,
       count(*) FILTER (WHERE reconstructed) AS reconstructed_rows
FROM hiring;

3. Presence and match rate. The sample covers the companies you track, and each table holds the companies its dataset describes: a company appears in Hiring Activity where it publishes roles, and in Company News where it announces or files. List the companies absent from a table and read each absence against the coverage page before you treat a gap as a defect.

SELECT u.isin, u.name
FROM read_csv('my_companies.csv', all_varchar = true) AS u
WHERE NOT EXISTS (SELECT 1 FROM hiring AS h WHERE h.isin = u.isin);

Then measure which of your identifiers does the matching. The dictionary says to join to market data on isin, figi or ticker with mic. If your records hold websites instead, match on domain in Technology Stack. The distinct subqueries below stop a shared isin from multiplying your rows.

SELECT count(*) AS total,
       count(i.isin) AS by_isin,
       count(f.figi) AS by_figi,
       count(t.ticker) AS by_ticker_and_mic
FROM read_csv('my_companies.csv', all_varchar = true) AS u
LEFT JOIN (SELECT DISTINCT isin FROM hiring) AS i ON i.isin = u.isin
LEFT JOIN (SELECT DISTINCT figi FROM hiring) AS f ON f.figi = u.figi
LEFT JOIN (SELECT DISTINCT ticker, mic FROM hiring) AS t ON t.ticker = u.ticker AND t.mic = u.mic;

4. Nulls and allowed values. SUMMARIZE hiring returns null_percentage for every column. Nulls in company_id or day would be a fault. A high null rate in median_salary_usd is expected, because advertised pay exists only where a company publishes it. For columns with a fixed list of values, count what arrives and compare it with the dictionary: work_mode is onsite, hybrid, remote or empty.

SELECT work_mode, count(*) AS postings
FROM postings
GROUP BY work_mode
ORDER BY postings DESC;

5. JSON cells. Objects such as by_function arrive as JSON text. Check that they parse, then pull a key out with the functions on the JSON functions page.

SELECT
  count(*) FILTER (WHERE by_function IS NOT NULL AND NOT json_valid(by_function)) AS unparsable,
  sum(json_extract_string(by_function, '$.software_engineering')::INTEGER) AS engineering_postings
FROM hiring;

6. Labels and versions. Postings are labelled by an evaluation model under a named version, and an empty choice means the model was not confident enough. Measure both on job_postings. jobs-v1 rows carry fewer labels than jobs-v2 rows and postings keep their version until they close, so a mixed result is expected.

SELECT label_version,
       count(*) AS postings,
       avg((coalesce(json_extract_string(labels, '$.job_function'), '') = '')::INTEGER) AS share_without_function
FROM postings
GROUP BY label_version;

7. Baselines. A posting already on a board at its first observation is marked found_on_first_read, and its first_seen_at is the date of that observation, not its opening date. Count them and find the earliest sighting. Counts of new postings measure from the days after the baseline. In an illustrative case, Acme Robotics shows 31 open postings on the first day of its file and none new: all 31 belong to the baseline, so the zero is the baseline at work, not a hiring freeze.

SELECT found_on_first_read, count(*) AS postings, min(first_seen_at) AS earliest_seen
FROM postings
GROUP BY found_on_first_read;

Run it again on delivery

Save the queries in a file such as profile.sql and run them with .read profile.sql; .mode markdown and .output shape and redirect the results, as the command line page lists. When the licensed files arrive, run the same file and compare. The grain should still hold, first_day and last_day should match the period on the manifest that comes with a bulk export, which names sources, period, label versions and licence, and the label versions should be the ones the methodology names. The sample shows the shape of the data and the profile shows whether a delivery kept it.

If you will query a table repeatedly, you can write it out in another format. The COPY page shows COPY ... TO with FORMAT parquet, a conversion you run in DuckDB after the load; the trade between the three formats is in CSV, JSON Lines and Parquet compared.

COPY (SELECT * FROM hiring) TO 'company_hiring_daily.parquet' (FORMAT parquet);

Where a sample stops

A profile of a sample says whether the data suits the companies you track; the delivered files show how it behaves over time. SUMMARIZE reports approximate quantiles, as its page says, and an approximate unique count in approx_unique. Presence by table tells you about your own list, not about the index: the hiring dataset page says what the hiring tables hold, and the coverage page describes the company index and its refresh schedule. Whether the sourcing passes your review is a separate question, answered in the sourcing statement, and the ten questions for a company data vendor give a checklist that applies to any vendor.

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.