Loading company data into Amazon Redshift

This guide loads Fokals company data from files in your own Amazon S3 bucket into Amazon Redshift. It gives a table definition, a COPY for JSON Lines and one for CSV, the use of the SUPER type for JSON cells, and a daily load that can run twice without duplicating a row. Fokals is delivered direct, by REST API and as bulk files, which you load with Amazon Redshift's own COPY from S3. The examples use five datasets, Hiring Activity, Sales Team Metrics, Market Series, Job Postings and Technology Stack, and they are illustrative: Acme Robotics keeps its files under s3://acme-robotics-data/fokals/ and loads them into its own Redshift database.

From the export to a bucket you own

A table is exported as CSV, JSON Lines or JSON, page by page through the export endpoints or as a package of files with a manifest that names the period, the label versions and the licence. A job of yours downloads the export, or pages the API from the last cursor you stored, and copies the files to S3 under one prefix for each table and period, for example fokals/company_hiring_daily/dt=2026-10-03/. The delivery page and the API reference describe both routes.

Three rules save time later. COPY loads every object under the prefix you give it, so keep the Fokals manifest under another prefix, such as fokals/manifests/, where it is not read as data. Redshift has a COPY manifest of its own, a list of file addresses, which is a different thing and is useful only when a prefix would match files you do not want. Keep the bucket and the cluster in one Region, or add the REGION option, for which the COPY documentation says transfer charges apply. Finally, attach to the cluster or serverless namespace a role that can read the bucket, and give the loading user the INSERT privilege on the table.

Choose JSON Lines when you have the choice. With FORMAT JSON 'auto' COPY matches each key to the column of the same name, puts nested objects and arrays into SUPER columns and skips keys that have no column, as the page on loading SUPER columns describes. CSV carries no keys: COPY fills columns by position, so your table must hold every column of the file in the order of its header row. In both formats a list or an object is JSON, which is what the SUPER type holds.

The tables and the keys

Every row carries company_id and, when the company or its parent is listed, ticker, exchange, mic, isin, lei and figi. Join Fokals tables to each other on company_id, and to market data on isin, figi or ticker with mic; the guide to joinable identifiers covers the options. Each table needs its own rule for repeated loads, because the data dictionary describes their rows differently.

The written-once tables never need an update, but a load can still be run twice by mistake, and Redshift does not stop it: COPY appends rows, and the CREATE TABLE page describes primary key constraints as informational and not enforced. The daily load below therefore goes through a staging table. Load periods oldest first, as the feeds run, so that a replaced row is never overwritten by an older version of itself.

Create the table and load one day

This definition follows the data dictionary for Hiring Activity. The count columns are integers, and the columns that hold {value: count} objects are SUPER.

create table company_hiring_daily (
  company_id           varchar(64),
  company              varchar(512),
  ticker               varchar(32),
  exchange             varchar(16),
  mic                  varchar(8),
  isin                 varchar(12),
  lei                  varchar(20),
  figi                 varchar(32),
  day                  date,
  open_postings        integer,
  new_postings         integer,
  closed_postings      integer,
  by_function          super,
  by_seniority         super,
  by_country           super,
  by_work_mode         super,
  ai_postings          integer,
  paid_media_postings  integer,
  tech_mentions        super,
  median_salary_usd    double precision
)
distkey (company_id)
sortkey (day);

Load one day from its prefix:

copy company_hiring_daily
from 's3://acme-robotics-data/fokals/company_hiring_daily/dt=2026-10-03/'
iam_role 'arn:aws:iam::111122223333:role/FokalsLoad'
format as json 'auto';

A SUPER value in a CSV file is serialised JSON with standard CSV quoting, which COPY reads with the CSV format. Skip the header row and make the table follow the header exactly:

copy company_hiring_daily
from 's3://acme-robotics-data/fokals/company_hiring_daily/dt=2026-10-03/'
iam_role 'arn:aws:iam::111122223333:role/FokalsLoad'
format as csv
ignoreheader 1;

To keep every key, including any your typed table has no column for, add a second table with one SUPER column and load the same files into it with FORMAT JSON 'noshred'. You can shred it with SQL later.

