Guide to data tools landscape for developers · OlegWock

Soon after joining I realized that I didn’t know a thing about it! There are so many data tools besides notebooks, and I had no idea what they were used for or what the general work process in data science was. And if I don’t know how various data tools are usually used, or how they interact with notebooks, I can’t really suggest a good feature for a notebook or spot a problematic UI flow.

But after reading tons of articles, poking my colleagues and strangers on the internet with questions, studying workflows and struggles of our clients, and just giving myself some time to internalize this new knowledge, it did get better. Unfortunately, there wasn’t a convenient “guide to data tools for software engineers who found themselves in a data company and have no idea what all those words mean”, or I just couldn’t find one.

I don’t work on notebooks anymore, but data got me hooked, so my next job was in the field as well. This time I’m working on a tool for analytical type specialists rather than scientific (see the next chapter for an explanation!), but general knowledge about data tools and processes is still very useful! So I tried condensing it into an easy-to-read article and I hope it will help lost software engineers feel a bit more comfortable.

Who is this article for

As you can guess, I’m not really interested in becoming a data specialist per se. As such, this article won’t cover, for example, how to create a dashboard in Metabase, basics of statistics, or how to administer a Spark cluster. It’s aimed at developers who, for some reason, need to learn wtf all those people from the data team are talking about.

In this article, we will briefly go over the data lifecycle: where data comes from, how it’s handled, how it’s stored, and how it’s displayed. You will understand to which stage each particular tool belongs and which tasks it solves for people working with data.

We won’t go into details about setting up each tool or comparing tools inside the same class in-depth. Trust me, the article will be pretty lengthy as is, even without going into any of those details.

Flavors of data professions

In the data world, there are quite a lot of different positions, and it’s not always clear how exactly one position differs from another. Lines here are really blurred, especially in small companies or teams. But for the purpose of this article, what you need to understand is that there are roughly four types of data professions.

Analytical type

This is a person who interprets the data, tries to get some insight out of it, and presents it. Often this will be a data analyst or BI analyst position. They are usually skilled in SQL and spreadsheets. They work with different business intelligence (BI) tools like Tableau and spreadsheet software like Excel.

An example day-to-day task for this type is pulling customer data with SQL, calculating churn rates by region. Then building a Tableau dashboard showing churn trends and presenting findings to marketing with suggestions for retention campaigns.

Scientific type

This is a person who digs deeper than surface-level reporting. They apply statistics, build models, and run experiments to answer less obvious questions or make predictions. Often this will be a data scientist position. They are usually skilled in Python and its scientific stack (pandas, scikit-learn, and friends), and frequently work in notebooks.

An example day-to-day task for this type is taking the same customer data, exploring which factors correlate with churn, and building a statistical model to estimate how likely each customer is to leave. Then designing an A/B test for a retention campaign and analyzing whether it actually moved the needle.

Engineering type

This is a person who cares about the infrastructure around the data. Their primary job is to make data analyzable in the first place: building and maintaining pipelines that pull data from various sources, clean and standardize it, and load it into a warehouse or lake so that analytical and scientific types can actually do their work. They are also often responsible for maintaining databases and administering other data tools that might be used in an organization. Usually, this position is called data engineer.

The other side of their work is scaling. Certain work produced by scientific and analytical types needs to be scaled. For example, a simple analysis from an analyst might be transformed into a reverse ETL pipeline (don’t worry, we’ll go over all those terms in a minute) which has to run reliably and repeatably.

They often use Python and work with tools like Apache Spark, various databases and warehouses, and use cloud providers for hosting those tools.

An example day-to-day task for this type is maintaining the data pipeline that ingests customer transaction data from multiple sources, standardizing schemas, and loading it into the warehouse. Then optimizing queries so analysts can pull customer data efficiently and adding data quality checks to flag missing customer records.

Machine learning type

This type of specialist is focused on building and maintaining AI models that can be used to solve different problems. It might be a smaller classification model, which, for example, helps to detect bot traffic on the website, but it also might be a more general large language model trained or finetuned by the company.

Looking at the general description, it might look like this type really intersects with the previous ones (they also analyze the data and build pipelines), but from what I learned they often use a very different set of tools, which separates them from the types above.

Here I’m lumping multiple types of specialists into one group (ML has its own scientists and engineers) because we won’t be covering ML-related topics in this article (as it wasn’t very relevant for me in the first place, so I have limited knowledge about this part of the field). But it’s still a part of the data landscape so I didn’t want to omit them completely.

An example for this type will be building a product recommendation model for an online shop: assembling training data from the warehouse, training and tuning the model, then deploying it behind an API so the website can request personalized recommendations in real time. After deploy they will continue monitoring the model’s predictions and will periodically retrain it so it keeps performing well as customer behavior shifts over time.

Data lifecycle

The data field revolves around data (no surprises here). Data big and small, ugly and beautiful. It all starts with getting it from somewhere, processing it somehow, and then putting results somewhere. That’s really it, thank you for coming to my TED talk.

Joking of course, but it does describe ETL. ETL stands for Extract-Transform-Load and describes a common process of handling data where raw data is extracted from the source, transformed (e.g. cleaned, joined with other data), and results are loaded into the final destination for further use.

