Data Warehousing and Data Science

19 May 2012

System Interfaces Contract

Filed under: Data Warehousing — Vincent Rainardi @ 1:11 pm

The data types should not be changed with informing the downstream systems. Imagine if you were responsible to maintain the company data warehouse. And imagine that one source system suddenly changes the data types from varchar(30) to varchar(100), or from decimal(12,4) to decimal(20,4), without talking to you first. One day suddenly your warehouse batch failed and your team needs to fix it there and then. That would not be appropriate right?

So, if you are lucky enough to be the owner of an upstream system, you should not update your data types without first informing all downstream systems (including the data warehouse and BI systems) and agree with them an implementation date. On that date both you and them change the data type at the same time.

The source system can do what they like and we in the warehouse would suffer, right? Wrong. But in practical world, that’s what happen. The source system can drop a column any time they like. They can even drop a table. If we in DW read this column or table then we are stuffed! Our ETL suddenly stops working because the whole table is gone from the source system. What a wonderful way to start the week if on Monday morning we found that our DW batch failed because of a table was dropped from source. If this happens to you, insist to the source system that they have to back out that change. Then agree with them a date when they can do that, in the mean time you can prepare the DW to be ready to accept that change.

The first rule of system interfacing is that all party must sign to a contract. If you (DW) read their system (OLTP), you agree with them something like this:

The interface will be a file of this format (attached a File Spec), publish daily Mon-Fri at 11:15 pm on MQ (message queue), under this ID (attach the MQ spec).

If you are reading directly from their tables (using ODBC), then it would be something like this:

We (DW) will be reading directly from your database. Any DB must be notified to us 4 weeks in advance, especially table/view/column renaming/deletion (exception: new column/table/view).

That is called “Systems Interface Contract” (SIC). The interface should be signed by all parties involved. If you just read their table without agreeing anything then they can change their table any time and you are stuffed!

Leave a Comment »

No comments yet.

RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

Blog at

%d bloggers like this: