How to Import CSV Into BigQuery

BigQuery documents several ways to move a CSV into a table. The Cloud console takes an upload, and a local file there cannot pass 100 MB. The bq load command runs the same job from a terminal. A load job from Cloud Storage carries up to 15 TB across as many as ten million files. The Storage Write API streams rows in as they happen. Every one of those starts from a seat that already holds the project's IAM. Your customer sits outside that seat, holding a folder of meter readings a property manager exported, one file per building.

Two of those files arrive looking like this.

1meter_serial,site,read_date,register,reading_kwh,tariff,read_type2E14K0392117,Riverside Court,2026-03-01,R1,12345.6,Day,Actual3E14K0392117,Riverside Court,2026-03-01,R2,4180.2,Night,Actual4E14K0392208,Riverside Court,2026-03-01,R1,9874.0,Peak,Estimated⋮180 rows not shown185E14K0397740,Riverside Court,2026-03-01,R2,3618.9,Night,Actual

The second building runs on different software, and its export shows it.

1msn,site,read_date,register,reading_kwh,tariff,read_type2K21M0044831,Harbour View,01.03.2026,R1,"12 345,6",DAY,Actual3K21M0044902,Harbour View,13.03.2026,R1,"8 902,4",Peak,Actual⋮129 rows not shown133K21M0049518,Harbour View,27.03.2026,R2,"4 217,8",Night,Actual

One header disagrees, and it is the one that identifies the meter. msn is a short form for a meter serial number, and no built-in synonym covers it. The readings disagree about punctuation. One file writes 12345.6 and the other writes 12 345,6, with a space for grouping and a comma for the decimal. The dates disagree about order. One file writes 2026-03-01 and the other writes 01.03.2026. The tariff column says Day in one file and DAY in the other.

The person drops both files into the importer inside your app. Updog Importer reads them in the browser, matches each file's headers to your schema, and puts every value in front of them. Your onComplete handler receives the rows grouped by the file they came from. The handler posts them to a route you own. The route stages the batch with a load job and runs one MERGE. BigQuery writes the target table.

No Updog server stands between the browser and BigQuery.

The tables the rows have to reach

Two tables carry the whole design, and the second one is temporary.

CREATE TABLE analytics.meter_readings (
  account_id   STRING    NOT NULL,
  meter_serial STRING    NOT NULL,
  site         STRING    NOT NULL,
  read_date    DATE      NOT NULL,
  register     STRING    NOT NULL,
  reading_kwh  NUMERIC,
  tariff       STRING,
  read_type    STRING,
  source_file  STRING,
  updated_at   TIMESTAMP NOT NULL
)
PARTITION BY read_date
CLUSTER BY account_id, site, meter_serial;

CREATE TABLE staging.meter_readings_inbox (
  batch_id     STRING    NOT NULL,
  account_id   STRING    NOT NULL,
  source_file  STRING    NOT NULL,
  meter_serial STRING,
  site         STRING,
  read_date    DATE,
  register     STRING,
  reading_kwh  NUMERIC,
  tariff       STRING,
  read_type    STRING,
  loaded_at    TIMESTAMP NOT NULL
)
PARTITION BY DATE(loaded_at)
OPTIONS (partition_expiration_days = 3);

read_date is the partition column, because every question anybody asks of meter readings names a period. BigQuery partitions a DATE column daily, monthly or yearly, and one table takes one partition column. CLUSTER BY sorts the storage blocks inside each partition, and BigQuery caps that list at four columns. The staging table partitions on load time and carries partition_expiration_days = 3, so a batch nobody merged drops itself. Partitions do not expire by default.

The schema in Updog Importer

The columns array is the same shape written for the person looking at the file.

import type { DataEditorColumn } from "@updog/data-editor";

const TARIFFS = ["Day rate", "Night rate", "Peak rate"];
const READ_TYPES = ["Actual", "Estimated", "Customer"];

export const columns: DataEditorColumn[] = [
  {
    id: "meterSerial",
    title: "Meter serial",
    size: 150,
    transformer: (value) => String(value).trim(),
    validators: [{ type: "required" }],
  },
  {
    id: "site",
    title: "Site",
    size: 180,
    validators: [{ type: "required" }],
  },
  {
    id: "readDate",
    title: "Read date",
    size: 130,
    editor: { type: "date" },
    validators: [
      { type: "required" },
      { type: "date" },
    ],
  },
  {
    id: "register",
    title: "Register",
    size: 100,
    validators: [{ type: "required" }],
  },
  {
    id: "readingKwh",
    title: "Reading kWh",
    size: 140,
    editor: { type: "number" },
    validators: [
      { type: "number", min: 0, decimalPlaces: 3 },
    ],
  },
  {
    id: "tariff",
    title: "Tariff",
    size: 140,
    editor: { type: "select", options: TARIFFS, enableCustomValue: false },
    validators: [{ type: "oneOf", values: TARIFFS }],
  },
  {
    id: "readType",
    title: "Read type",
    size: 140,
    editor: { type: "select", options: READ_TYPES, enableCustomValue: false },
    validators: [{ type: "oneOf", values: READ_TYPES }],
  },
];

