Destination Redshift: connection fails in normalization due to syntax error (field named `boolean`) using Typeform

[asharov]

Environment

• Airbyte version: 0.40.23
• OS Version / Instance: AWS EC2 Amazon Linux
• Deployment: docker-compose
• Source Connector and version: Typeform 0.1.9
• Destination Connector and version: Redshift 0.3.51
• Step where error happened: Sync job

Current Behavior

After setting up the connection from Typeform to Redshift, the sync fails with "Failure Origin: normalization, Message: Normalization failed during the dbt run. This may indicate a problem with the data itself." The details show

Database Error in model responses_answers (models/generated/airbyte_incremental/airbyte_jaakko/responses_answers.sql)
  syntax error at or near ","
  LINE 101:     boolean,
                       ^
  compiled SQL at ../build/run/airbyte_utils/models/generated/airbyte_incremental/airbyte_jaakko/responses_answers.sql

It seems to me that Airbyte is trying to create a table with dbt in a way that is not correct Redshift syntax (I had no problems with a Postgres destination).

Expected Behavior

The sync should complete and I should get my Typeform data into Redshift.

Logs

8f6266dc_fa9c_4da8_8172_3c840432455e_logs_1.txt

Steps to Reproduce

1. Create a Typeform source
2. Create a Redshift destination
3. Create a connection from Typeform to Redshift
4. Run sync

Are you willing to submit a PR?

I don't have the time now to learn enough of this system to be able to do that.

[marcosmarxm]

@asharov would be valuable to extract the sql file generated.
Something like:

docker exec -ti airbyte-server bash
cd /tmp/workspace/1/0/normalize
cat models/generated/airbyte_incremental/airbyte_jaakko/responses_answers.sql

[asharov]

Hi @marcosmarxm here are the contents of that file

{{ config(
    sort = "_airbyte_emitted_at",
    schema = "airbyte_jaakko",
    tags = [ "nested" ]
) }}
-- Final base SQL model
-- depends_on: {{ ref('responses_answers_ab3') }}
select
    _airbyte_responses_hashid,
    url,
    date,
    text,
    type,
    email,
    field,
    choice,
    number,
    boolean,
    choices,
    payment,
    file_url,
    phone_number,
    _airbyte_ab_id,
    _airbyte_emitted_at,
    {{ current_timestamp() }} as _airbyte_normalized_at,
    _airbyte_answers_hashid
from {{ ref('responses_answers_ab3') }}
-- answers at responses/answers from {{ ref('responses_scd') }}
where 1 = 1
{{ incremental_clause('_airbyte_emitted_at', this) }}

[marcosmarxm]

Sorry I asked the wrong one:

docker exec -ti airbyte-server bash
cd /tmp/workspace/1/0/
cat build/run/airbyte_utils/models/generated/airbyte_incremental/airbyte_jaakko/responses_answers.sql

[asharov]

