How to Import CSV Into Amazon Redshift

Amazon Redshift documents several ways to move a CSV into a table. Query editor v2 takes a local file up to 100 MB, once an administrator has named a staging bucket in Account settings. The same wizard loads from S3 and writes the COPY for you. COPY from S3 by hand reads every object under a key prefix in parallel. An S3 event integration turns new files under a prefix into COPY commands nobody has to run. Every one of those starts from a seat that already holds the cluster's IAM. Your customer sits outside that seat, holding an enrolment export a university registrar sent them.

The constraints Redshift will not enforce

Redshift takes the primary key you declare and reads it as a hint. AWS states it plainly. "Uniqueness, primary key, and foreign key constraints are informational only", and the same sentence says Amazon Redshift does not enforce them when you populate a table. An insert that violates one succeeds. The query planner still uses the key, and AWS says it "assumes that all keys in Amazon Redshift tables are valid as loaded", so a duplicate that reaches the table can make a SELECT DISTINCT return duplicate rows.

Redshift does enforce a NOT NULL column constraint.

So the duplicate has to be caught before the load. In how to import CSV into PostgreSQL the database rejects the row and hands back an error code. Here nothing rejects anything, and the two places left are the browser and the merge.

The file that arrives

The registrar exports the term from the student information system, and it looks like this.

1Student ID,Term,Course,Sec,Credits,Status,Enrolled,Campus224-118392,FA26,BIOL 1010,001,3,Enrolled,24/08/2026,Riverbank324-118392,FA26,BIOL 1010,001,3,ENR,24/08/2026,Riverbank424-119045,FA26,MATH 2210,002,"3,5",Withdrawn,02.09.2026,Northgate524-119045,FA26,PSYC 1101,001,3,Registered,02.09.2026,Northgate⋮8935 rows not shown894124-121870,FA26,ENGL 1020,003,3,Enrolled,04/09/2026,Riverbank

One header is short. Sec means the section number and no built-in synonym covers it. The first two lines carry the same student, term, course and section. The credits disagree about punctuation, one line writing 3 and another writing 3,5. The dates disagree about order, one writing 24/08/2026 and another writing 02.09.2026. The status column says Enrolled on one line, ENR on the next, and Registered further down.

The person drops that file into the importer inside your app. Updog Importer reads it in the browser, matches the headers to your schema, and puts every value in front of them beside the rows already in the warehouse. Your onComplete handler receives each row with a verdict on it. The handler posts the rows to a route you own, the route writes them to S3, and one Data API call turns them into a load, a merge and a delete.

No Updog server stands between the browser and Redshift.

The table the rows have to reach

One table holds the enrolments, and it declares the key it will never enforce.

create table analytics.enrolment (
  institution_id varchar(36)  not null,
  student_ref    varchar(16)  not null,
  term_code      varchar(8)   not null,
  course_code    varchar(16)  not null,
  section        varchar(8)   not null,
  credits        decimal(4,1),
  status         varchar(16),
  enrolled_on    date,
  campus         varchar(64),
  source_file    varchar(256),
  updated_at     timestamp    not null,
  primary key (institution_id, student_ref, term_code, course_code, section)
)
diststyle key
distkey (student_ref)
compound sortkey (institution_id, term_code);

AWS says to "always declare primary and foreign keys and uniqueness constraints when you know that they are valid", because the planner uses them to order joins and drop redundant ones. student_ref is the distribution key, so a student's rows sit on one slice and the merge join stays local. The sort key runs institution then term, since every question anybody asks of enrolment names a term. campus takes varchar(64) because Redshift measures a character column in bytes, and a campus name outside ASCII spends more than one byte per letter.

The schema in Updog Importer

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

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

const STATUSES = ["Enrolled", "Withdrawn", "Audit"];

export const columns: DataEditorColumn[] = [
  {
    id: "studentRef",
    title: "Student ref",
    size: 140,
    transformer: (value) => String(value).trim(),
    validators: [
      { type: "required" },
      { type: "regex", pattern: "^\\d{2}-\\d{6}$" },
    ],
  },
  {
    id: "termCode",
    title: "Term code",
    size: 110,
    validators: [{ type: "required" }],
  },
  {
    id: "courseCode",
    title: "Course code",
    size: 140,
    validators: [{ type: "required" }],
  },
  {
    id: "section",
    title: "Section",
    size: 100,
    validators: [{ type: "required" }],
  },
  {
    id: "credits",
    title: "Credits",
    size: 110,
    editor: { type: "number" },
    validators: [
      { type: "number", min: 0, max: 12, decimalPlaces: 1 },
    ],
  },
  {
    id: "status",
    title: "Status",
    size: 140,
    editor: { type: "select", options: STATUSES, enableCustomValue: false },
    validators: [{ type: "oneOf", values: STATUSES }],
  },
  {
    id: "enrolledOn",
    title: "Enrolled on",
    size: 140,
    editor: { type: "date" },
    validators: [{ type: "date" }],
  },
  {
    id: "campus",
    title: "Campus",
    size: 180,
  },
];

Each editor earns its place against what arrives. The date editor turns 02.09.2026 into 2026-09-02. The scan settles the file's date order on the first value carrying a part above 12, which is the 24 in 24/08/2026. The number editor swaps the comma for a dot, so 3,5 lands as 3.5. One comma decimal and no dot decimal decide the whole file. The select editor with enableCustomValue off holds the status column to three options, and a value nobody maps is dropped from the row.

The mount ties the rest together.

<DataEditor<Enrolment>
  apiKey="your-license-key"
  open={open}
  onClose={closeEditor}
  columns={columns}
  primaryKey={["studentRef", "termCode", "courseCode", "section"]}
  enableDeleteRow="all"
  blockSubmitOnError
  synonyms={{
    columns: { section: ["sec", "sect", "class section"] },
    values: { Enrolled: ["enr", "registered", "active"] },
  }}
  loadData={async (onChunk) => {
    const response = await fetch("/api/enrolment/current");
    const body = await response.json();
    if (!response.ok) throw new Error(body.message);
    onChunk(body.rows, { source: "Redshift", done: true });
  }}
  onComplete={onComplete}
/>

Sec reaches section through the synonyms table and through nothing else. Fuzzy matching scores it at zero. Three characters fall under the four-character floor the contains tier needs, sec shares no whole word with section, and the length gap of four exceeds the two edits allowed at that length. A match needs sixty. The same table carries enr and registered on the value side, where both stand too far from Enrolled to reach it alone. Whatever the person fixes by hand comes back on the result as learnedSynonyms, ready to store and feed back next time.

primaryKey takes four columns, because one student takes several courses in one term. The warehouse key has a fifth part, and institution_id never reaches the browser at all. It comes off the session on your server. enableDeleteRow="all" lets the person remove a row that the registrar dropped, and blockSubmitOnError keeps submit disabled while any row carries an error.

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

The rows already in the warehouse

loadData runs once when the editor opens, and it fills the grid with what Redshift holds today.

import {
  RedshiftDataClient,
  ExecuteStatementCommand,
  DescribeStatementCommand,
  GetStatementResultCommand,
} from "@aws-sdk/client-redshift-data";

const redshift = new RedshiftDataClient({});
const TARGET = { WorkgroupName: "analytics", Database: "dev" };

const CURRENT_SQL =
  "select student_ref, term_code, course_code, section, " +
  "credits, status, enrolled_on, campus " +
  "from analytics.enrolment " +
  "where institution_id = :institution_id " +
  "order by student_ref, term_code, course_code, section";

const waitFor = async (id) => {
  for (;;) {
    const state = await redshift.send(
      new DescribeStatementCommand({ Id: id, WaitTimeSeconds: 30 }),
    );
    if (state.Status === "FINISHED") return state;
    if (state.Status === "FAILED" || state.Status === "ABORTED") {
      const failed = state.SubStatements?.find((sub) => sub.Error);
      throw new Error(failed?.Error ?? state.Error ?? "Statement failed");
    }
  }
};

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

  const started = await redshift.send(
    new ExecuteStatementCommand({
      ...TARGET,
      Sql: CURRENT_SQL,
      Parameters: [
        { name: "institution_id", value: session.institutionId },
      ],
    }),
  );

  await waitFor(started.Id);

  const rows = [];
  let token;
  do {
    const page = await redshift.send(
      new GetStatementResultCommand({ Id: started.Id, NextToken: token }),
    );
    rows.push(...page.Records.map(toEnrolmentRow));
    token = page.NextToken;
  } while (token);

  response.json({ rows });
});

