# Stop Bad Data at the Door

![banner](https://articles.aux4.blog/data/posts/aux4/stop-bad-data-at-the-door/banner.png)

## Introduction

A single bad row — an invalid email, a missing name, a negative amount — is enough to blow up an import halfway through and leave your table half-loaded. This post puts a gatekeeper in front of the load: `aux4/validator` checks every incoming record against a rule set, drops the ones that fail, tells you exactly why, and lets only clean data reach the database.

## Install the packages

```bash
aux4 aux4 pkger install aux4/validator aux4/db-sqlite
```

## Step 1: some messy input

Real batches are never clean. Here's `signups.json` — two good records and five each broken a different way:

```json
[
  { "name": "Sally Chen",    "email": "sally.chen@example.com",   "age": 34, "amount": 49.90,  "signup_date": "2026-07-01" },
  { "name": "Marco Ruiz",    "email": "not-an-email",             "age": 41, "amount": 19.90,  "signup_date": "2026-07-03" },
  { "name": "Priya Nair",                                          "age": 29, "amount": 99.00,  "signup_date": "2026-07-05" },
  { "name": "Chen Wei",      "email": "chen.wei@example.com",      "age": 52, "amount": -12.50, "signup_date": "2026-07-06" },
  { "name": "Aisha Bello",   "email": "aisha.bello@example.com",   "age": 38, "amount": 29.90,  "signup_date": "2026-13-40" },
  { "name": "Kenji Tanaka",  "email": "kenji.tanaka@example.com",  "age": 45, "amount": 149.00, "signup_date": "2026-07-09" },
  { "name": "Lucia Moretti", "email": "lucia.moretti@example.com", "age": 16, "amount": 9.90,   "signup_date": "2026-07-10" }
]
```

Marco's email is invalid, Priya has no email at all, Chen's amount is negative, Aisha's date is impossible, and Lucia is under 18.

## Step 2: write the rules

Rules live in a `config.yaml`. Each field has a `path` (where to read it) and a `rule` (how to check it):

```yaml
config:
  data:
    signup:
      name:
        path: $.name
        rule: required
      email:
        path: $.email
        rule: required|email
      age:
        path: $.age
        rule: required|integer|min:18
      amount:
        path: $.amount
        rule: required|numeric|min:0
      signup_date:
        path: $.signup_date
        rule: required|date
```

## Step 3: keep only the clean rows

Pipe the input through the validator. `--onlyValid` keeps the records that pass; `--ignore` keeps the exit code at `0` so the pipeline flows on:

```bash
cat signups.json | aux4 validator validate --configFile config.yaml --config data --rules signup --onlyValid --ignore
```

```json
[
  { "name": "Sally Chen", "email": "sally.chen@example.com", "age": 34, "amount": 49.9, "signup_date": "2026-07-01" },
  { "name": "Kenji Tanaka", "email": "kenji.tanaka@example.com", "age": 45, "amount": 149, "signup_date": "2026-07-09" }
]
```

Five bad records gone, two clean ones through. (The validator emits only the fields that have rules — so it doubles as a whitelist, quietly stripping any unexpected extra fields.)

## Step 4: see exactly what failed

Swap `--onlyValid` for `--onlyInvalid` and the validator hands back the rejected records *with the reason* — perfect for a report or an alert:

```bash
cat signups.json | aux4 validator validate --configFile config.yaml --config data --rules signup --onlyInvalid --ignore
```

```json
[
  { "item": { "name": "Marco Ruiz", "email": "not-an-email", "age": 41, "amount": 19.9, "signup_date": "2026-07-03" },
    "errors": { "email": ["The email format is invalid."] } },
  { "item": { "name": "Priya Nair", "age": 29, "amount": 99, "signup_date": "2026-07-05" },
    "errors": { "email": ["The email field is required."] } },
  { "item": { "name": "Chen Wei", "email": "chen.wei@example.com", "age": 52, "amount": -12.5, "signup_date": "2026-07-06" },
    "errors": { "amount": ["The amount must be at least 0."] } },
  { "item": { "name": "Aisha Bello", "email": "aisha.bello@example.com", "age": 38, "amount": 29.9, "signup_date": "2026-13-40" },
    "errors": { "signup_date": ["The signup date is not a valid date format."] } },
  { "item": { "name": "Lucia Moretti", "email": "lucia.moretti@example.com", "age": 16, "amount": 9.9, "signup_date": "2026-07-10" },
    "errors": { "age": ["The age must be at least 18."] } }
]
```

Every rejection names the field and the rule it broke.

## Step 5: load only the clean data

Now put the gatekeeper in front of the database. Create the table, then pipe input → validator → SQLite. Only records that survive validation are ever inserted:

```bash
aux4 db sqlite execute --database signups.db \
  --query "CREATE TABLE IF NOT EXISTS signups (id INTEGER PRIMARY KEY, name TEXT, email TEXT, age INTEGER, amount REAL, signup_date TEXT)"

cat signups.json \
  | aux4 validator validate --configFile config.yaml --config data --rules signup --onlyValid --ignore \
  | aux4 db sqlite execute --database signups.db \
      --query "INSERT INTO signups (name, email, age, amount, signup_date)
               VALUES (:name, :email, :age, :amount, :signup_date) returning *" --inputStream
```

```json
[
  { "id": 1, "name": "Sally Chen", "email": "sally.chen@example.com", "age": 34, "amount": 49.9, "signup_date": "2026-07-01" },
  { "id": 2, "name": "Kenji Tanaka", "email": "kenji.tanaka@example.com", "age": 45, "amount": 149, "signup_date": "2026-07-09" }
]
```

The `:field` placeholders bind straight from each validated record — no loop, no glue. Two rows in the table, and not one bad record made it through.

## Choosing the contract: filter or fail

The `--ignore` flag is the switch between two behaviors:

- **With `--ignore`** — exit `0` no matter how many records fail. "Filter out the bad ones and keep going." (What we used above.)
- **Without `--ignore`** — if any record is invalid, exit non-zero (`40`). "Fail the whole job if the batch is dirty."

Drop `--ignore` when a dirty batch should stop the pipeline and page someone; keep it when you'd rather quietly load what's good.

## Conclusion

`aux4/validator` is a gatekeeper you drop into a pipe: it checks every record against your rules, keeps only the clean ones, and reports precisely what failed — so bad data is caught at the door instead of halfway through a load. Put it in front of any `aux4/db-sqlite` (or repository, or API) write, and your destination only ever sees data you trust.

## See Also

- [aux4/validator](https://hub.aux4.io/r/public/packages/aux4/validator)
- [aux4/db-sqlite](https://hub.aux4.io/r/public/packages/aux4/db-sqlite)