Vertica

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.

DDL: Schemas

CREATE SCHEMA IF NOT EXISTS marketing;
DROP SCHEMA marketing CASCADE;   -- drops all objects inside

DDL: CREATE TABLE

Minimal table

CREATE TABLE marketing.events (
    event_id    BIGINT       NOT NULL,
    user_id     BIGINT       NOT NULL,
    event_type  VARCHAR(64)  NOT NULL,
    amount      NUMERIC(18,2),
    event_ts    TIMESTAMP    NOT NULL,
    created_at  TIMESTAMP    DEFAULT NOW()
);

Full production table with segmentation and partitioning

CREATE TABLE dwh.fact_orders (
    order_id     BIGINT        NOT NULL,
    user_id      BIGINT        NOT NULL,
    order_date   DATE          NOT NULL,
    status       VARCHAR(32)   NOT NULL,
    amount       NUMERIC(18,2) NOT NULL,
    currency     CHAR(3)       NOT NULL DEFAULT 'USD',
    created_at   TIMESTAMP     NOT NULL DEFAULT NOW(),
    updated_at   TIMESTAMP
)
ORDER BY order_date, user_id       -- sort order within projection
SEGMENTED BY HASH(order_id) ALL NODES  -- distribution key
PARTITION BY order_date::DATE
    GROUP BY CALENDAR_HIERARCHY_DAY(order_date::DATE, 3, 12);

Key design rules:

• ORDER BY in CREATE TABLE sets the default sort order of the super projection; use columns that appear most in range predicates.
• SEGMENTED BY HASH(col) distributes rows across nodes; pick a high-cardinality column that appears in joins and group-bys.
• UNSEGMENTED ALL NODES for small dimension tables that are broadcast.
• PARTITION BY enables partition pruning and range-limited DELETE; keep partition granularity coarse enough to avoid excessive ROS containers (month or year is safer than day for very large tables).

LIKE — copy structure

CREATE TABLE staging.fact_orders_load
    LIKE dwh.fact_orders INCLUDING PROJECTIONS;

LIKE copies columns, constraints, and optionally projections. Use it for staging tables.

CREATE TABLE AS SELECT (CTAS)

CREATE TABLE tmp.monthly_revenue AS
SELECT
    DATE_TRUNC('month', order_date) AS month,
    currency,
    SUM(amount)                      AS revenue
FROM dwh.fact_orders
WHERE order_date >= DATE '2026-01-01'
GROUP BY 1, 2;

CTAS creates a table and populates it in one statement. The resulting table has no projections beyond the super projection unless you create them separately.

DDL: Data Types

Use BIGINT for IDs by default. Use NUMERIC (not FLOAT) for monetary values. Prefer TIMESTAMP over TIMESTAMPTZ unless time-zone awareness is explicitly required.

DDL: ALTER TABLE

Add a column

ALTER TABLE dwh.fact_orders ADD COLUMN region VARCHAR(64);
ALTER TABLE dwh.fact_orders ADD COLUMN is_refunded BOOLEAN DEFAULT FALSE NOT NULL;

After ADD COLUMN, new rows get the default. Existing rows see NULL unless a DEFAULT is specified and a NOT NULL constraint is added.

Drop a column

ALTER TABLE dwh.fact_orders DROP COLUMN region;
ALTER TABLE dwh.fact_orders DROP COLUMN region CASCADE; -- also drops dependent projections

Dropping a column that is part of a projection requires CASCADE or dropping the projection first.

Rename a column

ALTER TABLE dwh.fact_orders RENAME COLUMN region TO sales_region;

Modify column type or default

ALTER TABLE dwh.fact_orders ALTER COLUMN status SET DEFAULT 'pending';
ALTER TABLE dwh.fact_orders ALTER COLUMN status DROP DEFAULT;
ALTER TABLE dwh.fact_orders ALTER COLUMN status SET NOT NULL;
ALTER TABLE dwh.fact_orders ALTER COLUMN notes DROP NOT NULL;

Vertica does not support ALTER COLUMN ... TYPE for changing an existing column's data type in place — use a staged rename-and-recreate approach if needed.

Rename a table

ALTER TABLE staging.fact_orders_load RENAME TO fact_orders_v2;

Set partition expression

