How to date company attributes to see a historical record (SCD type 2)

This guide shows how to date company attributes so you can see the historical record of any company as it stood on a past date. The design is a slowly changing dimension of type 2 (SCD type 2) in your own warehouse: each attribute value carries an effective-from and an effective-to date, a change closes one row and opens the next, the merge can run twice without harm, and the date a fact was true stays apart from the date you learned it. The queries build the history from the dated observations Fokals delivers. Reading changes from the API is covered in keeping a warehouse in step with incremental API sync, and why the source tables are never revised in why we write each table once. This page starts where both end.

How to date company attributes so you can see the historical record (SCD type 2)

Give each attribute value its own row with a valid-from and a valid-to date, and never overwrite it. A change closes the open row at the time of the change and inserts a new row from that time. The record on any past date is the set of rows whose period contains that date.

Databricks describes the two common variants in its AUTO CDC documentation: type 1 updates records directly and keeps no history of updated records, while type 2 keeps a complete history by creating a new row for each version of a record. For company data, an attribute is anything an enrichment writes to an account: a technology on the website, a market the site serves, a stated headcount.

Use half-open periods. A row is in effect from its valid-from time, inclusive, up to its valid-to time, exclusive, and the current row has no valid-to. Two rows of one attribute then never both match a date, and the as-of query is a single condition. It is the condition SQL Server applies for FOR SYSTEM_TIME AS OF: start at or before the time, end after it.

-- the record as it stood on 1 April 2027
select company_id, attribute, value
from company_attribute_history
where valid_from <= timestamp '2027-04-01 00:00:00'
  and (valid_to is null or valid_to > timestamp '2027-04-01 00:00:00');

Temporal table design to track historical changes with an effective date and an end date

Key the history table on the company, the attribute and the effective date, and keep the end date empty while the row is current. Add the time your pipeline recorded each row and the time it closed it. Derive each end date from the next row's effective date, never from the clock.

create table company_attribute_history (
  company_id   varchar(36)  not null,  -- the Fokals company ID
  attribute    varchar(200) not null,  -- 'technology:hubspot', 'market:DE'
  value        varchar(200),
  valid_from   timestamp    not null,  -- when the value took effect, as the source dates it
  valid_to     timestamp,              -- empty while the row is current
  loaded_at    timestamp    not null,  -- when your pipeline recorded the row
  closed_at    timestamp,              -- when your pipeline set valid_to
  source_table varchar(50)  not null,
  primary key (company_id, attribute, valid_from)
);

Three properties follow. The key includes the effective date, so a version loaded twice is found already there. The end date comes from the order of versions, so a version that arrives late slots in between two others. And the two times you add, loaded and closed, record what your table knew and when, which no date in the source can tell you.

Warehouses offer parts of this, and each dates one kind of time.

SQL Server lists maintaining a slowly changing dimension among the uses of its temporal tables. Its period columns record when your database changed, so they suit the recorded time of the design above, and the effective dates stay in columns of your own.

The date a fact was true and the date you learned it

Every row of an attribute history involves two kinds of time. Valid time is when the value was true of the company. Recorded time is when a system learned it. A question about when a company changed needs valid time; a back-test, an audit or a question about what your sales team saw last quarter needs recorded time. Mixing the two is how look-ahead bias enters a dataset.

The Fokals datasets date each observation, and each date belongs to one of the two kinds.

• Technology Changes. Each change was seen between two readings of a company website and carries the time of the reading that saw it. That time is the earliest moment the change could be known and the latest moment it can have happened, so use it as the effective date.
• Technology Stack. For a technology already on a website at its first reading, the first-seen date is that reading. It says the technology was in place by then, not when it was adopted, so mark the row as a baseline.
• Employee Headcount. The as-of date is the date the count refers to, which is valid time. The count reaches you later, so your load time is the recorded time.
• Job Postings and Company News. A posting carries the date its job board gives and the date it was first observed. An announcement carries the date the company published it, or the time it was first observed where it gives none.

The query for what your table knew on a date adds recorded time to the period condition: only rows loaded by then, and a row closed after that date still counts as open. It is exact while each row is closed once: a late version that lands inside a closed period moves that row's end date and resets its closed time.

