Feature engineering on company signals in Databricks

This guide shows how to turn Fokals daily and weekly tables into point-in-time feature tables in Databricks, join them to labels as of a date, and keep information that did not yet exist out of a training set. Fokals is delivered direct, by REST API and as bulk files in JSON, JSON Lines or CSV, which you load into your own Delta tables with Databricks's own loaders first. The guide to loading company data into Databricks covers that step, and the examples here start from the loaded tables.

Which clock a feature belongs to

A feature row has up to three times: the period it describes, the earliest moment you could have known it, and the moment you loaded it. A point-in-time join has to use the second. Joining on the first lets future information into the training set, which is look-ahead bias.

Fokals narrows the problem. Daily and weekly tables are written once, after the period closes, and are not revised, so a row never changes after the fact. What is left is choosing the column to key on. The table gives, for each dataset, the column that dates a row and the earliest moment the row can be used. Every table is described in the data dictionary.

These are lower bounds. The dictionary does not give the moment a row was written, so for a pipeline that runs forward, stamp every load with your own loaded_at and key on the later of the two times. That stamp is what makes your copy point-in-time data.

Two flags keep rows out of a training set. reconstructed is true for a period written more than seven days after it closed, so the row did not exist when the period ended. found_on_first_read marks a posting that was already on the board at its first observation, so its first_seen_at is not its opening date.

Build a feature table from the daily hiring table

The example uses Hiring Activity (company_hiring_daily) from the hiring dataset, loaded as fokals.silver.company_hiring_daily; your catalogue and schema names will differ. In Unity Catalog any Delta table with a primary key constraint can serve as a feature table, as the feature tables page states. For a time series feature table you add a date or timestamp column to the key and mark it TIMESERIES. Databricks documents two requirements, one timestamp key and no partition columns, and recommends no more than two primary key columns. The key here is company_id plus available_on, the day after the row's day.

CREATE TABLE IF NOT EXISTS fokals.features.hiring_momentum (
  company_id    STRING NOT NULL,
  available_on  DATE   NOT NULL,
  open_postings BIGINT,
  new_28d       BIGINT,
  closed_28d    BIGINT,
  ai_share      DOUBLE,
  CONSTRAINT hiring_momentum_pk PRIMARY KEY (company_id, available_on TIMESERIES)
);

MERGE INTO fokals.features.hiring_momentum AS t
USING (
  SELECT
    company_id,
    date_add(day, 1) AS available_on,
    open_postings,
    CASE WHEN count(*) OVER w = 28 THEN sum(new_postings) OVER w END    AS new_28d,
    CASE WHEN count(*) OVER w = 28 THEN sum(closed_postings) OVER w END AS closed_28d,
    ai_postings / nullif(open_postings, 0) AS ai_share
  FROM fokals.silver.company_hiring_daily
  WHERE NOT coalesce(reconstructed, false)
  WINDOW w AS (
    PARTITION BY company_id
    ORDER BY unix_date(day)
    RANGE BETWEEN 27 PRECEDING AND CURRENT ROW
  )
) AS s
ON t.company_id = s.company_id AND t.available_on = s.available_on
WHEN NOT MATCHED THEN INSERT *;

Three choices matter. Rows with reconstructed set are dropped before the window is computed, so a late row never feeds a later feature. The sums are null until a company has 28 daily rows in the window, because the first observation of a job board sets a baseline and writes no new postings: without the check, a company that has just entered coverage looks as if it stopped hiring. And the merge only inserts, because Databricks documents primary key constraints as informational and takes no action to enforce them, so a second load of the same day would add a duplicate key. Databricks also recommends liquid clustering on time series feature tables.

Weekly tables follow the same pattern with available_on set to date_add(week_start, 7). Intent scores need less work than counts. A weekly score in Intent Scores (company_intent_weekly) already uses only the signals of the 90 days to the week's end, and a surge is measured against the company's own previous twelve weeks, so use the score as a ready-made feature instead of recomputing it with a window that could look forward. Rows exist only for topics scoring at least 5, so for a covered company an absent intent row means a score below 5 and filling it with zero is fair; a missing hiring row means unknown.

Market context joins by date, not by company. The rows of Market Series (market_series) are dated by as_of, so filter one metric, dimension_kind = 'all' and one window_days, key the row on the day after as_of, and read unit before you compare its rate with a company-level count.

Join labels as of a date

A label has its own date. The illustrative label here is whether a company reports a private capital raise in the 90 days after an observation date, built from Company Funding (company_funding) in the intent dataset. The label window opens the day after the observation date, and the features end the day before it, so the two never overlap.

