Loading company data into BigQuery

This guide loads Fokals bulk files into BigQuery from Cloud Storage and keeps them easy to query: an explicit schema with JSON columns, day partitions, an insert-only MERGE that makes each daily load safe to repeat, and checks to run afterwards. Fokals is delivered direct, by REST API and as bulk files, which you load with BigQuery's own LOAD DATA from Cloud Storage. The examples use Hiring Activity (company_hiring_daily) and Job Postings (job_postings) from the hiring dataset, and the project, bucket and dataset names belong to Acme Robotics, an invented buyer, so treat them as illustrative.

What arrives and where it lands

Fokals files are CSV in UTF-8 with one header row, and lists and objects are JSON in a single cell, as the data dictionary states. The export endpoints return CSV, JSON or JSON Lines page by page for the period you name, and a packaged export of files comes with a manifest that names the period, label versions and licence. Keep the manifest, or a record of the period you requested, beside the files: it shows what each load contained.

BigQuery reads all of these. Google's CSV loading page says to remove any byte order mark, not to mix gzip and uncompressed files in one load job, and that CSV cannot carry nested or repeated data, which is why the object cells go into JSON columns. Its JSON loader reads newline-delimited JSON, which Google's page says is the same format as JSON Lines.

Create the bucket in the dataset's location. The CSV page says the bucket must be in the same location as the dataset, and the batch loading page says data transfer charges apply when they differ. The files come from the export or the API, so the first step is a copy into your bucket.

gcloud storage buckets create gs://acme-robotics-data --location=US
gcloud storage cp ./exports/company_hiring_daily_2026-10-03.csv \
  gs://acme-robotics-data/fokals/company_hiring_daily/2026-10-03/

Load a day into a staging table

LOAD DATA is a SQL statement that runs a load job. Google's reference says a failed statement leaves the target table unchanged, so LOAD DATA OVERWRITE into a staging table gives each run a clean start.

CREATE SCHEMA IF NOT EXISTS fokals OPTIONS (location = 'US');
CREATE SCHEMA IF NOT EXISTS fokals_stage OPTIONS (location = 'US');

LOAD DATA OVERWRITE fokals_stage.company_hiring_daily (
  company_id        STRING,
  company           STRING,
  isin              STRING,
  day               DATE,
  open_postings     INT64,
  new_postings      INT64,
  closed_postings   INT64,
  by_function       JSON,
  by_country        JSON,
  ai_postings       INT64,
  median_salary_usd FLOAT64,
  reconstructed     BOOL
)
FROM FILES (
  format = 'CSV',
  uris = ['gs://acme-robotics-data/fokals/company_hiring_daily/2026-10-03/*.csv'],
  skip_leading_rows = 1,
  source_column_match = 'NAME',
  allow_quoted_newlines = true,
  ignore_unknown_values = true
);

The schema lists the columns this guide uses, and you add the rest from the dictionary. source_column_match = 'NAME' reads the header row and reorders columns to match the schema, so a change in column order does not shift values. ignore_unknown_values skips a column your schema does not list instead of failing the job. allow_quoted_newlines admits cells that contain line breaks. Declare company_id as STRING, because it is an opaque stable key.

The object cells go into JSON columns. Google's JSON page says a batch load can fill a JSON column from CSV, Avro or JSON, and that a CSV cell holds the JSON as text with its quotes doubled. Load a sample first. If a cell fails, check how its quotes are escaped. After the first load, check whether empty text cells arrived as NULL or as empty strings, because Google's page gives the default null marker as the empty string, and normalise with NULLIF(column, '') if they arrived as empty strings.

Create day-partitioned tables

Partition the final table by day and cluster it by company_id, so a query for one company over a range of dates reads only the days it needs. Google's partitioning page says a qualifying filter on the partitioning column lets BigQuery skip the other partitions, and that daily partitions are the default for a DATE column. The same page lists partitions of under about 10 GB as a reason to consider clustering instead, and the page on creating partitioned tables shows how to partition by month with DATE_TRUNC. Measure one day of your own file before you choose. The page also says a dry run on a pruned query estimates its cost before the query runs. A JSON column cannot be a partition or clustering column, so the keys stay in ordinary columns.

CREATE TABLE IF NOT EXISTS fokals.company_hiring_daily (
  company_id        STRING NOT NULL,
  company           STRING,
  isin              STRING,
  day               DATE   NOT NULL,
  open_postings     INT64,
  new_postings      INT64,
  closed_postings   INT64,
  by_function       JSON,
  by_country        JSON,
  ai_postings       INT64,
  median_salary_usd FLOAT64,
  reconstructed     BOOL,
  loaded_at         TIMESTAMP
)
PARTITION BY day
CLUSTER BY company_id;

loaded_at is your own column. It records when you loaded each row, which is the time a point-in-time join needs and the dictionary does not supply. The require_partition_filter table option makes every query name the partitions it reads, which can reduce cost on tables that analysts query directly. The dictionary describes what each column means and not how it is stored, so the types here are choices: check them against a sample before you load a longer history.

