Teradata Development – Keep it simple, stupid!
Specialization within the workforce has led to a growing issue, which I have observed with greater occurrence. Specifically, the number of professionals skilled in all aspects of data warehousing has declined significantly. A decade ago, teams responsible for data warehousing were composed primarily of individuals with extensive backgrounds in the information technology industry. However, this is no longer the norm, as companies have begun to create highly specialized roles. While this may be assumed to reduce costs, I question its effectiveness and believe it contributes to many new problems.
One issue that has surfaced in multiple recent projects is the creation of excessively large SQL statements.
Teradata data warehouse developers are commonly expected to possess a skill set primarily around SQL proficiency and familiarity with ETL tools.
Get the complete Teradata tuning book, free
Teradata Query Performance Tuning: The DWHPro Guide
By Roland Wenzlofsky & Artemios Vogiatzis
206 pages on statistics and demographics, access paths and indexes, query execution plans, and joins. Get the full PDF and practical Teradata updates from DWHPro.
Confirm your email to receive your download link. Privacy policy. Unsubscribe in any newsletter.
About the free bookA streamlined development process typically appears as follows:
Business Analyst -> Technical Analyst -> Developer
The technical analyst typically condenses the business logic into a mapping document that is subsequently provided to the developers.
Due to limited development skills, some developers tend to create a single, excessively large SQL statement for each mapping without considering the possibility of redundancy. I have observed instances where developers blindly replicate the same code repeatedly.
Although it may appear advantageous for maintenance purposes, this method fails to create efficient SQL, leading to erroneous and superfluous code, ultimately resulting in an increased maintenance burden.
The Teradata optimizer is responsible for generating the execution plan of a SQL query. While it may improve with each new release of Teradata, it is important to consider how the optimizer decomposes each SQL statement into a sequence of 2-table joins.
The number of possible join orders can grow rapidly. Factorial permutations are an illustration of search-space growth, not a description of the optimizer’s actual enumeration and pruning algorithm.
As you can imagine, the optimizer has a substantial risk of making mistakes.
More joins can make estimation and optimization harder, but a large statement does not inevitably produce a poor plan. Use actual estimates, plans and runtime evidence to decide where to intervene.
How can we reduce the risk of a bad execution plan?
Reducing the risk of poor performance can be achieved through three simple and effective methods:
- Keep the number of joins small (the obvious solution)
- Split your SQL statements into parts by working with temporary tables (volatile tables, global temporary tables, or even real tables)
- Identify common business logic and maintain it in one place within your code.
Materializing intermediate results can make stages easier to inspect and tune, but adds writes, reads, statistics maintenance and transaction considerations, and may prevent useful cross-stage optimization. Compare total cost and verify equivalent results before splitting a statement.
Deciding what to change in your data platform?
Compare optimisation, modernisation and migration against your workload and business requirements.
Get the complete Teradata tuning book, free
Teradata Query Performance Tuning: The DWHPro Guide
By Roland Wenzlofsky & Artemios Vogiatzis
206 pages on statistics and demographics, access paths and indexes, query execution plans, and joins. Get the full PDF and practical Teradata updates from DWHPro.
Confirm your email to receive your download link. Privacy policy. Unsubscribe in any newsletter.
About the free book
This is the very essence and secret of High Perferforming Teradata ETL!