ETL versus Lightning Connect External Objects: Dispelling Rumors

ETL versus Lightning Connect External Objects: Dispelling Rumors

April 02, 2015 0 Comments

word cloud of common data sources

There are many use cases where ETL is superior to Lightning Connect, and many cases where Lightning Connect is superior to ETL.

Recently, you could say that I’m living the dream of standards-based data connectivity. I’ve been consulting on a number of Salesforce1 Lightning Connect External Object integration projects with OData. At the same time, I have been consulting on some Extract, Transform, Load (ETL) projects using ODBC/JDBC targets and sources for Salesforce1 with tools like Microsoft SSIS, Informatica PowerCenter, IBM DataStage, Pentaho Data Integrator and SAP BODS.

While working on these projects, I keep getting asked which is better: ETL or Lightning Connect. I get the feeling that the stage is being set for a heavyweight data integration battle to the death! In fact, the two aren’t in conflict, and are often complementary.  Lightning Connect remains new to the Salesforce1 platform (Winter ’15), so the confusion is understandable.

Today, I want to share my experiences with both and set the record straight.

1. Lightning Connect replaces ETL


I’ve run across several cases where ETL strategies are being used when Lightning Connect would be a better fit. This is because Lightning Connect did not exist until now, and those strategies will be reevaluated.  However, both Lightning Connect and ETL are data integration strategies for specific use cases.  While there are cases where project requirements overlap, each has a specific purpose in data integration.

The major difference is that ETL physically moves data in and out of the platform while Lightning Connect does not.  To figure out which is the best strategy, ask yourself “Do we need to persist the data in the platform, or not?”  Consider accounts, contacts or other master data from external systems that need to be loaded into Salesforce in batches.  On the other hand, it may not make sense to load orders and order line items from the last 10 years into Salesforce.

ETL actually complements Lightning Connect behind the firewall because it builds data warehouses, marts, lakes and operational data stores which are ultimately consumed as external objects for 360-degree views of different Salesforce objects.

2. Lightning Connect is risky because it is first in real-time external data strategy


Salesforce will be the first to tell you this is untrue. As stated in Knowledge Article Number 204599:

Lightning Connect is new way for Data Integration–it is commonly known in the industry as Data Federation or Data Virtualization (similar to Siebel’s EBC, Oracle’s DBLink, PostgresSQL’s Foreign Data, SQL Server’s External Data Source).

While Lightning Connect is new to the Salesforce1 Platform, it’s not new to the industry.   None of the data federation strategies mentioned above from Oracle or Microsoft have replaced the need for ETL, nor is that the intent.

Progress® DataDirect® has more than 20 years of experience with the mentioned data federation strategies using open data standards. It’s no coincidence that we’re the first and only generally available player providing enterprise OData connectivity with Lightning Connect, nor is it a coincidence that we have over 11,000 users already.  To be completely transparent, I even wrote a “behind the scenes” article about my Lightning Connect integration experience with DataDirect Cloud on the Salesforce Developers blog.

3. Salesforce is Adding Support for Native Reporting on External Objects to Replace ETL


It’s true that native reporting is not supported with external objects today.  I’ve heard from the Salesforce community that it is planned for later this year (I feel like I should say safe harbor).  Regardless, the answer to rumor #1 still applies.

4. OData is Not as Secure as ETL Data Standards ODBC and JDBC


OData is an OASIS-approved industry standard for building RESTful APIs for which Lightning Connect is a consumer.  While OData stands for “open data protocol,” it does not mean the data is publicly available.  Authentication is configured from Salesforce External Data Source configuration, and DataDirect Cloud supports secure authentication while encrypting all data in transit over HTTPS.

5. Progress DataDirect Cloud is Only Useful for Lightning Connect


It’s true that DataDirect Cloud provides an OData service for data sources behind the firewall.  However, did you know that it also connects to Salesforce1 in the other direction, providing direct SQL access to the platform?  DataDirect Cloud ODBC clients can be used as both ETL sources and targets.

Developers Have the Freedom to Choose

While Lightning Connect will inevitably replace several projects where ETL integration was the only option at the time, hopefully it’s clear there is no dramatic battle between the two.  There are many use cases where ETL is superior to Lightning Connect, and many cases where Lightning Connect is superior to ETL. The freedom to choose the best solution is what I’m evangelizing.

Talk to Sumit Sarkar

What are your thoughts? Let me know in the comments, tweet me at @SAsinSumit or contact me directly at: And if you’re interested in giving DataDirect Cloud a try, you can pick up a trial on our website.

Sumit Sakar

Sumit Sarkar

Technology researcher, thought leader and speaker working to enable enterprises to rapidly adopt new technologies that are adaptive, connected and cognitive. Sumit has been working in the data access infrastructure field for over 10 years servicing web/mobile developers, data engineers and data scientists. His primary areas of focus include cross platform app development, serverless architectures, and hybrid enterprise data management that supports open standards such as ODBC, JDBC, ADO.NET, GraphQL, OData/REST. He has presented dozens of technology sessions at conferences such as Dreamforce, Oracle OpenWorld, Strata Hadoop World, API World, Microstrategy World, MongoDB World, etc.

Comments are disabled in preview mode.
Latest Stories
in Your Inbox

Subscribe to get all the news, info and tutorials you need to build better business apps and sites

More From Progress
2020 Progress Data Connectivity Report
2020 Progress Data Connectivity Report
Read More
Getting Ahead of the Hybrid Data Curve
Read More
570x321__Top 7 Considerations Before Choosing a Chatbot for Your Enterprise
Top 7 Considerations Before Choosing a Chatbot for Your Enterprise
Read More