Download Teradata Vantage Teradata Vantage Data Engineering Practice Exam and more Exams Technology in PDF only on Docsity!
Engineering Practice Exam
Question 1. Which Teradata object is best suited for encapsulating reusable DML logic that can accept parameters? A) View B) Macro C) Stored Procedure D) Global Temporary Table Answer: C Explanation: Stored procedures can contain procedural logic, accept IN/OUT parameters, and execute multiple statements, making them ideal for reusable DML with parameterization. Question 2. What is the primary purpose of a Volatile Temporary Table in Teradata? A) Persist data across sessions B) Store data that is automatically dropped at the end of the session C) Provide permanent storage for archiving D) Enable row-level security Answer: B Explanation: Volatile tables exist only for the duration of the user session and are automatically dropped when the session ends. Question 3. Which of the following table types automatically redirects rows that violate a primary index to an error table? A) Permanent Table B) Queue Table C) MultiLoad Table D) Error Table Answer: B
Engineering Practice Exam
Explanation: Queue tables have built-in error handling that captures rows failing the primary index constraint into an associated error table. Question 4. In Teradata, a Base (1:1) View is characterized by which of the following? A) It can be updated directly if the underlying table permits updates B) It must contain a GROUP BY clause C) It cannot reference any other views D) It always materializes data on disk Answer: A Explanation: A base view maps one-to-one to a base table; if the view does not contain aggregations or joins, it can be updateable. Question 5. Which data type is used to store JSON documents in Teradata Vantage? A) CLOB B) BLOB C) JSON D) VARCHAR Answer: C Explanation: Teradata provides a native JSON data type that stores JSON data efficiently and enables JSON-specific functions. Question 6. The PERIOD data type is primarily used for which purpose? A) Storing timestamps with time zones B) Representing a range of dates or timestamps C) Defining column-level security policies D) Enforcing referential integrity
Engineering Practice Exam
C) It distributes rows across AMPs for parallel processing D) It creates a clustered index similar to SQL Server Answer: C Explanation: The PI determines the hash value used to distribute rows evenly across Access Module Processors (AMPs). Question 10. Which secondary index type does NOT enforce uniqueness and can be used on any column? A) Unique Secondary Index (USI) B) Non-Unique Secondary Index (NUSI) C) Join Index D) Primary Index Answer: B Explanation: NUSI allows duplicate values and can be defined on any column to improve access paths. Question 11. A Join Index is most beneficial when: A) The table has a high number of columns B) Queries frequently perform joins on the same set of columns C) The table is a volatile temporary table D) Data is stored in an object storage system Answer: B Explanation: Join Indexes pre-join data and store the result, speeding up repetitive join operations. Question 12. Which partitioning method stores data physically in separate tables based on a column value?
Engineering Practice Exam
A) Row Partitioning B) Column Partitioning C) Hash Partitioning D) Range Partitioning Answer: D Explanation: Range (or value) partitioning creates separate partitions (often separate tables) for defined column value ranges. Question 13. Dynamic Partition Elimination (DPE) improves query performance by: A) Rebuilding indexes automatically B) Skipping partitions that cannot contain qualifying rows based on predicates C) Compressing data on the fly D) Parallelizing scalar subqueries Answer: B Explanation: DPE evaluates predicates at compile time and eliminates unnecessary partitions from the scan. Question 14. Which clause can be used to return a random sample of rows without specifying a seed? A) SAMPLE B) RANDOM C) TOP D) LIMIT Answer: A Explanation: The SAMPLE clause returns a percentage or number of rows randomly selected from the result set.
Engineering Practice Exam
Answer: B Explanation: REGEXP_SUBSTR extracts and returns the substring that satisfies the regular expression. Question 18. Which JSON function compresses a JSON document for storage? A) JSON_EXTRACT B) JSON_COMPRESS C) JSON_AGG D) JSON_TABLE Answer: B Explanation: JSON_COMPRESS reduces the size of a JSON document while preserving its structure. Question 19. What does the QUALIFY clause do in a Teradata SELECT statement? A) Filters rows before aggregation B) Applies a condition after window functions are evaluated C) Limits the number of rows returned D) Specifies the order of columns in the output Answer: B Explanation: QUALIFY works like HAVING but for windowed results, allowing filtering based on analytic functions. Question 20. Which join type returns all rows from the left table and matching rows from the right table, filling NULLs when there is no match? A) INNER JOIN B) LEFT OUTER JOIN
Engineering Practice Exam
C) RIGHT OUTER JOIN
D) FULL OUTER JOIN
Answer: B Explanation: LEFT OUTER JOIN preserves all rows from the left side, adding NULLs for missing matches on the right. Question 21. A scalar subquery can be used in the SELECT list because it: A) Returns multiple rows that are aggregated automatically B) Returns exactly one value (one row, one column) C) Is executed on each AMP independently D) Must be correlated with the outer query Answer: B Explanation: Scalar subqueries are required to produce a single value, making them suitable for placement in the SELECT clause. Question 22. Correlated subqueries differ from non‑correlated subqueries in that they: A) Are always faster due to index usage B) Reference columns from the outer query, causing row‑by‑row execution C) Must be placed in the FROM clause D) Cannot contain aggregates Answer: B Explanation: Correlated subqueries reference outer query columns, leading to evaluation for each outer row. Question 23. In Teradata, data profiling is typically performed to: A) Create indexes automatically
Engineering Practice Exam
Question 26. In an EXPLAIN plan, the “AMP” column indicates: A) The number of rows returned B) The specific Access Module Processor that performed the operation C) The total cost in milliseconds D) The type of join used Answer: B Explanation: The AMP column shows which AMP executed each step, helping identify data skew or distribution issues. Question 27. Which TPT operator is best suited for loading data from a flat file into a Teradata table in bulk? A) EXPORT B) LOAD C) UPDATE D) STREAM Answer: B Explanation: The LOAD operator performs high‑performance bulk inserts from files into tables. Question 28. When using TPT MLOAD (MultiLoad), which of the following is a limitation? A) It cannot load into volatile tables B) It supports only INSERT operations, not UPDATE or DELETE C) It requires the target table to have a primary index D) It cannot be run in parallel Answer: C Explanation: MLOAD requires a Primary Index to distribute data across AMPs for its multi‑pass loading algorithm.
Engineering Practice Exam
Question 29. The TPT STREAM operator is typically used for: A) Exporting data to external files B) Loading data from a streaming source such as Kafka C) Performing batch updates only D) Creating database objects Answer: B Explanation: STREAM ingests continuous data streams, making it ideal for real‑time data ingestion. Question 30. Which Access Module (AM) provides the fastest data retrieval for a heavily filtered query on a column with a USI? A) Full Table Scan AM B) Primary Index AM C) Secondary Index AM (USI) D) Join Index AM Answer: C Explanation: The USI AM can directly locate rows using the unique secondary index, avoiding full scans. Question 31. IPE (Incremental Statistics) in Teradata is used to: A. Rebuild all indexes after a bulk load B. Update column statistics incrementally without a full scan C. Encrypt table data automatically D. Partition tables based on date columns Answer: B
Engineering Practice Exam
D. ElapsedTime Answer: D Explanation: ElapsedTime records the wall‑clock time from start to end of query execution. Question 35. When moving data from AWS S3 into Vantage, which feature is commonly used? A. TPT LOAD with S3 connector B. FTP transfer to the Vantage server C. Direct INSERT statements referencing S3 URLs D. Exporting data to CSV then using BTEQ Answer: A Explanation: TPT includes an S3 connector that streams data directly from S3 into Vantage tables. Question 36. To export data from Vantage to Azure Blob Storage, which utility is recommended? A. FastExport with Azure Blob connector B. BTEQ with OUTFILE to local disk C. MultiLoad with Azure driver D. TPT EXPORT using Azure connector Answer: D Explanation: TPT EXPORT can write directly to Azure Blob storage, handling large volumes efficiently. Question 37. Which native cloud service can be leveraged to orchestrate Vantage data pipelines? A. AWS Lambda
Engineering Practice Exam
B. Azure Data Factory C. Google Cloud Functions D. All of the above via Vantage’s Cloud Integration APIs Answer: D Explanation: Vantage provides APIs and connectors that allow orchestration from any major cloud service. Question 38. In an event‑driven architecture for Vantage, which component typically captures change data from source systems? A. Data Lake B. Change Data Capture (CDC) connector C. Batch Scheduler D. Data Warehouse Mirror Answer: B Explanation: CDC streams row‑level changes in real time, enabling event‑driven ingestion. Question 39. Which lock type allows a transaction to read uncommitted changes made by another transaction? A. READ UNCOMMITTED (ACCESS) B. READ COMMITTED (LOAD) C. SERIALIZABLE (READ) D. EXCLUSIVE (WRITE) Answer: A Explanation: READ UNCOMMITTED permits dirty reads, allowing visibility of uncommitted modifications.
Engineering Practice Exam
Question 43. Which of the following is a valid use case for a Global Temporary Table (GTT)? A. Storing data that must survive a server reboot B. Sharing intermediate results across multiple sessions simultaneously C. Holding session‑specific data that can be accessed by multiple concurrent users in the same session D. Providing a permanent staging area for ETL loads Answer: C Explanation: GTTs are visible to all sessions that share the same transaction ID (e.g., in a multi‑user session) and persist for the duration of the transaction. Question 44. The SAMPLE clause with SYSTEM keyword samples rows based on: A. Random number generator seeded by the system clock B. A deterministic hash of row values, ensuring repeatable samples C. The first N rows of the result set D. The distribution of primary index values Answer: B Explanation: SAMPLE SYSTEM uses a hash function on row data, producing repeatable samples across runs. Question 45. Which Teradata function returns the first N rows of a result set based on a specified order? A. TOP N B. LIMIT C. ROW_NUMBER() in a QUALIFY clause D. FETCH FIRST N ROWS ONLY
Engineering Practice Exam
Answer: C Explanation: Using ROW_NUMBER() with QUALIFY allows precise control over ordered row selection. Question 46. What is the effect of defining a column with the NOT NULL attribute? A. The column is automatically indexed B. The column cannot store NULL values; attempts to insert NULL will fail C. The column is hidden from SELECT * queries D. The column values are encrypted at rest Answer: B Explanation: NOT NULL enforces presence of a value for every row. Question 47. Which of the following best describes a “Volatile Table” in terms of logging? A. Fully logged, with redo entries for each row B. Not logged; data is only stored in memory until session ends C. Logged only for DML operations, not for DDL D. Logged only when the table contains more than 1 million rows Answer: B Explanation: Volatile tables are not logged; they reside in memory and are dropped at session termination. Question 48. In Teradata, the CAST function can be used to: A. Change the physical storage format of a column B. Convert a value from one data type to another, optionally specifying a format C. Create a new table based on a SELECT statement D. Apply compression to a column
Engineering Practice Exam
C. Defining a column that can store both JSON and XML simultaneously D. Representing a variable‑length character string without a defined length Answer: B Explanation: ANYTYPE provides flexibility to store values of differing data types, useful in heterogeneous schemas. Question 52. Which operation is NOT permitted on a Volatile Table? A. INSERT SELECT B. ALTER TABLE ADD COLUMN C. CREATE INDEX D. MERGE Answer: C Explanation: Volatile tables do not support secondary indexes; they rely on primary hash distribution. Question 53. When using TOP 10 PERCENT without an ORDER BY clause, Teradata will: A. Return the first 10% of rows based on physical storage order B. Throw a syntax error C. Randomly select 10% of rows D. Return an empty result set Answer: A Explanation: Without ORDER BY, TOP operates on the physical row order, which may be nondeterministic. Question 54. Which attribute can be used to enforce a default value for a column when INSERT statements omit the column?
Engineering Practice Exam
A. NOT NULL
B. DEFAULT
C. FORMAT
D. CHECK
Answer: B Explanation: The DEFAULT attribute specifies a value applied automatically when the column is not supplied. Question 55. The ROW_NUMBER() function differs from RANK() in that it: A. Returns a sequential integer without gaps, even for ties B. Can only be used without PARTITION BY C. Requires a GROUP BY clause D. Is not supported in Teradata Vantage Answer: A Explanation: ROW_NUMBER() assigns a unique sequential number to each row, regardless of duplicate values. Question 56. Which of the following is true about a Primary Index defined as “UNIQUE”? A. It automatically creates a Unique Secondary Index on the same columns B. It guarantees that no two rows share the same PI value C. It must be defined on a VARCHAR column only D. It forces row‑level locking for all DML operations Answer: B Explanation: A UNIQUE PI enforces uniqueness of the hash key across the table. Question 57. In a Join Index, the “base tables” are:
