[SQL Processing] Tenant is somehow linked to a not installed app table not found on fanout

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?):

Tenant https://totvstechfin.carol.ai/tenant86fc4dd6207011eb96d80a586460278d/carol-ui/home behaviors like having an app installed.

The tenant doesn’t have the Carol App’s data models.

The app appears as deployed at Unified Tenant.

The tenant was linked to the unified tenant and then unlinked. The data was copied remains.
https://totvstechfindev.carol.ai/antecipaprotheusunif/carol-ui/tasks/activity/c27d5ff63f8045459e82d3626f864174

Another case:

Unif Antecipa failing a task: https://totvstechfindev.carol.ai/antecipaprotheusunif/carol-ui/tasks/activity/dbdd5d602a2347faa27b93494998f4c6?p=1&ps=25&sort=dateUpdated&order=DESC&filters=%5B%7B%22hideInternal%22:%22false%22%7D%5D

It says the tennat “3499b3a915074ecf8301a7352885056c“ (tenant86fc4dd6207011eb96d80a586460278d) does not have a specific table.

The tneant does not have to have this app installed:

https://totvstechfin.carol.ai/tenant86fc4dd6207011eb96d80a586460278d/carol-ui/home

Checked the tenant’s document, it has relation only to painel unified tenant:

{
  "mdmAllowSmsLogin": false,
  "mdmAllowSmsUsage": false,
  "mdmAvroSchemaProvisionStatusType": "UPDATED",
  "mdmBigQueryProvisionId": "d3d3ff57-1432-470f-9c77-2b280e5fcd1e",
  "mdmBigQueryProvisionVersion": "V3",
  "mdmBigQueryStatusType": "READY",
  "mdmCdsShortURLAuthentication": false,
  "mdmConsolidateBlocklist": false,
  "mdmCreated": "2020-11-06T20:50:38.835Z",
  "mdmCreatedUser": "jamir.araujo@totvs.com.br",
  "mdmDataDecorationEnabled": false,
  "mdmDataflowIgnoreRealtime": false,
  "mdmDescription": {},
  "mdmEnableAddressCleansing": false,
  "mdmEnableKafkaRt": false,
  "mdmEnableSSO": false,
  "mdmEntityType": "mdmTenant",
  "mdmExternalLoginConfiguration": {},
  "mdmFeatureLevelByQtUsers": "TECHFIN_PROD",
  "mdmFeatureLevelByShortURL": "CANARY",
  "mdmId": "3499b3a915074ecf8301a7352885056c",
  "mdmIsPublicData": false,
  "mdmLabel": {
    "en-US": "tenant86fc4dd6207011eb96d80a586460278d"
  },
  "mdmLastUpdated": "2023-09-01T14:19:04.205Z",
  "mdmLocale": "pt-BR",
  "mdmMappingsPending": false,
  "mdmName": "tenant86fc4dd6207011eb96d80a586460278d",
  "mdmOrgId": "f4e4697e021d4f68b849fa4971607b80",
  "mdmOverrideTaskQuotas": {
    "PROCESS_CDS_STAGING_DATA": 8
  },
  "mdmProcessingPriority": 2,
  "mdmProcessingTopicOverride": "staging-techfin",
  "mdmQueueSuffix": "",
  "mdmRealtimeDisabled": true,
  "mdmSQLProcessingOnly": true,
  "mdmSampleDataEnabled": true,
  "mdmSendEmailSmsCode": false,
  "mdmSmsProvider": "TWW",
  "mdmStatus": "ACTIVE",
  "mdmStatusDate": "2021-01-23T06:31:17.680Z",
  "mdmStopProcessing": false,
  "mdmSubdomain": "tenant86fc4dd6207011eb96d80a586460278d",
  "mdmTag": "PRODUCTION",
  "mdmTenantId": "8cd6e43115e9416eb23609486fa053e3",
  "mdmTotvsCustomerCode": "T60145",
  "mdmUnifiedTenantsMapping": {
    "efc39264c3ee454da66823d6cc047857": [
      {
        "connectorId": "d8d6eb590c434baca43041a0425f000b",
        "connectorName": "protheus_carol",
        "stagingName": "se1"
      },
      {
        "connectorId": "d8d6eb590c434baca43041a0425f000b",
        "connectorName": "protheus_carol",
        "stagingName": "se2"
      },
      {
        "connectorId": "d8d6eb590c434baca43041a0425f000b",
        "connectorName": "protheus_carol",
        "stagingName": "protheus_sharing"
      },
      {
        "connectorId": "d8d6eb590c434baca43041a0425f000b",
        "connectorName": "protheus_carol",
        "stagingName": "ct1"
      },
      {
        "connectorId": "d8d6eb590c434baca43041a0425f000b",
        "connectorName": "protheus_carol",
        "stagingName": "cv3"
      },
      {
        "connectorId": "d8d6eb590c434baca43041a0425f000b",
        "connectorName": "protheus_carol",
        "stagingName": "sa6"
      },
      {
        "connectorId": "d8d6eb590c434baca43041a0425f000b",
        "connectorName": "protheus_carol",
        "stagingName": "company"
      },
      {
        "connectorId": "d8d6eb590c434baca43041a0425f000b",
        "connectorName": "protheus_carol",
        "stagingName": "ctt"
      },
      {
        "connectorId": "d8d6eb590c434baca43041a0425f000b",
        "connectorName": "protheus_carol",
        "stagingName": "frv"
      },
      {
        "connectorId": "d8d6eb590c434baca43041a0425f000b",
        "connectorName": "protheus_carol",
        "stagingName": "sa1"
      },
      {
        "connectorId": "d8d6eb590c434baca43041a0425f000b",
        "connectorName": "protheus_carol",
        "stagingName": "sa2"
      },
      {
        "connectorId": "d8d6eb590c434baca43041a0425f000b",
        "connectorName": "protheus_carol",
        "stagingName": "se8"
      },
      {
        "connectorId": "d8d6eb590c434baca43041a0425f000b",
        "connectorName": "protheus_carol",
        "stagingName": "fk4"
      },
      {
        "connectorId": "d8d6eb590c434baca43041a0425f000b",
        "connectorName": "protheus_carol",
        "stagingName": "sea"
      },
      {
        "connectorId": "d8d6eb590c434baca43041a0425f000b",
        "connectorName": "protheus_carol",
        "stagingName": "fk5"
      },
      {
        "connectorId": "d8d6eb590c434baca43041a0425f000b",
        "connectorName": "protheus_carol",
        "stagingName": "fk6"
      },
      {
        "connectorId": "d8d6eb590c434baca43041a0425f000b",
        "connectorName": "protheus_carol",
        "stagingName": "sed"
      },
      {
        "connectorId": "d8d6eb590c434baca43041a0425f000b",
        "connectorName": "protheus_carol",
        "stagingName": "fk7"
      },
      {
        "connectorId": "d8d6eb590c434baca43041a0425f000b",
        "connectorName": "protheus_carol",
        "stagingName": "cvd"
      },
      {
        "connectorId": "d8d6eb590c434baca43041a0425f000b",
        "connectorName": "protheus_carol",
        "stagingName": "fk1"
      },
      {
        "connectorId": "d8d6eb590c434baca43041a0425f000b",
        "connectorName": "protheus_carol",
        "stagingName": "fk2"
      },
      {
        "connectorId": "d8d6eb590c434baca43041a0425f000b",
        "connectorName": "protheus_carol",
        "stagingName": "fk3"
      },
      {
        "connectorId": "d8d6eb590c434baca43041a0425f000b",
        "connectorName": "protheus_carol",
        "stagingName": "currency"
      },
      {
        "connectorId": "d8d6eb590c434baca43041a0425f000b",
        "connectorName": "protheus_carol",
        "stagingName": "sf2"
      },
      {
        "connectorId": "d8d6eb590c434baca43041a0425f000b",
        "connectorName": "protheus_carol",
        "stagingName": "fkd"
      },
      {
        "connectorId": "d8d6eb590c434baca43041a0425f000b",
        "connectorName": "protheus_carol",
        "stagingName": "sd1"
      },
      {
        "connectorId": "d8d6eb590c434baca43041a0425f000b",
        "connectorName": "protheus_carol",
        "stagingName": "sd2"
      },
      {
        "connectorId": "d8d6eb590c434baca43041a0425f000b",
        "connectorName": "protheus_carol",
        "stagingName": "sf1"
      },
      {
        "connectorId": "d8d6eb590c434baca43041a0425f000b",
        "connectorName": "protheus_carol",
        "stagingName": "fkc"
      },
      {
        "connectorId": "d8d6eb590c434baca43041a0425f000b",
        "connectorName": "protheus_carol",
        "stagingName": "mapping"
      },
      {
        "connectorId": "d8d6eb590c434baca43041a0425f000b",
        "connectorName": "protheus_carol",
        "stagingName": "organization"
      }
    ]
  },
  "mdmUpdatedUser": "aline.aparecida@totvs.com.br",
  "mdmUseSqlProcessingPriorityChannel": false,
  "mdmWhiteListDomains": [
    "*"
  ],
  "provisionReady": true,
  "tenantType": "CUSTOMER",
  "org": {
    "mdmAllowSmsLogin": false,
    "mdmBlockInvitationFromTenant": false,
    "mdmCarolCustomerCode": "Techfin",
    "mdmClusterId": "techfin",
    "mdmCreated": "2019-12-19T04:53:06.210Z",
    "mdmCreatedUser": "robson.poffo@totvs.com",
    "mdmDefaultNumberOfShards": 6,
    "mdmDescription": {
      "en-US": "techfin: Eduardo Teixeira"
    },
    "mdmEnableAddressCleansing": true,
    "mdmEnableJITProvision": true,
    "mdmEnableSSO": true,
    "mdmEnableSms": false,
    "mdmEntityType": "mdmOrganization",
    "mdmEnvironmentsLimit": 9999,
    "mdmExternalLoginConfiguration": {
      "Totvs Identity": {
        "mdmAppConfigurationToken": "NmRkMjdkYTk1MDAxNDFiOGJkZGU1MmRjMTlhZjlhZGI6OnpmMHk4NHZvNzE3Zzhoang=",
        "mdmAppDomains": [],
        "mdmAppIdToDomain": {},
        "mdmAppName": "Totvs Identity"
      }
    },
    "mdmFeatureLevelByQtUsers": "HIGH",
    "mdmId": "f4e4697e021d4f68b849fa4971607b80",
    "mdmLastUpdated": "2023-09-11T18:31:35.868Z",
    "mdmLocale": "pt-BR",
    "mdmName": "totvstechfin",
    "mdmObservabilityURL": "https://api-smartlink.totvs.app/api/observability/v1/carol/batch",
    "mdmProcessingPriorityFactor": 6,
    "mdmProcessingTopicOverride": "staging-techfin",
    "mdmRacAutheticationURL": "https://admin.rac.dev.totvs.app/totvs.rac/connect/token",
    "mdmRacClientId": "totvstechfin_cc",
    "mdmRacCredencial": "bb0887ce-2faa-4080-b3fc-d9f134cb7d70",
    "mdmSQLProcessingOnly": false,
    "mdmSendEmailSmsCode": false,
    "mdmSmsProvider": "TWW",
    "mdmStatus": "ACTIVE",
    "mdmStopProcessing": false,
    "mdmSubdomain": "totvstechfin",
    "mdmTenantId": "856e0510729e11e99928acde48001122",
    "mdmUpdatedUser": "robson.poffo@totvs.com",
    "mdmWhiteListDomains": [
      "*"
    ]
  }
}

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):

Identify the reason why Unified tenant is trying to fanout to a tenant without connection with the app.
- When running the allowed validate if the tenant has the app installed.
- When unsubscribing the app, remove from allowed.
Before fanout, we need to validate that the tenant has the app installed if not then add to the exclusion list and log this so we can have more information of this case still happening.
~~REFINEMENT --~~ -What if we invert the logic and we first remove the Customer Tenant from the list of allowed before we remove the CarolApp.
REFINEMENT - Create an alert (log) to identify current cenarios in Production where the customer tenant does not have anymore the CarolApp installed so we could give this list to Data Engineer later.
REFINEMENT - Also we need to make sure that this problem is not affecting other customers to receive the data. If that’s the case we cannot just log but we need to avoid this exception.
REFINEMENT - It was done something on the CdsFlowPipeline to validate if the destination table exists prior to send data into BQ for this table. We could also take a look into that because maybe checking for TenantApp on the Customer Tenant will degradate the Performance of a SQL Processing.
REFINEMENT - Make sure the fix will work for HYBRID or SQL Only Apps