Here's that file

      

  create  table
    "database".airbyte_jaakko."responses_answers"
    
    
      compound sortkey(_airbyte_emitted_at)
    
  as (
    
with __dbt__cte__responses_answers_ab1 as (

-- SQL model to parse JSON blob stored in a single column and extract into separated field columns as described by the JSON Schema
-- depends_on: "database".airbyte_jaakko."responses_scd"

    with joined as (
            select
                table_alias._airbyte_responses_hashid as _airbyte_hashid,
                _airbyte_nested_data
            from "database".airbyte_jaakko."responses_scd" as table_alias, table_alias.answers as _airbyte_nested_data
        )
select
    _airbyte_responses_hashid,
    case when _airbyte_nested_data."url" != '' then _airbyte_nested_data."url" end as url,
    case when _airbyte_nested_data."date" != '' then _airbyte_nested_data."date" end as date,
    case when _airbyte_nested_data."text" != '' then _airbyte_nested_data."text" end as text,
    case when _airbyte_nested_data."type" != '' then _airbyte_nested_data."type" end as type,
    case when _airbyte_nested_data."email" != '' then _airbyte_nested_data."email" end as email,
    case when _airbyte_nested_data."field" != '' then _airbyte_nested_data."field" end as field,
    case when _airbyte_nested_data."choice" != '' then _airbyte_nested_data."choice" end as choice,
    case when _airbyte_nested_data."number" != '' then _airbyte_nested_data."number" end as number,
    case when _airbyte_nested_data."boolean" != '' then _airbyte_nested_data."boolean" end as boolean,
    case when _airbyte_nested_data."choices" != '' then _airbyte_nested_data."choices" end as choices,
    case when _airbyte_nested_data."payment" != '' then _airbyte_nested_data."payment" end as payment,
    case when _airbyte_nested_data."file_url" != '' then _airbyte_nested_data."file_url" end as file_url,
    case when _airbyte_nested_data."phone_number" != '' then _airbyte_nested_data."phone_number" end as phone_number,
    _airbyte_ab_id,
    _airbyte_emitted_at,
    getdate() as _airbyte_normalized_at
from "database".airbyte_jaakko."responses_scd" as table_alias
-- answers at responses/answers
left join joined on _airbyte_responses_hashid = joined._airbyte_hashid
where 1 = 1
and answers is not null

),  __dbt__cte__responses_answers_ab2 as (

-- SQL model to cast each column to its adequate SQL type converted from the JSON schema type
-- depends_on: __dbt__cte__responses_answers_ab1
select
    _airbyte_responses_hashid,
    cast(url as text) as url,
    cast(nullif(date::varchar, '') as 
    TIMESTAMPTZ
) as date,
    cast(text as text) as text,
    cast(type as text) as type,
    cast(email as text) as email,
    cast(field as super) as field,
    cast(choice as super) as choice,
    cast(number as 
    float
) as number,
    cast(boolean as boolean) as boolean,
    cast(choices as super) as choices,
    cast(payment as super) as payment,
    cast(file_url as text) as file_url,
    cast(phone_number as text) as phone_number,
    _airbyte_ab_id,
    _airbyte_emitted_at,
    getdate() as _airbyte_normalized_at
from __dbt__cte__responses_answers_ab1
-- answers at responses/answers
where 1 = 1

),  __dbt__cte__responses_answers_ab3 as (

-- SQL model to build a hash column based on the values of this record
-- depends_on: __dbt__cte__responses_answers_ab2
select
    md5(cast(coalesce(cast(_airbyte_responses_hashid as text), '') || '-' || coalesce(cast(url as text), '') || '-' || coalesce(cast(date as text), '') || '-' || coalesce(cast(text as text), '') || '-' || coalesce(cast(type as text), '') || '-' || coalesce(cast(email as text), '') || '-' || coalesce(cast(json_serialize(field) as text), '') || '-' || coalesce(cast(json_serialize(choice) as text), '') || '-' || coalesce(cast(number as text), '') || '-' || coalesce(cast(case when boolean then 'true' else 'false' end as text), '') || '-' || coalesce(cast(json_serialize(choices) as text), '') || '-' || coalesce(cast(json_serialize(payment) as text), '') || '-' || coalesce(cast(file_url as text), '') || '-' || coalesce(cast(phone_number as text), '') as text)) as _airbyte_answers_hashid,
    tmp.*
from __dbt__cte__responses_answers_ab2 tmp
-- answers at responses/answers
where 1 = 1

)-- Final base SQL model
-- depends_on: __dbt__cte__responses_answers_ab3
select
    _airbyte_responses_hashid,
    url,
    date,
    text,
    type,
    email,
    field,
    choice,
    number,
    boolean,
    choices,
    payment,
    file_url,
    phone_number,
    _airbyte_ab_id,
    _airbyte_emitted_at,
    getdate() as _airbyte_normalized_at,
    _airbyte_answers_hashid
from __dbt__cte__responses_answers_ab3
-- answers at responses/answers from "database".airbyte_jaakko."responses_scd"
where 1 = 1

  );

[marcosmarxm]

@asharov there is a field in your report called boolean and this is breaking the dbt step.

The solution is to add boolean to reserved words in Redshift.

airbyte/airbyte-integrations/bases/base-normalization/normalization/transform_catalog/reserved_keywords.py

Lines 111 to 129 in 3532931

	REDSHIFT = {
	"AES128",
	"AES256",
	"ALL",
	"ALLOWOVERWRITE",
	"ANALYSE",
	"ANALYZE",
	"AND",
	"ANY",
	"ARRAY",
	"AS",
	"ASC",
	"AUTHORIZATION",
	"AZ64",
	"BACKUP",
	"BETWEEN",
	"BINARY",
	"BLANKSASNULL",
	"BOTH",