Eliminating Data Silos, Part 3: Using Reverse ETL to banish silos forever

Our recent market survey showed us that data silos remain a huge problem. If you're one of the 86% of teams with data silos, this blog series is for you! We'll be giving actionable advice on technical and cultural issues before assembling all these posts into a single reference ebook.

Check out Post 1 on Creating a Data Dictionary that Actually Works.

Check out Post 2 on Building a Tech Stack & Data Warehouse for a Silo-Free Future

--- Silos happens when information flows into your warehouse but remains trapped within its walls, with no way to reach the teams that need it. 

Thankfully, there’s a great tool that helps you put raw data from your warehouse in a form that can be fed into your teams’ operational tools. This tool is Reverse ETL, also known as data activation.

In this post, we’ll cover:

• What exactly reverse ETL is 

• Why it plays a critical role in eliminating data silos

• How to form data governance and data integration strategies in preparation for building a reverse ETL pipeline. 

• How to set up your own reverse ETL flow the right way

• What security, maintenance, and support processes you’ll need once your reverse ETL flow is built.

What is reverse ETL?

Reverse ETL is the “opposite” of ETL/ELT (Extract-Transform-Load or Extract-Load-Transform)—two similar processes that extract data from its initial source, load it into your warehouse, and transform it for analytics use. With reverse ETL, you can take transformed data from your warehouse and map it back to the tools used by teams across your company. While some SaaS tools are now built as “warehouse native applications”—meaning they can sit on top of your data warehouse without a mediating tool in between—most SaaS applications still need a reverse ETL solution to make use of warehouse data.

While reverse ETL is an essential step in making data accessible, many companies stumble at the starting line by:

• Failing to build a clear data governance strategy. Knowing how you’ll manage your data after implementing reverse ETL is just as important as knowing how you’ll build your pipelines. 

• Failing to clean data properly. Enriching your data before reverse ETL will help you prevent sending contaminated insights back to your operational tools.

• Failing to use tools that integrate with reverse ETL solutions. Your reverse ETL solution needs to seamlessly connect your data warehouse to your operational tools though out-of-the-box integration. This will help you avoid costly custom development headaches.

• Failing to consider “real-time” data pipelines when necessary. Warehouses that support dynamic or “streaming” tables (such as Snowflake or Databricks) can enable real-time reverse ETL pipelines, which can matter if you’re using warehouse data for customer-facing applications that require real-time updates.  

How does reverse ETL eliminate silos?

There are many reasons why you need to implement a reverse ETL pipeline if you’re looking to reduce data silos across your business. Here are just a few:

1. Operational insights. Data in your warehouse is useless if your teams can’t access it in the tools they use on a daily basis. Reverse ETL makes this smooth flow of information possible!

2. Collaboration across departments. When different teams across your company use different systems, reverse ETL helps break down data silos by ensuring that analytics insights can easily be shared, regardless of the tool accessing them.

3. Personalization. Reverse ETL is essential for any company that wants to use data insights to improve the customer experiences. For example, if you’re an e-commerce company and want to deliver personalized recommendations reverse ETL will push the right data to your CRM tool and make it possible. 

4. Marketing automation. Reverse ETL lets marketing teams pull information gathered in tools owned by other teams into their CRM tools, ensuring that campaigns are based on the most recent and relevant data. 

5. Real-time synchronization. Businesses sometimes need real-time or near-real time access to their latest data, depending on particular use cases. Reverse ETL tools now allow for this to happen, particularly when they’re integrated with data warehouse services (such as Snowflake or Databricks) that stream continuously updated data into reverse ETL pipelines.

6. Data monetization. If you monetize your data, reverse ETL is an essential step in efficiently delivering data to external partners, vendors, or customers.

How to set up reverse ETL the right way

To set up your reverse ETL pipeline, follow these steps:

1. Select your integration tools

When setting up reverse ETL, there are a few categories of integration tools commonly used that you’ll want to be aware of. They are:

• Reverse ETL platforms. These are platforms that are specifically designed for reverse ETL processes, with user-friendly interfaces, pre-built connectors, and specialized features for data synchronization and activation. Reverse ETL solutions on the market today include Hightouch, Census, Segment, Airbyte, Skyvia, Rudderstack, and others.

• Change data capture (CDC) tools. These can determine and track data changes in your warehouse to trigger real-time data syncs in your reverse ETL flow. While many top reverse ETL solutions (including Hightouch, Census, and Airbyte) have built-in CDC capabilities if you’re using mainstream warehouses like Snowflake or BigQuery, other platforms (including Rudderstack) require third-party CDC tools.

• Custom scripts and APIs. If your reverse ETL solution lacks pre-built connectors for a specific data source or destination, use custom scripts to bridge gaps, enable integration, and handle complex transformations. Similarly, APIs can help you connect to external data sources that don’t integrate directly with your reverse ETL tool. One or both of these integration tools may be necessary, depending on your requirements. 

2. Prioritize use cases

Once you’re ready to start implementing your reverse ETL flow, identify and prioritize the highest-impact use cases. Focus on initiatives with the clearest potential to deliver immediate value to teams that need it most. Consider the following factors:

• Business objectives. These might include customer personalization, better customer experiences, unmet team needs, or improved operational efficiency.

• Data availability and quality. If teams are operating in silos without the data they need, consider prioritizing a fix for them first.

• Ease of integration with SaaS tools. If certain tools your teams use can integrate with your reverse ETL solution out of the box, great! Consider setting these up first, particularly if it solves multiple team problems at once.

• Stakeholder buy-in and enthusiasm. If certain teams are itching to use the benefits of reverse ETL and helping them won’t get in the way of solving larger problems, consider moving them up on the priority list.

• Initial data sets. When choosing data sets to feed into your reverse ETL pipeline, focus on data elements that 1) directly impact important operational decisions or actions and 2) are already well-defined and standardized in your data warehouse. 

3. Design your data pipelines 

Before doing the actual setup of your reverse ETL flow, you’ll first want to design what your data pipelines will look like. To do this, do the following:

• Visualize your data flow. If you haven’t already done this clearly when developing your data integration strategy, map out how the data in your warehouse will travel to the operational tools your teams use. Make sure to include any necessary transformations and validations, along with definitions of relationships between the source and target data fields.

• Use data mapping tools. Tools like Boomi, Skyvia, Transcend, and CloverDX can help make the design process clear and more collaborative for stakeholders. 

• Define specific pipeline components. These should include data extraction points from the warehouse, necessary cleaning and transformation steps, and loading mechanisms into target operational tools.

4. Build your first data stream, and test it

In your chosen reverse ETL tool, configure your first data pipeline. Start small with one high-impact use case to learn the ropes and navigate any unforeseen complexity before continuing to build your other pipelines. Your first data stream should include:

• Source data. Specify your data warehouse and the relevant tables/views you need to push out to your operational tools.

• Transformation (optional). Clean, normalize, and enrich the data for optimal use in your target application, if necessary. 

• Destination targeting. In your target application, map the data fields accordingly so they match the fields being pushed out from your warehouse.

• Triggers. If you haven’t done so in your strategy, define any conditions (e.g. purchase events, system events, etc.) that should trigger data pushes. Then implement these in your reverse ETL flow.

Once your first reverse ETL pipeline is set up, run tests to ensure data accuracy and that all application integrations are working correctly. Track the performance in your pipeline, and adjust any triggers or transformations as needed. Don’t forget to test for various scenarios, including edge cases.Then move on to build all your other data streams. 

5. Document your first reverse pipeline so you can replicate it

Once you’ve tested your reverse ETL solution and know everything works, create comprehensive documentation for every step and facet of your process. Document data mappings, transformation rules, integration workflows, and any dependencies. Not only will this give you a blueprint for repeating your setup for other data pipelines, but it will also be necessary down the line for troubleshooting, maintenance, and future transfer of knowledge as team structures change.

Note that if you’re truly interested in eliminating data silos, complete documentation isn’t a “nice to have”—it’s mandatory! 

6. Build your remaining data pipelines

With your first pipeline built, tested, and documented, you now have a workflow for setting up your remaining data pipelines. Even so, you’ll want to be aware of differences that might exist in your process between each one. These might include:

• Data sources and destinations. Each of your pipelines might extract data from different sources in your warehouse and serve distinct use cases. You’ll also be delivering data to a variety of operational systems (e.g. CRMs, marketing automation platforms, customer service tools, etc.), all of which might have specific requirements. 

• Data transformations. Pipelines may apply different transformations to your data, depending on what is required. They may need to be customized to meet unique conditions of each destination operational tool.

• Sync frequencies. Pipelines may vary in terms of how often they need to sync with warehouse data. Some syncs may be in real-time all the time, and others might be daily, weekly, or monthly, depending on urgency and use cases. 

• Data volumes. Depending on the scope of your use cases and the granularity of data needing to be activated, pipelines might need to be set up to handle different data volumes. Your reverse ETL solution should be capable of handling all volumes and be able to scale to accommodate growth.

• Data quality and validations. Different pipelines may require different data quality checks and validation rules to ensure accuracy and integrity before activation. This could become necessary due to different industry regulations and privacy standards. 

• Error handling and monitoring. Depending on which pipeline you’re building, you might have different needs when it comes to alerts and troubleshooting. Make sure you have all the correct monitoring systems in place to ensure data reliability and prevent problems downstream.

Continue testing until you know that every bit of data showing up in your operational tools will be clean and accurate. 

7. Implement security

Implement any necessary security measures to protect your data during transit and storage. These should include:

• Data encryption. Implement ELS/SSL encryption for all data transfers between systems and tools. When any sensitive data is at rest in databases or storage, you should use strong encryption algorithms like AES-256. Also manage your encryption keys with robust key management practices, including secure storage, access controls, and regular rotation. 

• General access controls and authentication. As discussed in our previous post, you should use role-based access control (RBAC) that allows you to set granular access permissions for users and systems based on roles and needs. You should also set up multi-factor authentication (MFA) for all connected user accounts and token-based authentication and authorization for API interactions. 

