DEV Community: Taylor Coleman

The Data Engineers Descent Into Datetime Hell

Taylor Coleman — Mon, 15 Dec 2025 14:28:07 +0000

People cannot spell for s***. And datetimes are very difficult to format consistently for various reasons. Date inference is genuinely hard—there's no universal standard, and everyone has their own idea of what "01/02/2023" means (January 2nd or February 1st?). This is my personal battle.

There's a reason this meme is one of the most upvoted posts on r/dataengineering. We've all been there.

The AWS Glue Migration That Wasn't

Here's something I dealt with recently. We were migrating 50+ tables from MySQL to Postgres using AWS Glue. Should be straightforward—Glue's built for this. You set up the connection, map the tables, hit run, grab coffee.

Table #37 fails. Then #41. Then #44.

The error message is useless: ERROR: invalid input syntax for type timestamp. Cool. Which timestamp? Which row? Which of the 47 datetime columns in this table?

You're dealing with 10 million rows. And ONE row—ONE!!!—is causing the entire migration to fail.

You dig into the source data. The created_at column has entries like:

2023-01-15 14:30:00   -- Standard MySQL datetime
01/15/2023 2:30 PM    -- Someone's Excel export
January 15, 2023      -- Marketing team entry
2023-1-15             -- Missing zero padding
15-Jan-23             -- European contractor
NULL                  -- Actually fine
""                    -- Empty string (NOT fine)
Q1 2023               -- Why? Just... why?

MySQL doesn't care. It's got loose type coercion. Postgres? Postgres will not accept this nonsense.

PySpark Won't Save You

"Fine," you think. "I'll just use PySpark to clean this first."

from pyspark.sql import SparkSession
from pyspark.sql.types import TimestampType

spark = SparkSession.builder.getOrCreate()
df = spark.read.jdbc(url="jdbc:mysql://...", table="orders")

# Try to cast it
df = df.withColumn("created_at", F.col("created_at").cast(TimestampType()))

PySpark can't infer it. Which is bull****, by the way. It just returns NULL for anything it doesn't understand. No error. No warning. Just silent data loss.

So now you're writing custom parsing logic.

What I Actually Tried

Attempt 1: "Spark's built-in functions will handle this"

from pyspark.sql import functions as F
from pyspark.sql.types import TimestampType

df = df.withColumn("created_at", F.col("created_at").cast(TimestampType()))

Result: 30% of values become NULL. No error. No warning. Just gone.

"Fine, I'll try to_timestamp() with format strings:"

df = df.withColumn("created_at",
    F.to_timestamp(F.col("created_at"), "yyyy-MM-dd HH:mm:ss"))

Result: 70% become NULL because they're not in that exact format.

"I'll try multiple formats!"

df = df.withColumn("created_at",
    F.coalesce(
        F.to_timestamp(F.col("created_at"), "yyyy-MM-dd HH:mm:ss"),
        F.to_timestamp(F.col("created_at"), "MM/dd/yyyy"),
        F.to_timestamp(F.col("created_at"), "dd/MM/yyyy"),
        F.to_timestamp(F.col("created_at"), "yyyy-MM-dd")
    ))

Now I have 8% NULL. The other 92% parsed! But which format matched which row? No idea. And "January 15, 2023" is still NULL.

Added 12 more formats to the coalesce(). The query plan is now unreadable. Execution time: 45 minutes for 10 million rows.

Attempt 2: "I'll just write a UDF"

from pyspark.sql.functions import udf
from pyspark.sql.types import StringType
from datetime import datetime
import dateutil.parser

@udf(returnType=StringType())
def parse_datetime(value):
    if not value:
        return None
    try:
        return dateutil.parser.parse(value).strftime('%Y-%m-%d %H:%M:%S')
    except:
        return None

df = df.withColumn("created_at", parse_datetime(F.col("created_at")))

Execution time: 6 hours.

Why? Because UDFs serialize every row to Python, parse it, serialize it back. For 10 million rows. On distributed data. The Spark UI shows I'm not even using the cluster, it's all bottlenecked on Python serialization.

"Fine, I'll use pandas UDFs, those are faster:"

from pyspark.sql.functions import pandas_udf
import pandas as pd

@pandas_udf(StringType())
def parse_datetime_pandas(s: pd.Series) -> pd.Series:
    return pd.to_datetime(s, errors='coerce').dt.strftime('%Y-%m-%d %H:%M:%S')

Execution time: 2 hours. Better! But still 8% NULL. Still no idea which rows failed or why.

Attempt 3: "I'll do multiple passes with different UDFs"

# Pass 1: Standard formats
df = df.withColumn("created_at_clean", parse_udf_standard(F.col("created_at")))

# Pass 2: Fix nulls with named months
df = df.withColumn("created_at_clean",
    F.when(F.col("created_at_clean").isNull(),
        parse_udf_named_months(F.col("created_at")))
    .otherwise(F.col("created_at_clean")))

