Dados persistidos em staging (CDS e BQ) divergem do recebido em intake

Description

PRDE - Bug default text according to the team DoR (Definition of Ready)

01 - PERSON OF CONTACT (PERSON THAT CAN ANSWER QUESTIONS ABOUT THE PROBLEM):

@Danilo Queiroz Mota

02 - PROBLEM (WHAT'S THE ISSUE?):

Cliente reportou que dados em staging divergem do enviado pelo ERP.

SELECT timestamp_micros(mdmcounterforentity) as mdmcounterforentity, mdmauditId, DELETED
FROM 
`carol-985685f68ade47639304.985685f68ade476393049bec85e841da.stg_protheus_carol_se2`
WHERE protheus_pk = '01|0101|04|44558||NF|004228|01'
order by mdmcounterforentity

Dados em CDS também encontram-se divergentes.

SELECT timestamp_micros(mdmcounterforentity) as mdmcounterforentity, mdmauditId, DELETED
FROM 
`labs-app-mdm-production.985685f68ade476393049bec85e841da.ext_protheus_carol_se2`
WHERE protheus_pk = '01|0101|04|44558||NF|004228|01'
order by mdmcounterforentity

CREATE OR REPLACE EXTERNAL TABLE `labs-app-mdm-production.985685f68ade476393049bec85e841da.ext_protheus_carol_se2`
OPTIONS (
  uris = ['gs://prod-mdm-1-carol-internal-985685f68ade476393049bec85e841da/staging-output/parquet/9291250517d24616b2745fd84e147dcb_se2/*.parquet'],
  format = 'PARQUET'
);

Dados na auditoria refletem o que foi enviado pelo ERP.

SELECT timestamp_micros(CAST(counterForEntity AS INT64)) AS mdmcounterForEntity, auditId as mdmauditId, JSON_VALUE(record, '$.DELETED') AS DELETED,
  FROM (
    SELECT message_id AS messageId, auditId, tenantId, connectorId, stagingType, TIMESTAMP_TRUNC(publish_time, SECOND) AS record_timestamp,
      json_value(attributes, '$.counterForEntity') as counterForEntity,
      record, 
    FROM `labs-app-mdm-production.intake.records_landing` l
    CROSS JOIN UNNEST(JSON_EXTRACT_ARRAY(payload, '$')) AS record
    WHERE DATE(publish_time) = '2023-12-08'
    AND tenantId IN (
      "985685f68ade476393049bec85e841da"
    )
    AND stagingType = "se2"
  )
WHERE TRIM(JSON_VALUE(record, '$.protheus_pk')) = '01|0101|04|44558||NF|004228|01'
order by counterForEntity

03 - STEPS TO REPRODUCE (STEP (1...N), VIDEO, SCREENSHOTS, LOGS FOLDER, HEARTBEAT, ETC. – IF IS NOT POSSIBLE TO REPRODUCE EXPLAIN THE REASON):
04 - LINKS (ADD A LINK TO THE BUG OR TO THE TENANT):
05 - EXPECTED BEHAVIOR (LIST THE EXPECTED BEHAVIORS TO CONSIDER THIS BUG AS DONE):

Attachments

Activity

Show:

MARCOS STUMPF 31 January 2024, 15:51 • Jira Internal Users

@Robson Thanael Poffo Efetuei o vinculo entre

PRDE-3172:Dados persistidos em staging (CDS e BQ) divergem do recebido em intakeDone

CAPL-5136:Carol App Installation: allow to materialize the staging tables applying the ...Done

para ficar amarrado.

O KT sobre a validação final com o time DE está agendado para amanhã (1/fev).

Sobre o outro card mencionado pela Cindy (PRDE-3210) eu atrelei ele ao PX-03 que trata da observabilidade em geral.

Robson Thanael Poffo 31 January 2024, 13:13 • Jira Internal Users

@Cindy de Araujo Soares Moore , obrigado pelo retorno.

Pelo fato de estar inicialmente ligado à mudança de schema, vamos associar a solução do tema inicial com a issue

CAPL-5136:Carol App Installation: allow to materialize the staging tables applying the ...Done

A issue

CAPL-5136:Carol App Installation: allow to materialize the staging tables applying the ...Done

aplica uma nova forma que evita eliminação de atributos, evitando parte da mudança de schema em instalações de Carol App.

O @MARCOS STUMPF está acelerando as validações finais do tema.

Este tema está sendo aguardado através da issue:

PRDE-3172:Dados persistidos em staging (CDS e BQ) divergem do recebido em intakeDone

cc @Danilo Queiroz Mota

Cindy de Araujo Soares Moore 17 January 2024, 15:35 • Jira Internal Users

@Robson Thanael Poffo

Não conseguimos reproduzir esse cenário em ambiente de teste e analisando o código não encontramos o que poderia ter causado esse problema dos cases do nome da coluna. Mas concluímos que o problema foi causado pela mudança de schema no mesmo momento do envio do dado.

Vamos encerrar esse ticket com as evidências da mudança de schema já compartilhadas aqui anteriormente. Também reforço que os tickets abaixo vão ajudar a evitar o cenário que vimos aqui:

cc/ @Geny Isam Hamud Herrera @Gabriel DAmore Marciano

Automation for Jira 2 January 2024, 19:32 • Jira Internal Users

@Robson Thanael Poffo ,
@Geny Isam Hamud Herrera , @Reinaldo Oliveira Machado Junior , @Cindy de Araujo Soares Moore

This issue was planned to be delivered until 2024-01-22. You can check that by consulting the issue in the Due Date field.

Dates already planned for this issue: 2024-01-22, 2024-01-02

If External Issue Link field is filled, customer was also informed on JIRA TOTVS.

Cindy de Araujo Soares Moore 22 December 2023, 02:15 • Jira Internal Users

Eu criei um ticket para avaliação que pode ser interessante para investigações futuras:

PRDE-3210:[Dataflow] Keep history of schema.json file on GCPTo do

Cindy de Araujo Soares Moore 22 December 2023, 01:10 • Jira Internal Users

@Robson Thanael Poffo ainda um mistério. Pra mim tinha sido a alteração de caixa baixa pra caixa alta que supostamente não tinha ido. Mas não temos evidências disso. Fiz uns testes em sandbox e vi comportamentos diferentes a cada momento que repetia o teste. Suspeitava de algum problema no update de staging schema relacionado a isso. Mas eu analisei todo o código de update desse schema e não encontrei algo que deixasse atualizar a coluna pra caixa alta.

E sim, pro caso das outras colunas o outro ticket resolve. Minha preocupação é com as atualizações de caixa baixa e caixa alta.

Mas as outras colunas terem entrado como null comprova que o update da instalação tinha executado, antes do Dataflow processar o dado, o que sugere que a coluna DELETED não estava como esperando.

A partir de amanhã eu estou de férias, e o @Reinaldo Oliveira Machado Junior vai fazer novos testes pra esse cenário. No comentário anterior tentei agrupar o máximo de informação que consegui.

Robson Thanael Poffo 21 December 2023, 22:56 • Jira Internal Users

@Cindy de Araujo Soares Moore , entendi que as colunas E2_XE4COD, E2_XE4TIPO, and E2_XE4COND foram removidas e adicionadas na sequencia. E entendo ter perdido os dados por conta desta operação. Entendo que esse cenario é coberto na issue

CAPL-5136:Carol App Installation: allow to materialize the staging tables applying the ...Done

, certo?

Por qual motivo o valor de DELETED foi perdido?

Cindy de Araujo Soares Moore 21 December 2023, 22:01 • Jira Internal Users

There are some highlights to share about this case:

There are other column values in the record missing: DELETED, E2_XE4COD, E2_XE4TIPO, and E2_XE4COND are null.

The Carol app installation was triggered right before the record was sent. The staging tables were updated too close to the intake call sending the record:https://totvstechfin.carol.ai/tenant6cae3f457c3d11ebbe1f0a586460451f/carol-ui/tasks/activity/d357f658a352474893ed14f9a9f7145d?p=1&ps=25&sort=dateUpdated&order=DESC&filters=%5B%7B%22hideInternal%22:%22true%22%7D,%7B%22dateUpdated%22:%5B%22before%22,%222023-12-08T20:30:00%2B00:00%22%5D%7D,%7B%22taskType%22:%5B%22INSTALL_CAROL_APP%22,%22SCHEMA_CHANGED_NOTIFY_BIGQUERY%22%5D%7D%5D

Schema change notification triggered by Carol app installation removed three columns from the schema E2_XE4COD, E2_XE4TIPO, and E2_XE4COND: https://totvstechfin.carol.ai/tenant6cae3f457c3d11ebbe1f0a586460451f/carol-ui/tasks/activity/54173fc7888846f6ba2127f6ca2c6a91?p=1&ps=25&sort=dateUpdated&order=DESC&filters=%5B%7B%22hideInternal%22:%22false%22%7D,%7B%22taskType%22:%5B%22SCHEMA_CHANGED_NOTIFY_BIGQUERY%22%5D%7D,%7B%22dateCreated%22:%5B%22before%22,%222023-12-09T03:00:00%2B00:00%22%5D%7D%5D
Schema change notification triggered by flexible schema flag added the three columns back: https://totvstechfin.carol.ai/tenant6cae3f457c3d11ebbe1f0a586460451f/carol-ui/tasks/activity/ff4d606cc4a04004a071e0f1c0e5f3d0?p=1&ps=25&sort=dateUpdated&order=DESC&filters=%5B%7B%22hideInternal%22:%22false%22%7D,%7B%22taskType%22:%5B%22SCHEMA_CHANGED_NOTIFY_BIGQUERY%22%5D%7D,%7B%22dateCreated%22:%5B%22before%22,%222023-12-09T03:00:00%2B00:00%22%5D%7D%5D
Checking the differences between the app versions, 1.2.5 and 1.2.6, I noticed a change in the case of the column name, it was changed from deleted to DELETED. The staging Dataflow process could not recognize the column depending on the case the record is sent and the column name in the staging table.
Discussion about these findings: https://totvscarol.slack.com/archives/C03NT4US9J9/p1702911094836369?thread_ts=1702408172.329549&cid=C03NT4US9J9
The BigQuery tables never change the column name case, unless the table is recreated (new provision is a case): https://totvscarol.slack.com/archives/C02C1SARBDH/p1703190105682759
Testing app installation in sandbox the staging schema column wasn’t updated to DELETED in v1.2.6:

The endpoint to test sending the data to intake (record from landing table)

curl --location 'https://totvsladybug.qarol.ai/api/v2/staging/intake/se2?connectorId=636a981e205842959656b5f63ba26d10&returnData=true' \
--header 'Authorization: 8156d7fc00724b699d85234a386a3f78' \
--header 'Content-Type: application/json' \
--data '[
	{"E2_FILIAL":"0101","E2_PREFIXO":"04 ","E2_NUM":"44558    ","E2_PARCELA":"  ","E2_TIPO":"NF ","E2_NATUREZ":"20109     ","E2_PORTADO":"   ","E2_FORNECE":"004228","E2_LOJA":"01","E2_NOMFOR":"VENETO              ","E2_EMISSAO":"20231116","E2_VENCTO":"20231123","E2_VENCREA":"20231123","E2_VALOR":194.31,"E2_ISS":0,"E2_IRRF":0,"E2_NUMBCO":"               ","E2_INDICE":"   ","E2_BAIXA":"        ","E2_BCOPAG":"   ","E2_EMIS1":"20231122","E2_HIST":"                                                                                                                                                                                                                                                              ","E2_LA":"S","E2_LOTE":"    ","E2_MOTIVO":"                    ","E2_MOVIMEN":"        ","E2_OP":"              ","E2_SALDO":194.31,"E2_OK":"  ","E2_DESCONT":0,"E2_MULTA":0,"E2_JUROS":0,"E2_CORREC":0,"E2_VALLIQ":0,"E2_VENCORI":"20231207","E2_VALJUR":0,"E2_PORCJUR":0,"E2_MOEDA":1,"E2_NUMBOR":"      ","E2_FATPREF":"   ","E2_FATURA":"         ","E2_PROJETO":"      ","E2_CLASCON":"     ","E2_RATEIO":" ","E2_DTVARIA":"        ","E2_VARURV":0,"E2_VLCRUZ":194.31,"E2_DTFATUR":"        ","E2_ACRESC":0,"E2_TITORIG":"                                                  ","E2_IMPCHEQ":" ","E2_PARCIR":"  ","E2_ARQRAT":"                              ","E2_OCORREN":"  ","E2_ORIGEM":"MATA100 ","E2_IDENTEE":"      ","E2_FLUXO":"S","E2_PARCISS":"  ","E2_ORDPAGO":"      ","E2_DESDOBR":" ","E2_INSS":0,"E2_PARCINS":"  ","E2_NUMLIQ":"      ","E2_BCOCHQ":"   ","E2_AGECHQ":"     ","E2_CTACHQ":"          ","E2_DATALIB":"        ","E2_APROVA":"                    ","E2_TIPOFAT":"   ","E2_FLAGFAT":" ","E2_ANOBASE":"    ","E2_MESBASE":"  ","E2_TXMOEDA":0,"E2_SDACRES":0,"E2_DECRESC":0,"E2_SDDECRE":0,"E2_USUALIB":"                         ","E2_MULTNAT":" ","E2_NUMTIT":"                                                  ","E2_PROJPMS":" ","E2_PLLOTE":"          ","E2_DIRF":" ","E2_CODRET":"    ","E2_MODSPB":" ","E2_IDCNAB":"          ","E2_PARCCSS":"  ","E2_RETENC":0,"E2_CONTAD":"                    ","E2_CODORCA":"        ","E2_DOCHAB":"      ","E2_SEST":0,"E2_PARCSES":"  ","E2_FILDEB":"    ","E2_FILORIG":"0101","E2_FORNISS":"      ","E2_LOJAISS":"  ","E2_DEBITO":"                    ","E2_CCD":"         ","E2_ITEMD":"         ","E2_CLVLDB":"         ","E2_CREDIT":"                    ","E2_CCC":"         ","E2_ITEMC":"         ","E2_CLVLCR":"         ","E2_COFINS":0,"E2_PIS":0,"E2_CSLL":0,"E2_PARCCOF":"  ","E2_PARCPIS":"  ","E2_PARCSLL":"  ","E2_TITPIS":"                                                  ","E2_TITCOF":"                                                  ","E2_TITCSL":"                                                  ","E2_TITINS":"                                                  ","E2_VRETPIS":0,"E2_VRETCOF":0,"E2_VRETCSL":0,"E2_PRETPIS":" ","E2_PRETCOF":" ","E2_PRETCSL":" ","E2_SEQBX":"  ","E2_CODBAR":"                                            ","E2_LINDIG":"                                                ","E2_BASEPIS":0,"E2_BASECSL":0,"E2_VRETISS":0,"E2_VENCISS":"        ","E2_VBASISS":0,"E2_MDRTISS":"1","E2_VARIAC":0,"E2_PERIOD":" ","E2_MDCONTR":"               ","E2_MDREVIS":"   ","E2_MDPLANI":"      ","E2_MDCRON":"      ","E2_MDPARCE":"  ","E2_FRETISS":"1","E2_TXMDCOR":0,"E2_APLVLMN":"1","E2_CLEARIN":"   ","E2_HORASPB":"     ","E2_PRETIRF":" ","E2_SEFIP":" ","E2_TRETISS":" ","E2_VRETIRF":0,"E2_PLOPELT":"    ","E2_CODRDA":"      ","E2_PARCFET":"  ","E2_FETHAB":0,"E2_FORORI":"      ","E2_LOJORI":"  ","E2_STATUS":" ","E2_DTDIRF":"        ","E2_TITADT":"                                                  ","E2_TITPAI":"                                                  ","E2_INSSRET":0,"E2_CODAGL":"      ","E2_PROCPCC":"         ","E2_FORNPAI":"                                                  ","E2_CODISS":"      ","E2_USUASUS":"                         ","E2_USUACAN":"                         ","E2_DATASUS":"        ","E2_DATACAN":"        ","E2_LIMCAN":"        ","E2_PREOP":"      ","E2_DTAPUR":"        ","E2_NROREF":"               ","E2_NOMERET":"                                                            ","E2_TPINSC":" ","E2_FORBCO":"   ","E2_FCTADV":"  ","E2_FAGEDV":" ","E2_FORAGE":"     ","E2_FORCTA":"          ","E2_BASEISS":0,"E2_NUMPRO":"          ","E2_INDPRO":" ","E2_FIMP":" ","E2_RETCNTR":0,"E2_MDDESC":0,"E2_MDBONI":0,"E2_MDMULT":0,"E2_PRINSS":0,"E2_TPESOC":" ","E2_DATAAGE":"20231123","E2_RETINS":"    ","E2_CCUSTO":"         ","E2_FAMAD":0,"E2_FMPEQ":0,"E2_PARCFAM":"  ","E2_PARCFMP":"  ","E2_FORMPAG":"  ","E2_CIDE":0,"E2_PRETINS":"1","E2_BASEIRF":0,"E2_PARCAGL":"  ","E2_BASECOF":0,"E2_TEMDOCS":" ","E2_VRETINS":0,"E2_STATLIB":"  ","E2_CODAPRO":"      ","E2_IDMOV":"          ","E2_FATFOR":"      ","E2_FATLOJ":"  ","E2_DIACTB":"  ","E2_NODIA":"          ","E2_CLVL":"         ","E2_ITEMCTA":"         ","E2_NFELETR":"                    ","E2_MEDNUME":"      ","E2_BASEINS":0,"E2_CODOPE":"  ","E2_CNO":"      ","E2_PRISS":0,"E2_MSIDENT":"0000700252","E2_PARCFAC":"  ","E2_PARCFAB":"  ","E2_CNPJRET":"              ","E2_FACS":0,"E2_FABOV":0,"E2_CODINS":"    ","E2_PARIMP1":"  ","E2_PARIMP2":"  ","E2_PARIMP3":"  ","E2_PARIMP4":"  ","E2_PARIMP5":"  ","E2_PARCCID":"  ","E2_CODRPIS":"    ","E2_CODRCOF":"    ","E2_CODRCSL":"    ","E2_TIPOLIQ":"   ","E2_NUMSOL":"      ","E2_AGLIMP":"         ","E2_DTBORDE":"        ","E2_IDDARF":"                    ","E2_TPDESC":" ","R_E_C_N_O_":43489,"R_E_C_D_E_L_":0,"E2_BTRISS":0,"E2_CODSERV":"         ","E2_VRETBIS":0,"E2_XE4COD":"   ","E2_XE4COND":"                                        ","E2_XE4TIPO":" ","E2_PARCIMA":"  ","E2_IMA":0,"S_T_A_M_P_":"2023/12/08","I_N_S_D_T_":"2023/11/22","DELETED":false,"TIME_STAMP":"2023-12-08T20:12:13.847       ","INSERT_STAMP":"2023-11-22T15:06:29.530       ","company_group":"01","company":"01","unity":"","branch":"01","protheus_pk":"01|0101|04|44558||NF|004228|01","TIMESTAMP":"2023-12-08T20:12:13.847       ","INSERTSTAMP":"2023-11-22T15:06:29.530       ","Tenant":"6cae3f45-7c3d-11eb-be1f-0a586460451f","Organization":"807f3a4c37c22b87ebe092b2a3368f07","Created_At":"2023-11-22T15:06:29.530       ","Updated_At":"2023-12-08T20:12:13.847       ","Sent_At":"2023-12-08T20:12:42.340","ERP":"PROTHEUS"}
]'