• Data masking an anonymization. Always hide personally identifiable information (PII) or other sensitive data elements when they aren’t strictly required for operational purposes. This will help minimize risk of accidental or unnecessary exposure.

• Compliance measures. Always make sure you’re in compliance with any necessary data privacy and security regulations (e.g. GDPR, CCPA, HIPAA, PCI DSS, and SOC 2). Also consider data residency requirements, which may mandate that you store your data within specific geographic regions.

8. Maintain maintenance and support processes

Don’t forget to define ongoing maintenance and support processes for your new reverse ETL flow. This should include:

• Data monitoring and alerts. Make sure you set up a process for monitoring and logging all data access events, including who, what, when, and where. You’ll need alerts for odd activity or anomalies, including suspicious access attempts or strange data movements. You should also implement ways to scan and review your data logs regularly. We’ll cover these in more detail in our next post on eliminating data silos!

• Error handling and troubleshooting. You’ll need a robust process to capture, log, and address problems efficiently. Develop clear troubleshooting guidelines for common problems to aid quick fixes. If errors arise, conduct thorough investigations to identify root causes, and implement preventive measures to avoid the same problem later.

• Change management. Implement formal change management processes to control updates to data sources, transformations, destinations, and system configurations. Build a system for robust testing, and maintain detailed documentation of all changes for future reference.

• Team sharing considerations. You already have your data access policy down as part of your data governance strategy, but what happens if a new employee or a newly created role requires access? Create a process for granting access, including directives on where permission needs to come from and who has final authority. 

• Tool updates and maintenance. You should always keep all your tools current to account for bug fixes, security patches, and new features. Thoroughly test tool updates in a staging environment before pushing them live to ensure compatibility with your existing systems and data flows.

• Regular reviews and audits. Periodically conduct reviews of your pipelines to evaluate performance, identify potential issues, and validate data quality.

9. Communicate across teams

Now that you have a perfectly engineered reverse ETL flow, it’s time to communicate about it across teams. Let people know that they have access to all data in your warehouse, and let them come up with ideas about how they might use it. Also make sure to alert any company leaders who need to be aware of the new update. 

—-

Great! Hopefully this all should get your reverse ETL working right. However, it’s worth noting that reverse ETL works best when you have a few things in place already.

What should put in place for reverse ETL to work?

For your reverse ETL flow to work properly, you’ll want to have two things in place: a data integration strategy, and a data governance strategy. These are obviously larger topics, but here are a few places to start:

1. A data integration strategy

When implementing a reverse ETL solution, your data integration strategy should come first. This means that you need a comprehensive plan that outlines how data will move from your warehouse into your other tools. When building your strategy, make sure to consider the following elements:

• Data sources and destinations. If you haven’t yet created a complete inventory of all relevant data sources at your company, do so. Map out the data models and structure of sources and destinations to ensure they’ll be compatible.

• Data pipelines and transformations. Figure out how your data will flow from source to destination. Implement rules for data cleaning, formatting, and structuring, and then map data fields accurately to ensure consistency across systems. Finally, make sure you understand what types of data your teams will need access to in their operational tools, and clearly determine frequency of data updates if you’re not using real-time or “streaming” reverse ETL. 

• Testing and iteration. Define how you’ll test your reverse ETL solution and iterate on it, if necessary. You’ll need thorough tests to validate data accuracy, completeness, and consistency in destination tools.

• Monitoring and maintenance. How will you monitor your data flow, quality, and system performance and promptly address any errors?

2. A data governance strategy

A data governance strategy is a comprehensive plan that outlines how an organization manages its data. To build a data governance strategy, do the following:

• Confirm or establish data ownership and stewardship. If you haven’t already done so when setting up your data dictionary or tech stack and warehouse, assign clear ownership and accountability for all data elements in your pipeline. 

• Define your policies. Clearly define who can access what data under what conditions and for what purposes, and put data security measures like encryption and access controls into place. Establish data retention policies and procedures for archiving or deleting data.

• Implement data quality management. Define data quality metrics and standards for your data sets, including ways to measure accuracy, completeness, validity, uniqueness, and timeliness. Next, choose data monitoring tools that will help you identify and address errors and inconsistencies. These can include platforms like Datadog, Dynatrace, Rollbar, Harness, and others.

• Choose the right tools for governing your data. Tools like Clickup, Informatica’s Axon, Alteryx, and Precisely’s Data360 Govern are great for helping you manage your data. When choosing a tool, consider scalability, integration capabilities, data lifecycle management, ease of collaboration, and audit and compliance monitoring.

• Develop training for your team, and set up standards for continuous improvement. You’ll eventually need to teach your data owners, stewards, and users how to perform their data governance roles in context of data security best practices and compliance requirements. Since data can change over time, build in a process for reviewing and updating policies as needed, and collect feedback from stakeholders so you can identify any gaps and inconsistencies in your plan. 

This feature is not included

By following the tips and steps in this reverse ETL guide, you can establish a reliable and efficient reverse ETL process that meets your specific business needs and supports data flow between your warehouse and operational applications. Once this is in place, you’ll have broken through the walls of any data silos that were still in place at your company!