Can you recommend a good source for Teradata Best Practices?

Question

Looks like my data warehouse project is moving to Teradata next year (from SQL Server 2005).

I'm looking for resources about best practices on Teradata - from limitations of its SQL dialect to idioms and conventions for getting queries to perform well - particularly if they highlight things which are significantly different from SQL Server 2005. Specifically tips similar to those found in The Art of SQL (which is more Oracle-focused).

My business processes are currently in T-SQL stored procedures and rely fairly heavily on SQL Server 2005 features like PIVOT, UNPIVOT, and Common Table Expressions to produce about 27m rows of output a month from a 4TB data warehouse.

Answer 1

Top of the list on a Google search for "Teradata Best Practices" gave me TERADATA ADVISORY GROUP SETS BEST PRACTICES FOR BUSINESS OBJECTS AND TERADATA CUSTOMERS

EDIT: Seeing as that's just advertising, as you've pointed out, see how you go with these. Please bear in mind that I don't have a clue what Teradata is and can't see myself using it any time this side of the 22nd century AD.

• Teradata Discussion Forums
• Best Practices for Teradata Deployments
• Best Study Guides For NCR Teradata Certifications

The middle one looks promising with it's nice long link tree at the top

Oracle® Business Intelligence Applications Installation and Configuration Guide > Preinstallation and Predeployment Considerations for Oracle BI Applications > Teradata-Specific Database Guidelines for Oracle Business Analytics Warehouse >

and the first link, to the forums, should put you in touch with the right people.

Answer 2

Hi.

I don't know of any good references available online. Teradata has some design manuals that are available for download, but they're more instruction manuals and not "best practices" as such. check them out here: http://www.info.teradata.com/DataWarehouse/eTeradata-BrowseBy.cfm?page=Teradata%20Database

Alternatively, you need to find a friendly Teradata expert to bounce ideas off. Try Teradata themselves, or find a local consultant with Teradata experience.

Best Practices on Teradata isn't a topic that gets lots of discussions and most of the best tricks tend to be proprietary knowledge of the person/people who discovered them.

Sorry, David Stewardson Satyam Computer Services

Answer 3

I would suggest holding some money aside for a contractor or additional employee with Teradata experience. There seems to be little in the way of Teradata reference online. We are working to build some online resources @ www.Teradataconsultant.com but it is going to take a while to get built up.

I have a few people here in Atlanta that would be willing to do contract work with you if you are interested. I know budgets are tight, but keep us in mind. Thanks!

Answer 4

One place to start is here: http://www.teradataforum.com/

This might be a little late, but there are a few things which I can warn you about Teradata which I have learned.

Use the most recent version as often as possible. For V12 the optimizer was re-written and the database performs much better now. Try to realize that SQL Server and Teradata are very different beasts, most of the concepts will not transition well. Do not underestimate the importance of a primary index. The locks that teradata uses are very primitive when compared to other databases. Do NOT use TERA mode. You do not have any code which is legacy, ANSI mode is far superior and is widely encouraged. Join indexes are very helpful tools, but they do not provide all the answers. Parallelism, take the time to understand how FASTLOAD, MULTILOAD, and TPUMP works and find out how one can leverage it with their ETL strategy. If you are attempting to run a query which needs to be performant, do not use any casts, the optimizer will not use statistics to generate the best execution plan. Working with dates are going to be a pain, just a warning. Teradata is very DDL oriented, try to understand all the syntax related when creating a table. Compression is a wonderful tool, if you have any values which are repeated in a table, make use of it. There are not many tools available with Teradata, be prepared to build a lot. The tools that exist are very expensive.

Unfortunately, I do not know much about SQL Server, so I cannot say what tools in SQL Server appear in Teradata.

Hope this helps

Answer 5

I would also look into the recently launched Teradata Developer Exchange as well as the TeradataForum and forums on Teradata's main website.