[Source Stripe] Syntax error at or near "authorization" during normalization

[MaximilianSoerenPollak]

Hello there,

so I'm recently trying to get a Stripe connector to work. It's on the newest version currently, but the connector always fails with one of three issues.
These are:
message='activity Heartbeat timeout', timeoutType=TIMEOUT_TYPE_HEARTBEAT...
syntax error at or near "authorization" LINE 32: add column authorization text,...
syntax error at or near "." LINE 52: drop column customer.call_report__all_conver...

I'm not sure I can influence any of these as we have no custom transformation beyond the normalization that Airbyte offers.

I have already tried to completely delete the corresponding tables in the destination Table to have a 'clean' slate for it to replicate to, but that also did not help as the same errors happened again.

Also tried was to completely reset the connector -> i.e. make a new one and delete old one, also no change in the outcome sadly.

Did anybody encounter these issues, and if so could you fix them?

Thanks for your help and advice

[sajarin]

hey @MaximilianSoerenPollak, anyway you can post more of the loglines?

What is the destination you are using for this sync? It seems like there is an issue with some datatypes coming from one of the stripe streams that is causing an exception to be raised in the destination, which then causes the worker to timeout, which causes temporal to stop the sync and report a failure.

[MaximilianSoerenPollak]

Hi @sajarin sorry I was a bit busy the last couple days.

Yes I can post some of the log lines here.
The destination we use for this is 'Postgres'.

Anyway, here is some more log lines of the connector failing with the error message:
Failure reason: Normalization failed during the dbt run. This may indicate a problem with the data itself.

