CAIC Database Administration 2

Question 1

A company stores AI training datasets in a relational database. Which indexing strategy best accelerates similarity searches on high-dimensional embedding vectors?

Accepted Answer

Approximate Nearest Neighbor (ANN) index such as HNSW

Answer

ANN indexes like HNSW (Hierarchical Navigable Small World) are purpose-built for high-dimensional vector similarity searches, offering sub-linear query time.

Question 2

When an AI pipeline writes predictions continuously to a PostgreSQL table, which technique prevents table bloat caused by frequent UPDATEs triggering MVCC dead tuples?

Accepted Answer

Running VACUUM ANALYZE on a frequent schedule

Answer

Regular VACUUM ANALYZE reclaims space from dead tuples created by MVCC and updates planner statistics, preventing bloat in high-update workloads.

Question 3

An AI consultant needs to enforce row-level access control so that each data science team only queries their own labeled datasets. Which database feature provides the most fine-grained solution?

Accepted Answer

Row-Level Security (RLS) policies

Answer

Row-Level Security policies attach access predicates directly to tables, transparently filtering rows based on the executing user's role or session variables.

Question 4

A data lakehouse uses Apache Parquet files as the storage layer for ML features. What is the primary advantage of Parquet's columnar format over row-oriented storage for analytical AI workloads?

Accepted Answer

Efficient compression and selective column reads, reducing I/O

Answer

Columnar storage allows queries to read only the required columns and compresses homogeneous data more effectively, drastically reducing I/O for analytical scans.

Question 5

Which database normalization issue arises when an AI model's feature store table contains columns that depend on a non-key attribute rather than the full primary key?

Accepted Answer

Partial dependency violating Second Normal Form (2NF)

Answer

A partial dependency occurs when a non-key column depends on only part of a composite primary key, violating 2NF.

(CAIC) Certified AI Consultant Practice Test

(CAIC) Certified AI Consultant Practice Test

CAIC Database Administration 2