10 exam-style questions with answers and explanations, straight from our 1,030-question bank. Tap an answer to check yourself. When you're ready, take the scored version in the free practice test.
The ADVAP exam has 60 questions and runs 2 hours.
These 10 free ADVAP questions are organized by exam domain, so you can see how each part of the Vertica Analyst Certification Exam blueprint is tested. Reveal the answer and explanation under each question.
Two projections contain the same rows and use identical segmentation. In both, the numeric status column has six distinct values and uses RLE. One projection sorts by status before order_id; the other sorts by order_id and interleaves statuses. The status-first projection uses substantially less space for this column. What explains that difference?
Correct answer: B - Adjacent equal statuses form longer runs, reducing the number of stored runs.
During a tuning review, an analyst flags BROADCAST in a join plan as a defect. The inner input is a dimension filtered to 80 rows; the outer input is a 240-million-row fact scan. Profiling attributes less than 1% of elapsed time to redistribution, shows no spilling, and confirms that the query meets its latency target. A proposed replacement resegments the entire fact input without reducing scan work. What recommendation is justified by these findings?
Correct answer: D - Retain the plan; this small broadcast is not a performance problem.
Two projections are compared for the same SELECT under matched data, cache, concurrency, and resource settings. Both return identical results. Across repeated runs: Projection P: EXPLAIN cost 1,600; median elapsed time 2.4 s Projection Q: EXPLAIN cost 900; median elapsed time 4.1 s Storage and load costs are equivalent. Which conclusion belongs in the performance review?
Correct answer: C - P is faster in this test; lower optimizer cost does not guarantee lower elapsed time.
Most requests against public.sales retrieve one customer's transactions over a date interval: SELECT sale_id, sale_date, net_amount FROM public.sales WHERE customer_id = 418 AND sale_date >= DATE '2026-08-10' AND sale_date < DATE '2026-08-18'; Within each node, the required layout must keep each customer's rows together and order them chronologically by sale_date. Encoding and segmentation are identical across the candidates. Which single projection also covers the entire query?
Correct answer: B - Columns: customer_id, sale_date, sale_id, net_amount; sort: customer_id, sale_date.
An equipment dashboard must show the latest recorded event for each device, including that event's reading. event_time and event_id are NOT NULL; event_id is unique within each device, and the greater event_id wins a timestamp tie. A TopK projection begins: CREATE PROJECTION public.device_latest AS SELECT device_id, event_time, event_id, reading FROM public.device_events Which continuation implements the dashboard's row-selection rule?
Correct answer: A - LIMIT 1 OVER (PARTITION BY device_id ORDER BY event_time DESC, event_id DESC);
A monthly report derives AVG(amount) from a live aggregate projection grouped by store_id and sale_day. For one store, the complete month's activity is represented by these two daily totals: Day 1: SUM(amount) = 240; COUNT(amount) = 3; COUNT(*) = 4 Day 2: SUM(amount) = 960; COUNT(amount) = 6; COUNT(*) = 8 To two decimal places, which result preserves SQL AVG semantics for all transactions in the interval?
Correct answer: C - 133.33, using the sum of COUNT(amount) as the denominator.
Three new customer-facing queries are slow in an otherwise satisfactory warehouse. The DBA is authorized to tune those queries, not to replace the overall physical design. Additional storage and load overhead are acceptable if query latency improves. Which Database Designer setup fits the assignment?
Correct answer: B - Incremental design; Query objective; include the three new queries.
A large shipments table joins to a large invoices table on shipments.invoice_id = invoices.invoice_id. Both keys are INT, and both selected projections are sorted first by invoice_id, so the plan uses a presorted merge join. However, shipments is segmented by HASH(shipment_id), while invoices is segmented by HASH(invoice_id). Profiling identifies inter-node redistribution as the dominant cost. Which redesign targets that cost while preserving the useful ordering?
Correct answer: A - Segment shipments by HASH(invoice_id), matching invoices' segment count and node placement.
The refreshed expression projection public.line_items_value_b0 stores line_id, quantity, unit_price, and extended_value, where extended_value is quantity * unit_price. An application still runs: SELECT line_id, quantity * unit_price FROM public.line_items WHERE line_id = 782; The team expected automatic substitution of the stored computation. Which rewrite explicitly reads the precomputed value instead?
Correct answer: A - SELECT line_id, extended_value FROM public.line_items_value_b0 WHERE line_id = 782;
A deployment renamed a projection, but one query still contains: FROM public.orders /*+ PROJS('public.orders_old_b0') */ The named projection no longer exists. A valid superprojection remains available. The query emits a warning, completes, and returns correct results. Which response matches Vertica's hint behavior?
Correct answer: D - Repair the stale PROJS name; with no valid hinted candidate, Vertica warns and uses another valid projection.
The full bank has 1,020 more ADVAP questions with explanations.
Continue in the free practice test →
View plans