Platform Engineering · Lesson 182

Data Visualization & SQL as a Skill

A dashboard told me a service was fine. The chart was smooth. The numbers were wrong. I had joined two tables and doubled every row, then averaged the doubled rows and called it the truth. This lesson is the fix for that mistake. I start from raw rows, join them on purpose, group them, rank them with window functions, and only then draw the chart. The chart is the last hop. Everything before it is the work that makes the chart honest.

Difficulty: Intermediate · Know this first: Lesson 168, Lesson 179, Lesson 181

What you will build

By the end of this lesson you will have run an EDA on 2,400 real request rows in Postgres, joined them to a deploy table, grouped them by service and day, ranked them with a window function, and drawn two charts from the same numbers with matplotlib and seaborn, plus a plotly figure. You will also have a row-count check you run after every JOIN for the rest of your career.

What you need: Python 3 with pandas, matplotlib, seaborn, and plotly, and a local Postgres. The lab runs against a real local Postgres 16.2, started on this machine with no cloud account. Every number in this lesson comes from that run. If you have no Postgres, the same queries run in SQLite with two small changes, and the lab says which lines change.

What this lesson assumes: Lesson 168 already teaches SELECT, CTEs, indexes, EXPLAIN, and the slow query live catch. This lesson does not re-teach SELECT basics or EXPLAIN. It teaches what is missing: JOINs, GROUP BY and aggregates, HAVING, window functions, the EDA workflow, and the three chart libraries.

The story: one question, from raw rows to a chart

EDA is exploratory data analysis. It is the first hour with a new dataset, before any model and before any dashboard. The workflow has six steps, in order. Ask the question. Look at raw rows. Join the tables you need. Group and aggregate. Compare with window functions. Draw the chart. Skip a step and the chart lies to you with confidence.

The dataset in this lesson is 2,400 requests across four services, checkout, search, auth, and billing, over five days. One deploy landed on day 3. The question is whether checkout got slower after it. The answer is in the data, and the workflow is how I get it out without fooling myself.

The three chart libraries, and when each one earns its keep

LibraryWhat it is good atWhen I reach for itWhat it costs
matplotlibDraws anything, one call at a time. The base the other two stand on.Any chart, and every chart I need to control exactly.More code per chart. You build the chart piece by piece.
seabornStatistical charts in one call on top of matplotlib.The fastest way to a good line or bar chart from a dataframe.Less control than matplotlib, and it hides the details.
plotlyInteractive charts you can hover and zoom.When the reader needs to explore the chart, not just look at it.Heavier output, and the interactivity needs a browser.

The chart this lesson draws, from the real run

Average latency by service, five days, 2,400 requests. The bar chart matplotlib drew from the GROUP BY table:

Average latency by service (ms) checkout 285.4 billing 137.9 search 95.3 auth 59.3 Bars are proportional to the average. Same numbers as the GROUP BY table in the lab.

The line chart tells the other half. Checkout averages 209 ms on days 1 and 2, then 330, 342, and 337 ms on days 3, 4, and 5. The other three services stay flat. The bar chart says checkout is slowest. The line chart says when it got slow, and the answer is the day of the deploy. One chart per half of the question. That is the rule.

Do this now, before the diagram

Write your own question down, one sentence, with a service, a change, and a date in it. Then list the tables you need to answer it, and the column each table joins on. If you cannot name the join column, you do not have the question yet. You have a topic.

Run it as:

When it breaks, I check

One thing to remember

Four paths, one family

The diagram above follows one question from raw rows to a chart. These four paths are the same idea drawn four ways. One question, answered. One question, doubled by a careless JOIN. One rolling average, lagging the step it measures. One question, slowed by a table with no filter. Run each path. One run takes 7.2 seconds across the path, so you can watch each hop light up in turn.

The return rule: the green dot only starts moving after the orange dot reaches the data hop. A return with no request is a lie. The diagram never draws one. The failure replay at the end of Run all four shows the other side of the rule. Run it and watch the orange dot stop at the JOIN and never come back.

Path 1: the clean EDA

Check hop: skipped

One question walks the workflow from the story. Ask, sample, JOIN, GROUP BY, window, chart. The JOIN keeps the count at 2,400. The chart is the data hop, and the answer is born there: checkout jumped on day 3.

Ask0 ms
Sample14 ms
JOIN46 ms
GROUP BY34 ms
Window28 ms
Chart92 ms

