JSON
Snapshot Testing JSON Output Without Flaky Failures
Snapshot tests for JSON in pytest with syrupy and Node's test runner: what a snapshot stores, why random ids break it, and matchers and scrubbing that fix it.
6 min read
Tag
how-to
590 posts
CSV
Loading CSV Into BigQuery With bq load and Its Flags
Load a CSV from Cloud Storage into BigQuery with bq load: explicit schema, bad-record limits, quoted newlines, replace versus append and a job ID for reruns.
8 min read
CSVGuide
Loading CSV Into Cloud Warehouses Without Painful Reloads
How to load CSV into BigQuery, Snowflake and ClickHouse: stage the file, declare the schema, pick an error policy, load once and check the counts afterward.
17 min read
CSV
Datasette for Publishing CSV as a Searchable Site in Minutes
Turn a CSV into a browsable, searchable, filterable website with sqlite-utils and Datasette, with a JSON and CSV API, facets and full-text search built in.
7 min read
CSV
Polars for Large CSV Processing Beyond Memory Limits
Use Polars lazy scanning to filter and group a large CSV without loading it whole, set column types so zip codes stay text, and write Parquet as you go.
6 min read
PostgreSQL
Exporting JSON Lines From a Query in PostgreSQL Safely
Export JSON Lines from a PostgreSQL query with row_to_json or to_jsonb and COPY TO STDOUT, handle quoting, types, nulls, large results and compression.
6 min read
ExcelGuide
A Practical Guide to Connecting Excel Directly to a Database
Connect Excel to PostgreSQL, MySQL and SQL Server with connectors and ODBC, using read only accounts, parameters, safe credentials, refresh and speed checks.
16 min read
Excel
Keeping Excel as the Front End to a Database Safely
Keep Excel for reading and analysing database data through queries and refresh, and decide how writes should reach the database without losing rules.
7 min read
Excel
Turning Repeating Columns Into a Proper Child Table
Replace columns like Phone 1, Phone 2, Phone 3 from an Excel sheet with a child table: unpivot in SQL or pandas, drop blanks, add keys and check the counts.
7 min read
Excel
Designing Database Tables From a Messy Excel Sheet
Turn a wide Excel sheet into database tables: find the entities, choose keys, remove repeated groups and derived columns, and check the design with queries.
8 min read
Excel
Moving From Excel to PostgreSQL Step by Step Without Loss
Move an Excel workbook into PostgreSQL: staging table, clean, normalized tables, constraints, roles, a verified load, and Excel as the read connection.
7 min read
Excel
Moving From Excel to SQLite for a Small Team Step by Step
Move an Excel list into SQLite: clean it, design tables, load with Python, add constraints, query from Excel and know the limits for a small team.
7 min read