Query returning the records in production:

SELECT timestamp_micros(mdmcounterforentity) as mdmcounterforentity, mdmauditId, DELETED, Deleted, mdmDeleted, mdmLastUpdated, _ingestionDatetime
FROM 
`carol-985685f68ade47639304.985685f68ade476393049bec85e841da.stg_protheus_carol_se2`
WHERE protheus_pk = '01|0101|04|44558||NF|004228|01'
order by mdmcounterforentity

Query getting landing records:

SELECT timestamp_micros(CAST(counterForEntity AS INT64)) AS mdmcounterForEntity, auditId as mdmauditId, JSON_VALUE(record, '$.DELETED') AS DELETED,
  FROM (
    SELECT message_id AS messageId, auditId, tenantId, connectorId, stagingType, TIMESTAMP_TRUNC(publish_time, SECOND) AS record_timestamp,
      json_value(attributes, '$.counterForEntity') as counterForEntity,
      record, 
    FROM `labs-app-mdm-production.intake.records_landing` 
    CROSS JOIN UNNEST(JSON_EXTRACT_ARRAY(payload, '$')) AS record
    WHERE DATE(publish_time) = '2023-12-08'
    AND tenantId IN (
      "985685f68ade476393049bec85e841da"
    )
    AND stagingType = "se2"
  )