While a very common approach, it’s not set in stone. Steps can change order, repeat, or overlap. For example, an alternative (and quite popular) approach is ELT. With it, you extract data, put it directly into your warehouse, and then transform it directly in the warehouse (with results stored in different set of tables). This will fatten your bill (because of additional storage and compute), but you’ll keep the original data which allows you to process it in a different way if such a need arises.

How data is stored

Before we get into details of each ETL step, it would be beneficial to learn how and where data is stored.

File formats

A lot of data lives in files — an Excel file you get from the accounting team, or a huge JSON file with a supplier’s stock they upload to their website.

When it comes to file formats, you’ll often see CSV files (or other tabular formats like Excel) and Apache Parquet.

CSV is convenient to transfer smaller amounts of data, can be opened by virtually any office software, and is easy to use for less technical users. This is the format you’re likely getting from your sales team when they ask you to analyze this quarter’s deals.

Parquet is more often used by technical users. It’s a columnar format — data is laid out column-by-column rather than row-by-row — which enables great compression and allows you to effectively transfer and/or store bigger amounts of data. It can be read and produced by the majority of data tools, which makes it the lingua franca of data tooling. Another format solving similar problem is Apache ORC. You might also encounter Apache Avro — also a binary format, but row-oriented, it’s used for passing records around and especially in streaming processing. And yes, a lot of things in the data world are from the Apache Foundation.

Memory formats

Data can be stored in different formats. We already covered file formats, but data can be stored in memory. The most popular in-memory format is Apache Arrow.

While Parquet is a file format optimized for (among other things) compression and small file size (which helps with storage and transmission), Arrow is an in-memory format optimized for processing and zero-copy transfers. This means that data stored in Arrow format takes more space, but it can be transferred between tools (e.g. from Python’s pandas to Rust’s DataFusion) very efficiently.

Both formats are optimized for analytics and data loads, but Parquet is more focused on scanning (i.e. loading relevant entries into memory), while Arrow is focused on actual processing (e.g. efficient use of CPU/GPU instructions and caches to perform calculations).

Arrow is widely adopted and is the de-facto standard in-memory format. While it’s unlikely you’ll get data from your source in Arrow format, it’s used to exchange already loaded data between different data tools. Popular DataFrames libraries like pandas can use it as an optional backend, and there are projects that were built on Arrow from the start (e.g. Polars, DataFusion). We’ll cover DataFrames in more detail in the next sections.

Data warehouse

When analyzing data, you often work with big amounts of data from different sources. Pulling it from the source every time you want to analyze it is not very efficient and likely will put unnecessary strain on the source system. For this purpose, data is usually ingested into some kind of centralized storage before processing. There are different types of storage, each optimized for a different use-case.

A data warehouse is similar to databases like PostgreSQL or MySQL, but optimized for analytical loads. MySQL is an OLTP database, it’s optimized to work on rows. For example, a typical query for an OLTP database would be to get a user’s record (row) from the database by id. A data warehouse on the other hand is an OLAP database and is optimized to work on columns. This means that queries like “calculate total sales per region for the last year” on orders table would run much faster in a data warehouse than in a traditional database¹.

Because a warehouse is optimized for structured, cleaned data, it’s traditionally used as the final storage for processed data, rather than the first stop after ingestion. Although with the ELT approach it’s now common to use the warehouse as the landing zone for raw data too, and transform it right there.

A data warehouse manages both how your data is stored on disk, and how it’s queried. It provides its own querying engine and is tightly coupled to it. This might sound odd if you primarily worked with databases like MySQL and Mongo, but as you’ll soon learn, this is not always the case for other types of data storage.

Working with structured data and an optimized query engine allows them to provide excellent query speed. This works nicely with various BI and reporting tools where slow queries would provide a subpar user experience.

Data warehouses are the priciest out of the three storage types we’ll cover, as you pay for both performance and convenience. Popular data warehouses are Snowflake (the warehouse is only part of their platform, but they don’t provide it as a separate product as far as I know), BigQuery from Google, and Redshift from Amazon. Popular open-source/self-hosted options are ClickHouse, Apache Doris, and StarRocks.

Data lake

The opposite of structured data warehouses are data lakes. There you can dump all your CSV, Parquet, JSON, etc. files with minimal (or no at all) processing. Simply put, a data lake is just a big cloud folder with some extras. Being such, it doesn’t impose limitations on the structure of the data: you can store structured (Parquet), unstructured (emails), semi-structured (CSV), or straight up binary (images) data.

To build a data lake, start with cheap storage systems like Amazon S3, Google Cloud Storage, or Azure Blob Storage. Establish naming and partitioning conventions, dump a couple of Parquet files there, set up access policies. Add a metadata catalog (about them in a minute) with a query engine and you have yourself a data lake. Take good care of it, or else it will become a data swamp.

If you don’t want to do all that by yourself, you can use a managed offering from major cloud providers: Azure Data Lake or Snowflake (yes, they do data lakes too).

As you saw, a proper data lake, besides cloud object storage, requires a bit more bells and whistles, namely a metadata catalog (might also be called a metastore) and a query engine. They are needed to allow you to actually query data from your lake. So instead of searching for a CSV file manually in your script, downloading it, parsing/loading into memory, you can just query it.

