1 of 34

Below the tip of the Iceberg

How Wikimedia reduced reporting latency 10x using dbt and Iceberg

2 of 34

Agenda

Intros
Context + Motivation
Solution Overview: Building an Open-Source Data Lake
Tooling Deep Dive
Outcomes & Impact
Learnings & Takeaways

3 of 34

Meet the speakers

Joseph Mando

Lead Analytics Engineer

Wikimedia Foundation

Avishua Stein

Senior SRE - Data Engineering

Wikimedia Foundation

Speaker Three

Job Title

Company

3

4 of 34

Context + Motivation

5 of 34

The Wikimedia Foundation

Nonprofit organization hosting Wikipedia and other free knowledge projects

Mission to make knowledge sharing easy and accessible

Funding is powered by small donations from supporters worldwide

6 of 34

How does analytics help?

Data-driven fundraising through building dashboards and data models for effective fundraising

Key insights like A/B testing results and campaign health metrics

Empowers fundraising teams to secure funds for the Wikimedia Foundation and its work

7 of 34

all_donations

In order to support data-driven fundraising, analytics:

Provides comprehensive revenue reporting
All_donations was built to accomplish this

Consolidates 6 years of clean and attributed donation data
Single source of truth

Fundraising analytics often reports on revenue and donations, attributed to the various sources like banners and email

In order to accomplish this, we built the all_donations model in our database

This included donations back six fiscal years, with attribution by source, location, and more.

All data for this model was being pulled and joined directly from our crm database which is mysql/mariadb.

The crm is civi crm.

This resulted in a table with about 80 million rows and 100+ columns from 12 joins

This allowed for reporting and analysis to be done from the same source

So rather than always having to run ad hoc queries with complex joins, analysts can just reference this donations table
The BI tool is also built on this, so results would tie out across ad hoc requests and already circulated dashboards

8 of 34

Building all_donations

Patched together automation with python scripts scheduled via cron jobs
5-hour full refresh
Three separate scripts for different refresh frequencies
Identical queries across scripts

All have to be updated and tested for even a single change

9 of 34

All_donations Limitations

Adding and backfilling a new columns required up to one week lead time plus development

Limited to 6 years of data due to performance constraints

Missing long-term patterns and insights

Slow to query

10 of 34

The rest of the models

Downstream models had the same constraints

Every model change required updates across multiple scripts
Long lead times to fully populate new columns

No built in way to see and test model dependencies

11 of 34

The need for change

3+ minute query times created poor user experience in our BI tools

Aggregated models provided speed but increased tech debt

Growing demand for deeper insights drive more model complexity
We simply had outgrown mysql/mariadb and our model development process

12 of 34

The Need For Change

Question

How has revenue from banners grown year over year?

Self Serve

Navigate to dashboard and wait for it to load
Add filters and wait for more loading

Refine

See data
Realize it is limited
Cannot drill down
Need to filter further
More waiting

Request

Request adhoc data from analytics

Here is an example of how this impacted end users
Say I am a fundraiser and I have what I think is a rather simple question I do not need to bother analytics with.

In this instance: How has revenue from banners grown year over year since 2018?

I navigate to a dashboard I know has year over year data with some filters I can apply and I wait for it to load.

This could 2-5 minutes depending on the underlying data structure

It loads, I add my filters

I wait a few more minutes for all views to load.

I see the data and realize it is limited in time frame and I cannot really drill down for insights

Or maybe I need to filter again, which means more waiting

Inevitably the wait times and lack of drill down lead me to request the data ad hoc from analytics

Again even our fundraisers had outgrown this stack and workflow

13 of 34

Solution Overview: Building an Open-Source Data Lakehouse

14 of 34

Moving to a new stack

Why On-Prem & Open Source?

Stronger data security
Private deployments reduce attack surface
Full software control (no reliance on SaaS vendors)

Wikimedia-Specific Benefits

Aligns with existing on-prem FR Tech systems
Supports culture of in-house infrastructure & development

Other Advantages

Cost-effective hardware
No vendor lock-in or forced updates

We designed the new platform to be entirely open source and on-prem— **CLICK** primarily to ensure data security. **CLICK** Hosting on-prem allows us to **CLICK** deploy privately, reducing our cybersecurity attack surface. **CLICK** Open source gives us full control of the software, so we don’t need to rely on SaaS vendors for data protection.**CLICK**

For Wikimedia specifically, this approach fits well:

**CLICK**

FR Tech already runs on-prem systems, so it’s easier to build on processes and tools we know. **CLICK**
The foundation has a strong culture of maintaining and developing infrastructure internally as well as using open source, and this continues that tradition.
**CLICK**

