Puzzleshot #012 - Non-existent Delimiters

raw data→duckdb→result

In Puzzleshot 011 we handled ragged CSV data by slicing the first 10 columns with cut before passing the result to DuckDB. It worked cleanly — but required unix preprocessing.

This week, no unix tools. Solve it entirely within DuckDB.

Same data as before:

order_id,customer_id,dealer_id,region,order_total,order_date,product_id,quantity,status,sales_rep,tag1,tag2,tag3
5001,8801,1001,North,124500.00,2024-01-01,X9100,2,complete,Jones,preferred,volume
5002,8802,1003,South,87250.00,2024-01-02,CH95,1,pending,Smith
5003,8803,1001,East,203000.00,2024-01-03,X9100,3,complete,Davis,preferred,volume,new_account,referral
5004,8804,1002,West,156750.00,2024-01-04,CH95,1,complete,Jones

The challenge:

Using only DuckDB, produce the same result as #011 — total order value per region — without preprocessing the file first.

Hint: DuckDB’s read_csv has a delimiter option. What happens if you specify a delimiter that doesn’t exist in your data?

Things to consider:

What does DuckDB do when the delimiter never appears in the data?
How do you extract specific fields from the resulting column?
What are the tradeoffs of this approach vs the cut approach from #011?

Reveal solution

This puzzle’s trick is simple once you see it — but non-obvious until you do.

The solution:

SELECT
  split_part(column0, ',', 4) as region,
  sum(split_part(column0, ',', 5)::DOUBLE) as total_value
FROM read_csv('sales.csv.gz', delim='|')
WHERE split_part(column0, ',', 1) != 'order_id'
GROUP BY region
ORDER BY total_value DESC

What’s happening:

delim='|' tells DuckDB to use a pipe as the delimiter. Since the pipe never appears in the data, DuckDB reads each entire row as a single column — column0. No schema confusion, no ragged column complaints, just one big string per row.
split_part(column0, ',', n) then extracts individual fields by position. Field 4 is region, field 5 is order_total. DuckDB sees clean consistent data because we’ve sidestepped the parsing problem entirely.
WHERE split_part(column0, ',', 1) != 'order_id' removes the header row — since DuckDB isn’t parsing the CSV normally it doesn’t know which row is the header.
The ::DOUBLE cast on order_total converts the extracted string to a numeric value for aggregation.

The tradeoffs vs #011:

The cut approach from #011 is faster and simpler for clean data — it slices columns at the stream level before DuckDB sees anything. This approach is slower since DuckDB is doing string splitting on every row, but it requires no unix tools and works entirely within SQL.

The broader applicability:

This technique isn’t DuckDB-specific. Any SQL-capable tool with string functions can use the same approach — the function name may vary but the pattern is identical:

DuckDB / PostgreSQL: split_part(column, ',', n)
Spark SQL: split(column, ',')[n-1]
BigQuery: SPLIT(column, ',')[ORDINAL(n)]
Snowflake: SPLIT_PART(column, ',', n)
SQL Server (2022+): STRING_SPLIT with ordinal support via SELECT value FROM STRING_SPLIT(column, ',', 1) WHERE ordinal = n

Note that SQL Server’s approach is notably different — STRING_SPLIT returns a table rather than a scalar value, so extracting a specific position requires a subquery or CROSS APPLY. The ordinal parameter also requires SQL Server 2022 or Azure SQL. Older versions need a custom workaround.

That makes this a genuinely portable pattern worth knowing — whether you’re in DuckDB on a laptop or Spark on a cluster, the non-existent delimiter trick works the same way.

The overall point:

When your data structure fights your parser, sometimes the cleanest solution is to tell the parser there is no structure — then handle it yourself with string functions. Inelegant perhaps, but practical and portable.