The query engine helps with the querying part, it can take SQL (or another query language) from you and run it against data in your data lake. But to do so it needs the help of a metadata catalog. The metadata catalog describes which data you have in your lake: table names, schemas, how they map to files. Using this, the query engine can load relevant files and execute the provided query on the data.

Popular metadata catalog solutions are Hive Metastore, AWS Glue Data Catalog, and Unity Catalog from Databricks.

For query engine you can use, for example, Apache Spark, Trino, or Amazon Athena.

Data lakehouse

Yes, yes, it’s lake + warehouse. The idea is to combine the best sides of the previous two types.

A data lakehouse is built on top of a data lake with a couple of extra blocks that add extra features which make the resulting product closer to a data warehouse. The most important of these blocks is the table format which sits between your query engine and raw data and manages how data is stored. This makes a data lakehouse closer to the conventional database than a data lake is.

One of the most notable features the table format adds is ACID. It ensures that multiple applications can work on the same data at the same time without risk of corrupting it. This means that the lakehouse takes care of managing concurrent writes, handling errors mid-write, etc.

A lakehouse is more strict about schema than a data lake: you still can store semi-structured data, but you’ll need to define its structure (e.g. which fields your JSON should have) and the lakehouse will enforce it. Completely unstructured data is not for a lakehouse². Schema can change over time (schema evolution) and the lakehouse will keep track of its changes over time (schema versioning).

Having more control over data and schema, a data lakehouse can make querying faster by creating indexes and optimizing partitioning.

Some lakehouses also support time travel, which allows you to run a query on a snapshot of data from a specific point in time.

Cost-wise, a data lakehouse is still cheaper than a warehouse as it works on top of a data lake, which works on top of cheap storage. It’s important to note that unlike a data warehouse, a lakehouse doesn’t tie you to a specific querying engine, so we can’t compare costs one-to-one. But depending on tasks and workloads, separate lakehouse + compute (for query engine) can be significantly cheaper than warehouse.

Most popular table formats for data lakehouses are Apache Iceberg, Delta Lake, and Apache Hudi.

If you don’t want to build your lakehouse, there are plenty of managed solutions like Google’s Lakehouse for Apache Iceberg (formerly BigLake), Databricks (uses Delta Lake), and IBM’s watsonx.data (Iceberg).

Where data comes from

From anywhere really.

Data can come from conventional databases like PostgreSQL or Mongo. For example, in the case of an online shop, you could pull orders history data from your app database to later analyze it and combine with other data.

Similarly, data can come from third-party services. Want to analyze failed payments? Get that data from Stripe using their API.

In other cases, data might come from the user itself, like analytics events sent directly from the user’s browser, or events sent by IoT devices.

Once data is extracted, it can be either processed right away, or in the case of ELT raw data can be stored and processed later. In this case it will likely be stored in data lake, lakehouse, or maybe even data warehouse, depending on the data’s shape, size, and overall data infrastructure in the company/on a project.

Ingestion tools

To pull data from sources into your data lake or warehouse you can write bespoke scripts which will call the third-party API or query your PostgreSQL. This gives you flexibility, of course, but more often it just forces you to write code which has been written hundreds (or thousands?) of times already. Guess how many Python scripts there are that pull Stripe data and put it into BigQuery?

In cases when you don’t need something bespoke, you can use data ingestion tools that can connect a wide variety of data sources and destinations. Instead of writing repetitive glue code that covers auth, pagination, and error handling, you set up a connector for the source and for the destination and let the tool do its job. However, this approach pushes you toward an ELT approach, as usually extracted data needs to land somewhere first before it can be processed.

Most popular tools of this type are Fivetran, Airbyte, and dlt.

When pulling data from databases specifically, you’ll often hear about change data capture (CDC). Instead of repeatedly querying tables for new data, the tool reads the database’s replication log and captures inserts, updates, and deletes as they happen. Ingestion tools use CDC under the hood for database sources, and Debezium is a popular open-source solution if you need CDC as a standalone building block.

How data is processed

Now we’re getting to the juiciest part of this article. Let’s learn how data is transformed and what tools are used in the process. Of course, there are infinite possibilities for what you can do with data, and an almost infinite number of tools to help with that. We’ll try to cover as much as reasonably possible, but just keep in mind this is far from a definitive guide.

Languages

When it comes to general-purpose programming languages, Python is the king, it’s the de-facto standard language for data work. It has a big community and a lot of great native libraries for data. Even data tools and libraries written in different languages will often have Python bindings (e.g. Apache DataFusion made for Rust has Python bindings).

One of the most popular Python libraries is numpy which provides a performant implementation of multi-dimensional arrays and serves as a foundation for many other libraries. One such library (and also one of the most popular) is pandas which allows you to work with Series (one-dimensional arrays) and DataFrames (two-dimensional arrays). Pandas itself is the de-facto standard and most other data libraries will support or integrate with it in one way or another. For example, you can chart pandas DataFrames with seaborn or Plotly, build interactive apps with streamlit, use DuckDB to query DataFrames with SQL, or apply machine learning algorithms to pandas DataFrames with scikit-learn.

Other programming languages worth mentioning are R, Java/Scala, Julia, and even Rust. They all are used in data work, some are more prevalent in a specific field (e.g. R in academia), or in a specific tool’s ecosystem (e.g. Java/Scala is popular with big data frameworks like Spark), but their popularity is far from Python.

