Teradata Basics
Teradata Product Overview
Processing Types and Characteristics
Data Warehouse Architectures
Relational Database Concepts
Teradata RDBMS Components and Architecture
Database Managed Storage
Data Access Mechanics
Data Availability Features
Teradata Tools and Utilities
Workload Management
Security and Privacy
Teradata SQL
Teradata Extensions
Data Definition Language (DDL)
Data Manipulation Language (DML)
Data Control Language (DCL)
Views and Macros
Logical and Conditional Expressions
Data Conversions and Computations
CASE Expressions
Subqueries and Correlated Subqueries
Joins
Attribute and String Functions
Set Operations
Analytical Functions
Time/Date/Timestamp/Intervals (ANSI vs. TERADATA)
Stored Procedures Concepts
Aggregations
SQL Optimization Concepts
Advanced SQL Concepts
Teradata Physical Design and Implementation
Physical Database Design Overview
Table Attributes – table types
Column Attributes – Data types
Statistics
Primary Indexes – Advantages of Partitioned primary index
Secondary Indexes – Comparison of primary and secondary indexes
Other Index Considerations
Transaction Isolation
Physical Database Operations
Teradata Query Analysis
Database (Spool/Perm) Space Management
Teradata 12 Database Administration
System Software Setup and Parameters
User and Security Management
Session Management
Load and Extract
System Administration Tools
System Workload Analysis and Management
Performance Optimization
Capacity Management and Planning
Business Continuity
Object Maintenance
Teradata Solutions Development
The solutions development process: data integration and access, performance analysis tools, and data model characteristics
Solutions development considerations: physical design, data integration, data access performance, partitioning, security, privacy, and APIs.
The solutions development planning process: capacity, security, and data warehouse administration planning, and analyzing solution performance metrics
Solutions development strategies: advanced SQL functions, table selection, transaction mode, coding constructs, transaction types, NULL processing, and recursive queries
Data integration strategies: load utilities, export options, load strategies, referential integrity, and locking.
Solutions optimization: performance improvement, statistics collection, secondary and join indexes, understanding EXPLAINs, and Load utility performance.
Basics
o centrally located architectures vs. physically distributed architectures, such as an appliance
o star schema vs. third normal form
o non-partitioned vs. single level vs. multi-level partitioned tables
o benefits of PPI, MLPPI
o benefits of join indexes
o node failover
SQL
o ANSI vs. Teradata mode in the areas of transaction protocol, defaults, conversions, and table creation
o use of aliasing in table joins
o evaluation order of various join types
o joining partitioned tables
o formatting options for internationalization of data types
o UNION vs. UNION ALL
o Window Aggregate functions
o use of Timestamp in expressions
o use of Intervals in expressions
o extended GROUP BY operators
o TOP N vs. SAMPLE
o effect of dropping or altering a partition
o use of CREATE TABLE AS
o correctly written SELECT statement
o use of ANSI MERGE INTO and UPDATE (upsert processing)
o change a user’s role
o use a locking modifier in a view or macro
o IN and NOT IN logical predicate
o explicit and implicit data type conversion
o SQL statement for a correlated subquery
o use of a derived table
Physical Design and Implementation
o interpret DBQL output
o determine columns for compression – Introduction to compresssion
o ALTER a table vs. CREATE a new table
o use ALTER table on PPI tables
o tuning options that minimize table fragmentation
o performance considerations of Referential Integrity
o converting a UNICODE value to Latin
o identify columns for statistics collection
o when does Random AMP sampling occur
o when to use multi-column statistics
o when to use PPIs and MLPPIs
o when to use NUSIs
o when to use STJIs and Multi-table Join Indexes
o conflicting locks in a multi-statement request
o explain partial value searches and data conversions on index utilization
o identify if skew on processing will occur
Database Administration
o ctl/xctl functionality
o identify the outcome of partition maintenance
o recovery implications of user defined functions, table functions, user defined types, and stored procedures
o actions taken using object use count
o granting access rights
o use of Roles in relation to security management
o use of Profiles
o steps to solve session management problems
o identify use in a shared userid scenario
o unique features of Teradata Parallel Transporter
o how to control utility limits
o tools/resources to determine the cause of system bottleneck
o ResUsage tables to use for problem identification
o Priority Scheduler relative weights
o distinguish between Teradata Dynamic Workload Manager (TDWM) features
o use of extrapolated and propagated statistics
o given a scenario, identify columns to collect statistics on
o characteristics of a well-performing tactical query
o potential solutions to capacity shortages
o features and functions of Online Archive
o features and functions of Partition Archive and Restore
Solutions Development
o select an effective data integration approach based on application requirements
o select an effective data access approach based on differing application requirements
o contrast the update processes for event driven, active data warehouse, and BI environments, and identify their impact on development choices
o implement and manage application security and privacy
o impact of row and set manipulation requests on application performance and on developer choices
o correlate BI solution development choices with data access performance
o role of partitioning with respect to data integration
o behavior of an identity column
o resources to analyze solution performance metrics that can also be used during testing
o use of triggers
o recursive query performance issues
o compare Ordered Analytic Functions
o multi-statement request vs. multi-statement transaction
o identify the result of outer joins
o impact of NULL processing on a result set
o effect of Teradata Parallel Transporter parameters
o eliminate unwanted duplicate rows from a multi-set table
o locking impact caused by the different load utilities
o components for a dual system architecture
o describe the operation of a join plan
o use EXPLAIN to improve performance
o use Collect Statistics to improve performance
o use join indexes to improve performance
o given a scenario with table definitions, SQL, and data demographics, identify the best performing partitioning definition
Enterprise Architecture
o use Teradata workload management to satisfy service level agreements
o capacity factors to consider prior to promoting an application into production
o given a scenario, determine the appropriate system hardware configuration
o given a scenario, identify the potential system and application availability characteristics including active data warehousing that can affect a database architecture
o options to improve slow-running query
o balance and reserve system resources for a heavily loaded system
o determine the inputs necessary to establish a query management and workload policy
o determine the effects of database optimization techniques including those on tables or indexes
o ETL vs. ELT design
o identify the appropriate approach for data movement
o use of surrogate keys
o use the LDM to design an integrated data architecture
o choose the appropriate indexes by converting a LDM to an Extended LDM
o methods to meet privacy requirements
o methods to meet user access auditing requirements
o benefits of data governance [on a growing enterprise data warehouse environment
o distinguish between in-database analytics, external processing, ROLAP technology, or External Cube technology
o optimization techniques for a high-volume tactical workload
o manage tactical workloads to meet SLAs
o effects of embedded (nested) views
Learn Teradata from iexpertify, search for any topic you need, leave a comment if you need anything.
thanks