Puzzleshot #001 - Unix tools for solving data problems

raw data→unix tools→result

You’re working as a data engineer for a company providing a data product about US regional tractor sales to an external client. The client expected the most recent delivery to contain 4.2 million records. It only had 3,370,341 rows.

The engineer responsible for generating the data asks you to run some quick validations on a hopefully fixed version before re-sending to the customer. They point you to the file on a Linux box with just the basics - no Python, no DuckDB, no dbt… You do have access to normal utilities - awk, sed, cut, gzip, zcat, sort, uniq, etc.

How would you do the following?

Verify that at least 4.2 million records exist
Check that each record contains 8 columns
Make sure column #4 only contains entries in the set (North, East, South, West). For now, just verify that the values are in this set. We’ll consider filtering / updating them later.

More notes / requests:

The file is gzipped
Try to make this analysis run as quickly as possible
Try to limit the number of passes reading the source file

Sample rows:

2023-12-01,Deere,X9100,North,847500,dealer,new,12
2024-07-01,Caterpillar,CH95,West,923000,private,used,8
2025-03-11,Deere,X9100,Northwest,745000,dealer,new,15

Reveal solution

Part 1 - Number of rows

Various ways to do this one:

gunzip -c datafile.csv.gz | wc -l

Unzip the file to stdout, run it through wc -l which prints the number of lines

gunzip -c datafile.csv.gz | awk '{print NR}' | tail -1

Unzip the file to stdout, have awk print out the row number, and grab the last one

gunzip -c datafile.csv.gz | awk '{count++} END {print count}'

Unzip the file to stdout, use awk to increment a counter for each row and print afterwards

Note: I use gunzip -c out of habit, but zcat could be used in place.

Part 2 - Number of columns per row

Let’s now look at the column count. Obviously we could normally use a more powerful tool like Python’s csv module, pandas, polars, DuckDB, etc. But for this, let’s use awk again.

gunzip -c datafile.csv.gz | awk '{print NF}' | sort -n | uniq -c

Unzip the file to stdout, pipe to awk to print out the number of fields (columns), then sort that numerically with sort and have uniq show a list of all column counts. Works well on smaller files, but can get tricky with really big files as sort tends to put everything in memory.

zcat datafile.csv.gz | awk '{columns[NF]++} END {for (count in columns) {print count": ", columns[count]}}'

This one is a bit more involved, but has some advantages. First, we print the uncompressed data to stdout, and pipe to awk as before. Next step is for every line, we increment an associative array (aka hashtable) based on the column count (the NF variable). Once we’ve gone through the file, we loop over each entry in the array and print out the field count and the number of rows matching that. Our overall memory usage should be less on large files (just keeping counters, not the individual row values) and can specify our output format.

Part 3 - Verify that the column #4 only contains specific values

For this one, I’d turn to awk yet again with its regular expressions support. I would do something like:

zcat datafile.csv.gz | awk '$4 !~ /\^(North|East|South|West)\$/'

This will automatically print any rows where column 4 contains data outside of that set. Note that if it’s ok to have various casing (ie, north, North, NORTH) then you can handle that depending on your comparison prior to the check.

Part 4 - Combo

One great option is we can actually combine all these checks into a single form, such as:

zcat file.gz | awk '
  NF != 8 {print "Bad columns at line", NR}
  \$4 !~ /^(North|East|South|West)$/ {print "Bad value at line", NR, \$4}
  END {print NR, "records total"}
'

This one combines all the checks and also provides a reference to the row numbers that don’t meet our requirements, and only requires a single pass through the data. Please note that this report prints problem rows, but also total row count. You may need to change the format for your needs. Also consider how easily you can change the code to check different columns, different values, etc.