Unlocking the Power of Teradata Macros: Features, Creation, and Execution

What is a Teradata Macro?

The Teradata macro executes multiple SQL queries in a single transaction, ensuring that all requests are successful or changes to database objects are rolled back.

Want more practical data engineering analysis like this?

Join DWHPro Letters and get field-tested notes on Teradata, Snowflake, AI, migrations, performance, and enterprise data work. Launch access is open. The single paid plan will be EUR 49 per year.

Get the next issue

What are the Features of Teradata Macros?

• Macros encapsulate application logic and control access to it
• The code of macros is stored in DBC tables
• Because macros are stored in DBC tables, they are available for all client tools.
• Macros can be called with parameters and are, therefore, flexible.
• Macros can be used, for example, to ensure referential integrity.
• Macros are optimized before execution.
• Macros can execute other macros.

How is a Macro created?

CREATE MACRO ShowSubscribers AS
(
  SELECT Lastname, Firstname FROM Subscriber;
);

How is a Macro changed?

REPLACE MACRO ShowSubscribers AS
(
  SELECT Lastname, Firstname, BirthDate FROM Subscriber;
);

How is a Macro dropped?

DROP MACRO ShowSubscribers;

How is a Macro executed?

EXECUTE ShowSubscribers;

More details can be found in the official Teradata documentation.

Planning or surviving an enterprise data platform migration?

I write regularly about the performance, cost, architecture, and project mistakes that show up in real Teradata, Snowflake, Databricks, and enterprise data work.

Subscribe before the paid plan launches and keep launch access.

Written by Roland Wenzlofsky, founder of DWHPro and author of Teradata Query Performance Tuning. DWHPro has helped data warehouse practitioners for 15+ years.