Spark SQL: Data Type Conversion with CAST

1. What is CAST and Why Do We Need It?

In data pipelines, it's very common for data to arrive in a format that isn't ideal for analysis.

• Prices might be loaded as strings ("19.99").
• User IDs might be numbers when you need them as text for concatenation.
• Boolean flags might be represented as integers (1 or 0).

To perform correct calculations and maintain data integrity, you need to convert these values to their proper data types. This process is called casting. While Spark can sometimes perform implicit casting automatically, relying on it can lead to unexpected results. Explicitly using the CAST function makes your code clearer, more predictable, and easier to debug.

For a quick and clear introduction to the concept, please watch this short video.

2. CAST Function Syntax

As you saw in the video, the syntax for CAST is straightforward.

Standard Syntax:

• expression: A column name, literal value, or another function's result that you want to convert.
• target_data_type: The data type you want to convert to (e.g., INT, DECIMAL(10, 2), STRING, DATE, BOOLEAN).

Shorthand Syntax:
Databricks SQL also supports a common shorthand notation using ::.

For example, CAST('123' AS INT) is equivalent to '123'::INT. Both are valid, but CAST is more universally recognized across different SQL dialects.

This image provides a great visual summary of common conversions.

3. Common Casting Scenarios and Rules

The CAST function is powerful, but its behavior depends on the source and target data types. The official Databricks documentation is the best source for detailed rules.

Let's summarize the key takeaways and see some examples.

String to Numeric

This is one of the most frequent operations.

SELECT
  CAST('547' AS INT) AS string_to_int,
  '99.95'::DECIMAL(10, 2) AS string_to_decimal;

Important: If the string is not a valid representation of the number, the query will fail. For example, CAST('abc' AS INT) will produce an error.

Numeric to String

Useful when you need to concatenate a number with a string.

SELECT
  'Product ID: ' || CAST(1023 AS STRING) AS product_sku;

This is also useful for formatting dates, as shown in this practical example where the month number is cast to a string to be padded with a zero.

Numeric to Numeric

This is often used to change precision or truncate decimals.

SELECT
  CAST(99.95 AS INT) AS decimal_to_int,      -- Result: 99 (fraction is truncated)
  CAST(150 AS TINYINT) AS int_to_tinyint;   -- Result: Error! 150 is out of range for TINYINT (-128 to 127)

Be mindful of potential data loss (truncation) or overflow errors when the target type cannot accommodate the source value.

String to Date

As noted in the documentation, CAST can be used to convert strings to dates, but it expects the standard yyyy-MM-dd format.

SELECT CAST('2024-05-21' AS DATE);

For any other format (e.g., '21/05/2024'), you must use the to_date() function from our previous lesson with a format string.

4. Hands-On Practice: Cleaning a Staging Table

Let's apply what you've learned. Imagine we've loaded raw product data into a staging table. The data types are all strings, and the data needs to be cleaned and cast to the correct types for a final products table.

Instructions:
Open a Databricks SQL notebook and run these queries.

Step 1: Create the Raw Data
This simulates a table where all data has been ingested as STRING.

CREATE OR REPLACE TABLE raw_product_data (
  product_id STRING,
  product_name STRING,
  price STRING,
  units_in_stock STRING,
  is_active STRING,
  launch_date STRING
);

INSERT INTO raw_product_data VALUES
  ('101', 'Spark Engine Pro', '$499.99', '1,200', 'true', '2023-01-15'),
  ('102', 'Delta Lake Storage', '$199.50', '850', '1', '2022-11-01'),
  ('103', 'Photon Query Accelerator', 'Contact Us', '300', '0', '2023-05-30'),
  ('104', 'Unity Catalog Governor', '$250.00', 'N/A', 'false', '2023-08-20');

Step 2: Clean and CAST the Data
Now, construct a query to select from raw_product_data and convert each column to its proper type. Notice how we might need to use other functions before we can cast.

SELECT
  CAST(product_id AS INT) AS product_id,
  product_name,
  -- First, remove the '$' and then cast to a decimal.
  CAST(REPLACE(price, '$', '') AS DECIMAL(10, 2)) AS price,
  -- First, remove the ',' and then cast to an integer.
  CAST(REPLACE(units_in_stock, ',', '') AS INT) AS units_in_stock,
  -- Cast 'true'/'1' or 'false'/'0' to a boolean.
  CAST(is_active AS BOOLEAN) AS is_active,
  -- Cast the standard string format to a date.
  CAST(launch_date AS DATE) AS launch_date
FROM
  raw_product_data;

When you run this query, you will get an error! Why? CAST is strict. It fails when it encounters values that cannot be converted, such as 'Contact Us' and 'N/A'. This is a common problem in real-world pipelines.

5. Handling Errors with TRY_CAST

A single "bad" row can cause your entire query or job to fail. For production-grade pipelines, this is not ideal. Databricks provides a safer alternative: TRY_CAST.

TRY_CAST works exactly like CAST, but if a conversion fails, it returns NULL instead of an error, allowing your query to continue processing other rows.

Step 3: Build a Robust Query with TRY_CAST
Let's modify the previous query to use TRY_CAST.

SELECT
  TRY_CAST(product_id AS INT) AS product_id,
  product_name,
  TRY_CAST(REPLACE(price, '$', '') AS DECIMAL(10, 2)) AS price,
  TRY_CAST(REPLACE(units_in_stock, ',', '') AS INT) AS units_in_stock,
  TRY_CAST(is_active AS BOOLEAN) AS is_active,
  TRY_CAST(launch_date AS DATE) AS launch_date
FROM
  raw_product_data;

Now, the query succeeds. The rows with uncastable data ('Contact Us', 'N/A') now have NULL values in the problematic columns, which you could then filter or handle in a subsequent step. This makes your transformation logic much more resilient.

Conclusion

You now have a powerful and essential tool for managing data types in your transformations. By using CAST and TRY_CAST effectively, you can enforce schema correctness and build robust, fault-tolerant data pipelines.

Key Takeaways

• CAST is for Explicit Conversion: It's the standard SQL function to change a value's data type.
• Syntax: CAST(expression AS target_type) or the :: shorthand.
• Errors are Disruptive: CAST will fail the entire query if it encounters a value it cannot convert.
• TRY_CAST is for Resilience: For production pipelines, prefer TRY_CAST to handle bad data gracefully by returning NULL instead of throwing an error.

Next Steps

In the next lesson, we will shift gears from transforming values within columns to changing the very structure of your dataset. You will learn how to use PIVOT and UNPIVOT to reshape data, turning rows into columns and vice versa—a common requirement for creating summary reports and feature tables.