by Adam Roderick
Efficient enterprise data management implementation teams practice good code reuse to allow themselves to move quickly, minimize errors, and keep their systems simpler than otherwise. This results in dependencies among database objects such as tables, views, functions, and stored procedures.
Every team has good intentions to keep the system organized, and some do it successfully. But all eventually succumb to the large amount of complexity inherent in any EDM system. Hesitation and fear creep in when, inevitably, requirements dictate a code change to a foundational component, such as a heavily used user-defined function. The component is hard to test, it affects many areas of the system, the original developer moved on to something else—the list goes on. Whatever the reason, nobody wants to touch it.
I wish I had an answer to this. But reality is that we deal in complexity and our systems reflect that complexity. What I can offer is a couple of tidbits that may help your team deal with some of the complexity.
Most of our clients use EDM platforms based on SQL Server. SQL Server has built in meta data, called the Resource Database, about all the objects in the databases. Many EDM engineers do not leverage the Resource Database, because they perceive the meta data to be “internal” data that should not be touched. This is not the case, however, and Microsoft has extensive documentation and fully supports it.
One great example is a simple query that will give you a list of all database objects that reference a certain object.
SELECT referencing_schema_name, referencing_entity_name, referencing_id, referencing_class_desc, is_caller_dependent FROM sys.dm_sql_referencing_entities ('dbo.StatusItem', 'OBJECT'); GO
Another option is a free tool from RedGate called SQL Search 2.
Finally, an even better way to mitigate the risk of unintentionally breaking something while fixing something else is to practice modern testing techniques. EDM systems are unique animals in the software engineering world, and EDM teams often do not fully understand how to apply modern software testing techniques to data pipelines. hqTest can help you with that, because it is specifically designed to address testing enterprise data management systems and data pipelines.