Throughout my career as a data engineer, I have been fascinated by reusable code and object-oriented programming. I have always looked for ways to build something once and reuse it wherever possible.
That mindset led me to develop a metadata-driven ETL framework in SQL Server, replacing a collection of traditional SSIS packages used to populate a data warehouse.
The problem with traditional ETL
When every source table requires its own SSIS package, common extraction logic tends to become duplicated across dozens of packages. Even a small change to an extraction rule or transformation can require modifications in multiple places.
This increases maintenance costs, introduces opportunities for human error, and makes the ETL process harder to evolve.
I wanted a more centralized and reusable approach.
Defining the rules instead of duplicating the code
I built a framework in SQL Server that separated the definition of an ETL operation from its execution.
Instead of creating a dedicated package for every table, I stored the characteristics of each extraction in a centralized configuration, including:
- Source information and extraction rules.
- Target tables and loading behavior.
- Full loads versus incremental extraction.
- The last successful ETL execution.
- Reusable date-range expressions, such as the previous year, the previous two years, or the period since the last successful execution.
Stored procedures interpreted these configurations and generated the appropriate extraction logic.
This shifted the approach from maintaining numerous independent packages to managing a common execution framework driven by metadata and business rules.
Extending the framework beyond data extraction
Once the framework was established, I extended the same principle to other database operations.
One extension generated SQL Server Agent jobs with multiple steps. Job definitions followed consistent conventions, used environment-specific variables, and could be deployed across environments without hardcoding environment details.
Another extension handled API calls, including triggering Power BI dataset refreshes.
Rather than relying exclusively on scheduled refreshes, reports could be refreshed as soon as their upstream data was ready. This reduced unnecessary waiting and helped make data available to report consumers sooner.
The framework also provided a foundation for coordinating dependent operations, supporting sequential execution and parallel processing where appropriate.
From handcrafted SQL to a management interface
And yes, this was before vibe coding became a thing. Every line of SQL was carefully crafted by hand, with a smile. 🙂
Later, I took the framework a step further by building a Node.js web application to manage the objects it created. The interface made it possible to create, delete, duplicate, and back up framework-managed objects through a centralized UI.
Looking back, it was an early example of something we now see everywhere: combining a reusable backend framework with a simple interface that makes complex operations easier to manage.
The SQL was handcrafted. The management UI? That came with a little vibe coding.
The impact
The biggest improvement was not simply execution speed; it was reducing duplicated logic and manual coordination.
When a shared extraction rule needed to change, I could update the common implementation instead of modifying multiple packages. New extraction operations could be introduced through configuration rather than requiring a new package for each table.
The result was a reusable foundation for managing data extraction, job execution, and API-driven operations through a consistent approach.
Looking back
This project reinforced a principle that continues to influence my work as a data engineer:
When the same operational logic appears in many places, the better solution may be to centralize the rules rather than keep reproducing the implementation.
A few years later, working with dbt and Snowflake, I found myself revisiting the same principle from a different angle. These tools provide a structured way to define, manage, and deploy data transformations. They also inspired me to explore how the same framework-driven approach could be extended to other types of data products, beyond traditional transformation models.