I started my career as an Oracle developer, and the first thing I liked about it was the structured query language, though it was after a year or so when I realized that SQL was not proprietary to Oracle.
An offshoot of the 70s Sequel standard, SQL has matured and is still the most widely used language by database users around the world. The part I liked most about SQL was its simplicity (basic English words), but at the same time, it has the capability of providing answers to most of the complex problems. SQL is one of the easiest languages used today both to learn and to use. ANSI-SQL is a standard used by virtually all the major relational databases such as Oracle, DB2 and Microsoft SQL Server, which greatly improves portability, in case the customer decides to switch from one database to the other.
I enjoyed creating complex Oracle programming language/SQL code for approximately five years, before I was introduced to an ETL tool. Being an expert in SQL, my Initial ETL mappings were Source Structure -> Complex SQL -> Target, with all the complex transformation logic written in SQL.
I rarely used many of the excellent features which the ETL tool provided. Don’t get me wrong, I am not advocating SQLs to be used instead of an ETL tool, because ETL tools have much more to offer than just SQL. In fact, I am a very big proponent of ETL tools because of their ease of use, graphical user interface, and the ability to connect to different environments for extraction and load. Many ETL tools today have the ability to read data from disparate source systems like COBOL files, flat files, XML, databases and to an extent support real-time data capture.
Some Drawbacks of ETL
However robust an ETL tool is, the shortcoming of an ETL tool is that it is still at times unable to figure out how to write an efficient SQL query and get the best performance out of the database. I have experienced these kinds of issues in data warehousing projects where the volume of data in some of the tables is in billions, maybe trillions. The drawbacks are due to the generic way ETL tools are built to cater to all kinds of Databases and thus not able to use certain features of databases which would increase the performance of an ETL process. The strength of the SQL, if used intelligently, can increase the performance and reduce complexity of ETL mappings.
Types of Database Commands
The Standard Databases have three major types of commands, DDL, DML and DCL.
Data Definition Language (DDL) - Used to define the database structure (e.g., create, alter, drop etc.)
Data Manipulation Language (DML) - Used for managing data (e.g., select insert, update, and delete etc.)
Data Control Language (DML) - Used for managing security (e.g., Grant, Revoke etc.)
The Built-In Database ETL
An Insert with Select statement is the best and basic example of ETL, where extract and transformation is performed by the select statement and the load is performed by the insert statement. In the below statement, the calculation of bonus is a very good example of transformation applied at the SQL query level.
E.g., Insert into EMP_BONUS select empno, Sal, (Sal*0.1) Bonus from EMP;
There are many complex transformations you can perform at the database side with the help of SQL features like “CASE” statement, which works as an If ,Else if, and Else logic. I have used this feature many times to create complex transformation logic at the database side.
E.g., Select Empid,
CASE WHEN sal < 500 THEN ‘POOR’
WHEN sal between 500 and 1000 THEN ‘MID’
WHEN sal > 1000 THEN ‘RICH’
ELSE ‘N.A’
END As Status
From EMP;
Some Real-Life Experiences
I would like to share an experience where SQL played a major part in providing a critical solution on an engagement for a large client, when it mattered most. In one of the ETL mappings, we had logic to provide the latest record for a particular ID based on timestamp. This logic was replicated in many jobs via a reusable component. We had all the logic tested and validated in our development area before moving it to a test area for UAT. Suddenly we realized that the tool was behaving rather oddly and provided the
