Skip to contents

Data Checks

Source: vignettes/data-checks.Rmd
data-checks.Rmd
library(ducklake)
library(dplyr)

attach_ducklake("checks_lake", lake_path = vignette_temp_dir)

df_cars <- data.frame(model = rownames(mtcars), mtcars, row.names = NULL)

with_transaction(
  create_table(df_cars, "cars"),
  author = "Data Engineer",
  commit_message = "Add the Motor Trend car data"
)

DuckLake enforces one rule about the values in a table: a column can be NOT NULL. It has no check constraints, no primary or unique keys, and no foreign keys, and its catalog accepts no custom tags from SQL, so a rule like “cyl is 4, 6, or 8” has no slot of its own. The catalog does hold views. A view that returns the rows breaking a rule is a check: the rule’s logic sits in the catalog, it is versioned with the data it describes, and any client of the lake runs it by reading the view.

This article writes checks that way with create_check() and runs them with run_checks(). Then it uses what a lake adds: a load that rolls back when a check fails, the outcome of the checks recorded on a commit, and the rules and the data read together as of an earlier snapshot. It closes with how this sits next to affirm and data-dict. Both functions are experimental while the convention settles.

The rule the lake enforces

set_column_not_null() sets the one constraint DuckLake has. From then on the lake itself refuses a row without the value, whichever client writes it. Plain SQL stands in for another client here:

with_transaction(
  set_column_not_null("cars", "mpg"),
  author = "Data Manager",
  commit_message = "Require mpg"
)
#> Column "mpg" in "cars" now requires a value.
#> Committed snapshot 2 (Data Manager): Require mpg

try(
  DBI::dbExecute(
    get_ducklake_connection(),
    "INSERT INTO cars (model, cyl) VALUES ('Unknown', 4)"
  )
)
#> Error in duckdb_result(connection = conn, stmt_lst = stmt_lst, arrow = arrow) : 
#>   Invalid Error: Constraint Error: NOT NULL constraint failed: cars.mpg
#> ℹ Context: rapi_execute
#> ℹ Error type: INVALID

Rules as views

Every other rule is a view. The checks get a schema of their own (a schema is the lake’s namespace for tables and views), which keeps them apart from the data and tells run_checks() where to look. create_check() takes a rule the way you would say it, as the condition every row should meet, and stores the view of the rows where it is false, with the rule’s label as the view’s comment:

with_transaction({
  get_ducklake_table("main.cars") |>
    create_check(
      "cyl_known", cyl %in% c(4, 6, 8),
      label = "cyl is 4, 6, or 8",
      listing = c(model, cyl)
    )

  get_ducklake_table("main.cars") |>
    create_check(
      "hp_plausible", hp > 0 & hp < 500,
      label = "hp is between 0 and 500",
      listing = c(model, hp)
    )
}, author = "Data Manager", commit_message = "Add the first checks on cars")
#> Created check "cyl_known" in "checks": cyl is 4, 6, or 8
#> Created check "hp_plausible" in "checks": hp is between 0 and 500
#> Committed snapshot 3 (Data Manager): Add the first checks on cars

There is nothing more to a check than that view. The first call above stores what this pipeline would, creating the checks schema on the way:

get_ducklake_table("main.cars") |>
  filter(!(cyl %in% c(4, 6, 8))) |>
  select(model, cyl) |>
  create_view("checks.cyl_known")
set_table_comment("checks.cyl_known", "cyl is 4, 6, or 8")

Some rules are easier to state as their failure, and those are written just like that. No model should appear twice, and the rows that break the rule are the models counted more than once:

with_transaction({
  get_ducklake_table("main.cars") |>
    count(model) |>
    filter(n > 1) |>
    create_view("checks.model_unique")
  set_table_comment("checks.model_unique", "model appears once")
}, author = "Data Manager", commit_message = "Check that models are unique")
#> Created view "checks.model_unique".
#> Commented view "checks.model_unique".
#> Committed snapshot 4 (Data Manager): Check that models are unique

A rule that spans tables is written the same way, as a join: an anti_join() of visits against subjects returns the visits with no subject.