Another language we must mention is SQL. It’s wildly popular as a query language and it can be used to query data from the warehouse, as well as to transform it. Unlike Python, which has a standard interpreter used by the vast majority of users, SQL often differs slightly depending on where it’s executed.

Batch vs realtime

To better understand the purpose of some tools, we first need to understand different ways to process data.

First one is batch processing — when you process data in large chunks, often at regular intervals. For example, aggregating sales data for the previous month once the next one starts. This process is not time-sensitive (we can wait a couple of hours or even days to get results) and works with big amounts of data.

In contrast to batch processing, we have realtime processing. With this approach data is processed either right after it arrives (stream processing) or is batched in small batches (e.g. processing every 20s). Usually seen in pipelines where speed is very important. For example, when monitoring website users’ behavior to detect bots, you’d use realtime processing to spot (and block) them as early as possible.

SQL

There are countless ways to transform data. One of them is to let your query engine do the work. This is more often used in combo with a data warehouse, but you can make it work with your data lake or lakehouse as well.

Most popular tools for this kind of processing are dbt and SQLMesh. With those tools, you describe your transformations as SQL select statements and the tool compiles them into queries executed directly by your engine (dbt or SQLMesh don’t handle data directly).

Here is how example dbt transformation that produces model for monthly revenue by region could look like:

select
    date_trunc('month', o.order_date) as order_month,
    c.region,
    count(distinct o.order_id) as orders,
    sum(o.amount) as revenue
from 🔥 as o
join 💬 as c
    on o.customer_id = c.customer_id
where o.status = 'completed'
group by 1, 2

Using tools like dbt or SQLMesh has several advantages compared to e.g. calling query engine from custom Python scripts. Besides standardization (which is already a big advantage), it allows you to break complex transformations into simple manageable pieces with support for dependencies. dbt allows you to reference models instead of hardcoded table names (see ref in snippet above) and when performing a transformation it will create a dependency graph and process everything in the correct order.

While dbt/SQLMesh does all the orchestration in this setup, it’s ultimately your query engine that works with data. Usually processed data will land in the same warehouse/lakehouse/lake where it originated from, but depending on your setup, your query engine could push data to completely different destination (but this will be outside of dbt/SQLMesh scope).

Local DataFrames

When processing data with a general-purpose programming language one of the most common ways is dataframes. Simply put, dataframes are an abstraction on top of two-dimensional arrays which helps when working with tabular data. The most popular one is pandas, and, as I mentioned previously, it is the de-facto standard for local processing with Python. Processing data with pandas looks like this:

import pandas as pd
 
sales = pd.read_csv("sales.csv")
 
summary = (
    sales
    .assign(order_date=lambda df: pd.to_datetime(df["order_date"]))
    .query("status == 'completed'")
    .groupby([pd.Grouper(key="order_date", freq="ME"), "region"])
    .agg(
        orders=("order_id", "nunique"),
        revenue=("revenue", "sum"),
        avg_order_value=("revenue", "mean"),
    )
    .sort_values("revenue", ascending=False)
)
 
print(summary.head(10))

While pandas is the most popular one, it’s not the only one. Another well-known library for Python is Polars (although its adoption is still nowhere close to pandas). There are also dataframe libraries for other languages as well. Rust has the aforementioned Polars (as they are written in Rust and just provide Python bindings) and DataFusion. Julia has DataFrames.jl, R has data.frame, and Java has tablesaw.

Dataframe libraries can be classified by how they work with data. Pandas, data.frame, and tablesaw are eager. This means that once you call df.groupby("region")["revenue"].sum() pandas will do group by and aggregation right away. But some libraries behave differently: DataFusion and Polars LazyFrame (Polars provides both eager and lazy interfaces) won’t execute operations until you manually trigger collection. Every lazy dataframe is represented as a logical plan rather than raw data. This plan describes where to get data from and which operations to apply to it. Every time you call methods like .group_by(...) or .agg(...) Polars will just produce a new dataframe with an updated plan. And only when you call .collect() it will execute it. This gives Polars an opportunity to optimize the plan before execution, potentially making it faster.

A common limitation with those libraries is that data lives on your machine and is processed locally. This means that you’re limited by the memory and CPU you have. In case of pandas, you’re limited by your RAM as all data is stored and manipulated in-memory. Some libraries like Polars support stream processing, so you can work with datasets larger than available RAM, but some operations require loading the working set in memory so it’s not a universal solution. And you’re still limited by the CPU.

While we’re on the topic of local processing, DuckDB deserves a special mention. It’s an in-process OLAP database, often described as “SQLite for analytics”. It’s not a dataframe library per se, but it occupies the same niche and became very popular lately. With it you can query CSV and Parquet files (and even pandas DataFrames) with SQL right on your machine, without setting up any infrastructure.

Large-scale distributed processing

When data outgrows a single machine, the way out is to spread the work across many of them. Instead of one computer crunching through the whole dataset, you split data into chunks, hand each chunk to a separate machine in a cluster and let them process their parts in parallel. This way you can scale your pipeline horizontally when the amount of data that needs processing changes.

This idea isn’t new. One of the OGs of distributed processing is Apache Hadoop. Nowadays considered legacy, but you might still find it in some older setups. The de-facto standard nowadays is Apache Spark.