Not run yet.

Path 2: the fanout

Window: skipped

Same question, one careless JOIN. The JOIN matches deploy_notes on service only, so 2,400 rows become 4,800. The GROUP BY averages doubled rows. The chart lies. The check hop catches it, because the chart average does not match the table.

Ask0 ms
Sample14 ms
JOIN120 ms
GROUP BY85 ms
Chart lies140 ms
Check catches45 ms

Not run yet.

Path 3: the rolling lag

JOIN: skipped

Checkout's rolling 3-day average, day by day, from the window query in the lab. The daily average steps up on day 3, from 209 to 330. The rolling average lags it by two days: 209, 209, 249, 294, 336. The lag is not an error. It is what a 3-day window does.

Day 1209 ms
Day 2209 ms
Day 3249 ms
Day 4294 ms
Day 5336 ms
Lag namedstep on day 3

Not run yet.

Path 4: the slow scan

Check hop: skipped

The same question with no filter. The sample reads the whole table, the JOIN runs over every row, the GROUP BY spills, the window sorts, and the chart draws 100,000 points. The numbers are right. The trip is slow, and the page feels stuck.

Ask0 ms
Sample40 ms
JOIN180 ms
GROUP BY150 ms
Window120 ms
Chart210 ms

Not run yet.

Where the time goes

Every hop charges rent in milliseconds. The bar below is a worked example for one question through the EDA workflow, not a measurement from a live system, but the shape is honest. The chart hop is the big one, because drawing is the most expensive step in the workflow. At the simulator defaults the JOIN hop costs 46 ms, the GROUP BY hop costs 34 ms, the window hop costs 28 ms, and the chart hop costs 92 ms, so the clean EDA total is 254 ms. Move a simulator slider and watch those four hops and the total move with it. Switch the scenario and watch the JOIN hop or the chart hop change instead.

Rows, groups, or points: move the sliders and watch the trade

The budget bar above shows three fixed stories. This simulator lets you write your own. It answers the trade-off at the heart of this lesson: every row you scan, every group you build, and every point you plot costs time, and the chart hop is where the bill comes due. You set how many rows the table holds, how many groups the GROUP BY builds, how many points the chart draws, and how many tables the JOIN touches. Move a slider and watch the JOIN, GROUP BY, window, and chart hops in the budget bar change. The numbers are a teaching example, built from the formulas below, not live measurements. The shape is the lesson: the chart hop grows fastest, because drawing is the dearest step.

500,000 rows

How many rows the table holds. Ten thousand is a quiet service. Five million is a busy platform at the end of a week.

120 groups

How many groups the GROUP BY builds. Four services times five days is 20 groups. Five thousand groups is a GROUP BY on a column with too many values in it.

5,000 points

How many points the chart draws. Five thousand is a busy chart. One hundred thousand is a smear the reader cannot see.

2 tables

How many tables the JOIN touches. Each extra table is another match the database must make for every row.

Try this: (1) At the defaults, 500,000 rows, 120 groups, 5,000 points, 2 tables, the JOIN hop costs 46 ms, the GROUP BY hop costs 34 ms, the window hop costs 28 ms, and the chart hop costs 92 ms. The budget total is 254 ms, the same as the clean EDA scenario. (2) Drop to 50,000 rows, 20 groups, 500 points, 1 table. The JOIN hop costs 8 ms, the GROUP BY hop costs 14 ms, the window hop costs 14 ms, and the chart hop costs 20 ms. The budget total is 110 ms. Small data is fast everywhere. (3) Push to 5,000,000 rows, 5,000 groups, 100,000 points, 4 tables. The JOIN hop costs 806 ms, the GROUP BY hop costs 295 ms, the window hop costs 362 ms, and the chart hop costs 1,612 ms. The budget total is 3,129 ms. The chart hop is more than half the trip. Aggregate before you plot.

Formulas, in plain terms. JOIN hop ms = 6 plus rows divided by 25,000, times tables joined. GROUP BY hop ms = 12 plus rows divided by 25,000, plus groups divided by 60. Window hop ms = 12 plus rows divided by 50,000, plus groups divided by 20. Chart hop ms = 12 plus points times 0.016. Budget total = 54 plus the four moving hops, where 54 is the fixed hops: ask 0, sample 14, choose 10, check 18, share 12. All divisions are rounded to whole milliseconds.

Lab: run the EDA yourself