Each editor earns its place against what arrives. The date editor turns 01.03.2026 into 2026-03-01, the format BigQuery requires for a DATE column in loaded JSON. The number editor collapses the space grouping and swaps the comma for a dot, so 12 345,6 lands as 12345.6. Both editors read the locale off the file itself, and each file in one import gets its own verdict. The date scan settles on the first value carrying a part above 12, which is the 13.03.2026 in the second file. The number scan settles on a vote, where a value with one separator and one or two trailing digits counts once. Neither file's punctuation reaches the other.

The select editors with enableCustomValue off hold both list columns to their options. Anything outside the list travels to the value matching step, and a value nobody maps is dropped from the row.

The synonyms prop teaches matching the words your customers already use, and the mount ties the rest together.

<DataEditor<Reading>
  apiKey="your-license-key"
  variant="uploader"
  open={open}
  onClose={closeImporter}
  columns={columns}
  primaryKey={["meterSerial", "readDate", "register"]}
  synonyms={{
    columns: {
      meterSerial: ["msn", "meter serial number", "meter point"],
      readingKwh: ["kwh", "consumption", "units"],
    },
  }}
  blockSubmitOnError
  onComplete={onComplete}
/>

msn reaches meterSerial through that table and through nothing else. Fuzzy matching scores it at zero. The string appears inside neither meterSerial nor Meter serial, shares no word with either, and stands nine edits away where the spelling tier allows three. A match needs sixty. The kwh and consumption entries sit there for the next property manager. The tariff values need no help. Night and Peak sit inside Night rate and Peak rate. Day and DAY both flatten to day and reach Day rate on the word they share. Whatever the person fixes by hand comes back on the result as learnedSynonyms, ready to store and feed back next time.

primaryKey takes three columns here, because one meter reports several registers on the same day. An imported row merges into an existing row only when all three match. A row with an empty part merges with nothing and arrives as new. That prop settles identity inside the browser. The MERGE further down settles it against the warehouse, and the two have to name the same columns.

Every snippet here is React. Those props reach Vue, Angular and Svelte through the web component build. The meter schema and the handler travel there unchanged. The install and the modal wiring beneath this mount live in how to import a CSV file into a React app.

The result on submit

On submit, Updog Importer hands your handler every row grouped by source. An import contributes one entry per file, plus one per sheet when a workbook holds several. A whole file takes the file name, and a sheet takes the file name and the sheet name together.

import type { DataEditorResult } from "@updog/data-editor";

const BATCH_SIZE = 500;

const onComplete = useCallback(async (result: DataEditorResult<Reading>) => {
  const rows = result.sources.flatMap((source) =>
    source.rows
      .filter((entry) => !entry.isDeleted)
      .map((entry) => ({ ...entry.row, sourceFile: source.sourceName })),
  );

  let batchId;

  for (let start = 0; start < rows.length; start += BATCH_SIZE) {
    const response = await fetch("/api/readings/stage", {
      method: "POST",
      headers: { "Content-Type": "application/json" },
      body: JSON.stringify({
        batchId,
        rows: rows.slice(start, start + BATCH_SIZE),
      }),
    });

    const body = await response.json();
    if (!response.ok) throw new Error(body.message);
    batchId = body.batchId;
  }

  const merged = await fetch("/api/readings/merge", {
    method: "POST",
    headers: { "Content-Type": "application/json" },
    body: JSON.stringify({ batchId }),
  });

  const outcome = await merged.json();
  if (!merged.ok) throw new Error(outcome.message);
}, []);

Every staged row now carries the export it arrived in. When one building's readings turn out wrong, the file that carried them has a name inside the warehouse.

Google's quotas page caps a streaming request at 50,000 rows and adds that a maximum of 500 rows is recommended. Nothing equivalent is published for a load job, so 500 carries over here and keeps each POST small. A different destination publishes different numbers, and how to import CSV into Supabase derives its chunk from a request body cap and a role statement timeout. blockSubmitOnError keeps submit disabled while any row carries an error, so the handler never has to sort clean rows from broken ones.

Throw when a route answers with a failure. Updog holds submit until your promise resolves, then empties the editor. A handler that traps the error and returns counts as a finished load, and the readings clear the grid unwritten. A thrown error keeps the grid as it stands, with every reading, match and hand correction on it. The person submits again on the readings that never left the screen.

