Datetime conversion on streaming tables

[singh_tushar_14]

I am using streaming tables to read from multiple parquet files and create a table in my raw layer.

Tech Stack -- dbt-databricks

While loading I have a column called "ets" in my source which is nothing "milliseconds since Jan 1st 1970." 

I want this to be converted into a timestamp format for which I use 

timestampadd(millisecond, ets, '1970-01-01')

ets = 1746046131518
expected_output = 2025-04-30T20:48:51.518+00:00
actual_output = 1969-12-08T18:54:02.046+00:00

• Running it as a dry code on databricks notebook gives the correct output but when compiled through dbt and ran as a model same code gives a different output.

DBT Model code

SELECT
    *,
    {{ dbt.dateadd("MILLISECOND", "ets", "'1970-01-01'") }} AS ets_converted,
    {{ add_metadata_fields() }}
FROM STREAM read_files(
    'path/to/volume',
    format => 'parquet',
    header => TRUE
)

Compiled DBT Code from Databricks.

CREATE STREAMING TABLE `catalog_name`.`schema_name`.`table_name` (<inferredSchema>)
SELECT
  *,
  timestampadd(MILLISECOND, ets, '1970-01-01') AS ets_converted,
  CURRENT_TIMESTAMP() AS meta_loaded_at
FROM
  STREAM read_files(
    'path/to/volumes',
    format => 'parquet',
    header => TRUE
  )

I am not able to make an assumption is this thing wrong with dbt or streaming table or code.

[szymon_dybczak · Accepted solution]

Hi @singh_tushar_14 ,

Since your ets columns already contains integer that represents milliseconds since Jan 1st 1970, can't you just use Spark SQL fuction? You don't need to add anything to 1970-01-01, since that information is already "encoded" in your ets attribute:

%sql SELECT timestamp_millis(1746046131518) 

[singh_tushar_14]

Agreed. There is nothing wrong with the above code as well. Logically it should result in the same value.
I am seeing problem when running this as a sql model through dbt.

I tried 10 runs consecutively and it gave me the wrong outputs again and again (not the databricks cell run). On the 11th run the same code results in a different output. Is this kind of a bug or something ?

[szymon_dybczak]

Maybe there's something wrong with datatypes? Could you provide data type of that column? 

[singh_tushar_14]

Datatype - BIGINT. Sample Data 

ets = 1754013182959