DWHPro Insights

Table Skew

High-Performance Calculations with Teradata Ordered Analytical Functions Popular

Illustration of a glossy orange database cylinder with data fragments across its surface

For a true calendar-day window, aggregate to one row per department/day and supply missing dates, or use a date-bounded query. With duplicate dates, add a meaningful tie-breaker for a ROWS frame. Adding an ORDER BY or PARTITION BY column can change the result and must preserve the intended business semantics. Teradata employs two distinct approaches …

Read more

Teradata VantageCloud Lake: Architecture and Use Cases Widely read

Illustration of three stacked database storage units of differing heights against abstract chart shapes

Introduction Parallel database architectures have undergone significant advancements over the past four decades, transitioning from shared memory architecture to shared disk architecture and, finally, to the more efficient shared-nothing architecture. Databases designed specifically for cloud environments incorporate elements of shared-disk and shared-nothing architectures. Teradata is a powerful and scalable relational database management system designed to …

Read more

Teradata Rollbacks: Understanding the Impact on Performance and How to Avoid Them Popular

Illustration of a person in a suit standing beside a database cylinder

How to Abort the Teradata Rollback Rollback cancellation can invalidate the table and is a DBA recovery action, not routine tuning. Follow a documented recovery procedure and verify a rebuild/restore path; skew on one AMP does not inevitably stop the entire system. Watch: what happens when you abort a big DELETE This video shows why …

Read more

Understanding Teradata’s Primary AMP Index for Improved Performance Popular

Illustration of a stopwatch dial on an orange paint-splash background

Experienced Teradata users are familiar with the concept of a primary index. However, a new term has surfaced with the introduction of Teradata Release 15: Primary AMP Index. This blog post will demonstrate a Primary AMP Index’s benefits and optimal usage for improving load and query performance. First, let us examine Teradata’s approaches over the …

Read more

Optimize Teradata UNION ALL with a Single Table Scan Trick

Illustration of a stopwatch dial on an orange paint-splash background

The Idea Behind This Trick for Teradata UNION ALL What if you need to apply a UNION ALL operation to distinct columns within a single table? Typically, the process would involve: The rewrite may permit one base-table scan, but scan count and I/O benefit depend on the optimizer and data. Verify the plan rather than …

Read more

Building a Teradata Data Warehouse: Considerations for ETL Process, SQL Queries, and Physical Data Model Popular

Illustration of a network graph with circular nodes joined by lines

This post aims to compile all crucial aspects to be considered while constructing a Teradata Data Warehouse, including the ETL process and SQL queries. This list is just the beginning, and I anticipate receiving valuable feedback from my readers to expand it in the future. Initially, I have provided a few concepts, but I intend …

Read more

Optimizing Teradata Joins: Handling Skewed Workloads with Partial Redistribution and Partial Duplication Popular

Illustration of a stopwatch dial on an orange paint-splash background

How does Teradata handle Skew in Joins? The imbalanced workload is a primary issue that must be addressed in performance optimization. 🎓 Prefer video? Lecture 11 of my Udemy course Teradata Performance Tuning in Practice, “Skewed joins: partial redistribution and duplication”, covers this topic. See the course on Udemy → Efficient optimization is crucial for …

Read more

Teradata Table Design Checklist: Primary Index, Character Set, and Data Type Considerations Popular

Illustration of a stopwatch dial on an orange paint-splash background

Teradata Table Design Checklist As creating tables is a frequent task, I deemed it necessary to provide a checklist. 1. Primary Index Or NOPI Table? The Primary Index serves multiple purposes, primarily functioning as the primary access path to the data and being optimized for an even distribution of rows. However, designing a table without …

Read more

Teradata Architecture: How Shared-Nothing Parallelism Works Widely read

Illustration of three stacked database units of differing heights inside a circle

Teradata Query Parallelism Teradata can execute scans, joins, sorts and aggregations in parallel. The chosen plan determines how many AMPs participate; a primary-index lookup can use one AMP, and dependencies may serialize parts of a request. 🎓 Prefer video? Lecture 2 of my Udemy course Teradata Performance Tuning in Practice, “Shared-nothing parallelism: nodes, PEs and …

Read more

Protected by Security by CleanTalk and CleanTalk Anti-Spam