Declarative Design

Conventional ETL

Consider, for example, a common case in which sales figures must be summed over time for different customer age groups. The sales data comes from a sales management database, and age groups are described in an age distribution file. In order to combine these sources and then insert and update appropriate records in the customer statistics systems, you must design each step, which includes

1. Load the customer sales data in the engine

2. Load the age distribution file in the engine

3. Perform a lookup between the customer sales data and the age distribution data

4. Aggregate the customer sales grouped by age distribution

5. Load the target sales statistics data into the engine

6. Determine what needs to be inserted or updated by comparing aggregated information with the data from the statistics system

7. Insert new records into the target

8. Update existing records into the target

Conventional ELT

With declarative design, you just need to design what the process does, without describing how it will be done.

In our example, what the process does is

• Relate the customer age from the sales application to the age groups from the statistical file

• Aggregate customer sales by age groups to load sales statistics