dbt Unit Test Edge Case Patterns

Unit tests are a tool for documenting and enforcing edge case behavior. Three categories come up repeatedly: null handling, empty upstream tables, and date boundaries.

Null Handling

SQL’s null semantics are notoriously confusing. NULL + 100 = NULL. NULL = NULL is not TRUE. SUM() ignores nulls, but AVG() doesn’t count null rows in the denominator. These behaviors lead to subtle bugs that only manifest when null values appear in production.

Test that your aggregations handle nulls correctly:

unit_tests:
  - name: test_int_customers_revenue_null_handling
    model: int__customers_revenue
    description: "Null order values should be treated as zero in sum"
    given:
      - input: ref('base__shopify__orders')
        rows:
          - {customer_id: 1, order_value: 100}
          - {customer_id: 1, order_value: null}
          - {customer_id: 1, order_value: 50}
          - {customer_id: 2, order_value: null}  # All nulls
    expect:
      rows:
        - {customer_id: 1, total_revenue: 150}
        - {customer_id: 2, total_revenue: 0}

This test clarifies expected behavior for two scenarios:

• Customer 1 has three orders: 100, null, 50. SQL’s SUM() ignores the null and returns 150. But does your model use COALESCE(order_value, 0) before summing, or rely on SUM()’s null-ignoring behavior? The result is the same here, but the distinction matters if you later switch to a different aggregation.

• Customer 2 has only null orders. SUM() over all-null rows returns NULL, not 0. If your model should return 0 for customers with no valid orders, you need COALESCE(SUM(order_value), 0). The expected value documents whichever behavior you’ve chosen.

The comment “Or null, depending on your logic” is deliberate. The test isn’t prescribing the right behavior — it’s documenting and enforcing whatever your model does. If a future change accidentally flips null-to-zero or zero-to-null, the test catches it.

Empty Tables

What happens when an upstream table is empty? Maybe it’s a new deployment with no historical data. Maybe a source system failed and delivered zero records. Your model should handle this gracefully — either returning zero rows or returning a row with default values.

The challenge: format: dict with rows: [] doesn’t work in dbt unit tests. dbt can’t infer the column types from an empty list. The solution is format: sql with a WHERE false clause:

unit_tests:
  - name: test_mrt_finance_daily_revenue_empty_orders
    model: mrt__finance__daily_revenue
    description: "Model should return zero revenue for days with no orders"
    given:
      - input: ref('base__shopify__orders')
        format: sql
        rows: |
          select
            cast(null as string) as order_id,
            cast(null as date) as order_date,
            cast(null as float64) as order_value
          where false
      - input: ref('int__dates')
        rows:
          - {date_key: "2024-06-01"}
    expect:
      rows:
        - {date_key: "2024-06-01", daily_revenue: 0, order_count: 0}

The SQL format uses explicit casting (cast(null as string)) to define the column schema, then WHERE false ensures zero rows are returned. This gives dbt the type information it needs without providing any data.

The test verifies that when orders are empty but dates exist, the model still produces output with zero revenue — rather than returning nothing or throwing an error. This is especially important for reporting models that need to show all dates even when no activity occurred.

This format: sql with WHERE false technique is useful beyond empty table testing. Anytime you need to provide typed columns that the standard rows: format struggles with (complex nested types, specific BigQuery types like STRUCT or ARRAY), the SQL format gives you full control.

Date Boundaries

Date logic is a minefield. Fiscal years that don’t align with calendar years. Leap years. Week number calculations that vary by country. Time zones that shift during daylight saving transitions. These edge cases cause real bugs in production.

A fiscal calendar model is a perfect candidate for boundary testing:

unit_tests:
  - name: test_int_fiscal_calendar_boundaries
    model: int__fiscal_calendar
    description: "Fiscal year should handle year-end correctly (FY starts April 1)"
    given:
      - input: ref('int__dates')
        rows:
          - {calendar_date: "2024-03-31"}  # Last day of FY2024
          - {calendar_date: "2024-04-01"}  # First day of FY2025
          - {calendar_date: "2024-02-29"}  # Leap year
          - {calendar_date: "2024-12-31"}  # Calendar year end
    expect:
      rows:
        - {calendar_date: "2024-03-31", fiscal_year: 2024, fiscal_quarter: 4}
        - {calendar_date: "2024-04-01", fiscal_year: 2025, fiscal_quarter: 1}
        - {calendar_date: "2024-02-29", fiscal_year: 2024, fiscal_quarter: 4}
        - {calendar_date: "2024-12-31", fiscal_year: 2025, fiscal_quarter: 3}

This test assumes a fiscal year starting April 1. Each row tests a specific edge case:

• March 31, 2024: Last day of FY2024 Q4. The fiscal year ends here.
• April 1, 2024: First day of FY2025 Q1. One day later, different fiscal year and quarter.
• February 29, 2024: Leap year date. Verifies the model doesn’t break on Feb 29.
• December 31, 2024: Calendar year end, but mid-fiscal-year (FY2025 Q3). If someone hardcodes a calendar year assumption, this fails.

The same boundary testing approach from Unit Testing CASE WHEN Boundary Logic in dbt applies here: test the transition points, not the middle of each period. A date in the middle of July is boring — it’s unambiguously Q2 in a fiscal year starting April 1. The dates that break things are the ones at boundaries.

General Edge Case Strategy

For any model, three questions are worth checking:

1. What happens when input values are null? Nulls propagate through calculations and produce unexpected results downstream if not handled explicitly.

2. What happens when an input table is empty? If the model joins or aggregates from an empty table, does it return no rows, default rows, or error?

3. What happens at boundary values? Dates at period transitions, numbers at threshold boundaries, strings at length limits.

If the answers are known with confidence, no test is needed. If the behavior is uncertain, write the test.