Overview

Append-only is an optimization mode for data models where your records are insert-only with no updates after initial write. With this guarantee, Prequel narrows the scope of work for each transfer and optimizes table layout in supported destinations, improving performance during transfer.

Use append-only mode when your upstream system only appends new events/rows. Append-only is configured on one or more data models you maintain in Prequel.

📘

Support

Append-only mode is supported for the following destinations: BigQuery, Snowflake, Redshift, Databricks, Delta Lake, and Athena. Object storage (S3, GCS, ABS) and SFTP behave as insert-only by default, where files are written and no merge operations are performed.

How it works

• Prequel uses your model’s primary key and last-modified column to incrementally upsert new data.
• With append-only mode enabled, Prequel makes an assumption that no existing rows with timestamps prior to the latest updated_at value have been updated. This reduces the search space (by partitions) from which Prequel must locate and update rows in the destination. This improves write speed to the destination.
• In supported destinations, Prequel also finetunes clustering/partitioning more narrowly to align storage layout with recent data and query patterns. For more detail on clustering and partitioning support by destination type, contact the Prequel team

Configuration

Enable append-only mode per model by setting is_append_only to true on model with a PATCH model request (API reference).

Switching any model to append-only mode requires a full refresh transfer for the change to take. See Full Refresh Transfers for more.

Append-only behavior & interactions

• Transfers remain upserts; new rows will be inserted and existing rows (e.g., prior rows included in a transfer's lookback window) are merged based on primary key.
• Transfers that move records already present in the destination (e.g., a transfer re-triggered with a custom window, or repeated rows resulting from the lookback window), do not create duplicates in warehouse or database destinations. Rows will be MERGED into the destination as long as the row's updated_at timestamp has not been modified.
• Append-only works with source queries, multi-tenant filtering, and windowed transfers.

Considerations

• Ensure that your records are not modified, including any enrichment jobs after landing in the source that Prequel points to.
• Use your Prequel staging environment to test, validate performance, then migrate in production.

See also

• Writing config files
• Data models
• Destinations overview
• Windowed transfers
• Schema evolution