The Data API needs no driver, no connection pool and no password in the call. It reaches a provisioned cluster or a Serverless workgroup over HTTPS, and it authenticates through Secrets Manager, through IAM Identity Center, or through temporary credentials derived from the caller's own IAM identity. Every call is asynchronous, so a statement comes back as an id and DescribeStatement reports on it. WaitTimeSeconds holds that poll open for up to 30 seconds, which keeps the loop small.

Results live for 24 hours and cap at 500 MB after compression, and GetStatementResult pages through them. Each record arrives as typed fields with column metadata beside it, and a DECIMAL comes back as a string, which is the shape the editor wants anyway.

The person now sees the term as the warehouse has it. The registrar's file lands on top of it in the next step, and every row that matches on the four key columns merges into the row already there.

The result on submit

On submit, Updog Importer hands your handler every row grouped by source, each one carrying isNew, isChanged, isDeleted and isValid. The flags are independent, so one row can be new, changed and deleted at once. Rows that merged onto warehouse rows arrive under the Redshift source as changed. Rows the file added arrive under the file's own name as new. A warehouse row nobody touched never appears.

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

const CHUNK_SIZE = 2000;

const route = (entry: ResultRow<Enrolment>, sourceFile: string) => {
  if (entry.isDeleted) {
    if (entry.isNew) return [];
    return [{ op: "delete", sourceFile, ...entry.row }];
  }
  if (entry.isNew) return [{ op: "insert", sourceFile, ...entry.row }];
  if (entry.isChanged) return [{ op: "update", sourceFile, ...entry.row }];
  return [];
};

const onComplete = useCallback(async (result: DataEditorResult<Enrolment>) => {
  const rows = result.sources.flatMap((source) => {
    return source.rows.flatMap((entry) => route(entry, source.sourceName));
  });

  const batchId = crypto.randomUUID();

  for (let start = 0; start < rows.length; start += CHUNK_SIZE) {
    const staged = await fetch("/api/enrolment/stage", {
      method: "POST",
      headers: { "Content-Type": "application/json" },
      body: JSON.stringify({
        batchId,
        offset: start,
        rows: rows.slice(start, start + CHUNK_SIZE),
      }),
    });
    if (!staged.ok) throw new Error((await staged.json()).message);
  }

  const merged = await fetch("/api/enrolment/merge", {
    method: "POST",
    headers: { "Content-Type": "application/json" },
    body: JSON.stringify({ batchId }),
  });
  if (!merged.ok) throw new Error((await merged.json()).message);
}, []);

That handler gives each row one verb before it leaves the browser. A row the person added and then deleted goes nowhere. Since blockSubmitOnError holds submit until every error is gone, the routing runs on the three change flags alone.

No Redshift number caps the rows in a chunk, because the rows travel to S3 as an object and never sit inside the SQL text. The numbers that do bind sit further along. One BatchExecuteStatement carries at most 40 statements, and this import uses four. The other ceiling is whatever your own endpoint accepts as a request body, so the chunk size above is a starting point to check against your own runtime.

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 import, and the term clears the grid unwritten. A thrown error keeps the grid as it stands, with every match and hand correction on it. The person submits again on rows that never left the screen.

The staging posts and the merge all run behind a spinner in the confirm dialog. Anything on the result worth keeping gets copied inside the handler, since the editor drops its rows, its sources, its history and its learned synonyms once the promise resolves.

The route that stages the batch

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

import { S3Client, PutObjectCommand } from "@aws-sdk/client-s3";
import { BatchExecuteStatementCommand } from "@aws-sdk/client-redshift-data";
import { randomUUID } from "node:crypto";