Other advantages of keeping the stack open source and on-prem include: **CLICK**

Lower hardware costs compared to cloud. **CLICK**
No vendor lock-in or forced upgrades.

15 of 34

Our new analytics stack

16 of 34

Data lakehouses

Storage and compute separated for flexibility
Modern table formats (e.g. Iceberg) enable interoperability
Benefits:

Lower cost + flexible compute choices
Near data warehouse query performance
ACID transactions → improved data quality
Handles structured, semi-structured, and unstructured data
Supports schema & partition evolution

As you may have inferred from the previous diagram, we chose a data lakehouse as the architecture for our new data platform. Before explaining what a data lakehouse is, I want to quickly point out a key differentiator between two analytics architectures that came before it:

A Data Warehouse is an architecture that among other things, combines compute and storage. Compute refers to capability of transforming and processing data. E.g. joining two dataset or calculating aggregates. Storage is the capability of a system to actually store the data, usually in a special format that’s conducive to analytics. Data warehouses (and databases) combine these features so they are responsible for both manipulating data as well as storing that data.

Data Lakes on the other hand, separate compute and storage. So your data might be stored in an s3 bucket or hadoop cluster. But you’ll provide a separate query engine like Spark, or Trino to transform the data before writing it to elsewhere for storage.

So what is a Data Lakehouse?

**Click**

Lakehouses address the shortcomings of data warehouses and data lakes. Like data lakes, Lakehouses keep storage and compute separate for flexibility but **CLICK** use modern table formats like Iceberg to ensure compute engines and storage work seamlessly together. Tools (or technically ‘specs’ and their implementations) like Iceberg enable this by intelligently using metadata to help the compute and storage systems communicate with each other. **CLICK** This Lakehouse approach combines the best properties of both worlds: **CLICK** lower cost, more flexible compute options, **CLICK** performance close to warehouses, **CLICK** ACID compliance for data quality, **CLICK** support for a wide range of data types,**CLICK** evolving schemas, and more

17 of 34

Data architectures

18 of 34

Tooling deep dive

19 of 34

Components: Trino

Query engines transform data (compute)

Benefits of Trino:

Performant
Large community
Federated
Integrations

dbt
MinIO/ Iceberg
Metabase

**CLICK**

Recall that compute, or Query Engines as i’ll now use the two interchangeably, are responsible for taking commands like SQL or spark, and executing them on the data

We use Trino as our query engine. **CLICK**

We chose trino because: **CLICK**

It’s performant- we can toss some pretty large datasets at trino and it handles them well **CLICK**
Trino has a large community. There’s a large slack group where the maintainers hang out and they’re decently responsive. The git repo is also active **CLICK**
Trino is federated and can run queries on data that lives outside our lakehouse storage. We use it to query our application database, kafka streams, and object storage. This means we dont have to store as many copies of the data **CLICK**
There are many integrations with other parts of our stack like: **CLICK** dbt, **CLICK** Iceberg,**CLICK** and metabase

20 of 34

Components: Iceberg & Modern table formats

Data lakehouses rely on modern table formats for ACID guarantees and increased performance

Table formats like Iceberg bring new capabilities to data lakes:

Better metadata to improve query performance
ACID compliance
Schema evolution, smarter partitioning, time travel

Many of the benefits of the lakehouse architecture come from modern table formats. For us, Iceberg is the key. ** CLICK ** ** CLICK **

Iceberg defines how metadata is stored and managed across query engines and storage. ** CLICK **

This metadata makes analytics faster and more reliable by helping query engines optimize queries and locate relevant data. ** CLICK **

Iceberg also enables ACID transactions, which ensure data consistency and quality. ** CLICK **

On top of that, it offers features like schema evolution, hidden partitioning, and time travel, making it easier to manage large, complex datasets.

Lakehouses depend on table formats to connect query engines with object storage. We chose Iceberg because it integrates best with our stack and is built on a strong commitment to openness.

Many of the lakehouse architecture features i mentioned earlier are really modern table format features. Iceberg is a key component of our lakehouse architecture. It’s a table format specification that defines what metadata is kept and how it’s managed by query engines and storage. This metadata helps make analytics both faster and more reliable. Query engines use this metadata to optimize queries and know where to search for relevant data in object storage

Iceberg also enables ACID transactions, ensuring data consistency and quality. Beyond that, it provides other powerful features like schema evolution, hidden partitioning, and time travel so you can query historical versions of your data. These capabilities make it much easier to manage large, complex datasets in a lakehouse environment.

Lakehouses require table formats so query engines and object storage can interoperate effectively. Of the available table format options, we decided to use iceberg because the other tools in our stack worked best with Iceberg and we felt best about its continued openness.