ALTER TABLE dwh.fact_orders
    PARTITION BY order_date::DATE
    GROUP BY CALENDAR_HIERARCHY_DAY(order_date::DATE, 3, 12);

DDL: DROP TABLE

DROP TABLE IF EXISTS staging.fact_orders_load;
DROP TABLE staging.fact_orders_load CASCADE; -- also drops dependent objects

DDL: Projections

Vertica projections are the physical storage objects. Every table gets a default super projection. Create additional projections to cover specific query patterns.

Create a covering projection

CREATE PROJECTION dwh.fact_orders_by_user (
    user_id,
    order_date,
    amount,
    status
)
AS
SELECT
    user_id,
    order_date,
    amount,
    status
FROM dwh.fact_orders
ORDER BY user_id, order_date
SEGMENTED BY HASH(user_id) ALL NODES;

After creating a projection, refresh it:

SELECT MAKE_AHM_NOW();
SELECT START_REFRESH();

Or wait for the Tuple Mover to populate it, or refresh manually:

SELECT REFRESH('dwh.fact_orders');

Drop a projection

DROP PROJECTION dwh.fact_orders_by_user;

DML: INSERT

Single-row insert

INSERT INTO dwh.fact_orders (order_id, user_id, order_date, status, amount, currency)
VALUES (1001, 42, DATE '2026-05-01', 'completed', 199.99, 'USD');

Multi-row insert

INSERT INTO dwh.fact_orders (order_id, user_id, order_date, status, amount, currency)
VALUES
    (1002, 43, DATE '2026-05-02', 'pending',   49.00, 'EUR'),
    (1003, 44, DATE '2026-05-02', 'completed', 99.50, 'USD');

INSERT ... SELECT

INSERT INTO dwh.fact_orders (order_id, user_id, order_date, status, amount, currency)
SELECT
    order_id,
    user_id,
    order_date::DATE,
    COALESCE(status, 'unknown'),
    amount,
    currency
FROM staging.fact_orders_load
WHERE amount > 0;

COPY — bulk load (preferred for large volumes)

COPY staging.fact_orders_load (order_id, user_id, order_date, status, amount, currency)
FROM '/data/orders/2026-05-01.csv'
DELIMITER ','
ENCLOSED BY '"'
SKIP 1
DIRECT
REJECTMAX 100
EXCEPTIONS '/tmp/load_exceptions.txt';

• DIRECT: bypasses WOS and writes directly to ROS — preferred for large loads.
• REJECTMAX n: allow at most n rejected rows before aborting.
• Use COPY FROM STDIN for programmatic loading from application code.

DML: UPDATE

UPDATE dwh.fact_orders
SET
    status     = 'refunded',
    updated_at = NOW()
WHERE order_id = 1001;

UPDATE with JOIN (using a subquery)

Vertica does not support UPDATE ... FROM ... JOIN syntax directly. Use a correlated subquery or a scalar subquery:

UPDATE dwh.fact_orders o
SET status = (
    SELECT r.new_status
    FROM staging.order_status_updates r
    WHERE r.order_id = o.order_id
)
WHERE EXISTS (
    SELECT 1
    FROM staging.order_status_updates r
    WHERE r.order_id = o.order_id
);

Performance note: large UPDATE operations are expensive in Vertica because they logically delete old rows and insert new ones. For bulk updates affecting a large fraction of a table, prefer MERGE or staging + truncate + re-insert.

DML: DELETE

Simple delete

DELETE FROM dwh.fact_orders
WHERE order_date < DATE '2023-01-01';

Partition-limited delete (fast path)

DELETE FROM dwh.fact_orders
WHERE order_date BETWEEN DATE '2026-01-01' AND DATE '2026-01-31';

When the WHERE clause aligns with the partition expression, Vertica can drop entire ROS containers rather than marking individual rows deleted — significantly faster.

Truncate (delete all rows)

TRUNCATE TABLE staging.fact_orders_load;

TRUNCATE is much faster than DELETE with no WHERE clause. It is not transaction-safe in the same way as DELETE — it drops ROS containers immediately.

Purging deleted rows

Logically deleted rows are purged by the Tuple Mover on its schedule. To force immediate purging:

SELECT PURGE_TABLE('dwh.fact_orders');
SELECT PURGE_PARTITION('dwh.fact_orders', '2026-01-01', '2026-01-31');

DML: MERGE (upsert)

MERGE is the standard Vertica upsert pattern. Use it to apply changes from a staging table into a target table.

Standard upsert

MERGE INTO dwh.fact_orders AS target
USING staging.fact_orders_load AS source
    ON target.order_id = source.order_id
WHEN MATCHED THEN
    UPDATE SET
        target.status     = source.status,
        target.amount     = source.amount,
        target.updated_at = NOW()
WHEN NOT MATCHED THEN
    INSERT (order_id, user_id, order_date, status, amount, currency, created_at)
    VALUES (source.order_id, source.user_id, source.order_date,
            source.status, source.amount, source.currency, NOW());

Insert-only merge (when no update needed)

MERGE INTO dwh.dim_users AS target
USING staging.dim_users_delta AS source
    ON target.user_id = source.user_id
WHEN NOT MATCHED THEN
    INSERT (user_id, email, country, created_at)
    VALUES (source.user_id, source.email, source.country, NOW());

Merge with delete

MERGE INTO dwh.fact_orders AS target
USING staging.fact_orders_deletes AS source
    ON target.order_id = source.order_id
WHEN MATCHED THEN DELETE;

Merge with conditional update

MERGE INTO dwh.fact_orders AS target
USING staging.fact_orders_load AS source
    ON target.order_id = source.order_id
WHEN MATCHED AND source.updated_at > target.updated_at THEN
    UPDATE SET
        target.status     = source.status,
        target.amount     = source.amount,
        target.updated_at = source.updated_at
WHEN NOT MATCHED THEN
    INSERT (order_id, user_id, order_date, status, amount, currency, created_at)
    VALUES (source.order_id, source.user_id, source.order_date,
            source.status, source.amount, source.currency, NOW());

MERGE rules:

• The ON clause must uniquely identify each target row; duplicate matches cause errors.
• Deduplicate the source before MERGE if the source can have multiple rows per key.
• WHEN MATCHED THEN DELETE removes the matched target row.
• Multiple WHEN MATCHED and WHEN NOT MATCHED branches are supported; Vertica evaluates them in order.

Data Update Strategies

Choose the right strategy based on data volume and update pattern:

1. Row-level UPDATE / DELETE

Use for small targeted changes (single row or narrow filter).

UPDATE dwh.dim_users SET country = 'DE' WHERE user_id = 42;
DELETE FROM dwh.fact_orders WHERE order_id = 9999;

2. MERGE from staging

Use for incremental loads where rows can be new or changed. This is the standard ETL pattern.

-- Load delta to staging
TRUNCATE TABLE staging.fact_orders_load;
COPY staging.fact_orders_load FROM '/data/orders/delta.csv' DELIMITER ',' DIRECT;

-- Apply to target
MERGE INTO dwh.fact_orders AS t
USING (
    SELECT order_id, user_id, order_date, status, amount, currency,
           ROW_NUMBER() OVER (PARTITION BY order_id ORDER BY updated_at DESC) AS rn
    FROM staging.fact_orders_load
) AS s ON t.order_id = s.order_id AND s.rn = 1
WHEN MATCHED THEN UPDATE SET
    t.status = s.status, t.amount = s.amount, t.updated_at = NOW()
WHEN NOT MATCHED THEN INSERT (order_id, user_id, order_date, status, amount, currency)
    VALUES (s.order_id, s.user_id, s.order_date, s.status, s.amount, s.currency);

Always deduplicate the staging source on the business key before or inside the USING clause.

3. Partition swap (large bulk reload)

Use when reloading an entire partition (e.g., a month) more efficiently than UPDATE/MERGE.

-- Load new data for the partition into a staging table
CREATE TABLE tmp.fact_orders_202601 LIKE dwh.fact_orders;
COPY tmp.fact_orders_202601 FROM '/data/orders/2026-01/*.csv' DELIMITER ',' DIRECT;

-- Swap the partition
ALTER TABLE dwh.fact_orders
    SWAP PARTITION BETWEEN DATE '2026-01-01' AND DATE '2026-01-31'
    WITH tmp.fact_orders_202601;

-- Drop the staging table
DROP TABLE tmp.fact_orders_202601;