2023-11-22 23:44:03 normalization > Connection:
2023-11-22 23:44:03 normalization >   host: localhost
2023-11-22 23:44:03 normalization >   port: 5432
2023-11-22 23:44:03 normalization >   user: airbyte
2023-11-22 23:44:03 normalization >   database: postgres
2023-11-22 23:44:03 normalization >   schema: public
2023-11-22 23:44:03 normalization >   search_path: None
2023-11-22 23:44:03 normalization >   keepalives_idle: 0
2023-11-22 23:44:03 normalization >   sslmode: allow
2023-11-22 23:44:03 normalization >   Connection test: [OK connection ok]
2023-11-22 23:44:03 INFO i.a.w.n.NormalizationAirbyteStreamFactory(filterOutAndHandleNonAirbyteMessageLines):104 - 
2023-11-22 23:44:03 normalization > All checks passed!
2023-11-22 23:44:03 INFO i.a.w.n.NormalizationAirbyteStreamFactory(filterOutAndHandleNonAirbyteMessageLines):104 - 
2023-11-22 23:44:03 normalization > Forward dbt output logs to diagnose/debug errors (0):
2023-11-22 23:44:03 INFO i.a.w.n.NormalizationAirbyteStreamFactory(filterOutAndHandleNonAirbyteMessageLines):104 - 
2023-11-22 23:44:03 normalization > 499 of 519 START incremental model public.authorizations_card___verification_document................................... [RUN]
2023-11-22 23:44:03 normalization > 499 of 519 OK created incremental model public.authorizations_card___verification_document.............................. [INSERT 0 0 in 0.31s]
2023-11-22 23:44:03 normalization > 500 of 519 START incremental model public.payment_intents_last___debit_generated_from................................... [RUN]
2023-11-22 23:44:03 normalization > 500 of 519 OK created incremental model public.payment_intents_last___debit_generated_from.............................. [INSERT 0 0 in 0.27s]
2023-11-22 23:44:03 normalization > 501 of 519 START incremental model public.payment_intents_last__lling_details_address................................... [RUN]
2023-11-22 23:44:03 normalization > 493 of 519 OK created incremental model public.charges_payment_meth__ard_installments_plan.............................. [INSERT 0 0 in 3.51s]
2023-11-22 23:44:03 normalization > 487 of 519 OK created incremental model public.payment_intents_last__r_payment_method_card.............................. [INSERT 0 0 in 5.69s]
2023-11-22 23:44:03 normalization > 488 of 519 OK created incremental model public.payment_intents_last__payment_method_sofort.............................. [INSERT 0 0 in 5.74s]
2023-11-22 23:44:03 normalization > 502 of 519 START incremental model public.payment_intents_last___three_d_secure_usage................................... [RUN]
2023-11-22 23:44:03 normalization > 503 of 519 START incremental model public.payment_intents_last__d_card_generated_from................................... [RUN]
2023-11-22 23:44:03 normalization > 504 of 519 START incremental model public.payment_intents_last___method_card_networks................................... [RUN]
2023-11-22 23:44:03 normalization > 489 of 519 OK created incremental model public.payment_intents_last__payment_method_boleto.............................. [INSERT 0 0 in 6.49s]
2023-11-22 23:44:03 normalization > 505 of 519 START incremental model public.payment_intents_last__nt_method_card_checks................................... [RUN]
2023-11-22 23:44:03 normalization > 491 of 519 OK created incremental model public.payment_intents_last__or_payment_method_p24.............................. [INSERT 0 0 in 5.86s]
2023-11-22 23:44:03 normalization > 506 of 519 START incremental model public.payment_intents_last__nt_method_card_wallet................................... [RUN]
2023-11-22 23:44:03 normalization > 497 of 519 OK created incremental model public.charges_payment_meth__ard_wallet_masterpass.............................. [INSERT 0 0 in 4.10s]
2023-11-22 23:44:03 normalization > 498 of 519 OK created incremental model public.charges_payment_meth___wallet_visa_checkout.............................. [INSERT 0 0 in 4.04s]
2023-11-22 23:44:03 normalization > 507 of 519 START incremental model public.charges_payment_meth__pass_shipping_address................................... [RUN]
2023-11-22 23:44:03 normalization > 508 of 519 START incremental model public.charges_payment_meth__rpass_billing_address................................... [RUN]
2023-11-22 23:44:03 normalization > 507 of 519 OK created incremental model public.charges_payment_meth__pass_shipping_address.............................. [INSERT 0 0 in 0.47s]
2023-11-22 23:44:03 normalization > 509 of 519 START incremental model public.charges_payment_meth__ckout_billing_address................................... [RUN]
2023-11-22 23:44:03 normalization > 508 of 519 OK created incremental model public.charges_payment_meth__rpass_billing_address.............................. [INSERT 0 0 in 0.50s]
2023-11-22 23:44:03 normalization > 510 of 519 START incremental model public.charges_payment_meth__kout_shipping_address................................... [RUN]
2023-11-22 23:44:03 normalization > 509 of 519 OK created incremental model public.charges_payment_meth__ckout_billing_address.............................. [INSERT 0 0 in 0.48s]
2023-11-22 23:44:03 normalization > 510 of 519 OK created incremental model public.charges_payment_meth__kout_shipping_address.............................. [INSERT 0 0 in 0.45s]
2023-11-22 23:44:03 normalization > 501 of 519 OK created incremental model public.payment_intents_last__lling_details_address.............................. [INSERT 0 0 in 5.12s]
2023-11-22 23:44:03 normalization > 502 of 519 OK created incremental model public.payment_intents_last___three_d_secure_usage.............................. [INSERT 0 0 in 4.11s]
2023-11-22 23:44:03 normalization > 503 of 519 OK created incremental model public.payment_intents_last__d_card_generated_from.............................. [INSERT 0 0 in 4.12s]
2023-11-22 23:44:03 normalization > 504 of 519 OK created incremental model public.payment_intents_last___method_card_networks.............................. [INSERT 0 0 in 4.00s]
2023-11-22 23:44:03 normalization > 505 of 519 OK created incremental model public.payment_intents_last__nt_method_card_checks.............................. [INSERT 0 0 in 3.75s]
2023-11-22 23:44:03 normalization > 506 of 519 OK created incremental model public.payment_intents_last__nt_method_card_wallet.............................. [INSERT 0 0 in 3.48s]
2023-11-22 23:44:03 normalization > 511 of 519 START incremental model public.payment_intents_last__ayment_method_details................................... [RUN]
2023-11-22 23:44:03 normalization > 512 of 519 START incremental model public.payment_intents_last___wallet_visa_checkout................................... [RUN]
2023-11-22 23:44:03 normalization > 513 of 519 START incremental model public.payment_intents_last__ard_wallet_masterpass................................... [RUN]
2023-11-22 23:44:03 normalization > 511 of 519 OK created incremental model public.payment_intents_last__ayment_method_details.............................. [INSERT 0 0 in 2.73s]
2023-11-22 23:44:03 normalization > 512 of 519 OK created incremental model public.payment_intents_last___wallet_visa_checkout.............................. [INSERT 0 0 in 2.71s]
2023-11-22 23:44:03 normalization > 513 of 519 OK created incremental model public.payment_intents_last__ard_wallet_masterpass.............................. [INSERT 0 0 in 2.61s]
2023-11-22 23:44:03 normalization > 514 of 519 START incremental model public.payment_intents_last___details_card_present................................... [RUN]
2023-11-22 23:44:03 normalization > 515 of 519 START incremental model public.payment_intents_last__kout_shipping_address................................... [RUN]
2023-11-22 23:44:03 normalization > 516 of 519 START incremental model public.payment_intents_last__rpass_billing_address................................... [RUN]
2023-11-22 23:44:03 normalization > 517 of 519 START incremental model public.payment_intents_last__ckout_billing_address................................... [RUN]
2023-11-22 23:44:03 normalization > 518 of 519 START incremental model public.payment_intents_last__pass_shipping_address................................... [RUN]
2023-11-22 23:44:03 normalization > 514 of 519 OK created incremental model public.payment_intents_last___details_card_present.............................. [INSERT 0 0 in 1.66s]
2023-11-22 23:44:03 normalization > 518 of 519 OK created incremental model public.payment_intents_last__pass_shipping_address.............................. [INSERT 0 0 in 1.29s]
2023-11-22 23:44:03 normalization > 517 of 519 OK created incremental model public.payment_intents_last__ckout_billing_address.............................. [INSERT 0 0 in 1.34s]
2023-11-22 23:44:03 normalization > 515 of 519 OK created incremental model public.payment_intents_last__kout_shipping_address.............................. [INSERT 0 0 in 1.39s]
2023-11-22 23:44:03 normalization > 516 of 519 OK created incremental model public.payment_intents_last__rpass_billing_address.............................. [INSERT 0 0 in 1.40s]
2023-11-22 23:44:03 normalization > 519 of 519 START incremental model public.payment_intents_last___card_present_receipt................................... [RUN]
2023-11-22 23:44:03 normalization > 519 of 519 OK created incremental model public.payment_intents_last___card_present_receipt.............................. [INSERT 0 0 in 0.24s]
2023-11-22 23:44:03 normalization > Finished running 519 incremental models in 222.08s.
2023-11-22 23:44:03 normalization > Completed with 3 errors and 0 warnings:
2023-11-22 23:44:03 normalization > Database Error in model transactions_stg (models/generated/airbyte_incremental/public/transactions_stg.sql)
2023-11-22 23:44:03 normalization >   syntax error at or near "authorization"
2023-11-22 23:44:03 normalization >   LINE 32:                add column authorization text,
2023-11-22 23:44:03 normalization >                                      ^
2023-11-22 23:44:03 normalization > Database Error in model accounts (models/generated/airbyte_incremental/public/accounts.sql)
2023-11-22 23:44:03 normalization >   syntax error at or near "."
2023-11-22 23:44:03 normalization >   LINE 52:                 drop column customer.call_report__all_conver...
2023-11-22 23:44:03 normalization >                                                ^
2023-11-22 23:44:03 normalization > Database Error in model subscription_schedul__voice_items_tax_rates (models/generated/airbyte_incremental/public/subscription_schedul__voice_items_tax_rates.sql)
2023-11-22 23:44:03 normalization >   column "created__dbt_alter" is of type bigint but expression is of type timestamp with time zone
2023-11-22 23:44:03 normalization >   LINE 4: ...voice_items_tax_rates" set "created__dbt_alter" = "created";
2023-11-22 23:44:03 normalization >                                                                ^
2023-11-22 23:44:03 normalization >   HINT:  You will need to rewrite or cast the expression.
2023-11-22 23:44:03 normalization > Done. PASS=474 WARN=0 ERROR=3 SKIP=42 TOTAL=519
2023-11-22 23:44:04 INFO i.a.w.n.DefaultNormalizationRunner(close):207 - Terminating normalization process...
2023-11-22 23:44:04 ERROR i.a.w.g.DefaultNormalizationWorker(run):91 - Normalization failed for job 471.
io.airbyte.workers.exception.WorkerException: Normalization process did not terminate normally (exit code: 1)
	at io.airbyte.workers.normalization.DefaultNormalizationRunner.close(DefaultNormalizationRunner.java:218) ~[io.airbyte-airbyte-commons-worker-0.50.30.jar:?]
	at io.airbyte.workers.general.DefaultNormalizationWorker.run(DefaultNormalizationWorker.java:89) ~[io.airbyte-airbyte-commons-worker-0.50.30.jar:?]
	at io.airbyte.workers.general.DefaultNormalizationWorker.run(DefaultNormalizationWorker.java:38) ~[io.airbyte-airbyte-commons-worker-0.50.30.jar:?]
	at io.airbyte.workers.temporal.TemporalAttemptExecution.get(TemporalAttemptExecution.java:135) ~[io.airbyte-airbyte-workers-0.50.30.jar:?]
	at io.airbyte.workers.temporal.sync.NormalizationActivityImpl.lambda$normalize$2(NormalizationActivityImpl.java:175) ~[io.airbyte-airbyte-workers-0.50.30.jar:?]
	at io.airbyte.commons.temporal.TemporalUtils.withBackgroundHeartbeat(TemporalUtils.java:314) ~[io.airbyte-airbyte-commons-temporal-0.50.30.jar:?]
	at io.airbyte.workers.temporal.sync.NormalizationActivityImpl.normalize(NormalizationActivityImpl.java:124) ~[io.airbyte-airbyte-workers-0.50.30.jar:?]
	at jdk.internal.reflect.DirectMethodHandleAccessor.invoke(DirectMethodHandleAccessor.java:104) ~[?:?]
	at java.lang.reflect.Method.invoke(Method.java:578) ~[?:?]
	at io.temporal.internal.activity.RootActivityInboundCallsInterceptor$POJOActivityInboundCallsInterceptor.executeActivity(RootActivityInboundCallsInterceptor.java:64) ~[temporal-sdk-1.17.0.jar:?]
	at io.temporal.internal.activity.RootActivityInboundCallsInterceptor.execute(RootActivityInboundCallsInterceptor.java:43) ~[temporal-sdk-1.17.0.jar:?]
	at io.temporal.internal.activity.ActivityTaskExecutors$BaseActivityTaskExecutor.execute(ActivityTaskExecutors.java:95) ~[temporal-sdk-1.17.0.jar:?]
	at io.temporal.internal.activity.ActivityTaskHandlerImpl.handle(ActivityTaskHandlerImpl.java:92) ~[temporal-sdk-1.17.0.jar:?]
	at io.temporal.internal.worker.ActivityWorker$TaskHandlerImpl.handleActivity(ActivityWorker.java:241) ~[temporal-sdk-1.17.0.jar:?]
	at io.temporal.internal.worker.ActivityWorker$TaskHandlerImpl.handle(ActivityWorker.java:206) ~[temporal-sdk-1.17.0.jar:?]
	at io.temporal.internal.worker.ActivityWorker$TaskHandlerImpl.handle(ActivityWorker.java:179) ~[temporal-sdk-1.17.0.jar:?]
	at io.temporal.internal.worker.PollTaskExecutor.lambda$process$0(PollTaskExecutor.java:93) ~[temporal-sdk-1.17.0.jar:?]
	at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1144) ~[?:?]
	at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:642) ~[?:?]
	at java.lang.Thread.run(Thread.java:1589) ~[?:?]
