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
Tag
how-to
590 posts
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
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
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
XMLGuide
Handling XML in Warehouses and Lakes the Practical Way
How to get XML into Snowflake, BigQuery, Spark, DuckDB and a lake: keep raw files, parse once with a typed schema, store Parquet and reconcile counts.
16 min read
Excel
Producing a Monthly Management Pack From SQL Queries
Produce a monthly management pack from SQL: fixed sheets, prior month and budget comparisons, checks that tie out, and a scheduled build with sign off.
8 min read