Syntax for data validation framework

[sf-dcp]

What

We will be implementing one data validation framework using pandera in ingest & distribution. The goal of this discussion is to agree on a syntax we will be using in metadata and recipe template yaml files when defining data checks.

Common tests

From my research, most common tests we will be using at ingest and packaging stages are:

• schema level:

  • row count
  • unique records with a combination of columns (as seen in dbt)

• column level:

  • data type
  • not null
  • unique
  • values in a given set
  • geometry type (i.e. point, polygon, etc)
  • geometry projection
  • numeric values range

Requirements for syntax:

• Readable. This will allow cross-team collaboration
• Integrates with existing metadata syntax
• Similar to dbt test syntax, so that we just need to know 1 syntax that could be used everywhere. dbt test syntax:

  - name: <column_name>
    tests:
      - <test_name>
      - <test_name>:
        <argument_name>: <argument_value>
        config:
          <test_config>: <config-value> 

Proposed syntax

Combing existing metadata syntax + dbt syntax, I'm proposing something like this:

schema:
  tests:
    - row_count:
        above: 0
    - unique_combination_of_columns:
        combination_of_columns:
          - name
          - wkb_geometry

columns:
  - name: borough
    description: borough name
    data_type: string # could be standard or custom data type like bbl or point
    example: MANHATTAN
    tests:
      - not_null

      - unique

      - is_epsg_2263

      - accepted_values:
        values: ["MANHATTAN", "BROOKLYN"]
        description: this is description for check
        config:
          severity: warn

      - accepted_range:
        min_value: 0
        max_value: 100

[alexrichey]

Nice, this makes a lot of sense. I'm totally in favor of taking dbt syntax and making some bindings to Pandera's API.

Then I think there's an additional conversation to be had about types. When I was implementing metadata stuff, I ended up copying what was in the READMEs, but this would be a nice opportunity to revisit that. For example, for numeric types with a decimal, what should we call those? I called them doubles, copying the READMEs, but... is that what they are? (As opposed to floats, decimals, etc.) Or, another example, you've got string while I've got text. That type of thing.

Then for accepted values, I think that they serve a dual purpose as a test but also as documentation. So I'm not entirely sure that they belong under a tests key, but could be convinced.

[sf-dcp]

In terms of data types, I agree that we need to define our data type pool. I'm assuming the data types defined in metadata files will directly feed into future data dictionary files. In this case, I see 2 possible approaches:

• We use pandas/pythondatatypes + custom data types. When it comes to creation of data dict docs, we would use a mapping of pandas/pythondatatypes to data dict data types. For example, string --> text.
• We first create a mapping of python data types to data dict data types and use it in yaml files.

If we choose the former approach, this is something we can work on after data validation implementation.

Accepted values in tests vs documentation... I think if we plan to test only a subset of values in a column (say there are 50 categorical values but we only want to confirm 5), then it belongs under tests. Otherwise, I agree this type of check could live in either place.

[sf-dcp]

@damonmcc @fvankrieken would love to get your thoughts on this

[fvankrieken]

As far as the yml syntax goes, this looks great. My one pushback is against checks being "schema"-level, I think "dataset"-level makes more sense. And in that case, I think dataset-level tests key could just be one level higher in the yml.

The types is a funny one - I think it'd be best to stay more generalized and human-oriented (i.e. "text" over "string") and not python-specific, largely for consistency with other docs. Hopefully that wont complicate things too much.

I need to think about @alexrichey's point about accepted_values being under tests vs its own special field. I think I agree though

[sf-dcp]

Proposed data types: text, integer, decimal, bbl.

What about geom types?

[damonmcc]

text, integer, decimal seem great and human-oriented

I can see the value of a bbl type, but it doesn't seem worth putting it at the same top-level as the others. feels like a subset of text with certain constraints that we can enforce, like borough

on geometry types

TLDR: we could do something inspired by postgis and have a geometry type at the same level as the three types above. we could then have subsets of it (like text -> bbl) and declare things like geometry(MultiPolygon, 2263), geometry(2263)

looks like postgis has let us declare geometry types like this:

• geometry(Geometry, 2263) in a dbt test here
• geometry(MultiLineString, 4326) in a sql script here

I like that this forces a declaration of the projection so it's never ambiguous. and (when choosing Geometry) allows for multiple types in the same column (line, point, polygon, multi-*)

This alone doesn't address the WKB/WKT problem though (postgis docs here).

[fvankrieken]

I think wkb/wkt isn't hugely relevant for these metadata files, because again that's on the implementation side (like int32, int64), and we'll have norms depending on file type - shapefile has whatever it has under the hood, csvs use wkt, parquet uses wkb, etc.

[alexrichey]

and related question: for geom types, is the projection a part of the type? Or does that fall under tests?

[sf-dcp]

I was actually thinking about this too today... probably projection would be a part of tests like in dbt?

[damonmcc]

(I commented above before I saw this) I think projection is worth tying to a column's type, it seems like something that shouldn't vary in a single column (unlike allowing a mix of polygons, points, and lines)

but I also imagined it'd be worth having a general geoemtry type with neither projection nor "shape type" declared