---
title: "Data Checks"
output: rmarkdown::html_vignette
vignette: >
  %\VignetteIndexEntry{Data Checks}
  %\VignetteEngine{knitr::rmarkdown}
  %\VignetteEncoding{UTF-8}
---

```{r, include = FALSE}
# Evaluate chunks only where the ducklake DuckDB extension is already
# installed. The probe never downloads anything, so building this vignette
# needs no network access. jsonlite writes the check results onto a commit.
ducklake_available <- ducklake::ducklake_extension_available() &&
  requireNamespace("jsonlite", quietly = TRUE)
knitr::opts_chunk$set(
  collapse = TRUE,
  comment = "#>",
  eval = ducklake_available
)
# Use a unique temp directory for this vignette to avoid conflicts during R CMD check
vignette_temp_dir <- file.path(tempdir(), "checks_vignette")
dir.create(vignette_temp_dir, showWarnings = FALSE, recursive = TRUE)
knitr::opts_knit$set(root.dir = vignette_temp_dir)
```

```{r setup, message=FALSE}
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:

```{r not_null}
with_transaction(
  set_column_not_null("cars", "mpg"),
  author = "Data Manager",
  commit_message = "Require mpg"
)

try(
  DBI::dbExecute(
    get_ducklake_connection(),
    "INSERT INTO cars (model, cyl) VALUES ('Unknown', 4)"
  )
)
```

## 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:

```{r write_checks}
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")
```

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:

```{r by_hand, eval = FALSE}
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:

```{r failure_check}
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")
```

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:

```{r run_checks}
run_checks()
```

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:

```{r batch}
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:

```{r gate}
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")
)
```

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

```{r gate_result}
get_ducklake_table("cars") |> count() |> collect()

list_table_snapshots("cars") |>
  select(snapshot_id, author, commit_message)
```

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`:

```{r soft_load}
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))
  )
)

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

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

```{r soft_result}
list_table_snapshots("cars") |>
  select(snapshot_id, commit_message, commit_extra_info) |>
  tail(1)
```

## Which rows failed

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

```{r listing}
run_checks()

get_ducklake_table("checks.cyl_known") |> collect()
```

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:

```{r 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"
)

run_checks()
```

## 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:

```{r revise_check}
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"
)

list_table_snapshots("checks.hp_plausible") |>
  select(snapshot_id, author, commit_message)
```

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:

```{r pinned, message=FALSE}
detach_ducklake("checks_lake")
attach_ducklake(
  "checks_lake",
  lake_path = vignette_temp_dir,
  snapshot_version = v_loaded
)

run_checks()
```

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](https://pcctc.github.io/affirm/), from PCCTC, runs checks in R on
data frames and turns the failures into a report.
[data-dict](https://data-dict.tidyverse.org), 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:

```{r affirm_form, eval = FALSE}
# affirm
df_cars |>
  affirm_true(label = "cyl is 4, 6, or 8", condition = cyl %in% c(4, 6, 8))
```

```yaml
# data-dict.yaml
columns:
  - name: cyl
    constraints:
      - assert: cyl IN (4, 6, 8)
        description: cyl is 4, 6, or 8
```

```{r ducklake_form, eval = FALSE}
# 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.

```{r cleanup}
detach_ducklake("checks_lake")
```