Data & databases
SQL, modelling, indexes, transactions, NoSQL and data quality, from spreadsheets upwards.
Transactions and ACID properties explained with a bank transfer
Atomicity, consistency, isolation and durability: what a transaction guarantees, how to use BEGIN and COMMIT, and which code mistakes to avoid.
CSV files: the most common problems and how to fix them first
Separators, character encoding, lost leading zeros, ambiguous dates and quotes: how to spot and fix the typical defects in CSV files before importing.
CTEs and views: writing long SQL queries that stay readable
How to use common table expressions (WITH), views and materialized views to break complex SQL queries into clear, verifiable, reusable steps.
Data analysis with pandas: first steps through a concrete case
Loading, exploring, cleaning, grouping and merging data with pandas in Python, starting from a real sales file and covering the mistakes to avoid.
Data quality: six dimensions and the checks to measure them
Completeness, validity, uniqueness, consistency, accuracy and timeliness: what they mean in practice and which SQL queries to use to measure data quality.
Backing up a database: logical dumps, physical copies and restore
Why copying a running database's files is not enough, logical dumps versus physical backups, point-in-time recovery and how to actually test your restores.
Database normalization: the first three normal forms
First, second and third normal form explained through an orders and customers example: which problems they solve and when denormalizing makes sense.
Schema migrations: changing a database without taking it down
How to version database schema changes, write safe migrations and change columns and tables in production without downtime or losing any of your data.
Dates and time zones in databases: getting the times right
UTC, daylight saving, timestamps with and without zones: practical rules for storing dates and times in a database and showing them correctly.
Duplicate customer records: how to find and merge them safely
Normalising values, matching keys, string similarity and a merge procedure: a practical method for removing duplicate customers from a database.
GROUP BY in SQL: six common mistakes that distort totals
WHERE or HAVING, ungrouped columns, averages of averages, inflated counts and dates with times: the typical mistakes in SQL aggregations and fixes.
Database indexes: how they work and when to create them
What a B-tree index is, why it speeds up lookups, what it costs on writes and how to pick the right columns, including column order in composite indexes.
Money amounts in a database: why DECIMAL and not FLOAT
Why floating-point numbers get money wrong, how to use DECIMAL or integer cents, and how to handle rounding, VAT and multiple currencies correctly.
Natural or surrogate primary keys: how to choose
Tax code, email or an auto-increment number? The pros and cons of natural and surrogate keys, with practical criteria for choosing a table's primary key.
Operational database and data warehouse: OLTP, OLAP, star schema
Why heavy reports should not run on your business system's database, what a data warehouse is, how a star schema works and what changes in data modelling.
Parameterized queries: the right defence against SQL injection
Why concatenating strings into SQL queries is dangerous, how parameters work, what cannot be parameterized and which extra defences to add in the database.
Reading a query plan with EXPLAIN without fear
How to use EXPLAIN to understand why a query is slow: scans, index usage, row estimates and join strategies, with an annotated example step by step.
Spreadsheet or database: knowing when it is time to switch
Excel and Google Sheets are great tools, but they have clear limits. Here are the concrete signs that your data deserves a proper database instead.
SQL joins explained with examples: INNER, LEFT, RIGHT and FULL
How SQL joins work, with two sample tables and row-by-row results. Including the most common mistakes: multiplied rows and filters in the wrong place.
NULL in SQL: the pitfalls that distort query results
Why NULL does not equal NULL, how it behaves with COUNT, NOT IN and sums, and which rules to follow to avoid wrong reports that nobody ever notices.
SQL window functions: rankings, running totals and comparisons
How to use OVER, PARTITION BY and ORDER BY for rankings, running totals, moving averages and comparisons between rows, keeping every row's detail.
Table relationships: one-to-one, one-to-many and many-to-many
How to model relationships between entities in a relational database: foreign keys, junction tables with attributes, hierarchies and delete rules.
Transaction isolation levels and their typical anomalies
Dirty reads, non-repeatable reads, phantoms and lost updates: what they are, which isolation level prevents them and how to choose the right one.
NoSQL databases: documents, key-value, wide-column and graphs
The four main families of NoSQL databases, how they organise data, which problems they suit and when a relational database is still the better choice.
SQLite: when a single-file database is the right choice
How SQLite works, why it is sturdier than people think, the cases where it is more than enough, its concurrency limits and the settings to know right away.