Two limits apply to the files. One input row, or one JSON object, can be at most 4 MB. JSON files and compressed CSV files are not split for parallel loading, so for a large table such as Job Postings cut them into files of similar size, between 1 MB and 1 GB compressed, in a number that is a multiple of the slices in your cluster, as the guidance on loading data files sets out. Times in Fokals files are UTC and ISO 8601, and the TIMEFORMAT 'auto' option recognises that form, so use it for the timestamptz columns of job_postings. In that table locations, labels, sales_facts and sales_labels are the SUPER columns.

Load every day without duplicates

A rerun after a half-finished night would double the day. Load into a temporary staging table with the same columns, and merge it in one transaction, which is the pattern in the AWS guidance on updating and inserting data. The simplified MERGE below replaces a matching row and inserts the rest. It needs a source and a target with the same columns in the same order, which create table ... (like ...) provides.

create temp table stage (like company_hiring_daily);

begin;

copy stage
from 's3://acme-robotics-data/fokals/company_hiring_daily/dt=2026-10-03/'
iam_role 'arn:aws:iam::111122223333:role/FokalsLoad'
format as json 'auto';

merge into company_hiring_daily
using stage
on company_hiring_daily.company_id = stage.company_id
and company_hiring_daily.day = stage.day
remove duplicates;

commit;

drop table stage;

For Job Postings and Technology Stack use the same statements with the merge keys from the table above. MERGE cannot match on part of a SUPER column, so key on scalar columns, as here. If you prefer explicit statements, delete the target rows that match the staging table through a join and then insert the whole staging table, in the same transaction, which is the other form the AWS guidance describes.

If a table is written once, an auto-copy job can replace the nightly script. According to the COPY JOB page, COPY ... JOB CREATE ... AUTO ON loads each new object under a path once, tracking files by name. It needs an S3 event integration between the bucket and the warehouse, it skips files that were already there when the job was created, it accepts neither MAXERROR nor a COPY manifest, and a file delivered again under the same name is not loaded again. Tables that replace rows stay with staging and merge.

After each load, check what was read. On a provisioned cluster STL_LOAD_COMMITS lists the files loaded and STL_LOAD_ERRORS the rows refused. The SYS_LOAD_HISTORY and SYS_LOAD_ERROR_DETAIL views also cover serverless namespaces. Then add a line to a small load log of your own: the prefix, the period and the label versions that the manifest names, the time of the load and the row count. It records what each load contained and under which label versions it was produced.

Query the JSON columns

SUPER values are navigated with dots and brackets, and an object becomes rows with UNPIVOT. This query lists the job functions a watchlist is hiring in on one closed day, with AT naming the key and AS the count:

select h.company, job_function, cnt::int as open_postings
from company_hiring_daily h, unpivot h.by_function as cnt at job_function
where h.day = '2026-10-03'
  and h.company_id in (select company_id from watchlist)
order by open_postings desc
limit 20;

Arrays are unnested in the FROM clause. This one counts open postings by country from the locations list of job_postings, one row for each posting and location:

select loc.country::varchar as country, count(*) as open_postings
from job_postings p, p.locations as loc
where p.closed_at is null
group by 1
order by 2 desc
limit 10;

Labels live in the labels object, and they are produced under frozen versions named in label_version. This query groups open postings by version and job function:

select p.label_version, p.labels.job_function::varchar as job_function, count(*) as open_postings
from job_postings p
where p.closed_at is null
group by 1, 2
order by 3 desc
limit 10;

Three behaviours of SUPER matter here. A path that does not exist returns NULL instead of an error, so a misspelt key looks like a missing count. A cast that does not fit, such as a string to an integer, also returns NULL. And Redshift reads attribute names case-insensitively unless enable_case_sensitive_super_attribute is on, which the documentation for SUPER recommends. The first behaviour has a data counterpart: jobs-v1 rows carry only the first two label choices and six flags, so a label that a version does not carry reads as NULL, which is not the same as unspecified.

Reading the rows

Hiring Activity carries a row for each company with published postings on each closed day. Join from your watchlist with a left join and keep the NULL, because a missing row is not the same as zero open postings. The first observation of a company sets a baseline, and new_postings leaves it out. When you count openings from Job Postings, exclude rows where found_on_first_read is true, because their first_seen_at marks the baseline and not an opening date. Every row is dated and written once, so the history you load is point-in-time. The incremental pattern is the same for every table; the guide to keeping a warehouse in step with incremental API sync covers the cursor side, and the guide to querying the same files with Athena covers the route that needs no load.

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.