dbt (data build tool) project structure, SQL patterns, and best practices for the analytics warehouse. Use this skill when working with dbt models, testing SQL queries, or creating new analytical tables.
Install with Codex or Claude Copy this prompt, paste it into Codex, Claude, or another assistant, and let it review the skill page and install it for you.
A direct command skips the review prompt. Inspect the source before running it.
dbt (data build tool) project structure, SQL patterns, and best practices for the analytics warehouse. Use this skill when working with dbt models, testing SQL queries, or creating new analytical tables.
dbt Integration
Project Location
Root: dbt/ (in workspace root, NOT in code/)
Models: dbt/models/ organized by layer (staging, intermediate, mart, analytics)
Config: dbt/dbt_project.yml
Profiles: dbt/profiles.yml
Access Control
⚠️ CRITICAL: The dbt directory has restricted write access. When creating or modifying dbt models:
Create SQL in code/.builder/dbt-models/ first
Test the query using a script in code/scripts/
Validate results before requesting deployment
User must manually copy to dbt/models/ directory
Schema Organization
Schema
Purpose
Examples
dbt_staging_bigquery
Raw staged events from BigQuery
first_pageviews, all_pageviews, signups
dbt_staging
Raw staged data from other sources
hubspot_companies, hubspot_contacts
dbt_intermediate
Joins, transforms, denormalization
hubspot_form_submissions, deal_first_contact
dbt_mapping
Join tables, ID mappings
hs_deals_to_contact_id, user_id_to_org_id
dbt_mart
Dimensional models (fact/dim tables)
dim_hs_deals, dim_hs_contacts, dim_subscriptions
dbt_analytics
Reporting views, aggregates
deals_by_motion, revenue_funnel, active_users
dbt_dev
Development/testing (EXCLUDE from queries)
Auto-filtered by BigQuery lib
Model Configuration Best Practices
Standard config block
{{
config(
schema="dbt_analytics", -- Target schema
materialized="table", -- or "view", "incremental"
tags=["daily", "analytics", "hubspot"], -- For orchestration/docs
)
}}
Common materializations
table - Full refresh daily, good for < 10M rows
view - No storage, always fresh, good for simple transforms
incremental - Append-only, for large event tables
SQL Patterns & Gotchas
1. Column Name Mismatches
⚠️ Common bug source: Column names differ between spec and actual tables
Spec Column
Actual Column
Table
first_pageview_date
created_date (TIMESTAMP)
first_pageviews
channel
first_touch_channel
all_pageviews
referrer
c_referrer
all_pageviews
user_create_date
user_create_d
product_signups
deal_stage
stage_name
dim_hs_deals
deal_amount
amount
dim_hs_deals
Always verify column names by querying INFORMATION_SCHEMA.COLUMNS or reading the source dbt model.
✅ CORRECT (remove DISTINCT or order by same column):
-- Option 1: Remove DISTINCT (ORDER BY creates uniqueness)ARRAY_AGG(form_name IGNORE NULLSORDERBY form_fill_date LIMIT 1)[SAFE_OFFSET(0)]
-- Option 2: Order by the aggregated columnARRAY_AGG(DISTINCT form_name ORDERBY form_name LIMIT 1)[SAFE_OFFSET(0)]
3. Type Casting
BigQuery dbt models store booleans as strings in some tables. Always cast:
-- dim_hs_deals.is_closed_won is STRING 'true'/'false', not BOOLCASEWHENCAST(is_closed_won AS STRING) ='true'THEN1ELSE0END-- Amounts may be STRING, cast to numericSUM(CAST(amount AS FLOAT64))
4. Email Matching
Always use case-insensitive email matching:
LOWER(qf.email) =LOWER(c.email)
5. QUALIFY for Deduplication
Use QUALIFY for window function filtering (cleaner than subquery):
SELECT*FROMtable
QUALIFY ROW_NUMBER() OVER (PARTITIONBY deal_id ORDERBY created_date) =1
6. NULL-Safe Joins
When joining on potentially NULL columns (like visitor IDs):
LEFTJOIN forms f
ON (
LOWER(f.email) =LOWER(c.email)
OR (f.b_visitor_id ISNOT NULLAND f.b_visitor_id = c.b_visitor_id)
)
Common Join Paths
HubSpot Deals → Contacts → Forms
FROM {{ ref("dim_hs_deals") }} d
LEFTJOIN {{ ref("hs_deals_to_contact_id") }} dc
ON d.deal_id = dc.deal_id
LEFTJOIN {{ ref("dim_hs_contacts") }} c
ON dc.contact_id = c.contact_id
LEFTJOIN {{ ref("hubspot_form_submissions") }} f
ONLOWER(f.email) =LOWER(c.email)
AND f.form_fill_date < d.createdate
Key points:
hs_deals_to_contact_id unnests the associatedcontactids JSON array
Multiple contacts per deal → need aggregation or QUALIFY to dedupe
Match contacts to forms by email AND/OR b_visitor_id
Timestamp filter (form_fill_date < deal.createdate) for attribution
Visitor → Signup → Subscription
FROM {{ ref("first_pageviews") }} fp
LEFTJOIN {{ ref("signups") }} s
ON fp.visitor_id = s.visitor_id
LEFTJOIN {{ ref("dim_subscriptions") }} sub
ON s.root_organization_id = sub.root_id
Contact → User → Organization
-- Use product_signups for user dataFROM {{ ref("dim_hs_contacts") }} c
LEFTJOIN {{ ref("product_signups") }} ps
ONLOWER(ps.email) =LOWER(c.email)
OR (ps.user_id ISNOT NULLAND ps.user_id = c.builder_user_id)
LEFTJOIN {{ ref("dim_root_organizations") }} ro
ON ps.user_id = ro.user_id -- or use appropriate join keyWHERE ps.user_create_d ISNOT NULL
Important: Use dbt_analytics.product_signups for signup data - it has the most complete user coverage. Match on both email and user_id for best results.
Testing Queries Before Creating Models
Always test SQL before creating dbt model:
Create test script in code/scripts/test-<feature>.sql
Write BigQuery SQL with fully qualified table names:
FROM `your-project-id.dbt_mart.dim_hs_deals`
Create runner script in code/scripts/test-<feature>.ts:
Convert to dbt syntax (replace table names with {{ ref("table") }})
Save final SQL to code/.builder/dbt-models/<model_name>.sql
Deal Motion Classification Patterns
Warm Outbound Detection
To detect if a contact had a product signup before deal creation, use dbt_analytics.product_signups:
-- Join to product_signups (match by email OR user_id)-- AND signup was BEFORE deal creationLEFTJOIN {{ ref("product_signups") }} ps
ON (
LOWER(ps.email) =LOWER(c.email)
OR (ps.user_id ISNOT NULLAND ps.user_id = c.builder_user_id)
)
AND ps.user_create_d < d.createdate
Key columns in product_signups:
user_id - Builder user ID
email - User email
user_create_d (TIMESTAMP) - Signup/user creation date
Critical: Match on both email AND user_id with OR logic for complete coverage. user_create_d is already TIMESTAMP, no conversion needed.
-name:deals_inbound_outbound_motiondescription:>
Classifies Enterprise deals as Inbound or Outbound based on whether
any associated contact filled a qualifying form before deal creation.
columns:-name:deal_iddescription:Uniquedealidentifier-name:deal_motiondescription:"Inbound or Outbound classification"-name:qualifying_form_countdescription:"Number of distinct qualifying forms filled by associated contacts"-name:first_qualifying_form_namedescription:"Name of earliest qualifying form"
Performance Considerations
Byte limits: BigQuery queries have 750GB byte limit