const s3 = new S3Client({});
const BUCKET = "your-import-bucket";
const UUID = /^[0-9a-f]{8}-[0-9a-f]{4}-[0-9a-f]{4}-[0-9a-f]{4}-[0-9a-f]{12}$/;

app.post("/api/enrolment/stage", async (request, response) => {
  const session = await getVerifiedSession(request);
  if (!session) return response.status(401).json({ message: "Not signed in" });
  if (!UUID.test(request.body.batchId)) {
    return response.status(400).json({ message: "Bad batch id" });
  }

  const csv = request.body.rows
    .map((row, index) =>
      csvLine([
        request.body.offset + index,
        row.op,
        session.institutionId,
        row.studentRef,
        row.termCode,
        row.courseCode,
        row.section,
        row.credits,
        row.status,
        row.enrolledOn,
        row.campus,
        row.sourceFile,
      ]),
    )
    .join("\n");

  const key =
    "enrolment/" + session.institutionId +
    "/" + request.body.batchId +
    "/" + randomUUID() + ".csv";

  await s3.send(
    new PutObjectCommand({
      Bucket: BUCKET,
      Key: key,
      Body: csv,
      ContentType: "text/csv",
    }),
  );

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

app.post("/api/enrolment/merge", async (request, response) => {
  const session = await getVerifiedSession(request);
  if (!session) return response.status(401).json({ message: "Not signed in" });
  if (!UUID.test(request.body.batchId)) {
    return response.status(400).json({ message: "Bad batch id" });
  }

  const prefix =
    "s3://" + BUCKET +
    "/enrolment/" + session.institutionId +
    "/" + request.body.batchId + "/";

  const started = await redshift.send(
    new BatchExecuteStatementCommand({
      ...TARGET,
      Sqls: [CREATE_STAGE, copyFrom(prefix), MERGE_BATCH, DELETE_BATCH],
      Parameters: [
        { name: "institution_id", value: session.institutionId },
      ],
    }),
  );

  const finished = await waitFor(started.Id);
  response.json({ statements: finished.SubStatements.length });
});

getVerifiedSession() stands in for your own server-side authentication check. institutionId comes off that session and never off the request body, so a guessed batch id reaches no other tenant's prefix. The batch id is checked against a UUID shape before it goes anywhere near an S3 key. line_no carries the row's position in the whole import, which is what lets the merge keep the last copy of a repeated key.

Each chunk lands as its own object under one prefix. AWS loads every file under a key prefix in a single COPY and splits the work across slices, and it warns that several concurrent COPY commands into one table force a serialized load instead. So one COPY reads the whole batch.

The credential is the IAM role attached to the service. The COPY itself takes iam_role default, which uses the role set as default on the cluster, and AWS calls the access key alternative "not recommended". The bucket has to sit in the same Region as the cluster unless the COPY names a REGION.

The statements that land the import

Four statements go out in one BatchExecuteStatement, and AWS runs them serially in array order. The default ExecutionMode is TRANSACTION, so the four commit together and a failure anywhere rolls all of them back.

-- 1. the staging table, gone when the session ends
create temporary table stage_enrolment (
  line_no        integer      not null,
  op             varchar(8)   not null,
  institution_id varchar(36)  not null,
  student_ref    varchar(16)  not null,
  term_code      varchar(8)   not null,
  course_code    varchar(16)  not null,
  section        varchar(8)   not null,
  credits        decimal(4,1),
  status         varchar(16),
  enrolled_on    date,
  campus         varchar(64),
  source_file    varchar(256)
)
distkey (student_ref);

-- 2. every object under the batch prefix, loaded in parallel
copy stage_enrolment
from 's3://your-import-bucket/enrolment/inst-4471/9f2c.../'
iam_role default
format as csv
emptyasnull
blanksasnull;

-- 3. the inserts and the updates
merge into analytics.enrolment as t
using (
  select institution_id, student_ref, term_code, course_code, section,
         credits, status, enrolled_on, campus, source_file
  from (
    select s.*, row_number() over (
      partition by student_ref, term_code, course_code, section
      order by line_no desc
    ) as rn
    from stage_enrolment s
    where s.op <> 'delete'
      and s.institution_id = :institution_id
  ) as ranked
  where rn = 1
) as s
on  t.institution_id = s.institution_id
and t.student_ref    = s.student_ref
and t.term_code      = s.term_code
and t.course_code    = s.course_code
and t.section        = s.section
when matched then update set
  credits     = s.credits,
  status      = s.status,
  enrolled_on = s.enrolled_on,
  campus      = s.campus,
  source_file = s.source_file,
  updated_at  = getdate()
when not matched then insert (
  institution_id, student_ref, term_code, course_code, section,
  credits, status, enrolled_on, campus, source_file, updated_at
)
values (
  s.institution_id, s.student_ref, s.term_code, s.course_code, s.section,
  s.credits, s.status, s.enrolled_on, s.campus, s.source_file, getdate()
);

-- 4. the rows the person deleted
delete from analytics.enrolment
using stage_enrolment s
where s.op = 'delete'
  and s.institution_id = :institution_id
  and analytics.enrolment.institution_id = s.institution_id
  and analytics.enrolment.student_ref    = s.student_ref
  and analytics.enrolment.term_code      = s.term_code
  and analytics.enrolment.course_code    = s.course_code
  and analytics.enrolment.section        = s.section;

The staging table is temporary, and the Data API usually ends the session when the SQL finishes, so it has to be created and used inside the same batch. Session reuse through SessionKeepAliveSeconds is the other way, and it holds a session for up to 24 hours. The staging table takes the target's distribution key, which AWS says can improve a merge over a large source.

COPY carries two conversion options that match what the file did. emptyasnull and blanksasnull turn an empty character cell and a whitespace-only one into null, which is what a missing campus or status means here. The dates need no option, since COPY reads YYYY-MM-DD by default and that is the shape the editor produced.

The MERGE deduplicates in its USING subquery, and that is load-bearing. AWS writes that a row in the target may match only one row in the source, and its own example ends ERROR: Found multiple matches to update the same tuple. The registrar's file sent BIOL 1010 twice, and Updog leaves both rows on screen because two rows from one file never merge into each other. row_number() keeps the highest line number per key, so the second copy wins and the statement survives.

The delete runs as DELETE ... USING, the join form AWS documents on DELETE for naming a second table in the WHERE condition. Both statements filter on the institution from the session, so a batch can only touch the tenant that posted it.

The report on a failed batch

DescribeStatement answers with a status of SUBMITTED, PICKED, STARTED, FINISHED, ABORTED or FAILED. For a batch it also answers with SubStatements, one entry per statement, each with its own Error, Duration and ResultRows. The id of the failing one ends in a suffix, so :2 is the second statement in the array, which is the COPY. ResultRows on the merge is the number of rows it affected.

A COPY that fails on the data writes the detail to SYS_LOAD_ERROR_DETAIL, one row per COPY, with the file name, the line number, the column and the message. STL_LOAD_ERRORS holds the same kind of record and covers main provisioned clusters alone, so a Serverless workgroup reports through the SYS view. The whole transaction rolls back, the staging table goes with the session, and the objects still sit under the batch prefix for the retry.

Throttling arrives as a ThrottlingException with a 400. The Data API allows 30 ExecuteStatement calls a second, 20 BatchExecuteStatement calls, and 100 DescribeStatement calls, and AWS says the retry strategy runs automatically for throttling errors in some of its SDKs.

The routes nobody ships for you

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

Redshift already ships its own ways in for the other case. Query editor v2 takes a local file up to 100 MB and generates the COPY. The same wizard loads from an S3 bucket. COPY reads every object under a bucket prefix in one statement. An S3 event integration runs the COPY for every new file under a prefix, up to 200 jobs per cluster or workgroup. Every one of those needs an AWS seat with the cluster'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 registrar 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 one table, a schema with eight columns, a read route, a handler that gives each row a verb, two write routes, and four statements that commit together. The file stays on the machine that opened it. The rows travel from your own front end to your own routes, into your own bucket, and from there into Redshift, 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 registrar will send the term again once add and drop closes, with two sections cancelled and a handful of students moved. That time the mappings are already stored, the editor opens on the rows that landed last time, and the person sees the difference before anybody writes it down.