Understanding CopyFaultTolerantExpressions in Vertica

Vertica offers powerful configuration options to control data loading behavior, especially when handling imperfect input data. One such option is the CopyFaultTolerantExpressions parameter - a Boolean general configuration that governs how COPY handles transformation errors during data load.

📌 What Is CopyFaultTolerantExpressions? 

CopyFaultTolerantExpressions controls whether a COPY statement continues processing when transformation expressions (e.g., CAST, derived columns) fail for individual rows.

• Default: 0 (false) — abort on any transformation error.
• When set to 1 (true): COPY will skip rows with transformation errors and continue loading the rest.

 You can inspect this setting at various configuration levels by querying:

SELECT node_name, parameter_name, current_value, default_value, change_requires_restart
FROM vs_configuration_parameters
WHERE parameter_name ILIKE '%CopyFaultTolerantExpressions%';

The parameter is dynamic (no restart required) and configurable at node, session, user, or database level.

🚧 Why Does This Matter?

When loading semi-structured or inconsistent data, even a single bad transformation (like converting a string to an integer) can halt the entire COPY. With CopyFaultTolerantExpressions = 1, the system can instead skip malformed rows, improving fault tolerance and ensuring large loads don't fail due to minor issues.

🧪 Test Walkthrough with Example

🔹 1. Default Behavior (CopyFaultTolerantExpressions = 0)

SELECT SET_CONFIG_PARAMETER('CopyFaultTolerantExpressions', 0);

Sample data load:
1,new_one,1.1
e,new_two,2.1
3,new_three,3.1

Only the rows with valid integer values for f1 are inserted. The row with "e" is rejected, but the load succeeds:

SELECT * FROM my_fact_table;

-- Returns:
-- 1 | new_one   | 1.1
-- 3 | new_three | 3.1

But if a transformation like CAST(fx AS INT) fails:
COPY my_fact_table (fx FILLER VARCHAR(10), f1 AS CAST(fx AS INT),   f2,   f3) FROM STDIN DELIMITER ',';
1,new_one,1.1
bad,new_two,2.2
3,new_three,3.3

Result: Entire load fails due to casting "bad" to INT.

🔹 2. Tolerant Mode (CopyFaultTolerantExpressions = 1)
SELECT SET_CONFIG_PARAMETER('CopyFaultTolerantExpressions', 1);

Using the same transformation COPY as above:
Input:
1,new_one,1.1
bad,new_two,2.2
3,new_three,3.3

Result:
SELECT * FROM my_fact_table;

-- Returns:
-- 1 | new_one   | 1.1
-- 3 | new_three | 3.3

Only valid rows are loaded; "bad" is skipped, and COPY completes successfully. 

🧪 Behavior Summary with Decision Table

Below is a concise decision table summarizing what happens under different conditions: