Databases & SQL
Indexing, joins & subqueries, transactions, schema design, and performance
Study essential database interview questions on SQL fundamentals, indexing strategies, transaction isolation levels, query optimization, normalization, and data modeling. Covers both relational and NoSQL concepts with practical examples and trade-off analysis.
Indexing3 topics
B-tree Indexes
Speed up lookups, range scans, and ordered reads on a column by maintaining a balanced tree of sorted values.
Composite Indexes & Leftmost Prefix
A single index on multiple columns can serve many queries — but only if the WHERE clause uses the columns from the left.
Index Anti-patterns
Recognize patterns that silently disable indexes — implicit casts, leading wildcards, OR conditions, functions on columns.
Queries3 topics
Join Types
Combine rows from multiple tables — INNER for matches only, LEFT for matches plus unmatched-left, FULL for both sides.
Subqueries, CTEs & Window Functions
Express multi-step queries cleanly with CTEs (WITH), per-row computations with window functions, and lookups with subqueries.
EXPLAIN & Query Plans
Read what the optimizer actually does — scan types, join methods, row estimates — so you can target the real bottleneck.
Transactions3 topics
ACID & Transactions
Group multiple statements so they succeed or fail as a unit — protecting consistency in the face of crashes and concurrency.
Isolation Levels
Trade off concurrency anomalies vs throughput by choosing how much isolation each transaction gets.
Locking & Deadlocks
Recognize how the DB acquires locks (row, range, table) and avoid deadlocks via consistent lock order and short transactions.
Schema4 topics
Normalization
Organize tables so each fact lives in one place — eliminating update anomalies and redundancy.
Denormalization for Reads
Trade write complexity for read speed — duplicate or pre-aggregate data when the read path is hot and the freshness window is acceptable.
Constraints & Referential Integrity
Use NOT NULL, UNIQUE, CHECK, and FOREIGN KEY constraints so the database enforces invariants — instead of relying on every code path to do so.
Primary, Composite & Alternate Keys
A table has exactly one primary key (which can span multiple columns); other uniqueness constraints are "alternate keys" enforced by UNIQUE — the primary key is the row's canonical identity for foreign keys and the clustered index.
Performance3 topics
N+1 Query Problem
Spot and eliminate the pattern of "1 query for the list + N queries for each item's related rows" that quietly destroys performance.
Connection Pooling
Reuse a fixed set of physical DB connections — opening new ones per request is too expensive and the DB has a hard limit.
Query Optimization Workflow
A repeatable process: log slow queries → run EXPLAIN ANALYZE → identify the operator that's expensive → fix (index, rewrite, denormalize) → verify.
Cassandra25 topics
Cassandra Architecture & Ring Topology
Understand how Cassandra's masterless, ring-based architecture achieves scalability and availability.
Partition Keys & Clustering Columns
Learn how partition keys determine data placement and clustering columns control sort order within a partition.
Query-First Data Modeling & Denormalization
Design Cassandra schemas by starting from query patterns rather than normalized entity relationships.
Consistency Levels (ONE, QUORUM, ALL, LOCAL_QUORUM)
Understand how consistency levels let developers tune the trade-off between consistency, availability, and latency per query.
Replication Strategies: SimpleStrategy vs NetworkTopologyStrategy
Learn how Cassandra places replicas across nodes and datacenters using pluggable replication strategies.
Gossip Protocol & Failure Detection
Understand how Cassandra nodes discover cluster state and detect failures without a central coordinator.
Compaction Strategies: STCS, LCS, TWCS
Choose the right compaction strategy to balance read performance, write throughput, and space amplification for different workloads.
CQL Fundamentals: CREATE, INSERT, SELECT, UPDATE, DELETE
Get comfortable with the basic syntax of the Cassandra Query Language for everyday CRUD operations.
Secondary Indexes & SASI Indexes
Understand when secondary indexes are appropriate in Cassandra and their significant performance limitations.
Materialized Views
Learn how materialized views automate denormalization for alternate query patterns, and understand their operational risks.
Lightweight Transactions (LWT) & Compare-and-Set
Understand how Cassandra achieves linearizable compare-and-set semantics despite its eventually consistent design.
Tombstones & TTL
Understand how Cassandra handles deletes and expirations via tombstones, and the operational risks of tombstone accumulation.
Batch Operations: Logged vs Unlogged
Learn the correct use cases for CQL batches and why batching is not a general-purpose performance optimization in Cassandra.
Read Path & Write Path Internals
Understand the internal steps Cassandra takes to serve a read or write request, including memtables, SSTables, and the commit log.
Snitch Types & Topology Awareness
Understand how snitches inform Cassandra about network topology to optimize replica placement and request routing.
Anti-Entropy Repair
Learn how Cassandra reconciles replica divergence over time using repair, and why it's an essential operational task.
Tunable Consistency & Hinted Handoff
Understand how hinted handoff and read repair work alongside consistency levels to maintain data integrity during transient failures.
Wide Rows & Time-Series Data Modeling
Learn techniques for modeling time-series data in Cassandra using wide partitions and time-bucketing to avoid unbounded partition growth.
Collections (SET, LIST, MAP) & User-Defined Types
Learn how to model structured, multi-valued, and nested data using CQL collections and user-defined types.
Counters
Understand Cassandra's specialized counter column type for distributed increment/decrement operations.
Performance Tuning: Bloom Filters, Key Cache & Row Cache
Understand the caching and probabilistic data structures Cassandra uses to accelerate reads, and how to tune them.
Multi-Datacenter Deployment
Understand the design considerations and configuration required to run Cassandra reliably across multiple datacenters or cloud regions.
Cassandra vs RDBMS Trade-offs
Articulate the fundamental architectural and modeling trade-offs between Cassandra and traditional relational databases.
Backup & Restore: Snapshots & Incremental Backups
Learn how Cassandra's snapshot and incremental backup mechanisms work, and how to restore data from them.
Driver Policies: Load Balancing, Retry & Reconnection
Understand how Cassandra client drivers use pluggable policies to route requests, handle failures, and recover from outages.
Snowflake25 topics
Snowflake Architecture: Storage, Compute & Cloud Services
Understand Snowflake's unique multi-cluster shared data architecture and its three layers.
Virtual Warehouses: Sizing, Auto-Suspend & Auto-Resume
Configure compute clusters efficiently to balance performance and cost.
Micro-Partitions & Automatic Data Clustering
Understand how Snowflake physically organizes data for efficient pruning.
Clustering Keys for Large Table Optimization
Manually improve pruning efficiency on very large or poorly-ordered tables.
Time Travel & Fail-safe: Data Recovery Windows
Recover historical or accidentally-modified data using Snowflake's built-in versioning.
Zero-Copy Cloning
Instantly create full database, schema, or table copies without duplicating storage.
Secure Data Sharing & Reader Accounts
Share live data across Snowflake accounts without copying or moving it.
Stages: Internal, External & Named Stages
Understand where and how files are staged before loading into Snowflake tables.
COPY INTO & Snowpipe: Batch and Continuous Ingestion
Load data in bulk or continuously as files arrive, without manual intervention.
Streams: Change Data Capture on Tables
Track row-level changes (inserts, updates, deletes) on a table for incremental processing.
Tasks: Scheduling & Orchestrating SQL Workflows
Automate recurring SQL execution and chain dependent steps into pipelines.
Stored Procedures & UDFs (JavaScript, SQL, Python)
Encapsulate reusable logic and procedural workflows directly inside Snowflake.
RBAC & Discretionary Access Control in Snowflake
Design a secure, scalable permission model using roles and grants.
Resource Monitors & Cost Management
Proactively control and cap credit spend across warehouses and the account.
Semi-Structured Data: VARIANT & LATERAL FLATTEN
Query and shred nested JSON, Avro, Parquet, or XML data using native Snowflake types.
External Tables & Data Lake Integration
Query data directly from a cloud data lake without loading it into Snowflake storage.
Materialized Views for Precomputed Query Acceleration
Automatically maintain precomputed query results for faster repeated access.
Query Profile & Performance Optimization
Diagnose and resolve query performance bottlenecks using Snowflake's execution visualizations.
Result Caching: Query, Metadata & Warehouse Caches
Leverage Snowflake's multi-layer caching to reduce cost and latency on repeated queries.
Secure Views & Row Access Policies
Restrict which rows and query internals users can see based on their role or attributes.
Dynamic Data Masking
Conditionally obscure sensitive column values based on the querying user's role.
Snowpark: DataFrame API & ML Workloads
Write Python/Java/Scala data pipelines and ML workflows that execute inside Snowflake's compute.
Multi-Cluster Warehouses for Concurrency Scaling
Handle high query concurrency by automatically spinning up additional compute clusters.
Replication & Failover Across Regions
Maintain business continuity by replicating databases and account objects across regions or clouds.
Network Policies, Private Connectivity & Transactions/Locking
Secure network-level access to Snowflake and understand its transactional concurrency model.