Zoho CRM in Your Data Warehouse: Schema and SQL Models

The Zoho CRM connector is live

You can now sync Zoho CRM into your data warehouse with Weld and put your pipeline next to your billing data, your product usage and your ad spend. Twelve modules, incremental on Modified_Time, read-only OAuth, and every Zoho data centre except China. Setup is four steps and the connector docs walk through all of them.

Summary: This is the part that usually gets left out of a connector announcement — what the data actually looks like once it lands, and what you can honestly build with it. Zoho CRM's schema has one hub, two real foreign keys and three tables nothing joins to. It has no won/lost flag, no stage history, and no link between an activity and the deal it was about. All of that changes what a Zoho pipeline report can say, so it is worth knowing before you build one rather than after. At the end there are four SQL models — deal pipeline, deal flow by month, rep activity and account 360 — free to copy in both Weld and dbt form.

What lands in your warehouse

Twelve streams, one per Zoho module. Every one is keyed on id and syncs incrementally on Modified_Time, so the first run backfills and every run after that only asks Zoho for what changed.

Field names arrive in snake_case, and Zoho's lookup fields are flattened rather than nested: a record's owner turns up as three columns, owner_id, owner_name and owner_email, instead of one object. Custom modules and custom fields are not synced.

Every field of every stream is in the schema explorer — click through for the full, searchable version:

Part 1 — What actually joins to what

That diagram will show you all twelve tables. What it will not show you is that most of them do not connect to each other. Here is the whole join graph:

Three things follow from that picture.

user is the hub, so sync it first

Ten of the twelve streams carry owner_id. It is the single most connected column in the schema and the only dimension the modules share. If you skip the user stream because a CRM user list sounds like metadata, every report you build groups by a blank rep name and you will not notice until someone looks at a chart.

Zoho keeps deactivated users in the module rather than deleting them, so status is worth carrying through as a boolean. A deal owned by a deactivated rep is unmanaged pipeline, and that is a report worth having.

The account key is called account_name_id

Zoho's lookup field on both Deals and Contacts is named Account_Name, so the connector flattens it into account_name_id and account_name_name. Despite the name, account_name_id holds the account's id — it is the foreign key, and account_name_name is a denormalised copy of the label.

Rename it in staging and drop the label. Keeping the denormalised name around means a renamed account leaves stale copies of its old name scattered across every module that referenced it.

1-- staging.zoho_crm.deal
2select
3    cast(id as string) as deal_id
4  , nullif(trim(cast(stage as string)), '') as stage
5  , cast(amount as numeric) as amount
6  , cast(closing_date as date) as closing_date
7  , -- Zoho's lookup is named Account_Name; this column holds the account's id.
8    cast(account_name_id as string) as account_id
9  , cast(owner_id as string) as owner_id
10  , cast(created_time as timestamp) as created_time
11from
12    {{raw.zoho_crm.deal}}

Notes are the only activity you can attribute

Zoho's Notes module keeps its Parent_Id, and the connector syncs it as parent_id_id. That one column is the only route from something a rep did back to the record they did it about.

What it does not tell you is which module the parent is in — Zoho exposes that as $se_module and the connector does not sync it. Record ids are globally unique across Zoho modules though, so you can recover it by joining the parent id to each module in turn and seeing which one matches. That is how the account_360 model below counts notes written on an account, on its deals and on its contacts as one number.

Part 2 — Four things this schema cannot do

None of these are bugs. They are properties of what the connector syncs, and each one quietly changes a number somebody is going to put on a dashboard.

1. Activity cannot be attributed to a deal

Zoho's Calls, Events and Tasks modules each carry What_Id and Who_Id — the deal, account, contact or lead the activity belongs to. Neither field is synced. owner_id is the only foreign key on all three streams.

So you can report how many calls a rep made. You cannot report how many calls went into the deals you won, how much activity a stalled deal has had, or the activity-to-close ratio anyone asks for eventually. Any dashboard that claims to is inventing the link.

There are three honest ways around it: use note.parent_id, which is attributable; join a dialer or calendar source on rep email and meeting time; or wait for the connector to sync the parent ids. What you should not do is pick a proxy and let people assume it means something it does not.

2. There is no won/lost flag