A few habits keep a check dependable:

  • State a rule the way its label reads and let create_check() find the rows where it is false. A rule that forbids something stays readable (hp != 0, where the pipeline by hand would negate a negation), and a compound rule is not negated by hand, where & and | are easy to swap. When the failure is the natural thing to describe, write it as a pipeline into create_view(). The two forms select the same rows, because three-valued logic treats NOT (hp != 0) and hp = 0 alike.
  • A row where the rule is NA passes, as it does under a SQL CHECK constraint, so a missing cyl passes cyl_known. When a missing value matters, say so in the rule, give it a check of its own, or make the column NOT NULL.
  • List the columns a reviewer needs. The view is the listing of what failed, so its columns are the ones someone will act on.
  • Read the table by its schema-qualified name, "main.cars". DuckDB resolves an unqualified name from the view’s schema and the session’s current database, so "cars" can fail to bind for a client where another database is current.
  • Keep only checks in the schema. run_checks() counts the rows of every view it finds there, and a view that summarizes (a row of totals) would report a failure every time.

Running the checks

run_checks() counts the rows each check returns:

run_checks()
#>          check                   label n_fail
#> 1    cyl_known       cyl is 4, 6, or 8      0
#> 2 hp_plausible hp is between 0 and 500      0
#> 3 model_unique      model appears once      0

The label comes from the view’s comment and n_fail is the number of rows breaking the rule. The car data passes all three.

Gating a load

A new batch arrives, and one of its rows has a typing error:

df_batch <- data.frame(
  model = c("Saab 99", "Audi 100 LS"),
  mpg = c(24.5, 23.0),
  cyl = c(4, 40),
  hp = c(87, 91)
)

Inside a transaction the checks see the rows that are waiting to be committed. So a load can run the checks on itself and stop, and with_transaction() rolls everything back when it does:

try(
  with_transaction({
    rows_insert(get_ducklake_table("cars"), df_batch, by = "model")

    df_checks <- run_checks()
    if (any(df_checks$n_fail > 0)) {
      stop("failed ", toString(df_checks$check[df_checks$n_fail > 0]))
    }
  }, author = "Data Manager", commit_message = "Load the September batch")
)
#> Transaction rolled back.
#> Error : Transaction rolled back due to error: failed cyl_known

Nothing of the batch reached the lake. The table has its 32 rows and the history has no new snapshot:

get_ducklake_table("cars") |> count() |> collect()
#> # A tibble: 1 × 1
#>       n
#>   <dbl>
#> 1    32

list_table_snapshots("cars") |>
  select(snapshot_id, author, commit_message)
#>   snapshot_id        author               commit_message
#> 1           1 Data Engineer Add the Motor Trend car data
#> 2           2  Data Manager                  Require mpg

This is how a rule becomes a constraint in a lake. DuckLake would accept the row; a transaction that checks itself never commits it. The gate covers the writers that use it, and a client that writes without running the checks is not stopped.

Recording the outcome on the commit

Rejecting the batch is not always right. Raw data often has to land as it arrived, with the problems flagged for follow-up. A manual transaction can run the checks after the write and put the outcome on the commit, in commit_extra_info:

begin_transaction()
rows_insert(get_ducklake_table("cars"), df_batch, by = "model")

df_failed <- run_checks() |>
  filter(n_fail > 0) |>
  select(check, n_fail)

commit_transaction(
  author = "Data Manager",
  commit_message = "Load the September batch",
  commit_extra_info = as.character(
    jsonlite::toJSON(list(checks_failed = df_failed))
  )
)
#> Committed snapshot 5 (Data Manager): Load the September batch

v_loaded <- max(list_table_snapshots("cars")$snapshot_id)

The snapshot now says what state the data was in when it was committed:

list_table_snapshots("cars") |>
  select(snapshot_id, commit_message, commit_extra_info) |>
  tail(1)
#>   snapshot_id           commit_message
#> 3           5 Load the September batch
#>                                      commit_extra_info
#> 3 {"checks_failed":[{"check":"cyl_known","n_fail":1}]}

Which rows failed

The check is a view, so the failing rows are one read away, with the columns the check selected:

run_checks()
#>          check                   label n_fail
#> 1    cyl_known       cyl is 4, 6, or 8      1
#> 2 hp_plausible hp is between 0 and 500      0
#> 3 model_unique      model appears once      0

get_ducklake_table("checks.cyl_known") |> collect()
#> # A tibble: 1 × 2
#>   model         cyl
#>   <chr>       <dbl>
#> 1 Audi 100 LS    40

The correction is an ordinary update, and its commit message can name the check it answers. Afterwards the lake’s history holds the load, the finding, and the fix:

with_transaction(
  rows_update(
    get_ducklake_table("cars"),
    data.frame(model = "Audi 100 LS", cyl = 4),
    by = "model"
  ),
  author = "Data Manager",
  commit_message = "cyl_known: Audi 100 LS cyl corrected from 40 to 4"
)
#> Committed snapshot 6 (Data Manager): cyl_known: Audi 100 LS cyl corrected from
#> 40 to 4

