PortablePortable Logo

ETL Best Practices and Design Principles You Need To Know

Ethan
CEO, Portable

Before we dive into the top 9 best practices for ETL, let's quickly define ETL, summarize the value proposition of a data pipeline, and outline the steps to getting started with data integration.

ETL Definition: Extract, transform, and load (ETL) involves pulling information from your enterprise data sources, transforming the data into a useful format, and loading the information into your processing environment for analytics.

The Value Of ETL: ETL processes are critical to any scalable data integration strategy. By centralizing data from disparate sources into your analytics environment, an ETL solution can make it simple to conduct complex analysis, build machine learning models, and power better decision-making within your business.

Getting Started With ETL: Before you start replicating raw data into your data warehouse or data lake, it's important to identify the mission-critical systems within your company, outline an ETL architecture for moving data at scale, and prioritize the organizational workflows that will create the most business value.

Advanced Use Cases For ETL: As your enterprise creates more value with data, it can make sense to start unlocking more complicated use cases that create additional value - For instance, automating workflows in real-time, training machine learning models, or building data products for external consumption.

Now that we have a baseline for discussion, let's summarize the top 9 best practices for ETL.

What Are the 9 Best Practices for ETL?

  1. Drive Business Value

  2. Only Move The Data You Need

  3. Minimize Pipeline Logic

  4. Be Prepared for Scale

  5. Monitor Your Pipelines

  6. Fail Loudly

  7. Be Ready To Add Systems, Always

  8. Pick the Right ETL Solution for the Job

Drive Business Value

I might sound like a broken record on this one, but if you're putting in place any data infrastructure (ETL tools, data warehouses, visualization tools, etc.) without first identifying the most impactful ways to drive revenue, reduce costs, or improve strategic decision making, you're wasting your time.

What are the 3 ways to create business value from data integrations?

  1. Analytics - Business intelligence teams organize all of their data into a centralized location to power insights and dashboards. This empowers business leaders with data at their fingertips to make better strategic decisions

  2. Process Automation - Data teams help the company save time by automating manual tasks and business processes. Instead of manually copying information from one system to another, it should take place automatically

  3. Product Development - Product teams can use turn data into valuable products that customers can purchase. The products can include insights, automated workflows, or raw data feeds for monetization

Only Move The Data You Need

There are several benefits of minimizing the amount of data you extract from different sources across your enterprise:

  1. Reduced costs - You only need to store, process, and deliver the data you need

  2. Simpler to manage - You don't have as many moving pieces in your pipelines

  3. More discoverable data assets - You can find tables, fields, and values more quickly

  4. Fewer security risks - You can more easily enforce policies and secure sensitive data

You can always add more data over time, but if you start by moving too much data, it can be difficult to identify which data is being used, how it is being used, and to remove the data after the fact.

While there are lineage tools and other mechanisms like data contracts that can help in these scenarios, it's always best to take the path of moving as little data as possible.

Minimize Pipeline Logic

Engineering effort is one of the most expensive costs a company can incur.

If you're an engineer, it's important to protect your data engineering resources from low-value work in every possible manner.

For common systems like HubSpot or Stripe, it's easy to find an off-the-shelf connector from ETL tools, so the build vs. buy decision can be simple.

But for bespoke platforms, it can be enticing to use an open-source framework or write your Python code to power an ETL pipeline.

Every line of code that you write is a line of code you need to maintain. In these scenarios, I'd recommend minimizing the custom logic you write and keeping things simple.

If you want to remove maintenance entirely reach out to Portable with these custom connector requests - it's our specialty.

Be Prepared for Scale

The amount of data in your ETL / ELT pipelines can increase quickly. And in most scenarios, it's not under your control.

When you build your ETL pipelines or evaluate third-party solutions, it's important to make sure you don't overlook common scalability bottlenecks:

  1. Processing large volumes of data in memory can cause issues with scale

  2. Trying to process big data workloads on a single machine (instead of a distributed systems architecture)

  3. Not handling pagination correctly can lead to dropped records or missing data

  4. Failing to respect API rate limits causing delayed data extraction

  5. Not aggregating data before loading it into resource-constrained systems

  6. Missing out on the benefits of a column-aware data transformation architecture

There are countless other considerations to consider when building a data integration pipeline that scales. Don't overlook these considerations when you design and architect a platform for data management.

Monitor Your Pipelines

To keep your data analytics dashboards and pipelines running, make sure you have monitoring in place.

How? Start with ELT logging and basic metrics on your ETL jobs.

  • When did the job start?

  • When did the job complete?

  • Was the job completed successfully?

  • How much data was synced?

You can also put in place monitoring on the actual data in your data warehouse. Tracking total record counts over time in key tables can be used for auditing your outputs to detect anomalies and increase data quality.

Fail Loudly

In addition to building a pipeline that scales and putting in place monitoring, you always need to be for problems.

Whether you're using an orchestration tool like Airflow or managing your ETL pipelines, you should make sure that any errors fail loudly when they do take place.

Whenever possible, try and catch errors quickly and add alerting to notify developers and users. In addition, when your workflows see runtime errors, you need a way of handling these errors while data is in motion.

At Portable, we fail errors loudly, we notify our clients quickly, and our support team digs in to quickly resolve the issue.

Be Ready To Add Systems, Always

Change is a fact of life in the analytics ecosystem.

If you think about the world through the lens of the business applications you have in place today - the source systems, the data warehousing solutions, and the data pipelines - your architecture will immediately become out of date.

When you select an ETL tool, don't just think about the SaaS applications, data warehouses, or cloud environments that you are using today. You need to maintain flexibility for the future.

We are seeing an interesting pattern in our client base on this topic. Data teams are no longer on the sidelines, and data integrations aren't being evaluated after the fact. Instead, data teams are directly involved in the procurement process, and they ask Portable to build integrations before they even purchase the tool.

That way the data team knows for sure that the new system can be integrated into their architecture and business processes before the tool is chosen.

Pick the Right ETL Solution for the Job

There are quite a few data integration tools on the market and even more data integration consulting firms.

It's important to understand your specific needs and to evaluate the capabilities of each solution before picking the approach that make sense for you.

What are the key considerations to think about when picking an ETL tool?

When selecting an ETL / ELT solution, you should evaluate:

  • Data connectors

  • Extensibility

  • Pricing model

  • Support

  • Ease of use vs. customizability

  • On-premise vs. cloud-based

  • Proprietary vs. open-source

  • Batch vs. real-time processing

The landscape of ETL platforms is fragmented and complex. We have created outlines of the specific tools that are great for Snowflake, BigQuery, Redshift, and PostgreSQL.

If we can ever help with advice, or by providing a second set of eyes when evaluating the various options, don't hesitate to reach out.

Need an ETL Tool Today?

Get started with Portable!