I’ll admit it, as a data engineer I still use Excel. It’s great for exploring unknown datasets or CSVs from stakeholders. There is something just so convenient about seeing data in a tabular format in a tool that everyone has, knows and uses. I’m here to tell you that you can 10x your Excel powers without even using AI. As much as I like the tabular format, the downsides are also very real:

• My CSVs are always differently formatted, so I struggle with turning them from text into rows
• If I have multiple CSVs of the same data (e.g. multiple months or years) there’s a lot of manual work in making the columns match
• If there’s JSON or Parquet data involved, forget it. No Excel.
• If the data is nested, it’s equally painful
• Collaborating with others on the same dataset is a mess of v3_final_dumky_final.xlsx files.

With DuckDB we can tackle all of this with no programming required. If you can remember Excel function names, you can remember the SQL function names you’ll use in DuckDB.

Don’t believe me? Here’s how I query 1.3 million rows over 8 CSV files from different countries containing tracker usage data.

.

If you’re curious, follow along and see what you can do. We will:

1. Figure out where to best run DuckDB for you
2. Learn some key commands to play around with your data
3. Profit

How and where to run DuckDB

Just like Excel, DuckDB has multiple ways of running it. You can run DuckDB as an application on your computer, in a browser or use it to collaborate with multiple people in a cloud data warehouse using Motherduck.

You can run it on your own machine by following the instructions on duckdb.org and then running duckdb -ui to get started. Or create a new database-as-file with duckdb my-playground.duckdb -ui.

Of course you can use any application that connects to a database to query your data like DBeaver or even Tableau. But I think the notebooks of the DuckDB UI are a really nice way to explore data just like you would in Excel.

Getting started: opening a CSV from anywhere

To get started working with data in DuckDB you don’t need more than a CSV file. It doesn’t even have to be a file on your computer as you can easily reference files on a website too. That of course is very convenient if you always want to make sure you have the latest version. Here is how you can access a CSV with metadata for all the freely available books on Project Gutenberg.

create or replace table pg_raw as (select * from read_csv('https://www.gutenberg.org/cache/epub/feeds/pg_catalog.csv'));
select * from pg_raw limit 3;

This will give you ~80K rows with title, author and subjects. It is important to also create the table and not just select from the CSV directly every time because otherwise you will download the same file over and over.

I’m not sure if this happens to you, but I always find that I want to split data from a cell and analyze it at a different granularity. In this case I want to know what subjects are most common, but the subjects are separated by a -- symbol. In Excel I would have to go through a few different steps to split this and then pivot or aggregate the individual subjects. In DuckDB (or SQL in general) I can do it in three simple lines:

from pg_raw, unnest(split(Subjects, ' -- ')) as s
select s.unnest as subject, count(*)
group by 1 order by 2 desc

This will give me a summary of the most popular subjects:

Now, what is this sorcery you might ask. Let’s go through these three lines to see what it does.

from pg_raw, unnest(split(Subjects, ' -- ')) as s

In the previous statement we had created a table called pg_raw which had a column named Subjects. We split this column on the separator --. Now, crucially, this gives us a list of subjects instead of a single line of text. Using unnest means that we go from one row per book to one row per item in the subject list. The , means that we cross join the data to itself. That means that the value of the unnested column changes for each item in the list, but all the other values remain the same. So we basically get one full row per subject instead of one row per book.

Let’s take the second part in one go.

select s.unnest as subject, count(*)
group by subject order by 2 desc

We first select the unnested column and rename it to subject. Because we group by this first column we can count how many rows are present for each value of the subject column. Finally we order by the number of rows with the highest first.

Next Level CSV-ing: combining multiple CSV files in one line

For your local files and files on cloud storage (S3, GCS), DuckDB allows you to use pattern matching. You can replace part of the path with a * to fetch all files that match the pattern. So if I have a folder with multiple years of sales data I can just use /sales/sales_for_year_*.csv to get all files in one go. If I also have a folder per country I can just do /sales/**/sales_for_year_*.csv to also look in every subfolder.

The following example uses this pattern to fetch publically available data on privacy and trackers from Ghostery’s WhoTracksMe project on Amazon S3.

create or replace table whotracksme as (
  from read_csv('s3://data.whotracks.me/20**-01/global/sites_trackers.csv', union_by_name=true, filename = true) 
  select *
)

This can take a while depending on your internet connection as it will download a few large files with websites and trackers for Januari of each year, i.e. 2018-01 to 2025-01. There are two other things that are interesting to note here. First we union by name, that is, we combine each file not by the position of the column (as would happen in Excel), but by the header name of the column. This allows for changing columns and column positions over time, something that’s notoriously hard to solve with Excel. Secondly, by using filename = true we add the filename itself as a column. This allows us to use the name of the file as input in our data, for example when only the filename contains information about the date or year.

Next we want to again summarize our data, because that’s where it get interesting. We’re interested to see how the number of trackers on sites changes over time. There are a few things we need to do to fit our needs. First, by simply taking the first 4 characters of the month column, we get the year the data was collected, which we can then store as a number. Secondly, we want one row per site per year, but we also want some more information on which trackers are actually used. Therefore, we don’t just count, but we also aggregate the tracker names in a list with array_agg. This allows us to keep way more information per row than what would be possible in Excel, without having to do weird string concatenations and splits.

from whotracksme
select 
  site, 
  cast(month[1:4] as INT) as yr,
  country, 
  round(avg(site_rank)) as avg_site_rank, 
  count(distinct tracker) as num_trackers,
  array_agg(distinct tracker) as trackers,
group by all order by site, yr desc

This gives us a nicely formatted table with sites, years and trackers.

The impossible (in Excel): JSON data as tables

You might not be familiar with JSON data, but it is everywhere. Basically every site uses it in some form or another to represent the data you see on the page. If you want to see what it looks like, for example, Reddit allows you to simply turn any page into a JSON format. So if you go to r/duckdb.json you’ll find all posts in machine-readable format.

Reading this into Excel is pretty much impossible, but DuckDB does not mind at all. The following reads the newest 100 posts into a table.

create or replace table reddit_duckdb as (
  select * from read_json_auto('https://www.reddit.com/r/duckdb/new.json?limit=100')
)

Since it is one single object with multiple nestings, we have to then unnest it fully to get to all the 50 or so different columns per post from which we can then transform and select as we like.

with unnested as (
  select unnest(data.children, recursive := true) from reddit_duckdb
)

select 
  title, 
  strftime(to_timestamp(created_utc), '%Y-%m-%d') created_date, 
  ups, 
  downs, 
  upvote_ratio, 
  num_comments, 
  domain, 
  url, 
  author, 
from unnested
order by created_utc desc

This again, gives us a neatly formatted table to explore:

And that folks, is how you leave Excel behind and never look back.