3

How do people typically handle schema changes in source systems when running an ETL as part of an EDW? For example when a column you were operating on disappears and your ETL breaks.

  • 2
    It depends on your ETL tool! Do you have anything specific in mind? – Anthony Genovese Mar 21 at 19:05
  • Hand written code. – Michael Mar 21 at 22:27
2
+50

How do people typically handle schema changes in source systems when running an ETL as part of an EDW? For example when a column you were operating on disappears and your ETL breaks.

I would like to answer this question on the same abstraction level it was asked.

Using conceptual object model of Informatica

Table represented by Source object is used in a Mapping, which enumerates all required Source columns (Ports). When the Mapping is executed by Session (as a part of a Workflow), the Mapping will throw runtime error about missing source Port.

Using the ETL subsystem model of Kimball

Take a look at The 38 Subsysems of ETL by Kimball. Whatever ETL tools you use, you will have following subsystems which play a role in adressing your "variating source" issue:

  • Extract Subsystem should catch runtime errors. SELECT * is almost never a good idea. If data source changed in a way that caused some columns/tables to disappear, the error / warning should be triggered - and you do need to log/track all errors. See next one.
  • Error Event Tracking. - aka Logging system. It's crucial, but sometimes ignored by in-house ETL solutions. All the components of ETL should be able to log errors/warnings in uniform way, on different levels of severity. And the messages should be propagated to the responsible ETL manager person. Bare minimum: flat, syslog-like logfiles plus some script to classify & email errors.
  • Job Scheduler and/or Workflow Monitor. Obviously you will run automated and/or manual ETL jobs, and you should be able to get notified about errors - as well as see current and historical job results including status, rowcounts, warnings of recent jobs.
  • Problem Escalation.. You need to have an effective path of problem escalation and it's not a technical, but organizational issue. Technically, it's often enough to send an email titled "EDW workflow 001 failed with FATAL error (log attached)". The key is a responsible person and a business process which will push this up.
1
  1. DDL Permission : GRANT Drop Table/column or Alter Table or whatever change that can affect ETL to very few selected users.So that any change pass through proper channel and ETL developer are aware in advance.

  2. Proper Error handling : Catch Error,log error and in some Error-code shoot email

  3. Select * Into Staging Table : This syntax do not require column name.so you can build your logic around it.Don't know your exact requirement,if possible use Staging Table.you can Drop and Create Staging Table everytime.

OR when column not exists error is Catch in Catch Block then you can write like

Begin TRY
  --your usual ETL Logic
END TRY
BEGIN CATCH
if @@ErrorCode="XYZ"
Begin
 Select * into TestStaging
--- Whatever your logic
-- Log Error
-- Shoot email
END
END CATCH
0

It depends. I daresay there are any number of ways to feed and care for an EDW, so I suspect there are any number of ways to guard against source system changes.

Most of our stuff is Type 1 and fed with flat files, which ostensibly guards against source system changes—except when they remove a column, or change the order of columns, or any of a number of other or statements.

We get flat files from the Mainframe, .NET applications, and outside entities. A change to the file format of a file breaks the ETL, but we've developed a framework, of sorts, that reports success or error, and load statistics, so we know when there's a failure. I can generally troubleshoot the issue quickly and report back to the responsible team, in polite terms, "Please fix your problem."

If a column were to be deprecated, but they wanted to preserve existing values in that now deprecated column, I could do it. The load package [we use SQL Server and SSIS] would be modified from Kill N Fill to Insert/Update and never NULL out the deprecated column. On Insert, if the column were empty, then of course the deprecated column for that row would be NULL. We've done this before.

We also have some Kimball Design stuff, which reads from Message Queues. I don't know of any specific instances of deprecated columns, but we could certainly modify that framework to NULL out a deprecated column in any new record—assuming they didn't remove a PK, which would present an interesting challenge I'd be up for.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.