Spark takes care of actually loading the data and doing transformations, parallelizing and optimizing them when needed. You, as a user, will write a script which uses one of Spark’s bindings and instructs it what exactly to do with the data.

Here is illustrative example using Python bindings:

from pyspark.sql import SparkSession
import pyspark.sql.functions as F
 
spark = SparkSession.builder.appName("monthly-revenue").getOrCreate()
 
orders = spark.read.parquet("s3://my-lake/silver/orders/")
customers = spark.read.parquet("s3://my-lake/silver/customers/")
 
summary = (
    orders
    .filter(F.col("status") == "completed")
    .join(customers, "customer_id")
    .groupBy(F.date_trunc("month", "order_date").alias("order_month"), "region")
    .agg(
        F.countDistinct("order_id").alias("orders"),
        F.sum("amount").alias("revenue"),
    )
)
 
summary.write.mode("overwrite").parquet("s3://my-lake/gold/monthly_revenue/")

Spark provides bindings for multiple programming languages. For Python it has PySpark which has DataFrame API (and even a separate compatibility layer which emulates pandas APIs but on Spark). For R there is SparkR which was deprecated recently, and of course there are Java and Scala bindings as well (Spark itself is written in Scala). It can read from and write to pretty much any storage we covered earlier, which is why you’ll see it used both as a query engine for data lakes and as a workhorse for heavy transformations.

Besides Spark there are other tools as well. Dask scales Python code across a cluster while staying very close to familiar pandas and numpy APIs. Ray is a more general-purpose distributed compute framework, and is especially popular for ML workloads like model training. And Apache Flink can do batch processing too, but its speciality is stream processing. Which is conveniently our next topic :).

Stream processing

Depending on the needs of a project, you might want to opt into stream processing. This might be due to data processing results being critical, so we’d like to get them as soon as possible. A classic example of this is fraud detection on credit card transactions. Streaming processing might also be more resource efficient or better fit your data. For example, you might want to process incoming website analytics events as soon as they arrive (or in micro-batches): validate their shape, enrich them with geolocation data based on IP, and put the processed event into your ClickHouse. Even if we omit the fact that realtime analytics is simply a better user experience, processing events as they arrive allows you to not store “raw” payloads somewhere before they can be processed by the next run of a batch transformation.

When speaking about streaming processing, you’ll often hear about Apache Kafka. Originally developed at LinkedIn, currently Kafka is open-source and highly popular. It’s responsible for, simply speaking, accepting events from producers, storing them, and allowing consumers to read them. If you worked with message queues, this might sound similar. An important distinction between Kafka (and event streaming platforms in general) and message queues is that events aren’t discarded once one of the consumers acknowledges them. They remain in the log and other consumers (or even the same one) can read them as much as they want, until the event expires according to retention rules.

While sounding relatively simple, Kafka is also distributed and fault-tolerant, which makes it a good building block for stream data processing pipelines. However, it’s important to note that Kafka itself doesn’t do any data processing. Usually, that would be done by a separate worker, which, from Kafka’s standpoint, is just another consumer.

To make it a bit more confusing, in addition to Kafka the software, there are two APIs/libraries that have similar names: Kafka Connect to connect your Apache Kafka to other systems (like databases) and Kafka Streams — a Java/Scala stream processing library for building pipelines on top of Kafka. It covers stateful transformations, windowed aggregations, and joins — similar in spirit to Flink, but it runs embedded in your app and only works with Kafka.

Other event streaming platforms include Apache Pulsar, Redpanda, and AWS Kinesis Data Streams.

As mentioned earlier, an event streaming platform doesn’t do any processing itself, it will be done by a separate worker. This can be any custom script (as Kafka has bindings for many programming languages), but often it will be special software tailored for stream processing. One such solution is Apache Flink. It provides building blocks for you to compose your processing pipeline and then takes care of executing, scaling, and handling failures.

With stream processing you’ll describe where events should come from and how they should be processed. This can include filtering events, mapping fields, aggregating over a window, deduplicating events, and writing results to a destination (which can be another Kafka topic, database, or something else completely). And then you give this definition to Flink which deploys it on a cluster and runs it continuously, processing new events as they arrive from the source.

Besides Flink, there are other options for stream processing: Spark Structured Streaming, Google Cloud Dataflow, and Azure Stream Analytics.

Orchestration

Once you have a sizeable zoo of dbt transformations, Spark jobs, custom scripts, and god-knows-what else it becomes more and more tricky to keep track of everything and ensure everything runs smoothly. Fortunately, to help with that, we have orchestrators.

An orchestrator allows you to build a complex data processing pipeline from separate steps. You define what each task should do and what tasks it depends on, and the orchestrator will build a directed acyclic graph (DAG) of tasks. Usually this is done in code (predominantly Python). Orchestrator itself doesn’t process the data, but it can run your Spark scripts, trigger dbt transforms, call HTTP endpoints, etc. which will do actual work.

And once you’ve defined a pipeline you can run it on schedule, based on events from Kafka, manually from the UI, by HTTP request, etc. There is a wide variety of triggers you can use already or make your own using the provided plugins API.

Splitting work into independent tasks allows the orchestrator to optimize execution by, for example, running tasks that don’t depend on each other in parallel. This also allows it to retry certain tasks on failure and resume the pipeline from there (instead of restarting the whole pipeline).

