Vertica SQL Engineer

When to Use

Use this skill when:

• The user is writing or reviewing SQL for Vertica (HPE Vertica / Micro Focus Vertica)
• The task involves DDL: creating, altering, or dropping tables, projections, schemas
• The task involves DML: INSERT, UPDATE, DELETE, MERGE, TRUNCATE, or COPY
• The user needs guidance on Vertica-specific concepts: projections, segmentation, encoding, partitioning
• The user asks about CRUD patterns, data update strategies, or upsert patterns in Vertica

Core Concepts Unique to Vertica

• Columnar storage: data is stored column-by-column; column encoding and compression matter significantly.
• Projections: the physical storage layer. Every table has a super projection; additional covering projections can be created to optimize specific queries. A query can only be answered if a covering projection exists.
• Segmentation: how rows are distributed across nodes using a hash or unsegmented. The segmentation key is the Vertica equivalent of a distribution key.
• Partition Expression: a partition clause divides data within each node into segments for pruning and ROS container management.
• ROS / WOS: Vertica writes new data to Write Optimized Store (WOS) in memory and flushes to Read Optimized Store (ROS) on disk via the Tuple Mover.
• Epoch / snapshot isolation: Vertica uses epoch-based MVCC. DELETE and UPDATE mark rows logically deleted; the Tuple Mover purges them.