21 of 34

Components: dbt

Y’all know why dbt is great:

Version control
Run queries in parallel
Great community

Works with our tools

Trino
MinIO/ Iceberg
Dagster
Metabase

22 of 34

Outcomes & Impact

23 of 34

Improved Performance

5+ hours for all_donations to about 1.5 hours to build all models

Lead time of data backfills no longer one week

6 years of revenue data in 8+ minutes to 18 years in 8 seconds

BI tool timeouts to (closer to) instant data access
More time for insights, less time waiting for data

Due the aforementioned work, we have seen huge improvements in performance due to just query speed.

Rather than 5+ hours to build just our main donations model, all of our sources and marts now build in less than 1.5 hours

This means faster development as well as faster deployment to production

And rather than new columns being backfilled over the weekend, we can do it same day once the development work is complete

We have also gone from taking over 8 minutes to query just 6 years of aggregated revenue data to getting 18 years in just over 8 seconds

This alone has improved the end user experience

Because instead of timeouts, users are now seeing more enriched data than ever in our BI Tool

This also allows our analysts to be more productive and deep dive into complex analysis, rather than waiting for queries to run

24 of 34

The change

Question

How has revenue for banners grown year over year?

Self Serve

Navigate to dashboard
Add filters
See data
Initial question answered

Refine

Filter further
Drill Down

Request

Request more complex analysis from analytics

Let go back to that example from earlier
Again, say I start out just wanting to know how has revenue for banners grown year over year?
I navigate to that dashboard I mentioned earlier and have probably waited less than a minute and it has all loaded.
I add my filters, and less than a minute later I see more data than I need, but my initial question is answered
But also maybe I see a spike in a particular year I have not seen before (because previously we only had the 6 fiscal years available in reporting),
So I drill down to the raw data, maybe doing more filtering to try and see what was responsible for the growth.
Rather than just requesting the basic data, maybe my exploration has led me to a request for a more in depth analysis
Or even just some questions on how I may be able to dive deeper myself since query time is not such a variable

25 of 34

End-user feedback

OMG this is amazing!

I’ve answered so many questions for myself I have been wondering for years, in five minutes!

26 of 34

donations model in dbt

SQL largely unchanged
Replaced custom Python scripts with SQL files ran through dbt commands
Macros and seeds eliminate some repetition

27 of 34

Macros in dbt

Rather than repeating this case statement we can now just call this macro

Results in less and cleaner SQL

28 of 34

Building the donations table in dbt

dbt's incremental functionality replaces multi-script architecture
Same SQL file handles both incremental and full builds
Automation with Dagster replaces manual cron job management
Macros and Jinja enable environment and incremental-specific builds

29 of 34

Other models in dbt

Downstream and upstream models also use incremental functionality
Less files to create and maintain
Lineage graphs show dependencies

30 of 34

Other advantages of dbt

Built-in documentation and testing
Column descriptions visible in BI tools
Seed files replace complex case statements

31 of 34

Learnings & Takeaways

32 of 34

Learnings & takeaways

Set up logging and alerting early
Open source often does not include a manual
Experiment with configs!
Reducing threads in dbt improved performance and caused fewer failures!
Consider TCO!
Any columnar database will likely perform analytics better than row-based

Now I want to share some takeaways from our journey so far **CLICK**

Set up logging and alerting early. It’s hard to diagnose issues across systems if you dont have visibility into what each system is doing **CLICK**
Compared to a tool like Snowflake that has a ton of docs, quickstarts, tutorials, a great UI, etc - Trino has a weak UI and relatively few resources for getting started and performance tuning (but shout out to ‘trino the definitive guide’ for being an excellent resource) **CLICK**
Experimentation can drive performance. **CLICK** For example, We had an issue where our hive metastore db was failing to keep up with queries. We found that reducing the dbt threads reduced the load on this database, improving stability without degrading performance! **CLICK**
There’s a tradeoff between building with open source vs managed services. On the open-source side, you pay engineers to do the difficult work of implementing, deploying, and maintaining these ‘batteries-not-included’ tools. On the other side- you pay a premium for the convenience of managed services. So before you decide to use open source tooling, make sure you have the engineering resources do the hard work of connecting systems and deploying infrastructure. **CLICK**
Finally, this is obvious to some, but the main driver of the query speed improvements came from switching our query engine from the row-based mariaDB to the columnar Trino. So if you’re looking to increase query performance, consider copying data from your application database to a system designed for analytics and running your queries there.

33 of 34

Check out Wikimedia

Donate

Join Us

33

34 of 34

Thank you

Please be sure to take the post-session survey

34