Why is my pipeline batch job failing with "SQL compilation error"?

Question

I've tried several times to get new data into our Snowflake schema but with no luck. I've tried re-creating the pipelines, and re-exporting jobs with both incremental and batch options but no new data has made it into the warehouse.

  • Database: y57ngkcXtTgrvLi_DB

  • DataWarehouse: y57ngkcXtTgrvLi_WH

  • DatabaseSchema: y57ngkcXtTgrvLi_SCHEMA

  • Reltio Pipeline ID's:  (Recreated several times in attempts to get it to work) 

    • y57ngkcXtTgrvLi_1

    • y57ngkcXtTgrvLi_2

    • y57ngkcXtTgrvLi_3

  • Query to test with: 

    • select * from "y57ngkcXtTgrvLi_DB"."y57ngkcXtTgrvLi_SCHEMA"."entity_Contact_ov";

      • Only shows 8 entities, when there should be 50k

Pipeline/Job Details: 

Pipeline Definition

{
"pipelineId": "y57ngkcXtTgrvLi_poc",
"description": "Snowflake POC Reltio",
"entityTypes": [
{
"type": "CostCenter",
"attributesToIgnore": [],
"analyticsAttributesToIgnore": []
},
{
"type": "Organization",
"attributesToIgnore": [],
"analyticsAttributesToIgnore": []
},
{
"type": "Country",
"attributesToIgnore": [],
"analyticsAttributesToIgnore": []
},
{
"type": "Employee",
"attributesToIgnore": [],
"analyticsAttributesToIgnore": []
},
{
"type": "Location",
"attributesToIgnore": [],
"analyticsAttributesToIgnore": []
},
{
"type": "GeneralLedgerAccount",
"attributesToIgnore": [],
"analyticsAttributesToIgnore": []
},
{
"type": "BusniessUnit",
"attributesToIgnore": [],
"analyticsAttributesToIgnore": []
},
{
"type": "Contact",
"attributesToIgnore": [],
"analyticsAttributesToIgnore": []
}
],
"relationTypes": [
{
"type": "RelatedProducts",
"attributesToIgnore": [],
"analyticsAttributesToIgnore": []
},
{
"type": "affiliatedwith_sfdc",
"attributesToIgnore": [],
"analyticsAttributesToIgnore": []
}
],
"interactionTypes": [],
"additionalTypes": [],
"multiValueForSimpleAttribute": *false*,
"accountDetailsAlias": "snowflake_SalesforcePOC",
"database": "y57ngkcXtTgrvLi_DB",
"dataWarehouse": "y57ngkcXtTgrvLi_WH",
"databaseSchema": "y57ngkcXtTgrvLi_SCHEMA",
"ovOnly": *false*,
"overrideExistingTables": *true*
}

 

{
"jobId": "b42539a5-e995-490d-b1b8-50c15dfd52b6",
"type": "INCREMENTAL_BATCH",
"tenantId": "y57ngkcXtTgrvLi",
"pipelineId": "y57ngkcXtTgrvLi_poc",
"status": "FAILED",
"failureReason": "Task failed due to unexpected snowflake run time error, errorCode: SNOWFLAKE_RUNTIME_ERROR, errorMessage: SQL compilation error:\nTable '\"y57ngkcXtTgrvLi_DB\".\"y57ngkcXtTgrvLi_SCHEMA\".\"entity_CostCenter_source\"' does not exist or not authorized., errorDescription: An error occurred while running Snowflake ingestion task. SQL compilation error:\nTable '\"y57ngkcXtTgrvLi_DB\".\"y57ngkcXtTgrvLi_SCHEMA\".\"entity_CostCenter_source\"' does not exist or not authorized.",
"type": "SNOWFLAKE_INGEST_TASK"

 

Answer

 

The job failed because there was no table for entity_CostCenter_source created. It is because either the new table didn’t get created or you used an old pipeline with a new entry for entity_CostCenter_source without overriding existing tables. Our internal engineering team suggests deleting all existing tables and views, creating a new pipeline, and re-run the job.

Was this article helpful?
0 out of 0 found this helpful

Comments

0 comments

Please sign in to leave a comment.