ETL vs. ELT and other Data Integration Essentials

Traditionally, data integration has involved three key processing components: extracting data from the sources, transforming into the unified view, and loading the unified data into the destination.  In some cases, all three operations are contained in the same physical process, while in others, the steps are separated and run individually.  And the actions can come in different orders, with the most common ones ETL and ELT.

Data integration is often used for analytics purposes, sending the resulting data to analytical databases, data warehouses, or data marts, and organizing the data in specific analytic formats (star-schemas, multi-dimensional, etc.).  But new purposes for data integration include enriching data within an application such as CRM and bringing together all the data need for a new application such as customer or supply chain 360.

ETL stands for extract, transform, and load , one type of process used for data integration.  Each of the three steps is distinct and can be complicated.  But they are interrelated and come together into a complete process to deliver a unified set of data.

Here is how the steps work:

• Extraction pulls the data from the source.  For databases, extraction would involve using SQL.  For more complex applications, one might need to code to an API.  Or higher level ETL tools might abstract the extraction step into point-and-click operations.
• Transformation is the process of shaping the data into the final form needed.  This often includes cleansing (which may also be in the extraction process), blending, enrichment, aggregating, and organizing the data.
• Loading puts the data into the destination data store: a data warehouse, data mart, analytical database, or OLAP engine.  Some of the final organization of the data may be coded in this step.

ETL processes can be pieced together in ETL tools, hand-coded, or a combination thereof.  An ETL process could be a single synchronous process, or steps can be separated and run individually.  In the latter, there will often be an intermediate data store used to manage the in-flight data.

ETL processes can also range from simple – extract from a single source and transform it into an analytical form – to complex – bring together many sources to transform and feed into one or more sophisticated analytical schemas (e.g., star schema).  Organizations may have ETL processes that feed each other.  A complex process may feed a wide range of data into a data warehouse, and then a simpler process may move data from a data warehouse to a departmental OLAP cube.

ELT, or extract, load, and transform , is a new data integration model that has come about with data lakes and cloud analytics.  Data is extracted from the source and loaded into a destination still in its original or raw form.  The raw data is transformed within the destination to a second form that is then ready for analytics.

In practice, we see two primary use cases:

• Data lakes – Raw data is first extracted AND loaded into the data lake, either from traditional sources or newer complex sources (e.g., JSON, log files).  Once in the data lake, a data preparation process will transform the raw data into the final form, leave it on the data lake for use (e.g., in Hive) or send it to downstream BI or analytical destinations.
• Cloud analytics – Since most SaaS applications have complex APIs getting data out for analysis is difficult.  Some ELT tools provide connectors to SaaS applications making it easy to extract and load SaaS data into a cloud data warehouse.  Once in the cloud data warehouse, the data is then transformed into an analytics-ready format in a separate schema.

ELT processes on data lakes can be sophisticated as they are often dealing with complex data formats, multiple data sources, and a great deal of preparation.  Cloud analytics ELT from SaaS applications tend to be simpler, taking data from 1 or 2 SaaS sources, loading it, then transforming it into a denormalized form.

ETL is the more traditional method of doing data integration, has well-known best practices and approaches, and skills are easy to find.  There are a good number of tools on the market that support traditional ETL approaches.

However, developing ETL processes can be slow, cumbersome, and costly.  Since it requires specialized tools and skills, dedicated teams are needed.  These teams will typically have project backlogs, forcing analytics teams to wait.  Some of the legacy ETL products also carry a very high price tag.

ELT is more modern and embraces agile methodologies and cloud technologies.  As such:

• Teams can iterate quickly on projects using an ever-growing number of ELT no-code or low-code tools
• ELT data pipelines use modern cloud data warehouses such as Snowflake as the compute and storage engine reducing the cost and increasing the scalability of data pipeline processing
• One can break the ELT process into two pieces – the EL and the T – use tools that specialize in each piece and use resources that are best suited to each

Hence, ELT’s more modern approach can be far more efficient and effective.

At the heart of your modern data stack would be your analytics engine.  This would be a cloud data warehouse – Snowflake, Amazon Redshift, Google Big Query, or Azure Synapse – or a data lake engine such as Databricks Delta Lake.  The other tools in your data stack would integrate with your analytics engine, using it for compute and storage.  These tools would be:

• Data loaders – specialized EL tools that focus on (a) extracting data from data sources, especially ones with complex APIs such as SaaS applications and cloud services, and (b) loading that raw data into your CDW or analytics engine.  This includes vendors such as Fivetran, Xplenty, or Stitch.
• Data transformation platforms – tools that specialize in the “T” of the ELT stack, making it very easy to take the raw data loaded into the CDW or analytics engine and transform it into analytics-ready datasets consumable by BI tools.

Datameer is a powerful SaaS data transformation platform that runs in Snowflake – your modern, scalable cloud data warehouse – that combines to provide a highly scalable and flexible environment to transform your data into meaningful analytics.  With Datameer, you can:

• Allow your non-technical analytics team members to work with your complex data without the need to write code using Datameer’s no-code and low-code data transformation interfaces,
• Collaborate amongst technical and non-technical team members to build data models and the data transformation flows to fulfill these models, each using their skills and knowledge
• Fully enrich analytics datasets to add even more flavor to your analysis using the diverse array of graphical formulas and functions,
• Generate rich documentation and add user-supplied attributes, comments, tags, and more to share searchable knowledge about your data across the entire analytics community,
• Use the catalog-like documentation features to crowd-source your data governance processes for greater data democratization and data literacy,
• Maintain full audit trails of how data is transformed and used by the community to further enable your governance and compliance processes,
• Deploy and execute data transformation models directly in Snowflake to gain the scalability your need over your large volumes of data while keeping compute and storage costs low.

Datameer provides a number of key benefits for your modern data stack and cloud analytics, including:

• Creating a highly efficient data stack that reduces your data and analytics engineering costs,
• Allowing you to share the data transformation workload across your broader data and analytics team,
• Fostering collaboration among the data and analytics team to produce faster, error-free projects,
• Efficiently using your Snowflake analytics engine for cost-effective data transformation processing,
• Enabling you to crowdsource your data governance for more effective and efficient governance processes, and
• Improving data literacy to expand knowledge and effective use of your data.

All of these benefits will create highly efficient and effective analytics engineering processes and expand the use of data in the business.