Can you recommend a good source for Teradata Best Practices?
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
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