The staging posts and the merge all run behind a spinner in the confirm dialog. That is the second reason a batch stays small. Anything on the result worth keeping gets copied inside the handler. Once the promise resolves the editor drops its rows, its sources, its history and its learned synonyms.

The fork in the write path

BigQuery takes rows two ways, and its published limits decide which one a correcting import needs.

Streaming puts rows in front of a query straight away. The Node client's table.insert() posts to the Storage Write API (REST). Google renamed that path from tabledata.insertAll and still fully supports it. Rows are readable the moment BigQuery acknowledges the request. The request caps at 10 MB and 50,000 rows, and a single row caps at 10 MB. The bill runs $0.01 per 200 mebibytes with a 1 KB minimum per row, and a meter reading row sits far under 1 KB. The client also fills an insertId on every row, and Google calls leaving it out the recommended way to insert data. Set createInsertId to false and the client stops adding one.

Streamed rows are locked against DML for thirty minutes. BigQuery's own page states it plainly. Rows written through the Storage Write API (REST) cannot be modified by UPDATE, DELETE, MERGE or TRUNCATE inside the last 30 minutes of their write. Rows written through the Storage Write API (gRPC) are exempt on the same page, and that path ships as a separate client package. So an import whose whole job is correcting readings already stored waits half an hour, or it stages the batch and merges.

A load job carries no such buffer. It costs nothing on the shared slot pool, and its result is atomic across the whole payload. BigQuery allows 1,500 load jobs per table per day. Five hundred rows a request leaves room for 750,000 readings a day into one staging table, which is arithmetic worth redoing against your own volume.

Google points a new project at the gRPC Storage Write API. Its page names lower pricing and exactly-once delivery semantics. That path costs $0.025 per gibibyte with the first 2 TiB each month free. It also wants protocol buffers, a long-lived connection and a second client package. Reach for it when rows arrive all day from a service you run.

So the path below stages a batch with a load job, then moves it across with one MERGE.

The load job that stages a batch

The route holds the credential, and the browser stops there.

import { BigQuery } from "@google-cloud/bigquery";
import { Readable } from "node:stream";
import { randomUUID } from "node:crypto";

const bigquery = new BigQuery();
const inbox = bigquery.dataset("staging").table("meter_readings_inbox");

const SCHEMA = {
  fields: [
    { name: "batch_id", type: "STRING", mode: "REQUIRED" },
    { name: "account_id", type: "STRING", mode: "REQUIRED" },
    { name: "source_file", type: "STRING", mode: "REQUIRED" },
    { name: "meter_serial", type: "STRING" },
    { name: "site", type: "STRING" },
    { name: "read_date", type: "DATE" },
    { name: "register", type: "STRING" },
    { name: "reading_kwh", type: "NUMERIC" },
    { name: "tariff", type: "STRING" },
    { name: "read_type", type: "STRING" },
    { name: "loaded_at", type: "TIMESTAMP", mode: "REQUIRED" },
  ],
};

app.post("/api/readings/stage", async (request, response) => {
  const session = await getVerifiedSession(request);
  if (!session) return response.status(401).json({ message: "Not signed in" });

  const batchId = request.body.batchId ?? randomUUID();
  const loadedAt = new Date().toISOString();

  const ndjson = request.body.rows
    .map((row) =>
      JSON.stringify({
        batch_id: batchId,
        account_id: session.accountId,
        source_file: row.sourceFile,
        meter_serial: row.meterSerial,
        site: row.site,
        read_date: row.readDate,
        register: row.register,
        reading_kwh: row.readingKwh === "" ? null : row.readingKwh,
        tariff: row.tariff ?? null,
        read_type: row.readType ?? null,
        loaded_at: loadedAt,
      }),
    )
    .join("\n");

  await new Promise((resolve, reject) => {
    Readable.from([ndjson])
      .pipe(
        inbox.createWriteStream({
          sourceFormat: "NEWLINE_DELIMITED_JSON",
          schema: SCHEMA,
          writeDisposition: "WRITE_APPEND",
        }),
      )
      .on("error", reject)
      .on("complete", resolve);
  });

  response.json({ batchId, staged: request.body.rows.length });
});

getVerifiedSession() stands in for your own server-side authentication check. account_id comes off that session and never off the request body, so a stolen batch id merges nothing. The reading arrives as the string 12345.6 and stays a string. Google's streaming page requires a NUMERIC value in a row to sit inside double quotation marks, and keeping the string on the load path costs nothing. read_date arrives as 2026-03-01 and needs nothing. A select value nobody mapped never reaches the row at all, so tariff and read_type fall back to null here.