Worth noting that orchestrators are a batch processing thing. They run a pipeline from start to finish and then stop until the next trigger, which doesn’t really map onto stream processing where the pipeline is meant to run continuously and never “finish”. So in streaming setups you’ll rely on the stream processor itself (like Flink) rather than an orchestrator.

The most popular orchestrator with a vast ecosystem is Apache Airflow, but there are other options like Dagster, Prefect, and Luigi (this one is older and less popular nowadays).

Data observability and monitoring

As in software development you don’t want to learn about an outage from your users, in data you don’t want to learn that the pipeline was failing for the last 5 days from your boss (for whom this broke an important dashboard). Similarly to software development, in data there are dedicated monitoring and observability tools to help you avoid this fate.

It can be divided into two categories. In one you have pipeline monitoring — whether the job ran, did it fail, how long it took, etc. And then there is monitoring the data itself — is it up to date, are there any anomalies in volume, did schema change silently, and so on. Both are important.

For pipeline monitoring, the stack is similar to what you can find in an average web app — Prometheus, Grafana, ELK, etc. Plus some of the monitoring can be covered by the orchestrator itself. For data quality monitoring there are manual solutions where you define how data should look — for example Great Expectations or dbt tests. But there are also automated solutions that monitor your data to learn how it should look and then flag any anomalies as they happen. Examples of automated solutions are Monte Carlo, Bigeye, and Metaplane.

Where data lands

We’ve reached the L in ETL. After data is extracted and transformed, it has to land somewhere so people and tools can actually use it.

The storage systems it lands in are the same ones we already covered — warehouses, lakes, and lakehouses. What’s worth talking about is that data doesn’t just land once at the very end. It usually lands several times along the way, in different shapes.

Medallion architecture

When working with data pipelines, often the same data can be stored multiple times at different levels of refinement. Remember how in ELT we dump raw data into storage first and transform it later? Both raw and processed data needs to be stored somewhere. And while it’s possible to have separate storage for it (e.g. lake for raw and warehouse for processed), often you’ll see the same storage reused to store data at all stages of the pipeline.

A popular way to organize this is the medallion architecture, which splits data into three layers:

• Bronze — raw data, straight from the source.
• Silver — cleaned and conformed. This might involve fixing types, removing duplicates, maybe joining separate sources into single table.
• Gold — aggregated, modeled, and shaped for a specific use like a dashboard or a report.

It’s the same storage system, but data at different stages of its life. An analyst usually queries gold tables, while an engineer debugging a pipeline might dig into bronze.

Dimensional modeling

Medallion architecture tells you how refined the data is, but says nothing about the shape it takes. When it comes to shape, sooner or later you’ll hear analysts mentioning things like “fact table” or “grain”. This vocabulary comes from dimensional modeling — an approach to organizing tables in a warehouse popularized by Ralph Kimball in his “The Data Warehouse Toolkit”.

The idea is to split your data into two kinds of tables. Fact tables store events or measurements: one row per order, per payment, per page view. They are long and narrow, consist mostly of numbers and foreign keys to dimenstion tables, and grow all the time. Dimension tables store the context in which those facts happened: one row per customer, per product, per calendar date. They are wider, change less often. If you draw a fact table in the middle with its dimensions around it and you’d get a star schema (there is also a snowflake schema, a more normalized variant, and no, it has nothing to do with Snowflake the warehouse).

Two more terms you’ll likely encounter. Grain is what one row of a table represents (one order? one order line? one order per customer per day?). And a data mart is a slice of the warehouse modeled for one team or subject: a marketing mart, a finance mart. Those usually live in the gold layer.

But I must note that not every team follows this strictly. With modern warehouses being fast and storage being relatively cheap, plenty of teams just build wide denormalized tables for specific use cases (sometimes called the “one big table” approach).

Serving data to apps

Gold tables often live right in the warehouse, which works perfectly fine for internal dashboards and reports. But sometimes a warehouse’s query latency and per-query cost aren’t good enough, specifically when data is served to many users at once and needs to come back in milliseconds. For example, products that provide user-facing analytics: a product ingests a stream of events and lets users query and aggregate them into custom charts. The same goes for internal real-time monitoring, or app features backed by heavy aggregations (leaderboards, “trending now”, usage meters).

For these, processed data is loaded into a real-time OLAP database like Apache Druid or Apache Pinot. They are specifically built for sub-second queries at high concurrency. We already mentioned ClickHouse and Apache Doris in the warehouse section, but they can work as a real-time OLAP database as well (and ClickHouse is a very popular choice for this case).

Reverse ETL

Usually data flows from operational sources into your warehouse, but the opposite is also possible! For example, you could pull data from Stripe, calculate a customer’s life-time value and then upload this data into HubSpot, so your sales team can easily see high-value customers. This is known as operational analytics and we’ll talk about it as a use case in a minute. The process itself of loading processed data back into operational tools is called reverse ETL.

Similarly to ingestion tools, there is software which can help with reverse ETL as well. In this case you would point it to your warehouse and tell it where certain tables/columns should be synced to. The tool then will take care of boring tasks like handling failures and retries, rate limits, alerting, and incremental sync.

