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:

1. Create SQL in code/.builder/dbt-models/ first
2. Test the query using a script in code/scripts/
3. Validate results before requesting deployment
4. User must manually copy to dbt/models/ directory

Schema Organization

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

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:

1. Create test script in code/scripts/test-<feature>.sql
2. Write BigQuery SQL with fully qualified table names:

   FROM `your-project-id.dbt_mart.dim_hs_deals`
   

3. 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);
   

4. Run: pnpm action test-<feature>
5. Iterate until results are correct
6. Convert to dbt syntax (replace table names with {{ ref("table") }})
7. 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:

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