#045: What tools should you know as a Data Engineer?
Jun 07, 2023
The modern data stack is overwhelming, and new tools show up constantly. The useful move is to learn it by category rather than by brand name: databases, extract and load, transform, reverse ETL, version control and automation, orchestration, infrastructure, and BI. Once you know what each category is for, the specific tools inside it are interchangeable enough that learning one teaches you most of the next. Below is the lineup I walked through, grouped that way, with the names that came up most often on real teams. You do not need to learn all of them, and I have never met anyone who has.
Key takeaways
- Learn the categories first. The tools inside a category solve the same problem, so the second one in a category takes a fraction of the time the first one did.
- Cloud analytical databases such as Snowflake, Amazon Redshift, Azure Synapse and Google BigQuery separate compute from storage, which is what lets them scale up and down quickly.
- Row based relational databases like SQL Server, MySQL and Postgres usually sit upstream as application back ends, though plenty of companies have run a warehouse on one.
- For the transform step, dbt stands out above everything else in a modern stack. If you are using a tool like Azure Data Factory or AWS Glue, you can handle transforms there instead.
- If you only pick one orchestrator to understand, make it Apache Airflow. It is the one you will run into most.
- Reverse ETL, with tools like Census, Hightouch and RudderStack, pushes warehouse data back into business applications such as Salesforce.
- Do not skip spreadsheets. You will be moving data in and out of Excel or Google Sheets constantly, for troubleshooting and for handing results to people.
One note before the list. This was recorded in 2022, so treat it as a snapshot of what was common on teams at that point rather than a current market survey. The categories have held up better than any individual vendor.
Databases
Databases are the foundation, and they split into three groups worth knowing separately.
Cloud analytical databases
The main players here are Snowflake, Amazon Redshift, Azure Synapse and Google BigQuery.
Their defining trait is the separation of compute and storage. On a traditional server those sat together. In these platforms they are separate concepts, which is what lets you scale capacity up and down quickly.
These are built for data warehousing and analytical work, not for transactional workloads.
Traditional relational databases
The alternative is the row based relational database. The big names are SQL Server, MySQL and Postgres.
These are primarily transactional. Everything is row based, and you will often find them behind an application, which makes them a source for your analytical database.
They can also serve as a warehouse. I have worked at a company where SQL Server was the data warehouse, at least for a while.
NoSQL databases
NoSQL covers essentially anything that is not a traditional tabular database: document stores, key value stores, graph designs.
Common names are MongoDB, Elasticsearch, Cassandra, Cosmos DB and DynamoDB on AWS, and the list keeps going.
They are designed for flexibility. There is no structured schema, which is why a lot of developers reach for them when building an application back end.
ELT components
Next is the set of components inside an ETL or ELT process. Each step has its own tool category.
Extract and load: batch
The main batch loading players to know are Fivetran and Stitch, plus Airbyte, which is a strong open source option.
The cloud providers have their own as well, including Azure Data Factory and AWS Glue.
Extract and load: streaming
For streaming, the biggest one to know is Apache Kafka. AWS has Kinesis, which is very common, and the other cloud platforms have their own offerings too.
If you only learn one, learn Kafka.
Either way, the goal of all of these is the same: pull data out of a source and land it in a data warehouse or landing zone so you can transform it.
Transform
One tool stands out above everything else for the transform step: dbt. The name stands for data build tool.
It is a code based, SQL based tool for writing transformations on top of your staged, landed raw data and turning it into custom data models your analytics can use.
If you are already using an ETL tool like Data Factory or Glue, you can handle transformations inside those instead.
Reverse ETL
Reverse ETL was newer on the block at the time and it has become an important part of the stack.
It takes your core data warehouse, the single source of truth, and syncs data back out to your business applications. Think pushing warehouse data straight into Salesforce instead of building a separate report to match it.
The big players are Census, which was one of the originals, plus Hightouch and RudderStack.
Version control and automation
Version control
There are two big players: GitHub and GitLab. As an engineer you will spend a lot of time in a version control platform, so being comfortable in one or both pays off quickly.
CI/CD
Continuous integration and continuous deployment is a core part of the modern stack, and it is much of what people mean by DataOps.
You can implement it with custom scripts, but the two main tools live inside the version control platforms you already use: GitHub Actions and GitLab pipelines.
Both do the same job of automating workflows directly alongside your project, which matters because so many data projects are code based now.
Task orchestration and scheduling
You have an ETL process and you have databases. None of it runs on its own.
The names to be aware of are Apache Airflow, Jenkins and Luigi. Before these tools, a lot of scheduling lived inside an existing product, such as SQL Server Agent jobs.
If I had to pick one to start with, it would be Airflow. It is the most common one in my experience, and learning how one orchestrator works carries over to the rest.
Infrastructure
Infrastructure management
Sticking with the theme of code based tools, the two big names for infrastructure management are Terraform and Ansible.
Whether you are setting up services on a cloud platform, triggering containers, or building out a Snowflake environment with its objects and roles, these let you interact with your infrastructure through code and keep it version controlled.
Containers
This one is short, because there is really one major player: Docker.
If you want to learn containers, download Docker and work through their getting started tutorial. You can learn the whole model from your local machine.
Container orchestration
Once you see how easy it is to spin up a container, you end up with a lot of them. That is what happens at large companies, and it creates a management problem.
The name to know there is Kubernetes. Plenty of other tools build on top of it, but Kubernetes is where to start.
BI and analytics
Reporting and data visualization
Call these the big three: Power BI, Tableau and Looker.
Which one you learn usually depends on the company rather than on preference, because the choice tends to be baked into the stack already. A Microsoft shop will have Power BI, a Google shop will have Looker.
They all solve a similar problem: an analytics and visualization layer on top of your data.
An open source option
Those three are paid, subscription based platforms. One worth pointing out separately is Metabase, which is fully open source.
You can pay for a hosted version, or run the free version yourself in a container, either on a network server or locally just to see it. It connects to most of the common databases.
It will not be as advanced as the big platforms, but it gets close, and it is a good way to have a visualization layer in a playground stack.
Do not forget spreadsheets
With all the newer platforms, it is easy to overlook the one tool you will actually use every week: the spreadsheet, usually Microsoft Excel or Google Sheets.
You will be moving data in and out of databases and tools into a spreadsheet constantly. Sometimes for custom troubleshooting, sometimes because you need to package something up and hand it to a person.
Know your way around the functions and features. It comes up more than people expect.
How to use a list like this
You do not need to learn all of these to be a great data engineer. I have not used them all in depth, and I have never met anyone who is an expert at every one.
Pick one tool you do not know today and start learning it. When you are comfortable, move on to the next.
The point of a list like this is awareness and perspective, not a checklist. I wrote more about that in You Don't Need To Know Every Data Tool & Skill.
Key terms
Separation of compute and storage
The cloud database design where query processing and stored data are independent resources, so you can scale either one up or down without touching the other.
NoSQL database
Any database that is not a traditional tabular design, including document, key value and graph stores, typically without a fixed schema.
Reverse ETL
Syncing data from your warehouse back out into business applications such as Salesforce, so teams work from the same numbers in the tools they already use.
Task orchestration
The layer that schedules and sequences the jobs in your pipeline, since none of the extract, load and transform steps run on their own.
Infrastructure as code
Defining servers, cloud services and database objects in version controlled code rather than clicking through a console, using tools like Terraform and Ansible.
Common questions
What tools should a data engineer learn first?
Start with a database and SQL, then add one extract and load tool and one transform tool so you can move data end to end. Version control comes next because everything else assumes it. Orchestration and infrastructure can wait until you have something worth scheduling.
Do I need to know both cloud and traditional databases?
Yes, at a basic level. Row based databases like SQL Server, MySQL and Postgres are usually the sources feeding your warehouse, so you will read from them even if your analytics live in Snowflake or BigQuery.
Is dbt required for data engineering?
Not required, but it is the standout tool for the transform step in a modern stack, and it is worth understanding either way. If your pipeline runs through something like Azure Data Factory or AWS Glue, you can do transformations there instead.
Which orchestrator should I learn?
Apache Airflow is the most common in my experience, so it is the best single starting point. Jenkins and Luigi come up too. The concepts of dependencies, schedules and retries transfer between all of them.
Do data engineers need Docker and Kubernetes?
Docker is worth learning early because it makes local experimentation easy and shows up all over the stack. Kubernetes matters once you are managing many containers, which is more of a large company problem than a small team one.
Which BI tool should I learn?
Usually whichever one your company already owns, since the choice is typically made by the wider platform. Power BI, Tableau and Looker all solve the same problem. Metabase is a good free option for practicing on your own.
Related reading
- You Don't Need To Know Every Data Tool & Skill
- The 10 Key MDS Components: Part 1 (Essentials)
- How to Pick Tools for a Data Stack
- What Skills Do You Need As a Data Engineer?
Final takeaway
When I help a small team pick a stack, we are choosing one tool per category, not collecting them. Learn the categories and what each one is responsible for, and any specific vendor becomes a detail you can swap out later.
Additional Free Resources
Starter Guides & Checklists
Explore additional free resources built on the same patterns I use with real clients so you can build your own with structure and confidence. Topics include data architecture, modeling and more specifically for small data teams.