Some ingestion tools provide solutions for reverse ETL, namely Airbyte Data activation and Fivetran Activations (was named Census before the acquisition). There are, of course, standalone tools as well, namely Hightouch and RudderStack.

Semantic layer and data catalog

As the number of tables and columns in your data warehouse grows, keeping track of data becomes harder and harder. Of course, this problem didn’t go unnoticed and we have a wide variety of products trying to solve it.

First let’s talk about the data catalog. It’s similar in idea to the metastore which we talked about in the Data lake section, but it’s meant to be used by humans and not your query engine. It functions mostly as documentation and adds business context to the data: where it comes from, who owns it, what the access policies are, it makes data searchable and understandable by humans. Examples of data catalogs are previously mentioned Unity Catalog from Databricks, DataHub, and OpenMetadata.

Another class of tools is the semantic layer. The role of a semantic layer is to hold canonical definitions for business entities, their relationships, metrics, etc. Imagine you have a task to prepare a revenue report by regions and customer age. That sounds simple, but just until you realize you have 4 tables tracking orders, 3 more for customers, and zero idea which one you should use. And then, how exactly do you define regions? What age groups do we use? You can make reasonable assumptions, but there is no guarantee that the person who made this report last time had the same idea about region borders. The semantic layer solves this by providing a definition that the customer model has columns A, B, C which come from tables X and Y, EMEA region consists of this, this, and this markets, whether we include refunds in revenue calculations or not, and so on. And most crucially, the semantic layer integrates with other tools in your stack (primarily business intelligence and AI agents) so you can pick the correct entity and metric in the UI and the semantic layer will handle converting that into the necessary query (or at least will provide the necessary info for the tool to generate the query itself). Popular solutions for a semantic layer are LookML from Looker, Cube, dbt Semantic Layer, and the previously mentioned Unity Catalog also has semantic layer features.

Data lineage

Highly related to data catalogs (and often combined in one software solution) is data lineage. While catalogs describe which data you have, data lineage tracks how data is transformed as it goes through the pipeline. It is usually collected automatically from your orchestrator DAGs, by parsing SQL of your transforms, or from events/metadata emitted by data workers themselves.

Data lineage can be tracked with varying precision. In more basic setups you’ll have table-level lineage (e.g. gold.orders is built from silver.orders and silver.customers), but with more advanced setups you can get to column-level lineage (e.g. customers.life_time_value is calculated from orders.total and subscription_payments.amount).

Lineage can be used to assess downstream impact (e.g. will something break if I drop this seemingly unimportant column), for root-cause analysis (what data feeds this metric because it looks off), for compliance (what personally identifiable data of our users we use and where).

As mentioned, lineage tracking is often shipped as part of data catalogs — Unity Catalog, DataHub, and OpenMetadata all provide support for viewing data lineage. But more importantly your whole pipeline needs to collaborate to produce this tracking data, either via connectors that emit this data automatically or by emitting events manually as part of data processing.

This data could be in a vendor-specific format (e.g. DataHub has its own, Unity Catalog has a different one), or it could be in OpenLineage format which is an emerging standard and is already supported by many data catalogs and has many connectors for popular processing tools.

How data is consumed

Finally, we’re here. After figuring out the ingestion, learning about different kinds of storage, how data is processed and where it goes afterwards, finally we can actually use it. But wait, where can we use it? Glad you asked!

Dashboards and reports

If the first thing you picture when you hear “data” is a bunch of charts, that’s no accident. Dashboards (and reporting in general) are probably the single most common thing data pipelines end up feeding. Especially in smaller companies where the amount of data is smaller and there are no elaborate use cases, besides reporting.

Tools for this are usually called business intelligence (BI) tools. They connect to your warehouse (or lakehouse, or sometimes straight to an app database), and let you build charts from your data without writing code. These tools often have a wide range of chart types available and a lot of options to dial in the exact look of the chart. Then charts can be composed into dashboards. The main selling point is self-service: instead of pinging an analyst every time someone wants last week’s signups, less technical people can build (or at least explore) charts themselves through a UI, without writing SQL by hand.

A lot of tools go beyond just building charts. You’ll often find capabilities like scheduled reports which deliver a dashboard to your email or Slack on schedule, or alerting which can notify you when a metric crosses a threshold.

There are quite a few of those tools on the market, often with slightly different positioning. For example, Tableau and Power BI are very popular, especially in larger enterprises, and lean toward powerful, flexible visualizations. Looker³ is built around its own semantic layer (LookML) and is inclined towards more technical teams. Metabase focuses on being quick and easy to set up (including self-hosting it) and approachable for non-technical users. There are plenty more, but you get the gist.

Operational analytics

Another popular use case for data is operational analytics. We touched on it in the reverse ETL section, but that was merely a way to deliver it, now let’s focus on the use case itself. The idea is to bring useful data into apps that non-analysts already use to do their day-to-day work.

With a more traditional approach to data, it’s collected, aggregated and then compiled into a report for some higher-up to help them take a correct strategic decision. With operational analytics it’s the opposite: data helps people from different parts of company do their day-to-day work:

• Aggregated usage data synced into Hubspot for the sales team to upsell to the right customer
• Customer’s recent orders, recent support tickets, plan, etc. synced into Zendesk for the customer support team
• Internal app showing product adoption for a customer for the customer success team

