dqcheckr — Software Specification

Why this exists

The analytics organisation ingests external datasets from multiple suppliers on a recurring basis. Verifying that each new delivery is complete, structurally correct, and consistent with prior deliveries is currently slow and largely manual. Analysts spend time on repetitive visual inspection instead of higher-value work, and quality problems risk being missed or caught late.

What we are building

dqcheckr is a purpose-built R package that automates this verification process. For each dataset arrival it produces a single, plain-English HTML report that tells data management staff:

1. Whether the file is structurally sound (columns, types, row count as expected).
2. Whether data values are within acceptable bounds and free of obvious errors.
3. How this delivery compares to the previous one — what has changed, what has drifted.
4. How this delivery compares to a defined schema contract, if one exists.

Results are also stored in a local SQLite database so that quality trends can be tracked over time across many deliveries.

Design principles

• Owned and evolvable. The tool is first-party R code, not a thin wrapper around a third-party API. Every check is directly readable and modifiable.
• Plain English outputs. Reports are written for data management staff, not engineers. No R code, no package names, no jargon.
• Fail loudly. Errors in configuration, custom checks, or parsing stop the run with a clear message. Nothing is silently skipped.
• Non-destructive. The tool reads files and writes reports and database records. It never modifies, moves, or deletes source files.

B.1 How it is used

A data officer runs a single command for each arriving dataset:

run_dq_check("customer_accounts", config_dir = "config")

The tool finds the two most recent files in the dataset’s folder, checks the newer one, compares it to the older one, writes a timestamped HTML report to a reports folder, and prints a one-line summary to the console. The report opens automatically in the browser.

Files can also be identified explicitly by naming a current and a previous file, rather than relying on folder modification times.

Scale: the tool is designed for up to 10 dataset arrivals per day across all dataset types.

B.2 What is checked

Data completeness

• Any column with too many missing values is flagged.
• Any column that is entirely empty is an error.
• If the file has fewer rows than the configured minimum, it is an error.

Data validity

• Columns expected to contain only specific values (e.g. country codes, status flags) are checked against a permitted list. Unexpected values are an error.
• Numeric columns expected to fall within a range are checked. Out-of-range values are an error.
• Columns expected to match a format (e.g. a postcode pattern) are checked by regular expression. Violations are an error.

Data integrity

• Columns designated as keys (e.g. a unique ID column) are checked for duplicates. Duplicates are an error.
• Rows that are entirely identical to another row in the same file are flagged as a warning.

Data consistency

• The inferred data type of each column is recorded. A type change between deliveries is flagged.
• Non-numeric values in a column that should be numeric are flagged (warning if any present; error if too many).

Schema contract (when an expected column list is configured)

• Columns present in the file but not in the expected list are an error.
• Columns in the expected list but absent from the file are an error.

Custom checks (when a custom checks file is configured for a dataset)

• Organisation-specific rules written in R are run against the file. Results appear alongside the standard checks.

B.3 What is compared to the previous delivery

• Row count: a large change (increase or decrease) is flagged.
• Schema: new columns, dropped columns, and type changes are flagged.
• Missing rates: columns where the proportion of missing values has increased significantly are flagged.
• Numeric distributions: columns where the average value has shifted significantly are flagged.
• Non-numeric contamination: columns where the proportion of bad (non-numeric) values has increased are flagged.
• Distinct values: new values that have appeared and values that have disappeared in categorical columns are listed.
• Column order: a change in the order of columns is a warning for CSV files and an error for fixed-width files (where column order determines parsing).

B.4 What is produced

HTML report — one file per run, self-contained (no internet required to view). Contains:

1. A header showing dataset name, file name, run time, and an overall status badge (green = all clear, amber = warnings, red = failures).
2. A file summary (row count, column count, file size, format, encoding).
3. A quality checks table listing every check, its status, what was observed, the threshold, and a plain-English explanation. Failures and warnings appear first.
4. A custom checks table (if configured).
5. A version comparison section showing what changed since the previous delivery, with a plain-English narrative summary at the top.
6. A historical trend section showing row count and check outcomes across the last 10 runs (appears once at least two runs have been recorded).
7. An appendix with a full column-level statistics table.

SQLite database — a single database shared across all datasets. Records summary statistics for every run, enabling long-term trend queries without reading the source files again.

C.1 Package structure

dqcheckr/
├── R/
│   ├── run_check.R          # main entry point: run_dq_check()
│   ├── ingest.R             # read CSV / FWF; trim whitespace
│   ├── snapshot.R           # SQLite read/write
│   ├── checks_generic.R     # QC-01..14 and SC-01..02
│   ├── checks_custom.R      # source and call custom_checks(df)
│   ├── compare.R            # CP-01..08
│   ├── report.R             # rmarkdown::render() wrapper
│   ├── utils.R              # dq_result(), load_config(), infer_col_type(), helpers
│   └── dqcheckr-package.R   # package-level documentation
├── inst/
│   ├── templates/report.Rmd
│   ├── config/
│   │   ├── dqcheckr.yml
│   │   └── example_dataset.yml
│   └── demonstrations/
│       ├── demo.R           # named-file mode demo (CSV + FWF)
│       ├── demo2.R          # folder-scan mode demo with version comparison
│       ├── README.md
│       ├── config/          # global + starwars_csv + starwars_fwf configs
│       ├── config2/         # global + starwars_folder configs
│       ├── custom2/         # starwars_custom.R (human-specific checks)
│       ├── data/            # starwars.csv, starwars.fwf
│       └── data2/           # starwars_v1.csv (original), starwars_v2.csv (perturbed)
├── tests/testthat/
│   ├── test-utils.R
│   ├── test-checks.R
│   ├── test-compare.R
│   ├── test-ingest.R
│   ├── test-snapshot.R
│   └── test-integration.R
├── vignettes/
│   ├── dqcheckr.Rmd         # user guide
│   └── specification.Rmd    # this document
├── DESCRIPTION
└── NAMESPACE

C.2 Configuration

Global config — dqcheckr.yml

snapshot_db: "data/snapshots.sqlite"
report_output_dir: "reports/"

default_rules:
  # Single-snapshot thresholds
  max_missing_rate: 0.05            # FAIL if column missing rate exceeds this
  max_non_numeric_rate: 0.01        # FAIL if non-numeric rate in numeric column exceeds this
  min_row_count: 0                  # FAIL if row count below this (0 = disabled)

  # Version comparison thresholds
  max_row_count_change_pct: 0.10
  max_numeric_mean_shift_pct: 0.20
  max_missing_rate_change_pp: 2.0   # percentage points
  max_non_numeric_rate_change_pp: 1.0

  # Schema flags
  flag_new_columns: true            # set false to suppress new-column warnings in CP-02
  flag_dropped_columns: true        # set false to suppress dropped-column warnings in CP-02
  flag_type_changes: true           # set false to suppress type-change warnings in CP-02
  flag_column_order_change: true    # set false to skip CP-08 entirely (WARN CSV / FAIL FWF)

Per-dataset config — <dataset_name>.yml

dataset_name: "customer_accounts"
folder: "data/incoming/customer_accounts/"
format: csv                 # csv | fwf
encoding: "UTF-8"
delimiter: ","              # csv only
fwf_widths: ~               # fwf only
fwf_col_names: ~            # fwf only
fwf_skip: 0                 # fwf only — header rows to skip (default 0)

# Optional: explicit file paths override folder-scan version detection
# current_file: "data/incoming/customer_accounts/20260410.csv"
# previous_file: "data/incoming/customer_accounts/20260401.csv"

# Optional: schema contract
expected_columns:
  - id
  - name
  - country_code
  - account_status
  - account_balance
  - created_date