# Pass 3: Fix nulls with European formats
df = df.withColumn("created_at_clean",
    F.when(F.col("created_at_clean").isNull(),
        parse_udf_european(F.col("created_at")))
    .otherwise(F.col("created_at_clean")))

# Pass 4: ...you get the idea

Execution time: 4 hours (multiple full scans of 10 million rows)
Code length: 600 lines across 8 different UDFs
Success rate: 99.1%
My mental state: Broken

Attempt 4: "Maybe I can use regex_extract?"

# Extract ISO format dates
df = df.withColumn("extracted",
    F.regexp_extract(F.col("created_at"), r'(\d{4}-\d{2}-\d{2})', 1))

# Extract MM/DD/YYYY
df = df.withColumn("extracted",
    F.when(F.col("extracted") == "",
        F.regexp_extract(F.col("created_at"), r'(\d{2})/(\d{2})/(\d{4})', 0))
    .otherwise(F.col("extracted")))

This extracted dates but didn't parse them into the right format. 01/15/2023 stayed as 01/15/2023. I need it as 2023-01-15.

Now I need to parse what I extracted. Back to F.to_timestamp(). Back to NULLs.

Attempt 5: "I'll just dump to pandas and fix it there"

# Collect to pandas
pdf = df.toPandas()

# Fix in pandas
pdf['created_at'] = pd.to_datetime(pdf['created_at'], errors='coerce')

# Back to Spark
df = spark.createDataFrame(pdf)

Memory error. 10 million rows don't fit in memory on the driver node. Typical.

"Fine, I'll do it in partitions:"

def fix_partition(iterator):
    for pdf in iterator:
        pdf['created_at'] = pd.to_datetime(pdf['created_at'], errors='coerce')
        yield pdf

df = df.mapInPandas(fix_partition, schema=df.schema)

This works but takes 3 hours and yet still have 8% NULL values.

Attempt 6: The 2AM Abomination

from pyspark.sql.functions import pandas_udf
import pandas as pd
from dateutil import parser
import re

@pandas_udf(StringType())
def parse_datetime_nuclear_option(s: pd.Series) -> pd.Series:
    def parse_single(value):
        if pd.isna(value) or value == "":
            return None

        # Try pandas first (fast)
        try:
            return pd.to_datetime(value).strftime('%Y-%m-%d %H:%M:%S')
        except:
            pass

        # Try dateutil (slow but flexible)
        try:
            return parser.parse(str(value), fuzzy=True).strftime('%Y-%m-%d %H:%M:%S')
        except:
            pass

        # Try regex extraction for ISO
        match = re.search(r'\d{4}-\d{2}-\d{2}', str(value))
        if match:
            return match.group(0) + ' 00:00:00'

        # Try named months
        months = {'january': '01', 'jan': '01', 'february': '02', ...}
        # ... 40 more lines of string manipulation

        # Try quarter notation
        if 'Q' in str(value):
            # ... 20 more lines

        return None

    return s.apply(parse_single)

This is so frustrating. Just kill me.

df = df.withColumn("created_at", parse_datetime_nuclear_option(F.col("created_at")))

Execution time: 8 hours
Code length: 200 lines in a single UDF
Success rate: 99.4%
The remaining 0.6%: Truly cursed data like "FY Q3 2023", "sometime in january", and my personal favorite: "2023-13-45" (month 13, day 45—someone just mashing numbers)
Cost: $47 in AWS Glue DPU hours

At this point it's 4am. The migration is still failing. I have a 200-line UDF that takes 8 hours to run and still doesn't work for all rows. And I still have 12 other tables with datetime columns to fix.

Read the rest at Full Blog Post Here

DataCompose: A data transformation framework for building reusable, composable data pipelines in PySpark.

Taylor Coleman — Mon, 25 Aug 2025 17:36:37 +0000

The most important part of any Data Product is Data Quality.

Whether you're a Business Analyst using reporting tools like PowerBI or Superset, a data scientist building RAG systems for LLMs, or a researcher working with gene sequencing — clean data is the foundation of everything. Yet, achieving data quality remains one of the most challenging aspects of data engineering.

The Phone Number Problem

Let's look at a common scenario: cleaning phone numbers using regex in PySpark.

# This is you at 3 AM trying to clean phone numbers
df = df.withColumn("phone_clean",
    F.when(F.col("phone").rlike("^\d{10}$"), F.col("phone"))
    .when(F.col("phone").rlike("^\d{3}-\d{3}-\d{4}$"),
          F.regexp_replace(F.col("phone"), "-", ""))
    .when(F.col("phone").rlike("^\(\d{3}\) \d{3}-\d{4}$"),
          F.regexp_replace(F.regexp_replace(F.col("phone"), "[()-\s]", ""), " ", ""))
    # ... 47 more edge cases you haven't discovered yet
)

But wait, there's more problems:

Extracting phone numbers from free-form text
International formats and country codes
Extensions like "x1234" or "ext. 5678"
Phone numbers embedded in sentences

The Current Solutions Fall Short

Option 1: External Libraries

Packages like Dataprep.ai or PyJanitor seem promising, but:

They only work with Pandas (not PySpark)
Built-in assumptions you can't change without forking
One-size-fits-all approach doesn't fit your data

Option 2: Regex Patterns

Hard to maintain and difficult to read
Brittle and prone to edge cases
Each new format requires updating complex patterns

Option 3: LLMs for Data Cleaning

Compliance nightmare with PII data
Expensive at scale
Non-deterministic results

The Root Problem

Bad data is fundamentally a people problem. It's nearly impossible to abstract away human inconsistency into an external package. People aren't predictable, and their mistakes don't follow neat patterns.

Our Data Quality Hypothesis

I believe data errors follow a distribution something like this:

Distribution of errors in human-entered data:

█████████████ 60% - Perfect data (no cleaning needed)
████████      30% - Common errors (typos, formatting)
██            8%  - Edge cases (weird but handleable)
▌             2%  - Chaos (someone typed their life story in the phone field)

DataCompose: Clean the 38% that matters
Let the juniors clean the last 2% (it builds character)

The Uncomfortable Truth About AI and Data Quality

Everyone's racing to implement RAG, fine-tune models, and build AI agents. But here's what they don't put in the keynotes: Your RAG system is only as good as your data quality.

You can have GPT-5, Claude, or any frontier model, but if your customer database has three different formats for phone numbers, your AI is going to hallucinate customer service disasters.

The Real Cause of AI Failures

Most "AI failures" are actually data quality failures.

That customer complaint about your AI-powered system giving wrong information? It's probably because:

Your address data has "St." in one table and "Street" in another
Phone numbers are stored in three different formats
Names are sometimes "LASTNAME, FIRSTNAME" and sometimes "FirstName LastName"

DataCompose isn't trying to be AI. We're trying to make your AI actually work by ensuring it has clean data to work with.

And here's the kicker: your 38% of problematic data is not the same as everyone else's. Your business has its own patterns, its own rules, and its own weird edge cases.

DataCompose Principle #1: Own Your Business Logic

Data transformations and data cleaning are business logic. And business logic belongs in your code.

This is the fundamental problem. So how do we square the circle of these transformations being hard to maintain, yet too inflexible to have as an external dependency?

We took inspiration from the React/Svelte fullstack world and adopted the shadcn "copy to own" pattern, bringing it to PySpark. Instead of importing an external library that you can't modify, you get battle-tested transformations that lives in your code.

We call our building blocks "primitives" — small, modular functions with clearly defined inputs and outputs that compose into pipelines. When we have a module of primitives that you can compose together, we call it a transformer. These aren't magical abstractions; they're just well-written PySpark functions that you own completely.

With this approach, you get:

Primitives that do 90% of the work - Start with proven patterns
Code that lives in YOUR repository - No external dependencies to manage
Full ability to modify as needed - It's your code, change whatever you want
No dependencies beyond what you already have - If you have PySpark, you're ready

DataCompose Principle #2: Validate Everything

Data transformations should be validated at every step for edge cases, and should be adjustable for your use case.

Every primitive comes with:

Comprehensive test cases
Edge case handling
Clear documentation of what it does and doesn't handle
Configurable behavior for your specific needs

DataCompose Principle #3: Zero Dependencies

No external dependencies beyond Python/PySpark (including DataCompose). Each primitive must be modular and work on your system without adding extra dependencies.

Why this matters:

PySpark runs in the JVM — adding dependencies is complex
Enterprise environments have strict package approval processes
Every new dependency is a potential security risk
Simple is more maintainable

Our commitment: Pure PySpark transformations only.

How it works

1. Install DataCompose CLI

pip install datacompose

2. Add the transformers you need - they're copied to your repo, pre-validated against tests

datacompose add addresses

3. You own the code - use it like any other Python module

# This is in your repository, you own it
from transformers.pyspark.addresses import addresses
from pyspark.sql import functions as F

# Clean and extract address components
result_df = df \
    .withColumn("street_number", addresses.extract_street_number(F.col("address"))) \
    .withColumn("street_name", addresses.extract_street_name(F.col("address"))) \
    .withColumn("city", addresses.extract_city(F.col("address"))) \
    .withColumn("state", addresses.standardize_state(F.col("address"))) \
    .withColumn("zip", addresses.extract_zip_code(F.col("address")))

result_df.show()

4. Need more? Use keyword arguments or modify the source directly

The Future Vision

Our goal is simple: provide clean data transformations as drop-in replacements that you can compose as YOU see fit.

No magic
No vendor lock-in
Just reliable primitives that work

What's Available Now

We're starting with the most common data quality problems:

Addresses — Standardize formats, extract components, validate
Emails — Clean, validate, extract domains
Phone Numbers — Format, extract, validate across regions

What's Next

Based on community demand, we're considering:

Date/time standardization
Name parsing and formatting
Currency and number formats
Custom business identifiers

Want to see something specific? Let us know!

https://github.com/datacompose/datacompose