• Teradata 14 Physical Design and Implementation

  •  

    Teradata 14 Physical Design and Implementation Exam (TE0-143)

    Exam Details

    Purpose of the Exam

    This exam will certify that the successful 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.

    Credential Earned

    Teradata 14 Certified Technical Specialist Certification with Digital Badge

    Exam Duration

    2 hours*

    *The exam duration shown is the amount of time allowed for answering exam questions. The Test Delivery Provider may add time to your total appointment to provide the opportunity to get seated, read the Non-Disclosure Agreement (NDA) and complete the tutorial (if applicable) before you start the exam.  

    Language

    English

    Cost

    The cost of Teradata exams vary by exam, location and currency. Please visit www.pearsonvue.com/teradata to find the price of an exam where you are located.

    Teradata Employees visit the TCPP Connections Group to obtain an employee voucher. (You must be behind the Teradata firewall to access this page)

    Prerequisites, Eligibility Requirements and Recommended Experience

    Prerequisite Exams

    Exam TE0-143 does not require an eligibility voucher.

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

    How to Register

    Online - visit www.pearsonvue.com/teradata  

    For a list of regional phone numbers visit www.pearsonvue.com/teradata/contact/

    Retake Policy

    A failed exam may be retaken after a 21 calendar day waiting period. The 21 calendar day waiting period begins from the date of the failed exam attempt.

    You will be required to schedule a new appointment and pay for the exam.

    You may attempt an exam a maximum of three (3) times during a six (6) month period.

    Re-taking the same Teradata 14 exam after passing that exam will not be allowed.

    TE0-143 Teradata 14 Physical Design and Implementation Exam Objectives

    The high level objectives represent the general content areas. The more detailed information below the objective indicates representative topic areas. All Teradata 14 functions and features that fall within the stated objective areas are eligible topics on an exam.

    TE0-143 Teradata 14 Physical Design and Implementation

    • 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.
    • 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

    • 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.

    Click for Printable Exam Objectives PDF

    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.

    Teradata Employees only: visit the Teradata internal TCPP Connections site for more information on recommended Teradata training. (You must be logged in to the Teradata network).

    Resources & Preparation Materials

    Teradata Certification App (TCaPP)

    The TCaPP App is free and provides instant access to: program information, tutorials, Authorized Study Guides, practice question packs, exam registration and results from the convenience of your smartphone or tablet.

    Download the App for Android or iOS.

    Authorized Study Guide

    Authorized Teradata Certification Study Guides are available to support your Teradata Certification goals. Each guide provides focused content, simplified examples and explanations around key objective areas, and help in determining topics for further study prior to sitting for your Teradata Certification exams. The guides are intended as a study companion for individuals who have completed recommended Teradata training courses and who have the suggested experience using Teradata Database technology.

    Teradata 14 Certification Study Guide

    Note: The Teradata Certification Study Guides and Apps are the only authorized study guide publications in the market. The Teradata Certified Professional Program partners with Cerulium Corporation to author the Teradata Certification Study Guides and develop mobile applications.

    Sample Questions

    Access Teradata 14 Sample Questions  

    Sample questions are designed to give you an example of the format and types of questions on the actual Teradata Certification exams. The sample questions are not intended to duplicate items on the actual exams. Questions are rotated and will be slightly different each time.  

    The sample questions are offered on-line free of charge and are not “scored” or tracked. Practice at your own pace and as often as you want.  

    More sample questions are available for purchase through the Teradata Certification App (TCaPP) and end of chapter questions are included with the Authorized Study Guides.

    Documentation and Downloads

    Documentation: Search and access Teradata installation, programming, operations and service guides, and reference manuals.

    Software Downloads: are available through Teradata Developers Exchange to help you prepare through hands on application.

    Back to Top