Choosing column or table granularity from the requirements

Granularity refers to the level of detail represented by each row in a table or value in a column, and choosing it correctly is critical when designing Delta tables and transformation pipelines in Databricks. The right grain balances query performance, storage cost, and the level of detail downstream consumers (BI tools, ML models, other pipelines) actually need. In Databricks, granularity decisions typically involve choosing between raw/atomic-level data versus pre-aggregated summaries, and selecting appropriate partition or date-truncation columns.

1 · Learn the must-know

• 1The grain of a table should match the most detailed level of analysis required by consumers—aggregating too early makes it impossible to drill down later without reprocessing raw data.
• 2Functions like date_trunc(), window(), and GROUP BY are commonly used in Spark/Databricks to roll data up to a coarser granularity (e.g., day, hour, month) for reporting tables.
• 3Choosing overly fine partition granularity (e.g., partitioning by second or by a high-cardinality column) causes the small-file problem and degrades read performance in Delta Lake.
• 4Choosing overly coarse partition granularity (e.g., partitioning only by year) can result in very large files per partition, reducing the benefit of partition pruning for selective queries.
• 5In medallion architecture, Bronze layers typically preserve raw/atomic granularity, Silver layers apply cleansing/conformance at record grain, and Gold layers aggregate to the granularity needed for specific business reporting requirements.
• 6Column-level granularity choices (e.g., storing timestamp vs. date, or storing a full address vs. separate city/state columns) should be driven by query patterns and join requirements, since converting to a coarser grain later is easy but recovering lost detail is not.

2 · Check your understanding