Loading CSV Into Cloud Warehouses Without Painful Reloads

Loading a CSV into a database on one server and loading it into a cloud warehouse look like the same job, and they are not. On a server you run a command, watch it, fix the line it complains about and run it again. A warehouse runs your load as an asynchronous job against files in object storage, bills you for what it reads and reports problems after the fact, sometimes per file and sometimes as a count with a handful of sample errors. A mistake that costs a minute on a laptop costs a re-upload, a queue wait and a puzzling error message in the cloud. This guide covers what is common to BigQuery, Snowflake and ClickHouse, the decisions to make before the first load, and the checks afterward, and points to the per-engine articles for the commands. For the same job on an ordinary database, the pattern is the one in automating CSV imports into a database.

The shape every warehouse shares

Three steps recur, whatever the product. First, the file is staged: copied to object storage the warehouse can read (Google Cloud Storage for BigQuery, S3, GCS or Azure Blob for Snowflake and, through table functions, ClickHouse). Second, a load statement or job reads the staged file into a table. Third, you verify and publish: check the result, then make it visible to the people and dashboards that read the table.

The engines differ in the details of the second step, and the table below sets them side by side. Treat it as a map, not a manual.

The per-engine articles cover the exact commands: loading CSV into BigQuery with bq load, loading CSV into Snowflake and handling errors and loading CSV into ClickHouse from the command line. If your target is a classic RDBMS or a document store, see SQL Server with BULK INSERT, Oracle with SQL*Loader, DynamoDB and MongoDB with mongoimport.

Prepare the file before it leaves your machine

The cheapest place to fix a CSV is before it is uploaded, where you can open it, count it and run a command over it in seconds. Four things are worth doing every time.

Make it UTF-8. All three engines expect UTF-8 by default, with options for a few other encodings. A Windows-1252 file loads without a complaint and turns every accented letter into something else, and the damage is permanent once it is in a table (converting a CSV from Latin 1 or Windows 1252 to UTF8). Check for a byte order mark too, which turns the first column name into a different string (the UTF-8 BOM in CSV files).

Make the dialect boring. Comma, double quote, doubled quotes inside quoted fields, one header line, LF or CRLF consistently. Every option in a warehouse's load command exists to accommodate a file that is not boring, and each one is a place to be wrong. If a provider sends a semicolon file with a footer, convert it once at the edge (CSV dialects and variants you will run into covers the family).

Validate the structure. Count fields per line and look for quoted newlines, unterminated quotes and ragged rows before upload (validating a CSV file, CSVLint and other validators). A load that reads 40 million rows before failing on a stray quote is a bill and a wait that a laptop check would have saved.

Choose the file size on purpose. Warehouses parallelize across files, so one 50 GB file is a bottleneck and ten thousand 100 KB files is overhead. Snowflake's guidance is to aim for files of about 100 to 250 MB compressed, and BigQuery reads an uncompressed CSV in parallel but cannot split a gzip, so a large compressed file loads on one worker. A sensible target for any of them is a few hundred files of tens to low hundreds of megabytes, split on record boundaries (splitting a large CSV into smaller files).

Consider Parquet first

If you control the step before the warehouse, do not load CSV at all. Convert it to Parquet and load that. Parquet stores the schema with the file, compresses well and has none of the quoting, delimiter and encoding questions, and every warehouse here reads it natively. On the 2 million row test file:

csv        123.9 MB
csv.gz      40.2 MB   (4.6 s to compress)
parquet     49.1 MB   (0.68 s, DuckDB default)
parquet     32.6 MB   (0.69 s, zstd)

The conversion took about 0.7 seconds in DuckDB, because it reads the CSV in parallel and writes columnar chunks, and the zstd version was a quarter the size of the CSV and 20 percent smaller than the gzipped CSV. In DuckDB it was one statement:

COPY (SELECT * FROM read_csv('orders-2m.csv', types={'zip': 'VARCHAR'}))
TO 'orders.parquet' (FORMAT parquet, COMPRESSION zstd);

