Error in Dbt Transformation while Ingesting Data into MSSQL Tables from Square

Slack Knowledge Base Q&A Connector Questions
, , , ,
1

Summary

The user is facing errors related to timestamp format mismatch while ingesting data into MSSQL tables from Square. They have provided Dbt transformation code, dbt_project.yml, and profiles.yml. They are seeking help on handling the transformation issue.


Question

Hello Everyone

I was facing this error while ingesting data into my MSSQL tables from the Square
time data '2022-10-17T17:06:06Z' does not match format '%Y-%m-%dT%H:%M:%S.%fZ'
So, I tried to write a custom transformation and add it to my pipeline
but while re-running the pipeline again I’m getting this error now
message='io.temporal.serviceclient.CheckedExceptionWrapper: io.airbyte.workers.exception.WorkerException: Dbt Transformation Failed.', type='java.lang.RuntimeException', nonRetryable=false
this is my Dbt transformation code and its structure
tranformation code

    select *,
        case
            when updated_at ~ '^\d{4}-\d{2}-\d{2}T\d{2}:\d{2}:\d{2}Z$' then
                concat(updated_at, '.000000Z')
            else
                updated_at
        end as fixed_updated_at
    from {{ source('Square', 'discounts') }}
)

select
    *,
    fixed_updated_at as updated_at
from source```
`dbt_project.yml`
```name: 'my_transformation_project'
version: '1.0.0'
config-version: 2

profile: 'my_transformation_project'

model-paths: ["models"]

models:
  my_transformation_project:
    fix_discounts_timestamps:
      materialized: view ```
`profiles.yml`
```my_transformation_project:
  target: dev
  outputs:
    dev:
      type: msssql
      host: <host>
      user: <username>
      password: <password>
      port: <port>
      dbname: <dbname>
      schema: <schema>
      threads: 1
      keepalives_idle: 0
      connect_timeout: 10
      search_path: public ```
Structure:
```.
├── dbt_project.yml
├── models
│   └── fix_discounts_timestamps.sql
└── profiles.yml```
Any way I can handle this issue of transformation
```time data '2022-10-17T17:06:06Z' does not match format '%Y-%m-%dT%H:%M:%S.%fZ'```

Also attaching some screenshots for a better picture.
Any help will be great full
Thank you!

P.S. I'm using airbyte & Dbt for the first time

<br>

---

This topic has been created from a Slack thread to give it more visibility.
It will be on Read-Only mode here. [Click here](https://airbytehq.slack.com/archives/C021JANJ6TY/p1720083941436129) if you want 
to access the original thread.

[Join the conversation on Slack](https://slack.airbyte.com)

<sub>
["error", "dbt-transformation", "mssql", "square", "timestamp-format", "ingestion", "airbyte", "dbt"]
</sub>