4.3: Data Types and Data Transformation

Data Types and Transformation

Correct data types are the foundation of clean data. Every column of data has an expected type (e.g. integer, float, text, date, boolean), and using the proper type ensures that data is stored and interpreted in a meaningful way. Data type issues often arise in two forms: mismatched types (data stored in the wrong type) and inconsistent formatting (data that needs transformation to meet type requirements).

Why data types matter: Imagine a column product_price that contains numeric prices, but in your raw dataset it is stored as a text string "19.99" rather than a numeric value 19.99.

igure \(\PageIndex{1}\): Demonstrates how storing numeric data as text causes analytical functions like AVG() to fail, and how proper typing with CAST enables correct computation. (CC BY 4.0; Felix Amoruwa) Access to a detailed description of Figure \(\PageIndex{1}\)

If you try to compute an average price with an SQL query SELECT AVG(product_price) FROM Products;, you will get an error or a meaningless result because the database cannot average text strings. The data type must be converted to a numeric type (e.g., DECIMAL or FLOAT) to perform mathematical operations. Proper data types also enable correct sorting and filtering. For instance, if dates are stored as text, sorting them lexicographically (as strings) will put "12/31/2024" before "01/01/2023" (because "12" comes before "01"), which is obviously incorrect chronologically. If stored as actual DATE type, the sorting will be by date value.

Common type issues: Some frequent problems include numeric fields stored as text (as above), numbers stored in an overly permissive type that allows non-numeric characters (e.g. a ZIP code stored in a text field might accidentally contain letters or punctuation), or dates stored in inconsistent string formats ("MM/DD/YYYY" in some records vs "YYYY-MM-DD" in others). Another issue is unit inconsistency – e.g., a length field might sometimes include text like "5 ft" vs "60 in". While not exactly a wrong type (still text), it indicates a need for data transformation to a common unit and numeric type.

Using CAST and conversion: Data preparation often requires converting data from one type to another. SQL provides the CAST() function (and equivalent CONVERT() in some databases) to explicitly change a value’s type. For example, if product_price is text, one could write:

SELECT AVG(CAST(product_price AS DECIMAL(10,2)))
FROM Products;

This attempts to convert the text to a decimal numeric with two decimal places. If the text cannot be parsed as a number, the result will be an error or NULL for that row, alerting us that some entries are non-numeric (which is a data quality issue to fix by cleaning those entries). Another example: converting a string date CAST(order_date AS DATE) so that we can apply date functions or compare dates properly. When performing such transformations, it’s good to first run a validation query to ensure all values can be converted. For instance, SELECT product_price FROM Products WHERE TRY_CAST(product_price AS DECIMAL(10,2)) IS NULL AND product_price IS NOT NULL; (using a safe cast like TRY_CAST in SQL Server) will show any non-null values that failed conversion – these could be strings like "N/A" or "TBD" that need to be handled.

Beyond CAST, an alternative would be to use parsing functions or tools if the data is not cleanly separable. For example, splitting a full name "Doe, John" into last and first name fields might require a string function or a regex. Or extracting numbers from a text that contains units (e.g., "60in" → 60 as number and "in" as unit).

Enforcing data types: In relational databases, one of the first lines of defense is defining columns with appropriate types and constraints so that invalid data cannot be inserted. If a column is defined as INTEGER, the database will reject any non-integer input for that column (or store it as NULL/default). In practice, however, when importing from external data (CSV, JSON, etc.), we might temporarily store everything as text or load into a staging area, and then need to transform to correct types. This is where data cleaning scripts or tools come into play: they transform the raw data strings into typed, validated values for the final database or analysis.

Data type examples: Common data types we encounter in analytics include:
- Integer: Whole numbers (e.g., count of items, no fractional part).
- Float/Decimal: Numeric values with fractional parts or requiring high precision (e.g., prices, measurements).
- String (Text): Alphanumeric data (names, addresses, categorical values).
- Boolean: True/False values (sometimes stored as 1/0 or Y/N).
- Date/Time: Dates, times, or timestamps representing specific moments or intervals.

Using the correct type ensures that operations behave as expected. For example, summing two integers vs concatenating two strings that look like numbers yield very different results (“2” + “3” = “23” as strings, but 2 + 3 = 5 as integers). Similarly, date arithmetic (like adding 7 days) is only possible if the values are date types.

Data transformation logic: Cleaning data often entails more than simple type casting. We may need to apply transformation logic to standardize values. Some typical transformations include: - Trimming and case normalization: Remove leading/trailing whitespace and standardize text case (e.g., make all entries uppercase or lowercase) to avoid treating "New York " and "new york" as different. In SQL, one can use TRIM(), UPPER() or LOWER() functions.
- Replacing or recoding values: For example, convert all NULL or empty strings in a text field to a standard "Unknown" label, or map various forms of a category to one canonical form (e.g., ["NY", "N.Y.", "NewYork"] all mapped to "NY"). This can be done with SQL CASE expressions or lookup tables.
- Splitting or merging fields: Combine related fields (e.g., first_name and last_name into full_name) or split a compound field into parts (e.g., an address into street, city, zip). Each might require parsing logic.
- Deriving new values: Creating calculated fields to help with cleaning, such as an age derived from birth_date – if the age doesn’t match an expected range given the birth_date, that might flag an issue. Or generating a standardized key from text to identify duplicates (soundex or other fuzzy matching keys).

These transformations are part of data preparation to make the dataset consistent and analysis-ready. Tools like Google’s Cloud Dataprep (Trifacta) automate many of these transformations with a user-friendly interface [6]. In fact, Cloud Dataprep will infer the data type of each column and even identify likely errors or mismatches based on those types, suggesting cleaning steps. For instance, if a column is inferred as dates but 5% of values don’t parse as valid dates, the tool will flag those and might recommend a transformation or highlight them for user correction. Such tools leverage AI to guess the intended data type and common format corrections, making the process more efficient.

To conclude, using the correct data types and applying necessary transformations is fundamental to data cleaning. It ensures that subsequent analysis (sorting, filtering, aggregating) is accurate and meaningful. A good practice is to validate after conversion – e.g., after casting a column to numeric, check min, max, average to see if they make sense (if a price field has an average of 1e9 after casting, perhaps some outliers were parsed incorrectly). With sound data typing and transformation, we lay a solid groundwork for all further analysis.