run_checks()
#>          check                   label n_fail
#> 1    cyl_known       cyl is 4, 6, or 8      0
#> 2 hp_plausible hp is between 0 and 500      0
#> 3 model_unique      model appears once      0

Rules have history

A check is a catalog object, so changing a rule is a snapshot with an author and a message, like a change to the data. Calling create_check() again under the same name replaces the rule and its label together:

with_transaction(
  get_ducklake_table("main.cars") |>
    create_check(
      "hp_plausible", hp >= 40 & hp <= 400,
      label = "hp is between 40 and 400",
      listing = c(model, hp)
    ),
  author = "Data Manager", commit_message = "Narrow the plausible hp range"
)
#> Created check "hp_plausible" in "checks": hp is between 40 and 400
#> Committed snapshot 7 (Data Manager): Narrow the plausible hp range

list_table_snapshots("checks.hp_plausible") |>
  select(snapshot_id, author, commit_message)
#>   snapshot_id       author                commit_message
#> 1           3 Data Manager  Add the first checks on cars
#> 2           7 Data Manager Narrow the plausible hp range

A check written with create_view() keeps its label when the view is replaced, so only a change of wording needs set_table_comment() again.

Rules and data as of a snapshot

A lake attached at a snapshot shows its views as they were then, along with its tables. Re-attaching at the snapshot of the September load runs the rules of that day against the data of that day. The uncorrected row fails again, and the hp rule reads as it did then:

detach_ducklake("checks_lake")
attach_ducklake(
  "checks_lake",
  lake_path = vignette_temp_dir,
  snapshot_version = v_loaded
)

run_checks()
#>          check                   label n_fail
#> 1    cyl_known       cyl is 4, 6, or 8      1
#> 2 hp_plausible hp is between 0 and 500      0
#> 3 model_unique      model appears once      0

That answers a question an audit asks: which rules were in force when this data was committed, and did the data pass them?

Next to affirm and data-dict

Two other tools take a rule in the same form. affirm, from PCCTC, runs checks in R on data frames and turns the failures into a report. data-dict, from the tidyverse team, keeps the dictionary of a dataset and its rules in a YAML file and validates the data against it. The rule from the top of this article, in each:

# affirm
df_cars |>
  affirm_true(label = "cyl is 4, 6, or 8", condition = cyl %in% c(4, 6, 8))
# data-dict.yaml
columns:
  - name: cyl
    constraints:
      - assert: cyl IN (4, 6, 8)
        description: cyl is 4, 6, or 8
# ducklake
get_ducklake_table("main.cars") |>
  create_check("cyl_known", cyl %in% c(4, 6, 8), label = "cyl is 4, 6, or 8")

All three ask for the rule as it is said and work out the failing rows themselves. A missing value passes in data-dict and in a lake check, the way it does under a SQL CHECK constraint. They differ in where the rule lives and in what runs it:

affirm data-dict ducklake checks
The rule lives in an R script in a YAML file beside the data, easy to diff in git in the lake’s catalog, in the same snapshots as the data
What runs it R, on a data frame in memory its own engine, on Parquet files DuckDB, on the lake’s tables, from any client
What a rule can say any R expression an expression about one table’s rows, plus declared keys, required columns, allowed values, and ranges anything a query can return, joins and aggregates included
What comes back a gt or Excel report of the failing rows an HTML report, and the dictionary rendered as a site counts from run_checks(), and the failing rows as a view

The data-dict column describes its 0.0.3 preview of August 2026, and the project is moving quickly.

The three answer different questions, so they combine. data-dict describes a whole dataset (types, units, what the values of an enum mean, a glossary) in a file that people and agents can read without a database, and its draft command writes most of that file from the data. A lake’s checks are narrower and sit closer to the data. They run where the data is, inside the transaction that loads it if need be, and a change to a rule is a snapshot with an author, like a change to the data. For now data-dict reads Parquet files, and a lake table is more than its Parquet data files, since deletes are recorded separately and small inserts live in the catalog, so a table has to be exported before data-dict can validate it. What a lake holds of a dictionary today is the comments and labels of vignette("views-comments-labels").

run_checks() counts and nothing more, and a report is a job for the tools built for one. The rows collected from a check view are a data frame, ready for gt or for a report from affirm. pointblank interrogates lazy tables, so one of its agents can also run on get_ducklake_table("main.cars") inside DuckDB.

detach_ducklake("checks_lake")