Zoho ships no is_won, no is_closed, no probability and no forecast category. The only thing telling you a deal is decided is the stage string.

Match on a pattern rather than an equality list, because Zoho's own defaults include Closed-Lost to Competition alongside Closed Won and Closed Lost:

1case
2    when lower(stage) like '%won%' then 'Won'
3    when lower(stage) like '%lost%' then 'Lost'
4    else 'Open'
5end as stage_status

That covers the defaults. It does not cover the stages your sales ops team added — Contract Signed, Churned, Dead, Nurture, or anything in another language. Every one of those reads as open pipeline until you add it, which inflates your forecast and hides losses from your win rate at the same time.

This is what the first test below is for, and it is the one edit almost every org has to make.

3. There is no history, so today is all you get

Weld's Zoho CRM connector does not support history tables, and the deal stream carries no stage-change audit. There is no way to ask what the pipeline looked like at the end of last month, how long a deal sat in Negotiation, or when a deal actually closed.

closing_date is Zoho's expected close date, and Zoho does not clear it when a deal closes. So on a won deal it is the forecast that happened to be in place, not the day money changed hands. Booking wins on modified_time instead is worse: that column moves every time anyone edits the record, so a note added in August would move a June win into August.

If pipeline trend or stage velocity matters, materialise the deal model on a daily schedule and keep the runs. That is the only route to history here, and it only starts working from the day you set it up — which is a good argument for setting it up now rather than the first time someone asks for last quarter.

4. call_duration is a string, and its unit is ambiguous

call_duration arrives as text with one colon. Zoho's API reference documents the field as hh:mm; the CRM UI shows mm:ss for short calls; and there is no Call_Duration_in_seconds column on this stream to settle it.

Parse it, but do not trust the unit until you have checked one call of known length against it:

1case
2    when regexp_contains(
3        cast(call_duration as string)
4      , r'^\s*\d+:\d{1,2}\s*$'
5    ) then safe_cast(
6        split(trim(cast(call_duration as string)), ':') [offset(0)] as int64
7    ) * 60 + safe_cast(
8        split(trim(cast(call_duration as string)), ':') [offset(1)] as int64
9    )
10end as call_duration_minutes

event is better behaved — it carries both start_date_time and end_date_time, so meeting duration is computed rather than parsed and the unit is not in question.

One more worth knowing, though it is documented on the connector page: Zoho's records API does not report deletions. A deleted record stops receiving updates and stays in your warehouse. Run a ReSync on the affected table when the destination has to match Zoho exactly.

Part 3 — Four models to start from

Four core models, in the same four-layer shape as the Shopify templates: raw lands untouched, staging casts and renames, core holds every piece of business logic, and thin analytics contracts are what dashboards bind to.

1raw.zoho_crm.*        ELT output, untouched
2  ↓
3staging (9 models)    cast, rename, blanks to NULL — the dull work, once
4  ↓
5core (4 models)       stage classification, date spines, joins
6  ↓
7analytics (4 models)  BI-facing contracts, deliberately thin
8

deal_pipeline — the workhorse

One row per deal, classified, with its account and owner joined on. The other three models read it, so the stage logic is written once.

It returns stage_status plus is_open / is_won / is_lost, and splits amount into pipeline_amount, won_amount and lost_amount so BI can sum a column instead of repeating the CASE. age_days is time-to-close on a decided deal and time-in-pipeline on an open one. is_overdue flags open deals whose expected close date has already passed — the cheapest pipeline-hygiene number there is, and usually the first thing a sales lead asks for.

The user join is deliberately a LEFT join. A rep deleted from Zoho vanishes from the user module while their deals stay in the pipeline; an inner join would silently drop that pipeline from your total.

deal_flow_by_month — created, won, lost, win rate

Month × owner, on a month spine so a rep with a quiet month returns a row of zeros instead of disappearing from the series and letting a line chart interpolate straight over the gap.

The one decision worth flagging: open deals are excluded from the win-rate denominator. Counting them as not-yet-won drags every current month down and makes a healthy pipeline look like a collapse. Both a count-based and a value-based win rate are returned, along with average won deal size.

rep_activity — calls, meetings and tasks per rep per day

