Joining licensed company data to CRM tables in Snowflake

This guide joins company data you license from Fokals to the account table of your CRM in Snowflake, so that each account carries its hiring, technology and announcement signals. Matching the records of one company across two datasets is entity resolution, and here it is built in SQL from four parts: a domain normaliser, a bridge from domains to the Fokals company ID, a match with a measured rate, and a view a sales team can read. Fokals is delivered direct, by REST API and as bulk files in JSON, JSON Lines or CSV, which you load with Snowflake's own loaders, as the guide to loading company data into Snowflake shows. The data is company-level throughout, so the join works at the level of the account.

The join in three hops

A CRM account has a website. Once normalised, the website is looked up in a bridge of the domains of each company, which gives a Fokals company ID. The company ID then joins any Fokals dataset, because every file carries it. Domain matching is the first join to try because the data dictionary gives Technology Stack (company_technologies) and Web Traffic (traffic_ranks) a domain column, and a CRM account usually has a website field. Identifiers such as the ISIN serve a different join, between Fokals and market data, and the blog post on identifiers that make company data joinable covers those.

The examples use four schemas in one database: crm for your own accounts table (account_id, account_name, website, country), fokals for the loaded tables, signals for the join model and sales for what the sales team sees. Acme Robotics is an illustrative account.

Normalise the domain

Two spellings of one website will not match, so normalise both sides with the same function. This one lowercases the value and removes the scheme, any path, query or fragment, a port, a trailing dot and a leading www. It returns NULL for an empty value. It avoids non-greedy quantifiers, which Snowflake's regular expression functions do not support.

create or replace function signals.normalise_domain(site varchar)
returns varchar
as
$$
  nullif(
    regexp_replace(
      regexp_replace(
        regexp_replace(
          regexp_replace(lower(trim(site)), '^[a-z][a-z0-9+.-]*://', ''),
          '[/?#].*$', ''),
        ':[0-9]+$|[.]$', ''),
      '^www[0-9]*[.]', ''),
    '')
$$;

A subdomain stays as it is here and is handled in the match below. Do not strip the top-level domain to make more records match: acme-robotics.example and a different company on another ending are different companies. A free-mail domain captured from a sign-up form is not a company website either. Keep a signals.free_mail_domains table that you maintain and set those domains to NULL, as the first view does.

Build the bridge and match the accounts

The bridge lists the domains of each company. Build it from the two datasets that carry a domain column, Technology Stack (company_technologies) from the marketing stack dataset and Web Traffic (traffic_ranks) from the announcements and scale dataset, and normalise it with the same function. The match rate you measure reflects the domains the bridge holds.

The match tries the whole host first, then the host with its leftmost label removed, and so on down to two labels. The first level that finds a company wins. An account whose best level finds two companies is ambiguous and is left out for review, never guessed. No public suffix list is needed, because the bridge decides what counts as a company domain: a candidate such as co.uk finds nothing. Snowflake's FLATTEN function produces one row per label, and the position of a label, counted from zero, is the number of labels dropped when the host is cut there.

create or replace view signals.account_domain as
with normalised as (
  select account_id, signals.normalise_domain(website) as domain
  from crm.accounts
)
select n.account_id,
       iff(f.domain is null, n.domain, null) as domain
from normalised n
left join signals.free_mail_domains f on f.domain = n.domain;

create or replace view signals.company_domain as
select distinct company_id, signals.normalise_domain(domain) as domain
from (
  select company_id, domain from fokals.company_technologies
  union all
  select company_id, domain from fokals.traffic_ranks
)
where signals.normalise_domain(domain) is not null;

create or replace view signals.account_hits as
with parts as (
  select account_id, split(domain, '.') as labels
  from signals.account_domain
  where domain is not null
),
candidates as (
  select p.account_id,
         array_to_string(array_slice(p.labels, f.index, array_size(p.labels)), '.') as candidate,
         f.index as labels_dropped
  from parts p, lateral flatten(input => p.labels) f
  where array_size(p.labels) - f.index >= 2
)
select c.account_id, d.company_id, c.candidate as matched_domain, c.labels_dropped
from candidates c
join signals.company_domain d on d.domain = c.candidate
qualify c.labels_dropped = min(c.labels_dropped) over (partition by c.account_id);

create or replace view signals.account_company as
select account_id,
       min(company_id)     as company_id,
       min(matched_domain) as matched_domain,
       min(labels_dropped) as labels_dropped
from signals.account_hits
group by account_id
having count(distinct company_id) = 1;

Snowflake's QUALIFY clause filters on a window function after the join, which keeps only the best level for each account. If the views are slow on a large account base, keep the bridge as a table that you refresh after each load.

Measure the match rate

Define the rate before you read it. The match rate is matched accounts divided by accounts with a usable domain. Coverage is matched accounts divided by all accounts. Report both, and report ambiguous accounts on their own line.