WHERE TRIM(JSON_VALUE(record, '$.protheus_pk')) = '01|0101|04|44558||NF|004228|01'
order by counterForEntity

cc/ @Geny Isam Hamud Herrera @Reinaldo Oliveira Machado Junior

Automation for Jira 12 December 2023, 20:09 • Jira Internal Users

@Robson Thanael Poffo ,

@Geny Isam Hamud Herrera ,
This issue was planned to be delivered until 2024-01-02. You can check that by consulting the issue in the Due Date field.

Dates already planned for this issue: 2024-01-02

If External Issue Link field is filled, customer was also informed on JIRA TOTVS.

Automation for Jira 12 December 2023, 20:09 • Jira Internal Users

Message thread link on #red-phone channel:

https://totvscarol.slack.com/archives/C03NT4US9J9/p1702408172329549

Done

Details

Priority

Highest

Assignee

Cindy de Araujo Soares Moore

Reporter

Robson Thanael Poffo

Labels

CAROL_BACKLOG_REVIEWRED_PHONE

Due Date

22 January 2024

Fix versions

CAPL_3.98

Components

CONNECTOR | DATA_INGESTION

Created

12 December 2023, 16:08

Updated

31 January 2024, 11:51

More fields

Created: 12 December 2023, 20:08 Updated: 31 January 2024, 15:51

Dados persistidos em staging (CDS e BQ) divergem do recebido em intake

Description

Attachments

Activity

Details

Priority

Assignee

Reporter

Labels

Due Date

Fix versions

Components

Created

Updated

More fields

Original estimate

Time tracking

Affects versions

Rest of custom fields

Delete this comment?

Delete this attachment?

Dados persistidos em staging (CDS e BQ) divergem do recebido em intake

Description

Attachments

Activity

Details

Priority

Assignee

Reporter

Labels

Due Date

Fix versions

Components

Created

Updated

More fields

Original estimate

Time tracking

Affects versions

Rest of custom fields