4. TRUNCATE + INSERT (full table reload)

Use when rebuilding dimension tables or complete fact reloads.

TRUNCATE TABLE dwh.dim_users;
INSERT INTO dwh.dim_users SELECT * FROM staging.dim_users_full;

5. CREATE TABLE AS SELECT + RENAME (atomic full rebuild)

Use for full rebuilds where downtime must be minimized.

CREATE TABLE dwh.dim_users_new AS
SELECT * FROM staging.dim_users_full;

ALTER TABLE dwh.dim_users RENAME TO dim_users_old;
ALTER TABLE dwh.dim_users_new RENAME TO dim_users;
DROP TABLE dwh.dim_users_old;

DML: SELECT — Query Basics

Basic query structure

SELECT
    o.order_date,
    u.country,
    SUM(o.amount)    AS revenue,
    COUNT(DISTINCT o.order_id) AS order_count
FROM dwh.fact_orders o
JOIN dwh.dim_users u
    ON o.user_id = u.user_id
WHERE o.order_date BETWEEN DATE '2026-01-01' AND DATE '2026-01-31'
  AND o.status = 'completed'
GROUP BY o.order_date, u.country
ORDER BY o.order_date, revenue DESC;

CTEs

WITH completed_orders AS (
    SELECT order_id, user_id, order_date, amount
    FROM dwh.fact_orders
    WHERE status = 'completed'
      AND order_date >= DATE '2026-01-01'
),
user_revenue AS (
    SELECT
        o.user_id,
        u.country,
        SUM(o.amount) AS revenue
    FROM completed_orders o
    JOIN dwh.dim_users u ON o.user_id = u.user_id
    GROUP BY o.user_id, u.country
)
SELECT country, SUM(revenue) AS total_revenue
FROM user_revenue
GROUP BY country
ORDER BY total_revenue DESC
LIMIT 20;

Window functions