Day × owner, from call, event and task. Read Part 2 first: this is activity per rep, never per deal.

There is no tasks_completed column, and its absence is deliberate. The connector syncs the completion flag but no completion timestamp, so completions can be totalled as a current state and never placed on a day. Booking them on modified_time would move a task's completion every time anyone touched the record afterwards.

account_360 — the account-level view

One row per account: contacts and how many are reachable by email, deals open, won, lost and overdue with amounts, the next expected close date, and note activity resolved through all three routes described above.

is_stale_with_open_pipeline flags accounts sitting on open pipeline with nothing written on them in 90 days. It is also the natural source for a reverse-ETL sync writing pipeline value back onto the Zoho Account record, which puts the warehouse's view in front of the reps who need it.

Part 4 — The tests that matter

Four ship with the templates. The first one is the important one.

assert_deal_stages_are_classified lists every distinct stage that fell through to Open. A failure is information rather than a defect — genuinely open stages like Qualification are supposed to appear. What you are hunting for is a closed stage among them. Run it when you deploy, and again whenever sales ops adds a stage.

Part 5 — Deploying it

The models ship in both dialects, generated from the same source so they cannot disagree about how a deal is classified as won. The only difference is ref syntax:

1-- Weld: references map to folder paths
2from
3    {{staging.zoho_crm.deal}}
4    -- dbt: the same model, same logic
5from
6    {{ ref('stg_zoho_crm__deal') }}

With GitHub Sync a push deploys all of them and Weld resolves the dependency order. On the dbt side, copy dbt/models/ into your project and repoint sources.yml — the models set their own config(), so there are no dbt_project.yml changes and no packages to install.

Several Zoho orgs? Every staging model emits a zoho_org label and it is part of every join key. To add an org, UNION ALL a second block in each staging model with a different label, and change nothing else.

Everything is written for BigQuery. GENERATE_DATE_ARRAY, COUNTIF, SAFE_DIVIDE and REGEXP_CONTAINS need swapping for other engines, but the logic ports without restructuring.

Get the templates

• Zoho CRM — 4 core models, 9 staging, 4 tests, both dialects
• The full SQL template library
• Zoho CRM connector docs — setup, scopes and data centres

Treat every model as unverified until you have reconciled it. Open pipeline against Zoho's own Deals view is the check that matters; if the two disagree, the stage classification is where to look first.

If your CRM is not the only system you are trying to report on, How to build a modern data stack covers the wider picture, and Defining core business metrics covers agreeing on what a "won deal" means before you model it.

FAQ

Can I sync custom fields and custom modules from Zoho CRM?

Not today. Each stream syncs a fixed set of fields from its Zoho module, and custom modules and custom fields are not included. The connector changelog is where field additions are announced.

Why does my Zoho win rate look wrong?

Almost always because a custom deal stage is being read as open pipeline. Zoho has no won/lost flag, so the outcome has to be derived from the stage string, and a stage like Contract Signed or Churned matches neither "won" nor "lost". Run assert_deal_stages_are_classified to list every stage falling through to open, and add the closed ones to the classification.

Can I report on how much activity went into a won deal?

Not from this connector alone. Zoho's Calls, Events and Tasks modules carry What_Id and Who_Id pointing at the related record, and neither field is synced, so activity attaches to a rep rather than to a deal. Notes are the exception — parent_id is synced, so anything a rep wrote down can be tied back to the account, deal, contact or lead it was about.

Does the Zoho CRM connector capture deleted records?

No. Zoho's records API does not report deletions, so a record deleted in Zoho stops receiving updates and stays in your warehouse. Run a ReSync of the affected table when the destination has to match Zoho exactly.

Can I see how my pipeline changed over time?

Only from the point you start storing it. The connector does not support history tables and Zoho's deal stream carries no stage-change audit, so the warehouse holds current state only. Materialise the deal model on a daily schedule and keep the runs to build the history yourself.

Which Zoho data centres are supported?

All of them except China. You do not pick a region during setup — start from the standard Zoho sign-in page and Zoho routes you to your own data centre, which Weld then stores and uses for every later API call. Accounts in the China data centre (accounts.zoho.com.cn) cannot be reached through the standard sign-in flow and are not supported.