A vendor sends files; your data lakehouse needs tables. This guide shows how to turn the bulk exports of a company-data vendor into Apache Iceberg tables: how to partition them by day, how to load a day twice without duplicating it, and how to use Iceberg's time travel without confusing it with the vendor's own rule that a period is written once. The worked tables are the Fokals tables.
Fokals is delivered direct, by REST API and as bulk files in CSV, JSON or JSON Lines, through export endpoints that the API reference describes. You read the exports with your engine's own loader and write them as Iceberg tables, which gives you the table format, the partitioning and the file layout you choose.
Why an open table format for vendor data
The Apache Iceberg site describes Iceberg as an open table format for analytic datasets and says that engines such as Spark, Trino, Flink, Presto, Hive and Impala can work with the same tables at the same time. Four of its documented properties matter for licensed data.
- Many engines, one copy. A researcher's notebook and a data product can read the same table, so there is one set of files to govern. For the file-based alternative, see querying company data on S3 with Athena.
- Schema changes without rewrites. The evolution page lists add, drop, rename, widen and reorder as changes that rewrite no files, because Iceberg tracks columns by unique id. Vendor schemas grow: the Fokals API reference says fields are added within a version without notice.
- Hidden partitioning. Iceberg derives partition values from a column, and the partitioning page says consumers need not know how the table is partitioned or add extra filters.
- Atomic commits. The table specification says reads are isolated from concurrent writes and writes are never partially visible, so a day's load appears whole or not at all.
Iceberg does not make data point-in-time and does not carry a licence. The sections below cover the partitioning and loading choices first, then those two limits.
Partition by the vendor's clock
A partition should follow the time the vendor puts on the row, not the day you loaded it, so a late load or a backfill lands where a query for that day will look. The data dictionary names the time column and the grain of each table, and the grain is the key a merge needs.
| Fokals dataset | Time column | Partition | Merge key |
|---|---|---|---|
Technology Changes (company_tech_events), Company Signals (company_signals) | observed_at, a timestamp | day(observed_at) | id on the REST feeds; on files, a tuple you have tested |
Company News (company_news) | at, a timestamp | day(at) | a tuple you have tested |
Hiring Activity (company_hiring_daily) | day, a closed UTC day | the day column itself | company_id, day |
Sales Team Metrics (company_sales_weekly) | week_start, a Monday | the week_start column itself | company_id, week_start |
Intent Scores (company_intent_weekly) | week_start, a Monday | the week_start column itself | company_id, topic, week_start |
Market Series (market_series) | as_of, a Sunday | the as_of column itself | as_of, metric, dimension_kind, dimension, window_days |
The statements below use Spark SQL, the dialect the Iceberg documentation uses for its examples, with the Iceberg SQL extensions that its writes and DDL pages require for MERGE INTO and ADD PARTITION FIELD. The DDL page lists day(ts) among the partition transforms, so a query on observed_at prunes day partitions with no extra column.
CREATE TABLE lake.fokals.company_tech_events (
id bigint,
observed_at timestamp,
company_id string,
domain string,
category string,
`key` string,
change string,
`before` string,
`after` string,
loaded_at timestamp)
USING iceberg
PARTITIONED BY (day(observed_at));
CREATE TABLE lake.fokals.company_hiring_daily (
company_id string,
day date,
open_postings int,
new_postings int,
closed_postings int,
by_function string,
median_salary_usd double,
reconstructed boolean) -- remaining columns as in the data dictionary
USING iceberg
PARTITIONED BY (day);Objects and lists such as by_function arrive as JSON in a single cell, so they stay string here and are parsed when read. Day is a starting point, not a commitment. Small daily partitions mean small files, and the maintenance page describes rewriteDataFiles as combining small files into larger ones. The evolution page also says that when a partition spec changes, old data keeps its old layout and new data uses the new one, so a table can move from days to months later without a rewrite.
ALTER TABLE lake.fokals.company_tech_events ADD PARTITION FIELD month(observed_at);
ALTER TABLE lake.fokals.company_tech_events DROP PARTITION FIELD day(observed_at);Fokals files carry CSV and JSON text, while Iceberg's specification describes data files in Parquet, Avro or ORC. The conversion is yours, and CSV, JSON Lines and Parquet compared sets out the trade.
Load a day twice without duplicating it
Fokals writes a daily or weekly row once, after the period closes, so the loader's job is to add keys it has not seen. The writes page says MERGE INTO replaces only the affected data files and is recommended over INSERT OVERWRITE, partly because the data a dynamic overwrite replaces can change if the partitioning changes. A merge that only inserts is also what write-once means: run it again and it adds nothing.
MERGE INTO lake.fokals.company_hiring_daily t
USING staging.company_hiring_daily s
ON t.company_id = s.company_id AND t.day = s.day
WHEN NOT MATCHED THEN INSERT *;A row that matches on the key but differs in value is not an update to apply, because the vendor does not revise a closed period. Treat it as an incident to investigate, not a row to overwrite: either your pipeline changed it or the file is not the one you think it is. For event tables the REST feeds carry an integer id to merge on. The files list no unique column for them in the dictionary, so test a tuple on your sample, starting from the page order keys the API reference lists for each dataset, and confirm that no two rows share it before you merge on it.
From export to table
Put the steps in order and run them for closed periods only, because a daily or weekly row is written after its period closes.
- Call the export endpoint for the dataset with
fromandtoand aformatofcsvorjsonl, and walk the pages until the cursor header is absent. Keep the manifest that comes with the delivery, which names sources, period, label versions and licence. - Read the files into a staging table with types from the dictionary: dates as dates, identifiers as strings, JSON cells as strings.
- Run the merge on the table's key.
- Check the load against the manifest: the first and last
dayorweek_startmatch the period, and no key appears twice. - Record the snapshot id next to the manifest name in your own load log, so a later question about what a run saw has a precise answer.
Time travel is not point-in-time
Iceberg records the state of a table as snapshots. The queries page shows TIMESTAMP AS OF and VERSION AS OF reading a table as it stood at a snapshot, and the .snapshots and .history metadata tables list them.
SELECT count(*) FROM lake.fokals.company_hiring_daily TIMESTAMP AS OF '2026-09-30 06:00:00';
SELECT * FROM lake.fokals.company_hiring_daily.snapshots;There are two clocks, and Iceberg keeps only one. Time travel answers what your table held when you ran a model: that is your commit clock. The vendor's rule answers whether a period was ever revised, and Fokals daily and weekly rows are written once and never changed, with the time of observation on every row. A point-in-time question therefore filters on the vendor's time columns, the observation time, the first-seen date, the day or the week start, and does not depend on a snapshot. Use snapshots to reproduce a past run and to roll back your own mistake; the write-once design behind Fokals tables explains the vendor side.
Two cautions follow. First, expiring snapshots removes them from metadata, so they are no longer available for time travel, and the maintenance page recommends expiring regularly to delete files that are no longer needed. To keep one for audit, tag it: the branching page says tags retain important historical snapshots for auditing, with a retention you set.
ALTER TABLE lake.fokals.company_hiring_daily
CREATE TAG `model-2026-10` AS OF VERSION <snapshot_id> RETAIN 365 DAYS;Second, removal. A DELETE FROM leaves earlier snapshots in place, so time travel can still read the deleted rows until those snapshots are expired. If your agreement requires you to remove data, delete the rows and expire the snapshots, and read the agreement for what it requires.
What the format cannot know
Schema evolution adds a column without rewriting files, but it cannot tell you that a value changed meaning. Fokals labels and scores are produced under named, frozen versions, and a breaking change ships as a new version with at least 90 days' notice. Keep the label version and the intent version as columns and filter on them in every view you publish. Keep the flag that marks a period written more than seven days after it closed, and the flag that marks a posting already published at its first observation, which was never seen opening.
An open format is not an open licence. Because many engines can read one table, who may read it becomes a decision you make deliberately, while Fokals is licensed by written agreement for internal use, embedding in a product or redistribution. Grant access to the people and services your agreement covers; what a data licence covers lists the questions to settle. The data dictionary is the reference for every type and key used above.
Frequently asked questions
Why use Apache Iceberg for vendor data files?
Iceberg is an open table format, and its documentation says several engines can work with the same tables at the same time. For vendor data that means one governed copy, schema changes without rewriting files when the vendor adds a field, partitioning by a time column without extra filters, and commits that readers never see half done. It does not replace a licence or point-in-time columns.
How should I partition Iceberg tables for daily company data?
Partition by the time the vendor puts on the row, such as day(observed_at) for events or the day column for a daily table, not by load date. Queries on that column then prune partitions without an extra filter. If daily partitions are small, compact files with rewriteDataFiles or move to month(observed_at), which applies to new data only.
Is Iceberg time travel the same as point-in-time data?
No. Time travel reads a table as it was at one of its snapshots, which is the state of your copy at your commit time. Point-in-time data lets you ask what was known on a date, which needs the vendor's observation times on each row and tables the vendor never revises. Snapshots also disappear when expired unless a tag retains them.
How do I load the same file twice into Iceberg without duplicates?
Use MERGE INTO on the table's key with WHEN NOT MATCHED THEN INSERT *. The Iceberg documentation recommends MERGE INTO over INSERT OVERWRITE because it replaces only affected files and does not depend on the partitioning staying the same. Running the same merge a second time inserts nothing, and a matched row whose values differ should be investigated, not overwritten.
How do I get Fokals data into Iceberg tables?
Fokals is delivered direct, by REST API and as bulk files in CSV, JSON or JSON Lines. Call the export endpoint for a dataset and a period, read the files with your engine's loader into a staging table, and merge them into an Iceberg table partitioned by the vendor's time column. The manifest that comes with each delivery names the period, the label versions and the licence.
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.