Guide to Data
Integration:
Going Beyond ETL
with Metadata
Data integration refers to combining data from disparate sources in different formats and
structures into a single consistent data store. This single, coherent view of data promises to
eliminate data silos, so organizations can use it to enable analysis, data models, and
decision making.
How and where this data is stored has evolved over the years. Many organizations have found
themselves years-deep into data warehousing projects, relying on many disparate tools to
move and modify data from one system to another.
The earlier versions of “extract, transform, and load” (ETL) tools were designed to extract data
from a single source, convert the data into the format of the target system and then load the
data into the destination system.
This works well for one simple use case, but cannot handle data from various sources that
require disparate ETL tools and processes, resulting in a complex and cumbersome system to
manage. Despite the challenges, the industry has been all-in on ETL solutions in recent years.
However, a new integration pattern is emerging where the data store is represented through
metadata, so that data engineers and business users alike can transform and deliver data
anywhere – without unnecessary replication and failure points.
Guide to Data Integration: Going Beyond ETL with Metadata
The Evolution of Data Integration
Data integration has evolved significantly in unparalleled flexibility and scalability, they
recent years, driven by the increasing volume were also highly complex and often posed
and velocity of data. This evolution can be challenges in integrating with existing
traced through distinct generations, each systems. Some of the risks associated with
characterized by unique approaches and this generation include potential data loss,
challenges. quality issues, and operational challenges.
The first generation predominantly involved The fourth and latest generation leverages
manual scripts and custom coding tailored AI-powered data ingestion. This approach is
for each data source. While this method characterized by its self-learning capabilities,
had the advantage of low upfront costs, it high levels of automation, and low latency in
suffered from limited scalability and posed data processing. However, as it's a relatively
maintenance challenges. The risks with this newer method, its adoption is still limited,
method included data inconsistency, quality and it tends to be more costly than previous
issues, and potential security vulnerabilities. methods. A critical aspect of this generation
is the enforcement of rule-based policies.
The second generation saw the rise of These policies are essential to address
Extract, Transform, and Load (ETL) tools. potential data privacy and security concerns,
These tools brought a higher degree of ensuring that data remains both secure and
automation and scalability to data ingestion. compliant with regulations.
However, they also introduced higher
upfront and ongoing costs and came with
their own set of complexities. Integration of
data through ETL tools posed risks such as
potential data loss, quality issues, and limited
capabilities in processing real-time data.
In the third generation, the focus shifted
to stream processing and microservices,
offering real-time data ingestion and
processing. While these methods provided
2
Guide to Data Integration: Going Beyond ETL with Metadata
ETL vs. ELT vs. Reverse ETL
With the rise of powerful cloud data data analytics platform, improved targetability,
warehouses such as Redshift, Snowflake, and enhanced business user insight. While
and BigQuery, the trend has shifted towards departments like sales, marketing, and
ELT. These data warehouses can scale up finance reap the most benefits of reverse
and scale out to process large data sets ETL, in reality, any department can take
on demand, so companies no longer have advantage of the insights generated in the
to wait for data transformations and are no data warehouse. For data teams, the main
longer bound to the limitations of a certain benefit is that building integrations with
model. They can build different data models customer-facing SaaS applications is easier
and perform the relevant transformations as and quicker. In addition, the data models can
the business demands. be more flexible, allowing even more insights
to be delivered to the right teams.
The main difference between ETL and ELT
is that in ELT, all processing and analysis are Another benefit and often the primary reason
performed in the data warehouse, enabling for building a reverse ETL solution is to have
data centralization and flexible data models. greater flexibility and richer functionality than
Regardless of whether the transformations off-the-shelf customer data platforms (CDP)
were done before or after the data was loaded provide. CDPs are software systems that
in the data warehouse, the outcome of the enable organizations to unify customer data
transformations is the data that is analytics- from multiple sources and provide it to various
ready and provides value. Reverse ETL customer-facing applications in a consistent
closes the loop by taking this high-quality format. Some of the biggest players in this
and valuable data from the data warehouse, highly segmented market are Segment,
transforming it as needed, and loading it back Emarsys, and Exponea. CDPs can support
to operational systems. Hence, the word various customer-facing applications, including
“reverse” in the name refers to the reversal of customer relationship management (CRM),
source and target systems and not necessarily marketing automation, and e-commerce. CDPs
the order of the steps. The data warehouse have very limited transformation capabilities,
becomes the source of the data, and the and their data structures are extremely rigid.
targets are operational systems such as those Reverse ETL opens the possibility of delivering
related to CRM, finance, and marketing. customer insights to the whole organization
instead of the select departments, such as
Reverse ETL has many benefits, including sales and marketing.
increased return on investment (ROI) on the
3
Guide to Data Integration: Going Beyond ETL with Metadata
Pitfalls to Avoid When Choosing
Integration Tools
Switching between integration styles for each Purchasing more tools – that are inherently
use case creates challenges that highlight limiting – to suit more integration styles is
the limitation of data integration solutions not a scalable solution. The data integration
today. Buying multiple tools to cover paradigm is now shifting to adapt to the
disparate integration needs is expensive increased volume and velocity of data.
and a nightmare for ongoing maintenance.
However, limiting your integration styles is What to look for in a scalable
still not optimal for the following reasons. integration solution
1. Ballooning Infrastructure Costs If you only see data needs in your
Transforming data in the warehouse organization increasing, then you should
takes a lot of expensive compute. consider future-proofing your integration
This can also lead to the creation of solution with a converged tool that supports:
exponential data tables over time,
incurring even more expense in • More than one integration pattern,
management costs. i.e. ETL only covers SaaS to data
warehouse
2. High Maintenance Costs
Managing disparate tools creates • Streaming or real-time data processing
a system prone to errors. Lost
• Application to Application data flow
information and slowdowns may be
costing an organization more than it is • Robust library of existing connectors
benefitting.
• Ability to build new connectors to any
3. Rigidity & Lock-in system in a timely manner
Too often it’s a system-specific
• Flexible data-model that is not
integration that goes one way. For
predefined for a given SaaS service
example, ETL/ELT is only able to use
SaaS apps and similar systems as • Comprehensive control over all
sources and data warehouses as objects within a data set, i.e. not
a destination. What happens when limited to a subset of objects with a
new use cases require a different given connector
combination of data systems
outside of the bounds of any one
integration style?
4
Guide to Data Integration: Going Beyond ETL with Metadata
Solving Data Integration Challenges
with Metadata
Abstractions are a powerful software design Why abstract the data?
construct. By abstracting the data layer into
a collection of metadata, we are able to 1. Stop unnecessary data replication:
manage data in a much more flexible and Materialize the data at time-of-use.
agile way – much like containers have for 2. Process data at any speed on any
compute, or virtualization for networking. The compute with batch, streaming
same concept applied to data is referred to or real-time processing on one or
as “Data Products”. multiple clouds.
Data Products know where the data is, 3. Unmatched collaboration: Perform
what it looks like, the schema, metadata, common data functions on the
validations, samples, documentation, abstractions without designing to
access control, lineage etc. However, they unique schemas or formats.
don't contain a copy of data. Thus they can
4. Move beyond version and change
provide a common interface to any data,
control with Data Products that
regardless of source, format, and velocity.
represent a live view of the data and
The result is a common layer for discovery
automatically track and record version
and collaboration between producers and
changes, eliminating any worry about
consumers of data products.
data lineage or completeness.
5
Guide to Data Integration: Going Beyond ETL with Metadata
The Next-Generation: Delivering
Data with Metadata
Approaching integration with metadata organization and delivery. Data products
abstraction in the form of Data Products is can be included as a part of a data
enabling companies today to move beyond architecture or solution to streamline any
ETL, ELT, Reverse ETL, and other integration kind of data pipeline.
styles of the past to integrate data more
powerfully and flexibly – without worrying Data as a product is one of the four principles
about the minute details of data itself. Data of data mesh and is a fundamental part of
Products fit into more systems without how a data mesh solution functions. Data
worrying about data formats and structure. products are created by domains, with each
domain being responsible for meeting the
Data Products can be either auto- or user- needs of the users. Domain teams are in
generated, and once a product template charge of curating and processing their data
is set up, it can be standardized to make into data products, as well as making these
the terminology and metadata consistently data products available to users
formatted and sorted for more efficient
Data Apps
(AI, BI, Operations)
Unified
Capabilities
Nexsets: Data as
a Product
Continuous
Metadata
Intelligence
Universal
Connector
Architecture
SaaS, On-
Premise Hybrid-
Multi Cloud
6
Guide to Data Integration: Going Beyond ETL with Metadata
Data fabric pulls in raw data and tags Data solutions that combine different
and processes it. The data preparation elements can also use data products. The
and delivery layer then uses metadata to creation of data products can be built into
identify and transform the raw data into data custom solutions and configured for delivery
products to be delivered to appropriate in different parts of data solutions to suit
users. This automated generation and a specific enterprise or use case. Data
delivery of data products are curated by products streamline any data pipeline and
custom request to deliver the data product as add a pre-configured level of governance
requested in the format needed. and quality control that are manually built for
standard data pipelines.
7
Guide to Data Integration: Going Beyond ETL with Metadata
Features of a Metadata-driven
Architecture
The next generation data integration is not a collection of point tools, but a "Converged"
solution. One tool that solves for all the integration patterns. How is that possible? Not by
writing more code than anyone before, but by having a smarter architecture.
Old Way New Way
Fragmented Point Tools Converged Integration
2017 2019 2021 2023
SaaS ELT DWH SaaS/API API/SaaS
DWH R-ETL SaaS FILES FILES
Files ETL DB DB, DWH DB, DWH
APP/API iPaas APP/API STREAM STREAM
Data
Evente Streaming DB/DWH/API EVENTS Product EVENTS
Data-API
DB/API Realtime API
API Proxy Multi-speed Processing
Batch, Streaming, Real-time
1. Bi-directional connectors: Read and Write to any File, DB, DWH, API, Stream, or Event,
so gone are the days of uni-directional systems
2. Virtualized Data: Encapsulate the understanding of data, including data model,
transforms, filters, access control, validation rules, documentation as metadata.
3. Multiple, Dynamic Runtimes: Means an A↔B integration design pattern can map into
the right processing engine at run time - streaming, batch, and real-time
8
Conclusion
The data integration landscape hasn’t changed fast enough to catch up with the
exponentially growing amount of data, and number of data systems, that companies
work with today. As the quantity and complexity of data that every company uses
increases, data integration too will need to evolve to meet those needs without also
exponentially growing in cost and complexity.
Data integration has evolved to meet the requirements to store and analyze more
data from more places. Companies who adopt a metadata-based approach will benefit
from reduced costs, increased data agility and collaboration, and fewer roadblocks to
delivering valuable data projects.
Nexla is the only data engineering platform with a paradigm-shifting approach: Instead
of relying on leaky data pipelines, Nexla abstracts the data at its source & delivers
transformed data at time-of-use – giving your data engineering team time back to work
on the innovative projects that fuel the business.
Ready to tackle your data integration challenges?
• Schedule a free consultation to discuss your unique needs
• Read our get started guide
• Contact us with any questions at
[email protected] nexla.com
