Download Teradata Vantage Data Engineering V2 Practice Exam and more Exams Technology in PDF only on Docsity!
Exam
Question 1. Which Teradata object is best suited for encapsulating reusable SQL logic that can be invoked with parameters and returns a result set? A) Macro B) Stored Procedure C) View D) Trigger Answer: B Explanation: Stored procedures can accept input parameters, contain procedural logic, and return result sets, making them ideal for reusable, parameterized operations. Question 2. In Teradata, which type of table automatically drops at the end of the session that created it? A) Permanent Table B) Global Temporary Table C) Volatile Table D) Queue Table Answer: C Explanation: Volatile tables exist only for the duration of the user session and are automatically dropped when the session ends. Question 3. Which clause is required to make a view updatable in Teradata? A) WITH CHECK OPTION B) WITH READ ONLY C) WITH NO DATA D) WITH RECURSIVE Answer: A
Exam
Explanation: The WITH CHECK OPTION clause ensures that any DML through the view respects the view’s defining query, allowing the view to be updatable. Question 4. What is the primary purpose of a Queue table in Teradata? A) To store error rows from a load B) To hold intermediate results for fast retrieval C) To provide a staging area for high‑volume data ingestion D) To enforce referential integrity Answer: C Explanation: Queue tables are optimized for high‑speed inserts and are commonly used as a staging area for bulk loading operations. Question 5. Which attribute can be used to automatically assign a default value to a column if none is provided during INSERT? A) FORMAT B) DEFAULT C) CASESPECIFIC D) COMPRESS Answer: B Explanation: The DEFAULT attribute defines a value that the system inserts when the column is omitted or explicitly set to NULL. Question 6. When defining a column of type PERIOD, which function returns the start timestamp of the period? A) BEGIN B) END C) P_START
Exam
A) Unique Secondary Index (USI) B) Non‑Unique Secondary Index (NUSI) C) Join Index D) Hash Index Answer: B Explanation: NUSI stores index entries in the same AMP as the base row, avoiding a separate B‑tree and reducing lookup overhead. Question 10. What is the main advantage of a Join Index in Teradata? A) It eliminates the need for a Primary Index B) It pre‑aggregates data to speed up query execution C) It stores data in columnar format for compression D) It automatically enforces referential integrity Answer: B Explanation: Join Indexes can store pre‑joined or pre‑aggregated results, allowing the optimizer to skip costly join operations. Question 11. Which partitioning method is most suitable for a fact table that is queried primarily by calendar month? A) Row‑partitioned on a DATE column B) Column‑partitioned on a VARCHAR column C) Row‑partitioned on a HASH of the primary key D) No partitioning; use a PI on the date column Answer: A Explanation: Row‑partitioning on a DATE column allows the optimizer to prune partitions that fall outside the queried month, improving I/O efficiency.
Exam
Question 12. Which clause can be used to limit the number of rows returned by a query in Teradata? A) SAMPLE B) TOP C) LIMIT D) FETCH FIRST Answer: B Explanation: The TOP clause specifies the maximum number of rows to return, similar to FETCH FIRST in ANSI SQL. Question 13. In a windowed aggregation, which clause defines the logical window frame? A) PARTITION BY B) ORDER BY C) ROWS BETWEEN … AND … D) GROUP BY Answer: C Explanation: ROWS BETWEEN … AND … defines the start and end of the window frame for each row in a window function. Question 14. Which operator is used to generate a random number between 0 and 1 in Teradata? A) RANDOM() B) RAND() C) SAMPLE() D) RANDOM_NUMBER()
Exam
B) Data type C) Compression ratio D) Row length Answer: A Explanation: High cardinality indicates many distinct values, which may benefit from a histogram to guide optimizer statistics. Question 18. Which Teradata feature automatically eliminates partitions that do not satisfy a predicate during query execution? A) Dynamic Partition Elimination (DPE) B) Automatic Skew Detection (ASD) C) Primary Index Elimination (PIE) D) Join Index Pruning (JIP) Answer: A Explanation: DPE removes irrelevant partitions at runtime, reducing I/O and improving performance. Question 19. Which statement best describes the role of the Explain plan in Teradata? A) It rewrites the query for better performance B) It shows the logical and physical steps the optimizer will take C) It executes the query and returns the result set D) It creates statistics for the involved tables Answer: B Explanation: An Explain plan provides a detailed view of the optimizer’s chosen access paths, joins, and operations.
Exam
Question 20. Which QueryGrid configuration is appropriate for federated queries that need to push down filters to a remote Oracle database? A) Full Data Push‑Down B) Join Push‑Down C) Filter Push‑Down D) Result Set Pull‑Up Answer: C Explanation: Filter Push‑Down sends the WHERE clause to the remote system, reducing data transferred across the grid. Question 21. In TPT, which operator is responsible for reading data from a flat file and sending it to the Teradata database? A) LOAD B) EXPORT C) STREAM D) SQL INSERTER Answer: C Explanation: The STREAM operator reads from external sources (e.g., files) and streams data into the database. Question 22. Which TPT operator should be used when the target table has a Primary Index on a column that is not present in the source file? A) LOAD B) UPDATE C) SQL SELECTOR D) SQL INSERTER Answer: B
Exam
D) Use the REPLACE keyword to overwrite bad rows Answer: B Explanation: Specifying an ERRORFILE captures rejected rows for later analysis without aborting the whole job. Question 26. In a scenario where a large fact table is refreshed nightly, which load strategy typically provides the best performance? A) Single‑threaded INSERT statements B) TPT LOAD with multiple streams and partitioned target C) Bulk INSERT via the BTEQ utility D) Row‑by‑row MERGE statements Answer: B Explanation: TPT LOAD with parallel streams and partitioned target leverages Teradata’s parallelism, minimizing load time. Question 27. Which advanced tuning option can eliminate the need for a Primary Index when the optimizer determines that no data redistribution is required? A) Intelligent Primary Index Elimination (IPE) B) Partition Restriction by Pre‑processing (PRPD) C) Query Rewrite (QR) D) Join Index Elimination (JIE) Answer: A Explanation: IPE allows the optimizer to skip PI enforcement when it can prove that data distribution is already optimal. Question 28. What does the PRPD (Partition Restriction by Pre‑processing) option do? A) It creates histograms on partition columns automatically
Exam
B) It forces the optimizer to evaluate partition predicates before data retrieval C) It removes all partitions from a table before loading new data D) It disables statistics collection on partitioned tables Answer: B Explanation: PRPD pushes partition‑pruning predicates into the scan phase, reducing the amount of data read. Question 29. Which DBQL field provides the total CPU time consumed by a query? A) CpuTime B) ExecTime C) ParseTime D) IOMask Answer: A Explanation: The CpuTime field records the cumulative CPU usage for the query execution. Question 30. When analyzing data quality issues, which profiling output is most useful for identifying missing mandatory values? A) Frequency distribution of NULLs per column B) Average row length C) Compression ratio per column D) Column data type mismatches Answer: A Explanation: Frequency counts of NULLs reveal columns where mandatory data is absent, guiding data‑cleansing efforts. Question 31. In Teradata, what is the difference between a transaction and a request?
Exam
Question 34. When moving data from Amazon S3 into Teradata Vantage, which tool is commonly used? A) BTEQ B) FastLoad C) TPT with the S3 ACCESS MODULE D) CLIv Answer: C Explanation: TPT’s S3 access module enables direct read/write operations between S3 and Vantage. Question 35. Which step is essential before loading data from Azure Blob Storage into a Vantage database? A) Create an external table referencing the blob location B) Set the session’s DATEFORMAT to ISO C) Register the storage account as a credential object in Vantage D) Enable ANSI mode for the session Answer: C Explanation: Credentials (access key/secret) must be stored in Vantage to authorize access to Azure Blob Storage. Question 36. In a predictive model pipeline on Vantage, which language is natively supported for model training without external services? A) Java B) Python (via VantageML) C) Scala D) Go
Exam
Answer: B Explanation: VantageML provides native Python support for building, training, and scoring models within the database. Question 37. Which architectural pattern enables real‑time processing of events from a message queue into Teradata Vantage? A) Batch ETL B) Event‑Driven Architecture (EDA) using StreamSets or Apache Flink C) Traditional OLAP cubes D) Manual CSV imports Answer: B Explanation: EDA processes events as they arrive, often using streaming platforms that can write directly to Vantage. Question 38. Which feature allows Teradata Vantage to execute Python code without moving data out of the database? A) External Stored Procedure (C) B) VantagePython UDFs C) Java Stored Procedure D) ODBC Driver Answer: B Explanation: VantagePython UDFs run Python code inside the engine, keeping data in‑place for processing. Question 39. Which of the following is a benefit of using a NoPI table for a staging area? A) Automatic primary key enforcement B) Even data distribution without a defined PI
Exam
Question 42. When using the SAMPLE clause, what does the expression SAMPLE 0.1 return? A) 10 rows B) 10% of the rows, rounded up C) Exactly 0 rows if the table is empty D) A random 0.1% of rows Answer: B Explanation: SAMPLE with a decimal fraction selects approximately that percentage of rows from the table. Question 43. Which window function can be used to calculate a running total ordered by a date column? A) SUM(col) OVER (ORDER BY date) B) CUMULATIVE_SUM(col) C) TOTAL(col) PARTITION BY date D) ROW_NUMBER() OVER (ORDER BY date) Answer: A Explanation: SUM with an OVER clause and ORDER BY defines a cumulative (running) total. Question 44. In a correlated subquery, which reference is allowed? A) Referencing columns from the outer query inside the subquery's SELECT list B) Referencing columns from the subquery in the outer query’s WHERE clause C) Using aggregate functions without GROUP BY in the subquery D) Using the subquery’s alias in the outer SELECT list Answer: A
Exam
Explanation: Correlated subqueries can reference outer query columns, creating a row‑by‑row dependency. Question 45. Which attribute can be used to store a column’s values in a compressed format automatically? A) COMPRESS B) FORMAT C) CASESPECIFIC D) ENCODE Answer: A Explanation: The COMPRESS attribute tells Teradata to apply block compression to the column’s data. Question 46. Which function returns the number of characters in a VARCHAR column, counting multibyte characters correctly? A) LENGTH B) CHAR_LENGTH C) BYTE_LENGTH D) CHARACTER_LENGTH Answer: B Explanation: CHAR_LENGTH (or CHARACTER_LENGTH) returns the number of characters, handling multibyte encodings properly. Question 47. Which optimizer hint can be used to force the use of a specific Join Index? A) /*+ INDEX(table join_index) / B) /+ JOININDEX(table join_index) / C) /+ USE_JOIN_INDEX(table join_index) */
Exam
A) JSON_TO_VARBYTE
B) TO_BLOB
C) CAST(JSON AS VARBYTE)
D) JSON_COMPRESS
Answer: C Explanation: Casting a JSON column to VARBYTE stores the JSON in a binary format, which can then be compressed. Question 51. Which of the following statements about the NOPI table is FALSE? A) It automatically distributes rows using a hash of all columns B) It cannot have a Primary Index defined C) It is ideal for tables with a natural Primary Key that is not unique D) It may suffer from skew if the data has low cardinality on all columns Answer: C Explanation: NOPI tables are used when there is no natural PI; if a natural unique key exists, a PI should be defined. Question 52. Which clause can be used to enforce that a column’s values are always stored in upper‑case? A) FORMAT 'UPPER' B) CASESPECIFIC C) DEFAULT UPPER(column) D) CHECK (column = UPPER(column)) Answer: D Explanation: A CHECK constraint comparing the column to its UPPER version guarantees the stored value is uppercase.
Exam
Question 53. Which of the following is true about a Period data type column? A) It can store only dates, not timestamps B) It automatically enforces non‑overlapping intervals when defined with a UNIQUE constraint C) The END value is inclusive by default D) It cannot be indexed Answer: B Explanation: When a UNIQUE constraint is applied to a PERIOD column, Teradata ensures that intervals do not overlap. Question 54. In a query that uses the TOP clause without ORDER BY, what determines which rows are returned? A) The rows with the highest primary key values B) The rows with the lowest primary key values C) An arbitrary set of rows based on the internal data distribution D) Rows are returned in the order they were inserted Answer: C Explanation: Without ORDER BY, TOP selects an arbitrary subset of rows, often based on the internal scan order. Question 55. Which of the following is a recommended practice when creating statistics on a large table? A) Collect statistics on every column B) Collect column statistics on columns used in join predicates and WHERE clauses C) Disable automatic statistics collection to improve load performance D) Use SAMPLE 0.01 for all statistics to reduce collection time
