Download Teradata Vantage Teradata Vantage Practice Exam and more Exams Technology in PDF only on Docsity!
Question 1. Which component of Teradata Vantage is responsible for parsing SQL statements and generating an execution plan? A) Access Module Processor (AMP) B) Parsing Engine (PE) C) Virtual Processor (VP) D) QueryGrid Manager Answer: B Explanation: The Parsing Engine receives the SQL text, validates syntax, checks privileges, and creates the optimal execution plan before passing it to the AMPs. Question 2. In Teradata, what does a Primary Index (PI) primarily determine? A) Row-level security B) Physical data distribution across AMPs C) Column compression algorithm D) Order of rows within a table Answer: B Explanation: The PI hash value decides which AMP stores a particular row, ensuring parallel processing and data distribution. Question 3. Which of the following best describes a NoPI table? A) A table without a Primary Index, data is stored on a single AMP B) A table that uses a Unique Secondary Index as its primary distribution key C) A table that distributes rows using a random hash value on each insert D) A table that automatically creates a Primary Index based on the first column
Answer: C Explanation: NoPI tables generate a random hash for each row, resulting in a non-deterministic distribution across AMPs. Question 4. What is the main advantage of a Partitioned Primary Index (PPI) over a regular Primary Index? A) Eliminates the need for statistics collection B) Enables Dynamic Partition Elimination for faster pruning of data C) Guarantees a unique key for every row D) Stores data in compressed columnar format Answer: B Explanation: PPIs allow the optimizer to skip entire partitions that are not needed for a query, improving performance. Question 5. Which lock type in Teradata protects an entire table from concurrent modifications? A) Row Hash lock B) Database lock C) Table lock D) Column lock Answer: C Explanation: A Table lock prevents other sessions from updating, inserting, or deleting rows in the locked table. Question 6. In Teradata, what does the ANSI transaction mode enforce that Teradata‑Mode does not?
Explanation: TPT consolidates loading, exporting, and updating capabilities with flexible operators and APIs. Question 9. Which TPT operator is used to stream data directly into a table without staging? A) LoadOperator B) UpdateOperator C) DeleteOperator D) StreamOperator Answer: D Explanation: The StreamOperator reads data from a source and writes it to a target table in a continuous flow. Question 10. When using a Join Index, what is the main benefit? A) Eliminates the need for a Primary Index B) Stores pre‑joined data to accelerate join queries C) Provides automatic data compression D) Guarantees uniqueness of the joined columns Answer: B Explanation: A Join Index materializes the result of a join, allowing the optimizer to retrieve data without performing the join at runtime. Question 11. Which of the following statements about a Unique Secondary Index (USI) is true? A) It can be created on a NoPI table only
B) It guarantees that the indexed column values are unique across the table C) It distributes rows based on the primary index hash value D) It does not require statistics collection Answer: B Explanation: A USI enforces uniqueness on the indexed column(s) and creates a separate B‑Tree for fast lookups. Question 12. What does Dynamic Partition Elimination (DPE) rely on to prune partitions at runtime? A) Statistics on the Primary Index B) Partition boundaries defined in the PPI definition C) Row‑level locks D) User‑defined macros Answer: B Explanation: DPE uses the defined partition ranges to exclude partitions that cannot satisfy the query predicates. Question 13. Which SQL clause is used to create a Partitioned Primary Index on a table? A) PRIMARY INDEX (column) B) PARTITION BY RANGE (column) C) PRIMARY INDEX (column) PARTITION BY RANGE (column) D) DISTRIBUTE BY HASH (column) Answer: C Explanation: The syntax combines PRIMARY INDEX with PARTITION BY RANGE to define a PPI.
C) Data dictionary changes D) Query rewrite rules Answer: B Explanation: System Health presents real‑time metrics on resource consumption such as CPU, memory, and disk usage. Question 17. Which of the following is a characteristic of a Stored Procedure in Teradata? A) It can contain conditional logic and loops B) It can only execute a single SQL statement C) It cannot accept input parameters D) It is limited to read‑only operations Answer: A Explanation: Stored Procedures support procedural constructs like IF, WHILE, and can accept parameters for flexible logic. Question 18. In Teradata, what is the purpose of a Macro? A) To enforce row‑level security B) To encapsulate a set of SQL statements that can be executed as a single command C) To automatically collect statistics after data load D) To replace the Parsing Engine Answer: B Explanation: A Macro stores a sequence of SQL statements, allowing them to be invoked with a single command.
Question 19. Which function would you use to retrieve the nth highest salary from an employee table? A) RANK() OVER (ORDER BY salary DESC) B) ROW_NUMBER() OVER (ORDER BY salary ASC) C) DENSE_RANK() OVER (PARTITION BY salary) D) TOPN() Answer: A Explanation: RANK assigns the same rank to ties; selecting rows where rank = n returns the nth highest salary. Question 20. Which window function can be used to calculate a running total of sales ordered by date? A) SUM(sales) OVER (PARTITION BY date) B) SUM(sales) OVER (ORDER BY date ROWS UNBOUNDED PRECEDING) C) TOTAL(sales) OVER (ORDER BY date) D) CUMULATIVE_SUM(sales) Answer: B Explanation: The window frame “ROWS UNBOUNDED PRECEDING” computes the cumulative sum up to the current row. Question 21. What is the default isolation level for Teradata‑Mode transactions? A) READ UNCOMMITTED B) READ COMMITTED C) REPEATABLE READ D) SERIALIZABLE
A) JSON_PARSE()
B) JSON_EXTRACT()
C) TO_JSON()
D) CAST(... AS JSON)
Answer: D Explanation: Casting a VARCHAR containing valid JSON to the JSON data type converts it for JSON functions. Question 25. What is the primary benefit of using in‑database analytics in Teradata Vantage? A) It requires no SQL knowledge B) It eliminates data movement, reducing latency and I/O overhead C) It automatically creates visual dashboards D) It stores all results in external files Answer: B Explanation: In‑database analytics execute algorithms where the data resides, minimizing data transfer and improving performance. Question 26. Which ClearScape Analytics language is specifically designed for statistical modeling inside Teradata? A) R B) Python C) TDML (Teradata Machine Learning) D) SAS
Answer: C Explanation: TDML provides a SQL‑like syntax for building and scoring models directly in the database. Question 27. Which of the following is a supervised machine‑learning algorithm available in Teradata Vantage? A) K‑Means clustering B) Naïve Bayes classification C) Apriori association rule mining D) Principal Component Analysis Answer: B Explanation: Naïve Bayes is a classification algorithm; K‑Means is unsupervised clustering. Question 28. In Teradata, what does the “ModelOps” feature primarily address? A) Real‑time query rewriting B) Lifecycle management of analytical models, including versioning and deployment C) Automatic index creation D) Data compression Answer: B Explanation: ModelOps provides tools for managing, deploying, and monitoring models throughout their lifecycle. Question 29. Which SQL clause would you use to sample 10% of rows from a large table for a quick analysis? A) SAMPLE 10 PERCENT
Question 32. Which of the following is true about Teradata QueryGrid? A) It only works with Oracle databases B) It enables federated queries across multiple heterogeneous data sources C) It replaces the need for a Primary Index D) It is used exclusively for data backup Answer: B Explanation: QueryGrid allows a single SQL statement to access data in Teradata and external systems like Hadoop, Oracle, or S3. Question 33. In a Teradata environment, which object is used to define a user’s resource limits such as spool and CPU? A) Role B) Profile C) Database D) Macro Answer: B Explanation: Profiles contain resource‑governance settings that can be assigned to users. Question 34. Which privilege allows a user to create tables in a specific database? A) SELECT B) INSERT C) CREATE TABLE D) DROP Answer: C
Explanation: The CREATE TABLE privilege authorizes the creation of new tables within the database. Question 35. What does the “Proxy User” functionality in Teradata enable? A) Automatic password rotation B) One user to act on behalf of another, inheriting the latter’s privileges C) Encryption of all data at rest D) Real‑time data replication Answer: B Explanation: Proxy users allow a service account to execute statements as another user, useful for audit and security. Question 36. Which of the following is a system‑level log that records authentication attempts? A) DBC.LogonLogV B) DBC.SessionInfoV C) DBC.AccessLogV D) DBC.UserLogV Answer: A Explanation: LogonLogV captures successful and failed login attempts, aiding security audits. Question 37. In Teradata, what is the purpose of the “Spool” space? A) Permanent storage for tables B) Temporary workspace for query execution results and intermediates C) Backup storage for recovery files
Question 40. Which of the following is a valid reason to use a Non‑Unique Secondary Index (NUSI)? A) To enforce uniqueness on a column B) To improve query performance on a column that is not part of the Primary Index but may have duplicate values C) To replace the Primary Index entirely D) To guarantee that rows are stored on a single AMP Answer: B Explanation: NUSIs speed up lookups on non‑unique columns without enforcing uniqueness. Question 41. In a MERGE statement, which clause specifies the condition that determines whether to UPDATE or INSERT? A) WHEN MATCHED THEN UPDATE B) ON DUPLICATE KEY C) USING D) WHEN NOT MATCHED THEN INSERT Answer: A Explanation: WHEN MATCHED defines the actions for rows that already exist; WHEN NOT MATCHED handles inserts. Question 42. Which optimizer feature in Teradata rewrites a query to use a materialized join index when available? A) Iterative Planning Engine (IPE) B) Query Rewrite (QR) C) Partitioned Join Elimination (PJE)
D) Cost‑Based Optimization (CBO) Answer: B Explanation: Query Rewrite can substitute a join with a pre‑materialized Join Index to improve performance. Question 43. What does the “HASH BY” clause do in a CREATE TABLE statement? A) Defines a secondary index B) Specifies the distribution key for the table (Primary Index) C) Creates a partitioned table D) Sets the compression level Answer: B Explanation: HASH BY (column) defines the Primary Index that determines data distribution. Question 44. Which function is used to extract a value from a JSON column using a JSONPath expression? A) JSON_VALUE() B) JSON_EXTRACT() C) JSON_QUERY() D) JSON_GET() Answer: A Explanation: JSON_VALUE returns a scalar value from JSON data based on a JSONPath. Question 45. In Teradata, what is the purpose of the “LOCKING ROW FOR ACCESS” clause? A) To acquire an exclusive lock on the row
Question 48. Which statement best describes a “Macro” in Teradata? A) It is a compiled object that can contain control‑flow logic B) It stores a sequence of SQL statements that execute in a single request, but cannot contain procedural logic C) It automatically gathers statistics after execution D) It is used only for data loading Answer: B Explanation: Macros are simple containers for SQL statements without procedural constructs. Question 49. In Teradata, which command is used to retrieve the current session’s user name? A) SELECT USER FROM DBC.SessionInfoV; B) SELECT CURRENT_USER; C) SELECT SESSION_USER; D) SELECT USERNAME; Answer: B Explanation: CURRENT_USER returns the identifier of the user that initiated the session. Question 50. Which of the following is NOT a component of Teradata’s Active System Management (TASM)? A) Classification B) Throttles C) Data Compression D) Exceptions
Answer: C Explanation: Data compression is a storage feature, not part of TASM’s workload‑management components. Question 51. What does the “COLLECT STATISTICS ON (column) WITH SAMPLE 10 PERCENT” command do? A) Collects statistics on the column using a random 10% sample of rows B) Collects statistics on 10% of the columns in the table C) Forces a full table scan for the column D) Creates a secondary index on the column Answer: A Explanation: The SAMPLE clause limits statistics collection to a representative subset, reducing overhead. Question 52. Which Teradata function returns the difference in days between two DATE values? A) DATE_DIFF() B) DAYS_BETWEEN() C) (date1 - date2) D) INTERVAL(date1, date2) Answer: C Explanation: Subtracting two DATE values yields an integer representing the day difference.
