Data Engineering
Getting data from where it is to where it is useful — SQL, modelling, pipelines, and the quality checks that catch a bad load before a dashboard does.
- SQL fundamentalsWhat a SELECT actually does in execution order rather than in the order you type it, and why GROUP BY, HAVING, and WHERE are not interchangeable filters on the same data.
- Window functions, CTEs, and recursive queriesRANK vs DENSE_RANK vs ROW_NUMBER measured on real ties, LAG for period-over-period deltas without a self-join, and the recursive CTE mechanics that make a bill-of-materials query possible.
- Query optimisationSARGable predicates measured against the non-sargable rewrite that "does the same thing," why HAVING after GROUP BY costs more than WHERE before it, and reading a plan for where the actual cost is hiding.
- Dimensional modellingStar schema versus a normalized OLTP schema, and why "when did this customer live at this address" is a modelling decision — slowly changing dimensions — not a query you can bolt on afterward.
- Data profilingWhat to measure before trusting a new dataset — cardinality, null rate, and the mean-vs-median gap that tells you whether "average" is even a meaningful number for this column.
- Data cleansingWhy deduplication needs a definition of "same" before it needs code, imputation changes a column's statistical properties whether you notice or not, and cleansing logic that silently drops rows is the failure mode to design against first.
- Data quality frameworksGreat Expectations, dbt tests, and data contracts solve the same underlying problem at different points in the pipeline — what breaks when quality checks run after the damage is already downstream.
- Pipelines and orchestrationWhy a pipeline step has to be idempotent before it can be safely retried, what a DAG actually buys you over a cron job, and the backfill that silently double-counts because a task wasn't designed to run twice.
- Data ingestion and change data captureBatch versus streaming as a latency-and-cost trade, why polling for changes misses deletes and intermediate states, and what a CDC connector reading the transaction log actually captures that a timestamp column can't.
- dbtWhat "transform in SQL, version like code" actually buys you, why the materialization you pick trades build time against query time, and the incremental model bug that silently reprocesses the same rows forever.
- SnowflakeStorage and compute billed separately and what that actually buys you, warehouse sizing as a credits-per-hour multiplier, and the idle-warehouse cost that silently doubles a bill nobody looks at closely.
- Python for data — pandas, vectorisation, and polarsA measured 50x gap between a Python loop and a vectorised pandas operation, the SettingWithCopyWarning that means your edit silently didn't happen, and why polars closes both problems by construction rather than by convention.
- Document and PDF extractionWhy a PDF has no concept of a table, how layout-aware extraction reconstructs structure from position rather than reading markup that doesn't exist, and the silent column-misalignment failure that looks fine until someone checks the numbers.
- Data visualisation principlesWhy a truncated y-axis isn't a style choice, when a chart is genuinely the wrong tool for the question, and the aggregation-before-charting decision that determines whether the picture matches the data underneath it.
- Power BI — storage modes and DAX contextImport versus DirectQuery as a freshness-for-speed trade, and context transition — the DAX mechanism that turns a row context into a filter context — which is where most "why is this measure wrong" bugs live.