Do this now, on your own machine, against a real local Postgres. You will build the dataset, join it on purpose and by accident, group it, and draw two charts from the same numbers. The Python parts and the SQL files run on this machine and their outputs are shown exactly as they ran. Nothing in this lab runs against a live system or a cloud account.

Step 0: build the dataset

Save this file as build_data.py. It starts a local Postgres 16.2 with pgserver, creates three tables, and loads 2,400 synthetic requests across four services and five days, plus one deploy row per service on day 3 and two notes per service. The checkout latency is multiplied by 1.6 from day 3 on, so the deploy shows up in the data. Run it with the venv Python from the build notes.

"""Build the on-call dataset for Lesson 182 in a local Postgres.

Real Postgres 16.2, started by pgserver in /tmp. No cloud, no live
system. The data is synthetic: four services, five days, 2,400 requests.
Every number in this lesson comes from this file and the queries that
follow it. Nothing is invented after the run.
"""
import random

import pgserver

SERVICES = ["checkout", "search", "auth", "billing"]
BASE = {"checkout": 210, "search": 95, "auth": 60, "billing": 140}
ENDPOINTS = {s: [f"/{s}", f"/{s}/v2"] for s in SERVICES}


def main():
    srv = pgserver.get_server("/tmp/pgdata-vizsql")
    uri = srv.get_uri()
    print("postgres:", uri.split("@")[-1])
    srv.psql("DROP DATABASE IF EXISTS oncall_viz;")
    srv.psql("CREATE DATABASE oncall_viz;")
    srv.psql("DROP TABLE IF EXISTS requests; DROP TABLE IF EXISTS deploys; DROP TABLE IF EXISTS deploy_notes;")
    srv.psql("""CREATE TABLE requests (
        id SERIAL PRIMARY KEY, service TEXT, endpoint TEXT,
        status INT, latency_ms INT, day INT);""")
    srv.psql("""CREATE TABLE deploys (
        service TEXT PRIMARY KEY, version TEXT, deployed_day INT);""")
    srv.psql("CREATE TABLE deploy_notes (service TEXT, note TEXT);")
    rng = random.Random(7)
    rows = []
    for day in range(1, 6):
        for service in SERVICES:
            for _ in range(120):
                lat = max(8, int(rng.gauss(BASE[service], BASE[service] * 0.35)))
                if day >= 3 and service == "checkout":
                    lat = int(lat * 1.6)
                status = 500 if rng.random() < (0.04 if service != "checkout" else 0.09) else 200
                rows.append((service, rng.choice(ENDPOINTS[service]), status, lat, day))
    srv.psql("INSERT INTO requests (service, endpoint, status, latency_ms, day) VALUES " +
             ",".join(f"('{s}','{e}',{st},{la},{d})" for s, e, st, la, d in rows) + ";")
    srv.psql("""INSERT INTO deploys VALUES
        ('checkout', 'v2.14', 3), ('search', 'v1.9', 3),
        ('auth', 'v3.2', 3), ('billing', 'v0.8', 3);""")
    srv.psql("""INSERT INTO deploy_notes VALUES
        ('checkout', 'rolled out slowly'), ('checkout', 'flag on for half the traffic'),
        ('search', 'index rebuilt'), ('search', 'cache warmed'),
        ('auth', 'keys rotated'), ('auth', 'rate limit raised'),
        ('billing', 'retry added'), ('billing', 'timeout raised');""")
    print(srv.psql("SELECT count(*) AS requests FROM requests;"))
    print(srv.psql("SELECT count(*) AS deploys FROM deploys;"))
    print(srv.psql("SELECT count(*) AS deploy_notes FROM deploy_notes;"))


if __name__ == "__main__":
    main()
/tmp/vizsql-venv/bin/python build_data.py

Expected output, exactly as it ran on this machine:

 requests
----------
     2400
(1 row)


 deploys
---------
       4
(1 row)


 deploy_notes
--------------
            8
(1 row)

If you have no Postgres, the same lab runs in SQLite. Two lines change: the table creation uses the sqlite3 standard library module instead of pgserver, and SERIAL PRIMARY KEY becomes INTEGER PRIMARY KEY AUTOINCREMENT. The queries below run unchanged, because round(), count(), and avg() exist in both.

Step 1: JOIN, then GROUP BY, then HAVING

Save this file as joins.sql. The first query joins requests to deploys on service and day, and only counts rows from the deploy day on. The second query joins requests to deploy_notes on service only. Run both against the database from step 0.

