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

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.

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.