{"objects":[{"metadata":{"identifier":{"packageName":"Matillion Exchange","name":"Xero Incremental Load","revision":1,"type":"DYNAMIC"},"rootJobReference":{"name":"Xero - 1 - Orchestrator","type":"ORCHESTRATION","parameterMetadata":[{"slot":2,"variableName":"oauth","variableType":"SCALAR","displayName":"OAuth","description":"","defaultValue":[{"values":{"1":""}}],"defaultValueType":"TEXT","required":true},{"slot":3,"variableName":"schema","variableType":"SCALAR","displayName":"Schema","description":"","defaultValue":[{"values":{"1":"Accounting"}}],"defaultValueType":"TEXT","required":true},{"slot":4,"variableName":"input_source_list","variableType":"GRID","displayName":"Tables and Columns","description":"","defaultValue":[{"values":{"1":""}}],"defaultValueType":null,"required":true},{"slot":5,"variableName":"advanced_connection_options","variableType":"GRID","displayName":"Connection Options","description":"A list of values and parameters. Parameters and their allowed values are database/driver specific. Referring to the data model will provide insight of what you could provide here.\n<br>\nThey are usually not required as sensible defaults are assumed.","defaultValue":[{"values":{"1":""}}],"defaultValueType":null,"required":true},{"slot":6,"variableName":"load_concurrent","variableType":"SCALAR","displayName":"Load Type","description":"Sequential - Iterations are done in sequence, waiting for each to complete before starting the next. \nThis is the default.\n<br>\nConcurrent - Iterations are run concurrently. This requires all \"Variables to Iterate\" to be defined as\n<a href=\"https://redshiftsupport.matillion.com/customer/portal/articles/2037630?b_id=8915#header1\" target=\"_blank\"><i>Copied</i> variables</a>, so that each iteration gets its own copy of the variable isolated from the same\nvariable being used by other concurrent executions.\n<span class=\"env rs sf\"><br><b>Note:</b> The maximum concurrency is limited by the number of available threads (2x the number of virtual cpus on your cloud instance).</span>","defaultValue":[{"values":{"1":"Concurrent"}}],"defaultValueType":"TEXT","required":true},{"slot":7,"variableName":"s3_bucket_name","variableType":"SCALAR","displayName":"S3 Bucket Name","description":"<span class=\"env sf\">(AWS Only) </span>The name of an S3 bucket for temporary storage. Ensure your access credentials have S3 access and permission to write to the bucket. See <a href=\"https://redshiftsupport.matillion.com/customer/portal/articles/2054760\" target=\"_blank\">this document</a> for details on setting up access. The temporary objects created in this bucket will be removed again after the load completes, they are not kept.<br>\n<span class=\"env sf\">This property is available when using an Existing Amazon S3 Location for Staging.</span>","defaultValue":[{"values":{"1":""}}],"defaultValueType":"TEXT","required":true},{"slot":8,"variableName":"dist_style","variableType":"SCALAR","displayName":"Distribution Style","description":"<b>Even:</b> Distributes rows around the Redshift cluster evenly.<br>\n<b>All:</b> Copy rows to all nodes in the Redshift cluster.<br>\nTable distribution is critical to good performance. See the <a href=\"http://docs.aws.amazon.com/redshift/latest/dg/tutorial-tuning-tables-distribution.html\" target=\"_blank\">Amazon Redshift</a> documentation for more information.","defaultValue":[{"values":{"1":"Even"}}],"defaultValueType":"TEXT","required":true},{"slot":9,"variableName":"stage_prefix","variableType":"SCALAR","displayName":"Stage Prefix","description":"A prefix value that will be added to the start of the stage table names.\n<br>\ne.g. If a Stage Prefix of 'stage_' is specified and the table being processed is named 'test_data' then the target table will be named 'stage_test_data'.","defaultValue":[{"values":{"1":"stage_"}}],"defaultValueType":"TEXT","required":true},{"slot":10,"variableName":"stage_schema","variableType":"SCALAR","displayName":"Stage Schema","description":"The schema name where the staging data will be stored.","defaultValue":[{"values":{"1":""}}],"defaultValueType":"TEXT","required":true},{"slot":11,"variableName":"target_prefix","variableType":"SCALAR","displayName":"Target Prefix","description":"A prefix value that will be added to the start of the target table names.\n<br>\ne.g. If a Target Prefix of 'target_' is specified and the table being processed is named 'test_data' then the target table will be named 'target_test_data'.","defaultValue":[{"values":{"1":"target_"}}],"defaultValueType":"TEXT","required":false},{"slot":12,"variableName":"target_schema","variableType":"SCALAR","displayName":"Target Schema","description":"The schema name where the target data will be stored.","defaultValue":[{"values":{"1":""}}],"defaultValueType":"TEXT","required":true},{"slot":13,"variableName":"encryption_method","variableType":"SCALAR","displayName":"Encryption Method","description":"<span class=\"env sf\">(AWS Only) </span>Decide on how the files are encrypted inside the S3 Bucket.<span class=\"env sf\">This property is available when using an Existing Amazon S3 Location for Staging.</span><br>\n                <b>None:</b> No encryption.<br>\n                <b>SSE KMS:</b> Encrypt the data according to a key stored on <a href=\"https://aws.amazon.com/kms/\" target=\"_blank\">KMS</a>.<br>\n                <b>SSE S3:</b> Encrypt the data according to a key stored on an <a href=\"http://docs.aws.amazon.com/AmazonS3/latest/dev/UsingServerSideEncryption.html\" target=\"_blank\">S3 bucket</a>","defaultValue":[{"values":{"1":"None"}}],"defaultValueType":"TEXT","required":true},{"slot":14,"variableName":"kms_key_id","variableType":"SCALAR","displayName":"KMS Key ID","description":"The ID of the KMS encryption key you have chosen to use in the 'Encryption' property.","defaultValue":[{"values":{"1":""}}],"defaultValueType":"TEXT","required":false},{"slot":15,"variableName":"log_metrics","variableType":"SCALAR","displayName":"Log Metrics","description":"","defaultValue":[{"values":{"1":"TRUE"}}],"defaultValueType":"TEXT","required":true}]},"orchestrationJobs":["Xero - 1 - Orchestrator","Xero - 4 - Query Source","Xero - 3 - Stage Object","Xero - 2 - Stage and Load"],"transformationJobs":[],"imageMetadata":{"componentIcon":"iVBORw0KGgoAAAANSUhEUgAAACAAAAAgCAYAAABzenr0AAADnUlEQVR42u1XW0hUQRi2C/SWFJV3KU1LLIgeLIQKuiGBFpRFSncqK6S0wsuSt9CiFOwhupBUPohWWrqXtItpYVmm5WX37K5prmtruolmF7Kov29Gzral5XFd9KWBj5mzO/N/33zzz5k5Dg7/y/9iY3GV66Y5F6n9OVSN00eHVCEEuio0WajbALKGi0Iwob7solAvsTuxh6rRG8GL/yT9K+TCfTel4GsXcne5Zg2CvpdM/gsfXOWadSMmh7VfbSAX8c1mEU4KvZeNMx/ghE3L4SbXqOxALqJ0eNYr1YvsSM7hXCQskz57hXDB3gJclMLV4ez3FjYoouYNzVRpLUG2PDOS/x09ra1ooczGdxT10kSeSi35lejpcK2JDuJ5TrGOEtUdlKE309KyJmsR7ZLIve42OYqDNj9tJbmpl4vYW91Gua09tA+iclq7adXDZjpa1075be95u9z8kRM+Mn+inc/bKBgiH3R+xH+vLSJmFNY7DW0/MtbaOiaisusT5Rl7yEMp8Haq0EnJmg4OTe8X2lTZSuk6M22FQwnqtxQCclnDWwosbaJsQ7cllrtSmC9l7/tYC4iofkP3Oz5YnGAz3VhpoHWPWzjWPzFwB5iAMIg9DlGrMevIFyZaVtZMuTos461a6QKsl0C0HQN58CKI2AV7WR0K4jQ4cb6pyyKA5UMpbD9S207bqoxUYeykU+kZ5HujisdzKnk1Q2oSvmYDwkHKyEVBLPlYwi0vb+b2bwcJdgxPvJUQwfrMgkts9qk1zZRy6jTFxcVRWPo5CjqX9zkmJsZTkgC8fs+PdNv5Xa+kQ4kpXMDOE5kUfSzpO9pfgGgJeaALsJXYs7B+gAi2BF43a1bExsbKIOAH6v0SXNAqbRGw4+QZCrxS/JsIn4Lqe2Lc+Pj4NIjohYgp/xTgfFuYiQA9wyH3Lqjhlgdk3/3tMGI7S4yLPHBEnz4ICB/aBaU2CAH6pApYkFPOBfjkP/91HCu0IX/GBbke/RKkJWS/CElO+Oc95gLmocZzL25GwYOEHIdlMANR0u8GRdpZUo5nWE1RCcm0ITNL717YMFscHxkZOUlsy2SyxUwkBCwc/g2p/5i+CBgGEWCE3Zd2p6afZdsNBCusEu+amHShoaET8HxgxHdFT0XdFFzN5zK4F6unir8zAgjIBb4BF4EQwARUDZn59ipJSUnjQbYHpGpmtwjMPGXUP2AgZjLIDcwVtCeOyVcUe/mMGflQ5Sc9ydOROM9fDQAAAABJRU5ErkJggg==","smallIcon":"iVBORw0KGgoAAAANSUhEUgAAABAAAAAQCAYAAAAf8/9hAAABmklEQVR42mNgGJRAafddfumt1xOkt9zoB2GpzTcSQWJEaZbafC1casv1t0D8Hw2/k9x6PYoYzf+waIbhfzgNEdp2mw+HzWj4xnuF9ecFMAyQ3nwtTm/Xrf/SQEXaO2/91wGyfY8++C+/7cZ/10P3/5vvvfPf4cA9mCFJGAYAndarBdRYe+XF/9JLz/9Pv/v2f/6FZ/87brz6XwUUqwHiAiDf/fD9/9Jbr03A9P+W6z26QFurLr/4X3H5+f/um6//x596/D8WiFPOPPlff/Xl/5LzT/7HbTiM3QCQF0AuAHlBfcfN/xpADHK67NYb/1W33/xvuPv2/4wFa//nNLb9T2/uPlRdXS2LLRDf4Ao808V7/8ttuvzfasH2d1FdUw0qKyt3NDQ0sKC6YuuNMGzRqLT+/P/gCfPA0Si19XokSG1FRUUl0BVmmF6BGIISnfIbL/2P7pn+DaQZaCsTSB3QBZ1lZWWaONMEMCziJbdc7wNhULIuranrA2oqA9pcDqStgZplSM4j5eXl/kDNu4G4m+yMBnSBEpBihPEBUXFOkoxM3XAAAAAASUVORK5CYII="},"description":"","helpHtml":"<div class=\"mtln\"><img src=\"data:image/png;base64,iVBORw0KGgoAAAANSUhEUgAAACAAAAAgCAYAAABzenr0AAADnUlEQVR42u1XW0hUQRi2C/SWFJV3KU1LLIgeLIQKuiGBFpRFSncqK6S0wsuSt9CiFOwhupBUPohWWrqXtItpYVmm5WX37K5prmtruolmF7Kov29Gzral5XFd9KWBj5mzO/N/33zzz5k5Dg7/y/9iY3GV66Y5F6n9OVSN00eHVCEEuio0WajbALKGi0Iwob7solAvsTuxh6rRG8GL/yT9K+TCfTel4GsXcne5Zg2CvpdM/gsfXOWadSMmh7VfbSAX8c1mEU4KvZeNMx/ghE3L4SbXqOxALqJ0eNYr1YvsSM7hXCQskz57hXDB3gJclMLV4ez3FjYoouYNzVRpLUG2PDOS/x09ra1ooczGdxT10kSeSi35lejpcK2JDuJ5TrGOEtUdlKE309KyJmsR7ZLIve42OYqDNj9tJbmpl4vYW91Gua09tA+iclq7adXDZjpa1075be95u9z8kRM+Mn+inc/bKBgiH3R+xH+vLSJmFNY7DW0/MtbaOiaisusT5Rl7yEMp8Haq0EnJmg4OTe8X2lTZSuk6M22FQwnqtxQCclnDWwosbaJsQ7cllrtSmC9l7/tYC4iofkP3Oz5YnGAz3VhpoHWPWzjWPzFwB5iAMIg9DlGrMevIFyZaVtZMuTos461a6QKsl0C0HQN58CKI2AV7WR0K4jQ4cb6pyyKA5UMpbD9S207bqoxUYeykU+kZ5HujisdzKnk1Q2oSvmYDwkHKyEVBLPlYwi0vb+b2bwcJdgxPvJUQwfrMgkts9qk1zZRy6jTFxcVRWPo5CjqX9zkmJsZTkgC8fs+PdNv5Xa+kQ4kpXMDOE5kUfSzpO9pfgGgJeaALsJXYs7B+gAi2BF43a1bExsbKIOAH6v0SXNAqbRGw4+QZCrxS/JsIn4Lqe2Lc+Pj4NIjohYgp/xTgfFuYiQA9wyH3Lqjhlgdk3/3tMGI7S4yLPHBEnz4ICB/aBaU2CAH6pApYkFPOBfjkP/91HCu0IX/GBbke/RKkJWS/CElO+Oc95gLmocZzL25GwYOEHIdlMANR0u8GRdpZUo5nWE1RCcm0ITNL717YMFscHxkZOUlsy2SyxUwkBCwc/g2p/5i+CBgGEWCE3Zd2p6afZdsNBCusEu+amHShoaET8HxgxHdFT0XdFFzN5zK4F6unir8zAgjIBb4BF4EQwARUDZn59ipJSUnjQbYHpGpmtwjMPGXUP2AgZjLIDcwVtCeOyVcUe/mMGflQ5Sc9ydOROM9fDQAAAABJRU5ErkJggg==\"><h3>Xero Incremental Load</h3><p style='white-space: pre-line; word-wrap: break-word;'></p><h3>Properties</h3><table><tbody><tr><th>Property</th><th>Type</th><th>Description</th></tr><tr><td>OAuth</td><td>Variable</td><td style='white-space: pre-line; word-wrap: break-word;'></td></tr><tr><td>Schema</td><td>Variable</td><td style='white-space: pre-line; word-wrap: break-word;'></td></tr><tr><td>Tables and Columns</td><td>Grid</td><td style='white-space: pre-line; word-wrap: break-word;'></td></tr><tr><td>Connection Options</td><td>Grid</td><td style='white-space: pre-line; word-wrap: break-word;'>A list of values and parameters. Parameters and their allowed values are database/driver specific. Referring to the data model will provide insight of what you could provide here.\n<br>\nThey are usually not required as sensible defaults are assumed.</td></tr><tr><td>Load Type</td><td>Variable</td><td style='white-space: pre-line; word-wrap: break-word;'>Sequential - Iterations are done in sequence, waiting for each to complete before starting the next. \nThis is the default.\n<br>\nConcurrent - Iterations are run concurrently. This requires all \"Variables to Iterate\" to be defined as\n<a href=\"https://redshiftsupport.matillion.com/customer/portal/articles/2037630?b_id=8915#header1\" target=\"_blank\"><i>Copied</i> variables</a>, so that each iteration gets its own copy of the variable isolated from the same\nvariable being used by other concurrent executions.\n<span class=\"env rs sf\"><br><b>Note:</b> The maximum concurrency is limited by the number of available threads (2x the number of virtual cpus on your cloud instance).</span></td></tr><tr><td>S3 Bucket Name</td><td>Variable</td><td style='white-space: pre-line; word-wrap: break-word;'><span class=\"env sf\">(AWS Only) </span>The name of an S3 bucket for temporary storage. Ensure your access credentials have S3 access and permission to write to the bucket. See <a href=\"https://redshiftsupport.matillion.com/customer/portal/articles/2054760\" target=\"_blank\">this document</a> for details on setting up access. The temporary objects created in this bucket will be removed again after the load completes, they are not kept.<br>\n<span class=\"env sf\">This property is available when using an Existing Amazon S3 Location for Staging.</span></td></tr><tr><td>Distribution Style</td><td>Variable</td><td style='white-space: pre-line; word-wrap: break-word;'><b>Even:</b> Distributes rows around the Redshift cluster evenly.<br>\n<b>All:</b> Copy rows to all nodes in the Redshift cluster.<br>\nTable distribution is critical to good performance. See the <a href=\"http://docs.aws.amazon.com/redshift/latest/dg/tutorial-tuning-tables-distribution.html\" target=\"_blank\">Amazon Redshift</a> documentation for more information.</td></tr><tr><td>Stage Prefix</td><td>Variable</td><td style='white-space: pre-line; word-wrap: break-word;'>A prefix value that will be added to the start of the stage table names.\n<br>\ne.g. If a Stage Prefix of 'stage_' is specified and the table being processed is named 'test_data' then the target table will be named 'stage_test_data'.</td></tr><tr><td>Stage Schema</td><td>Variable</td><td style='white-space: pre-line; word-wrap: break-word;'>The schema name where the staging data will be stored.</td></tr><tr><td>Target Prefix</td><td>Variable</td><td style='white-space: pre-line; word-wrap: break-word;'>A prefix value that will be added to the start of the target table names.\n<br>\ne.g. If a Target Prefix of 'target_' is specified and the table being processed is named 'test_data' then the target table will be named 'target_test_data'.</td></tr><tr><td>Target Schema</td><td>Variable</td><td style='white-space: pre-line; word-wrap: break-word;'>The schema name where the target data will be stored.</td></tr><tr><td>Encryption Method</td><td>Variable</td><td style='white-space: pre-line; word-wrap: break-word;'><span class=\"env sf\">(AWS Only) </span>Decide on how the files are encrypted inside the S3 Bucket.<span class=\"env sf\">This property is available when using an Existing Amazon S3 Location for Staging.</span><br>\n                <b>None:</b> No encryption.<br>\n                <b>SSE KMS:</b> Encrypt the data according to a key stored on <a href=\"https://aws.amazon.com/kms/\" target=\"_blank\">KMS</a>.<br>\n                <b>SSE S3:</b> Encrypt the data according to a key stored on an <a href=\"http://docs.aws.amazon.com/AmazonS3/latest/dev/UsingServerSideEncryption.html\" target=\"_blank\">S3 bucket</a></td></tr><tr><td>KMS Key ID</td><td>Variable</td><td style='white-space: pre-line; word-wrap: break-word;'>The ID of the KMS encryption key you have chosen to use in the 'Encryption' property.</td></tr><tr><td>Log Metrics</td><td>Variable</td><td style='white-space: pre-line; word-wrap: break-word;'></td></tr></tbody></table></div>","created":1613407990249},"orchestrationJobs":{"Xero - 3 - Stage Object":{"id":-2,"revision":1,"created":1613406965565,"timestamp":1613406965565,"components":{"13232":{"id":13232,"inputCardinality":"ONE","outputCardinality":"ZERO","connectorHint":"UNCONDITIONAL","executionHint":"FLOW","implementationID":-1946388514,"x":1056,"y":16,"width":32,"height":32,"inputConnectorIDs":[13250],"outputSuccessConnectorIDs":[],"outputFailureConnectorIDs":[],"outputUnconditionalConnectorIDs":[],"outputTrueConnectorIDs":[],"outputFalseConnectorIDs":[],"exportMappings":{},"parameters":{"1":{"slot":1,"name":"Name","elements":{"1":{"slot":1,"values":{"1":{"slot":1,"type":"STRING","value":"End Success"}}}},"visible":true,"status":{},"columnNames":[]}},"validationStatus":{},"status":{},"expectedFailure":null,"activationStatus":"ENABLED","outputIterationConnectorIDs":[],"inputIterationConnectorIDs":[]},"13233":{"id":13233,"inputCardinality":"ZERO","outputCardinality":"MANY","connectorHint":"UNCONDITIONAL","executionHint":"FLOW","implementationID":444132438,"x":-1344,"y":16,"width":32,"height":32,"inputConnectorIDs":[],"outputSuccessConnectorIDs":[],"outputFailureConnectorIDs":[],"outputUnconditionalConnectorIDs":[13256],"outputTrueConnectorIDs":[],"outputFalseConnectorIDs":[],"exportMappings":{},"parameters":{"1":{"slot":1,"name":"Name","elements":{"1":{"slot":1,"values":{"1":{"slot":1,"type":"STRING","value":"Start"}}}},"visible":true,"status":{},"columnNames":[]}},"validationStatus":{},"status":{},"expectedFailure":null,"activationStatus":"ENABLED","outputIterationConnectorIDs":[],"inputIterationConnectorIDs":[]},"13234":{"id":13234,"inputCardinality":"ONE","outputCardinality":"MANY","connectorHint":"SUCCESS_FAIL","executionHint":"EXECUTE","implementationID":947479920,"x":-208,"y":16,"width":32,"height":32,"inputConnectorIDs":[13245],"outputSuccessConnectorIDs":[13247],"outputFailureConnectorIDs":[],"outputUnconditionalConnectorIDs":[],"outputTrueConnectorIDs":[],"outputFalseConnectorIDs":[],"exportMappings":{"1":{"slot":1,"fromId":null,"fromName":"SELECT_STRING","mapTo":"source_column_list","validationStatus":null,"validationMessage":null},"2":{"slot":2,"fromId":null,"fromName":"incremental_column","mapTo":"incremental_column","validationStatus":null,"validationMessage":null},"3":{"slot":3,"fromId":null,"fromName":"incremental_column_count","mapTo":"incremental_column_count","validationStatus":null,"validationMessage":null},"4":{"slot":4,"fromId":null,"fromName":"key_column_count","mapTo":"key_column_count","validationStatus":null,"validationMessage":null}},"gridExportMappings":{"2":{"slot":2,"fromGrid":"key_columns","toGrid":"key_columns","columnMapToFrom":{"column":"column_name"},"validationStatus":null,"validationMessage":null}},"parameters":{"1":{"slot":1,"name":"Name","elements":{"1":{"slot":1,"values":{"1":{"slot":1,"type":"STRING","value":"Parameter Parser"}}}},"visible":true,"status":{},"columnNames":[]},"2":{"slot":2,"name":"platform","elements":{"1":{"slot":1,"values":{"1":{"slot":1,"type":"STRING","value":"${target_platform}"}}}},"visible":true,"status":{},"columnNames":[]},"3":{"slot":3,"name":"table_name","elements":{"1":{"slot":1,"values":{"1":{"slot":1,"type":"STRING","value":"${table_name}"}}}},"visible":true,"status":{},"columnNames":[]},"4":{"slot":4,"name":"incremental_source_list","elements":{"1":{"slot":1,"values":{"1":{"slot":1,"type":"GRID","value":"source_list"}}},"2":{"slot":2,"values":{"1":{"slot":1,"type":"GRID","value":"table"},"2":{"slot":2,"type":"GRID","value":"column"},"3":{"slot":3,"type":"GRID","value":"incremental_column"},"4":{"slot":4,"type":"GRID","value":"key_column"}}}},"visible":true,"status":{},"columnNames":[]},"5":{"slot":5,"name":"source_list","elements":{},"visible":true,"status":{},"columnNames":[]},"6":{"slot":6,"name":"stage_warehouse","elements":{"1":{"slot":1,"values":{"1":{"slot":1,"type":"STRING","value":"${stage_warehouse}"}}}},"visible":true,"status":{},"columnNames":[]},"7":{"slot":7,"name":"stage_database","elements":{"1":{"slot":1,"values":{"1":{"slot":1,"type":"STRING","value":"${stage_database}"}}}},"visible":true,"status":{},"columnNames":[]},"8":{"slot":8,"name":"stage_schema","elements":{"1":{"slot":1,"values":{"1":{"slot":1,"type":"STRING","value":"${stage_schema}"}}}},"visible":true,"status":{},"columnNames":[]},"9":{"slot":9,"name":"stage_project","elements":{"1":{"slot":1,"values":{"1":{"slot":1,"type":"STRING","value":"${stage_project}"}}}},"visible":true,"status":{},"columnNames":[]},"10":{"slot":10,"name":"stage_dataset","elements":{"1":{"slot":1,"values":{"1":{"slot":1,"type":"STRING","value":"${stage_dataset}"}}}},"visible":true,"status":{},"columnNames":[]},"11":{"slot":11,"name":"stage_prefix","elements":{"1":{"slot":1,"values":{"1":{"slot":1,"type":"STRING","value":"${stage_prefix}"}}}},"visible":true,"status":{},"columnNames":[]},"12":{"slot":12,"name":"target_warehouse","elements":{"1":{"slot":1,"values":{"1":{"slot":1,"type":"STRING","value":"${target_warehouse}"}}}},"visible":true,"status":{},"columnNames":[]},"13":{"slot":13,"name":"target_database","elements":{"1":{"slot":1,"values":{"1":{"slot":1,"type":"STRING","value":"${target_database}"}}}},"visible":true,"status":{},"columnNames":[]},"14":{"slot":14,"name":"target_schema","elements":{"1":{"slot":1,"values":{"1":{"slot":1,"type":"STRING","value":"${target_schema}"}}}},"visible":true,"status":{},"columnNames":[]},"15":{"slot":15,"name":"target_project","elements":{"1":{"slot":1,"values":{"1":{"slot":1,"type":"STRING","value":"${target_project}"}}}},"visible":true,"status":{},"columnNames":[]},"16":{"slot":16,"name":"target_dataset","elements":{"1":{"slot":1,"values":{"1":{"slot":1,"type":"STRING","value":"${target_dataset}"}}}},"visible":true,"status":{},"columnNames":[]},"17":{"slot":17,"name":"target_prefix","elements":{"1":{"slot":1,"values":{"1":{"slot":1,"type":"STRING","value":"${target_prefix}"}}}},"visible":true,"status":{},"columnNames":[]},"18":{"slot":18,"name":"maximum_incremental_columns","elements":{"1":{"slot":1,"values":{"1":{"slot":1,"type":"DECIMAL","value":"1"}}}},"visible":true,"status":{},"columnNames":[]},"19":{"slot":19,"name":"source_enclosing_char_e","elements":{"1":{"slot":1,"values":{"1":{"slot":1,"type":"STRING","value":"\""}}}},"visible":true,"status":{},"columnNames":[]},"20":{"slot":20,"name":"source_enclosing_char_s","elements":{"1":{"slot":1,"values":{"1":{"slot":1,"type":"STRING","value":"\""}}}},"visible":true,"status":{},"columnNames":[]},"21":{"slot":21,"name":"metadata_stage_table_name","elements":{"1":{"slot":1,"values":{"1":{"slot":1,"type":"STRING","value":""}}}},"visible":true,"status":{},"columnNames":[]},"22":{"slot":22,"name":"stage_table_name","elements":{"1":{"slot":1,"values":{"1":{"slot":1,"type":"STRING","value":""}}}},"visible":true,"status":{},"columnNames":[]},"23":{"slot":23,"name":"target_table_name","elements":{"1":{"slot":1,"values":{"1":{"slot":1,"type":"STRING","value":""}}}},"visible":true,"status":{},"columnNames":[]},"24":{"slot":24,"name":"column_count","elements":{"1":{"slot":1,"values":{"1":{"slot":1,"type":"DECIMAL","value":"0"}}}},"visible":true,"status":{},"columnNames":[]},"25":{"slot":25,"name":"key_column_count","elements":{"1":{"slot":1,"values":{"1":{"slot":1,"type":"DECIMAL","value":"0"}}}},"visible":true,"status":{},"columnNames":[]},"26":{"slot":26,"name":"key_columns","elements":{},"visible":true,"status":{},"columnNames":[]},"27":{"slot":27,"name":"incremental_column_count","elements":{"1":{"slot":1,"values":{"1":{"slot":1,"type":"DECIMAL","value":"0"}}}},"visible":true,"status":{},"columnNames":[]},"28":{"slot":28,"name":"incremental_column","elements":{"1":{"slot":1,"values":{"1":{"slot":1,"type":"STRING","value":""}}}},"visible":true,"status":{},"columnNames":[]},"29":{"slot":29,"name":"SELECT_STRING","elements":{"1":{"slot":1,"values":{"1":{"slot":1,"type":"STRING","value":""}}}},"visible":true,"status":{},"columnNames":[]},"30":{"slot":30,"name":"stage_table_metadata_sql","elements":{"1":{"slot":1,"values":{"1":{"slot":1,"type":"STRING","value":""}}}},"visible":true,"status":{},"columnNames":[]}},"validationStatus":{},"status":{},"expectedFailure":null,"activationStatus":"ENABLED","outputIterationConnectorIDs":[],"inputIterationConnectorIDs":[]},"13235":{"id":13235,"inputCardinality":"ONE","outputCardinality":"MANY","connectorHint":"SUCCESS_FAIL","executionHint":"EXECUTE","implementationID":-313802845,"x":880,"y":16,"width":32,"height":32,"inputConnectorIDs":[13255],"outputSuccessConnectorIDs":[13250],"outputFailureConnectorIDs":[],"outputUnconditionalConnectorIDs":[],"outputTrueConnectorIDs":[],"outputFalseConnectorIDs":[],"exportMappings":{},"parameters":{"1":{"slot":1,"name":"Name","elements":{"1":{"slot":1,"values":{"1":{"slot":1,"type":"STRING","value":"Load target"}}}},"visible":true,"status":{},"columnNames":[]},"2":{"slot":2,"name":"platform","elements":{"1":{"slot":1,"values":{"1":{"slot":1,"type":"STRING","value":"${target_platform}"}}}},"visible":true,"status":{},"columnNames":[]},"3":{"slot":3,"name":"stage_warehouse","elements":{"1":{"slot":1,"values":{"1":{"slot":1,"type":"STRING","value":"${stage_warehouse}"}}}},"visible":true,"status":{},"columnNames":[]},"4":{"slot":4,"name":"stage_database","elements":{"1":{"slot":1,"values":{"1":{"slot":1,"type":"STRING","value":"${stage_database}"}}}},"visible":true,"status":{},"columnNames":[]},"5":{"slot":5,"name":"stage_schema","elements":{"1":{"slot":1,"values":{"1":{"slot":1,"type":"STRING","value":"${stage_schema}"}}}},"visible":true,"status":{},"columnNames":[]},"6":{"slot":6,"name":"stage_project","elements":{"1":{"slot":1,"values":{"1":{"slot":1,"type":"STRING","value":"${stage_project}"}}}},"visible":true,"status":{},"columnNames":[]},"7":{"slot":7,"name":"stage_dataset","elements":{"1":{"slot":1,"values":{"1":{"slot":1,"type":"STRING","value":"${stage_dataset}"}}}},"visible":true,"status":{},"columnNames":[]},"8":{"slot":8,"name":"stage_table_name","elements":{"1":{"slot":1,"values":{"1":{"slot":1,"type":"STRING","value":"${stage_table_name}"}}}},"visible":true,"status":{},"columnNames":[]},"9":{"slot":9,"name":"target_warehouse","elements":{"1":{"slot":1,"values":{"1":{"slot":1,"type":"STRING","value":"${target_warehouse}"}}}},"visible":true,"status":{},"columnNames":[]},"10":{"slot":10,"name":"target_database","elements":{"1":{"slot":1,"values":{"1":{"slot":1,"type":"STRING","value":"${target_database}"}}}},"visible":true,"status":{},"columnNames":[]},"11":{"slot":11,"name":"target_schema","elements":{"1":{"slot":1,"values":{"1":{"slot":1,"type":"STRING","value":"${target_schema}"}}}},"visible":true,"status":{},"columnNames":[]},"12":{"slot":12,"name":"target_project","elements":{"1":{"slot":1,"values":{"1":{"slot":1,"type":"STRING","value":"${target_project}"}}}},"visible":true,"status":{},"columnNames":[]},"13":{"slot":13,"name":"target_dataset","elements":{"1":{"slot":1,"values":{"1":{"slot":1,"type":"STRING","value":"${target_dataset}"}}}},"visible":true,"status":{},"columnNames":[]},"14":{"slot":14,"name":"target_table_name","elements":{"1":{"slot":1,"values":{"1":{"slot":1,"type":"STRING","value":"${target_table_name}"}}}},"visible":true,"status":{},"columnNames":[]},"15":{"slot":15,"name":"load_strategy","elements":{"1":{"slot":1,"values":{"1":{"slot":1,"type":"STRING","value":"${load_strategy}"}}}},"visible":true,"status":{},"columnNames":[]},"16":{"slot":16,"name":"dist_style","elements":{"1":{"slot":1,"values":{"1":{"slot":1,"type":"STRING","value":"${dist_style}"}}}},"visible":true,"status":{},"columnNames":[]},"17":{"slot":17,"name":"columns","elements":{"1":{"slot":1,"values":{"1":{"slot":1,"type":"GRID","value":"stage_table_metadata"}}},"2":{"slot":2,"values":{"1":{"slot":1,"type":"GRID","value":"column_name"}}}},"visible":true,"status":{},"columnNames":[]},"18":{"slot":18,"name":"key_columns","elements":{"1":{"slot":1,"values":{"1":{"slot":1,"type":"GRID","value":"key_columns"}}},"2":{"slot":2,"values":{"1":{"slot":1,"type":"GRID","value":"column"}}}},"visible":true,"status":{},"columnNames":[]},"19":{"slot":19,"name":"incremental_column","elements":{"1":{"slot":1,"values":{"1":{"slot":1,"type":"STRING","value":""}}}},"visible":true,"status":{},"columnNames":[]},"20":{"slot":20,"name":"join_statement","elements":{"1":{"slot":1,"values":{"1":{"slot":1,"type":"STRING","value":""}}}},"visible":true,"status":{},"columnNames":[]},"21":{"slot":21,"name":"log_metrics","elements":{"1":{"slot":1,"values":{"1":{"slot":1,"type":"STRING","value":"${log_metrics}"}}}},"visible":true,"status":{},"columnNames":[]},"22":{"slot":22,"name":"static_row_count","elements":{"1":{"slot":1,"values":{"1":{"slot":1,"type":"STRING","value":"${static_row_count}"}}}},"visible":true,"status":{},"columnNames":[]},"23":{"slot":23,"name":"logged_rows_inserted","elements":{"1":{"slot":1,"values":{"1":{"slot":1,"type":"DECIMAL","value":"0"}}}},"visible":true,"status":{},"columnNames":[]},"24":{"slot":24,"name":"logged_rows_update","elements":{"1":{"slot":1,"values":{"1":{"slot":1,"type":"DECIMAL","value":"0"}}}},"visible":true,"status":{},"columnNames":[]},"25":{"slot":25,"name":"logged_rows_deleted","elements":{"1":{"slot":1,"values":{"1":{"slot":1,"type":"DECIMAL","value":"0"}}}},"visible":true,"status":{},"columnNames":[]},"26":{"slot":26,"name":"sub_RangeStartDate","elements":{"1":{"slot":1,"values":{"1":{"slot":1,"type":"STRING","value":""}}}},"visible":true,"status":{},"columnNames":[]}},"validationStatus":{},"status":{},"expectedFailure":null,"activationStatus":"ENABLED","outputIterationConnectorIDs":[],"inputIterationConnectorIDs":[]},"13236":{"id":13236,"inputCardinality":"ONE","outputCardinality":"MANY","connectorHint":"SUCCESS_FAIL","executionHint":"EXECUTE","implementationID":-1773186960,"x":-672,"y":16,"width":32,"height":32,"inputConnectorIDs":[13252],"outputSuccessConnectorIDs":[13254],"outputFailureConnectorIDs":[],"outputUnconditionalConnectorIDs":[],"outputTrueConnectorIDs":[],"outputFalseConnectorIDs":[],"exportMappings":{},"parameters":{"1":{"slot":1,"name":"Name","elements":{"1":{"slot":1,"values":{"1":{"slot":1,"type":"STRING","value":"Adjust source_list"}}}},"visible":true,"status":{},"columnNames":[]},"2":{"slot":2,"name":"Script","elements":{"1":{"slot":1,"values":{"1":{"slot":1,"type":"STRING","value":"def get_key_column(column_data):\n  \"\"\"\n  For Xero, we will specify the Key Column using information from the CData Driver\n  This information is stored in the `source_table_metadata` grid at runtime.\n  This grid variable should be passed into the function through the column_data argument \n  \"\"\" \n  for row in column_data:\n    if str(row[4]) == 'true':\n      return row[0]\n  return \"\"\n\ndef get_inc_column(column_data):\n  \"\"\"\n  For Xero, we search for incremental columns in the following order:\n  UpdatedDateUTC -> CreatedDateUTC -> Date\n  \"\"\"\n  \n  # empty string indicates no incremental column found \n  inc_col = \"\"\n  \n  for row in column_data:\n    # UpdatedDateUTC is the highest priority \n    if row[0] == 'UpdatedDateUTC':\n      return 'UpdatedDateUTC'\n    \n    # If we find CreatedDateUTC, dont return as we may also find UpdatedDateUTC \n    if row[0] == 'CreatedDateUTC':\n      inc_col = row[0]\n    \n    # Date is the lowest priority - should only be set as the incremental column if it has not already been set\n    if row[0] == 'Date' and inc_col == \"\":\n      inc_col = row[0]\n      \n  return inc_col \n  \ncol_data = context.getGridVariable('source_table_metadata')\n\n\nsource_col_names = [ x[0] for x in col_data ] \n\nprint(\"Source Table Metadata:\")\nfor x in col_data:\n  print(\"\\t\".join(x))\n  \n  \nkey_column = get_key_column(col_data)\n\ninc_column = get_inc_column(col_data)\n\nprint(\"Table: {} - Got (key, incremental) column: ({}, {})\".format(table_name, key_column, inc_column))\n\nupdated = [] \n  \nfor row in context.getGridVariable('source_list'):\n \n  if row[0] != table_name:\n    continue \n  \n  col_name = row[1]\n  \n  key_val = '1' if col_name == key_column else '0'\n  \n  inc_val = '1' if col_name == inc_column else '0'\n  \n  updated.append(\n  [ table_name, col_name, inc_val, key_val ]  \n  )\n  \ncolumn_names = [ x[1] for x in updated ] \n\n# if a key column exists and the user does not specify it, add it to the end \nif key_column and key_column in source_col_names and key_column not in column_names:\n  updated.append([ table_name, key_column, '0', '1' ])\n\n# if an incremental column exists and the user does not specify, add it to the end \nif inc_column and inc_column in source_col_names and inc_column not in column_names:\n  updated.append([ table_name, inc_column, '1', '0' ])\n  \ncontext.updateGridVariable('source_list', updated)\n\nprint(\"Source List:\")\nfor x in context.getGridVariable('source_list'):\n  print(\"\\t\".join(x))\n  "}}}},"visible":true,"status":{},"columnNames":[]},"3":{"slot":3,"name":"Interpreter","elements":{"1":{"slot":1,"values":{"1":{"slot":1,"type":"STRING","value":"Python 3"}}}},"visible":true,"status":{},"columnNames":[]},"4":{"slot":4,"name":"Timeout","elements":{"1":{"slot":1,"values":{"1":{"slot":1,"type":"INTEGER","value":"360"}}}},"visible":true,"status":{},"columnNames":[]}},"validationStatus":{},"status":{},"expectedFailure":null,"activationStatus":"ENABLED","outputIterationConnectorIDs":[],"inputIterationConnectorIDs":[]},"13237":{"id":13237,"inputCardinality":"ONE","outputCardinality":"MANY","connectorHint":"SUCCESS_FAIL","executionHint":"EXECUTE","implementationID":-1773186960,"x":32,"y":16,"width":32,"height":32,"inputConnectorIDs":[13247],"outputSuccessConnectorIDs":[13246],"outputFailureConnectorIDs":[],"outputUnconditionalConnectorIDs":[],"outputTrueConnectorIDs":[],"outputFalseConnectorIDs":[],"exportMappings":{},"parameters":{"1":{"slot":1,"name":"Name","elements":{"1":{"slot":1,"values":{"1":{"slot":1,"type":"STRING","value":"Build stage_single_sql"}}}},"visible":true,"status":{},"columnNames":[]},"2":{"slot":2,"name":"Script","elements":{"1":{"slot":1,"values":{"1":{"slot":1,"type":"STRING","value":"# set base stage_single_sql\nstage_single_sql = (f'SELECT {source_column_list} \\n' + \n                    f'FROM \"{table_name}\" \\n'\n                    f'LIMIT 1'\n)\n\ncontext.updateVariable('stage_single_sql', stage_single_sql)\nprint(f\"Updated Variable 'stage_single_sql' with {stage_single_sql}\")\n"}}}},"visible":true,"status":{},"columnNames":[]},"3":{"slot":3,"name":"Interpreter","elements":{"1":{"slot":1,"values":{"1":{"slot":1,"type":"STRING","value":"Python 3"}}}},"visible":true,"status":{},"columnNames":[]},"4":{"slot":4,"name":"Timeout","elements":{"1":{"slot":1,"values":{"1":{"slot":1,"type":"INTEGER","value":"360"}}}},"visible":true,"status":{},"columnNames":[]}},"validationStatus":{},"status":{},"expectedFailure":null,"activationStatus":"ENABLED","outputIterationConnectorIDs":[],"inputIterationConnectorIDs":[]},"13238":{"id":13238,"inputCardinality":"ONE","outputCardinality":"MANY","connectorHint":"SUCCESS_FAIL","executionHint":"EXECUTE","implementationID":1010676649,"x":-1168,"y":16,"width":32,"height":32,"inputConnectorIDs":[13256],"outputSuccessConnectorIDs":[13248],"outputFailureConnectorIDs":[],"outputUnconditionalConnectorIDs":[],"outputTrueConnectorIDs":[],"outputFalseConnectorIDs":[],"exportMappings":{"1":{"slot":1,"fromId":null,"fromName":"source_metadata_sql","mapTo":"source_metadata_sql","validationStatus":null,"validationMessage":null},"2":{"slot":2,"fromId":null,"fromName":"get_metadata_sql","mapTo":"get_metadata_sql","validationStatus":null,"validationMessage":null},"3":{"slot":3,"fromId":null,"fromName":"target_table_name","mapTo":"target_table_name","validationStatus":null,"validationMessage":null},"4":{"slot":4,"fromId":null,"fromName":"stage_table_name","mapTo":"stage_table_name","validationStatus":null,"validationMessage":null},"5":{"slot":5,"fromId":null,"fromName":"metadata_stage_table_name","mapTo":"metadata_stage_table_name","validationStatus":null,"validationMessage":null}},"gridExportMappings":{"1":{"slot":1,"fromGrid":"source_list","toGrid":"source_list","columnMapToFrom":{"column":"column","key_column":"key_column","incremental_column":"incremental_column","table":"table"},"validationStatus":null,"validationMessage":null}},"parameters":{"1":{"slot":1,"name":"Name","elements":{"1":{"slot":1,"values":{"1":{"slot":1,"type":"STRING","value":"Build strings"}}}},"visible":true,"status":{},"columnNames":[]},"2":{"slot":2,"name":"table_name","elements":{"1":{"slot":1,"values":{"1":{"slot":1,"type":"STRING","value":"${table_name}"}}}},"visible":true,"status":{},"columnNames":[]},"3":{"slot":3,"name":"stage_database","elements":{"1":{"slot":1,"values":{"1":{"slot":1,"type":"STRING","value":"${stage_database}"}}}},"visible":true,"status":{},"columnNames":[]},"4":{"slot":4,"name":"stage_schema","elements":{"1":{"slot":1,"values":{"1":{"slot":1,"type":"STRING","value":"${stage_schema}"}}}},"visible":true,"status":{},"columnNames":[]},"5":{"slot":5,"name":"stage_project","elements":{"1":{"slot":1,"values":{"1":{"slot":1,"type":"STRING","value":"${stage_project}"}}}},"visible":true,"status":{},"columnNames":[]},"6":{"slot":6,"name":"stage_dataset","elements":{"1":{"slot":1,"values":{"1":{"slot":1,"type":"STRING","value":"${stage_dataset}"}}}},"visible":true,"status":{},"columnNames":[]},"7":{"slot":7,"name":"stage_prefix","elements":{"1":{"slot":1,"values":{"1":{"slot":1,"type":"STRING","value":"${stage_prefix}"}}}},"visible":true,"status":{},"columnNames":[]},"8":{"slot":8,"name":"target_prefix","elements":{"1":{"slot":1,"values":{"1":{"slot":1,"type":"STRING","value":"${target_prefix}"}}}},"visible":true,"status":{},"columnNames":[]},"9":{"slot":9,"name":"metadata_stage_table_name","elements":{"1":{"slot":1,"values":{"1":{"slot":1,"type":"STRING","value":""}}}},"visible":true,"status":{},"columnNames":[]},"10":{"slot":10,"name":"stage_table_name","elements":{"1":{"slot":1,"values":{"1":{"slot":1,"type":"STRING","value":""}}}},"visible":true,"status":{},"columnNames":[]},"11":{"slot":11,"name":"target_table_name","elements":{"1":{"slot":1,"values":{"1":{"slot":1,"type":"STRING","value":""}}}},"visible":true,"status":{},"columnNames":[]},"12":{"slot":12,"name":"source_metadata_sql","elements":{"1":{"slot":1,"values":{"1":{"slot":1,"type":"STRING","value":""}}}},"visible":true,"status":{},"columnNames":[]},"13":{"slot":13,"name":"get_metadata_sql","elements":{"1":{"slot":1,"values":{"1":{"slot":1,"type":"STRING","value":""}}}},"visible":true,"status":{},"columnNames":[]},"14":{"slot":14,"name":"source_list","elements":{"1":{"slot":1,"values":{"1":{"slot":1,"type":"GRID","value":"source_list"}}},"2":{"slot":2,"values":{"1":{"slot":1,"type":"GRID","value":"table"},"2":{"slot":2,"type":"GRID","value":"column"},"3":{"slot":3,"type":"GRID","value":"incremental_column"},"4":{"slot":4,"type":"GRID","value":"key_column"}}}},"visible":true,"status":{},"columnNames":[]},"15":{"slot":15,"name":"random_suffix","elements":{"1":{"slot":1,"values":{"1":{"slot":1,"type":"STRING","value":""}}}},"visible":true,"status":{},"columnNames":[]}},"validationStatus":{},"status":{},"expectedFailure":null,"activationStatus":"ENABLED","outputIterationConnectorIDs":[],"inputIterationConnectorIDs":[]},"13239":{"id":13239,"inputCardinality":"ONE","outputCardinality":"MANY","connectorHint":"SUCCESS_FAIL","executionHint":"TRANSFORM","implementationID":-1180878087,"x":-1024,"y":16,"width":32,"height":32,"inputConnectorIDs":[13248],"outputSuccessConnectorIDs":[13249],"outputFailureConnectorIDs":[],"outputUnconditionalConnectorIDs":[],"outputTrueConnectorIDs":[],"outputFalseConnectorIDs":[],"exportMappings":{},"parameters":{"1":{"slot":1,"name":"Name","elements":{"1":{"slot":1,"values":{"1":{"slot":1,"type":"STRING","value":"Get source metadata"}}}},"visible":true,"status":{},"columnNames":[]},"2":{"slot":2,"name":"Job Name","elements":{"1":{"slot":1,"values":{"1":{"slot":1,"type":"STRING","value":"Xero - 4 - Query Source"}}}},"visible":true,"status":{},"columnNames":[]},"3":{"slot":3,"name":"","elements":{},"visible":false,"status":{},"columnNames":[]},"4":{"slot":4,"name":"Set Scalar Variables","elements":{"1":{"slot":1,"values":{"1":{"slot":1,"type":"STRING","value":"target_table_name"},"2":{"slot":2,"type":"STRING","value":"${target_table_name}"}}},"2":{"slot":2,"values":{"1":{"slot":1,"type":"STRING","value":"sql"},"2":{"slot":2,"type":"STRING","value":"${source_metadata_sql}"}}},"3":{"slot":3,"values":{"1":{"slot":1,"type":"STRING","value":"warehouse"},"2":{"slot":2,"type":"STRING","value":"${stage_warehouse}"}}},"4":{"slot":4,"values":{"1":{"slot":1,"type":"STRING","value":"database"},"2":{"slot":2,"type":"STRING","value":"${stage_database}"}}},"5":{"slot":5,"values":{"1":{"slot":1,"type":"STRING","value":"schema"},"2":{"slot":2,"type":"STRING","value":"${stage_schema}"}}},"6":{"slot":6,"values":{"1":{"slot":1,"type":"STRING","value":"project"},"2":{"slot":2,"type":"STRING","value":"${stage_project}"}}},"7":{"slot":7,"values":{"1":{"slot":1,"type":"STRING","value":"dataset"},"2":{"slot":2,"type":"STRING","value":"${stage_dataset}"}}},"8":{"slot":8,"values":{"1":{"slot":1,"type":"STRING","value":"table"},"2":{"slot":2,"type":"STRING","value":"${metadata_stage_table_name}"}}},"9":{"slot":9,"values":{"1":{"slot":1,"type":"STRING","value":"bucket_name"},"2":{"slot":2,"type":"STRING","value":"${bucket_name}"}}},"10":{"slot":10,"values":{"1":{"slot":1,"type":"STRING","value":"dist_style"},"2":{"slot":2,"type":"STRING","value":"${dist_style}"}}},"11":{"slot":11,"values":{"1":{"slot":1,"type":"STRING","value":"encryption_method"},"2":{"slot":2,"type":"STRING","value":"${encryption_method}"}}},"12":{"slot":12,"values":{"1":{"slot":1,"type":"STRING","value":"kms_key_id"},"2":{"slot":2,"type":"STRING","value":"${kms_key_id}"}}},"13":{"slot":13,"values":{"1":{"slot":1,"type":"STRING","value":"staging_type"},"2":{"slot":2,"type":"STRING","value":"${staging_type}"}}},"14":{"slot":14,"values":{"1":{"slot":1,"type":"STRING","value":"storage_account"},"2":{"slot":2,"type":"STRING","value":"${storage_account}"}}},"15":{"slot":15,"values":{"1":{"slot":1,"type":"STRING","value":"blob_container"},"2":{"slot":2,"type":"STRING","value":"${blob_container}"}}},"16":{"slot":16,"values":{"1":{"slot":1,"type":"STRING","value":"cloud_storage_area"},"2":{"slot":2,"type":"STRING","value":"${cloud_storage_area}"}}},"17":{"slot":17,"values":{"1":{"slot":1,"type":"STRING","value":"oauth"},"2":{"slot":2,"type":"STRING","value":"${oauth}"}}}},"visible":true,"status":{},"columnNames":[]},"5":{"slot":5,"name":"Set Grid Variables","elements":{"1":{"slot":1,"elements":{"1":{"slot":1,"values":{"1":{"slot":1,"type":"GRID","value":"advanced_connection_options"}}},"2":{"slot":2,"values":{"1":{"slot":1,"type":"GRID","value":"parameter"},"2":{"slot":2,"type":"GRID","value":"value"}}}},"values":{"1":{"slot":1,"type":"STRING","value":"advanced_connection_options"},"2":{"slot":2,"type":"STRING","value":"grid"}}}},"visible":true,"status":{},"columnNames":[]},"6":{"slot":6,"name":"Melt Name","elements":{"1":{"slot":1,"values":{"1":{"slot":1,"type":"STRING","value":"Xero Incremental Load"}}}},"visible":true,"status":{},"columnNames":[]},"7":{"slot":7,"name":"Melt Package","elements":{"1":{"slot":1,"values":{"1":{"slot":1,"type":"STRING","value":"Matillion.Incremental"}}}},"visible":true,"status":{},"columnNames":[]},"8":{"slot":8,"name":"Melt Revision","elements":{"1":{"slot":1,"values":{"1":{"slot":1,"type":"INTEGER","value":"1"}}}},"visible":true,"status":{},"columnNames":[]},"9":{"slot":9,"name":"Melt Load Type","elements":{"1":{"slot":1,"values":{"1":{"slot":1,"type":"STRING","value":"DYNAMIC"}}}},"visible":true,"status":{},"columnNames":[]}},"validationStatus":{},"status":{},"expectedFailure":null,"activationStatus":"ENABLED","outputIterationConnectorIDs":[],"inputIterationConnectorIDs":[]},"13240":{"id":13240,"inputCardinality":"ONE","outputCardinality":"MANY","connectorHint":"SUCCESS_FAIL","executionHint":"EXECUTE","implementationID":-1096987946,"x":-848,"y":16,"width":32,"height":32,"inputConnectorIDs":[13249],"outputSuccessConnectorIDs":[13252],"outputFailureConnectorIDs":[],"outputUnconditionalConnectorIDs":[],"outputTrueConnectorIDs":[],"outputFalseConnectorIDs":[],"exportMappings":{},"gridExportMappings":{"1":{"slot":1,"fromGrid":"source_table_metadata","toGrid":"source_table_metadata","columnMapToFrom":{"is_nullable":"is_nullable","is_key":"is_key","length":"length","column_name":"column_name","data_type_name":"data_type_name","numeric_scale":"numeric_scale","ordinal":"ordinal"},"validationStatus":null,"validationMessage":null}},"parameters":{"1":{"slot":1,"name":"Name","elements":{"1":{"slot":1,"values":{"1":{"slot":1,"type":"STRING","value":"Check Source Metadata"}}}},"visible":true,"status":{},"columnNames":[]},"2":{"slot":2,"name":"metadata_stage_table_name","elements":{"1":{"slot":1,"values":{"1":{"slot":1,"type":"STRING","value":"${metadata_stage_table_name}"}}}},"visible":true,"status":{},"columnNames":[]},"3":{"slot":3,"name":"target_table_name","elements":{"1":{"slot":1,"values":{"1":{"slot":1,"type":"STRING","value":"${target_table_name}"}}}},"visible":true,"status":{},"columnNames":[]},"4":{"slot":4,"name":"get_metadata_sql","elements":{"1":{"slot":1,"values":{"1":{"slot":1,"type":"STRING","value":"${get_metadata_sql}"}}}},"visible":true,"status":{},"columnNames":[]},"5":{"slot":5,"name":"stage_database","elements":{"1":{"slot":1,"values":{"1":{"slot":1,"type":"STRING","value":"${stage_database}"}}}},"visible":true,"status":{},"columnNames":[]},"6":{"slot":6,"name":"stage_schema","elements":{"1":{"slot":1,"values":{"1":{"slot":1,"type":"STRING","value":"${stage_schema}"}}}},"visible":true,"status":{},"columnNames":[]},"7":{"slot":7,"name":"stage_project","elements":{"1":{"slot":1,"values":{"1":{"slot":1,"type":"STRING","value":"${stage_project}"}}}},"visible":true,"status":{},"columnNames":[]},"8":{"slot":8,"name":"stage_dataset","elements":{"1":{"slot":1,"values":{"1":{"slot":1,"type":"STRING","value":"${stage_dataset}"}}}},"visible":true,"status":{},"columnNames":[]},"9":{"slot":9,"name":"source_table_metadata","elements":{},"visible":true,"status":{},"columnNames":[]},"10":{"slot":10,"name":"error_message","elements":{"1":{"slot":1,"values":{"1":{"slot":1,"type":"STRING","value":""}}}},"visible":true,"status":{},"columnNames":[]}},"validationStatus":{},"status":{},"expectedFailure":null,"activationStatus":"ENABLED","outputIterationConnectorIDs":[],"inputIterationConnectorIDs":[]},"13241":{"id":13241,"inputCardinality":"ONE","outputCardinality":"MANY","connectorHint":"SUCCESS_FAIL","executionHint":"EXECUTE","implementationID":-231597779,"x":-448,"y":16,"width":32,"height":32,"inputConnectorIDs":[13254],"outputSuccessConnectorIDs":[13245],"outputFailureConnectorIDs":[],"outputUnconditionalConnectorIDs":[],"outputTrueConnectorIDs":[],"outputFalseConnectorIDs":[],"exportMappings":{},"gridExportMappings":{"1":{"slot":1,"fromGrid":"required_columns","toGrid":"source_list","columnMapToFrom":{"column":"column_name","key_column":"key_column","incremental_column":"incremental_column","table":"table"},"validationStatus":null,"validationMessage":null}},"parameters":{"1":{"slot":1,"name":"Name","elements":{"1":{"slot":1,"values":{"1":{"slot":1,"type":"STRING","value":"Check Required Columns"}}}},"visible":true,"status":{},"columnNames":[]},"2":{"slot":2,"name":"target_table_name","elements":{"1":{"slot":1,"values":{"1":{"slot":1,"type":"STRING","value":"${target_table_name}"}}}},"visible":true,"status":{},"columnNames":[]},"3":{"slot":3,"name":"actual_columns","elements":{"1":{"slot":1,"values":{"1":{"slot":1,"type":"GRID","value":"source_table_metadata"}}},"2":{"slot":2,"values":{"1":{"slot":1,"type":"GRID","value":"column_name"}}}},"visible":true,"status":{},"columnNames":[]},"4":{"slot":4,"name":"required_columns","elements":{"1":{"slot":1,"values":{"1":{"slot":1,"type":"GRID","value":"source_list"}}},"2":{"slot":2,"values":{"1":{"slot":1,"type":"GRID","value":"table"},"2":{"slot":2,"type":"GRID","value":"column"},"3":{"slot":3,"type":"GRID","value":"incremental_column"},"4":{"slot":4,"type":"GRID","value":"key_column"}}}},"visible":true,"status":{},"columnNames":[]},"5":{"slot":5,"name":"missing_columns","elements":{},"visible":true,"status":{},"columnNames":[]},"6":{"slot":6,"name":"error_message","elements":{"1":{"slot":1,"values":{"1":{"slot":1,"type":"STRING","value":""}}}},"visible":true,"status":{},"columnNames":[]}},"validationStatus":{},"status":{},"expectedFailure":null,"activationStatus":"ENABLED","outputIterationConnectorIDs":[],"inputIterationConnectorIDs":[]},"13242":{"id":13242,"inputCardinality":"ONE","outputCardinality":"MANY","connectorHint":"SUCCESS_FAIL","executionHint":"TRANSFORM","implementationID":-1180878087,"x":256,"y":16,"width":32,"height":32,"inputConnectorIDs":[13246],"outputSuccessConnectorIDs":[13251],"outputFailureConnectorIDs":[],"outputUnconditionalConnectorIDs":[],"outputTrueConnectorIDs":[],"outputFalseConnectorIDs":[],"exportMappings":{},"parameters":{"1":{"slot":1,"name":"Name","elements":{"1":{"slot":1,"values":{"1":{"slot":1,"type":"STRING","value":"Create stage table to check metadata"}}}},"visible":true,"status":{},"columnNames":[]},"2":{"slot":2,"name":"Job Name","elements":{"1":{"slot":1,"values":{"1":{"slot":1,"type":"STRING","value":"Xero - 4 - Query Source"}}}},"visible":true,"status":{},"columnNames":[]},"3":{"slot":3,"name":"","elements":{},"visible":false,"status":{},"columnNames":[]},"4":{"slot":4,"name":"Set Scalar Variables","elements":{"1":{"slot":1,"values":{"1":{"slot":1,"type":"STRING","value":"target_table_name"},"2":{"slot":2,"type":"STRING","value":"${target_table_name}"}}},"2":{"slot":2,"values":{"1":{"slot":1,"type":"STRING","value":"sql"},"2":{"slot":2,"type":"STRING","value":"${stage_single_sql}"}}},"3":{"slot":3,"values":{"1":{"slot":1,"type":"STRING","value":"warehouse"},"2":{"slot":2,"type":"STRING","value":"${stage_warehouse}"}}},"4":{"slot":4,"values":{"1":{"slot":1,"type":"STRING","value":"database"},"2":{"slot":2,"type":"STRING","value":"${stage_database}"}}},"5":{"slot":5,"values":{"1":{"slot":1,"type":"STRING","value":"schema"},"2":{"slot":2,"type":"STRING","value":"${stage_schema}"}}},"6":{"slot":6,"values":{"1":{"slot":1,"type":"STRING","value":"project"},"2":{"slot":2,"type":"STRING","value":"${stage_project}"}}},"7":{"slot":7,"values":{"1":{"slot":1,"type":"STRING","value":"dataset"},"2":{"slot":2,"type":"STRING","value":"${stage_dataset}"}}},"8":{"slot":8,"values":{"1":{"slot":1,"type":"STRING","value":"table"},"2":{"slot":2,"type":"STRING","value":"${stage_table_name}"}}},"9":{"slot":9,"values":{"1":{"slot":1,"type":"STRING","value":"bucket_name"},"2":{"slot":2,"type":"STRING","value":"${bucket_name}"}}},"10":{"slot":10,"values":{"1":{"slot":1,"type":"STRING","value":"dist_style"},"2":{"slot":2,"type":"STRING","value":"${dist_style}"}}},"11":{"slot":11,"values":{"1":{"slot":1,"type":"STRING","value":"encryption_method"},"2":{"slot":2,"type":"STRING","value":"${encryption_method}"}}},"12":{"slot":12,"values":{"1":{"slot":1,"type":"STRING","value":"kms_key_id"},"2":{"slot":2,"type":"STRING","value":"${kms_key_id}"}}},"13":{"slot":13,"values":{"1":{"slot":1,"type":"STRING","value":"staging_type"},"2":{"slot":2,"type":"STRING","value":"${staging_type}"}}},"14":{"slot":14,"values":{"1":{"slot":1,"type":"STRING","value":"storage_account"},"2":{"slot":2,"type":"STRING","value":"${storage_account}"}}},"15":{"slot":15,"values":{"1":{"slot":1,"type":"STRING","value":"blob_container"},"2":{"slot":2,"type":"STRING","value":"${blob_container}"}}},"16":{"slot":16,"values":{"1":{"slot":1,"type":"STRING","value":"cloud_storage_area"},"2":{"slot":2,"type":"STRING","value":"${cloud_storage_area}"}}},"17":{"slot":17,"values":{"1":{"slot":1,"type":"STRING","value":"oauth"},"2":{"slot":2,"type":"STRING","value":"${oauth}"}}}},"visible":true,"status":{},"columnNames":[]},"5":{"slot":5,"name":"Set Grid Variables","elements":{"1":{"slot":1,"elements":{"1":{"slot":1,"values":{"1":{"slot":1,"type":"GRID","value":"advanced_connection_options"}}},"2":{"slot":2,"values":{"1":{"slot":1,"type":"GRID","value":"parameter"},"2":{"slot":2,"type":"GRID","value":"value"}}}},"values":{"1":{"slot":1,"type":"STRING","value":"advanced_connection_options"},"2":{"slot":2,"type":"STRING","value":"grid"}}}},"visible":true,"status":{},"columnNames":[]},"6":{"slot":6,"name":"Melt Name","elements":{"1":{"slot":1,"values":{"1":{"slot":1,"type":"STRING","value":"Xero Incremental Load"}}}},"visible":true,"status":{},"columnNames":[]},"7":{"slot":7,"name":"Melt Package","elements":{"1":{"slot":1,"values":{"1":{"slot":1,"type":"STRING","value":"Matillion.Incremental"}}}},"visible":true,"status":{},"columnNames":[]},"8":{"slot":8,"name":"Melt Revision","elements":{"1":{"slot":1,"values":{"1":{"slot":1,"type":"INTEGER","value":"1"}}}},"visible":true,"status":{},"columnNames":[]},"9":{"slot":9,"name":"Melt Load Type","elements":{"1":{"slot":1,"values":{"1":{"slot":1,"type":"STRING","value":"DYNAMIC"}}}},"visible":true,"status":{},"columnNames":[]}},"validationStatus":{},"status":{},"expectedFailure":null,"activationStatus":"ENABLED","outputIterationConnectorIDs":[],"inputIterationConnectorIDs":[]},"13243":{"id":13243,"inputCardinality":"ONE","outputCardinality":"MANY","connectorHint":"SUCCESS_FAIL","executionHint":"EXECUTE","implementationID":332733136,"x":528,"y":16,"width":32,"height":32,"inputConnectorIDs":[13251],"outputSuccessConnectorIDs":[13736],"outputFailureConnectorIDs":[],"outputUnconditionalConnectorIDs":[],"outputTrueConnectorIDs":[],"outputFalseConnectorIDs":[],"exportMappings":{"1":{"slot":1,"fromId":null,"fromName":"load_strategy","mapTo":"load_strategy","validationStatus":null,"validationMessage":null},"2":{"slot":2,"fromId":null,"fromName":"stage_sql","mapTo":"stage_sql","validationStatus":null,"validationMessage":null},"3":{"slot":3,"fromId":null,"fromName":"inc_col_type","mapTo":"inc_col_type","validationStatus":null,"validationMessage":null},"4":{"slot":4,"fromId":null,"fromName":"max_value","mapTo":"max_value","validationStatus":null,"validationMessage":null},"5":{"slot":5,"fromId":null,"fromName":"incremental_column","mapTo":"incremental_column","validationStatus":null,"validationMessage":null}},"gridExportMappings":{"1":{"slot":1,"fromGrid":"stage_table_metadata","toGrid":"stage_table_metadata","columnMapToFrom":{"type":"type","column_name":"column_name"},"validationStatus":null,"validationMessage":null}},"parameters":{"1":{"slot":1,"name":"Name","elements":{"1":{"slot":1,"values":{"1":{"slot":1,"type":"STRING","value":"Decide Load Strategy"}}}},"visible":true,"status":{},"columnNames":[]},"2":{"slot":2,"name":"table_name","elements":{"1":{"slot":1,"values":{"1":{"slot":1,"type":"STRING","value":"${table_name}"}}}},"visible":true,"status":{},"columnNames":[]},"3":{"slot":3,"name":"source_column_list","elements":{"1":{"slot":1,"values":{"1":{"slot":1,"type":"STRING","value":"${source_column_list}"}}}},"visible":true,"status":{},"columnNames":[]},"4":{"slot":4,"name":"key_column_count","elements":{"1":{"slot":1,"values":{"1":{"slot":1,"type":"STRING","value":"${key_column_count}"}}}},"visible":true,"status":{},"columnNames":[]},"5":{"slot":5,"name":"incremental_column_count","elements":{"1":{"slot":1,"values":{"1":{"slot":1,"type":"STRING","value":"${incremental_column_count}"}}}},"visible":true,"status":{},"columnNames":[]},"6":{"slot":6,"name":"incremental_column","elements":{"1":{"slot":1,"values":{"1":{"slot":1,"type":"STRING","value":"${incremental_column}"}}}},"visible":true,"status":{},"columnNames":[]},"7":{"slot":7,"name":"skip_inc_col_check","elements":{"1":{"slot":1,"values":{"1":{"slot":1,"type":"STRING","value":"N"}}}},"visible":true,"status":{},"columnNames":[]},"8":{"slot":8,"name":"timestamp_precision","elements":{"1":{"slot":1,"values":{"1":{"slot":1,"type":"STRING","value":"${timestamp_precision}"}}}},"visible":true,"status":{},"columnNames":[]},"9":{"slot":9,"name":"safely_cast_datetime_to_date","elements":{"1":{"slot":1,"values":{"1":{"slot":1,"type":"STRING","value":"N"}}}},"visible":true,"status":{},"columnNames":[]},"10":{"slot":10,"name":"encloseTableString","elements":{"1":{"slot":1,"values":{"1":{"slot":1,"type":"STRING","value":"TRUE"}}}},"visible":true,"status":{},"columnNames":[]},"11":{"slot":11,"name":"source_enclosing_char_s","elements":{"1":{"slot":1,"values":{"1":{"slot":1,"type":"STRING","value":"\""}}}},"visible":true,"status":{},"columnNames":[]},"12":{"slot":12,"name":"source_enclosing_char_e","elements":{"1":{"slot":1,"values":{"1":{"slot":1,"type":"STRING","value":"\""}}}},"visible":true,"status":{},"columnNames":[]},"13":{"slot":13,"name":"target_platform","elements":{"1":{"slot":1,"values":{"1":{"slot":1,"type":"STRING","value":"${target_platform}"}}}},"visible":true,"status":{},"columnNames":[]},"14":{"slot":14,"name":"stage_database","elements":{"1":{"slot":1,"values":{"1":{"slot":1,"type":"STRING","value":"${stage_database}"}}}},"visible":true,"status":{},"columnNames":[]},"15":{"slot":15,"name":"stage_schema","elements":{"1":{"slot":1,"values":{"1":{"slot":1,"type":"STRING","value":"${stage_schema}"}}}},"visible":true,"status":{},"columnNames":[]},"16":{"slot":16,"name":"stage_project","elements":{"1":{"slot":1,"values":{"1":{"slot":1,"type":"STRING","value":"${stage_project}"}}}},"visible":true,"status":{},"columnNames":[]},"17":{"slot":17,"name":"stage_dataset","elements":{"1":{"slot":1,"values":{"1":{"slot":1,"type":"STRING","value":"${stage_dataset}"}}}},"visible":true,"status":{},"columnNames":[]},"18":{"slot":18,"name":"stage_table_name","elements":{"1":{"slot":1,"values":{"1":{"slot":1,"type":"STRING","value":"${stage_table_name}"}}}},"visible":true,"status":{},"columnNames":[]},"19":{"slot":19,"name":"target_database","elements":{"1":{"slot":1,"values":{"1":{"slot":1,"type":"STRING","value":"${target_database}"}}}},"visible":true,"status":{},"columnNames":[]},"20":{"slot":20,"name":"target_schema","elements":{"1":{"slot":1,"values":{"1":{"slot":1,"type":"STRING","value":"${target_schema}"}}}},"visible":true,"status":{},"columnNames":[]},"21":{"slot":21,"name":"target_project","elements":{"1":{"slot":1,"values":{"1":{"slot":1,"type":"STRING","value":"${target_project}"}}}},"visible":true,"status":{},"columnNames":[]},"22":{"slot":22,"name":"target_dataset","elements":{"1":{"slot":1,"values":{"1":{"slot":1,"type":"STRING","value":"${target_dataset}"}}}},"visible":true,"status":{},"columnNames":[]},"23":{"slot":23,"name":"target_table_name","elements":{"1":{"slot":1,"values":{"1":{"slot":1,"type":"STRING","value":"${target_table_name}"}}}},"visible":true,"status":{},"columnNames":[]},"24":{"slot":24,"name":"fix_target_data_types","elements":{"1":{"slot":1,"values":{"1":{"slot":1,"type":"STRING","value":"Yes"}}}},"visible":true,"status":{},"columnNames":[]},"25":{"slot":25,"name":"load_strategy","elements":{"1":{"slot":1,"values":{"1":{"slot":1,"type":"STRING","value":""}}}},"visible":true,"status":{},"columnNames":[]},"26":{"slot":26,"name":"max_value","elements":{"1":{"slot":1,"values":{"1":{"slot":1,"type":"STRING","value":""}}}},"visible":true,"status":{},"columnNames":[]},"27":{"slot":27,"name":"stage_sql","elements":{"1":{"slot":1,"values":{"1":{"slot":1,"type":"STRING","value":""}}}},"visible":true,"status":{},"columnNames":[]},"28":{"slot":28,"name":"inc_col_type","elements":{"1":{"slot":1,"values":{"1":{"slot":1,"type":"STRING","value":""}}}},"visible":true,"status":{},"columnNames":[]},"29":{"slot":29,"name":"stage_table_metadata","elements":{},"visible":true,"status":{},"columnNames":[]},"30":{"slot":30,"name":"messages","elements":{},"visible":true,"status":{},"columnNames":[]}},"validationStatus":{},"status":{},"expectedFailure":null,"activationStatus":"ENABLED","outputIterationConnectorIDs":[],"inputIterationConnectorIDs":[]},"13244":{"id":13244,"inputCardinality":"ONE","outputCardinality":"MANY","connectorHint":"SUCCESS_FAIL","executionHint":"TRANSFORM","implementationID":-1180878087,"x":768,"y":16,"width":32,"height":32,"inputConnectorIDs":[13740],"outputSuccessConnectorIDs":[13255],"outputFailureConnectorIDs":[],"outputUnconditionalConnectorIDs":[],"outputTrueConnectorIDs":[],"outputFalseConnectorIDs":[],"exportMappings":{"1":{"slot":1,"fromId":null,"fromName":"row_count","mapTo":"static_row_count","validationStatus":null,"validationMessage":null}},"parameters":{"1":{"slot":1,"name":"Name","elements":{"1":{"slot":1,"values":{"1":{"slot":1,"type":"STRING","value":"Stage data"}}}},"visible":true,"status":{},"columnNames":[]},"2":{"slot":2,"name":"Job Name","elements":{"1":{"slot":1,"values":{"1":{"slot":1,"type":"STRING","value":"Xero - 4 - Query Source"}}}},"visible":true,"status":{},"columnNames":[]},"3":{"slot":3,"name":"","elements":{},"visible":false,"status":{},"columnNames":[]},"4":{"slot":4,"name":"Set Scalar Variables","elements":{"1":{"slot":1,"values":{"1":{"slot":1,"type":"STRING","value":"target_table_name"},"2":{"slot":2,"type":"STRING","value":"${target_table_name}"}}},"2":{"slot":2,"values":{"1":{"slot":1,"type":"STRING","value":"sql"},"2":{"slot":2,"type":"STRING","value":"${stage_sql}"}}},"3":{"slot":3,"values":{"1":{"slot":1,"type":"STRING","value":"warehouse"},"2":{"slot":2,"type":"STRING","value":"${stage_warehouse}"}}},"4":{"slot":4,"values":{"1":{"slot":1,"type":"STRING","value":"database"},"2":{"slot":2,"type":"STRING","value":"${stage_database}"}}},"5":{"slot":5,"values":{"1":{"slot":1,"type":"STRING","value":"schema"},"2":{"slot":2,"type":"STRING","value":"${stage_schema}"}}},"6":{"slot":6,"values":{"1":{"slot":1,"type":"STRING","value":"project"},"2":{"slot":2,"type":"STRING","value":"${stage_project}"}}},"7":{"slot":7,"values":{"1":{"slot":1,"type":"STRING","value":"dataset"},"2":{"slot":2,"type":"STRING","value":"${stage_dataset}"}}},"8":{"slot":8,"values":{"1":{"slot":1,"type":"STRING","value":"table"},"2":{"slot":2,"type":"STRING","value":"${stage_table_name}"}}},"9":{"slot":9,"values":{"1":{"slot":1,"type":"STRING","value":"bucket_name"},"2":{"slot":2,"type":"STRING","value":"${bucket_name}"}}},"10":{"slot":10,"values":{"1":{"slot":1,"type":"STRING","value":"dist_style"},"2":{"slot":2,"type":"STRING","value":"${dist_style}"}}},"11":{"slot":11,"values":{"1":{"slot":1,"type":"STRING","value":"encryption_method"},"2":{"slot":2,"type":"STRING","value":"${encryption_method}"}}},"12":{"slot":12,"values":{"1":{"slot":1,"type":"STRING","value":"kms_key_id"},"2":{"slot":2,"type":"STRING","value":"${kms_key_id}"}}},"13":{"slot":13,"values":{"1":{"slot":1,"type":"STRING","value":"staging_type"},"2":{"slot":2,"type":"STRING","value":"${staging_type}"}}},"14":{"slot":14,"values":{"1":{"slot":1,"type":"STRING","value":"storage_account"},"2":{"slot":2,"type":"STRING","value":"${storage_account}"}}},"15":{"slot":15,"values":{"1":{"slot":1,"type":"STRING","value":"blob_container"},"2":{"slot":2,"type":"STRING","value":"${blob_container}"}}},"16":{"slot":16,"values":{"1":{"slot":1,"type":"STRING","value":"cloud_storage_area"},"2":{"slot":2,"type":"STRING","value":"${cloud_storage_area}"}}},"17":{"slot":17,"values":{"1":{"slot":1,"type":"STRING","value":"oauth"},"2":{"slot":2,"type":"STRING","value":"${oauth}"}}}},"visible":true,"status":{},"columnNames":[]},"5":{"slot":5,"name":"Set Grid Variables","elements":{"1":{"slot":1,"elements":{"1":{"slot":1,"values":{"1":{"slot":1,"type":"GRID","value":"advanced_connection_options"}}},"2":{"slot":2,"values":{"1":{"slot":1,"type":"GRID","value":"parameter"},"2":{"slot":2,"type":"GRID","value":"value"}}}},"values":{"1":{"slot":1,"type":"STRING","value":"advanced_connection_options"},"2":{"slot":2,"type":"STRING","value":"grid"}}}},"visible":true,"status":{},"columnNames":[]},"6":{"slot":6,"name":"Melt Name","elements":{"1":{"slot":1,"values":{"1":{"slot":1,"type":"STRING","value":"Xero Incremental Load"}}}},"visible":true,"status":{},"columnNames":[]},"7":{"slot":7,"name":"Melt Package","elements":{"1":{"slot":1,"values":{"1":{"slot":1,"type":"STRING","value":"Matillion.Incremental"}}}},"visible":true,"status":{},"columnNames":[]},"8":{"slot":8,"name":"Melt Revision","elements":{"1":{"slot":1,"values":{"1":{"slot":1,"type":"INTEGER","value":"1"}}}},"visible":true,"status":{},"columnNames":[]},"9":{"slot":9,"name":"Melt Load Type","elements":{"1":{"slot":1,"values":{"1":{"slot":1,"type":"STRING","value":"DYNAMIC"}}}},"visible":true,"status":{},"columnNames":[]}},"validationStatus":{},"status":{},"expectedFailure":null,"activationStatus":"ENABLED","outputIterationConnectorIDs":[],"inputIterationConnectorIDs":[]},"13716":{"id":13716,"inputCardinality":"ONE","outputCardinality":"MANY","connectorHint":"SUCCESS_FAIL","executionHint":"EXECUTE","implementationID":-1773186960,"x":656,"y":16,"width":32,"height":32,"inputConnectorIDs":[13736],"outputSuccessConnectorIDs":[13740],"outputFailureConnectorIDs":[],"outputUnconditionalConnectorIDs":[],"outputTrueConnectorIDs":[],"outputFalseConnectorIDs":[],"exportMappings":{},"parameters":{"1":{"slot":1,"name":"Name","elements":{"1":{"slot":1,"values":{"1":{"slot":1,"type":"STRING","value":"Build stage_sql"}}}},"visible":true,"status":{},"columnNames":[]},"2":{"slot":2,"name":"Script","elements":{"1":{"slot":1,"values":{"1":{"slot":1,"type":"STRING","value":"msg = \\\n\"\"\"\nIncremental Column is {}\nWith Type {}\n\"\"\".format(incremental_column, inc_col_type)\n\nprint(msg)\n\ndef operator_gte_check(table_name, column_name):\n  \n  if column_name != 'Date':\n    return False \n  \n  if table_name in ('ExpenseClaimPayments', 'BankTransfers', 'HistoryBankTransfers'):\n    return True \n  \n  return False \n\n\nsql = \\\n\"\"\"\nSELECT \n\t{}\nFROM \n\t\"{}\"\n\"\"\".format(source_column_list, table_name)\n\n# for incremental loads, use the psuedo column Min_ID\nif load_strategy in ('APPEND INSERT', 'UPDATE'):\n    \n    # fix only required for BQ timestamp objects\n    if target_platform == 'BigQuery' and inc_col_type =='timestamp':\n        \n        from datetime import datetime as dt\n        # get timestamp\n        new_value = float(max_value)\n        # create a date object and format as required \n        _date = dt.fromtimestamp(new_value).isoformat(sep = ' ', timespec = timestamp_precision)\n        print(f\"Parsed timestamp ({max_value}) to {_date}.\")\n        \n    else:\n        _date = max_value\n        \n    operator = '>'\n    if operator_gte_check(table_name, incremental_column):\n        ## TODO: Probably need to mutate _date again so its has the correct date format \n        ## i.e YYYY-MM-DD HH:MM:SS.MS -> YYYY-MM-DD \n        operator = '>='\n        \n    sql += \"\"\"\\n WHERE \"{}\" {} '{}' \"\"\".format(incremental_column, operator, _date)\n    \n    \ncontext.updateVariable('stage_sql', sql)\n\nprint(\"Variable `stage_sql`:\\n{}\".format(stage_sql))"}}}},"visible":true,"status":{},"columnNames":[]},"3":{"slot":3,"name":"Interpreter","elements":{"1":{"slot":1,"values":{"1":{"slot":1,"type":"STRING","value":"Python 3"}}}},"visible":true,"status":{},"columnNames":[]},"4":{"slot":4,"name":"Timeout","elements":{"1":{"slot":1,"values":{"1":{"slot":1,"type":"INTEGER","value":"360"}}}},"visible":true,"status":{},"columnNames":[]}},"validationStatus":{},"status":{},"expectedFailure":null,"activationStatus":"ENABLED","outputIterationConnectorIDs":[],"inputIterationConnectorIDs":[]}},"successConnectors":{"13245":{"id":13245,"sourceID":13241,"targetID":13234},"13246":{"id":13246,"sourceID":13237,"targetID":13242},"13247":{"id":13247,"sourceID":13234,"targetID":13237},"13248":{"id":13248,"sourceID":13238,"targetID":13239},"13249":{"id":13249,"sourceID":13239,"targetID":13240},"13250":{"id":13250,"sourceID":13235,"targetID":13232},"13251":{"id":13251,"sourceID":13242,"targetID":13243},"13252":{"id":13252,"sourceID":13240,"targetID":13236},"13254":{"id":13254,"sourceID":13236,"targetID":13241},"13255":{"id":13255,"sourceID":13244,"targetID":13235},"13736":{"id":13736,"sourceID":13243,"targetID":13716},"13740":{"id":13740,"sourceID":13716,"targetID":13244}},"failureConnectors":{},"unconditionalConnectors":{"13256":{"id":13256,"sourceID":13233,"targetID":13238}},"trueConnectors":{},"falseConnectors":{},"iterationConnectors":{},"noteConnectors":{},"canUndo":false,"undoCommand":"","undoCreated":0,"canRedo":false,"redoCommand":"","redoCreated":0,"notes":{"13227":{"id":13227,"x":-74,"y":-138,"width":483,"height":212,"text":"**Query to get stage schema**\n\nCreate a stage table so that we can check what names and data types are created in cloud data warehouse\n\nThe query has a LIMIT 1 clause applied so that we can get the source schema without needing to do a full download","colour":"e6e63c"},"13228":{"id":13228,"x":-554,"y":-140,"width":213,"height":215,"text":"**Check source contains all columns from source_list**\n\nIf requested columns are missing from the source, remove them from __source_list__\n\nIf any key columns are missing, clear any incremental column flag to force \"TRUNCATE INSERT\" load strategy later","colour":"e6e63c"},"13229":{"id":13229,"x":-315,"y":-137,"width":215,"height":213,"text":"**Parse input**\n\nParse input to \n- Generate column list\n- Identify incremental column\n- Get column counts","colour":"e6e63c"},"13230":{"id":13230,"x":-1242,"y":-137,"width":661,"height":213,"text":"** Check source metadata **\n\nQuery source metadata to find key columns\n- Add any missing key columns into __source_list__\n- If the table contains 'UpdatedDateUTC', 'CreatedDateUTC' or 'Date' add it to __source_list__ (if missing) and set as the incremental column\n","colour":"e6e63c"},"13231":{"id":13231,"x":439,"y":-137,"width":515,"height":213,"text":"** Stage data and load into target **\n\n- Decide load strategy of \"TRUNCATE INSERT\", \"APPEND INSERT\" or \"UPDATE\"\n- Execute the generated SQL and store in a stage table\n- Add data to target table based on load strategy","colour":"e6e63c"}},"variables":{"oauth":{"definition":{"name":"oauth","type":"TEXT","scope":"TASKBATCH","description":"","visibility":"PUBLIC"},"value":""},"max_value":{"definition":{"name":"max_value","type":"TEXT","scope":"TASKBATCH","description":"","visibility":"PRIVATE"},"value":""},"stage_sql":{"definition":{"name":"stage_sql","type":"TEXT","scope":"TASKBATCH","description":"","visibility":"PRIVATE"},"value":""},"dist_style":{"definition":{"name":"dist_style","type":"TEXT","scope":"TASKBATCH","description":"","visibility":"PUBLIC"},"value":""},"kms_key_id":{"definition":{"name":"kms_key_id","type":"TEXT","scope":"TASKBATCH","description":"","visibility":"PUBLIC"},"value":""},"table_name":{"definition":{"name":"table_name","type":"TEXT","scope":"TASKBATCH","description":"","visibility":"PUBLIC"},"value":""},"bucket_name":{"definition":{"name":"bucket_name","type":"TEXT","scope":"TASKBATCH","description":"","visibility":"PUBLIC"},"value":""},"log_metrics":{"definition":{"name":"log_metrics","type":"TEXT","scope":"TASKBATCH","description":"","visibility":"PUBLIC"},"value":""},"inc_col_type":{"definition":{"name":"inc_col_type","type":"TEXT","scope":"TASKBATCH","description":"","visibility":"PRIVATE"},"value":""},"stage_prefix":{"definition":{"name":"stage_prefix","type":"TEXT","scope":"TASKBATCH","description":"","visibility":"PUBLIC"},"value":""},"stage_schema":{"definition":{"name":"stage_schema","type":"TEXT","scope":"TASKBATCH","description":"","visibility":"PUBLIC"},"value":""},"staging_type":{"definition":{"name":"staging_type","type":"TEXT","scope":"TASKBATCH","description":"","visibility":"PUBLIC"},"value":""},"error_message":{"definition":{"name":"error_message","type":"TEXT","scope":"BRANCH","description":"","visibility":"PRIVATE"},"value":""},"load_strategy":{"definition":{"name":"load_strategy","type":"TEXT","scope":"TASKBATCH","description":"","visibility":"PRIVATE"},"value":""},"stage_dataset":{"definition":{"name":"stage_dataset","type":"TEXT","scope":"TASKBATCH","description":"","visibility":"PUBLIC"},"value":""},"stage_project":{"definition":{"name":"stage_project","type":"TEXT","scope":"TASKBATCH","description":"   ","visibility":"PUBLIC"},"value":""},"target_prefix":{"definition":{"name":"target_prefix","type":"TEXT","scope":"TASKBATCH","description":"","visibility":"PUBLIC"},"value":""},"target_schema":{"definition":{"name":"target_schema","type":"TEXT","scope":"TASKBATCH","description":"","visibility":"PUBLIC"},"value":""},"blob_container":{"definition":{"name":"blob_container","type":"TEXT","scope":"TASKBATCH","description":"","visibility":"PUBLIC"},"value":""},"stage_database":{"definition":{"name":"stage_database","type":"TEXT","scope":"TASKBATCH","description":"","visibility":"PUBLIC"},"value":""},"target_dataset":{"definition":{"name":"target_dataset","type":"TEXT","scope":"TASKBATCH","description":"","visibility":"PUBLIC"},"value":""},"target_project":{"definition":{"name":"target_project","type":"TEXT","scope":"TASKBATCH","description":"","visibility":"PUBLIC"},"value":""},"stage_warehouse":{"definition":{"name":"stage_warehouse","type":"TEXT","scope":"TASKBATCH","description":"","visibility":"PUBLIC"},"value":""},"storage_account":{"definition":{"name":"storage_account","type":"TEXT","scope":"TASKBATCH","description":"","visibility":"PUBLIC"},"value":""},"target_database":{"definition":{"name":"target_database","type":"TEXT","scope":"TASKBATCH","description":"","visibility":"PUBLIC"},"value":""},"target_platform":{"definition":{"name":"target_platform","type":"TEXT","scope":"TASKBATCH","description":"","visibility":"PUBLIC"},"value":""},"get_metadata_sql":{"definition":{"name":"get_metadata_sql","type":"TEXT","scope":"TASKBATCH","description":"","visibility":"PRIVATE"},"value":""},"key_column_count":{"definition":{"name":"key_column_count","type":"DECIMAL","scope":"TASKBATCH","description":"","visibility":"PRIVATE"},"value":""},"stage_single_sql":{"definition":{"name":"stage_single_sql","type":"TEXT","scope":"TASKBATCH","description":"","visibility":"PRIVATE"},"value":""},"stage_table_name":{"definition":{"name":"stage_table_name","type":"TEXT","scope":"TASKBATCH","description":"","visibility":"PUBLIC"},"value":""},"static_row_count":{"definition":{"name":"static_row_count","type":"DECIMAL","scope":"TASKBATCH","description":"","visibility":"PRIVATE"},"value":""},"target_warehouse":{"definition":{"name":"target_warehouse","type":"TEXT","scope":"TASKBATCH","description":"","visibility":"PUBLIC"},"value":""},"encryption_method":{"definition":{"name":"encryption_method","type":"TEXT","scope":"TASKBATCH","description":"","visibility":"PUBLIC"},"value":null},"target_table_name":{"definition":{"name":"target_table_name","type":"TEXT","scope":"TASKBATCH","description":"","visibility":"PRIVATE"},"value":""},"cloud_storage_area":{"definition":{"name":"cloud_storage_area","type":"TEXT","scope":"TASKBATCH","description":"The cloud storage area name that should be used when loading bq tables.","visibility":"PUBLIC"},"value":""},"incremental_column":{"definition":{"name":"incremental_column","type":"TEXT","scope":"TASKBATCH","description":"","visibility":"PRIVATE"},"value":""},"source_column_list":{"definition":{"name":"source_column_list","type":"TEXT","scope":"TASKBATCH","description":"","visibility":"PRIVATE"},"value":""},"source_metadata_sql":{"definition":{"name":"source_metadata_sql","type":"TEXT","scope":"TASKBATCH","description":"","visibility":"PRIVATE"},"value":""},"timestamp_precision":{"definition":{"name":"timestamp_precision","type":"TEXT","scope":"TASKBATCH","description":"","visibility":"PRIVATE"},"value":"microseconds"},"incremental_column_count":{"definition":{"name":"incremental_column_count","type":"DECIMAL","scope":"TASKBATCH","description":"","visibility":"PRIVATE"},"value":"0"},"metadata_stage_table_name":{"definition":{"name":"metadata_stage_table_name","type":"TEXT","scope":"TASKBATCH","description":"","visibility":"PUBLIC"},"value":""}},"grids":{"key_columns":{"definition":{"name":"key_columns","scope":"TASKBATCH","definitions":[{"name":"column","type":"TEXT"}],"description":"","visibility":"PRIVATE"},"values":[]},"source_list":{"definition":{"name":"source_list","scope":"TASKBATCH","definitions":[{"name":"table","type":"TEXT"},{"name":"column","type":"TEXT"},{"name":"incremental_column","type":"DECIMAL"},{"name":"key_column","type":"DECIMAL"}],"description":"","visibility":"PUBLIC"},"values":[]},"stage_table_metadata":{"definition":{"name":"stage_table_metadata","scope":"TASKBATCH","definitions":[{"name":"column_name","type":"TEXT"},{"name":"type","type":"TEXT"}],"description":"","visibility":"PRIVATE"},"values":[]},"source_table_metadata":{"definition":{"name":"source_table_metadata","scope":"TASKBATCH","definitions":[{"name":"column_name","type":"TEXT"},{"name":"data_type_name","type":"TEXT"},{"name":"length","type":"TEXT"},{"name":"numeric_scale","type":"TEXT"},{"name":"is_key","type":"TEXT"},{"name":"is_nullable","type":"TEXT"},{"name":"ordinal","type":"TEXT"}],"description":"","visibility":"PRIVATE"},"values":[]},"advanced_connection_options":{"definition":{"name":"advanced_connection_options","scope":"TASKBATCH","definitions":[{"name":"parameter","type":"TEXT"},{"name":"value","type":"TEXT"}],"description":"","visibility":"PUBLIC"},"values":[]}}},"Xero - 4 - Query Source":{"id":-2,"revision":1,"created":1613406965557,"timestamp":1613406965557,"components":{"13215":{"id":13215,"inputCardinality":"ONE","outputCardinality":"ZERO","connectorHint":"UNCONDITIONAL","executionHint":"FLOW","implementationID":515156205,"x":-320,"y":336,"width":32,"height":32,"inputConnectorIDs":[13222],"outputSuccessConnectorIDs":[],"outputFailureConnectorIDs":[],"outputUnconditionalConnectorIDs":[],"outputTrueConnectorIDs":[],"outputFalseConnectorIDs":[],"exportMappings":{},"parameters":{"1":{"slot":1,"name":"Name","elements":{"1":{"slot":1,"values":{"1":{"slot":1,"type":"STRING","value":"End Failure"}}}},"visible":true,"status":{},"columnNames":[]}},"validationStatus":{},"status":{},"expectedFailure":null,"activationStatus":"ENABLED","outputIterationConnectorIDs":[],"inputIterationConnectorIDs":[]},"13216":{"id":13216,"inputCardinality":"ZERO","outputCardinality":"MANY","connectorHint":"UNCONDITIONAL","executionHint":"FLOW","implementationID":444132438,"x":-496,"y":-16,"width":32,"height":32,"inputConnectorIDs":[],"outputSuccessConnectorIDs":[],"outputFailureConnectorIDs":[],"outputUnconditionalConnectorIDs":[13313],"outputTrueConnectorIDs":[],"outputFalseConnectorIDs":[],"exportMappings":{},"parameters":{"1":{"slot":1,"name":"Name","elements":{"1":{"slot":1,"values":{"1":{"slot":1,"type":"STRING","value":"Start"}}}},"visible":true,"status":{},"columnNames":[]}},"validationStatus":{},"status":{},"expectedFailure":null,"activationStatus":"ENABLED","outputIterationConnectorIDs":[],"inputIterationConnectorIDs":[]},"13217":{"id":13217,"inputCardinality":"ONE","outputCardinality":"MANY","connectorHint":"SUCCESS_FAIL","executionHint":"EXECUTE","implementationID":-1773186960,"x":-320,"y":96,"width":32,"height":32,"inputConnectorIDs":[13549],"outputSuccessConnectorIDs":[13223],"outputFailureConnectorIDs":[],"outputUnconditionalConnectorIDs":[],"outputTrueConnectorIDs":[],"outputFalseConnectorIDs":[],"exportMappings":{},"parameters":{"1":{"slot":1,"name":"Name","elements":{"1":{"slot":1,"values":{"1":{"slot":1,"type":"STRING","value":"Print error"}}}},"visible":true,"status":{},"columnNames":[]},"2":{"slot":2,"name":"Script","elements":{"1":{"slot":1,"values":{"1":{"slot":1,"type":"STRING","value":"msg = \\\n\"\"\"There was an issue whilst retrieving data from the source. \nThe attempted query was: \\n{q}\nError: {e}\n\"\"\".format(q = sql, e = error_message) \nprint(msg)\n"}}}},"visible":true,"status":{},"columnNames":[]},"3":{"slot":3,"name":"Interpreter","elements":{"1":{"slot":1,"values":{"1":{"slot":1,"type":"STRING","value":"Python 3"}}}},"visible":true,"status":{},"columnNames":[]},"4":{"slot":4,"name":"Timeout","elements":{"1":{"slot":1,"values":{"1":{"slot":1,"type":"INTEGER","value":"360"}}}},"visible":true,"status":{},"columnNames":[]}},"validationStatus":{},"status":{},"expectedFailure":null,"activationStatus":"ENABLED","outputIterationConnectorIDs":[],"inputIterationConnectorIDs":[]},"13218":{"id":13218,"inputCardinality":"ONE","outputCardinality":"MANY","connectorHint":"SUCCESS_FAIL","executionHint":"EXECUTE","implementationID":-1334993426,"x":-320,"y":208,"width":32,"height":32,"inputConnectorIDs":[13223],"outputSuccessConnectorIDs":[13222],"outputFailureConnectorIDs":[],"outputUnconditionalConnectorIDs":[],"outputTrueConnectorIDs":[],"outputFalseConnectorIDs":[],"exportMappings":{},"parameters":{"1":{"slot":1,"name":"Name","elements":{"1":{"slot":1,"values":{"1":{"slot":1,"type":"STRING","value":"Log error"}}}},"visible":true,"status":{},"columnNames":[]},"2":{"slot":2,"name":"Level","elements":{"1":{"slot":1,"values":{"1":{"slot":1,"type":"STRING","value":"error"}}}},"visible":true,"status":{},"columnNames":[]},"3":{"slot":3,"name":"Namespace","elements":{"1":{"slot":1,"values":{"1":{"slot":1,"type":"STRING","value":"${target_table_name}"}}}},"visible":true,"status":{},"columnNames":[]},"4":{"slot":4,"name":"Messages","elements":{"1":{"slot":1,"values":{"1":{"slot":1,"type":"STRING","value":"Unable to retrieve data from source"},"2":{"slot":2,"type":"STRING","value":"${error_message}"}}}},"visible":true,"status":{},"columnNames":[]}},"validationStatus":{},"status":{},"expectedFailure":null,"activationStatus":"ENABLED","outputIterationConnectorIDs":[],"inputIterationConnectorIDs":[]},"13219":{"id":13219,"inputCardinality":"ONE","outputCardinality":"ZERO","connectorHint":"UNCONDITIONAL","executionHint":"FLOW","implementationID":-1946388514,"x":-144,"y":-16,"width":32,"height":32,"inputConnectorIDs":[13546],"outputSuccessConnectorIDs":[],"outputFailureConnectorIDs":[],"outputUnconditionalConnectorIDs":[],"outputTrueConnectorIDs":[],"outputFalseConnectorIDs":[],"exportMappings":{},"parameters":{"1":{"slot":1,"name":"Name","elements":{"1":{"slot":1,"values":{"1":{"slot":1,"type":"STRING","value":"End Success"}}}},"visible":true,"status":{},"columnNames":[]}},"validationStatus":{},"status":{},"expectedFailure":null,"activationStatus":"ENABLED","outputIterationConnectorIDs":[],"inputIterationConnectorIDs":[]},"13294":{"id":13294,"inputCardinality":"ONE","outputCardinality":"MANY","connectorHint":"SUCCESS_FAIL","executionHint":"EXECUTE","implementationID":-1025498028,"x":-320,"y":-16,"width":32,"height":32,"inputConnectorIDs":[13313],"outputSuccessConnectorIDs":[13546],"outputFailureConnectorIDs":[13549],"outputUnconditionalConnectorIDs":[],"outputTrueConnectorIDs":[],"outputFalseConnectorIDs":[],"exportMappings":{"1":{"slot":1,"fromId":null,"fromName":"Row Count","mapTo":"row_count","validationStatus":null,"validationMessage":null},"2":{"slot":2,"fromId":null,"fromName":"Message","mapTo":"error_message","validationStatus":null,"validationMessage":null}},"parameters":{"1":{"slot":1,"name":"Name","elements":{"1":{"slot":1,"values":{"1":{"slot":1,"type":"STRING","value":"Query source"}}}},"visible":true,"status":{},"columnNames":[]},"2":{"slot":2,"name":"Basic/Advanced Mode","elements":{"1":{"slot":1,"values":{"1":{"slot":1,"type":"STRING","value":"Advanced"}}}},"visible":true,"status":{},"columnNames":[]},"3":{"slot":3,"name":"Authentication","elements":{"1":{"slot":1,"values":{"1":{"slot":1,"type":"STRING","value":"${oauth}"}}}},"visible":true,"status":{},"columnNames":[]},"6":{"slot":6,"name":"Data Source","elements":{"1":{"slot":1,"values":{"1":{"slot":1,"type":"STRING","value":""}}}},"visible":false,"status":{},"columnNames":[]},"7":{"slot":7,"name":"Data Selection","elements":{},"visible":false,"status":{},"columnNames":[]},"8":{"slot":8,"name":"Data Source Filter","elements":{},"visible":false,"status":{},"columnNames":[]},"9":{"slot":9,"name":"Combine Filters","elements":{"1":{"slot":1,"values":{"1":{"slot":1,"type":"STRING","value":"And"}}}},"visible":false,"status":{},"columnNames":[]},"10":{"slot":10,"name":"SQL Query","elements":{"1":{"slot":1,"values":{"1":{"slot":1,"type":"STRING","value":"${sql}"}}}},"visible":true,"status":{},"columnNames":[]},"11":{"slot":11,"name":"Limit","elements":{"1":{"slot":1,"values":{"1":{"slot":1,"type":"INTEGER","value":"100"}}}},"visible":false,"status":{},"columnNames":[]},"12":{"slot":12,"name":"Connection Options","elements":{"1":{"slot":1,"values":{"1":{"slot":1,"type":"GRID","value":"advanced_connection_options"}}},"2":{"slot":2,"values":{"1":{"slot":1,"type":"GRID","value":"parameter"},"2":{"slot":2,"type":"GRID","value":"value"}}}},"visible":true,"status":{},"columnNames":[]},"13":{"slot":13,"name":"Target Table","elements":{"1":{"slot":1,"values":{"1":{"slot":1,"type":"STRING","value":"${table}"}}}},"visible":true,"status":{},"columnNames":[]},"14":{"slot":14,"name":"Distribution Style","elements":{"1":{"slot":1,"values":{"1":{"slot":1,"type":"STRING","value":"${dist_style}"}}}},"visible":true,"status":{},"columnNames":[]},"15":{"slot":15,"name":"Distribution Key","elements":{"1":{"slot":1,"values":{"1":{"slot":1,"type":"STRING","value":""}}}},"visible":false,"status":{},"columnNames":[]},"16":{"slot":16,"name":"Sort Key","elements":{},"visible":true,"status":{},"columnNames":[]},"17":{"slot":17,"name":"Schema","elements":{"1":{"slot":1,"values":{"1":{"slot":1,"type":"STRING","value":"${schema}"}}}},"visible":true,"status":{},"columnNames":[]},"18":{"slot":18,"name":"Sort Key Options","elements":{"1":{"slot":1,"values":{"1":{"slot":1,"type":"STRING","value":""}}}},"visible":false,"status":{},"columnNames":[]},"19":{"slot":19,"name":"S3 Staging Area","elements":{"1":{"slot":1,"values":{"1":{"slot":1,"type":"STRING","value":"${bucket_name}"}}}},"visible":true,"status":{},"columnNames":[]},"20":{"slot":20,"name":"Data Schema","elements":{"1":{"slot":1,"values":{"1":{"slot":1,"type":"STRING","value":"ACCOUNTING"}}}},"visible":false,"status":{},"columnNames":[]},"21":{"slot":21,"name":"Primary Key","elements":{},"visible":true,"status":{},"columnNames":[]},"95":{"slot":95,"name":"","elements":{},"visible":false,"status":{},"columnNames":[]},"96":{"slot":96,"name":"Location","elements":{"1":{"slot":1,"values":{"1":{"slot":1,"type":"STRING","value":"s3://<bucket>/<path>"}}}},"visible":false,"status":{},"columnNames":[]},"97":{"slot":97,"name":"Target Table","elements":{"1":{"slot":1,"values":{"1":{"slot":1,"type":"STRING","value":""}}}},"visible":false,"status":{},"columnNames":[]},"98":{"slot":98,"name":"Schema","elements":{"1":{"slot":1,"values":{"1":{"slot":1,"type":"STRING","value":""}}}},"visible":false,"status":{},"columnNames":[]},"99":{"slot":99,"name":"Type","elements":{"1":{"slot":1,"values":{"1":{"slot":1,"type":"STRING","value":"Standard"}}}},"visible":true,"status":{},"columnNames":[]},"1001":{"slot":1001,"name":"","elements":{},"visible":false,"status":{},"columnNames":[]},"1013":{"slot":1013,"name":"Auto Debug","elements":{"1":{"slot":1,"values":{"1":{"slot":1,"type":"STRING","value":"Off"}}}},"visible":true,"status":{},"columnNames":[]},"1014":{"slot":1014,"name":"Debug Level","elements":{"1":{"slot":1,"values":{"1":{"slot":1,"type":"INTEGER","value":"3"}}}},"visible":false,"status":{},"columnNames":[]},"1034":{"slot":1034,"name":"Load Options","elements":{},"visible":true,"status":{},"columnNames":[]},"40000":{"slot":40000,"name":"Encryption","elements":{"1":{"slot":1,"values":{"1":{"slot":1,"type":"STRING","value":"${encryption_method}"}}}},"visible":true,"status":{},"columnNames":[]},"40001":{"slot":40001,"name":"KMS Key ID","elements":{"1":{"slot":1,"values":{"1":{"slot":1,"type":"STRING","value":"${kms_key_id}"}}}},"visible":false,"status":{},"columnNames":[]}},"validationStatus":{},"status":{},"expectedFailure":null,"activationStatus":"ENABLED","outputIterationConnectorIDs":[],"inputIterationConnectorIDs":[]}},"successConnectors":{"13222":{"id":13222,"sourceID":13218,"targetID":13215},"13223":{"id":13223,"sourceID":13217,"targetID":13218},"13546":{"id":13546,"sourceID":13294,"targetID":13219}},"failureConnectors":{"13549":{"id":13549,"sourceID":13294,"targetID":13217}},"unconditionalConnectors":{"13313":{"id":13313,"sourceID":13216,"targetID":13294}},"trueConnectors":{},"falseConnectors":{},"iterationConnectors":{},"noteConnectors":{},"canUndo":false,"undoCommand":"","undoCreated":0,"canRedo":false,"redoCommand":"","redoCreated":0,"notes":{"13214":{"id":13214,"x":-424,"y":-137,"width":210,"height":404,"text":"**Query source table**\n\nIf an error occurs,\n- print the details to the task history\n- log the error\n- end iteration with failure","colour":"e6e63c"}},"variables":{"sql":{"definition":{"name":"sql","type":"TEXT","scope":"TASKBATCH","description":"","visibility":"PUBLIC"},"value":""},"oauth":{"definition":{"name":"oauth","type":"TEXT","scope":"TASKBATCH","description":"","visibility":"PUBLIC"},"value":""},"table":{"definition":{"name":"table","type":"TEXT","scope":"TASKBATCH","description":"","visibility":"PUBLIC"},"value":""},"schema":{"definition":{"name":"schema","type":"TEXT","scope":"TASKBATCH","description":"","visibility":"PUBLIC"},"value":""},"dataset":{"definition":{"name":"dataset","type":"TEXT","scope":"TASKBATCH","description":"","visibility":"PUBLIC"},"value":""},"project":{"definition":{"name":"project","type":"TEXT","scope":"TASKBATCH","description":"","visibility":"PUBLIC"},"value":""},"database":{"definition":{"name":"database","type":"TEXT","scope":"TASKBATCH","description":"","visibility":"PUBLIC"},"value":""},"row_count":{"definition":{"name":"row_count","type":"DECIMAL","scope":"TASKBATCH","description":"","visibility":"PUBLIC"},"value":"0"},"warehouse":{"definition":{"name":"warehouse","type":"TEXT","scope":"TASKBATCH","description":"","visibility":"PUBLIC"},"value":""},"dist_style":{"definition":{"name":"dist_style","type":"TEXT","scope":"TASKBATCH","description":"","visibility":"PUBLIC"},"value":""},"kms_key_id":{"definition":{"name":"kms_key_id","type":"TEXT","scope":"TASKBATCH","description":"","visibility":"PUBLIC"},"value":""},"bucket_name":{"definition":{"name":"bucket_name","type":"TEXT","scope":"TASKBATCH","description":"","visibility":"PUBLIC"},"value":""},"staging_type":{"definition":{"name":"staging_type","type":"TEXT","scope":"TASKBATCH","description":"","visibility":"PUBLIC"},"value":""},"error_message":{"definition":{"name":"error_message","type":"TEXT","scope":"TASKBATCH","description":"","visibility":"PUBLIC"},"value":""},"blob_container":{"definition":{"name":"blob_container","type":"TEXT","scope":"TASKBATCH","description":"","visibility":"PUBLIC"},"value":""},"storage_account":{"definition":{"name":"storage_account","type":"TEXT","scope":"TASKBATCH","description":"","visibility":"PUBLIC"},"value":""},"encryption_method":{"definition":{"name":"encryption_method","type":"TEXT","scope":"TASKBATCH","description":"","visibility":"PUBLIC"},"value":""},"target_table_name":{"definition":{"name":"target_table_name","type":"TEXT","scope":"TASKBATCH","description":"","visibility":"PUBLIC"},"value":""},"cloud_storage_area":{"definition":{"name":"cloud_storage_area","type":"TEXT","scope":"TASKBATCH","description":"","visibility":"PUBLIC"},"value":""}},"grids":{"advanced_connection_options":{"definition":{"name":"advanced_connection_options","scope":"BRANCH","definitions":[{"name":"parameter","type":"TEXT"},{"name":"value","type":"TEXT"}],"description":"","visibility":"PUBLIC"},"values":[]}}},"Xero - 1 - Orchestrator":{"id":-2,"revision":5,"created":1613407736681,"timestamp":1613407736681,"components":{"121":{"id":121,"inputCardinality":"ONE","outputCardinality":"MANY","connectorHint":"SUCCESS_FAIL","executionHint":"EXECUTE","implementationID":-1773186960,"x":-320,"y":0,"width":32,"height":32,"inputConnectorIDs":[1871],"outputSuccessConnectorIDs":[156],"outputFailureConnectorIDs":[],"outputUnconditionalConnectorIDs":[],"outputTrueConnectorIDs":[],"outputFalseConnectorIDs":[],"exportMappings":{},"parameters":{"1":{"slot":1,"name":"Name","elements":{"1":{"slot":1,"values":{"1":{"slot":1,"type":"STRING","value":"Get unique table names"}}}},"visible":true,"status":{},"columnNames":[]},"2":{"slot":2,"name":"Script","elements":{"1":{"slot":1,"values":{"1":{"slot":1,"type":"STRING","value":"source_list = context.getGridVariable('input_source_list')\n\n# get distinct list of table names from source_list\ndistinct_table_names = list(set([table for table, col in source_list]))\n\n# convert to list of lists to store in grid variable\ndistinct_table_names = [[name] for name in sorted(distinct_table_names)]\ncontext.updateGridVariable(\"distinct_table_names\", distinct_table_names)\n\nprint(f\"source_list: {source_list}\")\nprint(f\"distinct_table_names: {distinct_table_names}\")\n"}}}},"visible":true,"status":{},"columnNames":[]},"3":{"slot":3,"name":"Interpreter","elements":{"1":{"slot":1,"values":{"1":{"slot":1,"type":"STRING","value":"Python 3"}}}},"visible":true,"status":{},"columnNames":[]},"4":{"slot":4,"name":"Timeout","elements":{"1":{"slot":1,"values":{"1":{"slot":1,"type":"INTEGER","value":"360"}}}},"visible":true,"status":{},"columnNames":[]}},"validationStatus":{},"status":{},"expectedFailure":null,"activationStatus":"ENABLED","outputIterationConnectorIDs":[],"inputIterationConnectorIDs":[]},"122":{"id":122,"inputCardinality":"ONE","outputCardinality":"MANY","connectorHint":"SUCCESS_FAIL","executionHint":"EXECUTE","implementationID":-1773186960,"x":-160,"y":0,"width":32,"height":32,"inputConnectorIDs":[156],"outputSuccessConnectorIDs":[154],"outputFailureConnectorIDs":[],"outputUnconditionalConnectorIDs":[],"outputTrueConnectorIDs":[],"outputFalseConnectorIDs":[],"exportMappings":{},"parameters":{"1":{"slot":1,"name":"Name","elements":{"1":{"slot":1,"values":{"1":{"slot":1,"type":"STRING","value":"Update environment default"}}}},"visible":true,"status":{},"columnNames":[]},"2":{"slot":2,"name":"Script","elements":{"1":{"slot":1,"values":{"1":{"slot":1,"type":"STRING","value":"# Script to replace any [Environment Default] entries with the schema name\n\nif target_platform == 'Redshift':\n  \n    if stage_schema == '[Environment Default]':\n        context.updateVariable('stage_schema', environment_default_schema)\n  \n    if target_schema == '[Environment Default]':\n        context.updateVariable('target_schema', environment_default_schema)\n\nelif target_platform == 'Snowflake':\n    \n    if stage_schema == '[Environment Default]':\n        context.updateVariable('stage_schema', environment_default_schema)\n  \n    if target_schema == '[Environment Default]':\n        context.updateVariable('target_schema', environment_default_schema)\n\n    if stage_database == '[Environment Default]':\n        context.updateVariable('stage_database', environment_database)\n\n    if target_database == '[Environment Default]':\n        context.updateVariable('target_database', environment_database)\n  \nelif target_platform == 'BigQuery':\n  \n    if stage_dataset == '[Environment Default]':\n        context.updateVariable('stage_dataset', environment_default_schema)\n\n    if target_dataset == '[Environment Default]':\n        context.updateVariable('target_dataset', environment_default_schema)\n\n    if stage_project == '[Environment Default]':\n        context.updateVariable('stage_project', environment_database)\n\n    if target_project == '[Environment Default]':\n        context.updateVariable('target_project', environment_database)\n"}}}},"visible":true,"status":{},"columnNames":[]},"3":{"slot":3,"name":"Interpreter","elements":{"1":{"slot":1,"values":{"1":{"slot":1,"type":"STRING","value":"Python 3"}}}},"visible":true,"status":{},"columnNames":[]},"4":{"slot":4,"name":"Timeout","elements":{"1":{"slot":1,"values":{"1":{"slot":1,"type":"INTEGER","value":"360"}}}},"visible":true,"status":{},"columnNames":[]}},"validationStatus":{},"status":{},"expectedFailure":null,"activationStatus":"ENABLED","outputIterationConnectorIDs":[],"inputIterationConnectorIDs":[]},"123":{"id":123,"inputCardinality":"ONE","outputCardinality":"MANY","connectorHint":"SUCCESS_FAIL","executionHint":"TRANSFORM","implementationID":-1180878087,"x":160,"y":32,"width":32,"height":32,"inputConnectorIDs":[],"outputSuccessConnectorIDs":[],"outputFailureConnectorIDs":[],"outputUnconditionalConnectorIDs":[],"outputTrueConnectorIDs":[],"outputFalseConnectorIDs":[],"exportMappings":{},"parameters":{"1":{"slot":1,"name":"Name","elements":{"1":{"slot":1,"values":{"1":{"slot":1,"type":"STRING","value":"2 - Stage Object"}}}},"visible":true,"status":{},"columnNames":[]},"2":{"slot":2,"name":"Job Name","elements":{"1":{"slot":1,"values":{"1":{"slot":1,"type":"STRING","value":"Xero - 2 - Stage and Load"}}}},"visible":true,"status":{},"columnNames":[]},"3":{"slot":3,"name":"","elements":{},"visible":false,"status":{},"columnNames":[]},"4":{"slot":4,"name":"Set Scalar Variables","elements":{"1":{"slot":1,"values":{"1":{"slot":1,"type":"STRING","value":"bucket_name"},"2":{"slot":2,"type":"STRING","value":"${s3_bucket_name}"}}},"2":{"slot":2,"values":{"1":{"slot":1,"type":"STRING","value":"stage_prefix"},"2":{"slot":2,"type":"STRING","value":"${stage_prefix}"}}},"3":{"slot":3,"values":{"1":{"slot":1,"type":"STRING","value":"stage_schema"},"2":{"slot":2,"type":"STRING","value":"${stage_schema}"}}},"4":{"slot":4,"values":{"1":{"slot":1,"type":"STRING","value":"target_prefix"},"2":{"slot":2,"type":"STRING","value":"${target_prefix}"}}},"5":{"slot":5,"values":{"1":{"slot":1,"type":"STRING","value":"target_schema"},"2":{"slot":2,"type":"STRING","value":"${target_schema}"}}},"6":{"slot":6,"values":{"1":{"slot":1,"type":"STRING","value":"table_name"},"2":{"slot":2,"type":"STRING","value":"${table_name}"}}},"7":{"slot":7,"values":{"1":{"slot":1,"type":"STRING","value":"dist_style"},"2":{"slot":2,"type":"STRING","value":"${dist_style}"}}},"8":{"slot":8,"values":{"1":{"slot":1,"type":"STRING","value":"target_platform"},"2":{"slot":2,"type":"STRING","value":"${target_platform}"}}},"9":{"slot":9,"values":{"1":{"slot":1,"type":"STRING","value":"target_database"},"2":{"slot":2,"type":"STRING","value":"${target_database}"}}},"10":{"slot":10,"values":{"1":{"slot":1,"type":"STRING","value":"target_warehouse"},"2":{"slot":2,"type":"STRING","value":"${target_warehouse}"}}},"11":{"slot":11,"values":{"1":{"slot":1,"type":"STRING","value":"stage_database"},"2":{"slot":2,"type":"STRING","value":"${stage_database}"}}},"12":{"slot":12,"values":{"1":{"slot":1,"type":"STRING","value":"stage_warehouse"},"2":{"slot":2,"type":"STRING","value":"${stage_warehouse}"}}},"13":{"slot":13,"values":{"1":{"slot":1,"type":"STRING","value":"target_dataset"},"2":{"slot":2,"type":"STRING","value":"${target_dataset}"}}},"14":{"slot":14,"values":{"1":{"slot":1,"type":"STRING","value":"target_project"},"2":{"slot":2,"type":"STRING","value":"${target_project}"}}},"15":{"slot":15,"values":{"1":{"slot":1,"type":"STRING","value":"stage_dataset"},"2":{"slot":2,"type":"STRING","value":"${stage_dataset}"}}},"16":{"slot":16,"values":{"1":{"slot":1,"type":"STRING","value":"stage_project"},"2":{"slot":2,"type":"STRING","value":"${stage_project}"}}},"17":{"slot":17,"values":{"1":{"slot":1,"type":"STRING","value":"cloud_storage_area"},"2":{"slot":2,"type":"STRING","value":"${cloud_storage_area}"}}},"18":{"slot":18,"values":{"1":{"slot":1,"type":"STRING","value":"storage_account"},"2":{"slot":2,"type":"STRING","value":"${storage_account}"}}},"19":{"slot":19,"values":{"1":{"slot":1,"type":"STRING","value":"blob_container"},"2":{"slot":2,"type":"STRING","value":"${blob_container}"}}},"20":{"slot":20,"values":{"1":{"slot":1,"type":"STRING","value":"encryption_method"},"2":{"slot":2,"type":"STRING","value":"${encryption_method}"}}},"21":{"slot":21,"values":{"1":{"slot":1,"type":"STRING","value":"kms_key_id"},"2":{"slot":2,"type":"STRING","value":"${kms_key_id}"}}},"22":{"slot":22,"values":{"1":{"slot":1,"type":"STRING","value":"staging_type"},"2":{"slot":2,"type":"STRING","value":"${staging_type}"}}},"23":{"slot":23,"values":{"1":{"slot":1,"type":"STRING","value":"log_metrics"},"2":{"slot":2,"type":"STRING","value":"${log_metrics}"}}},"24":{"slot":24,"values":{"1":{"slot":1,"type":"STRING","value":"oauth"},"2":{"slot":2,"type":"STRING","value":"${oauth}"}}}},"visible":true,"status":{},"columnNames":[]},"5":{"slot":5,"name":"Set Grid Variables","elements":{"1":{"slot":1,"elements":{"1":{"slot":1,"values":{"1":{"slot":1,"type":"GRID","value":"advanced_connection_options"}}},"2":{"slot":2,"values":{"1":{"slot":1,"type":"GRID","value":"parameter"},"2":{"slot":2,"type":"GRID","value":"value"}}}},"values":{"1":{"slot":1,"type":"STRING","value":"advanced_connection_options"},"2":{"slot":2,"type":"STRING","value":"grid"}}},"2":{"slot":2,"elements":{"1":{"slot":1,"values":{"1":{"slot":1,"type":"GRID","value":"source_list"}}},"2":{"slot":2,"values":{"1":{"slot":1,"type":"GRID","value":"table"},"2":{"slot":2,"type":"GRID","value":"column"},"3":{"slot":3,"type":"GRID","value":"incremental_column"},"4":{"slot":4,"type":"GRID","value":"key_column"}}}},"values":{"1":{"slot":1,"type":"STRING","value":"source_list"},"2":{"slot":2,"type":"STRING","value":"grid"}}}},"visible":true,"status":{},"columnNames":[]},"6":{"slot":6,"name":"Melt Name","elements":{"1":{"slot":1,"values":{"1":{"slot":1,"type":"STRING","value":"Xero Incremental Load"}}}},"visible":true,"status":{},"columnNames":[]},"7":{"slot":7,"name":"Melt Package","elements":{"1":{"slot":1,"values":{"1":{"slot":1,"type":"STRING","value":"Matillion.Incremental"}}}},"visible":true,"status":{},"columnNames":[]},"8":{"slot":8,"name":"Melt Revision","elements":{"1":{"slot":1,"values":{"1":{"slot":1,"type":"INTEGER","value":"1"}}}},"visible":true,"status":{},"columnNames":[]},"9":{"slot":9,"name":"Melt Load Type","elements":{"1":{"slot":1,"values":{"1":{"slot":1,"type":"STRING","value":"DYNAMIC"}}}},"visible":true,"status":{},"columnNames":[]}},"validationStatus":{},"status":{},"expectedFailure":null,"activationStatus":"ENABLED","outputIterationConnectorIDs":[],"inputIterationConnectorIDs":[162]},"124":{"id":124,"inputCardinality":"ONE","outputCardinality":"MANY","connectorHint":"SUCCESS_FAIL","executionHint":"ITERATE","implementationID":-424773870,"x":160,"y":0,"width":32,"height":16,"inputConnectorIDs":[155],"outputSuccessConnectorIDs":[157,158],"outputFailureConnectorIDs":[161],"outputUnconditionalConnectorIDs":[],"outputTrueConnectorIDs":[],"outputFalseConnectorIDs":[],"exportMappings":{"1":{"slot":1,"fromId":null,"fromName":"Duration","mapTo":"logged_duration","validationStatus":null,"validationMessage":null}},"parameters":{"1":{"slot":1,"name":"Name","elements":{"1":{"slot":1,"values":{"1":{"slot":1,"type":"STRING","value":"Iterate over distinct table names"}}}},"visible":true,"status":{},"columnNames":[]},"3":{"slot":3,"name":"Grid Variable","elements":{"1":{"slot":1,"values":{"1":{"slot":1,"type":"STRING","value":"distinct_table_names"}}}},"visible":true,"status":{},"columnNames":[]},"4":{"slot":4,"name":"Grid Variable Mapping","elements":{"1":{"slot":1,"values":{"1":{"slot":1,"type":"STRING","value":"table_name"},"2":{"slot":2,"type":"STRING","value":"table_name"}}}},"visible":true,"status":{},"columnNames":[]},"5":{"slot":5,"name":"Break on Failure","elements":{"1":{"slot":1,"values":{"1":{"slot":1,"type":"STRING","value":"No"}}}},"visible":false,"status":{},"columnNames":[]},"6":{"slot":6,"name":"Concurrency","elements":{"1":{"slot":1,"values":{"1":{"slot":1,"type":"STRING","value":"${load_concurrent}"}}}},"visible":true,"status":{},"columnNames":[]},"999":{"slot":999,"name":"Record Values In Task History","elements":{"1":{"slot":1,"values":{"1":{"slot":1,"type":"STRING","value":"Yes"}}}},"visible":true,"status":{},"columnNames":[]}},"validationStatus":{},"status":{},"expectedFailure":null,"activationStatus":"ENABLED","outputIterationConnectorIDs":[162],"inputIterationConnectorIDs":[]},"125":{"id":125,"inputCardinality":"ZERO","outputCardinality":"MANY","connectorHint":"UNCONDITIONAL","executionHint":"FLOW","implementationID":444132438,"x":-640,"y":0,"width":32,"height":32,"inputConnectorIDs":[],"outputSuccessConnectorIDs":[],"outputFailureConnectorIDs":[],"outputUnconditionalConnectorIDs":[1868],"outputTrueConnectorIDs":[],"outputFalseConnectorIDs":[],"exportMappings":{},"parameters":{"1":{"slot":1,"name":"Name","elements":{"1":{"slot":1,"values":{"1":{"slot":1,"type":"STRING","value":"Start"}}}},"visible":true,"status":{},"columnNames":[]}},"validationStatus":{},"status":{},"expectedFailure":null,"activationStatus":"ENABLED","outputIterationConnectorIDs":[],"inputIterationConnectorIDs":[]},"126":{"id":126,"inputCardinality":"ONE","outputCardinality":"ZERO","connectorHint":"UNCONDITIONAL","executionHint":"FLOW","implementationID":-1946388514,"x":320,"y":0,"width":32,"height":32,"inputConnectorIDs":[157],"outputSuccessConnectorIDs":[],"outputFailureConnectorIDs":[],"outputUnconditionalConnectorIDs":[],"outputTrueConnectorIDs":[],"outputFalseConnectorIDs":[],"exportMappings":{},"parameters":{"1":{"slot":1,"name":"Name","elements":{"1":{"slot":1,"values":{"1":{"slot":1,"type":"STRING","value":"End Success"}}}},"visible":true,"status":{},"columnNames":[]}},"validationStatus":{},"status":{},"expectedFailure":null,"activationStatus":"ENABLED","outputIterationConnectorIDs":[],"inputIterationConnectorIDs":[]},"127":{"id":127,"inputCardinality":"ONE","outputCardinality":"ZERO","connectorHint":"UNCONDITIONAL","executionHint":"FLOW","implementationID":515156205,"x":320,"y":80,"width":32,"height":32,"inputConnectorIDs":[161],"outputSuccessConnectorIDs":[],"outputFailureConnectorIDs":[],"outputUnconditionalConnectorIDs":[],"outputTrueConnectorIDs":[],"outputFalseConnectorIDs":[],"exportMappings":{},"parameters":{"1":{"slot":1,"name":"Name","elements":{"1":{"slot":1,"values":{"1":{"slot":1,"type":"STRING","value":"End Failure"}}}},"visible":true,"status":{},"columnNames":[]}},"validationStatus":{},"status":{},"expectedFailure":null,"activationStatus":"ENABLED","outputIterationConnectorIDs":[],"inputIterationConnectorIDs":[]},"151":{"id":151,"inputCardinality":"ONE","outputCardinality":"MANY","connectorHint":"SUCCESS_FAIL","executionHint":"EXECUTE","implementationID":-2112089365,"x":480,"y":-80,"width":32,"height":32,"inputConnectorIDs":[159],"outputSuccessConnectorIDs":[],"outputFailureConnectorIDs":[],"outputUnconditionalConnectorIDs":[],"outputTrueConnectorIDs":[],"outputFalseConnectorIDs":[],"exportMappings":{},"parameters":{"1":{"slot":1,"name":"Name","elements":{"1":{"slot":1,"values":{"1":{"slot":1,"type":"STRING","value":"Log duration"}}}},"visible":true,"status":{},"columnNames":[]},"2":{"slot":2,"name":"Metrics","elements":{"1":{"slot":1,"values":{"1":{"slot":1,"type":"STRING","value":"duration"},"2":{"slot":2,"type":"STRING","value":"Numeric"},"3":{"slot":3,"type":"STRING","value":"${logged_duration}"}}}},"visible":true,"status":{},"columnNames":[]},"3":{"slot":3,"name":"Namespace","elements":{"1":{"slot":1,"values":{"1":{"slot":1,"type":"STRING","value":" "}}}},"visible":true,"status":{},"columnNames":[]}},"validationStatus":{},"status":{},"expectedFailure":null,"activationStatus":"ENABLED","outputIterationConnectorIDs":[],"inputIterationConnectorIDs":[]},"152":{"id":152,"inputCardinality":"ONE","outputCardinality":"MANY","connectorHint":"CONDITIONAL","executionHint":"FLOW","implementationID":-1357378929,"x":320,"y":-80,"width":32,"height":32,"inputConnectorIDs":[158],"outputSuccessConnectorIDs":[],"outputFailureConnectorIDs":[],"outputUnconditionalConnectorIDs":[],"outputTrueConnectorIDs":[159],"outputFalseConnectorIDs":[],"exportMappings":{},"parameters":{"1":{"slot":1,"name":"Name","elements":{"1":{"slot":1,"values":{"1":{"slot":1,"type":"STRING","value":"Log Metrics?"}}}},"visible":true,"status":{},"columnNames":[]},"2":{"slot":2,"name":"Mode","elements":{"1":{"slot":1,"values":{"1":{"slot":1,"type":"STRING","value":"Simple"}}}},"visible":true,"status":{},"columnNames":[]},"3":{"slot":3,"name":"Condition","elements":{"1":{"slot":1,"values":{"1":{"slot":1,"type":"STRING","value":"log_metrics"},"2":{"slot":2,"type":"STRING","value":"Is"},"3":{"slot":3,"type":"STRING","value":"Equal to"},"4":{"slot":4,"type":"STRING","value":"TRUE"}}}},"visible":true,"status":{},"columnNames":[]},"4":{"slot":4,"name":"Combine Conditions","elements":{"1":{"slot":1,"values":{"1":{"slot":1,"type":"STRING","value":"And"}}}},"visible":true,"status":{},"columnNames":[]},"5":{"slot":5,"name":"Condition","elements":{"1":{"slot":1,"values":{"1":{"slot":1,"type":"STRING","value":""}}}},"visible":false,"status":{},"columnNames":[]}},"validationStatus":{},"status":{},"expectedFailure":null,"activationStatus":"ENABLED","outputIterationConnectorIDs":[],"inputIterationConnectorIDs":[]},"153":{"id":153,"inputCardinality":"ONE","outputCardinality":"MANY","connectorHint":"SUCCESS_FAIL","executionHint":"EXECUTE","implementationID":-1773186960,"x":-2,"y":1,"width":32,"height":32,"inputConnectorIDs":[154],"outputSuccessConnectorIDs":[155],"outputFailureConnectorIDs":[],"outputUnconditionalConnectorIDs":[],"outputTrueConnectorIDs":[],"outputFalseConnectorIDs":[],"exportMappings":{},"parameters":{"1":{"slot":1,"name":"Name","elements":{"1":{"slot":1,"values":{"1":{"slot":1,"type":"STRING","value":"Adjust source list"}}}},"visible":true,"status":{},"columnNames":[]},"2":{"slot":2,"name":"Script","elements":{"1":{"slot":1,"values":{"1":{"slot":1,"type":"STRING","value":"fixed_grid = [\n [ x[0], x[1], '0', '0' ] for x in context.getGridVariable('input_source_list') \n]\n\n\ncontext.updateGridVariable('source_list', fixed_grid)\n\nprint(\"Updated Grid Variable `source_list`:{}\".format(fixed_grid))"}}}},"visible":true,"status":{},"columnNames":[]},"3":{"slot":3,"name":"Interpreter","elements":{"1":{"slot":1,"values":{"1":{"slot":1,"type":"STRING","value":"Python 3"}}}},"visible":true,"status":{},"columnNames":[]},"4":{"slot":4,"name":"Timeout","elements":{"1":{"slot":1,"values":{"1":{"slot":1,"type":"INTEGER","value":"360"}}}},"visible":true,"status":{},"columnNames":[]}},"validationStatus":{},"status":{},"expectedFailure":null,"activationStatus":"ENABLED","outputIterationConnectorIDs":[],"inputIterationConnectorIDs":[]},"1867":{"id":1867,"inputCardinality":"ONE","outputCardinality":"MANY","connectorHint":"SUCCESS_FAIL","executionHint":"EXECUTE","implementationID":-1773186960,"x":-481,"y":1,"width":32,"height":32,"inputConnectorIDs":[1868],"outputSuccessConnectorIDs":[1871],"outputFailureConnectorIDs":[],"outputUnconditionalConnectorIDs":[],"outputTrueConnectorIDs":[],"outputFalseConnectorIDs":[],"exportMappings":{},"parameters":{"1":{"slot":1,"name":"Name","elements":{"1":{"slot":1,"values":{"1":{"slot":1,"type":"STRING","value":"Set Schema Connection Option"}}}},"visible":true,"status":{},"columnNames":[]},"2":{"slot":2,"name":"Script","elements":{"1":{"slot":1,"values":{"1":{"slot":1,"type":"STRING","value":"allowed_schema_options = ['Accounting', 'Assets', 'Files', 'Projects']\n\nif schema not in allowed_schema_options:\n  raise Exception(\"Invalid Xero Schema supplied: {} - allowed options are {}\".format(schema, \", \".join(allowed_schema_options)))\n  \n# The 'Schema' connection option is exposed as a first class parameter (`schema`) and so \n# we need to ensure that this value is passed into the connection options \nconn_opts = context.getGridVariable('advanced_connection_options')\n\nschema_opt_exists = False \n\nnew_opts = []\n\nif conn_opts:\n  for param, val in conn_opts:\n    # copy all existing connection options, except for Schema and SupportEnhancedSQL\n    # Schema will be determined by the 'schema' variable value \n    # SupportEnhancedSQL will always be true as this is required for incremental laods \n    if param not in ('Schema', 'SupportEnhancedSQL'):\n      new_opts.append([ param, val ])\n      continue \n      \n    if param == \"Schema\" and val != schema:\n      print(\"Overriding `Schema` connection option: {} -> {}\".format(val, schema))\n      new_opts.append([ param, schema ])\n      schema_opt_exists = True \n\n      \nif not schema_opt_exists:\n  new_opts.append(['Schema', schema])\n  \nnew_opts.append(['SupportEnhancedSQL', 'true'])\n\nprint(\"New Connection Options:\\n{}\".format(new_opts))\n\ncontext.updateGridVariable('advanced_connection_options', new_opts)"}}}},"visible":true,"status":{},"columnNames":[]},"3":{"slot":3,"name":"Interpreter","elements":{"1":{"slot":1,"values":{"1":{"slot":1,"type":"STRING","value":"Python 3"}}}},"visible":true,"status":{},"columnNames":[]},"4":{"slot":4,"name":"Timeout","elements":{"1":{"slot":1,"values":{"1":{"slot":1,"type":"INTEGER","value":"360"}}}},"visible":true,"status":{},"columnNames":[]}},"validationStatus":{},"status":{},"expectedFailure":null,"activationStatus":"ENABLED","outputIterationConnectorIDs":[],"inputIterationConnectorIDs":[]}},"successConnectors":{"154":{"id":154,"sourceID":122,"targetID":153},"155":{"id":155,"sourceID":153,"targetID":124},"156":{"id":156,"sourceID":121,"targetID":122},"157":{"id":157,"sourceID":124,"targetID":126},"158":{"id":158,"sourceID":124,"targetID":152},"1871":{"id":1871,"sourceID":1867,"targetID":121}},"failureConnectors":{"161":{"id":161,"sourceID":124,"targetID":127}},"unconditionalConnectors":{"1868":{"id":1868,"sourceID":125,"targetID":1867}},"trueConnectors":{"159":{"id":159,"sourceID":152,"targetID":151}},"falseConnectors":{},"iterationConnectors":{"162":{"id":162,"sourceID":124,"targetID":123}},"noteConnectors":{},"canUndo":true,"undoCommand":"Set Parameter","undoCreated":1613407736411,"canRedo":false,"redoCommand":"","redoCreated":0,"notes":{"120":{"id":120,"x":-588,"y":-521,"width":1110,"height":390,"text":"**Xero load into cloud data warehouse ** \nTop level orchestration process which reads the list of table names and column names provided via UI, then iterates \nthrough the table name list to process loading of the supplied columns.\n\nParameters are set from the calling UI as part of the Matillion shared job.\n\n======================================================================\n**Predecessor Information:**\nNone\n\n======================================================================\n**Successor Information:**\nXero - 2 - Stage Object\n\n======================================================================\n**Change History - __please append new changes as the first entry below ensuring date, author, version and change detail is recorded __**\n----------------------------------------------------------------------------------------------------------------------------\nVersion 1.0\nDate: Oct 2020\nAuthor: Matillion ELT\nChange Summary: Initial version of Xero load into cloud data warehouse.\nChange Detail: Initial version\n\n__**Change detail should be documented on the canvas or within this note for traceability.**__\n======================================================================","colour":"00ce4f"}},"variables":{"oauth":{"definition":{"name":"oauth","type":"TEXT","scope":"BRANCH","description":"","visibility":"PUBLIC"},"value":""},"schema":{"definition":{"name":"schema","type":"TEXT","scope":"TASKBATCH","description":"","visibility":"PUBLIC"},"value":"Accounting"},"dist_style":{"definition":{"name":"dist_style","type":"TEXT","scope":"BRANCH","description":"<b>Even:</b> Distributes rows around the Redshift cluster evenly.<br>\n<b>All:</b> Copy rows to all nodes in the Redshift cluster.<br>\nTable distribution is critical to good performance. See the <a href=\"http://docs.aws.amazon.com/redshift/latest/dg/tutorial-tuning-tables-distribution.html\" target=\"_blank\">Amazon Redshift</a> documentation for more information.","visibility":"PUBLIC"},"value":""},"kms_key_id":{"definition":{"name":"kms_key_id","type":"TEXT","scope":"BRANCH","description":"The ID of the KMS encryption key you have chosen to use in the 'Encryption' property.","visibility":"PUBLIC"},"value":""},"table_name":{"definition":{"name":"table_name","type":"TEXT","scope":"BRANCH","description":"","visibility":"PRIVATE"},"value":""},"log_metrics":{"definition":{"name":"log_metrics","type":"TEXT","scope":"BRANCH","description":"","visibility":"PUBLIC"},"value":"TRUE"},"stage_prefix":{"definition":{"name":"stage_prefix","type":"TEXT","scope":"BRANCH","description":"A prefix value that will be added to the start of the stage table names.\n<br>\ne.g. If a Stage Prefix of 'stage_' is specified and the table being processed is named 'test_data' then the target table will be named 'stage_test_data'.","visibility":"PUBLIC"},"value":""},"stage_schema":{"definition":{"name":"stage_schema","type":"TEXT","scope":"BRANCH","description":"The schema name where the staging data will be stored.","visibility":"PUBLIC"},"value":""},"staging_type":{"definition":{"name":"staging_type","type":"TEXT","scope":"BRANCH","description":"(AWS Only) <b>Snowflake Managed:</b> Allow Matillion ETL to create and use a temporary internal stage on Snowflake for staging the data. This stage, along with the staged data, will cease to exist after loading is complete.<br>\n                <b>Existing Amazon S3 Location:</b> Selecting this will avail the user of properties to specify a custom staging area on S3.","visibility":"PRIVATE"},"value":"Snowflake Managed"},"stage_dataset":{"definition":{"name":"stage_dataset","type":"TEXT","scope":"BRANCH","description":"The dataset name where the staging data will be stored.","visibility":"PRIVATE"},"value":""},"stage_project":{"definition":{"name":"stage_project","type":"TEXT","scope":"BRANCH","description":"The project name where the staging data will be stored.","visibility":"PRIVATE"},"value":""},"target_prefix":{"definition":{"name":"target_prefix","type":"TEXT","scope":"BRANCH","description":"A prefix value that will be added to the start of the target table names.\n<br>\ne.g. If a Target Prefix of 'target_' is specified and the table being processed is named 'test_data' then the target table will be named 'target_test_data'.","visibility":"PUBLIC"},"value":""},"target_schema":{"definition":{"name":"target_schema","type":"TEXT","scope":"BRANCH","description":"The schema name where the target data will be stored.","visibility":"PUBLIC"},"value":""},"blob_container":{"definition":{"name":"blob_container","type":"TEXT","scope":"BRANCH","description":"(Azure Only) Azure Blob Container to allow Matillion ETL to use the blob storage container object on Snowflake for staging data. ","visibility":"PRIVATE"},"value":""},"s3_bucket_name":{"definition":{"name":"s3_bucket_name","type":"TEXT","scope":"BRANCH","description":"<span class=\"env sf\">(AWS Only) </span>The name of an S3 bucket for temporary storage. Ensure your access credentials have S3 access and permission to write to the bucket. See <a href=\"https://redshiftsupport.matillion.com/customer/portal/articles/2054760\" target=\"_blank\">this document</a> for details on setting up access. The temporary objects created in this bucket will be removed again after the load completes, they are not kept.<br>\n<span class=\"env sf\">This property is available when using an Existing Amazon S3 Location for Staging.</span>","visibility":"PUBLIC"},"value":""},"stage_database":{"definition":{"name":"stage_database","type":"TEXT","scope":"BRANCH","description":"The database name where the staging data will be stored.","visibility":"PRIVATE"},"value":""},"target_dataset":{"definition":{"name":"target_dataset","type":"TEXT","scope":"BRANCH","description":"The dataset name where the target data will be stored.","visibility":"PRIVATE"},"value":""},"target_project":{"definition":{"name":"target_project","type":"TEXT","scope":"BRANCH","description":"The project name where the target data will be stored.","visibility":"PRIVATE"},"value":""},"load_concurrent":{"definition":{"name":"load_concurrent","type":"TEXT","scope":"BRANCH","description":"Sequential - Iterations are done in sequence, waiting for each to complete before starting the next. \nThis is the default.\n<br>\nConcurrent - Iterations are run concurrently. This requires all \"Variables to Iterate\" to be defined as\n<a href=\"https://redshiftsupport.matillion.com/customer/portal/articles/2037630?b_id=8915#header1\" target=\"_blank\"><i>Copied</i> variables</a>, so that each iteration gets its own copy of the variable isolated from the same\nvariable being used by other concurrent executions.\n<span class=\"env rs sf\"><br><b>Note:</b> The maximum concurrency is limited by the number of available threads (2x the number of virtual cpus on your cloud instance).</span>","visibility":"PUBLIC"},"value":"Concurrent"},"logged_duration":{"definition":{"name":"logged_duration","type":"DECIMAL","scope":"BRANCH","description":"","visibility":"PRIVATE"},"value":""},"stage_warehouse":{"definition":{"name":"stage_warehouse","type":"TEXT","scope":"BRANCH","description":"The warehouse name where the staging data will be stored.","visibility":"PRIVATE"},"value":""},"storage_account":{"definition":{"name":"storage_account","type":"TEXT","scope":"BRANCH","description":"(Azure Only) Azure Storage Account to allow Matillion ETL to specify a blob container object on Snowflake for staging data. ","visibility":"PRIVATE"},"value":""},"target_database":{"definition":{"name":"target_database","type":"TEXT","scope":"BRANCH","description":"The database name where the target data will be stored.","visibility":"PRIVATE"},"value":""},"target_platform":{"definition":{"name":"target_platform","type":"TEXT","scope":"BRANCH","description":"","visibility":"PRIVATE"},"value":"Redshift"},"stage_row_amount":{"definition":{"name":"stage_row_amount","type":"DECIMAL","scope":"TASKBATCH","description":"Number of rows to be retrieved during staging to determine the metadata. ","visibility":"PRIVATE"},"value":"1"},"target_warehouse":{"definition":{"name":"target_warehouse","type":"TEXT","scope":"BRANCH","description":"The warehouse name where the target data will be stored.","visibility":"PRIVATE"},"value":""},"encryption_method":{"definition":{"name":"encryption_method","type":"TEXT","scope":"BRANCH","description":"<span class=\"env sf\">(AWS Only) </span>Decide on how the files are encrypted inside the S3 Bucket.<span class=\"env sf\">This property is available when using an Existing Amazon S3 Location for Staging.</span><br>\n                <b>None:</b> No encryption.<br>\n                <b>SSE KMS:</b> Encrypt the data according to a key stored on <a href=\"https://aws.amazon.com/kms/\" target=\"_blank\">KMS</a>.<br>\n                <b>SSE S3:</b> Encrypt the data according to a key stored on an <a href=\"http://docs.aws.amazon.com/AmazonS3/latest/dev/UsingServerSideEncryption.html\" target=\"_blank\">S3 bucket</a>","visibility":"PUBLIC"},"value":"None"},"cloud_storage_area":{"definition":{"name":"cloud_storage_area","type":"TEXT","scope":"BRANCH","description":"The URL and path of the target Google Storage bucket to be used for staging the queried data throughout the job.","visibility":"PRIVATE"},"value":""}},"grids":{"source_list":{"definition":{"name":"source_list","scope":"BRANCH","definitions":[{"name":"table","type":"TEXT"},{"name":"column","type":"TEXT"},{"name":"incremental_column","type":"DECIMAL"},{"name":"key_column","type":"DECIMAL"}],"description":"","visibility":"PRIVATE"},"values":[]},"input_source_list":{"definition":{"name":"input_source_list","scope":"BRANCH","definitions":[{"name":"table","type":"TEXT"},{"name":"column","type":"TEXT"}],"description":"","visibility":"PUBLIC"},"values":[{"values":["Accounts","AccountId"]},{"values":["Accounts","Code"]},{"values":["Accounts","Name"]},{"values":["Accounts","Type"]},{"values":["Accounts","Description"]},{"values":["Accounts","TaxType"]},{"values":["Accounts","EnablePaymentsToAccount"]},{"values":["Accounts","ShowInExpenseClaims"]},{"values":["Accounts","Class"]},{"values":["Accounts","Status"]},{"values":["Accounts","SystemAccount"]},{"values":["Accounts","BankAccountNumber"]},{"values":["Accounts","CurrencyCode"]},{"values":["Accounts","ReportingCode"]},{"values":["Accounts","ReportingCodeName"]},{"values":["Accounts","UpdatedDateUTC"]},{"values":["Accounts","HasAttachments"]},{"values":["Accounts","BankAccountType"]},{"values":["Payments","PaymentId"]},{"values":["Payments","Date"]},{"values":["Payments","Amount"]},{"values":["Payments","CurrencyRate"]},{"values":["Payments","Reference"]},{"values":["Payments","PaymentType"]},{"values":["Payments","Status"]},{"values":["Payments","UpdatedDateUTC"]},{"values":["Payments","IsReconciled"]},{"values":["Payments","BatchPaymentId"]},{"values":["Payments","InvoiceId"]},{"values":["Payments","InvoiceNumber"]},{"values":["Payments","CreditNoteId"]},{"values":["Payments","CreditNoteNumber"]},{"values":["Payments","AccountId"]},{"values":["Payments","AccountCode"]},{"values":["BankTransactions","Id"]},{"values":["BankTransactions","BankTransactionId"]},{"values":["BankTransactions","Type"]},{"values":["BankTransactions","Contact_ContactName"]},{"values":["BankTransactions","Contact_ContactId"]},{"values":["BankTransactions","BankAccount_AccountId"]},{"values":["BankTransactions","BankAccount_AccountName"]},{"values":["BankTransactions","BankAccount_Code"]},{"values":["BankTransactions","LineItem_LineItemId"]},{"values":["BankTransactions","LineItem_Description"]},{"values":["BankTransactions","LineItem_Quantity"]},{"values":["BankTransactions","LineItem_UnitAmount"]},{"values":["BankTransactions","LineItem_AccountCode"]},{"values":["BankTransactions","LineItem_TaxType"]},{"values":["BankTransactions","LineItem_TaxAmount"]},{"values":["BankTransactions","LineItem_LineAmount"]},{"values":["BankTransactions","LineItem_TrackingCategory1_CategoryId"]},{"values":["BankTransactions","LineItem_TrackingCategory1_CategoryName"]},{"values":["BankTransactions","LineItem_TrackingCategory1_OptionId"]},{"values":["BankTransactions","LineItem_TrackingCategory1_OptionName"]},{"values":["BankTransactions","LineItem_TrackingCategory1_Option_Stat"]},{"values":["BankTransactions","LineItem_TrackingCategory2_CategoryId"]},{"values":["BankTransactions","LineItem_TrackingCategory2_CategoryName"]},{"values":["BankTransactions","LineItem_TrackingCategory2_OptionId"]},{"values":["BankTransactions","LineItem_TrackingCategory2_OptionName"]},{"values":["BankTransactions","LineItem_TrackingCategory2_Option_Stat"]},{"values":["BankTransactions","LineItemAggregate"]},{"values":["BankTransactions","IsReconciled"]},{"values":["BankTransactions","Date"]},{"values":["BankTransactions","Reference"]},{"values":["BankTransactions","CurrencyRate"]},{"values":["BankTransactions","URL"]},{"values":["BankTransactions","Status"]},{"values":["BankTransactions","SubTotal"]},{"values":["BankTransactions","TotalTax"]},{"values":["BankTransactions","Total"]},{"values":["BankTransactions","PrepaymentId"]},{"values":["BankTransactions","OverpaymentId"]},{"values":["BankTransactions","UpdatedDateUTC"]},{"values":["BankTransactions","FullyPaidOnDate"]},{"values":["PurchaseOrders","Id"]},{"values":["PurchaseOrders","PurchaseOrderId"]},{"values":["PurchaseOrders","PurchaseOrderNumber"]},{"values":["PurchaseOrders","Reference"]},{"values":["PurchaseOrders","Type"]},{"values":["PurchaseOrders","Contact_ContactId"]},{"values":["PurchaseOrders","Contact_Name"]},{"values":["PurchaseOrders","DeliveryDate"]},{"values":["PurchaseOrders","Date"]},{"values":["PurchaseOrders","Status"]},{"values":["PurchaseOrders","LineAmountTypes"]},{"values":["PurchaseOrders","LineItem_LineItemId"]},{"values":["PurchaseOrders","LineItem_Description"]},{"values":["PurchaseOrders","LineItem_Quantity"]},{"values":["PurchaseOrders","LineItem_UnitAmount"]},{"values":["PurchaseOrders","LineItem_ItemCode"]},{"values":["PurchaseOrders","LineItem_AccountCode"]},{"values":["PurchaseOrders","LineItem_TaxType"]},{"values":["PurchaseOrders","LineItem_TaxAmount"]},{"values":["PurchaseOrders","LineItem_LineAmount"]},{"values":["PurchaseOrders","LineItem_DiscountRate"]},{"values":["PurchaseOrders","LineItemAggregate"]},{"values":["PurchaseOrders","SubTotal"]},{"values":["PurchaseOrders","TotalTax"]},{"values":["PurchaseOrders","Total"]},{"values":["PurchaseOrders","LineItem_TrackingCategory1_CategoryId"]},{"values":["PurchaseOrders","LineItem_TrackingCategory1_CategoryName"]},{"values":["PurchaseOrders","LineItem_TrackingCategory1_OptionId"]},{"values":["PurchaseOrders","LineItem_TrackingCategory1_OptionName"]},{"values":["PurchaseOrders","LineItem_TrackingCategory1_Option_Stat"]},{"values":["PurchaseOrders","LineItem_TrackingCategory2_CategoryId"]},{"values":["PurchaseOrders","LineItem_TrackingCategory2_CategoryName"]},{"values":["PurchaseOrders","LineItem_TrackingCategory2_OptionId"]},{"values":["PurchaseOrders","LineItem_TrackingCategory2_OptionName"]},{"values":["PurchaseOrders","LineItem_TrackingCategory2_Option_Stat"]},{"values":["PurchaseOrders","UpdatedDateUTC"]},{"values":["PurchaseOrders","HasAttachments"]},{"values":["PurchaseOrders","CurrencyCode"]},{"values":["PurchaseOrders","BrandingThemeId"]},{"values":["PurchaseOrders","CurrencyRate"]},{"values":["HistoryContacts","Id"]},{"values":["HistoryContacts","ContactId"]},{"values":["HistoryContacts","Changes"]},{"values":["HistoryContacts","Date"]},{"values":["HistoryContacts","User"]},{"values":["HistoryContacts","Details"]}]},"distinct_table_names":{"definition":{"name":"distinct_table_names","scope":"BRANCH","definitions":[{"name":"table_name","type":"TEXT"}],"description":"","visibility":"PRIVATE"},"values":[]},"advanced_connection_options":{"definition":{"name":"advanced_connection_options","scope":"BRANCH","definitions":[{"name":"parameter","type":"TEXT"},{"name":"value","type":"TEXT"}],"description":"A list of values and parameters. Parameters and their allowed values are database/driver specific. Referring to the data model will provide insight of what you could provide here.\n<br>\nThey are usually not required as sensible defaults are assumed.","visibility":"PUBLIC"},"values":[]}}},"Xero - 2 - Stage and Load":{"id":-2,"revision":1,"created":1613406965584,"timestamp":1613406965584,"components":{"13259":{"id":13259,"inputCardinality":"ONE","outputCardinality":"ZERO","connectorHint":"UNCONDITIONAL","executionHint":"FLOW","implementationID":-1946388514,"x":-784,"y":16,"width":32,"height":32,"inputConnectorIDs":[13264],"outputSuccessConnectorIDs":[],"outputFailureConnectorIDs":[],"outputUnconditionalConnectorIDs":[],"outputTrueConnectorIDs":[],"outputFalseConnectorIDs":[],"exportMappings":{},"parameters":{"1":{"slot":1,"name":"Name","elements":{"1":{"slot":1,"values":{"1":{"slot":1,"type":"STRING","value":"End Success"}}}},"visible":true,"status":{},"columnNames":[]}},"validationStatus":{},"status":{},"expectedFailure":null,"activationStatus":"ENABLED","outputIterationConnectorIDs":[],"inputIterationConnectorIDs":[]},"13260":{"id":13260,"inputCardinality":"ZERO","outputCardinality":"MANY","connectorHint":"UNCONDITIONAL","executionHint":"FLOW","implementationID":444132438,"x":-1344,"y":16,"width":32,"height":32,"inputConnectorIDs":[],"outputSuccessConnectorIDs":[],"outputFailureConnectorIDs":[],"outputUnconditionalConnectorIDs":[13266],"outputTrueConnectorIDs":[],"outputFalseConnectorIDs":[],"exportMappings":{},"parameters":{"1":{"slot":1,"name":"Name","elements":{"1":{"slot":1,"values":{"1":{"slot":1,"type":"STRING","value":"Start"}}}},"visible":true,"status":{},"columnNames":[]}},"validationStatus":{},"status":{},"expectedFailure":null,"activationStatus":"ENABLED","outputIterationConnectorIDs":[],"inputIterationConnectorIDs":[]},"13261":{"id":13261,"inputCardinality":"ONE","outputCardinality":"MANY","connectorHint":"SUCCESS_FAIL","executionHint":"TRANSFORM","implementationID":-1180878087,"x":-1152,"y":16,"width":32,"height":32,"inputConnectorIDs":[13266],"outputSuccessConnectorIDs":[],"outputFailureConnectorIDs":[13267],"outputUnconditionalConnectorIDs":[13265],"outputTrueConnectorIDs":[],"outputFalseConnectorIDs":[],"exportMappings":{"1":{"slot":1,"fromId":null,"fromName":"stage_table_name","mapTo":"stage_table_name","validationStatus":null,"validationMessage":null},"2":{"slot":2,"fromId":null,"fromName":"metadata_stage_table_name","mapTo":"metadata_stage_table_name","validationStatus":null,"validationMessage":null}},"parameters":{"1":{"slot":1,"name":"Name","elements":{"1":{"slot":1,"values":{"1":{"slot":1,"type":"STRING","value":"Stage Object"}}}},"visible":true,"status":{},"columnNames":[]},"2":{"slot":2,"name":"Job Name","elements":{"1":{"slot":1,"values":{"1":{"slot":1,"type":"STRING","value":"Xero - 3 - Stage Object"}}}},"visible":true,"status":{},"columnNames":[]},"3":{"slot":3,"name":"","elements":{},"visible":false,"status":{},"columnNames":[]},"4":{"slot":4,"name":"Set Scalar Variables","elements":{"1":{"slot":1,"values":{"1":{"slot":1,"type":"STRING","value":"bucket_name"},"2":{"slot":2,"type":"STRING","value":"${bucket_name}"}}},"2":{"slot":2,"values":{"1":{"slot":1,"type":"STRING","value":"stage_prefix"},"2":{"slot":2,"type":"STRING","value":"${stage_prefix}"}}},"3":{"slot":3,"values":{"1":{"slot":1,"type":"STRING","value":"stage_schema"},"2":{"slot":2,"type":"STRING","value":"${stage_schema}"}}},"4":{"slot":4,"values":{"1":{"slot":1,"type":"STRING","value":"target_prefix"},"2":{"slot":2,"type":"STRING","value":"${target_prefix}"}}},"5":{"slot":5,"values":{"1":{"slot":1,"type":"STRING","value":"target_schema"},"2":{"slot":2,"type":"STRING","value":"${target_schema}"}}},"6":{"slot":6,"values":{"1":{"slot":1,"type":"STRING","value":"table_name"},"2":{"slot":2,"type":"STRING","value":"${table_name}"}}},"7":{"slot":7,"values":{"1":{"slot":1,"type":"STRING","value":"dist_style"},"2":{"slot":2,"type":"STRING","value":"${dist_style}"}}},"8":{"slot":8,"values":{"1":{"slot":1,"type":"STRING","value":"target_platform"},"2":{"slot":2,"type":"STRING","value":"${target_platform}"}}},"9":{"slot":9,"values":{"1":{"slot":1,"type":"STRING","value":"target_database"},"2":{"slot":2,"type":"STRING","value":"${target_database}"}}},"10":{"slot":10,"values":{"1":{"slot":1,"type":"STRING","value":"target_warehouse"},"2":{"slot":2,"type":"STRING","value":"${target_warehouse}"}}},"11":{"slot":11,"values":{"1":{"slot":1,"type":"STRING","value":"stage_database"},"2":{"slot":2,"type":"STRING","value":"${stage_database}"}}},"12":{"slot":12,"values":{"1":{"slot":1,"type":"STRING","value":"stage_warehouse"},"2":{"slot":2,"type":"STRING","value":"${stage_warehouse}"}}},"13":{"slot":13,"values":{"1":{"slot":1,"type":"STRING","value":"target_dataset"},"2":{"slot":2,"type":"STRING","value":"${target_dataset}"}}},"14":{"slot":14,"values":{"1":{"slot":1,"type":"STRING","value":"target_project"},"2":{"slot":2,"type":"STRING","value":"${target_project}"}}},"15":{"slot":15,"values":{"1":{"slot":1,"type":"STRING","value":"stage_dataset"},"2":{"slot":2,"type":"STRING","value":"${stage_dataset}"}}},"16":{"slot":16,"values":{"1":{"slot":1,"type":"STRING","value":"stage_project"},"2":{"slot":2,"type":"STRING","value":"${stage_project}"}}},"17":{"slot":17,"values":{"1":{"slot":1,"type":"STRING","value":"cloud_storage_area"},"2":{"slot":2,"type":"STRING","value":"${cloud_storage_area}"}}},"18":{"slot":18,"values":{"1":{"slot":1,"type":"STRING","value":"storage_account"},"2":{"slot":2,"type":"STRING","value":"${storage_account}"}}},"19":{"slot":19,"values":{"1":{"slot":1,"type":"STRING","value":"blob_container"},"2":{"slot":2,"type":"STRING","value":"${blob_container}"}}},"20":{"slot":20,"values":{"1":{"slot":1,"type":"STRING","value":"encryption_method"},"2":{"slot":2,"type":"STRING","value":"${encryption_method}"}}},"21":{"slot":21,"values":{"1":{"slot":1,"type":"STRING","value":"kms_key_id"},"2":{"slot":2,"type":"STRING","value":"${kms_key_id}"}}},"22":{"slot":22,"values":{"1":{"slot":1,"type":"STRING","value":"staging_type"},"2":{"slot":2,"type":"STRING","value":"${staging_type}"}}},"23":{"slot":23,"values":{"1":{"slot":1,"type":"STRING","value":"log_metrics"},"2":{"slot":2,"type":"STRING","value":"${log_metrics}"}}},"24":{"slot":24,"values":{"1":{"slot":1,"type":"STRING","value":"oauth"},"2":{"slot":2,"type":"STRING","value":"${oauth}"}}}},"visible":true,"status":{},"columnNames":[]},"5":{"slot":5,"name":"Set Grid Variables","elements":{"1":{"slot":1,"elements":{"1":{"slot":1,"values":{"1":{"slot":1,"type":"GRID","value":"advanced_connection_options"}}},"2":{"slot":2,"values":{"1":{"slot":1,"type":"GRID","value":"parameter"},"2":{"slot":2,"type":"GRID","value":"value"}}}},"values":{"1":{"slot":1,"type":"STRING","value":"advanced_connection_options"},"2":{"slot":2,"type":"STRING","value":"grid"}}},"2":{"slot":2,"elements":{"1":{"slot":1,"values":{"1":{"slot":1,"type":"GRID","value":"source_list"}}},"2":{"slot":2,"values":{"1":{"slot":1,"type":"GRID","value":"table"},"2":{"slot":2,"type":"GRID","value":"column"},"3":{"slot":3,"type":"GRID","value":"incremental_column"},"4":{"slot":4,"type":"GRID","value":"key_column"}}}},"values":{"1":{"slot":1,"type":"STRING","value":"source_list"},"2":{"slot":2,"type":"STRING","value":"grid"}}}},"visible":true,"status":{},"columnNames":[]},"6":{"slot":6,"name":"Melt Name","elements":{"1":{"slot":1,"values":{"1":{"slot":1,"type":"STRING","value":"Xero Incremental Load"}}}},"visible":true,"status":{},"columnNames":[]},"7":{"slot":7,"name":"Melt Package","elements":{"1":{"slot":1,"values":{"1":{"slot":1,"type":"STRING","value":"Matillion.Incremental"}}}},"visible":true,"status":{},"columnNames":[]},"8":{"slot":8,"name":"Melt Revision","elements":{"1":{"slot":1,"values":{"1":{"slot":1,"type":"INTEGER","value":"1"}}}},"visible":true,"status":{},"columnNames":[]},"9":{"slot":9,"name":"Melt Load Type","elements":{"1":{"slot":1,"values":{"1":{"slot":1,"type":"STRING","value":"DYNAMIC"}}}},"visible":true,"status":{},"columnNames":[]}},"validationStatus":{},"status":{},"expectedFailure":null,"activationStatus":"ENABLED","outputIterationConnectorIDs":[],"inputIterationConnectorIDs":[]},"13262":{"id":13262,"inputCardinality":"ONE","outputCardinality":"MANY","connectorHint":"SUCCESS_FAIL","executionHint":"EXECUTE","implementationID":-1291941950,"x":-960,"y":16,"width":32,"height":32,"inputConnectorIDs":[13265],"outputSuccessConnectorIDs":[13264],"outputFailureConnectorIDs":[],"outputUnconditionalConnectorIDs":[],"outputTrueConnectorIDs":[],"outputFalseConnectorIDs":[],"exportMappings":{},"parameters":{"1":{"slot":1,"name":"Name","elements":{"1":{"slot":1,"values":{"1":{"slot":1,"type":"STRING","value":"Drop stage tables"}}}},"visible":true,"status":{},"columnNames":[]},"2":{"slot":2,"name":"Schema","elements":{"1":{"slot":1,"values":{"1":{"slot":1,"type":"STRING","value":"${stage_schema}"}}}},"visible":true,"status":{},"columnNames":[]},"3":{"slot":3,"name":"Tables","elements":{"1":{"slot":1,"values":{"1":{"slot":1,"type":"STRING","value":"${metadata_stage_table_name}"}}},"2":{"slot":2,"values":{"1":{"slot":1,"type":"STRING","value":"${stage_table_name}"}}}},"visible":true,"status":{},"columnNames":[]},"4":{"slot":4,"name":"Cascade","elements":{"1":{"slot":1,"values":{"1":{"slot":1,"type":"STRING","value":"Yes"}}}},"visible":true,"status":{},"columnNames":[]},"5":{"slot":5,"name":"Ignore Missing","elements":{"1":{"slot":1,"values":{"1":{"slot":1,"type":"STRING","value":"Yes"}}}},"visible":true,"status":{},"columnNames":[]}},"validationStatus":{},"status":{},"expectedFailure":null,"activationStatus":"ENABLED","outputIterationConnectorIDs":[],"inputIterationConnectorIDs":[]},"13263":{"id":13263,"inputCardinality":"ONE","outputCardinality":"ZERO","connectorHint":"UNCONDITIONAL","executionHint":"FLOW","implementationID":515156205,"x":-1152,"y":144,"width":32,"height":32,"inputConnectorIDs":[13267],"outputSuccessConnectorIDs":[],"outputFailureConnectorIDs":[],"outputUnconditionalConnectorIDs":[],"outputTrueConnectorIDs":[],"outputFalseConnectorIDs":[],"exportMappings":{},"parameters":{"1":{"slot":1,"name":"Name","elements":{"1":{"slot":1,"values":{"1":{"slot":1,"type":"STRING","value":"End Failure"}}}},"visible":true,"status":{},"columnNames":[]}},"validationStatus":{},"status":{},"expectedFailure":null,"activationStatus":"ENABLED","outputIterationConnectorIDs":[],"inputIterationConnectorIDs":[]}},"successConnectors":{"13264":{"id":13264,"sourceID":13262,"targetID":13259}},"failureConnectors":{"13267":{"id":13267,"sourceID":13261,"targetID":13263}},"unconditionalConnectors":{"13265":{"id":13265,"sourceID":13261,"targetID":13262},"13266":{"id":13266,"sourceID":13260,"targetID":13261}},"trueConnectors":{},"falseConnectors":{},"iterationConnectors":{},"noteConnectors":{},"canUndo":false,"undoCommand":"","undoCreated":0,"canRedo":false,"redoCommand":"","redoCreated":0,"notes":{"13258":{"id":13258,"x":-1275,"y":-106,"width":419,"height":181,"text":"**Wrapper job to ensure stage tables are always dropped**\n\nUnconditional link to drop tables, regardless of stage 3 outcome\nFailure link to End Failure component to ensure any failures are propagated back stage 1","colour":"e6e63c"}},"variables":{"oauth":{"definition":{"name":"oauth","type":"TEXT","scope":"TASKBATCH","description":"","visibility":"PUBLIC"},"value":""},"dist_style":{"definition":{"name":"dist_style","type":"TEXT","scope":"TASKBATCH","description":"","visibility":"PUBLIC"},"value":""},"kms_key_id":{"definition":{"name":"kms_key_id","type":"TEXT","scope":"TASKBATCH","description":"","visibility":"PUBLIC"},"value":""},"table_name":{"definition":{"name":"table_name","type":"TEXT","scope":"TASKBATCH","description":"","visibility":"PUBLIC"},"value":""},"bucket_name":{"definition":{"name":"bucket_name","type":"TEXT","scope":"TASKBATCH","description":"","visibility":"PUBLIC"},"value":""},"log_metrics":{"definition":{"name":"log_metrics","type":"TEXT","scope":"TASKBATCH","description":"","visibility":"PUBLIC"},"value":""},"stage_prefix":{"definition":{"name":"stage_prefix","type":"TEXT","scope":"TASKBATCH","description":"","visibility":"PUBLIC"},"value":""},"stage_schema":{"definition":{"name":"stage_schema","type":"TEXT","scope":"TASKBATCH","description":"","visibility":"PUBLIC"},"value":""},"staging_type":{"definition":{"name":"staging_type","type":"TEXT","scope":"TASKBATCH","description":"","visibility":"PUBLIC"},"value":""},"stage_dataset":{"definition":{"name":"stage_dataset","type":"TEXT","scope":"TASKBATCH","description":"","visibility":"PUBLIC"},"value":""},"stage_project":{"definition":{"name":"stage_project","type":"TEXT","scope":"TASKBATCH","description":"   ","visibility":"PUBLIC"},"value":""},"target_prefix":{"definition":{"name":"target_prefix","type":"TEXT","scope":"TASKBATCH","description":"","visibility":"PUBLIC"},"value":""},"target_schema":{"definition":{"name":"target_schema","type":"TEXT","scope":"TASKBATCH","description":"","visibility":"PUBLIC"},"value":""},"blob_container":{"definition":{"name":"blob_container","type":"TEXT","scope":"TASKBATCH","description":"","visibility":"PUBLIC"},"value":""},"stage_database":{"definition":{"name":"stage_database","type":"TEXT","scope":"TASKBATCH","description":"","visibility":"PUBLIC"},"value":""},"target_dataset":{"definition":{"name":"target_dataset","type":"TEXT","scope":"TASKBATCH","description":"","visibility":"PUBLIC"},"value":""},"target_project":{"definition":{"name":"target_project","type":"TEXT","scope":"TASKBATCH","description":"","visibility":"PUBLIC"},"value":""},"stage_warehouse":{"definition":{"name":"stage_warehouse","type":"TEXT","scope":"TASKBATCH","description":"","visibility":"PUBLIC"},"value":""},"storage_account":{"definition":{"name":"storage_account","type":"TEXT","scope":"TASKBATCH","description":"","visibility":"PUBLIC"},"value":""},"target_database":{"definition":{"name":"target_database","type":"TEXT","scope":"TASKBATCH","description":"","visibility":"PUBLIC"},"value":""},"target_platform":{"definition":{"name":"target_platform","type":"TEXT","scope":"TASKBATCH","description":"","visibility":"PUBLIC"},"value":""},"stage_table_name":{"definition":{"name":"stage_table_name","type":"TEXT","scope":"TASKBATCH","description":"","visibility":"PRIVATE"},"value":""},"target_warehouse":{"definition":{"name":"target_warehouse","type":"TEXT","scope":"TASKBATCH","description":"","visibility":"PUBLIC"},"value":""},"encryption_method":{"definition":{"name":"encryption_method","type":"TEXT","scope":"TASKBATCH","description":"","visibility":"PUBLIC"},"value":""},"cloud_storage_area":{"definition":{"name":"cloud_storage_area","type":"TEXT","scope":"TASKBATCH","description":"The cloud storage area name that should be used when loading bq tables.","visibility":"PUBLIC"},"value":""},"metadata_stage_table_name":{"definition":{"name":"metadata_stage_table_name","type":"TEXT","scope":"TASKBATCH","description":"","visibility":"PRIVATE"},"value":""}},"grids":{"source_list":{"definition":{"name":"source_list","scope":"TASKBATCH","definitions":[{"name":"table","type":"TEXT"},{"name":"column","type":"TEXT"},{"name":"incremental_column","type":"DECIMAL"},{"name":"key_column","type":"DECIMAL"}],"description":"","visibility":"PUBLIC"},"values":[]},"advanced_connection_options":{"definition":{"name":"advanced_connection_options","scope":"TASKBATCH","definitions":[{"name":"parameter","type":"TEXT"},{"name":"value","type":"TEXT"}],"description":"","visibility":"PUBLIC"},"values":[]}}}},"transformationJobs":{}}],"version":"EMD_17421","environment":"redshift"}