xSV validation / linting

[ialarmedalien]

Numerous import pipelines use xSV files with a documented file structure. It would be great to be able to validate the files before putting the data therein into the lakehouse.

Some of the xSV files to be imported into the lakehouse are MASSIVE, so having a solution that works well on multi-GB files would be best. Ideally the invalid lines would be reported on and/or removed and the rest of the file processed.

• awesomeCSV tool list

candidates for use:

• qsv toolkit: includes a validation module that strips out invalid lines
• frictionless tabular data resource and validator
• duckdb csv import with invalid line detection
• csvlinter go package

TODO:

• choose csv linting tool, add to container
• create configurable csv clean / lint workflow
• generate configs for existing csv imports

[ialarmedalien]

qsv seems a reasonable option -- a schema for validating the CSV data can be provided as JSON schema, and there are numerous other useful functions for extracting information, reformatting, transforming, etc., files. Possible downside is needing to know the schema in advance if validation is to be done accurately.

sample workflow for CSV cleaning:

# clean up the file -- trim whitespace, remove unneeded comments, etc.
# automatically converts file to CSV if it was TSV
qsv input \
  --auto-skip \
  --trim-headers \
  --trim-fields \
  --encoding-errors replace \
  source_file.tsv > cleaned_file.csv

# are all column names safe?
qsv safenames --mode V cleaned_file.csv

# replace none or N/A with empties
qsv replace -i '^(na|null|NULL|None|N/A)$' '' cleaned_file.csv > nulled_cleaned_file.csv

# sort + dedup
qsv sort --select 'col_name_1,col_name_2' nulled_cleaned_file.csv > sorted.csv
qsv dedup --sorted --select 'col_name' --dupes-output dupes.csv sorted.csv > clean.csv
# --sorted enables streaming dedup (constant memory)
# --dupes-output dupes.csv keeps an audit trail of every row that got dropped — invaluable for explaining "where did N rows go?" later
# For files larger than RAM, swap sort → extsort and dedup --sorted works the same way.

# generate an index
qsv index clean.csv
# get a load of stats for each column -- useful for partitioning
qsv stats --cardinality --infer-dates --infer-boolean --stats-jsonl clean.csv

# generate a JSON schema from the file (if don't already have one)
# schema is generated at clean.csv.schema.json
qsv schema clean.csv

# validate the schema
qsv validate schema clean.csv.schema.json

# validate the data against the schema
qsv validate clean.csv clean.csv.schema.json
# exit code 0: all is well, all lines were valid
# non-zero exit code: some failures
# clean.csv.valid.csv — rows that passed
# clean.csv.invalid.csv — rows that failed
# clean.csv.validation-errors.csv — one row per error: row_number, field, error

[ialarmedalien]

Re: choose csv linting tool, add to container

See #172 and #174