| Osmel Brito, Angye Rivero |
For over 15 years, we have relied on SAS Data Integration Studio to develop and maintain robust ETL and data pipeline
solutions across industries. As data volumes have grown and performance demands increased, we have encountered
the natural evolution of DI Studio-generated jobs becoming monolithic, complex, and increasingly difficult to maintain and
optimize—especially when integrating with high-performance databases like Oracle.
This paper shares our journey in
breaking down monolithic ETL jobs into modular, reusable components using macro-based programming and design
patterns that complement SAS DI Studio's metadata-driven environment. We present a set of best practices that improve
code maintainability, reduce redundancy, and enhance troubleshooting and reusability. Additionally, we explore how
certain code-generation techniques can negatively impact Oracle database performance and show how simple
architectural decisions—such as leveraging pushdown optimization, proper index usage, and transactional control—can
drastically improve runtime and scalability.
The session will provide real-world examples, lessons learned, and
actionable strategies that attendees can adopt to modernize and optimize their own DI Studio implementations. Whether
you're inheriting legacy DI code or starting fresh, this paper will equip you with the tools and perspective to transition from
fragile ETLs to efficient, scalable, and maintainable data integration pipelines. |