Add a test for sequential values on a column

[yu-iskw]

Describe the feature

For instance, we incrementally update a table everyday, we want to make sure if missing dates exist or not.
We can implement a test to make sure if distinct values of a column is sequence without missing ones.

When the updated_date column should have sequential dates without missing ones from 2020-01-01 to 2020-12-31, we can declare the test as below:

columns:
  - name: updated_date
    tests:
      - dbt_utils.sequencial_values:
          period: day  # ["minute", "hour", "day", "week", "month", "year", "integer"]
          gaps: 1  # Optional, default 1
          where: "updated_date NOT IN ('2020-05-01', '2020-09-04')"

• period: (Required) duration of sequential values, say about 1 day
• gaps: (Optional) gaps of the sequence, default: 1
• where: (Optional) conditions to accept missing values

The genrated query for the test in BigQuery can be like:

WITH unique_values (
  SELECT
    DISTINCT `updated_date` AS v
  FROM `project`.`dataset`.`table`
  ORDER BY 1
)

, gaps_to_subsequent AS (
  SELECT
    DATE_DIFF(LEAD(v), v) AS gaps
  FROM unique_values
)

SELECT COUNT(*)
FROM gaps_to_subsequent
WHERE 
  gaps != 1
  AND updated_date NOT IN ('2020-05-01', '2020-09-04')

Describe alternatives you've considered

We can of course implement a dbt data test by hand. But, such a test is repeated in different tables.

Additional context

I don't think it is database-specific.

Are you interested in contributing this feature?

I want to implement the feature.

[clrcrl]

Hey there @yu-iskw! This is a cool test, I would definitely support it in dbt-utils. I'm doing a big cleanup of open PRs this week, do you mind holding off on writing the code on this for a week or so?

[yu-iskw]

@clrcrl Good to know. Sure things!

[yu-iskw]

@clrcrl Please let me know if we are ready to start implementation.

[yu-iskw]

I will work on it shortly.

[yu-iskw]

This was already implemented. But, I think it would be great to support the where clause so that we sometimes want to have conditions to exclude the test scope.
#327

[yu-iskw]

@clrcrl Can we close the issue, although we can support whare as well?

[yu-iskw]

Additional PR
#401