-- what your table knew on 1 February 2027
select company_id, attribute, value
from company_attribute_history
where loaded_at  <= timestamp '2027-02-01 00:00:00'
  and valid_from <= timestamp '2027-02-01 00:00:00'
  and (valid_to is null
       or valid_to  > timestamp '2027-02-01 00:00:00'
       or closed_at > timestamp '2027-02-01 00:00:00');

Databricks builds the same split into its AUTO CDC statement: STORED AS BITEMPORAL, in Beta, tracks changes across business time and system time, with SYSTEM SEQUENCE BY naming the time at which each change event is known to the system. The point-in-time data entry covers why the second time matters for research use.

Building the history from dated observations

Three datasets give the history its versions. In the marketing stack product, Technology Changes dates each change to a technology, market, language or currency on a company website, and Technology Stack gives the first-seen date of each technology. Employee Headcount, in announcements and scale, dates stated headcount. The data dictionary defines every column used below.

-- versions from Technology Changes: one per company, attribute and time
create temporary table stage_versions as
select company_id,
       category || ':' || key  as attribute,
       max(change)             as value,
       observed_at             as valid_from,
       cast('company_tech_events' as varchar(50)) as source_table
from company_tech_events
where category in ('technology', 'market', 'language', 'currency')
group by company_id, category, key, observed_at;

-- baselines from Technology Stack: in place before any change was seen
insert into stage_versions
select t.company_id, 'technology:' || t.technology, 'baseline',
       min(t.first_seen_at), 'company_technologies'
from company_technologies t
where not exists (
  select 1 from company_tech_events e
  where e.company_id   = t.company_id
    and e.category     = 'technology'
    and e.key          = t.technology
    and e.observed_at <= t.first_seen_at)
group by t.company_id, t.technology;

-- stated headcount, effective from the date the count refers to
insert into stage_versions
select company_id, 'headcount:' || source, cast(employees as varchar(20)),
       as_of, 'company_headcounts'
from company_headcounts;

In the first statement change becomes the value, so a removal is a version of its own and the as-of query shows a removed technology as removed. The group by leaves one version per company, attribute and time. That matters for the merge: Snowflake's MERGE, for one, returns an error by default when several source rows match one target row it would update, inserts one copy for each duplicate source row that matches nothing, and recommends a GROUP BY in the source.

Technology Changes records each change on the company website where it was seen. If a company you track runs several sites, add the website to the attribute; the change feed of the API returns the domain with each change. Take baselines from every Technology Stack you load, and leave a technology that shows a missing-since date as it is: that date marks a technology absent from recent readings and not yet counted as removed, so its row stays open until Technology Changes records the removal. Employee Headcount has one row per company, date and source, which is why the source is part of the attribute.

How to keep enriched company records in sync when source data changes

Load each observation into the history as a version, keyed on the company ID, the attribute and the effective date, and recompute end dates from the order of versions. Then write to the CRM only the current values that differ from what it holds. On a second run, each step finds nothing left to do.

-- 1. add the versions you do not hold; a second run adds nothing
insert into company_attribute_history
  (company_id, attribute, value, valid_from, loaded_at, source_table)
select s.company_id, s.attribute, s.value, s.valid_from,
       current_timestamp, s.source_table
from stage_versions s
where not exists (
  select 1 from company_attribute_history h
  where h.company_id = s.company_id
    and h.attribute  = s.attribute
    and h.valid_from = s.valid_from);

-- 2. each version ends where the next begins; a second run changes nothing
update company_attribute_history h
set valid_to  = n.next_from,
    closed_at = current_timestamp
from (
  select company_id, attribute, valid_from,
         lead(valid_from) over (partition by company_id, attribute
                                order by valid_from) as next_from
  from company_attribute_history
) n
where h.company_id = n.company_id
  and h.attribute  = n.attribute
  and h.valid_from = n.valid_from
  and h.valid_to is distinct from n.next_from;

