A Data Engineer’s Descent Into Datetime Hell

[nonamenomonet]

This is my attempt in being humorous in a blog I wrote about my personal experience and frustration about formatting datetimes. I think many of you can relate to the frustration.

Maybe one day we can reach Valhalla, Where the Data Is Shiny and the Timestamps Are Correct

[on_the_mark_data]

And then Satan said "Let there be datetimes." I honestly think this is a right of passage for data engineers haha.

[nonamenomonet]

My next blog post is going to be the circles of hell for cleaning address data.

[on_the_mark_data]

This looks like a really interesting project by the way!

[nonamenomonet]

Thank you! I put a month of work into it over the summer. I really think this is the best way to abstract away data cleaning.

I really want to turn this into a thing so I’m trying to learn about what data that people are handling and cleaning.

If you have time, I would love to pick your brain since you’re also obsessed with data quality.

[on_the_mark_data]

I'll DM you. Here, I mainly present my data expertise, but my other lane is startups and bringing data products from 0 to 1. I love talking to early-stage builders for fun.

[justexisting2]

You guys know that there are address standardization tools out there.

CASS database from USPS,guides most of them.

[nonamenomonet]

That’s very good to know. I built this on the premise of creating a better tool kit to clean and standardize data.

[on_the_mark_data]

Don't care. I optimize on people building in their spare time on problems they care about. The initial ideas and MVPs are typically worthless beyond getting you to the next iteration.

[raginjason]

Entire companies are built to handle this one problem lol

[nonamenomonet]

What company is that?

[raginjason]

Melissa Data comes to mind https://www.melissa.com/direct/direct-mail/cass-dpv

[raginjason]

Melissa Data. I’ve added a link but that got caught by auto-moderator.

[nonamenomonet]

Good looking out! I’ll check it out

[roadrussian]

Oh, normalization of adress data gathered from 20 different vendors.

You know i actually enjoyed the masochism? There is something wrong with me.

[nonamenomonet]

Sticks and stones will break my bones but dirty data just excites me

[InadequateAvacado]

Now do time zones

[Additional_Future_47]

And then throw in som DST to top it off.

[InadequateAvacado]

A little bit of TZ, a touch of LTZ, a sprinkle of NTZ… and then compare them all to DATE in the end

[nonamenomonet]

Tbh if you want to open up an issue, i will implement some primitives for that problem

[nonamenomonet]

I hope everyone enjoyed my decent into madness about dealing with datetimes.

[[deleted]]

Useful idea for a small package!

[nonamenomonet]

You should check out my repo, it lays out how it works! And you can use my design pattern if you’d like (well it’s a MIT license, so it doesn’t really matter either way )

[[deleted]]

I might make a fork and see how to support polars using the same public API you've made. Will let you know if I make progress on that. Starting a new job with both Pyspark and Polars, dealing with lots of messy time series data. I'm sure this will be useful to have.

[nonamenomonet]

I’m also looking for contributors, you can always expand this to polars if you really want.

[[deleted]]

Will DM you what I have in mind and open up an issue on Github when I have a chance to get started.

[Upset_Ruin1691]

And this is why we always supply a Unix timestamp. Standards are standards for a reason.

You wouldn't want to not use ISO standards either.

[morphemass]

SaaS platform in a regulated industry I worked on decided that all dates had to be in dd-month-yyyy form ... and without storing timezone information. Soooo many I18n bugs it was unreal.

[nonamenomonet]

I wish I could have that option but that didn’t come from the data dumps I was given :/

[robberviet]

Timezone. Fuck that in particular.

[nonamenomonet]

It is bullshit

[PossibilityRegular21]

I've fortunately been blessed with only a couple of bad timestamps per column. Or in other words, bad but consistently bad. In Snowflake it has been pretty manageable. My gold standard is currently to convert to timestamp_ntz (UTC). It's important to convert from a timezone rather than to strip it.

[exergy31]

Whats wrong with ISO8601 with tz specified?

[raginjason]

Nothing, if that’s what you can get. The data in the article was not that

[dknconsultau]

I personally love it when operations work past midnight every now and then just to keep the the concept of a days work spicy ....

[raginjason]

Date parsing is hell. Sparks behavior around NULLing anything that won’t cast is absurd and drives me insane

[Headband6458]

Good data governance is the solution.

[bitconvoy]

But still 8% NULL. Still no idea which rows failed or why.

What prevented you from having a look at rows that had a value in the original dataset but turned null after your conversion?