# Optional: key columns that must be unique
key_columns:
  - id

# Optional rule overrides (merged over global defaults)
rule_overrides:
  max_missing_rate: 0.02
  max_row_count_change_pct: 0.05
  max_non_numeric_rate: 0.005
  min_row_count: 1000

# Optional per-column rules
column_rules:
  country_code:
    allowed_values: ["GB", "US", "DE", "FR"]
    pattern: "^[A-Z]{2}$"
  account_status:
    allowed_values: ["ACTIVE", "CLOSED", "SUSPENDED"]
  account_balance:
    min_value: 0
    max_value: 1000000

# Optional custom checks
custom_checks_file: "custom/customer_accounts_checks.R"

C.3 File version detection

Two modes:

1. Folder scan (default): the two most recently modified files in folder are used. Newest = current, second-newest = previous.
2. Explicit naming: if current_file (and optionally previous_file) are set in the dataset config, those paths are used directly. Useful when file naming conventions do not reflect delivery order, or when re-running a check for a specific pair of files.

If only a current file is available (either via folder scan or explicit config), the run proceeds in single-file mode: version comparison and vs-previous schema checks are skipped.

C.4 Ingestion and whitespace trimming

1. File is read entirely as character columns via readr::read_delim() or readr::read_fwf().
2. Leading and trailing whitespace is trimmed from every value immediately after reading.
3. The trimmed data frame is used for all subsequent checks and comparisons. The raw file is never modified.

C.5 Type inference

Applied to each column of the trimmed data frame. Rules applied in order:

Known date formats tested: %Y-%m-%d, %d/%m/%Y, %m/%d/%Y, %Y%m%d, %d-%m-%Y.

The 90% threshold means a column with up to 10% bad (non-numeric) values is still classified as numeric, making those bad values visible to QC-11.

C.6 Quality checks

Each check returns one or more dq_result objects:

dq_result(check_id, check_name, column = NA, status, observed, threshold = NA, message)
# status: "PASS" | "WARN" | "FAIL" | "INFO"

Single-snapshot checks (QC series)

Schema contract checks (SC series) — only when expected_columns is configured

Version comparison checks (CP series) — skipped in single-file mode

CP-02 is the single source of truth for schema differences. The new_cols_vs_previous and missing_cols_vs_previous columns in the snapshots table are derived directly from the CP-02 result — they are not computed separately.

C.7 Custom checks

• custom_checks_file in dataset config points to an .R file.
• Sourced into an isolated environment (not the global env).
• Must define custom_checks(df) returning a list of dq_result objects.
• Receives the trimmed data frame.
• Errors (missing file, missing function, runtime error) stop the run.
• Results appear in the “Custom Checks” report section and are written to column_snapshots (see C.8).

C.8 SQLite schema

Database and tables created automatically on first run via init_snapshot_db().

Table: snapshots

Table: column_snapshots

Rows written per column type:

Custom check rows: one row per dq_result with a non-NA column value. dq_check = check_id, value = observed, threshold = threshold, severity_on_breach = status if WARN/FAIL, else NULL.

C.9 Main entry point

run_dq_check(
  dataset_name,        # matches <dataset_name>.yml in config_dir
  config_dir = ".",    # contains dqcheckr.yml and <dataset_name>.yml
  open_report = TRUE   # open in browser when interactive
)
# Returns invisibly: list(status, report_path, snapshot_id)

Report filename: <dataset_name>_<YYYYMMDD_HHMMSS>.html

Console output:

[dqcheckr] customer_accounts: FAIL - 0 warning(s), 2 failure(s). Report: reports/customer_accounts_20260410_143022.html

C.10 Error handling

C.11 Dependencies

All on CRAN. pointblank and dataCompareR were evaluated and not used — all logic is implemented natively.

D.1 Needs design before implementation

D.2 Backlog — agreed

D.3 Known limitations