The credential is the service account attached to the service. It needs roles/bigquery.dataEditor on both datasets, plus roles/bigquery.jobUser on the project to run the jobs. For a Cloud Run service, Google recommends a user-managed service account as that identity over the Compute Engine default. Google writes that the best way to mitigate the threats around downloadable keys is "to avoid user-managed service account keys and to use other methods to authenticate service accounts whenever possible". The same page calls a key "an exception rather than the norm". An attached identity needs no key file at all.

One MERGE into the partitioned target

One statement moves the batch across, and it has to survive the same export arriving twice.

MERGE analytics.meter_readings AS t
USING (
  SELECT * EXCEPT (rn)
  FROM (
    SELECT
      account_id, meter_serial, site, read_date, register,
      reading_kwh, tariff, read_type, source_file,
      ROW_NUMBER() OVER (
        PARTITION BY meter_serial, read_date, register
        ORDER BY loaded_at DESC
      ) AS rn
    FROM staging.meter_readings_inbox
    WHERE batch_id = @batch_id
      AND account_id = @account_id
  )
  WHERE rn = 1
) AS s
ON  t.account_id   = s.account_id
AND t.meter_serial = s.meter_serial
AND t.read_date    = s.read_date
AND t.register     = s.register
AND t.read_date BETWEEN @from_date AND @to_date
WHEN MATCHED THEN UPDATE SET
  site        = s.site,
  reading_kwh = s.reading_kwh,
  tariff      = s.tariff,
  read_type   = s.read_type,
  source_file = s.source_file,
  updated_at  = CURRENT_TIMESTAMP()
WHEN NOT MATCHED THEN INSERT (
  account_id, meter_serial, site, read_date, register,
  reading_kwh, tariff, read_type, source_file, updated_at
)
VALUES (
  s.account_id, s.meter_serial, s.site, s.read_date, s.register,
  s.reading_kwh, s.tariff, s.read_type, s.source_file, CURRENT_TIMESTAMP()
);

MERGE combines insert, update and delete into one statement and runs them atomically. The USING subquery deduplicates first. A target row that joins with more than one source row fails the whole statement with UPDATE/MERGE must match at most one source row for each target row. ROW_NUMBER() keeps the newest reading per meter, date and register. March sent twice writes March once, and a corrected reading overwrites the one already stored.

The route reads the batch's own date range with one small query first, so @from_date and @to_date arrive as parameters. That predicate sits in the merge condition on purpose. Google documents three places a partition filter limits which partitions get scanned, and the merge condition is one of them. The optimizer might or might not push it down, depending on the join type, so read the execution plan against your own table.

BigQuery runs two concurrent mutating DML statements per table, queues twenty more, and fails anything past that. One MERGE for the whole import stays clear of that ceiling. One MERGE per file walks toward it.

The cost of a partitioned target

Partitioning changes what that statement costs every time it runs. On on-demand billing, a MERGE carrying an UPDATE clause against an unpartitioned table is billed for the bytes the statement reads plus the size of the whole table before the change. On a partitioned table the second half shrinks to the total size of the partitions the statement updates. A month of readings merged into three years of history is billed against the partitions that month lands in.

Clustering by account_id, site and meter_serial sorts the blocks inside each partition, so a query about one building reads the blocks holding that building. BigQuery stops giving an exact cost estimate before a clustered query runs, because the number of blocks stays unknown until it does. Google's own pricing page says to use partitioning and clustering wherever possible. Set require_partition_filter on the target and queries against the table have to carry a filter on read_date.

On-demand queries cost $6.25 per tebibyte after the first tebibyte each month, with a 10 MB minimum for every table a query touches. Google prices each region on its own row, so read the one your dataset sits in. Pruning is what keeps the tebibytes counted against a month.

The routes nobody ships for you

Updog Importer integrates with nobody. There is no BigQuery connector, no destination list, no webhook and no server of ours. onComplete hands your code an object, and the two routes in the middle are work you do.

BigQuery already ships its own way in for the other case. The Cloud console takes a local file up to 100 MB, one file at a time, with no wildcards. The bq load command runs from a terminal. A load job from Cloud Storage carries 15 TB across ten million files on the default quota, and costs nothing on the shared slot pool. Every one of those needs a Google Cloud seat with the project's IAM behind it. For an export your own team downloaded, those tools are the shorter way in. Everything above exists for the export a property manager sent, opened in a browser inside a session your app issued. Client-side and server-side CSV import names the jobs each of the two models fits.

The pieces you wrote and the next export

You wrote two tables, a schema with seven columns, a handler that batches, a route that stages, and one MERGE. The files stay on the machines that opened them. The rows travel from your own front end to your own routes, and from there into BigQuery, and the only party you added to the chain is yourself. Point the same setup at a React CSV importer modal or at the web component and the middle stays the same.

The property manager will send March again later, with two corrections and one meter that was estimated the first time. That time the mappings are already stored, the dates already parse, and the MERGE writes two readings where a second copy of the month would have gone.