The types argument is the part that matters, and it leads to the next section. The conversion is also a free validation: if DuckDB reads the CSV and writes the Parquet, the file parsed with one consistent dialect, and the result's schema is a record of what you decided each column was. The cost is one more step and one more format to keep, and it pays back whenever the same data is loaded more than once or queried directly (why Parquet beats CSV for repeated analysis). Keep the original CSV as the evidence of what arrived.

Declare the schema

Each warehouse can work out a schema from the file, and each does it from a sample. BigQuery's autodetect looks at a limited number of rows at the start of the file, Snowflake's INFER_SCHEMA reads a sample of files and ClickHouse's schema inference reads a configurable number of rows. They face the same problem as every inferring reader in CSV tools worth knowing: a column of postal codes whose first rows have no leading zeros looks like integers. In the test file, a sample of the first 1,000 rows said Unsigned for zip, and the whole column said String, with values like 05398 further down.

So declare it. Write the table's columns and types yourself, with identifiers as strings, money as a decimal or numeric type and timestamps with their time zone stated, and load into that table. The load then fails on a value that does not fit, which is the behavior you want, and not on a schema that adapted to whatever it saw first. Autodetect is fine for a first look and for scratch tables, and a poor basis for anything that feeds a report. For money, use the exact numeric type the engine offers, since the float noise shown in Miller for records and pipelines (3196886.2000000123) is a reconciliation problem in a ledger.

Choose a bad-row policy

Every engine lets you decide what happens when a row will not load, and the defaults are strict: BigQuery's max_bad_records defaults to 0, Snowflake's COPY INTO aborts the statement on the first error unless ON_ERROR says otherwise, and ClickHouse accepts no errors unless you raise input_format_allow_errors_num or input_format_allow_errors_ratio. Strict defaults are right for a feed that should be clean, because the first bad row stops the job and you find out.

Loosening the policy is a decision with a price. A tolerance of 100 bad rows on a table with 50 million rows hides a systematic problem (a column shifting after row 12 million) behind a number that sounds small, and the rejected rows are lost unless you capture them. If you must tolerate bad rows, set a count or ratio, keep the rejects (Snowflake's VALIDATE and VALIDATION_MODE can report errors without loading, and BigQuery's job statistics list errors) and alert on the reject count. For a feed where a small tail of bad rows is expected, load to a text-typed staging table, validate in SQL and write rejects to their own table (logging rejected rows from a CSV import for review). The same policy question, file or row, applies as in automating CSV imports into a database.

Load once, or on purpose twice

Reloading is where warehouses differ most, so know which protection you have. BigQuery load jobs can be given a job ID, and a repeated job with the same ID is not run twice, and the other way to be safe is WRITE_TRUNCATE, which replaces the table's contents so a rerun gives the same result. Snowflake remembers which files a table has loaded, for 64 days, and skips them on a repeat COPY INTO unless you pass FORCE = TRUE, which makes the stage a natural deduplicator and a surprise when you re-upload a corrected file under the same name (it will be skipped, because the engine thinks it has seen it). ClickHouse can deduplicate identical insert blocks in replicated tables, within a window, a convenience and not a design to rely on.

None of these replaces your own record of what you loaded. Keep a ledger row per file with its name, a content hash, the row count and the load time, and key reruns on the hash, as in idempotent CSV imports that are safe to run twice. For a table that gets replaced daily, load into a new table and swap names, so readers never see a half-loaded table.

A daily feed, start to finish

It helps to walk one feed through all the steps, with the decisions in the order you meet them. Take a supplier that drops orders-YYYY-MM-DD.csv into a bucket every morning, around 120 MB, a few hundred thousand rows.

The first decision is the landing path. Files go to landing/orders/ and are never edited there, only moved out. A small job lists the prefix and finds files it has not recorded in its ledger, which holds the file name, the content hash from the object's metadata or a computed SHA-256, the row count and the load time. A file that is new by name and old by hash, because the supplier re-sent Tuesday's file as Wednesday's, is logged and skipped, and a file that is old by name and new by hash, a correction, is loaded and flagged.

Next the job validates a local copy of the file, or reads it through DuckDB straight from the bucket, which does not need a download: it checks the header against the expected list, counts fields on every line, parses every date and number against the declared types and compares the row count with the manifest if the supplier sends one. A failure here sends the file to landing/orders-rejected/ with a message and stops. Nothing has touched the warehouse yet, which is the point of validating first.

