dbt Testing Patterns: Null, Accepted Values & Relationships for Real-Time Marketing Alerts

20 minutes to read

February 5, 2026

Discovering that your ad spend exceeded budget by 40% at 5 PM on a Friday is a nightmare no marketing team should face. Yet teams waste 5 to 8 hours weekly manually checking dashboards, often catching campaign issues only after significant ROI loss has occurred.

Data quality testing in dbt transforms this reactive firefighting into proactive protection. By implementing robust testing patterns for null values, accepted values, and relationships, you create a safety net that catches problems before they propagate to your reports and downstream decisions.

This guide walks you through three core dbt testing patterns specifically designed for marketing analytics teams. We go beyond basic syntax to show you how to integrate these tests with real-time Slack alerts, so your team receives instant notifications when daily ad spend crosses a limit, conversion tracking breaks, or attribution data fails integrity checks.

By the end of this article, you will have production-ready code snippets for dbt schema configurations, anomaly detection models, and webhook integrations that deliver alerts to Slack in under 2 minutes.

Why Data Quality Testing is Essential for Marketing Analytics

The Hidden Cost of Bad Marketing Data

Marketing data quality issues are expensive. When campaign issues like overspend or broken tracking go undetected, teams experience an average 30% ROI loss before the problem surfaces in reports. Common failure modes include:

Null campaign IDs that break attribution models
Invalid status values are causing incorrect spend calculations
Orphaned records where conversions cannot be traced to campaigns

These issues compound quickly. A single null value in your ad_platform_id field can cascade through joins, producing misleading reports that inform flawed budget decisions.

From Reactive Dashboards to Proactive Alerts

Traditional dashboard monitoring puts your team in a reactive position. Someone needs to notice the problem, investigate the root cause, and then alert stakeholders. This cycle often takes days.

Automated dbt testing flips this model. Tests run as part of your data pipeline, catching anomalies at the source. When combined with Slack integration, your marketing ops team receives immediate notifications the moment data quality degrades.

This approach aligns with modern data governance principles. The NIST Research Data Framework 2.0 emphasizes proactive data quality monitoring as essential for maintaining data integrity across analytical workflows.

Pattern 1: Null Checks in dbt

The not_null Test: Your First Line of Defense

Null values in marketing data often indicate upstream pipeline failures, broken integrations, or data source issues. The dbt official documentation on data tests provides the not_null test as a foundational check for data completeness.

Here is a basic implementation for a staging model:

# models/staging/ads/_ads__models.yml
version: 2

models:
  - name: stg_google_ads__campaigns
    columns:
      - name: campaign_id
        description: "Unique identifier for the campaign"
        data_tests:
          - unique
          - not_null
      - name: campaign_name
        description: "Display name of the campaign"
        data_tests:
          - not_null

Critical Marketing Columns to Test for Nulls

For marketing data models, certain columns require mandatory null checks because they drive attribution, reporting, and budget calculations:

# models/marts/marketing/_marketing__models.yml
version: 2

models:
  - name: fct_ad_performance
    columns:
      - name: campaign_id
        data_tests:
          - not_null
      - name: customer_id
        data_tests:
          - not_null
      - name: ad_spend
        description: "Daily spend amount in USD"
        data_tests:
          - not_null
      - name: conversion_value
        description: "Revenue attributed to this campaign"
        data_tests:
          - not_null
      - name: ad_platform_id
        data_tests:
          - not_null

Conditional Null Tests with Custom Macros

Sometimes nulls are acceptable in specific contexts. For example, cancelled_at should only be non-null when order_status equals ‘cancelled’. Create a custom test macro for these scenarios:

-- tests/generic/not_null_when_status.sql
{% test not_null_when_status(model, column_name, status_column, required_status) %}
    select *
    from {{ model }}
    where {{ status_column }} = '{{ required_status }}'
      and {{ column_name }} is null
{% endtest %}

Reference this custom test in your schema:

columns:
  - name: cancellation_reason
    data_tests:
      - not_null_when_status:
          status_column: campaign_status
          required_status: 'PAUSED'

Pattern 2: Accepted Values and Domain Constraints

Using accepted_values for Marketing Categorical Data

Marketing datasets contain categorical fields that must match predefined domain values. Invalid values indicate data corruption or upstream changes that require investigation.

