Guide to data tools landscape for developers

Comments

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.

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
• Redshift from Amazon

Popular open-source/self-hosted options are:

• ClickHouse
• Apache Doris
• 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, and then processing it, you can just query it with SQL.