Teradata:How to check DDL(table structures) are in sync among different environments(development/Test/Production) ?

Title of this post will be explained with an example. For example, If your projects involve populating data from source to target for ten (10) new tables. Then you have to create the DDL for ten tables at least in three different environments(development/Test/Production). Well, you start with development environment, develop the jobs and populate data into target area and when those are working fine then you take DDL from development environment to Test. And if tester or business find that you need to add a new column or data type then you need to change. Moreover, maybe your go-live date is closer so same DDL that put into test also may go for deploy into Production. It means , any change caught by tester or you; now you need to change the SQL in three different environment to keep consistent. Offourse, you follow change order to keep track of that, but as a developer you will prefer to see if all three different environments's table structure looks same.

If you use database as Teradata you have options to test through different environment, you can compare tables, table's column and column's data type. So you can quickly find out if you have any inconsistency through different environments.

Below code will help you to find out the difference between Test and Production environments.

select
upper(trim(tablename)) Table_name,
upper(trim(columnname)) Column_name,
upper(trim(ColumnFormat)) Column_format
from dbc.columns

where upper(trim(tablename)) in ('Custmer', 'Cust_Addreess', 'Geography', ,'Address_X_Geo','Customer_X_Product', 'Customer_X_Sales')
and upper(trim(databasename))='DWH_T'
except
select
upper(trim(tablename)) Table_name,
upper(trim(columnname)) Column_name,
upper(trim(ColumnFormat)) Column_format
from dbc.columns
where upper(trim(tablename)) in ('Custmer', 'Cust_Addreess', 'Geography', ,'Address_X_Geo','Customer_X_Product', 'Customer_X_Sales')
and upper(trim(databasename))='DWH_P';


Sometimes it's challenging to find out if  table structures (DDL) for a particular projects in different environments in data warehouse has same DDL. And due to the inconsistent table structure especially between test and production can lead into ETL job failure in production.

As precaution, by running above mentioned code you can verify if your production DDL is different than Test DDL where your ETL job ran fine. So you can fix the DDL before the jobs failed in production.

A newly developed job in SAS DI Studio: Why target table not found after Checked In?

When you start working with new tool you will find the tool is buggy (you assume so.. but actually most of the cases it’s you who don’t know all features of the tool).  However, a few cases tool have it’s known issue or can’t report the error message properly.

If you are new to SAS DI, I am sure you will find this post interesting.

Let’s start with a ETL job that which get data from an external file and loading into Teradata table.

       Fig 1: An example job that get data from external file and load into Teradata table

When job is completed we do checked in and so that other developer can review it and can deployed to Production.

Check In Window Look like below:

                                        Fig 2:  Check In Window

After clicking Check In you will find below three steps to do check In the job.

Fig 3: Title and description at while check in.

  Fig 4: Verify the objects that you are checking in

Fig 5: Summary of Check In Wizard

After completing the check In when you tried to open the job you will find below error:

Fig 6: Missing Objects

By looking at the information message you can't identify what has changes until you look and observe your job is missing two objects one is external source file and other is target Terdata table. 

But you have checked in the job, why then those objects are missing??

Well, only by checking  the job you can't expect all objects will be check in automatically. You must need to do check In source file and target table then you will find the job as it should be.

Tips: It's always good idea to do check In even if the job is not completed and check In all the objects related to job.