SELECT r.service, d.version, count(*) AS n, round(avg(r.latency_ms)) AS avg_ms
FROM requests r JOIN deploys d ON r.service = d.service AND r.day >= d.deployed_day
GROUP BY r.service, d.version ORDER BY r.service;
SELECT count(*) AS joined_rows FROM requests r JOIN deploy_notes n ON r.service = n.service;

Expected output, exactly as it ran on this machine:

 service  | version |  n  | avg_ms
----------+---------+-----+--------
 auth     | v3.2    | 360 |     59
 billing  | v0.8    | 360 |    135
 checkout | v2.14   | 360 |    336
 search   | v1.9    | 360 |     97
(4 rows)


 joined_rows
-------------
        4800
(1 row)

Read the two numbers together. The clean JOIN keeps 360 rows per service, three days times 120 rows, and checkout averages 336 ms after its deploy. The careless JOIN returns 4,800 rows, exactly double the 2,400 in the table, because two notes per service match every request. That is the fanout, measured, not theorised.

Now save this file as groupby.sql. The first query groups by service and day with a count, an average, a max, and an error count. The second filters groups with HAVING, after the math.

SELECT service, day, count(*) AS n, round(avg(latency_ms)) AS avg_ms, max(latency_ms) AS max_ms,
sum(CASE WHEN status = 500 THEN 1 ELSE 0 END) AS errors
FROM requests GROUP BY service, day ORDER BY service, day;
SELECT service, round(avg(latency_ms)) AS avg_ms FROM requests GROUP BY service
HAVING avg(latency_ms) > 100 ORDER BY avg_ms DESC;

Expected output, exactly as it ran on this machine:

 service  | day |  n  | avg_ms | max_ms | errors
----------+-----+-----+--------+--------+--------
 auth     |   1 | 120 |     60 |    110 |      8
 auth     |   2 | 120 |    61 |    101 |      8
 auth     |   3 | 120 |     57 |    107 |      5
 auth     |   4 | 120 |    60 |    116 |      4
 auth     |   5 | 120 |     59 |    107 |      3
 billing  |   1 | 120 |    144 |    287 |      6
 billing  |   2 | 120 |    139 |    296 |      3
 billing  |   3 | 120 |    134 |    264 |      5
 billing  |   4 | 120 |    133 |    246 |      5
 billing  |   5 | 120 |    139 |    257 |      6
 checkout |   1 | 120 |    209 |    355 |     10
 checkout |   2 | 120 |    209 |    432 |     11
 checkout |   3 | 120 |    330 |    577 |     15
 checkout |   4 | 120 |    342 |    628 |      9
 checkout |   5 | 120 |    337 |    616 |      8
 search   |   1 | 120 |     94 |    162 |      4
 search   |   2 | 120 |    91 |    212 |      4
 search   |   3 | 120 |    98 |    164 |      4
 search   |   4 | 120 |    98 |    193 |      2
 search   |   5 | 120 |     96 |    176 |      6
(20 rows)


 service  | avg_ms
----------+--------
 checkout |    285
 billing  |    138
(2 rows)

Twenty groups, four services times five days, 120 rows each. Checkout jumps from 209 ms to 330 ms on day 3 and stays up. The HAVING query keeps two services, checkout at 285 and billing at 138. WHERE would have filtered rows before the math. HAVING filters groups after it. That is the whole difference, and it is the difference that matters here.

Step 2: describe it, then draw it

Save this file as eda_charts.py. It reads the requests table from a CSV dump of the database, describes the latency column, counts statuses, averages by service, and draws two charts: a matplotlib bar chart of average latency by service and a seaborn line chart of daily averages. It also builds the same line chart in plotly and prints the trace names.

"""EDA + charts for Lesson 182. Reads the CSV the lab dumps from Postgres.

Run with system python3 (pandas, matplotlib, seaborn, plotly are already
installed there). Saves avg_latency_by_service.png and
checkout_daily_avg.png next to this file and prints the numbers the
lesson quotes. Nothing here is invented: every value comes from the CSV.
"""
import pandas as pd
import matplotlib
matplotlib.use("Agg")
import matplotlib.pyplot as plt
import seaborn as sns
import plotly.express as px
from pathlib import Path

