Hi @Sunny_singh,

Welcome to Data Engineering! This is a great question and one that comes up frequently. There is no single "correct" answer because real-world sizing depends on data format, cloud provider, data skew, and more, but here is a solid framework you can use to reason through the two cases you described.

FOUNDATIONAL CONCEPTS

Before jumping into configurations, here are the key principles:

- 100GB of compressed Parquet/Delta on disk can expand 2-5x in memory, so plan for 200-500GB of effective in-memory data.
- The default partition size in Databricks is 128MB, so 100GB produces roughly 800 initial partitions.
- One partition is processed by one task, and one task requires one core. So total cores determine how many partitions run in parallel.
- Adaptive Query Execution (AQE) is enabled by default on Databricks Runtime and dynamically optimizes shuffle partitions, coalesces small partitions, and handles skew. This is a major advantage on the platform.

Reference: https://docs.databricks.com/optimizations/aqe.html

CASE 1: SIMPLE TRANSFORMATIONS (MINIMAL SHUFFLE)

Examples: filters, column projections, map-only operations, simple aggregations on pre-partitioned data.

What matters most: raw CPU throughput and read speed. Memory pressure is low since there is little data shuffled between nodes.

Recommended approach -- scale UP (vertical scaling):
- Instance family: Compute-optimized (AWS: c5.4xlarge / Azure: F16s_v2 / GCP: c2-standard-16)
- These give you more cores per dollar since you are CPU-bound, not memory-bound
- Workers: 2-4 workers (each with 16 cores gives you 32-64 total cores)
- Driver: Same type or one size larger
- Autoscaling: Min 2, Max 4

With 64 cores and 800 partitions, you would process in about 12-13 waves. Each wave handles a lightweight task, so this completes quickly.

Partition tuning: The default 200 shuffle partitions is usually fine since there is minimal shuffle. AQE will coalesce small partitions automatically. You can also set:

spark.conf.set("spark.sql.shuffle.partitions", "auto")

This lets Databricks determine the optimal count based on the query plan and data size.

CASE 2: COMPLEX TRANSFORMATIONS (HEAVY SHUFFLE -- JOINS, GROUPBY, WINDOW FUNCTIONS)

Examples: large joins across two 100GB datasets, groupBy with many keys, window functions, multi-stage aggregations.

What matters most: memory (to avoid disk spill during shuffle) and local SSD storage (for fast spill when it does happen). Shuffle operations move data between executors across the network, so you want enough memory to hold intermediate results and fast local disks as a safety net.

Recommended approach -- scale OUT (horizontal scaling):
- Instance family: Memory-optimized with local SSD (AWS: i3.2xlarge or r5d.2xlarge / Azure: E8ds_v5 or L8s_v3 / GCP: n2-highmem-8)
- Local SSDs are important here because shuffle spill writes to local disk, and SSDs make that dramatically faster
- Workers: 4-8 workers (each with 8 cores and 32-64GB RAM)
- Driver: One size larger to handle the query plan and result collection
- Autoscaling: Min 4, Max 8-10

With 8 workers at 8 cores each (64 cores) and 32GB RAM each (256GB total), you have enough memory to handle the in-memory expansion of 100GB and enough parallelism for shuffle stages.

Partition tuning: For heavy shuffles, you may want to increase shuffle partitions:

spark.conf.set("spark.sql.shuffle.partitions", "400")

This creates smaller partitions that are less likely to cause memory pressure during shuffle. However, with AQE enabled (the default), the system will automatically coalesce partitions that are too small, so starting with "auto" is also a good approach:

spark.conf.set("spark.sql.shuffle.partitions", "auto")

WHY THE DIFFERENCE?

The key insight is:

- Simple workloads are CPU-bound: fewer, more powerful nodes work well because there is little data exchange between nodes. This is vertical scaling.

- Complex workloads are memory-and-IO-bound: more nodes with generous memory spread the shuffle data across more executors, reducing per-executor memory pressure. More local SSDs across the cluster also means faster spill recovery. This is horizontal scaling.

ADDITIONAL OPTIMIZATIONS FOR BOTH CASES

1. Enable Photon: Databricks' native vectorized engine accelerates SQL and DataFrame operations (especially joins and aggregations) often by 2-5x. Enable it by checking "Use Photon Acceleration" in the compute configuration.
Reference: https://docs.databricks.com/runtime/photon

2. Use Delta format: If your 100GB is stored as Delta, run OPTIMIZE to compact small files and use liquid clustering on frequently-filtered columns. This reduces read overhead significantly.

3. Consider Serverless Compute: If you want to skip the sizing exercise entirely, Databricks serverless compute handles all of this automatically. It scales on demand, requires no configuration, and is optimized out of the box. For interview discussions this may not be the expected answer, but in practice it is often the simplest path.
Reference: https://docs.databricks.com/compute/cluster-config-best-practices.html

4. Use the latest Databricks Runtime LTS: This ensures you get AQE, dynamic file pruning, and all the latest optimizations enabled by default.

QUICK REFERENCE TABLE

Here is a side-by-side comparison:

Simple Transformations:
Instance type: Compute-optimized (c5, F-series)
Workers: 2-4
Cores per worker: 16
RAM per worker: 32GB
Scaling approach: Vertical (fewer, bigger nodes)
Shuffle partitions: auto or 200
Key bottleneck: CPU throughput

Complex Transformations:
Instance type: Memory-optimized with SSD (i3, r5d, E-series, L-series)
Workers: 4-8
Cores per worker: 8
RAM per worker: 32-64GB
Scaling approach: Horizontal (more nodes)
Shuffle partitions: auto or 400
Key bottleneck: Memory and shuffle I/O

Remember, these are starting points. In practice, you would monitor the Spark UI to look for spill, long-running tasks, or underutilized cores, and then adjust accordingly. The Databricks documentation on cluster configuration best practices is an excellent resource for going deeper:
https://docs.databricks.com/compute/cluster-config-best-practices.html

Hope this helps with both interviews and real-world sizing!

* This reply used an agent system I built to research and draft this response based on the wide set of documentation I have available and previous memory. I personally review the draft for any obvious issues and for monitoring system reliability and update it when I detect any drift, but there is still a small chance that something is inaccurate, especially if you are experimenting with brand new features.