columns:
  - name: campaign_status
    description: "Current state of the campaign"
    data_tests:
      - accepted_values:
          values: ['ACTIVE', 'PAUSED', 'ENDED', 'REMOVED']
  - name: channel_type
    description: "Marketing channel classification"
    data_tests:
      - accepted_values:
          values: ['SEARCH', 'DISPLAY', 'VIDEO', 'SHOPPING', 'SOCIAL']
  - name: region_code
    description: "Geographic targeting region"
    data_tests:
      - accepted_values:
          values: ['US', 'CA', 'UK', 'DE', 'FR', 'AU']

Custom Range Tests for Marketing Metrics

For numeric marketing metrics, you need range validation to catch anomalies. Ad spend should never be negative, and CTR should fall within realistic bounds:

-- tests/generic/value_in_range.sql
{% test value_in_range(model, column_name, min_value, max_value) %}
    select *
    from {{ model }}
    where {{ column_name }} < {{ min_value }}
       or {{ column_name }} > {{ max_value }}
{% endtest %}

Apply this to your marketing metrics:

columns:
  - name: ad_spend
    data_tests:
      - value_in_range:
          min_value: 0
          max_value: 100000
  - name: click_through_rate
    data_tests:
      - value_in_range:
          min_value: 0
          max_value: 1

Documenting Domain Constraints for Compliance

Well-documented constraints create an audit trail that satisfies compliance requirements and accelerates onboarding. Include descriptions that explain the business rationale:

- name: conversion_type
  description: "Type of conversion event. Must be one of the defined values to ensure accurate ROAS calculations across reporting systems."
  data_tests:
    - accepted_values:
        values: ['PURCHASE', 'LEAD', 'SIGNUP', 'ADD_TO_CART']

Pattern 3: Relationship (Referential Integrity) Tests

Ensuring Foreign Key Integrity in Marketing Data

Orphaned records break attribution models. When a conversion record references a campaign_id that does not exist in your campaigns dimension, that revenue becomes unattributable, creating gaps in your marketing performance analysis.

The relationships test validates referential integrity:

# models/marts/marketing/_marketing__models.yml
version: 2

models:
  - name: fct_conversions
    columns:
      - name: campaign_id
        description: "Foreign key to dim_campaigns"
        data_tests:
          - relationships:
              to: ref('dim_campaigns')
              field: campaign_id
      - name: customer_id
        description: "Foreign key to dim_customers"
        data_tests:
          - relationships:
              to: ref('dim_customers')
              field: customer_id

Marketing-Specific Relationship Examples

Build a comprehensive referential integrity layer for your marketing data mart:

columns:
  - name: ad_platform_id
    data_tests:
      - relationships:
          to: ref('dim_ad_platforms')
          field: platform_id
  - name: creative_id
    data_tests:
      - relationships:
          to: ref('dim_creatives')
          field: creative_id
  - name: audience_segment_id
    data_tests:
      - relationships:
          to: ref('dim_audience_segments')
          field: segment_id

Common Pitfalls and Best Practices

Late-arriving dimensions present challenges for relationship tests. If your campaign dimension updates after your fact table, legitimate records may fail the test. Use conditional relationship tests to handle this:

- name: campaign_id
  data_tests:
    - relationships:
        to: ref('dim_campaigns')
        field: campaign_id
        config:
          where: "campaign_id is not null and created_at < current_date"

Integrating dbt Tests with Real-Time Slack Alerts

Architecture Overview: dbt to Webhook to Slack

The alerting architecture follows a straightforward flow:

DBT runs your data models and executes tests
When tests fail, dbt Cloud triggers a webhook
A serverless function (AWS Lambda or Google Cloud Functions) receives the webhook payload
The function formats and sends a message to your Slack channel

According to dbt Cloud webhook documentation, you can subscribe to three event types: job.run.started, job.run.completed, and job.run.errored.

Setting Up Threshold Detection Models in dbt

Create a dbt model that flags anomalies based on business thresholds. This model identifies when daily ad spend exceeds your defined limit:

-- models/alerts/alert_ad_spend_threshold.sql
{{ config(
    materialized='table',
    tags=['alerts']
) }}

with daily_spend as (
    select
        date_trunc('day', event_date) as spend_date,
        campaign_id,
        campaign_name,
        sum(ad_spend) as total_daily_spend
    from {{ ref('fct_ad_performance') }}
    where event_date >= current_date - interval '1 day'
    group by 1, 2, 3
)