WORK = Path(__file__).resolve().parent
df = pd.read_csv(WORK / "requests.csv")
print("rows:", len(df), "columns:", list(df.columns))
print("missing per column:")
print(df.isna().sum().to_string())
print("describe latency_ms:")
print(df["latency_ms"].describe().round(1).to_string())
print("status counts:")
print(df["status"].value_counts().to_string())
avg = df.groupby("service")["latency_ms"].mean().round(1).sort_values(ascending=False)
print("avg latency by service:")
print(avg.to_string())
daily = df.groupby(["service", "day"])["latency_ms"].mean().round(1).unstack(0)
print("daily avg latency:")
print(daily.to_string())
print("checkout day 1 vs day 3 avg:", round(daily.loc[1, "checkout"], 1), "->", round(daily.loc[3, "checkout"], 1))

plt.figure(figsize=(7, 4))
ax = avg.plot(kind="bar", color="#b3541e")
ax.set_ylabel("avg latency (ms)")
ax.set_title("Average latency by service, 5 days, 2,400 requests")
ax.set_xlabel("")
plt.tight_layout()
plt.savefig(WORK / "avg_latency_by_service.png", dpi=120)
plt.close()

plt.figure(figsize=(7, 4))
sns.lineplot(data=df.groupby(["day", "service"])["latency_ms"].mean().reset_index(),
             x="day", y="latency_ms", hue="service", marker="o")
plt.ylabel("avg latency (ms)")
plt.title("Daily average latency by service")
plt.tight_layout()
plt.savefig(WORK / "checkout_daily_avg.png", dpi=120)
plt.close()

fig = px.line(df.groupby(["day", "service"])["latency_ms"].mean().reset_index(),
              x="day", y="latency_ms", color="service",
              title="Daily average latency by service")
print("plotly traces:", [t.name for t in fig.data], "| points per trace:", len(fig.data[0].x))
print("figures saved:", sorted(p.name for p in WORK.glob("*.png")))
python3 eda_charts.py

Expected output, exactly as it ran on this machine:

rows: 2400 columns: ['service', 'endpoint', 'status', 'latency_ms', 'day']
missing per column:
service       0
endpoint      0
status        0
latency_ms    0
day           0
describe latency_ms:
count    2400.0
mean      144.5
std       108.3
min         8.0
25%        69.0
50%       110.0
75%       182.0
max       628.0
status counts:
status
200    2274
500     126
avg latency by service:
service
checkout    285.4
billing     137.9
search       95.3
auth         59.3
daily avg latency:
service  auth  billing  checkout  search
day
1        59.6    144.2     208.6    93.8
2        60.5    139.4     209.2    90.8
3        56.9    133.9     330.0    98.1
4        60.5    133.1     342.3    97.7
5        59.1    139.0     336.6    96.3
checkout day 1 vs day 3 avg: 208.6 -> 330.0
plotly traces: ['auth', 'billing', 'checkout', 'search'] | points per trace: 5
figures saved: ['avg_latency_by_service.png', 'checkout_daily_avg.png']

Zero missing values in every column. 2,274 requests answered 200 and 126 answered 500. The mean latency is 144.5 ms, the median is 110 ms, and the max is 628 ms. The mean sits above the median, so the slow tail pulls the average up. That is why the next lesson in this track reaches for percentiles. The bar chart in the story section is drawn from these exact numbers, and the line chart is the daily table above, drawn as lines.

Check yourself

Change the JOIN in joins.sql to join deploy_notes on service AND note, then run it again. The count falls back to 2,400. Then change the HAVING threshold from 100 to 200 and run groupby.sql again. Only checkout survives. A threshold you cannot move on purpose is a threshold you do not understand yet.

Clean up

Stop the Postgres server and delete nothing else. The database lives in a temporary directory and the CSV and the two PNG files live next to these scripts, so you can re-run the charts without re-building the data.

Check yourself

Three questions. No score is saved anywhere. This is just you against the material.

Basic to master

Seeing data clearly is not one skill. It is four levels of the same job, and every team I have worked with sits on one of them. The levels are not about how many libraries you can name. They are about whether the chart changes what the team does. Read them in order. Each level fixes the failure that hurt the most at the level below it.

Basic · Look at raw rows and describe them

At the basic level, you look at the data before you summarise it. You run describe() on the numbers, you count missing values, you count the statuses. The failure that ends this level is the first chart. A chart drawn from data you never looked at is a guess with colors, and it will be wrong in a way you cannot see, because you never saw the rows.

