schema.yml

version: 2

seeds:
  - name: data_test_not_constant
    columns:
      - name: field
        data_tests:
          - dbt_utils.not_constant
          - dbt_utils.not_constant:
              group_by_columns: ['col_a']

  - name: data_test_at_least_one
    columns:
      - name: field
        data_tests:
          - dbt_utils.at_least_one
      - name: value
        data_tests:
          - dbt_utils.at_least_one:
              group_by_columns: ['field']
              error_if: "<1"
              warn_if: "<0"

  - name: data_test_expression_is_true
    data_tests:
      - dbt_utils.expression_is_true:
          expression: col_a + col_b = 1
      - dbt_utils.expression_is_true:
          expression: col_a = 0.5
          config:
            where: col_b = 0.5
    columns:
      - name: col_a
        data_tests:
          - dbt_utils.expression_is_true:
              expression: + col_b = 1
      - name: col_b
        data_tests:
          - dbt_utils.expression_is_true:
              expression: = 0.5
              config:
                where: col_a = 0.5

  - name: data_people
    columns:
      - name: is_active
        data_tests:
          - dbt_utils.cardinality_equality:
              field: is_active
              to: ref('data_people')

  - name: data_test_not_accepted_values
    columns:
      - name: city
        data_tests:
          - dbt_utils.not_accepted_values:
              values: ['Madrid', 'Berlin']

  - name: data_test_relationships_where_table_2
    columns:
      - name: id
        data_tests:
          - dbt_utils.relationships_where:
              to: ref('data_test_relationships_where_table_1')
              field: id
              from_condition: id <> 4

  - name: data_test_mutually_exclusive_ranges_no_gaps
    data_tests:
      - dbt_utils.mutually_exclusive_ranges:
          lower_bound_column: lower_bound
          upper_bound_column: upper_bound
          gaps: not_allowed

  - name: data_test_mutually_exclusive_ranges_with_gaps
    data_tests:
      - dbt_utils.mutually_exclusive_ranges:
          lower_bound_column: valid_from
          upper_bound_column: coalesce(valid_to, '2099-01-01')
          partition_by: subscription_id
          gaps: allowed

      - dbt_utils.mutually_exclusive_ranges:
          lower_bound_column: valid_from
          upper_bound_column: coalesce(valid_to, '2099-01-01')
          partition_by: subscription_id
          gaps: required

  - name: data_test_mutually_exclusive_ranges_with_gaps_zero_length
    data_tests:
      - dbt_utils.mutually_exclusive_ranges:
          lower_bound_column: valid_from
          upper_bound_column: valid_to
          partition_by: subscription_id
          zero_length_range_allowed: true

  - name: data_unique_combination_of_columns
    data_tests:
      - dbt_utils.unique_combination_of_columns:
          combination_of_columns:
            - month
            - product

  - name: data_cardinality_equality_a
    columns:
      - name: same_name
        data_tests:
          - dbt_utils.cardinality_equality:
              to: ref('data_cardinality_equality_b')
              field: same_name
          - dbt_utils.cardinality_equality:
              to: ref('data_cardinality_equality_b')
              field: different_name

  - name: data_test_accepted_range
    columns:
      - name: id
        data_tests:
          - dbt_utils.accepted_range:
              min_value: -1
              max_value: 11
              inclusive: true

          - dbt_utils.accepted_range:
              min_value: -2
              max_value: 11.1
              inclusive: false

          - dbt_utils.accepted_range:
              min_value: 0
              inclusive: true
              where: "id <> -1"

  - name: data_not_null_proportion
    columns:
      - name: point_5
        data_tests:
          - dbt_utils.not_null_proportion:
              at_least: 0.5
              at_most: 0.5
          - dbt_utils.not_null_proportion:
              at_least: 0
              group_by_columns: ['point_9']
      - name: point_9
        data_tests:
          - dbt_utils.not_null_proportion:
              at_least: 0.9

  - name: data_test_equality_a
    data_tests:
      - dbt_utils.equality:
          compare_model: ref('data_test_equality_a')
      - dbt_utils.equality:
          compare_model: ref('data_test_equality_b')
          error_if: "<1" #sneaky way to ensure that the test is returning failing rows
          warn_if: "<0"
      - dbt_utils.equality:
          compare_model: ref('data_test_equality_b')
          compare_columns:
            - col_a
            - col_b
      - dbt_utils.equality:
          compare_model: ref('data_test_equality_b')
          exclude_columns:
            - col_c

  - name: data_test_equality_different_column_names_a
    data_tests:
      - dbt_utils.equality:
          compare_model: ref('data_test_equality_different_column_names_b')
          compare_columns:
            - col_a
            - [tbl_a_col_b, tbl_b_col_b]
      - dbt_utils.equality:
          compare_model: ref('data_test_equality_different_column_names_b')
          compare_columns:
            - col_a
            - [tbl_a_col_float, tbl_b_col_float]
          precision: 4
      - dbt_utils.equality:
          compare_model: ref('data_test_equality_different_column_names_b')
          compare_columns:
            - col_a
            - [tbl_a_col_float, tbl_b_col_float]
          precision: 8
          error_if: "<1" #sneaky way to ensure that the test is returning failing rows
          warn_if: "<0"

  - name: data_test_equality_floats_a
    data_tests:
      # test precision only
      - dbt_utils.equality:
          compare_model: ref('data_test_equality_floats_b')
          precision: 4
      - dbt_utils.equality:
          compare_model: ref('data_test_equality_floats_b')
          precision: 8
          error_if: "<1" #sneaky way to ensure that the test is returning failing rows
          warn_if: "<0"

  - name: data_test_equality_floats_columns_a
    data_tests:
    # Positive assertion tests
      - dbt_utils.equality:
          compare_model: ref('data_test_equality_floats_columns_b')
          compare_columns:
            - id
            - float_number
          precision: 4
      - dbt_utils.equality:
          compare_model: ref('data_test_equality_floats_columns_b')
          exclude_columns:
            - to_ignore
          precision: 4
    # all columns should fail even with rounding
      - dbt_utils.equality:
          compare_model: ref('data_test_equality_floats_columns_b')
          precision: 4
          error_if: "<1" #sneaky way to ensure that the test is returning failing rows
          warn_if: "<0"

