ETL or ELT? What is the best for Vertica Database?

mpedroso

Hey guys

I'm developing a DW using Vertica and would like to know which option would be best on Vertica. Do ETL (Extraction, transformation and load) or ELT (Extraction, loading and transformation within the Vertica)?

Hugs.

marcothesane

Hi mpedroso

I would always go for E-LT. Working at internal HP IT a few years ago, we even forced our ETL tool - Informatica PowerCenter - to push down all transformations, so that instead of running derivations, lookups and such on the middle tier, PowerCenter generated INSERT.. SELECT scripts containing the transformations, so that they were executed within the database.

So - bring the data into Vertica using straight move - that is, into landing tables with the same structure as the sources - and perform all transformations using SQL - by hand or generated.

My half Swiss franc on that ...

mpedroso

Hi marcothesane,

Many thanks for the reply. I was already thinking about working with ELT. Now, with your confirmation I'll implement it that way.

Thank you very much.

mpedroso

Hi marcothesane

Thanks for answering my ETL or ELT question.

I would like to take advantage of the contact and ask how the process of transforming and loading data in Vertica using the ELT process is done. I think I need to use two schemas, a "STAGE" to load my raw data and a "DW" schema to load the data already processed and following a multidimensional model. Would it be this?
How could I automate the process of transforming and loading this data into Vertica? For this I could create a procedure?