- A comically brief history of the data world
- Data stacks centralize your data and shield your application from analytical queries that could slow your app down
- Overview of data stack layers
- Data source layer
- Data transformation and modeling layer
- Data storage layer
- Data analysis layer
- Further reading
This article provides a high-level overview for people who are new to the data space and who want to get some context on all the tools that make up the data landscape. The idea here is just to give you a lay of the land so you have some basic context when people talk about this stuff. But first:
A comically brief history of the data world
In the early days, data storage and manipulation was a complex and expensive process. Storing data required on-premise servers (like racks of blinking computers and cable salad in an air-conditioned room over by the bathrooms). And data retrieval could be really slow: querying and manipulating that data took hours, sometimes days.
But tooling has improved dramatically, especially in the last ten years, and especially with the rise of cloud infrastructure (which is just larger racks of computers in an air-conditioned environment with sane cable management that you can rent on demand). These days data is faster and cheaper to work with than ever. There are now a ton of tools for working with data, which can make the data landscape seem more complicated than it actually is.
There’s a lot of talk about Business Intelligence, Big Data, and AI-driven insights, but these are just marketing terms. Most businesses don’t have petabytes of information, or even terabytes, and they don’t need that much data. Most businesses just need to be able to query what data they do collect, spot and react to trends, and think about what other data they’d like to collect to field the questions they can’t answer. Many businesses also need to be able to provide data to their customers, for example by embedding charts into their application.
Nowadays, a simple data stack is all most companies need, and they’re easy to scale for when your business really takes off. They’re also just a good way to understand the data landscape in general, so we’ll give you a brief tour of a basic data stack, starting with why you’d need a data stack in the first place.
Data stacks centralize your data and shield your application from analytical queries that could slow your app down
“Data stack” is just a term that refers to the set of software that manages the data along its journey from source to dashboard. There is a cost to building a data stack separate from your application data, but the advantages of a data stack are essentially two-fold:
- A data stack doesn’t slow down your application’s database with queries from curious analysts. These queries can range across large amounts of data, which can tie up your database’s resources and make your app slower for innocent people using your app.
- A data stack centralizes your data, including data from sources other than your application, and organizes the data in such a way that makes it easy to query.
Overview of data stack layers
At a high level, a data stack consists of four layers:
- Source layer. Data collection and ingestion.
- Transformation and modeling layer(s). Ingesting, formatting, and organizing data.
- Storage layer. You have to put that data somewhere, ideally in a place that makes it easy to find.
- Analysis layer. Create charts and tables that help you understand what’s going on.
These layers are somewhat arbitrary, and they overlap (particularly the transformation and modeling layer). And you can argue there are other layers (like data governance, or data monitoring), but these four layers are the major ones.
Data source layer
Businesses collect data from their apps, but they also accrue a lot of data from third-party apps and services. Data can come from:
- Production databases. Sometimes called transactional databases, or operational or application databases (or just “Prod”). That is, the database your software uses to store all of its data while it’s doing whatever it is your software does.
- Operational apps. Data from social media, customer relationship management, and payment provider apps and APIs.
- External data. Data from third parties like web analytics (e.g., Google Analytics), or demographic data, market data, geospatial data, and so on.
Data ingestion and data integration
Your company will have to get data from all of these different sources. Wrangling all of this data on your own can be really time-consuming, as you have to grab data from each app’s API. So one option is to use a service that maintains connectors to these applications. These are sometimes called integration and ingestion tools, which include tools like:
Data transformation and modeling layer
Generally, trying to analyze unmodified data from multiple sources is a nightmare: the data is scattered all over the place, and answering simple questions might involve querying multiple different tables just to get the data you need.
The transformation and modeling layer of the data stack is all about getting data into a “shape” that’s easy to query and explore. The idea is to write scheduled, large queries called ETLs that grab a bunch of data , and then dump that data into your data warehouse. In some cases, ETLs grab the data in batches (like once a night); it can also be streamed, which means the data is sent off to the data warehouse as soon as the app records it.
An nonexhaustive list of transformations:
- Taking data from .csv or JSON format, and inserting that data into a relational database.
- Aggregating data from multiple sources into a single dataset.
- Changing data format, for example, from JSON to CSV.
- Filtering out data, or filling in gaps in the data.
ETLs: Extract, Transform, Load
ETLs are scripts, sometimes called jobs, that extract data from various sources (including raw data already stored in a data warehouse), transform the data, and load the transformed data into the data warehouse
ETL stands for Extract, Transform and Load:
- Extract: A set of queries or API requests that grabs the raw data.
- Transform: Queries or scripts that prep the data for analysis (i.e., make the data easy to query).
- Load: A set of queries that load transformed data into a data warehouse.
This sounds complicated, but it’s really just a sequence of scripts being run. A super basic ETL job could go something like this:
- Query data from three tables that contain customer data.
- Change all the values in the Address column to use upper case.
- If there’s no value in the State column, look up the state from the Zip code and insert the State in the State column.
- Insert the results to the
CUSTOMERtable in the data warehouse.
Within the past few years, ELT (not ETL) has become more popular (even though everyone still just says ETL). The idea with ELTs is to use data ingestion tools to load a bunch of raw data from sources into the data warehouse in whatever format it comes in, then run SQL queries that take that data, transform it into tables or columns that are easy to query, and save that data in the data warehouse.
The rationale for the ELT path is that it’s generally easier to first load the raw data into a data warehouse and then do more complex transformation with it (versus transforming the data before putting it into the data warehouse). Plus then you also have the raw data as is, if you ever need it (storage has gotten pretty cheap).
Data modeling is an abstract term, with slightly different definitions in different contexts, so even if you’ve encountered the term before, you may still not be totally sure you understand what exactly people are talking about.
Data modeling refers to deciding which data you want to collect about the real world, and how to organize the data you collect into tables in a database in a way that’s useful. That’s basically it. If you have a spreadsheet called Customer, your “model” of the customer is the set of headings in that spreadsheet:
Data modeling can also refer to how that data is stored in a database. So, not just defining the columns (sometimes called fields) and their data types, but how those columns are grouped in tables, and how those tables can be linked together to form a bigger picture. For example, you might want to link the Customer model to a Purchases model to figure out who’s buying what.
The reason for all this transformation is that the data model used for applications is usually unsuitable for analysis. The way you model data for applications typically optimizes for creating, reading, updating, and deleting records. You’ll sometimes here the acronym CRUD (Create, Read, Update, Delete). Your data has a “shape” in your application (like rows and columns), and you want to re-shape those rows and columns in your data warehouse so that the tables are easier to filter and summarize.
We should probably write an article on modeling, but that topic can get pretty abstract, and people tend to tune out unless they actually have to model something from the real world and need to figure out how to go about doing it.
Data transformation and modeling tools
- dbt - is an open-source tool for building, testing, and documenting data models. dbt sits on top of your data warehouse and uses SQL to transform data that is already stored in the data warehouse.
- Apache Airflow - an open-source data workflow management tool which can be used in conjunction with Fivetran or Stitch. It can be used for overseeing execution of Fivetran or Stitch tasks.
- Metabase. Some analytical tools (like Metabase) also let people model data themselves. This way, people can model data on the fly, refining their customer or product models as they respond to changes in their business.
Data storage layer
Once you’ve transformed the data, you need to put it somewhere that makes it easy to retrieve when you need it.
There are different types of databases, but for the purpose of a data stack we’ll talk primarily about two broad categories of databases here: transactional and analytical.
Transactional databases are built for backing software. They use tables (relations) to group columns (attributes).
- SQL Server
Analytical databases are structured to make analytics queries easy (e.g., what is the average price point for all products in categories a, b, and c?). They usually use columnar storage, where data is collected in columns, rather than columns in a table. Examples analytical databases include:
Data Warehouses are databases used to store all the data that your organization wants to analyze. Data warehouse sometimes refers to analytical databases like Snowflake or BigQuery, but transactional databases like PostgreSQL and MySQL have gotten ridiculously good, and (with proper modeling) can handle most analytical workloads no problem. So unless you’re working with seriously massive datasets, a powerhouse RBDMS like PostgreSQL may be all you need for a data warehouse. In either case, data warehouses are usually run in the cloud (on Amazon Web Services, Google Cloud Platform, Microsoft Azure, and so on) so companies don’t have to worry about building their own servers to run the database.
Data Lakes are stores of unstructured data. You can think of them like big, distributed file systems full of folder and files. You use data lakes to dump data in all kinds of formats (CSV, videos, JSON, sound files, etc). Once the data is in the data lake, you can use query engines like Presto or Athena to query data, format and model it, and load that modeled into a data warehouse for analysts to explore.
Data analysis layer
Data analysis is the visualization layer of the data stack. Sometimes called Data Analytics or Business Intelligence. This is the layer where you make tables and charts of data to show other people that you know what you’re doing, and that we’re all definitely not just making everything up as we go along.
Once transformed and loaded into your data warehouse, you can hook up a visualization tool like Metabase (sorry, a business intelligence platform) to your data warehouse to “unlock insights”.
Some BI tools also include tools for modeling data and in some cases writing back to your databases (that is, the allow you to create, update, or delete records), which opens up all kinds of possibilities for building back-office apps to work with your data.
Data analysis tools
Like every layer, you have a lot of options:
- Power BI
- Well, most of Learn Metabase, really.
Did this article help you?
Thanks for your feedback!