Then the file is converted. For a feed of this size I would write it as a Parquet file next to the CSV, in staged/orders/, with the schema declared in the conversion. The warehouse load reads the Parquet into a staging table named for the day. The merge step upserts from staging into the final table on the business key, in one statement, and the ledger row is written when the merge succeeds. The staging table is dropped after a retention period, not at once, because it is the quickest way to see what a file contained when someone asks.

The last step is the reconciliation: row count of staging against the file, total of the amount column against the file's total, the date range against the file's name. Any difference stops the merge and pages someone. All of it is boring, and that is the target. The cost of the whole design is a few hundred lines of code and a ledger table, and what it buys is that a Tuesday morning with a malformed file produces a message and a rejected-files folder and not a quiet gap in a revenue dashboard.

Timestamps, dates and time zones

Dates are where a warehouse load most often succeeds and is wrong. A CSV holds text like 2026-10-02 08:15:00 with no time zone, and each warehouse has several column types for it: BigQuery separates DATETIME (no zone) from TIMESTAMP (an instant in UTC), Snowflake has TIMESTAMP_NTZ, TIMESTAMP_LTZ and TIMESTAMP_TZ, and ClickHouse has DateTime with an optional zone and DateTime64 for sub-second precision. Loading a zone-less string into a zone-aware column makes the engine assume a zone, often UTC or the session's, and a feed written in local time then lands hours off, with no error and a shape that looks plausible.

Decide what the supplier's time means, write it down and load it that way. If the feed is in a known local zone, load into a no-zone column and convert with an explicit AT TIME ZONE in a view or a transformation, so the assumption is visible in SQL. If the supplier can send ISO 8601 with an offset (2026-10-02T08:15:00+02:00), ask for it: the offset is the only part of a timestamp that survives a change of laptop. For plain dates, YYYY-MM-DD loads without ambiguity in every engine, and 03/04/2026 does not, so reject day-first and month-first formats at the validation step (handling time zones in CSV timestamps goes through the cases).

Also check the edges after loading. The minimum and maximum of every date column, compared with what the business expects, catch the century typo, the epoch default of 1970-01-01 for a missing value and the date in the future that came from swapping day and month.

Compression, speed and what the engine can split

Compression changes how a load scales, and the rules differ by engine. A gzip file is a single stream: to read byte one million you decompress the first million, so one gzipped 20 GB CSV is read by one worker, however large the warehouse. BigQuery's documentation says so directly, noting that uncompressed CSV can be read in parallel while compressed CSV cannot be split, which is why a large load runs faster from many moderate files, or from a single uncompressed file, than from one huge .gz. Snowflake parallelizes across files and not within one, hence its guidance on file size. ClickHouse reads compressed input by detecting it from the extension or an explicit option, and an insert through the client is limited by the single connection unless you run several.

Parquet changes the picture. It is internally split into row groups that engines read in parallel, and it is compressed per column, so a modest file loads quickly and stays splittable. The test file as zstd Parquet was 32.6 MB against 40.2 MB for the gzipped CSV, and it was splittable, typed and free of dialect options. For a CSV you cannot convert, split on record boundaries to files of tens to a couple of hundred megabytes, compress each with gzip, and let the engine fan out across them.

Do not chase speed before correctness, however. A load that takes four minutes and is right beats one that takes forty seconds and puts a day's data in the wrong column. Measure the whole feed's path, from the supplier's drop to the table being queryable, and fix the longest step, which is usually waiting for the file or for a person to look at a failure, not the load itself.

Nested and awkward content

