2 min lesson
SQL & Python craft for scale
Explain what the example in "SQL & Python craft for scale" is doing and why it matters.
Step 1 of 3
The technical screen for this role is data-shaped, not algorithmic: SQL on event/interaction data plus applied stats. They're checking whether you can actually pull p95 latency by model from a billion-row table without melting the warehouse - and whether someone could rerun your number tomorrow.
Three skills carry most screens. Window functions for sessionization and funnels, percentile aggregations for latency and cost-aware querying so your scan touches partitions instead of the whole table. Show all three on one realistic problem and you've answered the round.
Learn more
Advanced table
Cost-aware querying on billions of rows
Cost-aware querying on billions of rows
- Technique
- Partition pruning
- What it buys
- Scan days, not years
- When to reach for it
- Always - filter on the partition key first
- Technique
- Pre-aggregation / rollups
- What it buys
- Dashboards read a small table
- When to reach for it
- Repeated, well-known reliability questions
- Technique
- Sampling
- What it buys
- Fast exploratory answers
- When to reach for it
- Iterating on a hypothesis, not a final number
- Technique
- Approximate quantiles
- What it buys
- Tail metrics at scale
- When to reach for it
- p95/p99 over huge groups
- Technique
- Column pruning
- What it buys
- Less I/O on wide event tables
- When to reach for it
- Select only the columns you need
| Technique | What it buys | When to reach for it |
|---|---|---|
| Partition pruning | Scan days, not years | Always - filter on the partition key first |
| Pre-aggregation / rollups | Dashboards read a small table | Repeated, well-known reliability questions |
| Sampling | Fast exploratory answers | Iterating on a hypothesis, not a final number |
| Approximate quantiles | Tail metrics at scale | p95/p99 over huge groups |
| Column pruning | Less I/O on wide event tables | Select only the columns you need |
On billions of rows, the query plan is part of the analysis - a correct number you can't afford is not an answer.
Python for log analysis and detection
SQL gets you aggregates; Python gets you the modeling SQL can't express. Pull a manageable slice with pandas or polars, run hypothesis tests with scipy/statsmodels and prototype lightweight detection - a change-point or anomaly check - before anyone builds the production version.
from statsmodels.stats.proportion import proportions_ztest
# successes / totals for control (pre) and treatment (post)
successes = [pre_success, post_success]
totals = [pre_total, post_total]
stat, pval = proportions_ztest(count=successes, nobs=totals)
delta = post_success / post_total - pre_success / pre_total
print(f"success-rate delta={delta:+.3%} p={pval:.4f}")
# A real regression should clear significance AND be large enough to matterAt billions of interactions, a 0.01% change is statistically significant and operationally meaningless. Always pair the p-value with the effect size and a materiality bar (does this move the error budget, does it affect a meaningful number of users). Reporting significance alone in this role reads as junior.