XML
Parsing XML in BigQuery With a JavaScript UDF or Upstream
BigQuery has no XML loader or functions. Two routes: a JavaScript UDF with fast-xml-parser, tested here in Node, or parsing before the load to Parquet.
6 min read
Library
Everything you need to level up your coding skills.
Excel
Putting a README Sheet Into Every Generated Workbook
Add a README sheet to every generated workbook with the period, source, definitions, checks and version, written by the same code that builds the report.
7 min read
CSV
Exporting a PostgreSQL Query to CSV With psql and COPY
Export a PostgreSQL query to CSV with psql: NULL against empty string, delimiter and quoting options, encodings, why variables fail and the file left behind.
9 min read
YAMLGuide
Analyzing Configuration and Metadata Held in YAML With SQL
Turn a repository of YAML into tables and ask SQL: Compose fleets, version drift, Kubernetes audits, dbt coverage and duplicate keys, with tested results.
17 min read
JSON
Finding One Bad Line in a Huge JSON Lines File Fast
Locate the malformed record in a large JSON Lines file with jq, Python, awk and DuckDB, why parser line numbers drift, and how to split good from bad.
7 min read
CSVGuide
Exporting Query Results to CSV From Five Major Databases
The same eight awkward rows exported from PostgreSQL, MySQL, SQLite, DuckDB and MongoDB, then read back: four tools return all 8, one recipe returns 4.
25 min read
Excel
Handling Currency and Locale in Generated Workbooks
Handle currency and locale in generated workbooks: store numbers, set format codes, name the currency and know what the reader's settings change.
7 min read
XML
Loading XML Into Snowflake With Native Support and FLATTEN
Snowflake loads XML into VARIANT with a file format of TYPE XML, and XMLGET plus FLATTEN turn it into rows. The statements, the data shape and the limits.
6 min read
CSV
Writing Data Quality Checks for CSV Files in dbt and DuckDB
Read a CSV in a dbt model with DuckDB and test it with unique, accepted_values, relationships and SQL tests: 6 of 8 failed on a faulty file.
12 min read
JSON
Streaming JSON Lines Into PostgreSQL Using COPY and psql
Load a JSON Lines file into a PostgreSQL jsonb column with COPY. The backslash trap in text format, the csv-format fix, bad-line errors and timings.
8 min read
Excel
Rounding and Formatting Numbers for Finance Readers
Round and format numbers for finance readers: store exact values, round once on purpose, use number formats for display and make displayed totals add up.
7 min read
CSV
Making a CSV Contract With Your Data Provider in Writing
What to agree with whoever sends you CSV files, as a document plus a manifest your code can check: six deliveries, six different rejections, 193,708 rows.
12 min read
Get new articles by email
We send one email when a new article is published. No spam.