Skip to content

GatorSet write operations

  • append: Use when adding new rows and duplicates should be avoided by PK/UNIQUE checks.
  • overwrite: Use when replacing entire table content; quickest path when you can tolerate full table replacement and object recreation as handled by engine. Note: BackUp if rollback is required.
  • truncate: Use when preserving table and metadata but clearing and refilling data. Good for large reloads where you want stable object definitions. Requires DELETE permissions and temp table write.
  • update: Use when you need to replace a subset of rows matching a filter (WHERE or filter groups). The engine deletes matching rows from target, loads temp, and copies back. Ensure filters match key semantics to avoid accidental removal.

Append Mode

What Happens

  • New records are added to the existing dataset/table.
  • Existing rows are not removed or updated.
  • It prevents insertion of duplicate values when a primary key or unique constraint exists.
  • If no key or de-duplication logic is available, duplicates will be appended freely.

When to Use

  • For incremental loads where new rows periodically arrive.
  • For event-style or immutable log data, where history must remain untouched.
  • For targets that enforce uniqueness with PK/unique constraints, or where de-duplication is handled upstream.

Update Mode

What Happens

  • A subset of existing rows is removed or replaced based on a filter or key match, then new rows are inserted.
  • Often done via DELETE FROM target WHERE key IN (incoming keys.
  • This approach limits table-wide churn, affecting only the rows that match the filter or key set.
  • Often implemented using a temporary staging table:
  • Insert incoming rows into temp table.
  • Delete matching rows from the target table.
  • Insert new rows from temp table into the target.
  • Drop temp table.

When to Use

  • Incremental loads where only a specific set of rows need refresh (e.g., last N days, specific IDs).
  • Corrections for a subset of data where deterministic replacement is required.
  • Targets where you need consistent replacement without truncating the entire table.

Overwrite Mode

What Happens

  • The target dataset/table is completely replaced with the new dataset.
  • Databases: Content is replaced — this may involve:
  • Dropping and recreating the table, or
  • Using an overwrite write mode.
  • Identity/sequence columns and constraints are preserved according to the database’s capabilities.
  • Locking may occur during table swap or delete operations.

When to Use

  • Full refresh loads where the target should exactly match a new snapshot.
  • Situations requiring JDBC overwrite semantics (driver-specific behaviors).

Back up or snapshot before overwrite if rollback is required.

Truncate Mode

What Happens

  • Existing rows in the target are removed, then the incoming dataset is loaded into the same structure.
  • Implementation often uses a temporary/staging table:
  • Write new data to a temp table.
  • Delete or swap data in the base table.
  • Drop temp table.
  • Table structure, DDL, indexes, and privileges remain intact.

When to Use

  • Regular full refreshes where table structure must remain unchanged.
  • Large datasets where append or update operations would be inefficient.

Notes:

  • Views: When a job reads from a source view and writes to a relational target, the engine write the data into a physical table in the target (not a view). The table name follows the configured target object naming; creating a target view is not currently supported by write modes.