Having your metadata under control and thus providing data lineage is a toughie. Not to mention another level of complexity when data travel across multiple databases. Especially through separate instances, thus when querying data through the database links are applied.
Part of our client base had a pretty extreme problem with their complex data warehouseenvironment. Any architectural change or hunting down bugs proved to be too time intensive for the data warehouse team. Especially since the number of such databases easily ranges between anything from 5 instances to 30 stand alone instances. This includes any data marts built for departments like finance, marketing, or sales. Tracing data lineage manually is a complete nightmare.
[Thanks for picture: http://www.legaltechnews.com]
Since we already have a very powerful SQL parser it was a logical conclusion to start tracing data lineage between databases in the same way developers manually scan through ETL and analyse SQL statements. The big difference is that we are utilizing cheap CPU time instead of your company burning the time of their expensive experts.
To sum up—you can pick any column within a database and visually trace all related data dependencies regardless how complex your architecture is. All you have to do is call our REST API and submit your ETL jobs based on SQL/stored procedures. Within minutes you will have complete documentation.
On top of this we have implemented support for table/view synonyms as well. These are useful to maintain naming conventions or keep access privileges under control. And the ultimate combo is to combine this new feature with our SQL Wayback machine. It allows you to visually compare data lineage and easily see the differences between production today versus a week ago. Not to mention comparing the staging environment with the production environment to have deployment under control.
With SQLdep you have excellent data governance. It doesn’t matter whether the guys from the audit department stop by or if you are fixing bugs. Through fully automated SQL code analysis you have perfect control over your business intelligence ecosystem..