models:
  - name: recency_time_included
    data_tests:
      - dbt_utils.recency:
          datepart: day
          field: created_at
          interval: 1
      - dbt_utils.recency:
          datepart: day
          field: created_at
          interval: 1
          group_by_columns: ['col1']
      - dbt_utils.recency:
          datepart: day
          field: created_at
          interval: 1
          group_by_columns: ['col1', 'col2']

  - name: recency_time_excluded
    data_tests:
      - dbt_utils.recency:
          datepart: day
          field: created_at
          interval: 1
          ignore_time_component: true
      - dbt_utils.recency:
          datepart: day
          field: created_at
          interval: 1
          ignore_time_component: false
          error_if: "<1" #sneaky way to ensure that the test is returning failing rows
          warn_if: "<0"

  - name: test_equal_rowcount
    data_tests:
      - dbt_utils.equal_rowcount:
          compare_model: ref('test_equal_rowcount')
      - dbt_utils.equal_rowcount:
          compare_model: ref('test_equal_rowcount')
          group_by_columns: ['field']

  - name: test_equal_column_subset
    data_tests:
      - dbt_utils.equality:
          compare_model: ref('data_people')
          compare_columns:
            - first_name
            - last_name
            - email

  - name: test_fewer_rows_than
    data_tests:
      - dbt_utils.fewer_rows_than:
          compare_model: ref('data_test_fewer_rows_than_table_2')
      - dbt_utils.fewer_rows_than:
          compare_model: ref('data_test_fewer_rows_than_table_2')
          group_by_columns: ['col_a']

  - name: equality_less_columns
    data_tests:
      - dbt_utils.equality:
          compare_model: ref('data_test_equality_a')
          exclude_columns:
            - col_c