HubSpot · data model template

HubSpot Core Objects Source Replica data model

Faithful 3NF replica of core HubSpot objects with the native object Id as the business key and connector metadata (createdate, hs_lastmodifieddate, archived flag, sync time, soft-delete) for incremental, archive-aware loading. HubSpot's M:N associations (deal↔contact, deal↔company, contact↔list) are resolved with junction tables. Mirrors how Fivetran/Airbyte land HubSpot; serves as the raw schema beneath the HubSpot analytics mart.

A normalized (3NF) landing replica of core HubSpot objects exactly as an ELT connector (Fivetran/Airbyte) replicates them into the warehouse: owner, company, contact, deal_pipeline, deal_pipeline_stage, deal, engagement, email_campaign, email_event, ticket, contact_list (+ membership), deal↔contact / deal↔company associations, and form (+ submission) — the raw layer that staging models and marts build on. It is a normalized (3NF) relational model that mirrors the source system, suited to operational databases and to landing source data faithfully.

3NFHubSpotGDPRCCPACAN-SPAM

16
tables: 181
columns: 22
relationships: 7
PII columns

Use this template About database schema design →

Opening the template creates a project with the ERD, platform-native DDL, a dbt project and documentation already generated (recommended platform: Snowflake). Edit it in plain English or on the canvas, then ship it to any supported warehouse.

Subject areas

platformcrm_coreactivitymarketingservice

Design decisions

Native object Id retained as the business key
ELT connectors replicate HubSpot keyed on the object Id; keeping it as the business key (alongside a warehouse surrogate) preserves lineage to the source portal and lets associations resolve on the same id the API exposes.
hs_lastmodifieddate and archived flag on every object
Incremental syncs filter on hs_lastmodifieddate and must honour archived/soft-deleted records; carrying both on every table makes the landing layer reloadable and archive-aware.
M:N associations resolved with junction tables
HubSpot deals associate with many contacts and companies, and contacts belong to many lists; modelling these as junction tables (deal↔contact, deal↔company, contact↔list) keeps the replica in 3NF without repeating object data.
Conformed pipeline table for deals and tickets
HubSpot stores deal and ticket pipelines in the same structure; a single pipeline table with a pipeline_type flag mirrors the source and avoids two near-identical tables.

Tables in the HubSpot Core Objects Source Replica model

Every table with its grain, columns, types and keys. PK marks a primary key, BK a business key and PII a column classified as personal data, which gets a masking strategy by default.

Tables (13)

hs_owner

Grain: one row per owner (user)

Column	Type	Keys
owner_key	bigint	PK
hubspot_id	varchar	BK
email	varchar	PII
first_name	varchar	PII
last_name	varchar	PII
user_id	varchar
team_id	varchar
is_active	boolean
created_date	timestamp
updated_date	timestamp
is_archived	boolean
fivetran_synced	timestamp
fivetran_deleted	boolean

hs_company

Grain: one row per company

Column	Type	Keys
company_key	bigint	PK
hubspot_id	varchar	BK
company_name	varchar
domain	varchar
industry	varchar
company_type	varchar
lifecycle_stage	varchar
country	varchar
number_of_employees	integer
annual_revenue	decimal
owner_key	bigint
created_date	timestamp
updated_date	timestamp
is_archived	boolean
fivetran_synced	timestamp
fivetran_deleted	boolean

hs_contact

Grain: one row per contact

Column	Type	Keys
contact_key	bigint	PK
hubspot_id	varchar	BK
email	varchar	PII
first_name	varchar	PII
last_name	varchar	PII
phone	varchar	PII
job_title	varchar
lifecycle_stage	varchar
lead_status	varchar
original_source	varchar
email_opt_out	boolean
associated_company_key	bigint
owner_key	bigint
created_date	timestamp
updated_date	timestamp
is_archived	boolean
fivetran_synced	timestamp
fivetran_deleted	boolean

hs_deal

Grain: one row per deal

Column	Type	Keys
deal_key	bigint	PK
hubspot_id	varchar	BK
deal_name	varchar
amount	decimal
deal_type	varchar
stage_key	bigint
pipeline_key	bigint
owner_key	bigint
close_date	date
is_closed	boolean
is_won	boolean
created_date	timestamp
updated_date	timestamp
is_archived	boolean
fivetran_synced	timestamp
fivetran_deleted	boolean

hs_engagement

Grain: one row per engagement (call/email/meeting/note/task)

Column	Type	Keys
engagement_key	bigint	PK
hubspot_id	varchar	BK
engagement_type	varchar
owner_key	bigint
engagement_timestamp	timestamp
activity_type	varchar
direction	varchar
duration_ms	bigint
created_date	timestamp
updated_date	timestamp
is_archived	boolean
fivetran_synced	timestamp
fivetran_deleted	boolean

hs_email_campaign

Grain: one row per marketing email campaign

Column	Type	Keys
email_campaign_key	bigint	PK
hubspot_id	varchar	BK
campaign_name	varchar
subject	varchar
email_type	varchar
from_name	varchar
created_date	timestamp
updated_date	timestamp
is_archived	boolean
fivetran_synced	timestamp
fivetran_deleted	boolean

hs_email_event

Grain: one row per marketing email event

