数据测试

1version: 2
2
3models:
4  - name: good_orders
5    description: 包含有效订单的模型。
6    columns:
7      - name: order_id
8        description: 订单的唯一标识符
9      - name: customer_id
10        data_type: integer
11       
12  - name: bad_orders
13    columns:
14      - name: product_id
15      - name: order_status

1version: 2
2
3models:
4  - name: good_orders
5    columns:
6      - name: order_id
7        tests:
8          - unique

1dbt test

1dbt test -s tag:marketing

1dbt test -s bad_orders

1/* {"app": "dbt", "dbt_version": "1.3.1", "profile_name": "shop", "target_name": "dev", "node_id": "test.shop_project.unique_good_orders_order_id.131034712b"} */
2select
3      count(*) as failures,
4      count(*) != 0 as should_warn,
5      count(*) != 0 as should_error
6    from (
7      
8with dbt_test__target as (
9
10  select order_id as unique_field
11  from `project`.`dataset`.`good_orders`
12  where order_id is not null
13
14)
15
16select
17    unique_field,
18    count(*) as n_records
19
20from dbt_test__target
21group by unique_field
22having count(*) > 1
23      
24    ) dbt_internal_test

1version: 2
2
3models:
4  - name: good_orders
5    columns:
6      - name: order_id
7        tests:
8          - unique
9  - name: bad_orders
10    columns:
11      - name: product_id
12        tests:
13          - not_null

1# 为项目命名！项目名称应仅包含小写字母和下划线。
2# 良好的包名称应反映您的组织名称或这些模型的预期用途
3name: 'shop_project'
4version: '1.0.0'
5config-version: 2
6
7# 此设置配置 dbt 在此项目中使用的“profile”。
8profile: 'shop'
9
10# 这些配置指定 dbt 应在何处查找不同类型的文件。
11# 例如，`model-paths` 配置表示该项目中的模型位于 "models/" 目录中。您可能不需要更改这些配置！
12model-paths: ["models"]
13analysis-paths: ["analyses"]
14test-paths: ["tests"]
15seed-paths: ["seeds"]
16macro-paths: ["macros"]
17snapshot-paths: ["snapshots"]
18
19target-path: "target"  # 存储编译后 SQL 文件的目录
20clean-targets:         # 由 `dbt clean` 命令删除的目录
21  - "target"
22  - "dbt_packages"
23
24
25# 模型配置
26# 完整文档：https://docs.getdbt.com/docs/configuring-models
27tests:
28  +store_failures: true

1version: 2
2
3models:
4  - name: good_orders
5    columns:
6      - name: order_id
7        tests:
8          - unique
9  - name: bad_orders
10    columns:
11      - name: product_id
12        tests:
13          - not_null:
14              config:
15                store_failures: true

1version: 2
2
3models:
4  - name: good_orders
5    columns:
6      - name: order_id
7        tests:
8          - unique
9            config:
10              store_failures_as: view
11  - name: bad_orders
12    columns:
13      - name: product_id
14        tests:
15          - not_null:
16              config:
17                severity: warn
18                config:
19                  store_failures_as: ephemeral

1dbt test --store-failures

1version: 2
2
3models:
4  - name: good_orders
5    columns:
6      - name: order_id
7        tests:
8          - unique
9  - name: bad_orders
10    columns:
11      - name: product_id
12        tests:
13          - not_null:
14              config:
15                severity: warn

dbt test 返回警告

113:24:01  1 of 3 START test not_null_bad_orders_product_id ................................ [RUN]
213:24:02  1 of 3 WARN 2 not_null_bad_orders_product_id ........................... [WARN 2 in 1.14s]

1version: 2
2
3models:
4  - name: good_orders
5    columns:
6      - name: order_id
7        tests:
8          - unique
9  - name: bad_orders
10    columns:
11      - name: product_id
12        tests:
13          - not_null:
14              config:
15                severity: warn
16      - name: order_status
17        tests:
18          - accepted_values:
19             values: ["Shipped", "Canceled", "Delivered", "Accepted"]
20             config:
21              error_if: ">1"

终端输出

115:28:25  1 of 3 START test accepted_values_bad_orders_order_status__Shipped__Cancelled__Delivered__Accepted [RUN]
215:28:26  1 of 3 WARN 1 accepted_values_bad_orders_order_status__Shipped__Canceled_Delivered__Accepted [WARN 1 in 1.04s]

1version: 2
2
3models:
4  - name: good_orders
5    columns:
6      - name: order_id
7        tests:
8          - unique
9  - name: bad_orders
10    columns:
11      - name: product_id
12        tests:
13          - not_null:
14              config:
15                severity: warn
16      - name: order_status
17        tests:
18          - accepted_values:
19             values: ["Shipped", "Canceled", "Delivered", "Accepted"]
20             config:
21              error_if: ">10"
22              warn_if: ">5"

1version: 2
2
3models:
4  - name: good_orders
5    columns:
6      - name: order_id
7        tests:
8          - unique
9      - name: customer_id
10        tests:
11          - relationships:
12             to: ref('customers')
13             field: customer_id
14             config:
15              where: "order_date='Monday'"
16  - name: bad_orders
17    columns:
18      - name: product_id
19        tests:
20          - not_null:
21              config:
22                severity: warn
23      - name: order_status
24        tests:
25          - accepted_values:
26             values: ["Shipped", "Canceled", "Delivered", "Accepted"]
27             config:
28              error_if: ">1"

1{% test lower_than(model, column_name, upper_bound) %}
2SELECT 
3   *
4FROM {{ model }}
5WHERE {{ column_name }} > {{ upper_bound }}
6{% endtest %}

1version: 2
2
3models:
4  - name: good_orders
5    columns:
6      - name: order_id
7        tests:
8          - unique
9          - lower_than:
10             upper_bound: 100
11      - name: customer_id
12        tests:
13          - relationships:
14             to: ref('customers')
15             field: customer_id
16             config:
17              where: "order_date='Monday'"
18  - name: bad_orders
19    columns:
20      - name: product_id
21        tests:
22          - not_null:
23              config:
24                severity: warn
25      - name: order_status
26        tests:
27          - accepted_values:
28             values: ["Shipped", "Canceled", "Delivered", "Accepted"]
29             config:
30              error_if: ">1"

1SELECT COUNT(*)
2FROM {{ ref("good_orders")}}
3HAVING COUNT(*)=0