-- 3. current values the CRM does not hold yet
select h.company_id, h.attribute, h.value
from company_attribute_history h
left join crm_enriched_fields c
  on c.company_id = h.company_id and c.attribute = h.attribute
where h.valid_to is null
  and c.value is distinct from h.value;

Here crm_enriched_fields is your own copy of the enriched values as the CRM holds them. Run the first two statements in one transaction with the bookmark of your sync. Snowflake streams follow the same rule for changes captured inside the warehouse: a stream advances its offset only when it is consumed in a DML transaction, and querying it alone does not move it.

A data enrichment sync strategy with change data capture on CRM records

Run two flows and give each field one owner. Change data capture from the CRM reports each account created, edited or deleted, which you match to a company ID and enrich. The history table owns the enriched fields and writes them back; people own the fields they edit, and enrichment never overwrites those.

In Salesforce, Change Data Capture publishes events for create, update, delete and undelete operations on records, so that corresponding records in an external data store can be kept in step. The event that matters most for enrichment is an edit to the account's website. The domain is the bridge to the company ID, as the entry on domain matching describes, so a new domain means a new match, and the enriched fields must follow the company the account now points to. Re-match on that event, then push the current rows of the new company. Keep the owner of each field in a small table that the push reads. The guide to enriching company records in your data product covers the match itself.

Best practices for data synchronization: CDC, idempotent upserts and one source of truth for enriched records

Give each field a single source of truth, upsert on a key that includes the effective date, deduplicate before the merge, derive end dates instead of carrying them, and commit rows and bookmark together. Then test it: run every load twice and expect no change, and expect at most one current row per attribute.

-- overlapping periods for one attribute: expect no rows
select a.company_id, a.attribute, a.valid_from, b.valid_from
from company_attribute_history a
join company_attribute_history b
  on  b.company_id = a.company_id
  and b.attribute  = a.attribute
  and b.valid_from > a.valid_from
  and b.valid_from < coalesce(a.valid_to, timestamp '9999-12-31 00:00:00');

-- more than one current row for one attribute: expect no rows
select company_id, attribute, count(*)
from company_attribute_history
where valid_to is null
group by company_id, attribute
having count(*) > 1;

Worked example: Acme Robotics, illustrative

Acme Robotics is an invented company, and every figure below is illustrative.

Assumptions:

• Acme Robotics has one website. Dates are invented, times are midnight UTC, and you load each reading the day after it.
• The first reading, on 4 January 2027, finds HubSpot on the site. It sets a baseline, so Technology Changes holds no row for it.
• A reading on 15 March 2027 sees HubSpot removed and Salesforce added. A reading on 10 May 2027 sees a new market, Germany.
• Employee Headcount gives 180 employees as of 31 December 2026, loaded on 29 March 2027, and 240 as of 30 June 2027, loaded on 2 August 2027.

After the staging statements and the two merge statements, the history holds six rows.

The working:

1. The HubSpot baseline row covers 4 January to 15 March 2027: 28 days to 1 February, 28 in February and 14 in March, 70 days. Its start is the first reading, not an adoption date.
2. As true on 1 April 2027, the record shows Salesforce added, HubSpot removed and 180 employees, because 31 December 2026 is on or before 1 April and 30 June 2027 is after it. Germany is absent: it starts on 10 May.
3. As known on 1 February 2027, the record shows only the HubSpot baseline. The count of 180 refers to 31 December 2026 but was loaded on 29 March, so a back-test dated 1 February that used it would act on data your table did not hold that day.
4. Between the two counts headcount rose by 240 minus 180, 60 employees, or 60 / 180 = 33.3 percent, over the 181 days from 31 December 2026 to 30 June 2027 (31 + 28 + 31 + 30 + 31 + 30).
5. Loading the 15 March reading again inserts no row, because both versions are keyed on 2027-03-15 and already there, and the second statement updates no row, because every valid-to already equals the next valid-from.
6. A count of 150 as of 30 June 2026, loaded later, is inserted with a valid-to of 2026-12-31 by the second statement, and the six rows above stay as they are.

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 7 October 2026. Product and company names are trademarks of their owners. Fokals is not affiliated with them or endorsed by them.