Starysn
05/05/2025, 9:34 AM2025-05-02 only.
Below is my schema and table configuration
Schema:
"enableColumnBasedNullHandling": true,
"dateTimeFieldSpecs": [
{
"name": "datepromised",
"dataType": "TIMESTAMP",
"format": "TIMESTAMP",
"granularity": "1:DAYS"
}
]
Table:
"ingestionConfig": {
"transformConfigs": [
{
"columnName": "datepromised",
// "transformFunction": "FromDateTime(SUBSTR(JSONPATHSTRING(_airbyte_data, '$.datepromised'), 0, 10), 'yyyy-MM-dd')"
"transformFunction": "FromDateTime(SUBSTR(REPLACE(JSONPATHSTRING(_airbyte_data, '$.datepromised'), '+', ''), 0, 10), 'yyyy-MM-dd')"
}
]
}Xiang Fu
Xiang Fu
CASE WHEN FromDateTime(SUBSTR(JSONPATHSTRING(_airbyte_data, '$.datepromised'), 0, 10), 'yyyy-MM-dd') BETWEEN 0 AND xxxxxxx THEN FromDateTime(SUBSTR(JSONPATHSTRING(_airbyte_data, '$.datepromised'), 0, 10), 'yyyy-MM-dd') ELSE nullXiang Fu
Starysn
05/07/2025, 2:27 AM