Data Science Stack

SQL declares what data you want and leaves the engine to work out how to fetch it. Joins combine tables, GROUP BY aggregates, window functions compute across rows without collapsing them, and CTEs keep complex logic readable.

It is the highest-leverage skill on this page. Nearly every data role uses it daily, it is the interface to every warehouse, and being genuinely good at it — window functions, query plans, why something is slow — pays back faster than almost anything else you could study.

The two databases you are most likely to be querying, and the comparison that comes up in nearly every data interview. Both are mature, free, and will handle far more than most projects need — so the honest answer is rarely "one is better", it is "they were built around different priorities". MySQL optimised early for fast, simple reads at web scale; PostgreSQL optimised for correctness, extensibility, and complex queries.

Feature by feature

What actually matters for data work

Most of the table is irrelevant if you are querying rather than operating the database. Four rows are not: window functions and CTEs, because analytical SQL is unwritable without them and a MySQL below 8.0 has neither; FULL OUTER JOIN, because reconciling two sources is a daily task and MySQL makes you write a UNION for it; materialised views, because that is how a slow aggregate becomes a fast one without building a pipeline; and the planner, because a seven-way join with subqueries is where the difference shows up.

Add JSONB and pgvector and the gap widens for anything modern — semi-structured event data stays queryable, and embeddings can live beside the rows they describe instead of in a separate vector store. That last point removes an entire moving part from a RAG system.

Where MySQL wins

Simple high-volume reads on well-indexed tables, where its lighter footprint and simpler operational model tell. It is also easier to run: no vacuum to tune, replication that a very large number of engineers have operated before, and Vitess as a genuinely proven sharding path. And it is often simply what is already there — which is the most common reason anyone uses either.

Pandas provides the DataFrame: a labelled, two-dimensional table with an index, supporting joins, grouping, reshaping, and time-series operations in Python.

The habit worth building early is vectorised thinking. Iterating over rows works and is often a hundred times slower than the equivalent column operation; most pandas performance problems are a loop that should have been a single expression.

NumPy supplies the N-dimensional array and the vectorised operations on it. Its arrays are contiguous typed memory, so element-wise work runs in compiled code rather than the Python interpreter.

Almost everything else in the scientific Python stack — pandas, scikit-learn, PyTorch — sits on this foundation, which is why broadcasting rules and array shapes are worth understanding properly rather than fighting.

Visualisation serves two distinct purposes that call for different standards. Exploratory plots are for you — fast, ugly, and numerous. Explanatory plots are for someone else, and every element should earn its place.

The judgment worth developing is knowing which chart answers the question, and being honest with axes. A truncated y-axis, a dual axis, or an area chart of non-additive quantities can each turn noise into a convincing story.

Statistics is reasoning from a sample to a population under uncertainty. Descriptive statistics summarise what you have; inferential statistics quantify how much that tells you about what you do not.

The habit that matters is refusing to report an estimate without its uncertainty. A number without an interval is an opinion with decimals — and the gap between "conversion is 4.2%" and "4.2% ± 1.8%" is often the difference between a good and a bad decision.

Probability is the mathematics of uncertainty: independence, conditional probability, expectation, and variance. Bayes' rule — updating a belief given evidence — is the single most practically useful piece.

Its most valuable application is the base rate. A test that is 99% accurate for a condition affecting 1 in 10,000 people yields mostly false positives, and the intuition that resists this error is worth more than the formula.

A distribution describes how values are spread. Normal for sums of many small effects, Poisson for counts of rare events, exponential for waiting times, binomial for repeated trials, power-law for quantities where a few observations dominate.

Recognising the shape tells you which methods apply. Assuming normality on heavily skewed data — revenue per customer, session length, request latency — is one of the most common and most consequential analytical errors.

Hypothesis testing asks whether an observed effect is distinguishable from chance. You state a null hypothesis, compute how surprising the data would be if it were true, and reject it when that surprise passes a threshold.

Two traps do most of the damage. A p-value is not the probability the hypothesis is true. And testing many variants without correcting for multiple comparisons manufactures significance — test twenty things at p<0.05 and one "wins" by construction.

A confidence interval gives a range of plausible values for a quantity, with a stated coverage level. It communicates both the estimate and its precision in a single object.

It is almost always the better thing to report. "Lift was 3%, interval −1% to 7%" tells a decision-maker immediately that the result is inconclusive, where "p = 0.31" makes them ask what that means.

Sampling is how you get a subset that represents the whole. Random, stratified, and cluster sampling each trade cost against precision for particular population structures.

Sample size gets attention; sample bias does the damage. A biased sample does not improve with more data — it converges more confidently on the wrong answer. Survivorship bias, self-selection, and analysing only users who completed the flow are the everyday versions.

EDA is the first pass: distributions, missingness, outliers, relationships, and sanity checks against what the data is supposed to represent. It is where you discover the column that is 40% null and the timestamps in three timezones.

Skipping it is how modelling projects fail late and expensively. Most "the model doesn't work" problems are data problems that would have been visible in an hour of looking.

Cleaning resolves missing values, duplicates, inconsistent encodings, and outliers before analysis. Every decision is a judgment call with consequences: dropping rows with nulls can silently bias the sample if missingness is not random.

It routinely consumes most of a project's time and affects the result more than model choice. Document what you changed and why — otherwise nobody, including you in three months, can reproduce or defend the analysis.

Feature engineering turns raw fields into inputs a model can use: ratios, aggregates over time windows, encoded categories, date parts, domain-specific derivations.

The failure to guard against is leakage — building a feature from information unavailable at prediction time. It produces spectacular validation scores and a model that collapses in production, and it is the most common reason a promising result does not survive deployment.

Correlation says two things move together. Causation says intervening on one changes the other. Only the second supports a decision, and observational data alone rarely establishes it.

The usual culprit is a confounder driving both. Users of a feature retain better — but engaged users both adopt features and retain, so the feature may have caused nothing at all. Recognising this before recommending action is a large part of the job.

Regression models a relationship between inputs and an outcome. Used predictively it estimates unknown values; used inferentially it quantifies how much each input matters, with uncertainty attached.

Its interpretability is the attraction and the risk. A coefficient means "holding the other included variables constant" — which is only meaningful if the right variables are in the model, and says nothing about the ones you omitted.

An A/B test randomly assigns users to variants so the measured difference can be attributed to the change rather than to who received it. Randomisation is what buys the causal claim.

The decisions that determine validity happen before data collection: the metric, the sample size from a power calculation, the duration, and the guardrails. Stopping the moment the result looks good — peeking — inflates false positives dramatically, which is why the analysis plan is written first.

Causal inference estimates effects when a randomised experiment is impossible — because it would be unethical, illegal, or simply infeasible. Difference-in-differences, propensity score matching, instrumental variables, and regression discontinuity each recover a causal estimate under stated assumptions.

Those assumptions are the whole method, and they are not testable from the data. The discipline is stating them explicitly and arguing for their plausibility, rather than letting the technique imply a rigour the design does not have.

Time series data has order, trend, seasonality, and autocorrelation — observations near in time are related, which breaks the independence most standard methods assume.

The practical consequence is that random train/test splits are invalid. Shuffling lets the model see the future, producing excellent backtests and useless forecasts. Splits must respect chronology, and evaluation must reflect what was actually knowable at each point.