Working with ETL Objects
It has long been a best practice to build software using multiple layers of abstraction. By creating an abstraction layer, the underlying objects are always used indirectly.
Abstraction can give you greater flexibility and lower maintenance costs in situations where database tables are only accessed using abstraction objects.
In SQL programming, abstraction layers are typically used with views and stored procedures. Adding this layer of objects can have the following benefits:
- Mask complexity of their programming statements
- Facilitate changes to underlying tables while hiding these changes
- Allow for security settings that protect the tables from misuse
A SQL view is a single select statement that is saved internally in the database under a specified name. The view is then used as if it were a table. The biggest differences between a table and a view are that a view is simply a saved select statement, while a table stores the actual data.
Views are useful for many ETL processing tasks such as the following:
- Transforming column names
- Using column aliases
- Combining data from multiple tables
- Processing nulls
- Performing data conversions
Stored procedures are similar to views. Both are named sets of SQL code. Unlike views, however, stored procedures can contain multiple statements, work with variables, and process transaction statements.
A combination of both Views and Stored procedures can be used to create an effect ETL Process:
Creating ETL views:
Note: Microsoft is the owner of the content and video in this blog post.