Low Throughput Pipelines

An easy SQL interview practice problem on DataDriven. Write the query and run it against a real database, with instant feedback.

Domain

SQL

Difficulty

easy

Seniority

L3

Asked in

Spotify

Problem

Every row of data_pipes is a single pipeline run, carrying both the rows it read in and the rows it wrote out. List each run that wrote out fewer than 2000 rows, with its pipeline name and output count, highest count first.

Pipelines barely moving data.

Tables

data_pipes

Columns: pipe_id INTEGER, pipe_name TEXT, status TEXT, rows_in INTEGER, rows_out INTEGER, start_at TEXT, dur_secs INTEGER

Expected output

Worked solution and explanation

What this really is

This is a row-level filter in a monitoring costume, and the whole test is the grain. Anyone can write WHERE rows_out < 2000. What separates candidates is noticing that each row of data_pipes is one run, and that the same pipeline shows up again and again, often with the same output count. Reach for GROUP BY or DISTINCT because the ask mentions a pipeline name and a count, and the 44 qualifying runs shrink to 22 lines or fewer. The repeat low-output runs, the exact anomalies being hunted, quietly vanish. The other slip is reading 'rows' as rows_in, which returns a different set of runs entirely.

Step 1: Keep the run as the grain

Nothing in the ask says to summarise a pipeline. 'Each run' means one output line per row of data_pipes, so there is no GROUP BY, no aggregate and no DISTINCT. The two etl_orders runs at 1940 are two separate incidents and both belong in the answer.

Step 2: Filter on rows_out, not rows_in

Low output means what the run wrote out. rows_in sits right next to it and filtering on it looks just as plausible, but it measures the input side and selects different runs. WHERE rows_out < 2000 keeps the runs that actually under-delivered.

Step 3: Sort by output count, highest first

One key does it: ORDER BY rows_out DESC. Runs that share a count, such as the eight runs that logged -1, can come back in any order among themselves. The grade checks the sort, not how the ties fall.

SELECT pipe_name, rows_out
FROM data_pipes
WHERE rows_out < 2000
ORDER BY rows_out DESC

Rolled up

Adding DISTINCT, or GROUP BY pipe_name, rows_out, returns 22 lines, and grouping by pipe_name alone returns one per pipeline. Each repeated run, like dbt_build at 1843 twice, folds into a single line.

One row per run

With no GROUP BY and no DISTINCT, all 44 qualifying runs come back, duplicates included, because two runs with the same count are two things that went wrong.

Common follow-up questions

• The runs with `rows_out = -1` look like a sentinel. How would you report them separately from genuine low output? (Tests whether the candidate questions a value that passes the filter for the wrong reason.)
• Now flag runs whose `rows_out` is under 10% of their `rows_in`. What changes? (Moves from a fixed threshold to a per-row ratio, with NULL and zero `rows_in` to guard against.)
• Product wants one line per pipeline with how many low-output runs it had. How does the query change? (Now the rollup is the ask: a deliberate grain change to `pipe_name` with a count.)