dbt

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.

2. ARRAY_AGG Syntax

❌ WRONG (DISTINCT + ORDER BY non-argument):

ARRAY_AGG(DISTINCT form_name IGNORE NULLS ORDER BY form_fill_date LIMIT 1)

✅ CORRECT (remove DISTINCT or order by same column):

-- Option 1: Remove DISTINCT (ORDER BY creates uniqueness)
ARRAY_AGG(form_name IGNORE NULLS ORDER BY form_fill_date LIMIT 1)[SAFE_OFFSET(0)]

-- Option 2: Order by the aggregated column
ARRAY_AGG(DISTINCT form_name ORDER BY 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 BOOL
CASE WHEN CAST(is_closed_won AS STRING) = 'true' THEN 1 ELSE 0 END

-- Amounts may be STRING, cast to numeric
SUM(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 *
FROM table
QUALIFY ROW_NUMBER() OVER (PARTITION BY deal_id ORDER BY created_date) = 1

6. NULL-Safe Joins

When joining on potentially NULL columns (like visitor IDs):

LEFT JOIN forms f
  ON (
    LOWER(f.email) = LOWER(c.email)
    OR (f.b_visitor_id IS NOT NULL AND f.b_visitor_id = c.b_visitor_id)
  )

Common Join Paths

HubSpot Deals → Contacts → Forms

FROM {{ ref("dim_hs_deals") }} d
LEFT JOIN {{ ref("hs_deals_to_contact_id") }} dc
  ON d.deal_id = dc.deal_id
LEFT JOIN {{ ref("dim_hs_contacts") }} c
  ON dc.contact_id = c.contact_id
LEFT JOIN {{ ref("hubspot_form_submissions") }} f
  ON LOWER(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
LEFT JOIN {{ ref("signups") }} s
  ON fp.visitor_id = s.visitor_id
LEFT JOIN {{ ref("dim_subscriptions") }} sub
  ON s.root_organization_id = sub.root_id

Contact → User → Organization

-- Use product_signups for user data
FROM {{ ref("dim_hs_contacts") }} c
LEFT JOIN {{ ref("product_signups") }} ps
  ON LOWER(ps.email) = LOWER(c.email)
  OR (ps.user_id IS NOT NULL AND ps.user_id = c.builder_user_id)
LEFT JOIN {{ ref("dim_root_organizations") }} ro
  ON ps.user_id = ro.user_id -- or use appropriate join key
WHERE ps.user_create_d IS NOT 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:

import { runQuery } from "../server/lib/bigquery";
import { readFileSync } from "fs";
const sql = readFileSync("scripts/test-<feature>.sql", "utf-8");
const result = await runQuery(sql);
console.log(result.rows);

Run: pnpm action test-<feature>
Iterate until results are correct
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 creation
LEFT JOIN {{ ref("product_signups") }} ps
  ON (
    LOWER(ps.email) = LOWER(c.email)
    OR (ps.user_id IS NOT NULL AND 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.

Do NOT use:

dbt_staging_bigquery.signups - incomplete coverage
dim_hs_contacts.sign_up_time_stamp - DATE type, requires conversion and has gaps

Form Submission Attribution

When attributing form submissions to deals/contacts:

Qualifying form categories (based on actual data analysis):

WHERE (
  -- Sales-related forms
  LOWER(form_name) LIKE '%sales%'
  OR LOWER(conversion_details) LIKE '%sales%'

  -- Demo forms
  OR LOWER(form_name) LIKE '%demo%'
  OR LOWER(conversion_details) LIKE '%demo%'

  -- Specific high-intent forms
  OR form_name = '[Marketing]  | Component Indexing Request'
  OR conversion_details = 'Unlock Ent Trial'
)

Common form names (March 2026 data):

[Marketing] Sales Demo Form | 7.20.23 - 5,803 submissions
[Marketing] Sales Demo Form - Unlock Enterprise Features - 3,971 submissions
Demo Library Form - 1,866 submissions
[Marketing] | Component Indexing Request - 51 submissions

Model Documentation

Add to dbt/models/analytics/_models.yml:

- name: deals_inbound_outbound_motion
  description: >
    Classifies Enterprise deals as Inbound or Outbound based on whether 
    any associated contact filled a qualifying form before deal creation.
  columns:
    - name: deal_id
      description: Unique deal identifier
    - name: deal_motion
      description: "Inbound or Outbound classification"
    - name: qualifying_form_count
      description: "Number of distinct qualifying forms filled by associated contacts"
    - name: first_qualifying_form_name
      description: "Name of earliest qualifying form"

Performance Considerations

Byte limits: BigQuery queries have 750GB byte limit
Table size: dim_hs_deals ~3,400 rows, hubspot_form_submissions ~20K rows
Caching: 24-hour cache in server/lib/bigquery.ts
Enterprise filter: Always filter to Enterprise pipelines early in WHERE clause
Avoid: Unnecessary JOINs, avoid SELECT * from large tables

Useful AI Instructions to Add

Based on this analysis, here are additional AI instructions that would be helpful:

Form submission attribution logic - Document the exact form categories that qualify as "inbound"
Deal-to-contact join patterns - The unnesting of associatedcontactids is non-obvious
Column name mapping reference - Centralized list of common mismatches
Type casting patterns - Which fields need CAST and to what type
Email matching best practices - Always case-insensitive, NULL-safe for visitor IDs
ARRAY_AGG syntax rules - DISTINCT + ORDER BY gotcha
QUALIFY usage - Preferred over subquery for window function filtering
Enterprise pipeline filter - Standard WHERE clause for enterprise deals