DbtCAUTION

A framework for building agentic apps

The instruction file, verbatim from the audited commit — this is the text the model reads, and the surface the audit's instruction layer examines. Quoted here so you can judge it without cloning anything.

---
name: dbt
description: >
  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:

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

| 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

```sql
{{
    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):

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

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

```sql
-- 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:

```sql
-- 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:

```sql
LOWER(qf.email) = LOWER(c.email)
```

### 5. QUALIFY for Deduplication

Use `QUALIFY` for window function filtering (cleaner than subquery):

```sql
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):

```sql
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

```sql
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

```sql
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

```sql
-- 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 k

CAUTIONgrade B · trust 89/100 Install with care. The audit found things worth knowing before you trust its output.

What the source does

Filesystem

none-observed

Network

none-observed

Shell

none-observed

Dependencies

pinned

Secrets in source

none-found

Findings (5)

.agents/skills/an

.agents/skills/content-product-development

.agents/skills/design-exploration

.agents/skills/turn-into-app

.agents/skills/visual-edit

Gates applied: no_behavioural_pass.

[![ModelsAtlas audit: Dbt](https://modelsatlas.com/skills/builderio/dbt/badge.svg)](https://modelsatlas.com/skills/builderio/dbt/)

Not your listing, or something out of date? Claim or update your listing.