select
    spend_date,
    campaign_id,
    campaign_name,
    total_daily_spend,
    case
        when total_daily_spend > 10000 then 'CRITICAL'
        when total_daily_spend > 7500 then 'WARNING'
        else 'NORMAL'
    end as alert_level
from daily_spend
where total_daily_spend > 7500

Triggering Webhooks for Slack Notifications

According to Slack’s incoming webhooks documentation, you need to create a Slack app and enable incoming webhooks to receive messages programmatically.

Here is a Python function for AWS Lambda that processes dbt webhook events and sends Slack alerts:

# lambda_function.py
import json
import os
import hmac
import hashlib
import urllib.request

def lambda_handler(event, context):
    # Validate webhook authenticity
    body = event.get('body', '')
    auth_header = event.get('headers', {}).get('authorization', '')
    
    secret = os.environ['DBT_WEBHOOK_SECRET'].encode('utf-8')
    expected_signature = hmac.new(secret, body.encode('utf-8'), hashlib.sha256).hexdigest()
    
    if not hmac.compare_digest(expected_signature, auth_header):
        return {'statusCode': 401, 'body': 'Unauthorized'}
    
    # Parse dbt webhook payload
    payload = json.loads(body)
    event_type = payload.get('eventType', '')
    data = payload.get('data', {})
    
    if event_type == 'job.run.errored':
        slack_message = format_slack_alert(data)
        send_slack_notification(slack_message)
    
    return {'statusCode': 200, 'body': 'OK'}

def format_slack_alert(data):
    return {
        "blocks": [
            {
                "type": "header",
                "text": {
                    "type": "plain_text",
                    "text": "Marketing Data Alert"
                }
            },
            {
                "type": "section",
                "fields": [
                    {"type": "mrkdwn", "text": f"*Job:* {data.get('jobName', 'Unknown')}"},
                    {"type": "mrkdwn", "text": f"*Status:* {data.get('runStatus', 'Unknown')}"},
                    {"type": "mrkdwn", "text": f"*Project:* {data.get('projectName', 'Unknown')}"},
                    {"type": "mrkdwn", "text": f"*Environment:* {data.get('environmentName', 'Unknown')}"}
                ]
            },
            {
                "type": "section",
                "text": {
                    "type": "mrkdwn",
                    "text": "Daily ad spend has exceeded the $10,000 threshold. Review campaign budgets immediately."
                }
            }
        ]
    }

def send_slack_notification(message):
    slack_url = os.environ['SLACK_WEBHOOK_URL']
    req = urllib.request.Request(
        slack_url,
        data=json.dumps(message).encode('utf-8'),
        headers={'Content-Type': 'application/json'}
    )
    urllib.request.urlopen(req)

Example: Daily Ad Spend Alert Over $10,000

When your daily ad spend monitoring job runs and detects spend exceeding $10,000, the complete flow executes:

The alert_ad_spend_threshold model populates with flagged records
The associated test fails because records exist
dbt Cloud fires the job.run.errored webhook
Lambda processes the payload and posts to Slack

Your marketing team receives a Slack notification like this:

{
  "blocks": [
    {
      "type": "header",
      "text": {"type": "plain_text", "text": "Marketing Data Alert"}
    },
    {
      "type": "section",
      "fields": [
        {"type": "mrkdwn", "text": "*Campaign:* Summer_Sale_2025"},
        {"type": "mrkdwn", "text": "*Spend:* $12,450.00"},
        {"type": "mrkdwn", "text": "*Threshold:* $10,000.00"},
        {"type": "mrkdwn", "text": "*Alert Level:* CRITICAL"}
      ]
    }
  ]
}

CI/CD Automation and Test Management at Scale

Running dbt Tests in GitHub Actions

Integrate dbt tests into your deployment pipeline to catch issues before they reach production:

# .github/workflows/dbt_ci.yml
name: dbt CI
on: [pull_request]

jobs:
  test:
    runs-on: ubuntu-latest
    steps:
      - uses: actions/checkout@v4
      - name: Set up Python
        uses: actions/setup-python@v4
        with:
          python-version: '3.11'
      - name: Install dependencies
        run: pip install dbt-snowflake dbt-utils
      - name: Run dbt tests
        run: |
          dbt deps
          dbt test --select state:modified+
        env:
          DBT_PROFILES_DIR: ./

