🐛 Destination BigQuery-Denormalized: cannot format root of NULL with nested objects

[bazarnov]

Current Behavior

• During the sync, the connector outputs the complex data structure with a lot of nested objects/arrays with objects/ etc.
• If the root node == null and the json schema of the stream contains nested objects as expected by the destination, but data is not present - the destination fails with the error. (see the logs attached).

Expected Behavior

• Sync finished successfully

Possible solution

In theory we should check for the root object is not empty first, if so - drill down formatting nested data, otherwise - set the null.

Logs

logs-57-0.txt

Steps to Reproduce

1. Setup the source-shopify from this PR
2. Setup destination-bigquery-denormalized, using the latest version.
3. Run the sync using one of: draft_orders, products streams.

[bazarnov]

Also, adding the sample of the expected data from draft_orders stream.

{
    "type": "RECORD",
    "record": {
        "stream": "draft_orders",
        "data": {
            "id": 876124897469,
            "note": null,
            "email": "[email protected]",
            "taxes_included": true,
            "currency": "USD",
            "invoice_sent_at": null,
            "created_at": "2021-07-07T08:18:51-07:00",
            "updated_at": "2021-07-07T08:18:58-07:00",
            "tax_exempt": false,
            "completed_at": "2021-07-07T08:18:58-07:00",
            "name": "#D11",
            "status": "completed",
            "line_items": [
                {
                    "id": 57455297691837,
                    "variant_id": 40090613776573,
                    "product_id": 6796234391741,
                    "title": "Back Of Polka Dog Bandana",
                    "variant_title": "fuchsia",
                    "sku": "",
                    "vendor": "Lueilwitz - D'Amore",
                    "quantity": 1,
                    "requires_shipping": true,
                    "taxable": true,
                    "gift_card": false,
                    "fulfillment_service": "manual",
                    "grams": 182,
                    "tax_lines": [],
                    "applied_discount": null,
                    "name": "Back Of Polka Dog Bandana - fuchsia",
                    "properties": [],
                    "custom": false,
                    "price": 41.0,
                    "admin_graphql_api_id": "gid://shopify/DraftOrderLineItem/57455297691837"
                }
            ],
            "shipping_address": {
                "first_name": "Benedict",
                "address1": "4694 Monroe Gateway",
                "phone": "1-532-436-8221",
                "city": null,
                "zip": null,
                "province": null,
                "country": "United States",
                "last_name": "Farrell",
                "address2": null,
                "company": null,
                "latitude": null,
                "longitude": null,
                "name": "Benedict Farrell",
                "country_code": "US",
                "province_code": null
            },
            "billing_address": {
                "first_name": "Benedict",
                "address1": "4694 Monroe Gateway",
                "phone": "1-532-436-8221",
                "city": null,
                "zip": null,
                "province": null,
                "country": "United States",
                "last_name": "Farrell",
                "address2": null,
                "company": null,
                "latitude": null,
                "longitude": null,
                "name": "Benedict Farrell",
                "country_code": "US",
                "province_code": null
            },
            "invoice_url": "https://airbyte-integration-test.myshopify.com/58033176765/invoices/f5ed373bb6b92d984a936f22504a3f60",
            "applied_discount": null,
            "order_id": 3944225538237,
            "shipping_line": null,
            "tax_lines": [],
            "tags": "",
            "note_attributes": [],
            "total_price": "41.00",
            "subtotal_price": "41.00",
            "total_tax": "0.00",
            "admin_graphql_api_id": "gid://shopify/DraftOrder/876124897469",
            "customer": {
                "id": 5330782224573,
                "email": "[email protected]",
                "accepts_marketing": false,
                "created_at": "2021-06-22T23:05:08-07:00",
                "updated_at": "2021-07-07T08:18:58-07:00",
                "first_name": "Benedict",
                "last_name": "Farrell",
                "orders_count": 1,
                "state": "disabled",
                "total_spent": 41.0,
                "last_order_id": 3944225538237,
                "note": null,
                "verified_email": true,
                "multipass_identifier": null,
                "tax_exempt": false,
                "phone": null,
                "tags": "developer-tools-generator",
                "last_order_name": "#1009",
                "currency": "USD",
                "accepts_marketing_updated_at": "2021-06-22T23:05:08-07:00",
                "marketing_opt_in_level": null,
                "tax_exemptions": [],
                "admin_graphql_api_id": "gid://shopify/Customer/5330782224573",
                "default_address": {
                    "id": 6564640030909,
                    "customer_id": 5330782224573,
                    "first_name": "Benedict",
                    "last_name": "Farrell",
                    "company": null,
                    "address1": "4694 Monroe Gateway",
                    "address2": null,
                    "city": null,
                    "province": null,
                    "country": "United States",
                    "zip": null,
                    "phone": "1-532-436-8221",
                    "name": "Benedict Farrell",
                    "province_code": null,
                    "country_code": "US",
                    "country_name": "United States",
                    "default": true
                }
            }
        },
        "emitted_at": 1639056319000
    }
}

[mkhokh-33]

The root cause of this issue is not about root of NULL containing nested object itself it's about containing nested date-time field