Convert CSV to NDJSON for BigQuery / Snowflake

NDJSON mode, inference on. BigQuery autodetect reads events as INTEGER and active as BOOLEAN.

Input (users.csv):
user_id,events,active
1,12,true
2,0,false

Output (NDJSON):
{"user_id":1,"events":12,"active":true}
{"user_id":2,"events":0,"active":false}

bq load --source_format=NEWLINE_DELIMITED_JSON --autodetect \
  analytics.users users.ndjson

Skip empty cells on: a blank country omits the key, so BigQuery loads NULL. With it off you would get an empty string instead.

Input:
user_id,country
1,US
2,

Output (Skip empty cells ON):
{"user_id":1,"country":"US"}
{"user_id":2}

→ row 2's country loads as NULL, not ''

Stage the NDJSON, then COPY INTO with a JSON file format. Each line becomes one VARIANT or typed-column row.

Output (NDJSON, staged as @my_stage/users.ndjson):
{"user_id":1,"plan":"pro"}
{"user_id":2,"plan":"free"}

COPY INTO users
  FROM @my_stage/users.ndjson
  FILE_FORMAT = (TYPE = JSON);

A 19-digit key exceeds safe-integer range; inference keeps it a string so it loads to a STRING/NUMERIC column without rounding.

Input:
event_key,name
9223372036854775807,launch

Output (NDJSON):
{"event_key":"9223372036854775807","name":"launch"}
→ load into a STRING or NUMERIC column, then CAST as needed

Turn inference off when you would rather do all typing in the warehouse. Every value is a quoted string; CAST in the load SELECT or a downstream view.

Input:
id,amount,paid
1,19.99,true

Output (NDJSON, inference OFF):
{"id":"1","amount":"19.99","paid":"true"}
→ SELECT CAST(amount AS NUMERIC), CAST(paid AS BOOL) …

By default a blank cell becomes "", which warehouses load as an empty string, not NULL — these are distinct and can break WHERE col IS NULL logic. Turn on Skip empty cells to omit the key so the column loads as NULL. The literal text null (any case) is always emitted as JSON null regardless of this setting.

With inference on, 00042 becomes 42 and autodetect picks INTEGER. For a key that must stay exact, turn off Infer numbers, booleans, null so the column is a string, then load it to a STRING column. Inference is global — if you need other columns typed, load everything as STRING and CAST in SQL.

An integer beyond JavaScript's safe-integer range is kept as a string by inference, avoiding silent corruption. BigQuery's INT64 actually goes higher than JS safe-int, so if you need the numeric value, load this column into NUMERIC/BIGNUMERIC (or STRING) and CAST — the string is exact, so no precision is lost in the file.

2026-06-12 and ISO timestamps don't match the numeric/boolean/null patterns, so they stay strings. Autodetect types them as STRING. Either define an explicit schema with a DATE/TIMESTAMP column (BigQuery parses ISO strings into it), or CAST in a downstream view.

BigQuery autodetect samples rows; a column that is mostly numeric but has one stray non-numeric cell may type as STRING or fail. This is a warehouse behaviour, not a converter behaviour — the NDJSON faithfully reflects the cells. Supply an explicit schema to bq load (--schema) for predictable typing on messy columns.

address.city is a literal field name, not a nested BigQuery RECORD/STRUCT. To load nested structures, convert here, then expand dotted keys with json-unflattener before loading, or define a STRUCT schema and provide nested NDJSON.

A header with spaces or a leading digit (1st_visit, User Name) becomes a JSON key the same way, but BigQuery/Snowflake reject it as a column name on autodetect. Rename the columns in the source CSV (or with csv-header-rename) to legal identifiers before converting.

Free tier caps at 2 MB / 500 rows — fine for a sample, not a load. Pro is 100 MB / 100,000 rows, Developer 5 GB with no row cap. For very large loads, split the CSV into chunks under your cap, convert each to NDJSON, and load them as a wildcard set (bq load … data_*.ndjson).