Kettle – Best Practices

 

During my ETL development work, there have been instances when I had to do a DDL change in the Data Mart tables, sometimes for an enhancement or because of bug fixes.This involves fixing the Kettle Transformation as well. For this you need to follow this simple yet useful practice.

•Clear Database Cache whenever you do a DDL operation on Lookup or Target tables otherwise Spoon might mark Transformation as Invalid on Validation.

Whenever we introduce a table in Kettle, it keeps a cached version of the table metadata into the repository so as to optimize the transformation viewing/editing. But it doesn’t refresh this metadata from database every time we open the transformations as its memory expensive operation. For this reason we have to manually clear this cache whenever we make a change to the Database  tables.

•For this you need to click on the Target (or Dimension Lookup) Step and then Click on SQL and then hit Clear Cache. It’ll ask you to Clean Cache only for this Table on Entire Database.

Kettle – Best Practices

In tune with the last post, I’ll discuss more about ETL Best practices which I follow with Kettle.

• Enable cache for lookup on Dimension tables.

This improves the Performance of your Transformation for Small/Medium size Dimension tables. But you need to monitor the Logs to check whether It is beneficial or not. In some cases it might add un-necessary load on ETL server.

Its always a dilemma for ETL developer when it comes to Caching of Dimensional tables while doing the lookups. Usually right approach is collect Performance data, for the ETL where Caching is Implemented. This data usually includes Time taken,

• To fetch Dimension table into memory
• To filter this data
• To update the Cache if underlying lookup table data gets changed.

Another good practice which I learned from Informatica, is to Place these Cached files on  Faster Drives.