DOCSEARCHbyAlgolia
Submit Docs

dbt-expectations

repository·main·Indexed 41 days ago

https://github.com/metaplane/dbt-expectations

An extension package for dbt 1.7.x and higher that implements Great Expectations-style data quality tests directly within the data warehouse. It provides a wide range of validation tests for table shape, row and column counts, nullity, uniqueness, regex and LIKE pattern matching, statistical aggregates, and distributional functions. Supported warehouses include Postgres, Snowflake, BigQuery, DuckDB, Trino, and Spark (experimental).

Tokens
6.5K
Snippets
21
Records
22
Agent score
1%

What's inside dbt-expectations

  1. Overview of available dbt-expectations tests

    main

    The dbt-expectations package provides a wide range of tests categorized by the type of validation they perform:

    Table shape

    Tests for column existence, row recency, table aggregation comparisons, column counts, and row counts.

    Missing values, unique values, and types

    Tests for nullity, uniqueness, data types, and casing consistency.

    Sets and ranges

    Tests for value membership in sets, range boundaries, and monotonic trends (increasing/decreasing).

    String matching

    Tests for string length and pattern matching using regex or LIKE patterns.

    Aggregate functions

    Tests based on statistical aggregates like distinct counts, max, mean, median, min, stdev, sum, and quantiles.

    Multi-column

    Tests involving relationships between multiple columns, such as pair comparisons, compound uniqueness, or sums across columns.

    Distributional functions

    Tests for statistical distributions, such as values within N standard deviations or data density over date parts.

  2. Install dbt-expectations

    main

    To use dbt-expectations, add it to your packages.yml file. This package requires dbt 1.7.x or higher.

    Supported data warehouses:

    • Postgres
    • Snowflake
    • BigQuery
    • DuckDB
    • Spark (experimental)
    • Trino

    Note: This package includes a reference to dbt-date, so you do not need to import dbt-date separately in your project.

    packages:
    - package: metaplane/dbt_expectations
      version: 0.10.9 # Check https://github.com/metaplane/dbt-expectations/releases for the latest release

  3. How to use dynamic dates with expect_row_values_to_have_data_for_every_n_datepart

    main

    When testing large or unpartitioned tables, specifying explicit test_start_date and test_end_date is recommended to avoid performance issues. You can use modules.datetime to create dynamic date literals.

    Note: You cannot use raw SQL dynamic dates like current_date or macros from other packages (like dbt-date) because the underlying date_spine macro requires a date literal.

    Example using modules.datetime:

        date_part: day
    test_start_date: '2021-05-01'
    test_end_date: '{{ modules.datetime.date.today() }}'

    Example for yesterday:

        date_part: day
    test_start_date: '2021-05-01'
    test_end_date: '{{ modules.datetime.date.today() - modules.datetime.timedelta(1) }}'

  5. Exclude specific dates in expect_row_values_to_have_data_for_every_n_datepart

    main

    If you have known or expected missing dates (like holidays), use the exclusion_condition to prevent the test from failing on those dates.

    Example: Exclude a specific single date:

    exclusion_condition: not(date_day = '2021-10-19')

    Example: Exclude Christmas Day every year:

    exclusion_condition: not(date_part(month, date_day) = 12 and date_part(day, date_day) = 25)

  6. Use expect_column_values_to_be_within_n_moving_stdevs

    main

    This test expects metric values to be within a specified number of moving standard deviations from the mean. It is useful for detecting anomalies in time-series data.

    Configuration Options:

    • date_column_name: The column containing the date/time information.
    • period: The granularity of the moving window (e.g., day). Default is day.
    • lookback_periods: Number of periods to look back. Default is 1.
    • trend_periods: Number of periods to use for trend calculation. Default is 7.
    • test_periods: Number of periods to test. Default is 14.
    • sigma_threshold: The Z-score threshold. Default is 3.
    • sigma_threshold_upper: Upper bound threshold. Default is None.
    • sigma_threshold_lower: Lower bound threshold. Default is None.
    • take_logs: Whether to log details. Default is true.
    • take_diffs: Whether to log differences. Default is true.
    • group_by: A list of columns to group the calculation by. Default is None.
    tests:
  - dbt_expectations.expect_column_values_to_be_within_n_moving_stdevs:
      date_column_name: date
      period: day
      lookback_periods: 1
      trend_periods: 7
      test_periods: 14
      sigma_threshold: 3
      take_logs: true
      sigma_threshold_upper: x
      sigma_threshold_lower: y
      take_diffs: true
      group_by: [group_id]

  7. Validate column values and types

    main

    These tests are applied to individual columns to ensure data quality and integrity.

    Basic Value Tests

    • expect_column_values_to_be_unique: Ensures all values in the column are unique.
    • expect_column_values_to_not_be_null: Ensures no null values exist.
    • expect_column_values_to_be_null: Ensures all values are null.
    • expect_column_values_to_be_in_set: Checks if values are within a value_set. Use quote_values: true (default) to handle string quoting.
    • expect_column_values_to_not_be_in_set: Checks if values are NOT in a value_set.
    • expect_column_values_to_be_between: Checks if values are within min_value and max_value. Use strictly: true to exclude boundaries.

    Type and Casing Tests

    • expect_column_values_to_be_of_type: Verifies the column matches a column_type (e.g., date).
    • expect_column_values_to_be_in_type_list: Verifies the column matches one of several types in column_type_list.
    • expect_column_values_to_have_consistent_casing: Verifies casing consistency. Use display_inconsistent_columns: true to see the count of inconsistent values in the terminal.
    tests:
  - dbt_expectations.expect_column_values_to_be_unique:
      row_condition: "id is not null"

  - dbt_expectations.expect_column_values_to_be_in_set:
      value_set: ['a','b','c']
      quote_values: true

  - dbt_expectations.expect_column_values_to_be_of_type:
      column_type: date

  8. Use expect_column_distinct_count_to_equal_other_table

    main

    Expect the number of distinct values in a specific column to match the number of distinct values in a column from a different model.

    If compare_model or compare_column_name are not specified, the test defaults to the current model and current column name. This allows for comparing different columns within the same model or identically named columns across different models.

    models:
  - name: my_model_1
    tests:
      - dbt_expectations.expect_column_distinct_count_to_equal_other_table:
          column_name: col_1
          compare_model: ref("my_model_2")
          compare_column_name: col_2
          row_condition: "id is not null" # (Optional)
          compare_row_condition: "id is not null" # (Optional)

  9. Validate string lengths in a column

    main

    Use these tests to enforce constraints on the length of string entries in a column.

    • expect_column_value_lengths_to_be_between: Ensures lengths are within min_value and max_value (inclusive). Use strictly: true to make the comparison exclusive.
    • expect_column_value_lengths_to_equal: Ensures all string entries have a length exactly equal to value.
    tests:
  - dbt_expectations.expect_column_value_lengths_to_be_between:
      min_value: 1
      max_value: 4
      strictly: false
  - dbt_expectations.expect_column_value_lengths_to_equal:
      value: 10

  10. Validate table column counts and sets

    main

    These tests verify the schema composition of a Model, Seed, or Source.

    • expect_table_column_count_to_be_between: Checks if the number of columns is within a min_value and max_value range.
    • expect_table_column_count_to_equal_other_table: Compares the column count of the current model to a compare_model.
    • expect_table_column_count_to_equal: Checks if the column count matches a specific value.
    • expect_table_columns_to_contain_set: Verifies the table contains a specific list of columns. Supports an optional transform (e.g., upper).
    • expect_table_columns_to_not_contain_set: Verifies the table does NOT contain a specific list of columns.
    • expect_table_columns_to_match_ordered_list: Verifies columns match a list exactly in order.
    • expect_table_columns_to_match_set: Verifies columns match a list regardless of order.
    models:
  - name: my_model
    tests:
      - dbt_expectations.expect_table_column_count_to_be_between:
          min_value: 1
          max_value: 4
      - dbt_expectations.expect_table_columns_to_contain_set:
          column_list: ["col_a", "col_b"]
          transform: upper

  11. Match column values against SQL LIKE patterns

    main

    Use these tests to validate string patterns using standard SQL LIKE syntax.

    • expect_column_values_to_match_like_pattern: Validates entries match a single like_pattern.
    • expect_column_values_to_not_match_like_pattern: Validates entries do NOT match a single like_pattern.
    • expect_column_values_to_match_like_pattern_list: Validates entries match any pattern in a list. Use match_on: any (default) or match_on: all.
    • expect_column_values_to_not_match_like_pattern_list: Validates entries do not match any pattern in a list.
    tests:
  - dbt_expectations.expect_column_values_to_match_like_pattern:
      like_pattern: "%@%"
  - dbt_expectations.expect_column_values_to_match_like_pattern_list:
      like_pattern_list: ["%@%", "%&%"]
      match_on: any

  12. Validate table row counts

    main

    Use these tests to ensure the volume of data in your models is correct.

    • expect_table_row_count_to_be_between: Checks if row count is within min_value and max_value. Use strictly: true to exclude the boundary values. Supports group_by and row_condition.
    • expect_table_row_count_to_equal_other_table: Compares row count to another model. Supports group_by, compare_group_by, and a factor multiplier.
    • expect_table_row_count_to_equal_other_table_times_factor: Compares row count to another model multiplied by a factor.
    • expect_table_row_count_to_equal: Checks if the row count matches a specific value. Supports group_by and row_condition.
    models:
  - name: my_model
    tests:
      - dbt_expectations.expect_table_row_count_to_be_between:
          min_value: 1
          max_value: 4
          group_by: [group_id]
          strictly: false
      - dbt_expectations.expect_table_row_count_to_equal_other_table:
          compare_model: ref("other_model")
          factor: 1