Destination BigQuery: MM/DD/YYYY date format fails

[myyc]

Environment

• Airbyte version: 0.41.0
• OS Version / Instance: GCP
• Deployment: GKE
• Source Connector and version: Appstore 0.2.6
• Destination Connector and version: Bigquery 1.2.14
• Step where error happened: Sync job, normalisation step

Current Behavior

This has been reported in a few places. Data looks like this:

{"end_date":"11/29/2022","customer_price":"0.00","product_type_identifier":"1F","begin_date":"11/29/2022","_api_report_date":"2022-11-29",...}

As you can see, end_date and begin_date (possibly some others too) are MM/YY/DDDD. This is a problem with the API but the connector should take care of it.

Expected Behavior

Dates should be transformed into YYYY-MM-DD

Logs

Relevant bits:

2023-03-06 18:38:37 normalization > Running: transform-config --config destination_config.json --integration-type bigquery --out /config
2023-03-06 18:38:37 normalization > Namespace(config='destination_config.json', integration_type=<DestinationType.BIGQUERY: 'bigquery'>, out='/config')
2023-03-06 18:38:37 normalization > transform_bigquery
2023-03-06 18:38:37 normalization > Running: transform-catalog --integration-type bigquery --profile-config-dir /config --catalog destination_catalog.json --out /config/models/generated/ --json-column _airbyte_data
2023-03-06 18:38:37 normalization > Processing destination_catalog.json...
2023-03-06 18:38:37 normalization >   Generating airbyte_ctes/airbyte/appstoresales_report_ab1.sql from appstoresales_report
2023-03-06 18:38:37 normalization >   Generating airbyte_ctes/airbyte/appstoresales_report_ab2.sql from appstoresales_report
2023-03-06 18:38:37 normalization >   Generating airbyte_ctes/airbyte/appstoresales_report_ab3.sql from appstoresales_report
2023-03-06 18:38:37 normalization >   Generating airbyte_incremental/airbyte/appstoresales_report.sql from appstoresales_report
2023-03-06 18:38:38 normalization > detected no config file for ssh, assuming ssh is off.
2023-03-06 18:38:41 normalization >            [--event-buffer-size EVENT_BUFFER_SIZE]
2023-03-06 18:38:41 normalization >   --event-buffer-size EVENT_BUFFER_SIZE
2023-03-06 18:38:41 normalization > DBT >=1.0.0 detected; using 10K event buffer size
2023-03-06 18:38:44 normalization > Running with dbt=1.0.0
2023-03-06 18:38:44 normalization > Partial parse save file not found. Starting full parse.
2023-03-06 18:38:46 normalization > [WARNING]: Configuration paths exist in your dbt_project.yml file which do not apply to any resources.
There are 2 unused configuration paths:
- models.airbyte_utils.generated.airbyte_tables
- models.airbyte_utils.generated.airbyte_views
2023-03-06 18:38:46 normalization > Found 4 models, 0 tests, 0 snapshots, 0 analyses, 624 macros, 0 operations, 0 seed files, 1 source, 0 exposures, 0 metrics
2023-03-06 18:38:46 normalization > Concurrency: 8 threads (target='prod')
2023-03-06 18:38:48 normalization > 1 of 1 START incremental model airbyte.appstoresales_report............................................................. [RUN]
2023-03-06 18:38:55 normalization > 1 of 1 ERROR creating incremental model airbyte.appstoresales_report.................................................... [ERROR in 7.46s]
2023-03-06 18:38:55 normalization > Finished running 1 incremental model in 9.42s.
2023-03-06 18:38:55 normalization > Completed with 1 error and 0 warnings:
2023-03-06 18:38:55 normalization > Database Error in model appstoresales_report (models/generated/airbyte_incremental/airbyte/appstoresales_report.sql)
2023-03-06 18:38:55 normalization >   Invalid date: '03/05/2023'
2023-03-06 18:38:55 normalization > Done. PASS=0 WARN=0 ERROR=1 SKIP=0 TOTAL=1

Steps to Reproduce

1. Create an Appstore - BigQuery connection
2. Sync
3. Wait for the first step to finish
4. Transformation step should fail.

Are you willing to submit a PR?

WIP.

[sh4sh]

Hey @myyc could you submit your PR to this repo? The connector you modified is located here: https://github.com/airbytehq/airbyte/tree/master/airbyte-integrations/connectors/source-appstore-singer

[myyc]

Hey @myyc could you submit your PR to this repo? The connector you modified is located here: https://github.com/airbytehq/airbyte/tree/master/airbyte-integrations/connectors/source-appstore-singer

that's where i started, but then i realised this:

# setup.py
"tap-appstore @ https://github.com/airbytehq/tap-appstore/tarball/v0.2.1-airbyte",

which is why i submitted it here. i believe that in order for this to change there needs to be a version bump here and in the airbyte repo.

[Nakachi-S]

@myyc
Thank you for addressing this issue! Nice work!

@sh4sh
Can you confirm the issue and PR?
I am facing the same problem.

[egottschalk]

@sh4sh should the PR be the following change in the Dockerfile:

RUN pip install --prefix=/install https://github.com/myyc/tap-appstore/archive/refs/tags/0.2.1.1.tar.gz

This seems to be the only option until this is merged:

airbytehq/tap-appstore#3

[cgardens]

We have moved away from using dbt under the hood here. This issue should be resolved. Feel free to re-open if you run into this again.