Has anyone gotten Incremental Refresh to work with only date?

[mkcongrove1]

All of the articles, and all of my searching tell me no - that I have to have my source data in DateTime. But wanted to see if there was some hidden knowledge that could be shared 🙂

My current hurdle is that I have multi-billion record tables that store date as a decimal (I'm not IT, don't ask me why). I can convert this to Integer easily. However, I'm afraid my database doesn't have the capacity to convert these billions of records to DateTime format, from their current Decimal format - the transformation will fail due to having to convert to text first and then to DateTime (I have yet to figure out how "Query folding DEFINITELY works with Snowflake", but that's a different issue).

I was successful in setting up the parameters and function to work with my date, but I run into my roadblock when I go to "Define Parameters" after importing. 

I know the error is because I don't have things in DateTime - so, has anyone figured out how to work around this issue?

[DataInsights · Accepted solution]

@mkcongrove1,

Incremental refresh does require a DateTime column. I've accomplished this with custom SQL and Snowflake functions such as TO_TIMESTAMP and TIMESTAMP_FROM_PARTS. Once you convert the decimal to integer, you can use TIMESTAMP_FROM_PARTS and SUBSTRING to construct a DateTime column. You might explore the most performant options for the DateTime conversion (Snowflake view, function, etc.).