XML
Dealing With XML Feeds That Break Their Own Schema
A feed with a raw ampersand, a missing id, a bad date, a duplicate id and an extra element: repair what is safe, quarantine the rest, report each reject.
7 min read
Tag
how-to
590 posts
JSON
Handling a Weather API With Nested Arrays of Measurements
Weather APIs return columns as parallel arrays. Turn them into rows with pandas and DuckDB, handle nulls and unequal lengths, and keep units and zones.
6 min read
JSON
Cleaning a Twitter Style Archive File That Is Not Quite JSON
Clean a social media archive that is JavaScript, not JSON: strip the assignment, join parts, fix string numbers and dates, unescape HTML and write Parquet.
6 min read
JSON
Flattening a Shopify Style Order Export Into Tables
Flatten a Shopify style export of 20,000 orders into orders and line items with pandas and DuckDB: money as strings, null customers, tags and zones.
7 min read
JSON
Analyzing a GitHub Events Dump With DuckDB and SQL
Query a GitHub style events dump of 300,000 JSON lines in DuckDB: one struct for ten event types, truncated commit lists and three-state booleans.
7 min read
JSON
Normalizing a Stripe Style Webhook Payload in DuckDB
Turn a Stripe style webhook log into tables with DuckDB: dedupe retries, zero-decimal currencies, invoice lines and the metadata map that hides gaps.
7 min read
JSON
Fixtures in JSON for Database Tests With PostgreSQL
Load JSON fixtures into PostgreSQL for tests with jsonb_populate_recordset: 25,000 rows in 0.21 s, bad fixtures caught by constraints, one typo missed.
6 min read
JSON
Contract Testing JSON APIs With an OpenAPI Schema Tool
Test a JSON API against its OpenAPI contract with Schemathesis: 1,903 generated requests found a 500 and undocumented statuses, and missed one bug.
6 min read
CSVGuide
Worked CSV Problems and the Patterns Behind Them Explained
Eight messy CSV files solved end to end with a check each: mixed dates, unquoted commas, multi-line notes, display formats, headers, bad quotes, footnotes.
18 min read
CSV
Reconciling a Bank Statement CSV With Your Ledger in SQL
Match a bank statement CSV against a ledger in SQL with a date tolerance, pair duplicate amounts correctly, and explain the difference in totals row by row.
7 min read
CSV
Making Quick Charts From a CSV With Python and DuckDB
Make quick charts from a large CSV: aggregate in DuckDB, plot with matplotlib or draw text bars in the terminal, and keep the charts honest.
6 min read
CSV
Checking if a CSV Column Is Normally Distributed With Python
Test whether a CSV column follows a normal distribution using skew, kurtosis, percentiles and Shapiro-Wilk, and why a huge file always fails the test.
7 min read