What you will learn
This small-data tutorial uses cqlsh COPY and psql \copy. It focuses on reconciliation and rerun behavior.
Before you begin
Prepare Cassandra and PostgreSQL lab connections and a private working directory. The example source is tutorial.events_by_id(id int PRIMARY KEY, message text). Create a few synthetic rows, including a comma, a quote, and a non-ASCII character. Quiesce the source for a consistent comparison.
1. Write the column contract
Map Cassandra int to PostgreSQL integer and text to text for this example. Preserve identifiers that may contain leading zeros as text. Decide how to represent missing values, timestamps, collections, and expiration before exporting. A relational uniqueness constraint must reflect the complete source key.
2. Export a small, explicit projection
COPY tutorial.events_by_id (id,message) TO '/srv/migration/events.csv' WITH HEADER=TRUE;
This command writes on the cqlsh client machine. Use DSBulk and a reviewed mapping for larger datasets; do not infer CSV record count by counting physical lines because fields can contain newlines.
3. Load an isolated staging table
CREATE TABLE staging_events (id integer, message text);
\copy staging_events (id,message) FROM '/srv/migration/events.csv' WITH (FORMAT csv, HEADER true);
SELECT id, count(*) FROM staging_events GROUP BY id HAVING count(*) > 1;
SELECT count(*) FROM staging_events WHERE id IS NULL;
An empty duplicate/null-key result is a gate before merge. Verify the file’s actual delimiter, quoting and null convention. psql \copy reads from the client, unlike server-side COPY.
4. Compare before merging
Load the new batch into a fresh staging table and compare it with the previously accepted target by primary key. Identify inserts, changed values, and target-only rows separately. Target-only rows are not automatically deletions; establish the authoritative snapshot before removing anything.
CREATE TABLE IF NOT EXISTS events (id integer PRIMARY KEY, message text);
BEGIN;
INSERT INTO events SELECT id,message FROM staging_events
ON CONFLICT (id) DO UPDATE SET message=EXCLUDED.message;
COMMIT;
This upsert does not delete target-only rows. Use it only after the staging checks pass and the target write policy is established.
Example output
The following is an illustrative, normalized lab result, not output captured from a live customer system. Your versions, addresses, timings, and row counts will differ.
-- Duplicate-key check on the PostgreSQL staging table
id | count
----+-------
(0 rows)
-- Null-key check
count
-------
0
(1 row)
These two checks should return no duplicate groups and zero null IDs before the merge. They do not validate every message value or prove that the export is complete.
Verify the result
Compare keys and values, not just totals. Verify quoted text, Unicode, null handling, and any timezone conversions. Run the service’s relational queries and inspect execution plans after indexing and ANALYZE. Keep the conversion contract with the export.
Troubleshooting
A COPY parse error commonly means delimiter, quoting, or encoding disagreement. Unique violations indicate duplicate keys or an incomplete key mapping. Do not strip malformed rows silently: quarantine them and reconcile the rejected set.
Recovery and next steps
A failed staging load leaves the accepted target untouched. Before commit, ROLLBACK discards merge changes. After committed application writes on the target, reverting requires a data reconciliation plan. Keep the original export immutable for diagnosis.