Do this: run step 2 of the lab. Read the describe output and write down the mean, the median, the max, and the missing count. The mean is 144.5 ms and the median is 110 ms. Write down why they differ before you draw anything.

Intermediate · JOIN, GROUP BY, HAVING

At the intermediate level, you join on purpose and you group on purpose. You count rows before and after every JOIN. You group by service and day, and you filter groups with HAVING after the math. The failure that ends this level is the JOIN that doubles your rows. It will happen. The count check is what catches it.

Do this: run the JOIN query from the lab, then change the JOIN to the notes table and watch the count double to 4,800. Change it back. Then change the HAVING threshold from 100 to 200 and watch billing drop out of the answer.

Advanced · Window functions

At the advanced level, you compare rows without collapsing them. RANK, LAG, rolling averages, and the frame clause. You know when a window beats a GROUP BY and when it does not. A window ranks the race. A GROUP BY ends it. The failure that ends this level is the wrong partition. Partition by day ranks services within a day. Partition by service ranks days within a service. Both are right answers to different questions, and the wrong one is silent.

Do this: run the rolling query from Go deeper, then change the frame from 2 PRECEDING to 1 PRECEDING and watch the lag shrink from two days to one. Then change PARTITION BY service to PARTITION BY day and read what the numbers mean now.

Master · The chart is a contract

At the master level, every chart links to its query, every query lives in the repo, and the EDA notebook re-runs from scratch. A chart that cannot be re-run is deleted, no matter how good it looks. The team reads the dashboard and trusts it, because the dashboard is built from queries anyone can re-run, and the queries are built from tables anyone can count.

Do this: take one chart from your own work, find its query, and re-run it. If you cannot find the query, the chart is a rumor. Write the query down, link it to the chart, and re-draw the chart from the query.

Go deeper

Window functions in full

A window function has three parts, and each one is a decision. PARTITION BY cuts the rows into races. ORDER BY decides who wins each race. The frame clause decides which rows the function can see. RANK() gives ties the same rank and skips the next number, so two rows ranked 1 are followed by a row ranked 3. ROW_NUMBER() never ties: it numbers rows 1, 2, 3 even when the values are equal. DENSE_RANK() ties like RANK() but never skips. LAG() reads the previous row in the partition, so LAG(avg) over days gives you yesterday. LEAD() reads the next row. The frame ROWS BETWEEN 2 PRECEDING AND CURRENT ROW is what makes the rolling average roll over three days, and it is also what makes it lag a step by two days. Change the frame to 1 PRECEDING and the lag shrinks to one day. Change it to UNBOUNDED PRECEDING and the average stops rolling and becomes a running average from day 1.

Save this file as window.sql and run it against the lab database. The first query ranks services within each day. The second is the rolling 3-day average from the diagram.

SELECT day, service, round(avg(latency_ms)) AS avg_ms,
rank() OVER (PARTITION BY day ORDER BY avg(latency_ms) DESC) AS slowest_rank
FROM requests GROUP BY day, service ORDER BY day, slowest_rank;
SELECT service, day, round(avg(latency_ms)) AS avg_ms,
round(avg(avg(latency_ms)) OVER (PARTITION BY service ORDER BY day
ROWS BETWEEN 2 PRECEDING AND CURRENT ROW)) AS rolling_3day
FROM requests GROUP BY service, day ORDER BY service, day;

What it returns, exactly as it ran: checkout ranks 1 on all five days. The rolling 3-day average for checkout reads 209, 209, 249, 294, 336. The other services barely move. Auth rolls 60, 60, 59, 59, 59. The window keeps all 20 rows and adds a column. GROUP BY would have collapsed them.

Seaborn or plotly: the choice is about the reader

Seaborn is the fastest way to a good static chart. One call, one line chart, done. The chart goes into a report and never changes. Plotly is the choice when the reader must hover a point and read its value, or zoom into day 3 and day 4. The plotly figure in the lab has four traces, one per service, five points each. Twenty points, and every one of them is readable on hover. A static chart with twenty points is fine. A static chart with 100,000 points is a smear. That is the real difference between the libraries: not the look, but what the reader can do with the chart after you draw it.

Sampling, said out loud

When the table holds 5,000,000 rows, the EDA does not read all of them. It reads a random sample of 5,000 and says so on the chart, in words, next to the title. A sample answers in seconds. The full scan answers overnight. The dishonest version is a chart drawn from a sample that never says it is a sample. Write the sample size on the chart. The reader deserves to know what they are looking at.

