How to Build a TAM Model in Snowflake (With SQL)

Most TAM models are built once and ignored within a quarter. The data goes out of date, the market changes, and the number in the deck stops reflecting reality. 

Most TAM guides cover the math, top-down versus bottom-up, TAM versus SAM versus SOM, and hand you a spreadsheet template. What they don't cover is that any total addressable market model built from static data starts decaying the moment you finish it.

This guide covers how to build a total addressable market model in Snowflake that updates automatically so the number is always accurate.

What Is TAM, SAM, and SOM?

TAM, SAM, and SOM are three progressively narrower cuts on the same market.

TAM is what you show investors to justify the opportunity. SAM is what your GTM team actually works from. SOM is what goes in the revenue model.

For day-to-day GTM work, SAM is the number that matters — and specifically, the list of companies inside it.

How Do You Calculate TAM?

Three approaches are frequently used for market sizing. 

Top-Down

Start with a total industry figure from a research firm, Gartner, IDC, Forrester, and apply filters to narrow to your segment. If the global HR tech market is $35B and you serve mid-market North American companies, you might peg your segment at 15-20%.

The limitation: you're trusting someone else's numbers (often $10,000+ per report), and the filters are educated guesses. Top-down works for investor narratives, but it doesn't tell you who to reach out to.

Bottom-Up

Count the actual companies that fit your ICP and multiply by your average contract value.

Example: your ICP is US-based SaaS companies with 50-500 employees that have raised at least a Series A. You identify 6,800 companies that fit those criteria. Your ACV is $24,000/year.

TAM = 6,800 × $24,000 = $163M

That number is grounded. You can verify it, segment it by sub-ICP, and update it when the market changes.

Value-Theory

Estimate the value your product creates for a customer, then work out what a fair share of that looks like as a price.

Example: your product saves a 200-person sales team eight hours per rep per week. At a cost of $75/hour, that's $780,000 in annual value. If you capture 10% of that as price, your ACV for that segment is $78,000.

Use value-theory when you're pricing a new category or moving upmarket where comparable deals don't exist yet.

Which method to use? Investors want top-down for the narrative. Your GTM team wants bottom-up for the list. If your numbers diverge significantly between the two, and they often do by 5-10x, the top-down market definition is almost always broader than your actual ICP. Trust the bottom-up.

The bottom-up method is the most defensible. It's also the most data-intensive. Everything that follows shows you how to run it on live, automatically-updating company data.

Why Does a TAM Model Go Stale?

More than 22% of B2B contact data goes bad every year, and company-level firmographics -  headcount, funding stage, geography - change even faster. Companies grow past headcount thresholds, close funding rounds, get acquired, expand into new markets, and occasionally just shut down.

Most teams build their TAM in Q1, present it in Q2, and quietly stop looking at it by Q3.

Consider what can happen in just two weeks between building a model and presenting it. For a 14,300-company dataset:

• Hundreds of companies raised a Series A or B that wasn't on record at the time of the data pull

• Companies could be acquired by larger organizations outside the target market

A SAM off by several hundred accounts isn't necessarily the result of bad analysis. It's just the result of a static model in a market that keeps moving.

Out-of-date data gives you false confidence. You're making decisions based on numbers that feel accurate but aren't. Rebuilding the model more often only delays the problem; the better approach is to build it so it updates itself.

What Does a Live TAM Model in Snowflake Look Like?

Instead of a spreadsheet you refresh by hand, it's a table in Snowflake that updates when your underlying company data does. The architecture has four layers:

Snowflake's own revenue operations team runs enrichment and account scoring pipelines inside Snowflake using live company data for the same segmentation most companies still do in Excel. This architecture isn't novel; it's just not widely written about outside of data engineering circles.

The core logic is three SQL queries. One API call populates the raw layer, and a dbt schedule keeps everything up to date.

Step 1: Pull Your Company Universe from a Live Data Source

The dataset should be a live source you can query, filter, and refresh on a schedule instead of a CSV file downloaded months ago.

You define your ICP as a set of filters, send a request to a company data API, and get back companies that match with current firmographics: headcount, funding stage, geography, and industry.

Here's what that looks like using Crustdata's Company Search API for a typical B2B SaaS ICP - US-based software companies, 50-500 employees, Series A through Series C funded:

curl --request POST 'https://api.crustdata.com/screener/companydb/search' \
  --header 'Authorization: Token YOUR_API_TOKEN' \
  --header 'Content-Type: application/json' \
  --data-raw '{
    "filters": {
      "op": "and",
      "conditions": [
        {"filter_type": "hq_country", "type": "=", "value": "USA"},
        {"filter_type": "industries", "type": "(.)", "value": "software"},
        {"filter_type": "employee_metrics.latest_count", "type": "=>", "value": 50},
        {"filter_type": "employee_metrics.latest_count", "type": "=<", "value": 500},
        {"filter_type": "last_funding_round_type", "type": "in",
        "value": ["series_a", "series_b", "series_c"]}
      ]
    },
    "limit": 1000
  }'

The response returns a paginated list of matching companies, each with company ID, domain, headcount, last funding round, geography, headcount growth rates and more. Load these records into Snowflake's raw layer via a Lambda function on a schedule, or directly from your pipeline orchestration tool.

From that point, dbt handles everything downstream.