Merge the day without duplicates

Daily and weekly rows are written once and never changed, so the merge only inserts. Matching on company_id and day makes a repeated load do nothing, and a constant day filter in the join condition lets BigQuery prune partitions: Google's page on DML with partitioned tables says the query optimiser attempts to use such a filter for that purpose. The staging query lists columns in the table's order, because INSERT ROW copies them by position. Put the day you are loading in both filters.

MERGE fokals.company_hiring_daily AS t
USING (
  SELECT
    company_id, company, NULLIF(isin, '') AS isin, day,
    open_postings, new_postings, closed_postings,
    by_function, by_country, ai_postings, median_salary_usd,
    reconstructed, CURRENT_TIMESTAMP() AS loaded_at
  FROM fokals_stage.company_hiring_daily
  WHERE day = DATE '2026-10-03'
) AS s
ON t.company_id = s.company_id
   AND t.day = s.day
   AND t.day = DATE '2026-10-03'
WHEN NOT MATCHED THEN
  INSERT ROW;

job_postings is different, because a posting changes after it is first seen: last_seen_at moves, and closed_at is set when it closes. Key on posting_id, keep one source row per posting, and update only when the incoming row is newer. Google's DML reference says a MERGE that updates returns a runtime error when several source rows match one target row, so the source is deduplicated first. The final table has the staging columns in the same order, plus loaded_at.

MERGE fokals.job_postings AS t
USING (
  SELECT *, CURRENT_TIMESTAMP() AS loaded_at
  FROM fokals_stage.job_postings
  WHERE posting_id IS NOT NULL
  QUALIFY ROW_NUMBER() OVER (PARTITION BY posting_id ORDER BY last_seen_at DESC) = 1
) AS s
ON t.posting_id = s.posting_id
WHEN MATCHED AND s.last_seen_at > t.last_seen_at THEN
  UPDATE SET last_seen_at = s.last_seen_at, closed_at = s.closed_at
WHEN NOT MATCHED THEN
  INSERT ROW;

That merge keeps the current state of each posting, not its history. If you need to know what you knew on a date, keep the staged files in the bucket, or append each load to a history table instead of updating in place.

The same two patterns cover the other tables. Insert only suits the tables that are written once, and a merge that updates suits a table whose rows change.

A table that records changes, such as Technology Changes (company_tech_events), has no single id in the dictionary. Choose the columns that identify one change, for example company_id, observed_at, category, key and change, and test them for uniqueness on a sample before you merge on them.

The first load is a backfill. Load every file into the staging table with a wildcard URI and run the merge once without the day filters. Because you loaded those days together, loaded_at is the backfill date for all of them. That is the correct value for a point-in-time join, because you did not hold those days earlier.

Query the JSON cells

The object cells hold {value: count} pairs. Google's JSON page says a JSON column is read with the field access and subscript operators and converted with functions such as INT64, and the keys of an object come from JSON_KEYS. Flatten the pairs into rows once, in a view, so that analysts do not repeat the work.

CREATE OR REPLACE VIEW fokals.hiring_by_function AS
SELECT
  company_id,
  day,
  job_function,
  INT64(by_function[job_function]) AS open_postings
FROM fokals.company_hiring_daily,
  UNNEST(JSON_KEYS(by_function, 1)) AS job_function;

Equality and comparison are not defined on JSON values, so extract a string or a number before you group or sort.

Schedule the load and check it

Run the load and the merge from the scheduler you already use. For a managed schedule on the load step, the BigQuery Data Transfer Service has a Cloud Storage connector. Its default write preference is APPEND, under which an unmodified file loads once and a file whose modification time changes loads again, and every file that matches the pattern must share the destination table's schema. Google lists a default repeat interval of 24 hours and a minimum of 15 minutes. With the MIRROR preference a transfer overwrites its destination on each run, which suits a staging table, and the merge runs after it. Google's pricing page states that batch loading from Cloud Storage is not charged by default, while storage is, in BigQuery and for the files you keep in Cloud Storage.

After each load, check that every key appears once and that the latest day is the day you expected.

SELECT company_id, day, COUNT(*) AS copies
FROM fokals.company_hiring_daily
WHERE day >= DATE '2026-09-01'
GROUP BY company_id, day
HAVING COUNT(*) > 1;

SELECT MAX(day) AS latest_day, COUNTIF(reconstructed) AS late_rows
FROM fokals.company_hiring_daily
WHERE day >= DATE '2026-09-01';

Keep the Fokals tables in a dataset of their own. Fokals is licensed by written agreement for internal use, embedding in a product or redistribution, and a dataset boundary gives you one place to grant, review and delete access that matches the agreement.

Pulling the API on a schedule, instead of downloading files, is covered in keeping a warehouse in step with incremental API sync. The guide to BigQuery sharing describes how that platform shares data between accounts, and the delivery page describes both ways Fokals is delivered.

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.