https://linen.dev logo
Join Slack
Powered by
how can i change the timestmap precision of execut...
# ask-elementary-ai
d
how can i change the timestmap precision of execute_completed_at
k
The approach to fixing timestamp precision for 
execute_completed_at
depends on your data warehouse: BigQuery Starting with 
dbt-bigquery >= 1.11
, nanosecond-precision timestamps (9 fractional digits) began appearing in 
execute_completed_at
in 
dbt_run_results
, causing 
CAST(... AS TIMESTAMP)
failures since BigQuery only supports microsecond precision (6 digits). [BigQuery timestamp fix] This was addressed in two ways in the Elementary package: 1. A 
normalize_artifact_timestamp_precision
macro was introduced to normalize timestamp precision for 
execute_started_at
, 
execute_completed_at
, 
compile_started_at
, and 
compile_completed_at
fields in 
upload_run_results.sql
and 
upload_source_freshness.sql
. [BigQuery artifacts fix] 2. A 
bigquery__edr_cast_as_timestamp
dispatch macro was added that truncates fractional seconds beyond microsecond precision (6 digits) before casting string values to 
TIMESTAMP
. [BigQuery timestamp fix] Recommendation: Upgrade to the latest Elementary dbt package version to get these fixes automatically. Redshift A similar issue (null values in 
execute_completed_at
and related columns) was reported after upgrading to dbt 
1.8.x
with Elementary 
0.15.1
. This was resolved in Elementary version 0.16.0. [GitHub issue] If you are on a different data warehouse or a specific version and still experiencing issues, please share your Elementary version and DWH type, as the fix may vary. The knowledge sources do not contain a manual SQL workaround you can apply yourself beyond upgrading the package. šŸ‘ 0 button šŸ‘Ž 0 button ā„¹ļø Tip: Mention @elementary.ai in the thread for follow-ups or DM the app for 1-to-1 help.
d
i what 6 digit precision
i need 6 digit
@kapa.ai how can i change the precision from 9 to 6
k
Based on the knowledge sources, the fix for truncating nanosecond precision (9 digits) down to microsecond precision (6 digits) in BigQuery is handled automatically by Elementary via a 
bigquery__edr_cast_as_timestamp
dispatch macro. Here's what it does under the hood: It uses 
regexp_replace
to truncate fractional seconds beyond 6 digits before casting to 
TIMESTAMP
. The pattern used is 
r'(.\d{6})\d+'
with a 
'\\1'
backreference ā€” timestamps with ā‰¤6 fractional digits or no fractional seconds pass through unchanged. [BigQuery timestamp fix] The recommended approach is simply to upgrade your Elementary dbt package to the version that includes this fix, rather than manually modifying anything. The fix is already built into the package. If you are not on BigQuery, the knowledge sources do not contain specific instructions for manually changing timestamp precision from 9 to 6 digits on other data warehouses. Could you share which data warehouse you're using? That would help narrow down the right guidance. šŸ‘ 0 button šŸ‘Ž 0 button ā„¹ļø Tip: Mention @elementary.ai in the thread for follow-ups or DM the app for 1-to-1 help.
d
@kapa.ai
Copy code
elementary-data==0.23.2
dbt-bigquery==1.11.1
Copy code
packages:
  - package: elementary-data/elementary
    version: 0.23.0
dbt-fusion 2.0.0-preview.173
k
Based on your setup: ā€¢ 
elementary-data==0.23.2
(CLI) ā€¢ 
elementary-data/elementary==0.23.0
(dbt package) ā€¢ 
dbt-bigquery==1.11.1
ā€¢ 
dbt-fusion 2.0.0-preview.173
This matches exactly the scenario described in the knowledge sources. With 
dbt-bigquery >= 1.11
, nanosecond-precision timestamps (9 fractional digits) appear in 
execute_completed_at
, causing 
Invalid timestamp
errors that block 
edr report
and 
edr send-report
. [BigQuery timestamp fix] Two fixes were merged into the Elementary dbt package to address this: 1. A 
normalize_artifact_timestamp_precision
macro to normalize precision when writing artifact timestamps. [BigQuery artifacts fix] 2. A 
bigquery__edr_cast_as_timestamp
dispatch macro that truncates fractional seconds beyond 6 digits before casting. [BigQuery timestamp fix] However, the knowledge sources do not explicitly confirm whether these fixes are included in version 
0.23.0
of the Elementary dbt package. Since you are already on 
0.23.0
, I cannot say with certainty whether upgrading further would resolve the issue based on the available information. What I'd recommend: ā€¢ Check if a newer version of 
elementary-data/elementary
is available beyond 
0.23.0
and upgrade to it. ā€¢ Reach out to the Elementary community Slack, as this exact issue (BigQuery + 
dbt-bigquery >= 1.11
+ nanosecond timestamps) was actively being tracked and discussed there. [Slack discussion] šŸ‘ 0 button šŸ‘Ž 0 button ā„¹ļø Tip: Mention @elementary.ai in the thread for follow-ups or DM the app for 1-to-1 help.
d
@kapa.ai make me a macro to fix it
k
Based on the knowledge sources, here is the 
bigquery__edr_cast_as_timestamp
dispatch macro that Elementary uses to fix the nanosecond-to-microsecond precision issue on BigQuery: [BigQuery timestamp fix]
Copy code
{% macro bigquery__edr_cast_as_timestamp(column) %}
    cast(
        regexp_replace({{ column }}, r'(\.\d{6})\d+', '\\1')
        as timestamp
    )
{% endmacro %}
This macro: ā€¢ Uses 
regexp_replace
to truncate any fractional seconds beyond 6 digits (microseconds) before casting to 
TIMESTAMP
ā€¢ Passes through timestamps with ā‰¤6 fractional digits or no fractional seconds unchanged ā€¢ Only affects BigQuery; all other adapters continue using the default 
cast(... as timestamp)
path You can place this macro in your own dbt project's 
macros/
directory. Since dbt prioritizes project-level macros over package macros, it will override the default behavior without modifying the Elementary package files directly. > Note: The knowledge sources describe this macro's behavior and intent but do not show the exact full implementation code. The above is derived directly from the description in the PR. I'd recommend verifying the exact syntax against the merged PR in the Elementary repository before using it in production. šŸ‘ 0 button šŸ‘Ž 0 button ā„¹ļø Tip: Mention @elementary.ai in the thread for follow-ups or DM the app for 1-to-1 help.
d
@kapa.ai and what is the file name
k
The knowledge sources do not explicitly mention the file name for the 
bigquery__edr_cast_as_timestamp
macro. However, following standard dbt naming conventions and the pattern described in the PR, a reasonable name would be something like 
edr_cast_as_timestamp.sql
placed in your 
macros/
directory. [BigQuery timestamp fix] I'd recommend checking the Elementary repository directly to confirm the exact file name used in the merged fix. šŸ‘ 0 button šŸ‘Ž 0 button ā„¹ļø Tip: Mention @elementary.ai in the thread for follow-ups or DM the app for 1-to-1 help.
d
edr_cast_as_timestamp
20 Views
Open in Slack
PreviousNext
Powered by