Redshift column types being converted (UTC -> PT, BIGINT -> Double (float))

Hi all,

I'm having an issue with a specific datasource in Redshift converting columns of ID numbers from INTs to Double, thus making the "combine datasets" feature not work.

Additionally, in this specific data source, timestamps are being converted from UTC into PDT or MST. Since we store most times as UTC in our databases, this means that specific charts from specific data sources will report different times/dates/totals.

The underlying query is just a SELECT * FROM tablename;, which I created in the web interface.

It seems like the options are pretty simple, but is there some sort of configuration tool that I'm missing here that's making this happen selectively?

Find more posts tagged with

Comments

kshah008

Hi all,

Can anybody help @rfilmyer-turo with their question?

Thanks!

rfilmyer-turo

I reached out to @DomoSupport within Buzz. Still an open question.

They said that they're reaching out to the engineering team for why BIGINTs are being converted to DOUBLEs instead of LONGs (or why it's happening inconsistently between different tables!)

The workaround for now is to run it through an ETL function.

kshah008

@rfilmyer-turo, please keep us updated if you figure anything out ?

rfilmyer-turo

It turns out that the date issue is not a bug, but it is a gotcha that might confuse other people who deal with data stored in UTC time.

TL;DR Dates don't have timestamps, so if you have date-level and timestamp-level data sources in Domo, they will not reconcile if your data isn't in the same timezone as your Domo configuration.

Our issues with dates were caused by 2 data sources - one a conventional relational SQL database, and one Amazon Redshift (columnar) - that returned conflicting results. The data from the SQL DB (where we would get data summarized by date) returned data that was aggregated daily by UTC. But the data from Redshift (where we would get individual data points and do aggregation in Domo instead) returned data that was aggregated daily by Pacific Time (our local timezone).

Although it is obvious in retrospect, at the time we were trying to hammer down this error, we had no idea why Domo was converting some data's timestamps and not others. Customer support mentioned that it was likely because of our At the time, we just decided to avoid transitioning a few charts from SQL-based data to Redshift-based data.

It wasn't until we revisited the discrepancy in a different chart that we realized why MySQL's data points were not converted: Since we converted SQL's timestamps to dates at the beginning of the pipeline (as part of the query used to load data), and Redshift's at the end (in our charts), the data was only selectively being localized. After all, if you get a date without a timestamp, how are you to know whether an event happened on Monday UTC, but Sunday PST?

While we're transitioning our metrics to Redshift, the intermediate solution is to set Domo's local timezone to UTC. Eventually, when we are able to phase out the relational database entirely, we'll consider setting local time again.

The takeaway is that if your data doesn't have time zones, and you want your figures to reconcile completely, make sure that either you only use either date-level or timestamp-level data, or set Domo's local time to the timezone of your data.

rfilmyer-turo

That being said, the columns being converted from integers to floating point numbers is still an ongoing issue that precludes us from using the "combine datasets" feature reliably. I reached out to customer support yesterday and am awaiting a response in the meantime.

kshah008

@rfilmyer-turo, please keep us updated ?