2023-11-22 23:44:04 INFO i.a.w.g.DefaultNormalizationWorker(run):102 - Normalization executed in 5 minutes 3 seconds for job 471.
2023-11-22 23:44:04 INFO i.a.w.g.DefaultNormalizationWorker(run):114 - Normalization summary: io.airbyte.config.NormalizationSummary@29ccfc69[startTime=1700696340746,endTime=1700696644296,failures=[io.airbyte.config.FailureReason@7d98606[failureOrigin=normalization,failureType=system_error,internalMessage=syntax error at or near "authorization"
LINE 32:                add column authorization text,,externalMessage=Normalization failed during the dbt run. This may indicate a problem with the data itself.,metadata=io.airbyte.config.Metadata@5f1f97a1[additionalProperties={attemptNumber=11, jobId=471, from_trace_message=true}],stacktrace=AirbyteDbtError: 
35 of 519 ERROR creating incremental model _airbyte_public.transactions_stg............................................. [ERROR in 2.03s]
43 of 519 ERROR creating incremental model public.accounts.............................................................. [ERROR in 1.90s]
462 of 519 ERROR creating incremental model public.subscription_schedul__voice_items_tax_rates.......................... [ERROR in 1.01s]
Database Error in model transactions_stg (models/generated/airbyte_incremental/public/transactions_stg.sql)
  syntax error at or near "authorization"
  LINE 32:                add column authorization text,
                                     ^
Database Error in model accounts (models/generated/airbyte_incremental/public/accounts.sql)
  syntax error at or near "."
  LINE 52:                 drop column customer.call_report__all_conver...
                                               ^
Database Error in model subscription_schedul__voice_items_tax_rates (models/generated/airbyte_incremental/public/subscription_schedul__voice_items_tax_rates.sql)
  column "created__dbt_alter" is of type bigint but expression is of type timestamp with time zone
  LINE 4: ...voice_items_tax_rates" set "created__dbt_alter" = "created";
                                                               ^
  HINT:  You will need to rewrite or cast the expression.
Database Error in model transactions_stg (models/generated/airbyte_incremental/public/transactions_stg.sql)
  syntax error at or near "authorization"
  LINE 32:                add column authorization text,
                                     ^
Database Error in model accounts (models/generated/airbyte_incremental/public/accounts.sql)
  syntax error at or near "."
  LINE 52:                 drop column customer.call_report__all_conver...
                                               ^
Database Error in model subscription_schedul__voice_items_tax_rates (models/generated/airbyte_incremental/public/subscription_schedul__voice_items_tax_rates.sql)
  column "created__dbt_alter" is of type bigint but expression is of type timestamp with time zone
  LINE 4: ...voice_items_tax_rates" set "created__dbt_alter" = "created";
                                                               ^
  HINT:  You will need to rewrite or cast the expression.,retryable=<null>,timestamp=1700696644243,additionalProperties={}], io.airbyte.config.FailureReason@57a515da[failureOrigin=normalization,failureType=system_error,internalMessage=syntax error at or near "authorization"
LINE 32:                add column authorization text,,externalMessage=Normalization failed during the dbt run. This may indicate a problem with the data itself.,metadata=io.airbyte.config.Metadata@19c484f3[additionalProperties={attemptNumber=11, jobId=471, from_trace_message=true}],stacktrace=AirbyteDbtError: 
35 of 519 ERROR creating incremental model _airbyte_public.transactions_stg............................................. [ERROR in 2.03s]
43 of 519 ERROR creating incremental model public.accounts.............................................................. [ERROR in 1.90s]
462 of 519 ERROR creating incremental model public.subscription_schedul__voice_items_tax_rates.......................... [ERROR in 1.01s]
Database Error in model transactions_stg (models/generated/airbyte_incremental/public/transactions_stg.sql)
  syntax error at or near "authorization"
  LINE 32:                add column authorization text,
                                     ^
Database Error in model accounts (models/generated/airbyte_incremental/public/accounts.sql)
  syntax error at or near "."
  LINE 52:                 drop column customer.call_report__all_conver...
                                               ^
Database Error in model subscription_schedul__voice_items_tax_rates (models/generated/airbyte_incremental/public/subscription_schedul__voice_items_tax_rates.sql)
  column "created__dbt_alter" is of type bigint but expression is of type timestamp with time zone
  LINE 4: ...voice_items_tax_rates" set "created__dbt_alter" = "created";
                                                               ^
  HINT:  You will need to rewrite or cast the expression.
Database Error in model transactions_stg (models/generated/airbyte_incremental/public/transactions_stg.sql)
  syntax error at or near "authorization"
  LINE 32:                add column authorization text,
                                     ^
Database Error in model accounts (models/generated/airbyte_incremental/public/accounts.sql)
  syntax error at or near "."
  LINE 52:                 drop column customer.call_report__all_conver...
                                               ^
Database Error in model subscription_schedul__voice_items_tax_rates (models/generated/airbyte_incremental/public/subscription_schedul__voice_items_tax_rates.sql)
  column "created__dbt_alter" is of type bigint but expression is of type timestamp with time zone
  LINE 4: ...voice_items_tax_rates" set "created__dbt_alter" = "created";
                                                               ^
  HINT:  You will need to rewrite or cast the expression.,retryable=<null>,timestamp=1700696644243,additionalProperties={}]],additionalProperties={}]
