ETL - Designing

ETL design is one of key processes of a DWH project. Improperly designed ETL systems lead to considerable complexity and significant operational problems in later phase of the project which costs extra time and resources due to issues and required rework.

To design a good ETL system, ETL designers should follow basic steps in respective phases of the project as below -

Requirement Gathering

Data Analysis and Profiling(5D)

            Data Integrity

            Data Volume

            Data Distribution

Frequently Asked Questions - Datastage

Questions on Configuration File and Partitioning 

1) What is Configuration file, its contents, usage, and node pools?

2) What is difference between resource desk and resource scratch desk space in configuration file.

3) DataStage Collector methods, which collector method to use if records should be collected in a sequential way by 3 target files?

4) What all stages require Hash partitioning and why ?

5) What is the difference between Hash and Modulus partitioning ?

6) What will happen if the data is not sorted on keys before join stage ?

7) In a parallel job with 4 nodes configuration file, containing 1 sequential file stage, 2 transformers and 1 odbc stage. how many processes will be generated ?

Questions on Datastage Components

Datastage Job Extraction - Performance Tuning

Extraction from Database - 

Filtration
1) Try to have the constraints in the 'Selection' criteria of the jobs itself. This will eliminate the unnecessary records even getting in before joins are made.
2) Using a constraint to filter a record set is much slower than performing a SELECT … WHERE….er joins in SQL itself if possible.

Sorting - Try not to use a sort stage when you can use an ORDER BY clause in the database. 

Join - Always prefer joins in SQL itself if possible.

Datastage for Beginners

This page lists Datastage topics which should be covered for grasping basic understanding of ETL Datastage (Parallel Jobs). All Topics are listed in a logical order with required questions to be asked and practicals to be performed.

1)  What is Datastage

• Pipelining Concept
• Partitioning Concept
• Partitioning in Datastage
• Collecting in Datastage

Questions –

• Why is ETL needed?
• What are the benefits of partitioning? 
• Why Collecting is required?
• Why different type of partitioning and collecting methods, which is the best, fastest, slowest and why? 

2)  Configuration File in Datastage

• What is a node
• What happens in background when a Datastage Job runs

3) Datasets

• Descriptor file
• Data(Binary file)
• Datastage management utility
• orchadmin utility

4) Sequential File

• Read a Delimited File
• Read a Fixed Width File
• Read by File Pattern
• Read by Filter option
• Read by Schema file
• Read from multiple nodes
• Number of readers per node

5) Funnel Stage

• Continuous Funnel
• Sort Funnel
• Sequence Funnel

6) Copy
7) Filter

8) Transformer
9) Modify
10) Sort
11) Remove Duplicate
12) Aggregator
13) Change Capture
14) Join
15) Lookup
16) Merge
17) Pivot Enterprise
18) Sequence Job
19) Director
20) Administrator

Reference - ibm-datastage-reference-links

ETL Recoverability

ETL design should be such that if required, It can be set back to any stable point from current state. For example, when ETL run is completed with some data issues from source files/tables, it’s required to seamlessly put the ETL system back in original state before this run has happened. If the system satisfies above said condition it’s said to be recoverable.