How to move CSV data into a SQL table and know the transfer was correct

Decide what correct means before you move a row

A transfer is correct when three things hold. Every source row you meant to move is in the target. No row is in the target twice. The values that arrived are the values that left, after the transformations you wrote down and nothing else. Each of those needs its own check, and none of them can be read off a run that simply finished without an error.

Write the checks down before the first run, next to the mapping. If you cannot say which query will prove a row arrived, you will end up proving it by scrolling through the table, which is a way to feel confident rather than a way to be right.

• Mapping. Which source column feeds which target column, and what happens to it on the way.
• Key. Which column, or set of columns, makes a row unique in the target.
• Evidence. The count and checksum queries you will run on both sides, and who reads them.

Map the columns and check the types in one step

The Nightly CSV feed to SQL template downloads an export over HTTP, parses it with the header row, and hands the rows to the Process engine. That step maps source columns to target fields and applies rules to them. A rule set to autofix corrects the value and carries on. A rule set to error stops the run on a bad row instead of writing it.

Here is what the template’s mapping does to one synthetic row. The email is trimmed, lowercased and validated. The name is trimmed and put into title case. A literal source tag is added so you can find every row this feed wrote.

source CSV (header row, then one record)
  email,full_name
  "  Test.Customer@Example.COM ","test customer"

after the Process step
  email      test.customer@example.com  trim, lowercase, validate_email (error)
  fullName   Test Customer              trim, title case
  source     nightly-csv                literal

upsert into customers, keyed on email

Type checks belong here, not in the database. A target column that expects a date will reject a malformed one, but it will do so on the write, one row at a time, after half the file is already in. A rule on the Process step fails the run before anything is written, and the failed step is kept with its input so you can see which row it was.

Unique keys, batches and retries

The upsert is keyed on the columns you choose; in the template that is email. Running the same export again updates the existing rows rather than adding copies. That property is what makes everything else in this post safe: a retry, a rerun after a fix, or a nightly sync while two systems run side by side all land on the same rows.

Check the key before you write, not after. If the source contains two rows with the same key, an upsert will quietly keep the last one, and your counts will disagree for a reason that has nothing to do with the transfer. A dedupe step ahead of the write surfaces those rows while you can still decide what to do about them.

• A For Each step works through the parsed rows, so a large export is written in pieces rather than as one call.
• Each step has its own retries and timeout. A write that fails on a network blip is tried again; one that still fails is kept with its input and error.
• Because the write is keyed, a retry cannot create a duplicate. Without a key, retries and reruns are how duplicates are made.

Compare counts and checksums on both sides

After the batches finish, run the same two figures against the source and the target. A count answers whether the right number of rows arrived. A checksum answers whether they arrived intact: an aggregate hash over the key and the columns you care about, computed the same way on both sides. Counts catch missing and extra rows. Checksums catch the row that arrived with the wrong value.

reconcile   (worked design)

              source           target
  count       rows exported    rows with source = nightly-csv
  checksum    hash over key    hash over key
              and columns      and columns

  both match  → report the run
  any differ  → owner decides: accept, fix and rerun, or roll back

On OCNORA these are ordinary SQL query steps, so their results sit on the run beside the writes and the comparison is on the record. A query step fails rather than return more rows than its limit, which is a useful constraint here: write the checks as aggregates, not as full reads you compare row by row. Compare the checksum over the normalised values, or the source side will differ for every row the mapping changed.

Rollback is a decision someone owns

OCNORA does not undo writes on its own. When the totals differ, a branch sends the run to an approval step, and a named owner decides what happens next: accept the difference with a reason, fix the mapping and rerun, or roll back. The decision is recorded with the run, alongside the figures that prompted it.

Rolling back means your own backup, snapshot or reverse script, run by the owner who decided it. Take the snapshot before the first run, not after the first surprise. Because every row this feed writes carries its source tag and its key, a reverse script can be scoped to exactly those rows rather than to the whole table.

Prerequisites, limitations and a next step

• Prerequisites: a CSV export with a header row at a URL the workflow can reach; a MySQL or MariaDB connection for the target table, with the table already created, because SQL steps refuse DDL; a written column mapping, the types each target column accepts and the key that makes a row unique; the count and checksum queries for both sides; a named owner for rollback and a snapshot of the target taken before the first run.

• This is a set of workflow steps you assemble and test, not a migration engine. Schema design, cleansing rules and cut-over planning stay with your team.
• The template’s upsert writes one row per call to MySQL or MariaDB. Other targets, including Postgres, are written as SQL in a query step.
• Count and checksum reconciliation and the rollback step are a worked design until you have tested them on your own data.
• We publish no volume, speed or accuracy figure for migrations.

The data migration solution lays out the whole workflow, from the download to the owner’s decision. If you want to walk through it against your own export, book a demo: we start from the CSV-to-SQL template, write the mapping and the reconciliation queries together, and read the first run’s record with you.