Leverage standard SQL to write new data pipelines and unlock organizational bottlenecks

Published: October 12, 2022; Last updated: July 14, 2023

What is a data pipeline?

A data pipeline moves data from raw state to another location by executing a series of processing steps. This allows the data to be used by data consumers to derive business insights. Without this critical step, none of this value would be possible.

Modern IT environments rely on many different data pipelines, often overlapping and interconnecting, to facilitate their big data initiatives and extract value from data while meeting compliance, security, and accessibility requirements.

Is ETL a data pipeline? What is the difference between data pipeline and ETL pipeline?

Data pipeline is an umbrella term.

Every ETL pipeline is also a data pipeline, but not every data pipeline is an ETL pipeline.

What is a data pipeline example?

One specific example of data pipelines is extract, transform, and load (ETL) pipelines, which extract data from a source, transform it somehow, and load it into a separate system for processing.

Though that sounds similar to a data pipeline, a data pipeline doesn’t necessarily transform the data – it may simply transmit it from source to destination.

Related reading: How to use Starburst and Airflow to create resilient data pipelines

Benefits of a data pipeline

Organizations that rely on data pipelines to collect, move, and integrate data enjoy a number of benefits that apply to IT, executive leadership, and every decision that gets made.

Data pipelines include many benefits and are typically a fully-integrated component of most organizations’ data strategy.

Performance

Data pipelines automate processing and analysis, saving both time and energy. This allows data consumers to interact with the data without interruption, reducing their latency and workload.

Data compliance

Data pipelines help manage compliance and governance risks by ensuring that data is structured in the right way before it is consumed. This is particularly helpful when managing the risks associated with handling personal data, and is a requirement of some jurisdictions. For example, GDPR in the EU or CCPA in California each require specific actions to be taken when passing data from one system to another for analysis.

Improved data quality

Data pipelines improve the trustworthiness of data, which has a positive impact on the downstream usage of that data. Data which has been made to fit a standard pattern, known as normalization, is superior to raw data because it can be used more reliably for analysis.

This also allows canonical datasets to be built, which improves reliability and confidence that the insights being derived are accurate. Ultimately, all of this helps the business use the data for better decision making.

Why data quality matters to a data pipeline

Data pipelines help to manage data quality, iteratively improving it step-by-step. This has a large impact on the use of that data downstream.

Data is only usable if it provides valuable business intelligence. For this value to be realized, the data must be of high quality. This means that any discrepancies arising from different data sources must be eliminated. Bad data is of little value to anyone.

How data pipelines manage data quality

Data pipelines play a major role in managing data quality. They allow variations in data structure and quality to be absorbed and standardized through a managed, rigorous process. This predictability allows organizations to have confidence in the data feeding their systems and has a direct impact on Service Level Objectives (SLO).

How data pipelines enhance data observability

Data engineers, data producers, and others in your data science teams can monitor data as it moves through each stage of the pipeline, allowing them to correct any discrepancies. This visibility is one of the key values offered by data pipelines.

How data pipelines facilitate data continuity between the different layers and stages in the pipeline

How? Engineers check the dataset after each stage, ensuring that the rows in the source match the rows in the target.

Starburst Galaxy and Starburst Enterprise allow some of these steps to be accomplished using SQL. This makes pipelines very user-friendly compared to scripting languages like Python, which are powerful but not always easy to use.

Starburst also includes integrations with data pipeline technologies like dbt, which automate many pipeline processes.

Division of labor

Data pipelines help divide the data workload into two camps, data producers and data consumers.

Data producers

Manage the flow of data in the pipeline and include roles like data engineer or data architect. These roles focus on data pipelines directly, centralizing the tasks involved and making use of specialized technical knowledge.

Data consumers

Make use of the pipeline to consume and analyze the data in its finished state. Their roles often include data analysts, business analysts, and others. Because of the division of labor, the technicalities of the data pipeline itself are not within the scope of the data consumer. This frees them to make use of the data rather than manage its quality or integrity.

Ease of use

Data pipelines are complex and require continuous maintenance, but their operation makes datasets far easier to use after the pipeline process is complete.

This directly benefits data consumers, and allows the end goal of consumption and business insight generation to take place. For example, by aligning two tables with different date formats, analysts are able to aggregate that data more easily without having to make these changes manually themselves.

Reduces manual operations for accuracy and efficiency

The alternative to data pipelines is having staff run ad-hoc queries for data, which can be time- and labor-intensive process. Data pipelines improve the completeness, quality, and integrity of data sets while lowering risks associated with noncompliance or bad information. Repeatability and automation become especially important as the volume of data, speed, and diversity of data all increase.

Enhances metrics for strategic business decisions

With data pipelines to efficiently shuttle around information, decision makers have more complete, current, and accurate information at their disposal and make the “right” choice more often. Having trustworthy, abundant data to back up decision-making benefits an organization in endless ways.