2023-11-22 23:44:04 INFO i.a.c.i.LineGobbler(voidCall):149 - 
2023-11-22 23:44:04 INFO i.a.c.i.LineGobbler(voidCall):149 - ----- END DEFAULT NORMALIZATION -----
2023-11-22 23:44:04 INFO i.a.c.i.LineGobbler(voidCall):149 - 
2023-11-22 23:44:06 INFO i.a.w.t.s.a.AppendToAttemptLogActivityImpl(log):56 - Retry State: RetryManager(completeFailureBackoffPolicy=BackoffPolicy(minInterval=PT10S, maxInterval=PT30M, base=3), partialFailureBackoffPolicy=null, successiveCompleteFailureLimit=5, totalCompleteFailureLimit=10, successivePartialFailureLimit=1000, totalPartialFailureLimit=10, successiveCompleteFailures=0, totalCompleteFailures=2, successivePartialFailures=1, totalPartialFailures=10)
 Backoff before next attempt: 0 seconds
2023-11-22 23:44:06 ERROR i.a.w.t.s.a.AppendToAttemptLogActivityImpl(log):54 - Failing job: 471, reason: Job failed after too many retries for connection 61e0b6da-7cd3-4746-ac2a-bf791c7cca14

I also added the whole as a file here.
Hope that helps.
Let me know if I can do anything more to help triage this issue.

I appreciate your help a lot.
6e493a4a_53c8_41e5_b5c9_d04990f157f0_job_471_attempt_12_txt.txt

[MaximilianSoerenPollak]

Short update.

I have updated the stripe connector to the newest version again, but the issue still seems to be persisting.

[marcosmarxm]

This is a problem with the destination problem. What happens if you disable the transaction stream?
Did you try to sync the source to another destination?

[MaximilianSoerenPollak]

@marcosmarxm I have not tried a different destination. This is the only Database we have, where we bring together the whole information from different resources.
I will try disabling streams and then redo the sync and report on the outcome then.

[marcosmarxm]

Ok, let me know if you have any further information.

[MaximilianSoerenPollak]

So an update on this.
I have tried disabling the 'transaction' Stream but the outcome is still the same.
I'm a bit out of my depth now on what else to try.

Any other further ideas what might be doable here to triangulage the issue?