with status as (
  select a.account_id,
         a.domain is not null                                as has_domain,
         m.account_id is not null                            as matched,
         (h.account_id is not null and m.account_id is null) as ambiguous
  from signals.account_domain a
  left join signals.account_company m on m.account_id = a.account_id
  left join (select distinct account_id from signals.account_hits) h
         on h.account_id = a.account_id
)
select count(*)                                           as accounts,
       sum(iff(has_domain, 1, 0))                         as with_usable_domain,
       sum(iff(matched, 1, 0))                            as matched,
       sum(iff(ambiguous, 1, 0))                          as ambiguous,
       round(100 * sum(iff(matched, 1, 0))
             / nullif(sum(iff(has_domain, 1, 0)), 0), 1)  as match_rate_pct
from status;

Then read the misses. Each has a cause, and only some can be fixed in the join.

Also count matches by labels_dropped and review every match above zero before you trust it. A site hosted on a subdomain of a platform's own domain would otherwise match the platform, not the account's owner.

select labels_dropped, count(*) as accounts
from signals.account_company
group by labels_dropped
order by labels_dropped;

Report the rate by segment as well as overall, such as country, size or the way the account was created. A base of listed groups will match differently from a base of small private firms, and one overall figure hides that. Start with a sample of a few hundred accounts, because you can read every miss in it.

Name is the fallback, never the first key. Use the strongest identifier first (ISIN, then LEI, then ticker with its market), the normalised name last, always with a country, and a person to review every name-only match. Record each reviewed decision in a small signals.match_overrides table (account_id, company_id, reason), where a NULL company blocks a match. The next view reads it first.

A view your sales team can read

The model for the sales team is one row per CRM account, with the matched company's latest signals beside it. Each signal comes from a different table, so each is aggregated to one row per company first. The view uses the latest closed day of Hiring Activity (company_hiring_daily), the technologies added in the last 90 days from Technology Changes (company_tech_events), and the announcements of the last 90 days from Company News (company_news). It assumes JSON cells such as event_types were parsed to VARIANT when the tables were loaded.

create or replace view signals.account_match as
select a.account_id,
       iff(o.account_id is not null, o.company_id, m.company_id) as company_id,
       case when o.account_id is not null then 'override'
            when m.company_id is not null then 'domain' end      as match_basis,
       m.matched_domain
from crm.accounts a
left join signals.account_company m on m.account_id = a.account_id
left join signals.match_overrides o on o.account_id = a.account_id;

create or replace view sales.account_signals copy grants as
with hiring as (
  select company_id,
         max(day)                    as latest_day,
         max_by(open_postings, day)  as open_postings,
         sum(new_postings)           as new_postings_28d
  from fokals.company_hiring_daily
  where day > dateadd('day', -28, current_date())
  group by company_id
),
tools as (
  select company_id,
         array_agg(distinct technology_name) as tools_added_90d
  from fokals.company_tech_events
  where category = 'technology'
    and change = 'added'
    and observed_at >= dateadd('day', -90, current_timestamp())
  group by company_id
),
news as (
  select n.company_id,
         max(n.at) as latest_announcement,
         sum(iff(array_contains('leadership_change'::variant, n.event_types::array), 1, 0))
           as leadership_changes_90d
  from fokals.company_news n
  where n.at >= dateadd('day', -90, current_date())
  group by n.company_id
)
select a.account_id,
       a.account_name,
       m.company_id is not null as matched,
       m.match_basis,
       m.matched_domain,
       h.latest_day,
       h.open_postings,
       h.new_postings_28d,
       t.tools_added_90d,
       n.latest_announcement,
       n.leadership_changes_90d
from crm.accounts a
left join signals.account_match m on m.account_id = a.account_id
left join hiring h on h.company_id = m.company_id
left join tools  t on t.company_id = m.company_id
left join news   n on n.company_id = m.company_id;

grant usage on database company_data to role sales_ops;
grant usage on schema company_data.sales to role sales_ops;
grant select on view company_data.sales.account_signals to role sales_ops;

Snowflake's CREATE VIEW page says a role granted a view can use it without privileges on the underlying tables, so the loaded Fokals tables can stay closed to the sales team. It also says OR REPLACE drops the old view's grants unless COPY GRANTS is given, which is why the view above carries it.

Three rules keep the view honest.

• Show the date beside every figure, as latest_day does. Daily and weekly tables are written once after the period closes, so the view shows the last closed day, not today.
• Treat NULL as unknown: a company with no hiring rows has no hiring data yet, which is different from no hiring.
• Show the matched domain and the basis, so a representative can report a wrong match.

If you add Intent Scores (company_intent_weekly), show its evidence beside its score, because every score carries the dated signals behind it. The use case on prioritising accounts with intent scores takes that further.

Groups and dated matches

The accounts that match are the companies of the Fokals index: listed companies, the brands they own and verified private companies. To see a group, aggregate by the company ID first, so a company is counted once even if two CRM accounts match it, and then by ISIN: a brand or subsidiary carries the identifiers of its listed parent. The glossary entry on corporate hierarchy explains why a group needs a parent key.

The match is computed from the bridge as it stands today. A company that later adds a domain can change an account's match, and so can an edit to the website field in your CRM. If you need to know what a past report was based on, write the match to a dated table each time you rebuild it.

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.