Choosing the right database for the job — SQL & Relational Databases

The most important data engineering skill after understanding individual database technologies is knowing which database to choose for each specific use case. The phrase 'use the right tool for the job' is simple to say but requires systematic reasoning about the trade-offs between consistency, availability, partition tolerance (CAP theorem), query flexibility, write throughput, schema rigidity, and operational complexity. A database that is excellent for one use case can be catastrophically wrong for another — and the cost of choosing incorrectly is measured in months of migration work when the chosen database hits its fundamental limitations.

The CAP theorem states that a distributed database system cannot simultaneously guarantee all three of Consistency (every read sees the most recent write), Availability (every request receives a response), and Partition Tolerance (the system continues operating despite network partitions). In practice, partition tolerance is non-negotiable for distributed systems (network partitions happen), so the real choice is between CP (consistency + partition tolerance, sacrificing availability) and AP (availability + partition tolerance, sacrificing strict consistency). PostgreSQL is CP; Cassandra is AP (tunable); MongoDB is CP by default but configurable.

For data engineers, database selection decisions arise in two contexts: choosing the persistence technology for a new component in a data pipeline (e.g., what stores the pipeline run state? what caches the expensive aggregation?), and recommending the right source system to application teams whose data the pipeline will consume. In both contexts, the selection framework is the same: match the database's primary strength to the component's primary requirement — and be explicit about the trade-offs accepted by the choice.

Analogy🏏Cricket

🏏 Think of it like cricket: A SELECT query is precisely how a selection committee picks a playing XI. FROM is the full list of centrally contracted players — the raw pool. WHERE is the fitness and eligibility screen: injured or unavailable players are removed before anyone debates merit, and the fewer names that survive this screen, the faster the meeting goes — exactly why a good WHERE clause matters more than anything downstream. ORDER BY is ranking the survivors by recent form, then by experience as the tiebreaker. LIMIT 11 takes the top of that ranked list and stops. The committee never ranks the entire national player pool and then discards thousands of names — and neither should your query force the database to sort millions of rows it will immediately throw away. The order of operations is the whole game: filter first, sort what remains, take only what you need.