Yada yada yada, you get the idea. While reverse ETL is very popular approach for bringing operational analytics to its consumers (sales, customer support, etc.), it’s not the only way. If you make an internal Customer 360 app for your support team which pulls all the data from your warehouse, this is still operational analytics.

Ad-hoc and exploratory analysis

Not every question needs its own dashboard or pipeline. Sometimes an analyst or data scientist just needs to sit down with the data and poke at it to answer a specific, often one-off question: why did signups drop last week, which cohort is driving refunds, is this weird spike even real. We covered batch and realtime processing, but this is neither. This is ad-hoc analysis. In this case we don’t have an automated data processing pipeline, but we have an analyst who has a question and tries to get an answer for it from existing data. Similar to ad-hoc analysis we also have exploratory analysis. This time the analyst doesn’t have a question, but they have data and a strong desire to get some insight from it.

What makes it different from simple charts is that processing usually is more complicated than simple filtering and aggregation, so reporting tools quickly become useless for this kind of job.
An analyst could apply one set of operations to find anomalies, then depending on the results apply a different set of operations, and so on.

There are different ways you can do ad-hoc/exploratory analysis. You can write Python scripts (and likely use pandas or Polars dataframes we met earlier) in your favorite text editor and run them from the terminal, or you can write SQL directly in a UI provided by warehouse itself. You might be using one of the IDEs made for data work like Spyder or RStudio. There is not much to be said about those environments, and as a developer you are likely well accustomed to all of those.

One environment I’d like to highlight specifically is the notebook. It allows you to combine markdown with code (and often other cell types like SQL cell) in one “file” — a notebook. Code cells can produce wide range of outputs like images, interactive charts, tables, etc. This comes in very handy for exploratory analysis as it allows you to see the results of execution alongside the code, visually explore the data, and do analysis gradually. And it’s great for presenting your work too.

Popular standalone notebooks are Jupyter (OG and still most popular choice), Google Colab, Deepnote, and marimo. Though many vendors provide notebooks as part of their platform (e.g. Databricks or Snowflake).

ML-related cases

ML is a whole field of its own — its own storage (feature stores), its own training, tracking, and deployment tools, its own scientists and engineers — so we won’t get into much detail here, but machine learning is such a big consumer of data (probably the hungriest, too) that I just can’t omit it.

Machine learning can be used for wide range of tasks. Besides large language models which have been all over the news for the last couple of years, there are a lot of smaller, more specialized models. This could be a churn prediction model, recommendations model, demand forecasting model, or customer segmentation model. What they have in common, is that before they can be used in production they need clean data to be trained on.

For those models, a data scientist or ML engineer first pulls a set of input signals called features, like “orders in the last 30 days” or “days since last login”, from the warehouse, trains a model on them, and deploys it to make predictions on fresh data. Those predictions then loop back into things we’ve already seen: a churn score synced into a CRM (operational analytics), or product recommendations served to users.

Embedded analytics

Data can power specific features in your app (like the product recommendations we mentioned in the previous chapter). Now, this can wildly differ from app to app, but one prominent use case I’d like to discuss here is embedded analytics.

Imagine you’re running a marketplace where a person can create a shop and start selling something (kinda like Etsy or Shopify). And of course they would like to know what is selling the best. Or maybe where most customers come from. Or which search queries their products rank the best for. And you, as a platform, have all this data and would like to let users see it.

For simple cases, like 5 pre-defined charts with limited filtering, you could build it yourself: write queries, code some UI for filtering, and wire some charting library to render charts. But for complex cases when user might want to do complex querying you’ll have a better time reaching for purpose-built tools. Previously mentioned reporting software (Metabase, Looker, Tableau) can all be used for embedded analytics as well. And in addition to that there are embed-first solutions like Sisense and Luzmo.

These tools will integrate with your app and be displayed directly in it (with various degrees of UI customizability). In this symbiosis your app will handle authentication and authorization and the embedded analytics solution will handle querying (and related UI) and rendering charts.

Data as a Product

For example, your company could be ingesting, transforming, and indexing data from various crypto blockchains and then sell access to it to analysts. Or you could be scraping Google’s search results and then sell that data to SEO specialists.

In both these scenarios data (and access to querying it) is what you sell, so you’ll need a robust pipeline to timely ingest it and to allow performant querying of it.

Data governance

And we finish this guide on data governance, a section that probably has the least to do with tech (at least when compared to other sections of the guide). Nevertheless, still a very important one.

Nowadays, companies collect a ton of data (and then some). Often it includes personally identifiable information, health data, and other kinds of data not meant to be read by anyone and everyone. Governance controls who can access the data and how we keep track of who did access the data, who is the owner, how privacy concerns (e.g. right-to-be-forgotten) are handled, where data is physically stored, how long it’s kept around, you get the idea.

Although tech can help with some of this, for example roles and access control can be set up at the level of the data warehouse, data catalog can contain ownership info, lineage can be used to track usage of PII, it’s much more about people and processes and sits close to legal/compliance and security teams.

***

And that’s the map. We went from where data comes from through how it’s stored, processed, and finally put to use. There’s plenty more under each of these boxes (every tool here could be its own article, and new ones keep showing up), and I had to gloss over some details at times, but I’m hopeful that after reading this guide the words your data team throws around make a bit more sense now.