What changes at 10x and 100x

At 10x the rows, the GROUP BY spills to disk and the fix is a filter before the GROUP BY, not a bigger machine. Filter to the last 24 hours first, then group. At 10x the groups, the GROUP BY column has too many values in it, and the fix is to group by a coarser column: day instead of hour, service instead of endpoint. At 100x, the EDA runs on a sample and the full query runs once, overnight, and the dashboard reads the summary table, not the raw table. The workflow does not change. The size of the table it runs on does.

Further reading

PostgreSQL documentation, Window Functions. The tutorial names the frame clause and the ranking functions this lesson uses. Matplotlib gallery. The bar chart and the line chart in this lesson are both in the gallery, with code. Seaborn example gallery. The line chart with one call per hue is the pattern the lab uses. All three pages were checked on November 21, 2026.

One time it broke

What broke

A dashboard showed the error rate falling after a deploy, and the team celebrated. The query joined requests to deploys with an INNER JOIN on service and day. One service had no deploy row that day, so its requests dropped out of the JOIN. The failing service vanished from the chart, and the error rate fell because the errors were gone from the data, not from the system. The chart was smooth. The service was down.

Timeline

  • 09:00Deploy ships. One service has no deploy row for that day.
  • 09:30The dashboard shows the error rate falling. The team posts the good news.
  • 11:00A user reports the service is down. The dashboard still says it is fine.
  • 11:20I count rows before and after the JOIN. 600 rows are missing. The INNER JOIN dropped them.
  • 11:40The JOIN becomes a LEFT JOIN. The service comes back on the chart, and the error rate jumps to its real value.

What changed after

Two rules. First, count rows before and after every JOIN, and write the count in the query comment. A JOIN that drops rows must say so, in words, where the next person can read it. Second, prefer LEFT JOIN when the right table can miss a row, and say in the comment what a missing match means. An INNER JOIN is a filter. A filter that nobody asked for is a bug with a chart on top of it.

If you remember nothing else

Design exercise

Design the on-call latency EDA and dashboard for four services, 2,400 requests a day growing to 2,400,000 a day, with a deploy table and a notes table. The on-call engineer needs to answer in two minutes whether a deploy made a service slower, and by how much. The dashboard must not lie by omission, and it must stay fast at full size.

Clarifying questions to ask

  • How many rows a day at full size, and how long can the query take at 2 AM?
  • Which table is the source of truth for deploy times, and what happens to a service with no deploy row that day?
  • What counts as slow: an average, or a percentile? The average hides the slow tail.
  • Who reads the dashboard, and do they need to hover and zoom, or just look?

Worked approach

Start with the filter. Filter to the last 24 hours first, then JOIN to deploys on service and day, then GROUP BY service and day with an average and a p95, then a window RANK per day and a rolling 3-day average, then a line chart of daily averages plus a bar chart of overall averages. The filter goes first because every hop after it pays for the rows the filter removes.

Then the size. 2,400,000 rows a day is 100,000 an hour. The GROUP BY has 4 services times 24 hours, 96 groups, and the chart draws 96 points, not 2,400,000. The dashboard reads a summary table that the overnight job builds, not the raw table. The raw table is for the engineer who wants to dig, not for the page that loads at 2 AM.

Then the JOIN that lies. The deploy table can miss a service on a given day, so the JOIN is a LEFT JOIN, and the comment says what a missing match means: no deploy that day, not no traffic. The row count before and after the JOIN is written in the query comment, and the dashboard shows the count next to the chart. A chart that drops a service by accident is the incident from this lesson, repeated.

Trade-offs to name. Average versus p95: the average hides the slow tail and the p95 costs a sort. Sample versus full scan: a sample answers in seconds and the full scan answers overnight, and the chart must say which one it is. INNER versus LEFT JOIN: the INNER JOIN drops a service with no deploy row and the chart lies by omission. Seaborn versus plotly: seaborn for the static report, plotly for the on-call engineer who must hover day 3 and read the value.

Saved in this browser, so the Lessons index can show your progress across the course.

Next in this series: Lesson 183
Data Engineering at Scale

This lesson worked with 2,400 rows on one machine. The next lesson works at the size where one machine stops being enough: Spark, Flink, ingestion, experiment tracking, and the pipelines that move data before any chart ever sees it.

Go to Lesson 183 →