Column	Type	Keys
email_event_key	bigint	PK
hubspot_id	varchar	BK
email_campaign_key	bigint
contact_key	bigint
event_type	varchar
event_timestamp	timestamp
device_type	varchar
created_date	timestamp
updated_date	timestamp
is_archived	boolean
fivetran_synced	timestamp
fivetran_deleted	boolean

hs_ticket

Grain: one row per Service Hub ticket

Column	Type	Keys
ticket_key	bigint	PK
hubspot_id	varchar	BK
subject	varchar
pipeline_key	bigint
ticket_stage	varchar
owner_key	bigint
company_key	bigint
contact_key	bigint
priority	varchar
source_type	varchar
close_date	date
is_closed	boolean
created_date	timestamp
updated_date	timestamp
is_archived	boolean
fivetran_synced	timestamp
fivetran_deleted	boolean

hs_contact_list_member

Grain: one row per contact per list (membership junction)

Junction resolving the many-to-many between contacts and lists.

Column	Type	Keys
list_member_key	bigint	PK
list_key	bigint
contact_key	bigint
added_date	timestamp
fivetran_synced	timestamp

hs_deal_contact

Grain: one row per deal↔contact association

Junction resolving the many-to-many between deals and contacts.

Column	Type	Keys
deal_contact_key	bigint	PK
deal_key	bigint
contact_key	bigint
fivetran_synced	timestamp

hs_deal_company

Grain: one row per deal↔company association

Junction resolving the many-to-many between deals and companies.

Column	Type	Keys
deal_company_key	bigint	PK
deal_key	bigint
company_key	bigint
fivetran_synced	timestamp

hs_form

Grain: one row per marketing form

Column	Type	Keys
form_key	bigint	PK
hubspot_id	varchar	BK
form_name	varchar
form_type	varchar
created_date	timestamp
updated_date	timestamp
is_archived	boolean
fivetran_synced	timestamp
fivetran_deleted	boolean

hs_form_submission

Grain: one row per form submission

Column	Type	Keys
form_submission_key	bigint	PK
submission_id	varchar	BK
form_key	bigint
contact_key	bigint
submitted_at	timestamp
page_url	varchar
created_date	timestamp
updated_date	timestamp
is_archived	boolean
fivetran_synced	timestamp
fivetran_deleted	boolean

Lookup tables (3)

hs_deal_pipeline

Grain: one row per pipeline (deal or ticket)

Column	Type	Keys
pipeline_key	bigint	PK
hubspot_id	varchar	BK
pipeline_label	varchar
pipeline_type	varchar
display_order	integer
created_date	timestamp
updated_date	timestamp
is_archived	boolean
fivetran_synced	timestamp
fivetran_deleted	boolean

hs_deal_pipeline_stage

Grain: one row per pipeline stage

Column	Type	Keys
stage_key	bigint	PK
hubspot_id	varchar	BK
pipeline_key	bigint
stage_label	varchar
display_order	integer
probability	decimal
is_closed_won	boolean
is_closed_lost	boolean
created_date	timestamp
updated_date	timestamp
is_archived	boolean
fivetran_synced	timestamp
fivetran_deleted	boolean

hs_contact_list

Grain: one row per contact list

Column	Type	Keys
list_key	bigint	PK
hubspot_id	varchar	BK
list_name	varchar
list_type	varchar
created_date	timestamp
updated_date	timestamp
is_archived	boolean
fivetran_synced	timestamp
fivetran_deleted	boolean

Relationships

Each relationship is a foreign key on the first table referencing the second.

hs_company.owner_key → hs_owner (many-to-one)
hs_contact.associated_company_key → hs_company (many-to-one)
hs_contact.owner_key → hs_owner (many-to-one)
hs_deal_pipeline_stage.pipeline_key → hs_deal_pipeline (many-to-one)
hs_deal.stage_key → hs_deal_pipeline_stage (many-to-one)
hs_deal.pipeline_key → hs_deal_pipeline (many-to-one)
hs_deal.owner_key → hs_owner (many-to-one)
hs_engagement.owner_key → hs_owner (many-to-one)
hs_email_event.email_campaign_key → hs_email_campaign (many-to-one)
hs_email_event.contact_key → hs_contact (many-to-one)
hs_ticket.pipeline_key → hs_deal_pipeline (many-to-one)
hs_ticket.owner_key → hs_owner (many-to-one)
hs_ticket.company_key → hs_company (many-to-one)
hs_ticket.contact_key → hs_contact (many-to-one)
hs_contact_list_member.list_key → hs_contact_list (many-to-one)
hs_contact_list_member.contact_key → hs_contact (many-to-one)
hs_deal_contact.deal_key → hs_deal (many-to-one)
hs_deal_contact.contact_key → hs_contact (many-to-one)
hs_deal_company.deal_key → hs_deal (many-to-one)
hs_deal_company.company_key → hs_company (many-to-one)
hs_form_submission.form_key → hs_form (many-to-one)
hs_form_submission.contact_key → hs_contact (many-to-one)

Related templates

All templates →

Start for $1

From requirements to AI-ready context, in one platform.

Describe your business in a conversation, start from a template or connect your own database. Your first model is ready in minutes.

Get started Book a demo

Plans from $1/month · cancel any time