Test Severity and Ownership Configuration

Prioritize critical marketing tests by configuring severity levels and assigning ownership:

columns:
  - name: ad_spend
    data_tests:
      - not_null:
          config:
            severity: error
            meta:
              owner: marketing_ops
              priority: P1

How Stellans Accelerates Your Marketing Analytics Workflow

Pre-Built Marketing Alert Templates

We work with marketing analytics teams to deploy production-ready alerting systems that integrate seamlessly with existing workflows. Our approach includes customized dbt test configurations for common marketing data sources like Google Ads, Meta, and Salesforce.

Reduced Setup Time and Expert Support

Implementing dbt testing patterns with Slack integration requires expertise across data engineering, cloud infrastructure, and marketing operations. Our team has helped clients reduce manual monitoring time by 5 to 8 hours weekly while catching data quality issues 40% faster.

When you partner with Stellans, you get responsive data workflows that connect your transformation layer directly to your team’s communication tools. We handle the technical complexity so your marketing analysts can focus on optimization rather than data firefighting.

Conclusion

Robust DBT testing patterns form the foundation of trustworthy marketing analytics. By implementing not_null checks for critical identifiers, accepted_values validation for categorical data, and relationships tests for referential integrity, you create multiple layers of protection against data quality failures.

The real power emerges when you connect these tests to real-time alerting. Your team receives Slack notifications within minutes instead of discovering budget overruns days after they occur.

Key takeaways:

Use not_null tests on campaign_id, customer_id, ad_spend, and conversion_value columns
Validate categorical fields like campaign_status and channel_type with accepted_values
Ensure referential integrity between fact and dimension tables with relationship tests
Deploy webhook integrations for immediate Slack notifications on test failures

Ready to implement automated marketing alerts for your data pipeline? Contact Stellans to discuss how we can help you build proactive data quality monitoring that protects your marketing ROI.

Frequently Asked Questions

What are the three core dbt testing patterns for marketing data quality?

The three core DBT testing patterns are: not_null tests to ensure critical fields like campaign_id and ad_spend contain values, accepted_values tests to validate categorical data like campaign status or channel types match predefined lists, and relationships tests to verify foreign key integrity between marketing tables, such as conversions linking to valid campaigns.

How do I integrate DBT tests with Slack for real-time marketing alerts?

You can integrate dbt with Slack by creating a dbt model that detects anomalies or threshold breaches, configuring dbt Cloud webhooks to trigger on job.run.errored events, and setting up a webhook endpoint using AWS Lambda or Google Cloud Functions that receives dbt events and posts formatted alerts to a Slack incoming webhook URL.

Can DBT tests detect anomalies in marketing KPIs like ad spend or conversions?

Yes, dbt can detect marketing KPI anomalies by combining built-in tests with custom SQL models. You can create a dbt model that flags when daily ad spend exceeds a threshold (for example, over $10,000) or when conversion rates drop below a baseline, then trigger Slack alerts via webhooks when these conditions are met.

What is the difference between completed and errored webhook events in dbt Cloud?

The job.run.errored event fires immediately when a job fails, while job.run.completed fires only after metadata and artifacts have been ingested. If your integration needs access to run logs or model statuses from the dbt API, use job.run.completed. If you need faster notification delivery, use job.run.errored and build your integration to handle cases where API data may not be immediately available.

References

dbt Labs. “About data tests property.” dbt Developer Hub. https://docs.getdbt.com/reference/resource-properties/data-tests
dbt Labs. “Webhooks for your jobs.” dbt Developer Hub. https://docs.getdbt.com/docs/deploy/webhooks
Slack. “Sending messages using incoming webhooks.” Slack Developer Docs. https://docs.slack.dev/messaging/sending-messages-using-incoming-webhooks
National Institute of Standards and Technology. “NIST Releases Version 2.0 of Research Data Framework (RDaF).” February 2024. https://www.nist.gov/news-events/news/2024/02/nist-releases-version-20-research-data-framework-rdaf

Article By:

https://stellans.io/wp-content/uploads/2026/01/1723232006354-1.jpg

Roman Sterjanov

Data Analyst

Get free consultation

dbt Testing Patterns: Null, Accepted Values & Relationships for Real-Time Marketing Alerts

Why Data Quality Testing is Essential for Marketing Analytics

