Teradata 14

Teradata 14 Physical Design and Implementation

About the exam

Covers the Features and Functionality of the Teradata Database 13.0, 13.10, and 14.0 (including SLES 11)

This exam will certify that candidate has core knowledge and skills necessary to design and implement a Teradata Database from a logical data model. The candidate understands the Teradata implementation of data distribution, space usage, data access, and data manipulation processes. The candidate can implement a Teradata Database using a set of requirements.

REGISTRATION INFORMATION

Teradata 14 Physical Design and Implementation

To get registered for the Teradata Database Associate Exam,
visit home.pearsonvue.com/teradata and select exam TE0-143.
For a list of regional phone numbers visit home.pearsonvue.com/teradata/contact/.

bypass this exam

Already Certified in Teradata 12?

Teradata 12 Certified Technical Specialist, Database Administrator, Solution Developer, Enterprise Architect and Master certification holders can bypass this exam and take the Teradata 14 Bridge from Teradata 12 Exam (TE0-14B). Certifications are not awarded for exams that are bypassed. 
  • EXAM DETAILS
  • EXAM OBJECTIVES
  • EXAM PREP
RECOMMENDED EXPERIENCE

Minimum of 12 months of hands-on experience with Teradata databases and applications is recommended.

CREDENTIALS

Successful completion of this exam will earn you a Teradata 14 Certified Technical Specialist Certification with a digital badge.

DURATION

This exam allows two (2) hours for answering exam questions. 

PREREQUISITES

Successfully passing Teradata 14 Basics Exam (TE0-141)  and Teradata 14 SQL Exam (TE0-142)  are prerequisites for this exam. 

COST

The cost of Teradata exams varies by exam, location, and currency. Please visit home.pearsonvue.com/teradata to find the price of an exam. Teradata Employees can visit the TCPP Connections Group to obtain an employee voucher. You must be behind the Teradata firewall to access this page.

For more details regarding specific exam objectives, download this PDF.

PHYSICAL DATABASE DESIGN OVERVIEW

Describe the inputs, outputs, and objectives for physical database design. Determine when denormalization is appropriate. Identify the types of derived data and how to manage them. Describe the effects of denormalization on data integration. Explain the effects of the Primary Key and Unique constraints on physical design. Given a scenario about designing a database hierarchy, identify the characteristics of databases and users. Explain the effect of the Primary Index choices on physical design.

TABLE ATTRIBUTES

Describe the temporary table options and their advantages and disadvantages. Describe table-level options. Given a scenario, determine the performance impact of using a Set or Multiset table. Given a scenario, describe the use of a Queue Table. Given a scenario, determine when it is appropriate to ALTER a table vs. CREATE a new PPI or NPPI table. Identify table-level options that minimize table fragmentation. When performing a CREATE TABLE AS, identify possible skewing risks.

COLUMN ATTRIBUTES

Describe the performance considerations of Referential Integrity. Describe the performance characteristics of UNICODE character sets. Given a scenario, identify which data types are appropriate.

STATISTICS

Given a scenario, identify columns that are appropriate for statistics collection. Given a scenario, describe conditions where stale stats can impact performance. Determine when multi-column statistics are useful. Identify the utilities available to determine which statistics are useful. Identify when SAMPLE statistics are sufficient. Identify when SUMMARY statistics are sufficient. Identify when MAXINTERVALS are useful. Identify how ROLLUP Optimization can improve collect times. Describe the tables and views used for Statistics Collection. Interpret the output of SHOW and HELP STATISTICS statements.

PRIMARY INDEXES

Given a scenario, identify when to use a UPI. Given a scenario, identify when to use a NUPI. Given a scenario, identify when to use a NoPI.

TABLE PARTITIONING

Given a scenario, identify when to use a PPI. Given a scenario, identify when to use a MLPPI. Given a scenario, identify when to use Column Partitioning (CP). Given a scenario, identify when to use a Character PPI. Identify issues to consider when using NO RANGE on PPI tables. Describe how to construct the partitioning expression for a PPI table. Given a scenario, describe the effects of altering a partitioning expression. Given a scenario, describe the effects of using the ALTER TABLE TO CURRENT.

COMPRESSION

Identify the techniques to determine candidate columns for Multivalued compression (MVC). Identify the table level compression options (e.g., Block Level Compression (BLC) and Temperature-based Block Level Compression (TBBLC). Identify the column level compression options (e.g., Multivalued compression (MVC) and algorithmic Compression (ALC). Given a scenario, determine the most effective columns available for compression. Given a scenario, identify when to use the NO AUTO compress option for Column Partitioning.

SECONDARY INDEXES

Given a scenario, identify when to use USIs. Given a scenario, identify when to use NUSIs.

OTHER INDEX TYPES

Given a scenario, identify when to use STJIs. Given a scenario, identify when to use a non-compressed join index vs. a compressed join index. Given a scenario, identify when to use AJIs. Given a scenario, identify when to use Multi-table Join Indexes. Given a scenario, identify when to create an index that covers queries. 

PHYSICAL DATABASE OPERATIONS

Explain the effects of row access, selection, aggregation, and selectivity on query optimization. Explain partial value searches and data conversions on index utilization. Identify the effects of conflicting implicit data type conversions. Given a scenario, identify if skewed processing will occur. Given a scenario, identify a design strategy to efficiently join tables together. 

temporal design considerations

Given a scenario, design an effective VALIDTIME table. Given a scenario, design an effective TRANSACTIONTIME table. Given a scenario, design a temporal table. Given a scenario, design a temporal view using CURRENT time. Given a scenario, design a SEQUENCED or NON-SEQUENCED temporal view. Given a scenario, determine an effective method for implementing an AS OF view.

teradata query analysis

Interpret the EXPLAIN syntax. Interpret the EXPLAINs of Joins. Determine the methods to uncover embedded (nested) views. Interpret DBQL output. Identify the utilities available to perform Query Analysis.

Keywords: Teradata Analyst Pack, Viewpoint

RECOMMENDED COURSES

The Teradata Certified Professional Program partners with the Teradata Education Network to provide Teradata courseware and curriculum that specifically relates to each certification level. You can view course descriptions and schedules, and enroll online. Several courses are offered as web-based training (WBT) courseware for your convenience.

  • •    Physical Database Design
  • •    Physical Database Tuning

The TCPP team has created sample questions to help you prepare for the exam. Access the Teradata Database Sample Questions here

SOFTWARE DOWNLOADS

Available through Teradata Developers Exchange to help you prepare with hands on application.

TERADATA EMPLOYEES NOLY

Log on to the Teradata network and the TCPP Connections site for recommended training.

  • POLICIES

Exam Administration Policies

Before registering for the exam, familiarize yourself with retaking an exam, recertification of an exam, and the order required for the Teradata 14 Certification track. 

View Policies

Security and Privacy Policies

We take your security and privacy very seriously. Learn more about how we’re actively ensuring TCPP integrity and what Teradata considers to be a violation.

View Policies

  • Exam Administration Policies
  • Security and Privacy Policies