Guide to data tools landscape for developers
Found yourself on a data project and have no idea what they all are talking about? Feel excluded from all the fun discussions in the office kitchen? If only there were a humongous guide going over all the concepts and buzzwords...
Some time ago, I joined Deepnote as a software engineer. Deepnote makes a cloud notebook for data teams. I, however, didn't have any background in data. But I knew what a notebook was and I thought it would be interesting to work on this kind of project. I didn't think the data field was that far from software engineering, I always thought of them as adjacent fields.
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.
detect bot traffic on the website
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 database1.
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 lakehouse2. 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).
Google's Lakehouse for Apache 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:
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:
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
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:
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
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.
warehouses, lakes, and lakehouses
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
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