Challenges of a data pipeline

Creating, maintaining, modifying, and repurposing data pipelines can all pose various challenges that create risks (sometimes significant) if not addressed.

Traditional data pipelines have many benefits, but they do come with certain drawbacks.

Cost

Data pipelines are expensive, costing both time and money. Although this initial investment is often recouped in the long run, not all organizations have the timeframe to absorb short-term losses for long-term gains. For this reason, it is important to understand the true costs before embarking on any data pipeline project.

Technical complexity

In most cases, constructing a data pipeline is not straightforward. It traditionally requires complex technologies to match the business needs of the individual organization deploying it.

Organizational complexity

This generates both technical and organizational complexity at every level and requires a robust reporting structure and project management strategy to help manage these issues.

Data pipelines also require data engineers and data architects to have a specialized skillset. This can make data pipeline projects difficult to recruit for, and can lead human resource constraints if not properly managed.

Juggling types of data may compromise data integrity

Having multiple types of data moving through dense webs of data pipelines to reach one location can easily become an inefficient exercise that slows down the arrival of data or compromises the integrity of what arrives.

Data routinely defies expectations, and when it does, it can result in the wrong data being stored in the wrong location, leading to compliance violations or security issues. Inconsistent data can also cause data pipelines to break down. Data pipelines take constant observation, analysis, and adjustment to work as efficiently as possible

Data security is more difficult to maintain

End users—often independent data consumers—will often try to create their own data pipelines that are redundant, noncompliant, and probably less effective than official pipelines. They may also try to alter data or existing data pipelines to fit their objectives without getting approval or documenting the change.

Furthermore, since data pipelines make data more accessible, they can inadvertently create compliance and security problems by giving the wrong users access to sensitive or private data. Data pipeline privileges must be carefully managed.

What are the main 3 stages in a data pipeline architecture? How data flows and how a traditional data pipeline works

Data scientists who are wrangling more data that they want will understand that data pipelines will often have three unique components: a data source, transformation steps, and a data destination.

In a typical data pipeline, there are three main stages:

1. Data Ingestion

The data source: an internal database, a cloud platform, or an external data source — anywhere that data gets ingested.

This stage involves gathering and importing raw data from various sources into the data pipeline. The data sources can be diverse, such as databases, files, APIs, streaming platforms, etc. During ingestion, the data may undergo initial validation and basic transformations to ensure its integrity and compatibility with downstream processes. 

Data ingestion can be done in real-time or in batches, depending on the requirements of the pipeline.

2. Data processing

Data transformation: movement or modification of the data as prescribed by hand-coding or purpose-built tools.

Once the data is ingested, it goes through the data processing stage. Here, the data is transformed, cleaned, and enriched to make it more usable and meaningful. 

This stage involves a range of operations such as filtering, aggregating, sorting, joining, and applying business rules. The processed data is often stored in a more structured format that is optimized for querying and analysis.

3. Data Destination

The destination: a data lake or data warehouse at the end of the data pipeline that brings together from multiple sources.

The final stage of the data pipeline is data delivery, where the processed and transformed data is made available to data consumers. This stage can involve loading the data into data warehouses or data lakes for storage and further analysis. 

The data may also be delivered to downstream applications, reporting tools, dashboards, or other systems that require access to the data. Data delivery may involve scheduling and automating the distribution of data to ensure it is up-to-date and available for decision-making and data analysis.

Related reading: Data pipelines and data lakes: Transforming raw data into actionable insights

What are the types of data pipelines?

There are several types of data pipelines based on an organization’s data analytics architecture. Here are a four common types:

1. ETL (Extract, Transform, Load) Data Pipeline

ETL pipelines are designed to extract data from various sources, transform it into a desired format, and load it into a target system or data warehouse. 

This type of pipeline is often used for batch processing and is appropriate for structured data.

2. ELT (Extract, Load, Transform) Data Pipeline

ELT pipelines are similar to ETL pipelines but with a different order of operations. In ELT pipelines, data is first extracted and loaded into a target system or data lake without immediate transformation. 

Transformation is performed at a later stage, which can take advantage of the scalability and flexibility of modern data storage systems.

3 .Streaming Data Pipeline

Stream processing handles the ingestion, transformation, and analysis of streaming data. It enables the seamless flow of data through the pipeline, allowing for immediate processing and analysis of data as it arrives. 

They handle continuous data streams and enable immediate processing and analysis. Streaming pipelines are commonly used for apps such as real-time data analytics, fraud detection, monitoring, and recommendation systems.

Streaming data like the kind that companies like Uber use to see the exact location of drivers costs more, but provides real-time visibility when necessary. Whether to go with batch or streaming is one of many important considerations when constructing data pipelines.