CREATE OR REPLACE TABLE fokals.labels.form_d_90d AS
SELECT
  o.company_id,
  o.as_of,
  max(CASE WHEN f.filed_at > o.as_of AND f.filed_at <= date_add(o.as_of, 90)
           THEN 1 ELSE 0 END) AS filed_90d
FROM (
  SELECT DISTINCT company_id, day AS as_of
  FROM fokals.silver.company_hiring_daily
  WHERE dayofweek(day) = 1  -- Sundays
) AS o
LEFT JOIN fokals.silver.company_funding AS f
  ON f.company_id = o.company_id AND f.form = 'D'
GROUP BY o.company_id, o.as_of;

The as-of join then takes, for each label row, the latest feature row that is not later than the observation date. In plain SQL it is a range join with a row number, and the 14-day limit stops an old value being carried forward:

SELECT l.company_id, l.as_of, l.filed_90d, f.available_on,
       f.open_postings, f.new_28d, f.closed_28d, f.ai_share
FROM fokals.labels.form_d_90d AS l
LEFT JOIN fokals.features.hiring_momentum AS f
  ON f.company_id = l.company_id
 AND f.available_on <= l.as_of
 AND f.available_on > date_sub(l.as_of, 14)
QUALIFY row_number() OVER (
  PARTITION BY l.company_id, l.as_of ORDER BY f.available_on DESC
) = 1;

Keep available_on in the joined output while you build the pipeline and assert that none of it is later than as_of. The join above guarantees that by construction, and the assertion catches the day someone edits the join to use day.

The Feature Engineering client does the same join for you. In its point-in-time documentation, timestamp_lookup_key names the label date and lookback_window excludes feature values older than the limit you set, so a company whose board stopped being read gets null instead of its last value indefinitely. Databricks states that a lookup does not skip rows with null feature values, so the null written for an unfilled window is returned, not replaced by an older value.

from datetime import timedelta
from databricks.feature_engineering import FeatureEngineeringClient, FeatureLookup

fe = FeatureEngineeringClient()
lookups = [
    FeatureLookup(
        table_name="fokals.features.hiring_momentum",
        feature_names=["open_postings", "new_28d", "closed_28d", "ai_share"],
        lookup_key="company_id",
        timestamp_lookup_key="as_of",
        lookback_window=timedelta(days=14),
    )
]
training_set = fe.create_training_set(
    df=labels,  # company_id, as_of, filed_90d
    feature_lookups=lookups,
    label="filed_90d",
    exclude_columns=["company_id", "as_of"],
)
training_df = training_set.load_df()

At scoring time, pass today's date as as_of. Databricks says the DataFrame given to score_batch must carry a timestamp column with the same name and type as the timestamp_lookup_key, and the model then retrieves its features with the same point-in-time lookup it was trained on, so training and scoring follow one rule. The client suits a model that is trained and scored in Databricks, because the overview says a model trained on Feature Store features tracks lineage to them and looks up feature values at inference. Plain SQL is enough for a one-off study, and it shows what the join does.

The Databricks Feature Store overview now describes two ways to author features: Feature Views, which it marks as Public Preview, and feature tables that you populate yourself. A Feature View computes features from source rows available before each label timestamp, using the timeseries_column you name, so the availability rule above applies to that column as well.

Leakage that is left

A correct join does not finish the job. These traps remain.

• Splits. Split labels by as_of before you build training sets, and leave a gap equal to the label horizon between the last training date and the first test date. Otherwise a 90-day label on a late training row looks into the test period.
• Label versions. Posting and website labels are model output under frozen version names such as jobs-v2, and a breaking change ships as a new version with at least 90 days' notice. Keep the version named in each export's manifest beside the load, and do not train on one version and score on another. See label version.
• Slow sources. Employee Headcount and Company News are dated by the event, not by the day it reached your data. Lag headcount by a margin you can defend, and key news on your load date.
• Absence. A missing row means unknown, not zero. A feature table that fills gaps with zero teaches a model that a company without hiring rows does not hire.
• Time travel. Delta Lake time travel reads your own table at an earlier version. Databricks says its point-in-time functionality is not related to time travel, and its table history page says to use only the past 7 days for time travel unless you raise both data and log retention. Keep an append-only copy of every load instead.

The use case on training and evaluating models on dated company signals covers the evaluation side.

How to read the labels

A posting records an intention to hire, and a hire is a separate event. Labels are model output under named, frozen versions, so measure a model against the version it was trained on. Company Funding records private capital raises reported in regulatory filings, so a label built from it describes reported raises. Every observation is dated and written once, which lets you choose a label horizon and match it to the period you have loaded.

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.