[FEATURE] Define Clocktick in dbt_project vars

[MartinHofpower]

What additional value does this feature bring to the project?

I want to define the clocktick for ldts either explicitly as variable or implicitly by defining the format for ldts columns.

Is your feature request related to a problem? Please describe.

At the moment it is possible to choose a datatype for the ldts via the variable datavault4dbt.timestamp_format but in the sat_v1 templates there is a hardcoded time difference subtracted (e.g. for fabric 100 ns).
For example when someone chooses datetime2(0) as format for their ldts in a fabric implementation then the 100 ns that are subtracted in the sat_v1 models are washed away by rounding the ledts to full seconds.

Describe the solution you'd like

When choosing the clocktick to be one second implicitly by choosing the datavault4dbt.timestamp_format the "time subtraction" in the sat_v1 models should be adjusted dynamically.

Describe alternatives you've considered

At the moment there are two possibilities from my point of view:

• Choosing a more granular data type for ldts that corresponds to the time subtracted for my chosen adapter
• Overriding the sat_v1 macro

[tkiehn]

Hi @MartinHofpower and thanks for reaching out!

I absolutely agree this would be a valuable addition to the package!

Especially due to the fact that the subtractions are happening in multiple macros this is something that should be configurable globally.

My initial thought was the addition of two new global variables, one for the step-size and one for the unit.
This way we can ensure cross-platform compatibility in an easy way, in my opinion.

Deriving the clocktick from the chosen datatype is certainly the more elegant way, however I am unsure how simple the implementation would be, again due to the different types of data platforms that we support.

What do you think about the two-variable approach and would you be open to contribute by opening a PR?

Best Regards
Theo

[MartinHofpower]

Hi @tkiehn,

I think so too.
Two seperate global variables will also do the job and might be easier to implement and easier understandable from user's perspective.

I will have a look into the implementation and will get back to you in this issue or open a PR right away.

Best regards
Martin

[MartinHofpower]

Hi @tkiehn

I opened PR #488 from my fork of the repo. I was just able to test it with fabric and not the other adapters. If you have some kind of unit tests or regression tests for all platforms feel free to run them on the PR - theoretically the implementation should be perfectly backwards compatible with the used default parameters.

Best regards
Martin