Question regarding invalidated sources

[jisoo23]

Hello all,

This is more of a theory-based question more than anything else. From what I understand, if a database table is revised (i.e. a column is deleted) then the source object for that table is invalidated and must be re-imported. Does anyone know the specific reason why? As in what are the underpinnings as to what's causing the invalidation? I'm just curious.

Thanks,
Jisoo23

 

[bsellick]

Since we are talking "in theory" I'll step in with a comment.

In my experience, the meta data definitions within an ETL tool (like Informatica) won't AUTOMATICALLY be invalidated because of a change to the underlying physical table... that's one of the beauties of having the meta data layer. If the change does not actually impact any of your ETL projects, then you'll notice nothing. If the change does impact a project then you'll start getting errors at job execution time.

The point at which a table object DOES become invalidated is generally when you manually do a validation check within the tool (I suppose there may be some tools that have an option/functionality to automatically go out and validate).

The "underpinning" I guess would simply be the tool comparing its current internal table def with the current external def and seeing, or not seeing, a difference. I'm guessing that the external check is generally done against a db's "sys" tables to return the column names and data types of the physical table.

Does that help at all?