Choosing column data types

Choosing appropriate column data types in Azure Databricks means mapping source data to Spark SQL types (e.g., STRING, INT, BIGINT, DOUBLE, DECIMAL, BOOLEAN, DATE, TIMESTAMP, ARRAY, MAP, STRUCT) so that storage, computation, and downstream transformations behave correctly and efficiently. Correct type selection affects query performance, storage size in Delta Lake, join/aggregation correctness, and schema evolution behavior. This is especially important when defining schemas explicitly, casting during ingestion, or working with schema inference on semi-structured data like JSON.

1 · Learn the must-know

• 1Use DECIMAL(p,s) rather than DOUBLE or FLOAT for exact numeric values like currency, since floating-point types introduce rounding errors that DECIMAL avoids.
• 2Spark's schema inference (e.g., when reading CSV or JSON with inferSchema) can be slow on large files and may incorrectly widen types (e.g., inferring STRING instead of INT) if sample data contains nulls or mixed formats, so explicit schema definition is recommended for production pipelines.
• 3TIMESTAMP in Spark includes time zone handling based on session time zone settings, while DATE does not, so choose TIMESTAMP only when time-of-day and time zone precision are actually needed.
• 4Complex nested data (JSON objects/arrays) should be modeled using STRUCT, ARRAY, and MAP types rather than flattening everything to STRING, since this preserves queryability with dot notation and functions like explode().
• 5Narrower numeric types (INT vs BIGINT, FLOAT vs DOUBLE) reduce memory footprint and can improve performance on large datasets, but choosing too narrow a type risks overflow errors during transformations or aggregations.
• 6When defining Delta Lake table schemas, changing a column's data type later requires schema evolution or overwrite operations, so selecting the correct type upfront avoids costly downstream migrations.

2 · Check your understanding