[Want to pull your ICP company universe with live filters? Explore Crustdata's Company Search API →]

Step 2:  Model TAM and SAM in SQL

Once company data lives in Snowflake, the TAM model is SQL. Three queries do most of the work.

Start with the broadest definition of your addressable market:

-- TAM: all companies in your addressable market (broad ICP)
SELECT
  COUNT(*) AS tam_count,
  SUM(estimated_revenue_lower_bound_usd) AS tam_revenue_floor
FROM stg_companies
WHERE industries ILIKE '%software%'
  AND hq_country = 'USA';

Narrow to SAM by applying your specific ICP filters:

-- SAM: companies matching your core ICP
SELECT
  COUNT(*) AS sam_count,
  SUM(estimated_revenue_lower_bound_usd) AS sam_revenue_floor
FROM stg_companies
WHERE industries ILIKE '%software%'
  AND hq_country = 'USA'
  AND employee_count BETWEEN 50 AND 500
  AND last_funding_round_type IN ('series_a', 'series_b', 'series_c');

These are your board-facing figures. Run them against realtime data and you have numbers you can actually defend because you can show exactly which companies are included and why.

Want to segment by territory? Your AE covering the Pacific Northwest gets `AND hq_location ILIKE '%Seattle%' OR hq_location ILIKE '%Portland%'`. 

[Start a free Crustdata trial]

Step 3: Turn Your SAM Into a Ranked Target Account List

A count of 4,200 SAM companies is useful in a board presentation. It's not useful on Monday morning when your AE asks which accounts to prioritize this week.

The same Snowflake model that produces your SAM count can output a ranked target account list, each company scored by fit signals. Add columns for recent headcount growth, time since last funding round, and revenue range, then weight them:

-- Target account list: SAM companies scored and ranked by fit signals
SELECT
  company_name,
  company_domain,
  employee_count,
  last_funding_round_type,
  last_funding_date,
  employee_growth_6m_percent,
  estimated_revenue_lower_bound_usd,
  -- Composite fit score
  (
    CASE WHEN employee_growth_6m_percent > 20 THEN 30 ELSE 0 END +
    CASE WHEN DATEDIFF(day, last_funding_date, CURRENT_DATE) < 180 THEN 25 ELSE 0 END +
    CASE WHEN employee_count BETWEEN 100 AND 300 THEN 20 ELSE 10 END
  ) AS fit_score
FROM stg_companies
WHERE linkedin_industries ILIKE '%software%'
  AND hq_country = 'USA'
  AND employee_count BETWEEN 50 AND 500
  AND last_funding_round_type IN ('series_a', 'series_b', 'series_c')
ORDER BY fit_score DESC;

Companies with the highest fit scores - growing fast, recently funded, squarely in your ICP headcount range get sequenced first. The ones at the bottom stay in your SAM; they just rank lower until something changes.

Some GTM teams call this a PAM: a Prioritized Account Market. The point is to go from "how big is the market" to "who do we call Monday morning."

Step 4: How Do You Keep the Model Automatically Updated?

Schedule a dbt pipeline to re-run on a daily or weekly cadence. dbt's incremental models process only the records that changed since the last execution, so your TAM and SAM counts update automatically when new companies enter your ICP filters, when existing accounts cross a specific threshold.

For real-time triggers, Crustdata's Watcher API pushes events directly into your pipeline when specific conditions are met: a company in your SAM raises a new funding round, crosses 200 employees, or starts posting aggressively about hiring in a new region. Those events land in Snowflake and the dbt model picks them up on the next scheduled run.

If you're already running a Lambda → S3 → Snowflake → dbt pipeline for company enrichment, adding the TAM model on top takes three SQL queries. The infrastructure is already there; the model is the missing piece.

Conclusion

Here's what usually happens: someone builds the TAM in a spreadsheet, presents it at a board meeting, and then it sits in a Google Drive folder for six months while the market keeps moving.

By the time it's finished and approved, part of it is already wrong. Companies raised money last week. Others crossed a headcount threshold. A few got acquired. The number in the deck reflects a world that shifted while you were building the model.

A Snowflake-based approach doesn't eliminate that problem, but it shrinks it. When your data source updates, the model updates. Your SAM count stays current. Your target account list reranks on its own.

Three queries. Four layers. The hard part is getting the company data into the raw layer — and a live API is the only thing that actually solves that.

[Start building with live company data. Try Crustdata free →]

FAQ

What's the difference between top-down and bottom-up TAM?

Top-down starts with a broad industry number — usually from Gartner or a similar firm — and applies filters to estimate your segment. Bottom-up counts the actual companies that match your ICP and multiplies by your ACV. Bottom-up is more defensible because you can check the inputs. Top-down is faster and good enough for early-stage investor decks where you don't have granular ICP data yet.

How accurate is a TAM model?

Only as accurate as your data and your ICP definition. A bottom-up model beats a top-down industry report because you can point to exactly which companies are counted. That said, it's still an estimate — no TAM model is a forecast. The goal is informed confidence, not false precision.

How often should you update your TAM?

Quarterly at minimum if you're doing it manually. With a Snowflake-based model on a live data source, it just updates on its own. The real question isn't frequency — it's whether your model is connected to data that reflects what the market looks like right now.

What data sources do you use to calculate TAM?

For top-down: Gartner, IDC, Forrester, or industry-specific research firms. For bottom-up: a live company database you can filter by ICP criteria. APIs like Crustdata's Company Search return companies matching your exact filters — industry, headcount, geography, funding stage — with data that's current, not from whenever the last database refresh happened.

Can you build a TAM model without a data warehouse?

Yes — a spreadsheet works fine for early-stage companies with small, stable ICPs. Once you're doing quarterly planning, carving territories, or feeding account scores into your sequencing tools, spreadsheets break: they don't refresh automatically, they don't scale to tens of thousands of records, and they don't connect to your CRM. That's when Snowflake starts earning its keep.