54 posts · page 1 of 2
Prepare for data engineering interviews: SQL deep dives, Python coding, system design, data modeling, behavioral questions, and take-home project tips.
Master dbt (data build tool): project structure, models, materializations, testing, Jinja templating, and how dbt became the standard for analytics engineering.
Compare relational and non-relational databases from first principles. Learn ACID properties, NoSQL types, polyglot persistence, and when to pick each — with real migration stories from Uber and Netflix.
Learn Kimball, Inmon, and Data Vault modeling approaches for analytics — star schemas, normalized models, and modern patterns like the Activity Schema.
Learn the fundamentals of data warehousing — star schemas, snowflake schemas, fact tables, dimension tables, and slowly changing dimensions with examples.
Compare MongoDB and PostgreSQL across data modeling, performance, scalability, and use cases. Choose the right database for your project.
Compare PostgreSQL, MySQL, and SQLite on features, performance, scalability, and use cases. Find the best relational database for your project in 2026.
Master efficient bulk data loading with multi-row INSERT, COPY, ON CONFLICT upserts, and batch strategies that avoid locking and performance traps.
Replace slow OFFSET pagination with cursor-based (keyset) pagination. Covers implementation, indexing, bidirectional cursors, and encoding strategies.
Move beyond LIKE queries with SQL full-text search. Covers tsvector, tsquery, ranking, indexes, and when to choose Postgres FTS over external search engines.
Learn when temporary tables improve query performance and readability. Covers session-scoped temps, CTEs, unlogged tables, and cleanup strategies.
Learn how SQL triggers work, when to use them, and when to avoid them. Covers BEFORE/AFTER triggers, audit logging, and common pitfalls.
Learn how SQL deadlocks occur, how databases detect them, and practical strategies to prevent deadlocks in your applications.
A practical guide to SQL index types -- B-tree, hash, partial, and composite -- and when to use each for maximum query performance.
Learn how to store, query, and manipulate JSON and JSONB data in PostgreSQL with practical examples and indexing strategies.
Learn how LATERAL joins work in SQL, how they replace correlated subqueries, and when to use them for top-N-per-group patterns.
Learn how to create, refresh, and index materialized views in PostgreSQL to dramatically speed up expensive queries.
Learn how to pivot rows into columns and unpivot columns into rows using CASE, CROSSTAB, PIVOT, and UNPIVOT in SQL.
Learn to read EXPLAIN and EXPLAIN ANALYZE output to diagnose slow queries, spot sequential scans, and optimize SQL performance.
Understand SQL isolation levels, their concurrency trade-offs, and how to choose the right level for your application.
Master SQL window functions with practical examples covering ROW_NUMBER, RANK, DENSE_RANK, LAG, LEAD, and frame clauses.
A practical guide to SQL Common Table Expressions: the WITH clause, recursive CTEs, readability improvements, materialization hints, and when CTEs outperform subqueries.
Practical SQL query optimization: reading EXPLAIN plans, choosing the right indexes, avoiding N+1 queries, understanding query planner decisions, and knowing when denormalization makes sense.
Understanding SQL transactions and isolation levels: ACID properties, read phenomena, read committed, repeatable read, serializable isolation, deadlocks, and practical concurrency patterns.
A practical tour of Postgres indexing: btree, hash, GIN, BRIN, partial and expression indexes, plus how to choose, measure, and maintain them in production.
Use Go's standard database/sql package the right way: drivers, connection pools, prepared statements, transactions, context cancellation, and avoiding the classic Rows.Close leak.
Connect to relational databases from Java using JDBC: drivers, PreparedStatement, transactions, connection pooling, and resource management.
What ACID and BASE actually mean, where each shines, and how to reason about consistency, availability, and durability when you design data systems.
How aggregate functions interact with GROUP BY, HAVING, and window functions, with practical patterns and pitfalls every backend engineer should know.
Compare common table expressions, subqueries, and temporary tables in SQL. Learn when each shines, the performance trade-offs, and concrete examples.
How to read EXPLAIN ANALYZE output, interpret cost estimates, spot bad plans, and use it to drive real performance improvements in production databases.
Learn how to read EXPLAIN output, what scan and join types mean, and how to spot the indexes and rewrites that make slow queries fast.
How ON DELETE and ON UPDATE cascade options work, when to use CASCADE, SET NULL, RESTRICT, or NO ACTION, and how to avoid surprise mass deletes.
A practical comparison of B-Tree, Hash, and GIN indexes in PostgreSQL, when each one shines, and how to pick the right structure for your query patterns.
A thorough tour of SQL joins with diagrams, sample queries, and the gotchas that bite real systems: NULLs, duplicates, and join order.
Learn how NULL behaves in SQL, why three-valued logic trips up queries, and the patterns that keep your data consistent and your queries correct.
How to reshape rows into columns and back using PIVOT, UNPIVOT, CASE aggregations, and lateral unions across Postgres, MySQL, and SQL Server.
A grounded comparison of Postgres and MySQL across data types, transactions, replication, JSON, and ecosystem - so you can pick the right one for your project.
What prepared statements actually do, why they prevent SQL injection, how they affect performance, and how to use them correctly from any language.
Learn how recursive CTEs work in SQL, how to traverse hierarchies and graphs in pure SQL, and how to avoid the common termination and performance pitfalls.
Learn how PostgreSQL row-level security works, how to write effective policies, and how to enforce per-tenant isolation safely in multi-tenant applications.
When to use stored procedures, when to use functions, and how transaction control, return values, and side effects differ across major databases.
Understand BEGIN, COMMIT, ROLLBACK, and the four standard isolation levels with concrete examples of dirty reads, non-repeatable reads, and phantoms.
Learn how window functions work in SQL, when to use ROW_NUMBER, RANK, LAG, and SUM OVER, and how they differ from GROUP BY.
Learn how to read Postgres EXPLAIN and EXPLAIN ANALYZE output, spot expensive operations, and apply practical indexing and rewrite techniques to speed up queries.
A practical guide to database normalization with real customer and order examples. Covers 1NF, 2NF, 3NF, when to denormalize, and tradeoffs.
Learn SQL window functions with practical examples. Covers ROW_NUMBER, RANK, DENSE_RANK, LAG, LEAD, running totals, and the OVER clause in depth.
Build your first real schema — CREATE TABLE with the right column types, PRIMARY KEY, NOT NULL, DEFAULT, then load it with single and multi-row INSERT, and learn when to reach for ALTER or DROP.