Designing a Robust CSV Import Workflow in DBeaver: Data Integrity, Performance, and Error Handling

Requirements

When importing bulk data into a relational database via DBeaver, the primary goals are:

• Maintain data integrity – enforce constraints and avoid partial inserts.
• Maximize performance – handle millions of rows without exhausting JVM memory.
• Provide clear diagnostics – pinpoint failing rows and root causes.
• Ensure operational safety – allow users to cancel or preview before committing.

Assumptions:

• DBeaver 23.x or newer (JDBC‑based import).
• Target database supports JDBC batch operations (e.g., PostgreSQL, MySQL, Oracle).
• CSV uses UTF‑8 encoding unless otherwise specified.

Smallest Suitable Design

The import process can be modeled as a single, deterministic pipeline:

1. Read CSV file line‑by‑line (streaming).
2. Map header names to table columns (auto or manual).
3. Batch rows into JDBC statements (configurable batch size).
4. Execute batch within one JDBC transaction.
5. Commit if all rows succeed; otherwise roll back the entire transaction.

Key design choices:

• Streaming reduces JVM heap usage by not loading the entire file into memory.
• Batch size balances transaction duration and network round‑trips.
• Full‑transaction import guarantees atomicity – no partial data.

Trust & Data Boundaries

Data boundaries are defined by the table schema and database constraints. DBeaver’s import honors:

• NOT NULL, UNIQUE, PRIMARY KEY, and other constraints.
• Foreign key relationships – violating a FK will abort the batch.
• Triggers and cascading actions – any exception in a trigger causes a rollback.
• Connection profile isolation level – influences visibility of concurrent writes.

Because the import runs in a single transaction, any constraint violation or trigger error will roll back every row in that batch, potentially rolling back millions of rows if the batch size is large.

Operational Checks

Before committing the import, perform these checks:

1. Preview: In the wizard, click Preview to view the first 10 rows and verify mapping.
2. Validation: DBeaver performs a pre‑check for column count and delimiter consistency. Errors stop the wizard.
3. Permissions: Ensure the JDBC user has INSERT rights on the target table and any referenced tables.
4. Encoding: Explicitly set the CSV encoding if not UTF‑8.
5. Batch size: For very large files, set a smaller batch (e.g., 100–500 rows) to reduce transaction time and memory.
6. Streaming: Enable Use streaming to read the file incrementally.
7. Isolation level: In Database → Connection Settings → Transaction Isolation, choose READ COMMITTED for typical loads.

Failure Modes & Conditions to Change Design

Typical failure scenarios and mitigations:

Failure	Cause	Mitigation
Duplicate key violation	Row violates UNIQUE/PRIMARY KEY	Define unique constraint; pre‑filter duplicates in CSV; use ON CONFLICT DO NOTHING if DB supports.
Trigger exception	Trigger logic fails	Test trigger logic separately; use smaller batch to isolate problematic rows.
Out‑of‑Memory	Large file, streaming disabled	Enable streaming; increase JVM heap via -Xmx if necessary.
Permission denied	No INSERT rights	Grant rights or use a different user.
Encoding mismatch	Non‑UTF‑8 file	Specify encoding in wizard.
Read‑only database	Write operations blocked	Verify write permissions; use a staging schema.
Large batch rollback	Constraint violation in middle of batch	Reduce batch size; enable Commit after each batch if supported.

When any of these conditions arise, consider switching to a streaming, small‑batch approach or using a database‑native bulk loader (e.g., PostgreSQL COPY) for better control.

Concrete Example

Below is a step‑by‑step example that demonstrates a robust import workflow, including a deliberate duplicate to trigger rollback.

1. Create Test Table

// PostgreSQL example
CREATE TABLE customers (
  id          BIGSERIAL PRIMARY KEY,
  email       VARCHAR(255) NOT NULL UNIQUE,
  name        VARCHAR(100),
  signup_date DATE
);

Note the UNIQUE constraint on email; this will be the source of the rollback.

2. Prepare CSV File

email,name,signup_date
[contact removed],Alice,2024-01-01
[contact removed],Bob,2024-02-15
[contact removed],Duplicate Alice,2024-03-20

The third row duplicates the first email, violating the unique constraint.

3. Import via DBeaver

1. Open the database connection and right‑click the customers table → Import Data.
2. Select the CSV file and click Next.
3. In the Mapping tab, verify that columns map correctly. DBeaver auto‑maps by header name.
4. Switch to the Advanced tab:
• Check Use streaming to read the file line‑by‑line.
• Set Batch size to 100.
• Leave Commit after each batch unchecked (full transaction).
• Set Encoding to UTF‑8.
• Optionally, set Transaction isolation level to READ COMMITTED.
5. Click Finish to start the import.

During import, DBeaver will report: “Rows processed: 3; Errors: 1; Rollback initiated.” The error log will contain a line similar to:

Line 3: duplicate key value violates unique constraint "customers_email_key"

After the import completes, run:

SELECT COUNT(*) FROM customers;

Result: 0 rows – the entire batch rolled back due to the duplicate.

4. Verify Performance on Large File

To test streaming and batch size, generate a CSV with 1,000,000 rows (e.g., using generate_series in PostgreSQL or a CSV generator). Import with streaming enabled and batch size 500. Monitor JVM memory via jcmd VM.native_memory summary or OS tools. Expect memory usage to stay below the configured -Xmx limit.

5. Optional: Incremental Import Using COPY

For very large datasets, consider bypassing DBeaver’s import wizard and using the database’s native bulk loader (e.g., PostgreSQL COPY). This yields lower memory consumption and faster load times. However, it sacrifices DBeaver’s GUI diagnostics.

Practical Verification Steps

• After import, query the target table to confirm row count matches expected.
• Check the DBeaver Import Summary panel for error counts and line numbers.
• Review the Error Log in DBeaver’s Window → Show View → Error Log for detailed stack traces.
• Use SELECT * FROM customers WHERE email = '[contact removed]'; to ensure no duplicate rows exist.
• Run SELECT pg_stat_activity; (or equivalent) to verify that no long‑running transaction remains after a rollback.

Limitations & Caveats

• DBeaver’s import does not perform incremental deduplication; the database must enforce uniqueness.
• Large batch sizes can cause long transaction times and increased risk of rollback.
• Streaming is not available for all JDBC drivers; verify driver support.
• Trigger logic may override expected behavior; test triggers separately.
• Read‑only or replicated databases may silently ignore writes; always confirm write permissions.

By following the design principles above, you can build a CSV import workflow in DBeaver that balances performance with data integrity and provides clear diagnostics for troubleshooting.