4 ETL
A common and practical application of PL/SQL is in the processing of incoming data files—whether from operational systems such as CRM platforms or during data migration from legacy systems to modern applications. Example 14 demonstrates a typical configuration for this process.
Example 14
In many implementations, the primary task involves restructuring incoming data to create dimension and fact tables during the transformation phase of the ETL process. For reasons of stability and validation, transformation is often performed after the data has been successfully loaded and verified in the target environment. This approach aligns with an Extract, Load, Transform (ELT) pattern, rather than the traditional ETL model.
PL/SQL is frequently used to load raw data into staging tables that preserve the structure of the source system. These staging areas are typically highly normalized, and transformation involves de-normalizing this structure to create analytical models. This is where PL/SQL proves particularly valuable—allowing the consolidation of normalized data into larger, purpose-built tables, such as dimensions, fact tables, or hybrid structures.
The tools used to perform the extract phase have evolved over time but generally follow consistent principles. Solutions have included vendor-specific applications, Informatica, Dell Boomi, and custom-built extract utilities. More recently, platforms like Salesforce have been designed with built-in export functionality optimized for downstream consumption by systems like Snowflake or Databricks, supporting out-of-the-box ELT workflows.
In earlier architectures, legacy ETL processes often relied on file transfers to analytical databases, where transformation and loading into data marts or warehouses were handled by custom PL/SQL programs. In such environments, both the data pipelines and the data warehouse schema were frequently entirely bespoke, requiring deep PL/SQL expertise to ensure accuracy, performance, and maintainability.
PL/SQL has certain limitations, particularly when it comes to tasks such as scraping data from external websites—an increasingly common requirement—or interacting with databases that are not hosted within the same server infrastructure as the target system. One major challenge in such scenarios is gaining access to source systems, which often involves limited system knowledge, security concerns, and the need for formal trust agreements. These obstacles make it far more practical to package the data externally and deliver it to the target system for processing.
For the purposes of this book, it is assumed that incoming data has already been packaged using one of the tools mentioned earlier, and the focus will be on unpacking and processing that data. A traditional method used for this purpose is Oracle’s SQL*Loader, though it is now considered outdated. More modern alternatives include using programming languages such as Python or PL/SQL. This book will focus on the PL/SQL-based approach.
Once data has been extracted and delivered to the host system in the form of files or staging tables, PL/SQL is a highly effective tool for managing and transforming that data.
Example 15 introduces five user stories that define the requirements and lay the groundwork for the techniques that follow.
ETL stories – Setting up an ETL
STORY #1
USER: AS a medical practitioner
I want: I would like my patient data identified on system x so that it can be moved and that it includes all my important data.
Why: So that I know what data will be moved and what types of data to expect.
Tests:
1 I would like to verify that all fields I regularly use have been identified so I can make reports about my patients.
STORY #2
USER: AS an analytics data user
I want: I would like my patient data identified in Story #1 to be moved nightly from my patient care system on system x to my data warehouse on system Y.
Why: So that I can discover patterns in what patients are doing.
Tests:
1 I would like to verify that all changes entered every day for a patient are being moved and integrated into our analytics instance
2. I would like to ensure that the daily patient data is being properly added to my patient analytics
STORY #3
USER: AS an analytics data user
I want: I would like my patient data identified in Story #2 to be checked on system Y so that I am sure that the correct number of table rows have been moved to the target system and that the count of all non-nullable rows also matches.
Why: So that I am sure all the identified rows have been accounted for.
Tests:
1 I would like to verify that the count of rows sent march the rows received by the target system. integrated into our analytics instance
2. I would like to ensure that all non-nullable rows have been accounted for.
STORY #4
USER: As a analytics user
I want: I would like patient data to be organized such that I can measure how often they are getting sick and what kinds of treatments they are being given.
Why: So that I can produce accurate metric report about my patients.
TESTS:
1 How many times has patient x been admitted
2 What is the top admitted diagnosis
3 How many instances of this top diagnosis have been recorded my month
STORY #5
USER: As a analytics user
I want: I would like patient data to be organized such that I can mine the data to discover unknown trends in the data
Why: So that I can use analytic tools to figure out new trends in my data.
TESTS:
1 How many times has patient x been re-diagnosed
2 What are the top procedures being prescribed.
Example 15
In both scenarios there usually was a need to interpret what the incoming data was and how it was structured. Initially in these cases the name of the game was to figure out what the data model of the original data looked like and then transfer it to whatever new model was needed. This in fact is why data pipelines are called ETL’s or ELT’s (Extract, Transform, Load or Extract, Load, Transform).
The primary difference between the two being when the transformation occurs. PL/SQL becomes important in all three parts of the EXTRACT, LOAD and Transform phases of an ETL since a way is needed to easily work with the data and change it to conform with whatever data model is being used. The character of the work is defined mostly by the work needed to decompose the data model of the source data using PL/SQL to read the data from the many tables used to store the source data and then finding a way to load the data into the destination system conforming to a different data model.
4.1 JASON Example
Robert Diekmann's Dev Demo Platform