The Hidden Cost of Bad Marketing Data

From Reactive Dashboards to Proactive Alerts

Pattern 1: Null Checks in dbt

The not_null Test: Your First Line of Defense

Critical Marketing Columns to Test for Nulls

Conditional Null Tests with Custom Macros

Pattern 2: Accepted Values and Domain Constraints

Using accepted_values for Marketing Categorical Data

Custom Range Tests for Marketing Metrics

Documenting Domain Constraints for Compliance

Pattern 3: Relationship (Referential Integrity) Tests

Ensuring Foreign Key Integrity in Marketing Data

Marketing-Specific Relationship Examples

Common Pitfalls and Best Practices

Integrating dbt Tests with Real-Time Slack Alerts

Architecture Overview: dbt to Webhook to Slack

Setting Up Threshold Detection Models in dbt

Triggering Webhooks for Slack Notifications

Example: Daily Ad Spend Alert Over $10,000

CI/CD Automation and Test Management at Scale

Running dbt Tests in GitHub Actions

Test Severity and Ownership Configuration

How Stellans Accelerates Your Marketing Analytics Workflow

Pre-Built Marketing Alert Templates

Reduced Setup Time and Expert Support

Conclusion

Frequently Asked Questions

What are the three core dbt testing patterns for marketing data quality?

How do I integrate DBT tests with Slack for real-time marketing alerts?

Can DBT tests detect anomalies in marketing KPIs like ad spend or conversions?

What is the difference between completed and errored webhook events in dbt Cloud?

References

Article By:

Roman Sterjanov

Related Posts

Let’s
Talk

Get a Free Data Audit

dbt Testing Patterns: Null, Accepted Values & Relationships for Real-Time Marketing Alerts

Why Data Quality Testing is Essential for Marketing Analytics

The Hidden Cost of Bad Marketing Data

From Reactive Dashboards to Proactive Alerts

Pattern 1: Null Checks in dbt

The not_null Test: Your First Line of Defense

Critical Marketing Columns to Test for Nulls

Conditional Null Tests with Custom Macros

Pattern 2: Accepted Values and Domain Constraints

Using accepted_values for Marketing Categorical Data

Custom Range Tests for Marketing Metrics

Documenting Domain Constraints for Compliance

Pattern 3: Relationship (Referential Integrity) Tests

Ensuring Foreign Key Integrity in Marketing Data

Marketing-Specific Relationship Examples

Common Pitfalls and Best Practices

Integrating dbt Tests with Real-Time Slack Alerts

Architecture Overview: dbt to Webhook to Slack

Setting Up Threshold Detection Models in dbt

Triggering Webhooks for Slack Notifications

Example: Daily Ad Spend Alert Over $10,000

CI/CD Automation and Test Management at Scale

Running dbt Tests in GitHub Actions

Test Severity and Ownership Configuration

How Stellans Accelerates Your Marketing Analytics Workflow

Pre-Built Marketing Alert Templates

Reduced Setup Time and Expert Support

Conclusion

Frequently Asked Questions

What are the three core dbt testing patterns for marketing data quality?

How do I integrate DBT tests with Slack for real-time marketing alerts?

Can DBT tests detect anomalies in marketing KPIs like ad spend or conversions?

What is the difference between completed and errored webhook events in dbt Cloud?

References

Article By:

Roman Sterjanov

Related Posts

Let’s Talk

Get a Free Data Audit

Get a Free Consultation

Let's talk about your project

Select an available slot to get in touch with Stellans so that one of our representatives can contact you and start a discussion.

David Ashirov

Co-founder, CTO

30 minutes

Contact us

Select an available slot to get in touch with Stellans so that one of our representatives can contact you and start a discussion.

Anton Malyshev

Co-founder, COO

30 minutes

Contact us

Select an available slot to get in touch with Stellans so that one of our representatives can contact you and start a discussion.

Vitaly Lilich

Co-founder, CEO

30 minutes

Contact us

Thank You

Thank You

Thank You

Let’s
Talk

Let's talk about
your project

Select an available slot to
get in touch with Stellans
so that one of our representatives can contact you and start a discussion.

Select an available slot to
get in touch with Stellans
so that one of our representatives can contact you and start a discussion.

Select an available slot to
get in touch with Stellans
so that one of our representatives can contact you and start a discussion.

Thank
You

Thank
You

Thank
You