4. Batch Data Pipeline

Data can move through a data pipeline in batches, at timed intervals, or upon reaching adequate quantities, which is more cost-effective but means data isn’t always updated. 

Batch pipelines process data in predefined batches or time intervals. They are well-suited for large volumes of historical or periodic data that can be processed offline. Batch pipelines are often used for tasks such as data aggregation, reporting, and data warehousing.

These pipeline types are not mutually exclusive, and in practice, pipelines often combine elements from multiple types to handle the complexities of real-world data processing scenarios.

How data pipelines play a role in data governance

Companies often create standards governing the retention and processing of data. These standards control how long organizations are allowed to keep data, and other rules impacting how data is handled. The exact governance model employed depends on the situation and the use-case.

This impacts data quality directly by predefining how data should be handled and the legal requirements needed. Data pipelines help standardize data and align it with an organization’s governance standards. This can have both business, reputational, and legal advantages.

Constructing data pipelines with SQL and general scripting languages

Several different languages are used to construct data pipelines and query the data afterwards. Typically, this might involve either a general scripting language, SQL, or a combination of the two. 

General scripting languages

Traditionally, data pipelines rely on general scripting languages to create the processes that move data from one step to the next. This includes popular languages like: 

• Python
• Scala
• Ruby (Apache Kafka framework)
• Bash Shell Scripting

These languages are powerful tools that can be used to construct original scripts from scratch to complete data workloads. 

In these traditional workflows, scripting languages are used to construct the data pipeline itself and SQL acts as a query language used by data consumers at the end of the pipeline process. This division of labor roughly follows the division between data producers and data consumers.

Why are scripting languages used?

To construct a data pipeline, you need to understand the dataset and its structure. Normalizing these structures can be complex, and general scripting languages offer a lot of flexibility and a robust support for conditional logic. Data pipelines make use of conditional logic to process the transformation activities needed to process the data.

This means that no matter how complex your pipeline can get, a Python or Scala script can likely be constructed to meet the requirements.

User defined functions

General scripting languages like Python include the possibility of user defined functions (UDF). These allow data engineers to construct completely custom functions to match specific pipeline needs. This highly complex process is extremely powerful and many of the world’s most complex pipelines operate in this way.

Drawbacks to general scripting languages

Scripting languages are powerful but they are also highly manual. This complexity presents a bottleneck to their adoption and usage,  requiring a specialized data engineering skill set. This can limit the application of these tools to a smaller subset of data professionals and leaves data consumers entirely outside the process of creating and maintaining the data pipeline.

SQL and data pipelines

SQL is primarily used as a query language. However, SQL can also be used to structure data, and is a powerful tool when used in this way. 

Starburst Galaxy and Starburst Enterprise make use of SQL to both query and structure data. This approach is particularly useful in data lakes and data lakehouses.

Starburst Enterprise is a data lake analytics platform that allows organizations to access and analyze data on the lake and additional connected sources, including cloud data warehouses, legacy and on-premise databases, and modern streaming and NoSQL sources, all through a single, unified interface.

Starburst Galaxy is a fully managed data lake analytics platform designed for large and complex data sets in and around your cloud data lake.  It is the easiest and fastest way for you to start running queries at interactive speeds across data sources using the business intelligence and analytics tools you already know.

For example, with Starburst, users are able to cast from one data type to another using SQL. The full usage of SQL for both transformation and querying is one of the reasons that Starburst is considered a data lake analytics platform.

With Starburst, everything is built with openness in mind, and we are interoperable with nearly any data environment, including modern open file and table formats. Starburst truly separates storage from compute and treats any data store like our own. We allow customers to use the architecture that meets their specific business needs and change it when new needs emerge.

Automate with data pipeline tools

Data pipelines can also be automated in a number of ways. This makes them easier to use and saves time and money. Often, data engineers might use data pipeline engines, like Spark. 

It is also common to use a workflow manager or schedulers like: 

• dbt Cloud: dbt (data build tool) Cloud is a popular data transformation tool that allows analysts and data engineers to define and manage data transformations in SQL. 
• Airflow: Apache Airflow is an open-source platform for orchestrating and managing data pipelines.
• Dagster: Dagster is an open-source data orchestrator that focuses on building reliable and scalable data pipelines.
• Prefect: Prefect is an open-source workflow management system that supports building and orchestrating data pipelines.

Streamline your data pipeline with Starburst

Data, especially unstructured data, is your biggest competitive advantage.  

Traditional data pipelines are based on a legacy data warehouse model. The most innovative companies rely on Starburst to unlock the value of all their data, no matter the amount of data and not just what can be loaded into the warehouse—to improve product performance and customer experiences. 

Try our 50+ connectors to cloud and on-prem data sources allow data engineers to start new data initiatives with exploratory analytics rather than moving data unnecessarily.