CSV is flat, and the content often is not. A column that holds a JSON string, a semicolon-separated list or a text field with embedded line breaks loads fine as text and needs a decision about where it gets parsed. Embedded newlines inside quoted fields are legal CSV and need an option in some engines (BigQuery's allow_quoted_newlines is off by default, and a file with such fields fails without it), and they defeat any tool that splits the file on lines before parsing (splitting a large CSV into smaller files shows how to cut on record boundaries). Load the text as it is and parse it in SQL, with the engine's JSON or array functions, so the raw value stays available when the parsing rule turns out to be wrong.

Very wide rows and very large fields have limits: BigQuery caps the size of a CSV row, and every engine has some limit for a single value, so a free-text column with whole documents in it may need to go through a different path. The same applies to binary data hidden in text. If a field is longer than anything a person typed, find out what it is before the load does.

Costs and surprises that arrive later

The expensive surprises are rarely in the load. Reading a CSV through an external table on every query bills you for scanning the whole file every time, which is why an external table over raw CSV is a bad dashboard backend and an external table over Parquet with partitions can be fine. A staging table that is never dropped becomes a second copy of your data, quietly growing. A scheduled load that retries on failure without a limit can load the same file many times into an append-only table, and the cost of cleaning that up exceeds the cost of the loads. And a cluster that is sized for the nightly load sits idle for twenty-three hours unless it suspends, which Snowflake warehouses do automatically if configured and which you must plan for elsewhere.

Put alerts on three things: the age of the newest successful load, the row count compared with the trailing average and the bytes scanned per query on the loaded tables. They catch a stuck feed, a truncated file and a dashboard that started reading the wrong table, respectively, and between them cover most of the incidents these pipelines have.

Check the numbers afterward

A load that returns "success" has loaded some rows, and proving that they are the right ones takes four numbers. The row count in the table should equal the row count of the file, from wc -l minus the header, or from a count of records if quoted newlines are present. A control total, the sum of a numeric column, should match a total computed on the file. The minimum and maximum of a date column should lie in the expected range. And the count of NULLs in each required column should be zero. Compute the file-side numbers with the tool you validated with (the DuckDB check on the test file gave 2,000,000 rows, a total of 160,174,646.36 and timestamps from 2025-01-01 00:00:04 to 2025-12-31 23:59:38) and the table-side numbers with a query, and fail the pipeline if they differ.

Do it before publishing the table to readers. In BigQuery and Snowflake, load into a staging table, check, then insert or swap. It is the same discipline as the staging merge in loading a CSV into a staging table, then merging, made more important by the fact that undoing a bad load in a warehouse means another job and, sometimes, another bill.

Permissions, regions and cost

Three practical matters decide whether a load works in production. Give the loading identity read access to one prefix of one bucket and write access to one dataset or schema, not an account-wide role; on Snowflake, use a storage integration so that the credentials never appear in a COPY statement or a stage definition, where they would sit in query history. Keep the bucket and the warehouse in the same region, because cross-region reads add latency and transfer charges, and some services refuse them. And know what a load costs: BigQuery's batch loads from Cloud Storage use a shared pool and are not charged for the load itself, though you pay for storage, while Snowflake charges for the warehouse compute that runs the COPY, and ClickHouse uses the cluster's resources directly. Quotas apply too, such as a maximum number of load jobs per table per day in BigQuery, which a loop that loads one file at a time can hit.

A checklist

Before the first load of any feed: the file is UTF-8 with no BOM and one dialect; the structure is validated; the schema is declared with identifiers as text; the bad-row policy is chosen and the rejects are kept; the file has a content hash recorded; the load goes to a staging table; and a count and a total are compared. Before the hundredth load, add: a freshness alert, a row-count comparison against recent days and a schema check that fails when the provider changes a column (handling a CSV provider who changes columns without warning).

When not to load CSV at all

Loading is the right answer for files that arrive from outside, and the wrong answer when the data already lives somewhere the warehouse can reach. If it is in object storage as Parquet, an external table queries it in place, and you load only if the query patterns justify the copy. If it comes from a database or a SaaS tool, a native connector or change-data-capture feed keeps the warehouse current without any file in the middle and without the dialect problems. If you need results in seconds, a streaming insert API fits better than a batch of files. And if the data is a few thousand rows that change monthly, a manual upload through the console is quicker than building a pipeline.

CSV is a transport, not a storage format, and the best loads treat it that way: parse once, with a known dialect and a declared schema, land typed data, and stop passing text around. The vendor documentation is the reference for flags that change release to release: BigQuery's CSV loading guide, Snowflake's COPY INTO reference and ClickHouse's CSV format page. For the format itself, RFC 4180 remains the baseline.