SELECT
    user_id,
    order_date,
    amount,
    SUM(amount) OVER (
        PARTITION BY user_id
        ORDER BY order_date
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS running_total,
    ROW_NUMBER() OVER (
        PARTITION BY user_id
        ORDER BY order_date DESC NULLS LAST
    ) AS recency_rank
FROM dwh.fact_orders
WHERE status = 'completed';

Deduplication with ROW_NUMBER

SELECT order_id, user_id, order_date, amount
FROM (
    SELECT *,
        ROW_NUMBER() OVER (
            PARTITION BY order_id
            ORDER BY updated_at DESC NULLS LAST
        ) AS rn
    FROM staging.fact_orders_load
) t
WHERE rn = 1;

Date and time filtering

-- Prefer direct date comparisons for partition pruning
WHERE order_date >= DATE '2026-01-01'
  AND order_date <  DATE '2026-02-01'

-- Avoid wrapping partition columns in functions
-- BAD: WHERE DATE_TRUNC('month', order_date) = DATE '2026-01-01'
-- GOOD: WHERE order_date >= DATE '2026-01-01' AND order_date < DATE '2026-02-01'

Useful Vertica date functions:

DATE_TRUNC('month', order_date)
ADD_MONTHS(order_date, 3)
DATEDIFF('day', start_date, end_date)
TIMESTAMPDIFF('hour', start_ts, end_ts)
NOW()
CURRENT_DATE
TO_DATE('2026-05-01', 'YYYY-MM-DD')
TO_TIMESTAMP('2026-05-01 12:00:00', 'YYYY-MM-DD HH24:MI:SS')

String functions

LOWER(status)
UPPER(country)
TRIM(email)
LTRIM(s), RTRIM(s)
SUBSTR(s, start, length)
REGEXP_LIKE(email, '^[a-z0-9._%+-]+@[a-z0-9.-]+\.[a-z]{2,}$')
REGEXP_SUBSTR(description, '[0-9]+')
SPLIT_PART(path, '/', 2)
REPLACE(s, 'old', 'new')
LENGTH(s)

NULL handling

COALESCE(amount, 0)
NVL(status, 'unknown')      -- Vertica alias for COALESCE with 2 args
NULLIFZERO(amount)           -- returns NULL if 0
ZEROIFNULL(amount)           -- returns 0 if NULL
DECODE(status, NULL, 'n/a', status)

Type casting

CAST(amount AS VARCHAR(20))
amount::VARCHAR(20)          -- Vertica shorthand
TO_CHAR(order_date, 'YYYY-MM-DD')
TO_NUMBER('123.45', '999.99')

Query Performance Guidance

Use partition pruning

Always filter on the partition column using direct comparisons, not function-wrapped columns:

WHERE order_date >= DATE '2026-01-01'
  AND order_date <  DATE '2026-04-01'

Check query plan

EXPLAIN SELECT ...;

Look for:

• GROUP BY PIPELINED: good, no sort needed.
• RESEGMENT: data is being redistributed across nodes for a join; check if segmentation keys match join keys.
• BROADCAST: small table is broadcast to all nodes; expected for small dimensions.
• Full-table scans without partition pruning on large partitioned tables: add a partition filter.
• Missing covering projections: Vertica uses the best available projection; create a covering one for hot queries.

ANALYZE_STATISTICS

Keep statistics current for the query optimizer:

SELECT ANALYZE_STATISTICS('dwh.fact_orders');
SELECT ANALYZE_STATISTICS('dwh.fact_orders', 'order_date');

Hints

-- Force use of a specific projection
SELECT /*+ PROJECTION(dwh.fact_orders_by_user) */ user_id, SUM(amount)
FROM dwh.fact_orders
GROUP BY user_id;

Useful System Queries

-- List tables in a schema
SELECT table_name FROM v_catalog.tables WHERE table_schema = 'dwh';

-- List columns
SELECT column_name, data_type, is_nullable, column_default
FROM v_catalog.columns
WHERE table_schema = 'dwh' AND table_name = 'fact_orders'
ORDER BY ordinal_position;

-- List projections
SELECT projection_name, is_super_projection, is_up_to_date
FROM v_catalog.projections
WHERE projection_schema = 'dwh' AND anchor_table_name = 'fact_orders';

-- List partitions
SELECT partition_key, ros_count, ros_row_count
FROM v_monitor.partitions
WHERE table_schema = 'dwh' AND table_name = 'fact_orders'
ORDER BY partition_key;

-- Check table row counts
SELECT table_schema, table_name, row_count
FROM v_monitor.table_storage
WHERE table_schema = 'dwh'
ORDER BY row_count DESC;

-- Recent load history
SELECT table_name, rows_accepted, rows_rejected, load_start, load_duration_ms
FROM v_monitor.load_streams
ORDER BY load_start DESC
LIMIT 20;

-- Active sessions and running queries
SELECT session_id, user_name, current_statement, is_active
FROM v_monitor.sessions
WHERE is_active = TRUE;

Anti-Patterns

Do not:

• Use SELECT * in production queries or INSERT-SELECT.
• Wrap partition columns in functions inside WHERE — this disables partition pruning.
• Run large UPDATE on most rows of a large table — use MERGE + staging or partition swap instead.
• Forget to deduplicate the source before MERGE — duplicate source keys cause runtime errors.
• Create many narrow projections — each projection is a full copy; balance query coverage with storage cost.
• Run TRUNCATE when transactional safety matters — prefer DELETE with a condition.
• Ignore PURGE_TABLE / PURGE_PARTITION on tables with heavy DELETE/UPDATE churn — deleted rows persist on disk until purged and inflate storage.
• Use ORDER BY in subqueries or CTEs unless absolutely required — it forces unnecessary sorts.
• Use FLOAT for monetary or precision-critical values — use NUMERIC(p,s).
• Build large literal IN (...) lists — use a join or a temp table instead.
• Omit the segmentation key in joins and group-bys — mismatched segmentation triggers expensive RESEGMENT operations.

Output Expectations

When producing Vertica SQL:

• Return valid Vertica SQL, not generic ANSI SQL or PostgreSQL.
• Use explicit column lists in INSERT, SELECT, and MERGE.
• Prefer MERGE for upserts over conditional INSERT/UPDATE logic.
• Explain segmentation, partition, and projection choices briefly when they affect correctness or performance.
• Mention when a write strategy (partition swap, full reload) is more appropriate than row-level DML.
• Include ANALYZE_STATISTICS or EXPLAIN recommendations when performance depends on data distribution.
• Call out PURGE_TABLE needs when heavy DELETE/UPDATE is used.