Reverse ETL: what it is and why it works so well

Data pro­fes­sion­als are per­haps rivalled only by mem­bers of the armed forces in their fond­ness for acronym-laced jar­gon. The indus­try is swim­ming in an alpha­bet soup of TLAs (three-let­ter abbre­vi­a­tions) that serve to baf­fle out­siders. And now there’s a new term to add to the lex­i­con: it’s called reverse ETL (extract, trans­form, load).

It’s an impor­tant con­cept for any­one with a role in man­ag­ing infor­ma­tion. In a nut­shell, it describes the process of trans­fer­ring data back to the sys­tems from which it orig­i­nat­ed. 

In the long-estab­lished tra­di­tion­al ETL process, a com­pa­ny gath­ers data from across the enter­prise and stores it all togeth­er in a data ware­house. Hav­ing every­thing col­lat­ed in a cen­tral repos­i­to­ry is great for ana­lyt­ics pur­pos­es, but there is a dis­ad­van­tage: the mate­r­i­al is stuck in one place. The var­i­ous appli­ca­tions from which the data was obtained, such as cus­tomer rela­tion­ship man­age­ment (CRM) sys­tems, are like­ly to be out of sync with each oth­er. They may con­tain obso­lete and/or incom­plete infor­ma­tion, which makes life dif­fi­cult for the front-line users of those sys­tems. 

In this way, the data ware­house – cre­at­ed to pre­vent silos – becomes the biggest silo of all. It means that the ELT process, which helped to move data gen­er­at­ed by sev­er­al appli­ca­tions to the ware­house, needs to be reversed to return mate­r­i­al to these appli­ca­tions.

“Reverse ETL means using your data ware­house as a hub,” says Rob Jones, CEO at data ser­vices provider Qbase. “This allows you to com­bine all your incom­ing data feeds into a sin­gle ver­sion of the truth, apply enhance­ments and share these ‘gold­en’ records back to the orig­i­nal data sources. This will make the data in all your appli­ca­tions more accu­rate and com­plete. This syn­chro­ni­sa­tion is a key prin­ci­ple in mas­ter data man­age­ment.” 

Sales and mar­ket­ing appli­ca­tions – Sales­force, Hub­Spot and Tableau, for instance – are the most cit­ed exam­ples of reverse ETL des­ti­na­tions. 

Jones explains that one of the main rea­sons for engag­ing in reverse ETL is to pro­vide front-line staff with enriched data. “For exam­ple, a sales­per­son using a CRM appli­ca­tion will be able to offer a giv­en cus­tomer far bet­ter ser­vice if they can see not only the data in the CRM, but also infor­ma­tion about the cus­tomer that has been gen­er­at­ed else­where – that person’s brows­ing behav­iour on the company’s web­site, for instance. This insight helps them to form a bet­ter under­stand­ing of what the cus­tomer is real­ly inter­est­ed in, enabling them to have a more tai­lored and effec­tive con­ver­sa­tion with them dur­ing their next call.”

Why don’t these appli­ca­tions sim­ply access data from the ware­house when­ev­er it’s need­ed? 

Dar­ren Tim­mins, chief tech­nol­o­gy offi­cer of ana­lyt­ics provider Intui­ta, says there are four main rea­sons why it’s worth going to the trou­ble of using reverse ETL. The first con­cerns the pre­ven­tion of bot­tle­necks. Source appli­ca­tions are built to han­dle many simul­ta­ne­ous enquiries on small data sets. Exam­ples of this include sales reps run­ning CRM requests. Data ware­hous­es, by con­trast, are built to run large-scale enquiries sub­mit­ted by a small­er num­ber of users. Reverse ETL enables each sys­tem to play to its strength. 

The sec­ond rea­son cen­tres on resilience. Rely­ing on a cen­tral repos­i­to­ry to answer all queries would ren­der the sys­tem vul­ner­a­ble to total fail­ure in the event of an out­age at the ware­house. Push­ing data back to the appli­ca­tions enables them to oper­ate autonomous­ly even if the ware­house is down. 

Third is the mat­ter of cost con­trol. Data ware­house tech­nolo­gies are pow­er­ful, but they come with big over­heads. Reverse ETL allows data to be queried on local, nim­ble and low-cost infra­struc­ture. 

And last, but not least, comes net­work secu­ri­ty. Tim­mins observes that “push­ing only the min­i­mal amount of data required to a sys­tem reduces the foot­print of data that could be viewed by a third par­ty if part of the sys­tem were to be com­pro­mised”.

Exe­cut­ing a reverse ETL is made eas­i­er by the exis­tence of spe­cial­ist tools. Among the most pop­u­lar of these are Cen­sus, High­touch, Groupa­roo and Seek­well. 

But is it the best approach? There are scep­tics. Mark Shel­don, chief tech­nol­o­gy offi­cer at Side­trade, an arti­fi­cial intel­li­gence plat­form for finance teams, believes that the whole con­cept can be made redun­dant by sync­ing data in a more obvi­ous way. 

“Reverse ETL was dead before it was born,” he argues. “Why com­pa­nies are adding anoth­er lay­er of com­plex­i­ty and tool­ing to inter­face with sys­tems of exe­cu­tion is beyond me.”

The appli­ca­tion pro­gram­ming inter­face (API) is usu­al­ly suf­fi­cient, he explains, adding: “With reverse ETL, in essence all we want to do is push insights to their final des­ti­na­tion – and this process should be as sim­ple as pos­si­ble. Every front-line sys­tem on the plan­et has an API designed for exact­ly this pur­pose, yet we seem reluc­tant to use them. My advice to any chief tech­nol­o­gy offi­cer is: don’t con­cen­trate too much on reverse ETL. Keep inte­gra­tions sim­ple, but focus every­thing on the exe­cu­tion by track­ing a suc­cess met­ric and improv­ing iter­a­tive­ly over time.”

Oth­er crit­ics note that a data ware­house can be opti­mised to run oper­a­tional ana­lyt­ics with­out push­ing the data back to appli­ca­tions. But advo­cates of reverse ETL argue that is a ‘clean­er’ approach because the mate­r­i­al is kept in a sin­gle loca­tion, there­by reduc­ing the risk that errors will creep in. More­over, it can even be made cheap­er, by using open-source dis­trib­uted SQL data­bas­es rather than expen­sive lega­cy providers. 

In any case, such eso­teric argu­ments are like­ly to be of lim­it­ed inter­est out­side the pro­fes­sion. End users sim­ply want the most cur­rent and com­plete set of records pos­si­ble. To them, ques­tions about whether they should obtain this mate­r­i­al through API access, reverse ETL or query­ing the ware­house each time are irrel­e­vant.

Nonethe­less, reverse ETL is a nice option to have. It means that data can be col­lat­ed and crunched in one place using heavy-duty ana­lyt­ics and also fed back into front-line appli­ca­tions to equip sales staff with all the infor­ma­tion they need. For many organ­i­sa­tions, the process may be the best, and maybe the only, way to achieve this.