{"objects":[{"metadata":{"identifier":{"packageName":"Matillion Exchange","name":"Google Analytics Incremental Load","revision":2,"type":"DYNAMIC"},"rootJobReference":{"name":"Google Analytics - 1 - Iterate Objects","type":"ORCHESTRATION","parameterMetadata":[{"slot":2,"variableName":"oauth","variableType":"SCALAR","displayName":"OAuth","description":"The name of the OAuth entry to be used from the Matillion OAuth Manager to connect to the source. These must be set up in advance, using <b>Project → Manage OAuth</b>.","defaultValue":[{"values":{"1":""}}],"defaultValueType":"TEXT","required":true},{"slot":3,"variableName":"source_list","variableType":"GRID","displayName":"Tables and Columns","description":"Contains the list of tables and columns (and in some cases an incremental_column to specify which column the load should be incremented on [accepts values of 0 or 1]) to be processed. ","defaultValue":[{"values":{"1":""}}],"defaultValueType":null,"required":true},{"slot":4,"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":5,"variableName":"sub_RangeStartDate","variableType":"SCALAR","displayName":"Start Date for Data Fetching","description":"The start date for fetching data from Google Analytics. Please note that as the interval for making calls to the API is set by default as 1 day via job variable 'load_int' that the further back the RangeStartDate is set, the more processes will be spawned and longer the overall ELT process will take to run.","defaultValue":[{"values":{"1":"2005-01-01"}}],"defaultValueType":"TEXT","required":true},{"slot":6,"variableName":"load_int","variableType":"SCALAR","displayName":"Load Interval for Microbatching","description":"The default microbatch interval for staging and loading - the actual microbatch will be interval -1 days.","defaultValue":[{"values":{"1":"28"}}],"defaultValueType":"TEXT","required":true},{"slot":7,"variableName":"cloud_storage_area","variableType":"SCALAR","displayName":"Cloud Storage Staging Area","description":"The URL and path of the target Google Storage bucket to be used for staging the queried data throughout the job.","defaultValue":[{"values":{"1":""}}],"defaultValueType":"TEXT","required":true},{"slot":8,"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":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":""}}],"defaultValueType":"TEXT","required":true},{"slot":10,"variableName":"stage_dataset","variableType":"SCALAR","displayName":"Stage Dataset","description":"The dataset name where the staging data will be stored.","defaultValue":[{"values":{"1":""}}],"defaultValueType":"TEXT","required":true},{"slot":11,"variableName":"stage_project","variableType":"SCALAR","displayName":"Stage Project","description":"The project name where the staging data will be stored.","defaultValue":[{"values":{"1":""}}],"defaultValueType":"TEXT","required":true},{"slot":12,"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":""}}],"defaultValueType":"TEXT","required":false},{"slot":13,"variableName":"target_dataset","variableType":"SCALAR","displayName":"Target Dataset","description":"The dataset name where the target data will be stored.","defaultValue":[{"values":{"1":""}}],"defaultValueType":"TEXT","required":true},{"slot":14,"variableName":"target_project","variableType":"SCALAR","displayName":"Target Project","description":"The project name where the target data will be stored.","defaultValue":[{"values":{"1":""}}],"defaultValueType":"TEXT","required":true},{"slot":15,"variableName":"debug_mode","variableType":"SCALAR","displayName":"Debug Mode","description":"","defaultValue":[{"values":{"1":"FALSE"}}],"defaultValueType":"TEXT","required":true},{"slot":16,"variableName":"log_metrics","variableType":"SCALAR","displayName":"Log Metrics","description":"Option to switch off Metrics logging in the full product - default is TRUE which records the metrics for the batch run. ","defaultValue":[{"values":{"1":"TRUE"}}],"defaultValueType":"TEXT","required":false}]},"orchestrationJobs":["Google Analytics - 1 - Iterate Objects","Google Analytics - 3 - Stage Object","Incremental lib - Check source metadata","Google Analytics - 4 - Query Source","Google Analytics - 3-1 - Stage and Load View","Google Analytics - 2 - Stage Object"],"transformationJobs":[],"imageMetadata":{"componentIcon":"iVBORw0KGgoAAAANSUhEUgAAACAAAAAgCAYAAABzenr0AAAC/ElEQVR42u2XX0hTURzHfYty+RBSTz3kjIIIopAgiAgi+gP9gXCbprvq1LRpaMttbasHM7A0a6Zm217KtM16KKgINyOKqCCzFdTDQKiHoCL06rrubvbrnEP3NLvnOldu9tCFL+wczjmfz86/y83I+P/EPdCzJAtcKg14MrmUBI+NGMpw96IQuDMhtUEMlgSxSzn8ZxBLLoCnKC0CKhBv7+DmSUAFwpATeJ6fDwEKx9GnWQDBX7ZL8HQLYPjFeHg6BTC843d4mgQ8CD7cyYLDROhNigU8ixG8iwn/1t8Oolad3CkQB/QQeXwsCfglBbgTopqc5AREPwf82CgZYPKJIzH8VTcb7rtA4EkJiP4SCpcy+ezUDPDLbLj3F3zWAqK/VAYnAs+bkEQT2eHT4S4mfNznnAaflYAYMLDhQ+fRi2QB8KNfyK1GJDA86GbCP/V3gc+oTU5ADJQz4ULQhYALqQCpQ+stBD0K8E44ZzoMLSYjvOM2wYRuVWIBcbCCCQ8HfDB1chmBxwsoRehrhYcVO8FqtYLNYgG7xQwnLA1wr2ovRLRqtoDw9ibHj43J4X4fRHUrifl3R3ZCAaG3hf7Tu1X7oKO+Aia1ufCibCs0NtRDX00BWwC/IuVwL4VLmbJnKwoI187CDaMGPheupe2HS7fQ3++LN5DZ6D5SVplQgAWXInjbGPAz8LVgDZn2keI8Zj+c3ppC3MY7o0B4QBlOJdA6U3hPM6kL6TcSgQiacqV+Dyp34TZDigLhgesJ4VQCTblwtZmWPxStJwJ4JpT63Knaj9sMsgRKkoGzImrUcLqhDgKVu2nduG412flS+UptMTjMZrv8FLRWc1Fd7h/DpTw1bAMH2mjB0s2kjPdDT+1BKjGO7oOPRevkpyCmVWv+Fi7l/qE9cNxqge46Azl2eFniJWL5K/LlF1FhblZMkxOaKwl85G5VHyD3ABZoO1oNPFoOzMAs9tcRlkB2+KKYqzwq396IBF6PcHkcHlsRnqrHZrMtN5lMS//ZD+IfWa9aiMHIygAAAAAASUVORK5CYII=","smallIcon":"iVBORw0KGgoAAAANSUhEUgAAABAAAAAQCAYAAAAf8/9hAAABl0lEQVR42q2TyUsCUQCHvWfHrpESQoSW1xaJopPRoYMpFGIZRVk0UUIQ+Ad0j6iICCmVGgZPXeoWEbZcyspCoZFICGPGgplk5tcs4lZISg9+PN7yfW/jaTT/UbCltWBTS0o19aeocy0FgdJRh2oibjeQxTugqoGFoBEsfUXVJBCCJrCvD2AYpnqBEGyT4JgMg0m//S4Q/U0Q9wwVYTbxAH7J+lMg+nXI0BGwLzcQ91uK4HYJfszB9+BnLcgO60oFKnyhTpLOyJ35IASMEEISnFLh90QMScKKL7u+VCAGWqkMfameLZmAsGsGd7qsrMqmnlRp/A4HvnmsERM4d/eVCpj0C6XAdAL8Qj+yrkZwxyuqMAc/EwNIjZgQdXUi4u7FzpwzXBBIT8LQcQWWtyeH93RL4L0Cp+f6EJ4eyo/J2SDG4nkBG7+liuG8ZKZLyYfDgJDHrvRx9malXp8fv84Lsk4jWQ6X52TSiqOpQQQ8DiRHzfi0Gw4LApvOkrXpSfliKiXq6qBWF2eiF+4eUmZq+rler7e+uP0Nm/wYPV6mAZMAAAAASUVORK5CYII="},"description":"","helpHtml":"<div class=\"mtln\"><img src=\"data:image/png;base64,iVBORw0KGgoAAAANSUhEUgAAACAAAAAgCAYAAABzenr0AAAC/ElEQVR42u2XX0hTURzHfYty+RBSTz3kjIIIopAgiAgi+gP9gXCbprvq1LRpaMttbasHM7A0a6Zm217KtM16KKgINyOKqCCzFdTDQKiHoCL06rrubvbrnEP3NLvnOldu9tCFL+wczjmfz86/y83I+P/EPdCzJAtcKg14MrmUBI+NGMpw96IQuDMhtUEMlgSxSzn8ZxBLLoCnKC0CKhBv7+DmSUAFwpATeJ6fDwEKx9GnWQDBX7ZL8HQLYPjFeHg6BTC843d4mgQ8CD7cyYLDROhNigU8ixG8iwn/1t8Oolad3CkQB/QQeXwsCfglBbgTopqc5AREPwf82CgZYPKJIzH8VTcb7rtA4EkJiP4SCpcy+ezUDPDLbLj3F3zWAqK/VAYnAs+bkEQT2eHT4S4mfNznnAaflYAYMLDhQ+fRi2QB8KNfyK1GJDA86GbCP/V3gc+oTU5ADJQz4ULQhYALqQCpQ+stBD0K8E44ZzoMLSYjvOM2wYRuVWIBcbCCCQ8HfDB1chmBxwsoRehrhYcVO8FqtYLNYgG7xQwnLA1wr2ovRLRqtoDw9ibHj43J4X4fRHUrifl3R3ZCAaG3hf7Tu1X7oKO+Aia1ufCibCs0NtRDX00BWwC/IuVwL4VLmbJnKwoI187CDaMGPheupe2HS7fQ3++LN5DZ6D5SVplQgAWXInjbGPAz8LVgDZn2keI8Zj+c3ppC3MY7o0B4QBlOJdA6U3hPM6kL6TcSgQiacqV+Dyp34TZDigLhgesJ4VQCTblwtZmWPxStJwJ4JpT63Knaj9sMsgRKkoGzImrUcLqhDgKVu2nduG412flS+UptMTjMZrv8FLRWc1Fd7h/DpTw1bAMH2mjB0s2kjPdDT+1BKjGO7oOPRevkpyCmVWv+Fi7l/qE9cNxqge46Azl2eFniJWL5K/LlF1FhblZMkxOaKwl85G5VHyD3ABZoO1oNPFoOzMAs9tcRlkB2+KKYqzwq396IBF6PcHkcHlsRnqrHZrMtN5lMS//ZD+IfWa9aiMHIygAAAAAASUVORK5CYII=\"><h3>Google Analytics 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;'>The name of the OAuth entry to be used from the Matillion OAuth Manager to connect to the source. These must be set up in advance, using <b>Project → Manage OAuth</b>.</td></tr><tr><td>Tables and Columns</td><td>Grid</td><td style='white-space: pre-line; word-wrap: break-word;'>Contains the list of tables and columns (and in some cases an incremental_column to specify which column the load should be incremented on [accepts values of 0 or 1]) to be processed. </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>Start Date for Data Fetching</td><td>Variable</td><td style='white-space: pre-line; word-wrap: break-word;'>The start date for fetching data from Google Analytics. Please note that as the interval for making calls to the API is set by default as 1 day via job variable &#39;load_int&#39; that the further back the RangeStartDate is set, the more processes will be spawned and longer the overall ELT process will take to run.</td></tr><tr><td>Load Interval for Microbatching</td><td>Variable</td><td style='white-space: pre-line; word-wrap: break-word;'>The default microbatch interval for staging and loading - the actual microbatch will be interval -1 days.</td></tr><tr><td>Cloud Storage Staging Area</td><td>Variable</td><td style='white-space: pre-line; word-wrap: break-word;'>The URL and path of the target Google Storage bucket to be used for staging the queried data throughout the job.</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 &quot;Variables to Iterate&quot; to be defined as\n&lt;a href=&quot;https://redshiftsupport.matillion.com/customer/portal/articles/2037630?b_id=8915#header1&quot; target=&quot;_blank&quot;&gt;<i>Copied</i> variables&lt;/a&gt;, so that each iteration gets its own copy of the variable isolated from the same\nvariable being used by other concurrent executions.\n&lt;span class=&quot;env rs sf&quot;&gt;<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>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 &#39;stage_&#39; is specified and the table being processed is named &#39;test_data&#39; then the target table will be named &#39;stage_test_data&#39;.</td></tr><tr><td>Stage Dataset</td><td>Variable</td><td style='white-space: pre-line; word-wrap: break-word;'>The dataset name where the staging data will be stored.</td></tr><tr><td>Stage Project</td><td>Variable</td><td style='white-space: pre-line; word-wrap: break-word;'>The project 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 &#39;target_&#39; is specified and the table being processed is named &#39;test_data&#39; then the target table will be named &#39;target_test_data&#39;.</td></tr><tr><td>Target Dataset</td><td>Variable</td><td style='white-space: pre-line; word-wrap: break-word;'>The dataset name where the target data will be stored.</td></tr><tr><td>Target Project</td><td>Variable</td><td style='white-space: pre-line; word-wrap: break-word;'>The project name where the target data will be stored.</td></tr><tr><td>Debug Mode</td><td>Variable</td><td style='white-space: pre-line; word-wrap: break-word;'></td></tr><tr><td>Log Metrics</td><td>Variable</td><td style='white-space: pre-line; word-wrap: break-word;'>Option to switch off Metrics logging in the full product - default is TRUE which records the metrics for the batch run. </td></tr></tbody></table></div>","created":1619093687653},"orchestrationJobs":{"Google Analytics - 3-1 - Stage and Load View":{"id":-2,"revision":1,"created":1619081773989,"timestamp":1619081773989,"components":{"4780":{"id":4780,"inputCardinality":"ZERO","outputCardinality":"MANY","connectorHint":"UNCONDITIONAL","executionHint":"FLOW","implementationID":444132438,"x":-544,"y":-64,"width":32,"height":32,"inputConnectorIDs":[],"outputSuccessConnectorIDs":[],"outputFailureConnectorIDs":[],"outputUnconditionalConnectorIDs":[4790],"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":[]},"4781":{"id":4781,"inputCardinality":"ONE","outputCardinality":"MANY","connectorHint":"SUCCESS_FAIL","executionHint":"EXECUTE","implementationID":-1255297039,"x":208,"y":-64,"width":32,"height":32,"inputConnectorIDs":[4788],"outputSuccessConnectorIDs":[4789],"outputFailureConnectorIDs":[],"outputUnconditionalConnectorIDs":[],"outputTrueConnectorIDs":[],"outputFalseConnectorIDs":[],"exportMappings":{},"parameters":{"1":{"slot":1,"name":"Name","elements":{"1":{"slot":1,"values":{"1":{"slot":1,"type":"STRING","value":"Sum Logged Inserted Recs"}}}},"visible":true,"status":{},"columnNames":[]},"2":{"slot":2,"name":"Script","elements":{"1":{"slot":1,"values":{"1":{"slot":1,"type":"STRING","value":"# BigQuery does not get row counts correctly (always pulls full table row count)\n# must supply a static row count (from the staging table) \n# which will replace the wrongly calculated variable\nif target_platform.lower()=='bigquery':\n  context.updateVariable('logged_rows_inserted_local',static_row_count)\n\n# need to bubble up rows inserted from this iteration and combine with\n# total rows inserted from previous iterations\nlogged_rows_inserted = int(str(logged_rows_inserted)) + int(str(logged_rows_inserted_local))\ncontext.updateVariable('logged_rows_inserted', logged_rows_inserted)\n\nmsg=\\\n\"\"\"## Metrics for {load_strategy} load into {target_table_name} ##\nInserted: {logged_rows_inserted_local}\nTotal Inserted: {logged_rows_inserted}\n\"\"\".format(\n  load_strategy = load_strategy,\n  target_table_name = target_table_name,\n  logged_rows_inserted_local = logged_rows_inserted_local,\n  logged_rows_inserted = logged_rows_inserted\n  )\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":[]},"4782":{"id":4782,"inputCardinality":"ONE","outputCardinality":"MANY","connectorHint":"SUCCESS_FAIL","executionHint":"TRANSFORM","implementationID":-1180878087,"x":-224,"y":-64,"width":32,"height":32,"inputConnectorIDs":[4786],"outputSuccessConnectorIDs":[4787],"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 (View)"}}}},"visible":true,"status":{},"columnNames":[]},"2":{"slot":2,"name":"Job Name","elements":{"1":{"slot":1,"values":{"1":{"slot":1,"type":"STRING","value":"Google Analytics - 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":"Google Analytics 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":"2"}}}},"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":[]},"4783":{"id":4783,"inputCardinality":"ONE","outputCardinality":"MANY","connectorHint":"SUCCESS_FAIL","executionHint":"EXECUTE","implementationID":-1255297039,"x":-368,"y":-64,"width":32,"height":32,"inputConnectorIDs":[4790],"outputSuccessConnectorIDs":[4786],"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":"# set base stage_single_sql\nstage_sql = (f'SELECT {source_column_list} \\n'\n             f'FROM {table_name} \\n'\n             f\"WHERE startDate = '{batch_start_date}' \\n\"\n             f\"AND enddate = '{batch_end_date}'\"\n)\n\ncontext.updateVariable('stage_sql', stage_sql)\nprint(f\"Updated Variable 'stage_sql' with {stage_sql}\")\n\n# use upper case column names in Snowflake to prevent issues with QUOTED_IDENTIFIERS_IGNORE_CASE\nif target_platform.lower()=='snowflake':\n    incremental_column = incremental_column.upper()\n\n    context.updateVariable('incremental_column', incremental_column)\n    print(f\"Updated Variable 'incremental_column' with {incremental_column}\")\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":[]},"4784":{"id":4784,"inputCardinality":"ONE","outputCardinality":"MANY","connectorHint":"SUCCESS_FAIL","executionHint":"EXECUTE","implementationID":-313802845,"x":-16,"y":-64,"width":32,"height":32,"inputConnectorIDs":[4787],"outputSuccessConnectorIDs":[4788],"outputFailureConnectorIDs":[],"outputUnconditionalConnectorIDs":[],"outputTrueConnectorIDs":[],"outputFalseConnectorIDs":[],"exportMappings":{"1":{"slot":1,"fromId":null,"fromName":"logged_rows_inserted","mapTo":"logged_rows_inserted_local","validationStatus":null,"validationMessage":null}},"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":"ColumnName"}}}},"visible":true,"status":{},"columnNames":[]},"18":{"slot":18,"name":"key_columns","elements":{},"visible":true,"status":{},"columnNames":[]},"19":{"slot":19,"name":"incremental_column","elements":{"1":{"slot":1,"values":{"1":{"slot":1,"type":"STRING","value":"${incremental_column}"}}}},"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":"FALSE"}}}},"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":"${batch_start_date}"}}}},"visible":true,"status":{},"columnNames":[]}},"validationStatus":{},"status":{},"expectedFailure":null,"activationStatus":"ENABLED","outputIterationConnectorIDs":[],"inputIterationConnectorIDs":[]},"4785":{"id":4785,"inputCardinality":"ONE","outputCardinality":"ZERO","connectorHint":"UNCONDITIONAL","executionHint":"FLOW","implementationID":-1946388514,"x":416,"y":-64,"width":32,"height":32,"inputConnectorIDs":[4789],"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":[]}},"successConnectors":{"4786":{"id":4786,"sourceID":4783,"targetID":4782},"4787":{"id":4787,"sourceID":4782,"targetID":4784},"4788":{"id":4788,"sourceID":4784,"targetID":4781},"4789":{"id":4789,"sourceID":4781,"targetID":4785}},"failureConnectors":{},"unconditionalConnectors":{"4790":{"id":4790,"sourceID":4780,"targetID":4783}},"trueConnectors":{},"falseConnectors":{},"iterationConnectors":{},"noteConnectors":{},"canUndo":false,"undoCommand":"","undoCreated":0,"canRedo":false,"redoCommand":"","redoCreated":0,"notes":{"4777":{"id":4777,"x":-106,"y":-189,"width":197,"height":184,"text":"Run transformation job to load records into target.\n\n__Log Metrics__ hardcoded as FALSE as we want to sum the counts at the parent job (stage 2)","colour":"e6e63c"},"4778":{"id":4778,"x":-441,"y":-186,"width":307,"height":183,"text":"Stage data for current micro batch using __sub_RangeStartDate__ and __sub_RangeEndDate__","colour":"e6e63c"},"4779":{"id":4779,"x":117,"y":-188,"width":196,"height":182,"text":"Increment running count of inserted records to export and import into next iteration","colour":"e6e63c"}},"variables":{"oauth":{"definition":{"name":"oauth","type":"TEXT","scope":"BRANCH","description":"","visibility":"PUBLIC"},"value":""},"stage_sql":{"definition":{"name":"stage_sql","type":"TEXT","scope":"TASKBATCH","description":"","visibility":"PRIVATE"},"value":""},"dist_style":{"definition":{"name":"dist_style","type":"TEXT","scope":"BRANCH","description":"","visibility":"PUBLIC"},"value":""},"kms_key_id":{"definition":{"name":"kms_key_id","type":"TEXT","scope":"BRANCH","description":"","visibility":"PUBLIC"},"value":""},"table_name":{"definition":{"name":"table_name","type":"TEXT","scope":"BRANCH","description":"","visibility":"PUBLIC"},"value":""},"bucket_name":{"definition":{"name":"bucket_name","type":"TEXT","scope":"BRANCH","description":"","visibility":"PUBLIC"},"value":""},"log_metrics":{"definition":{"name":"log_metrics","type":"TEXT","scope":"BRANCH","description":"","visibility":"PUBLIC"},"value":""},"stage_prefix":{"definition":{"name":"stage_prefix","type":"TEXT","scope":"BRANCH","description":"","visibility":"PUBLIC"},"value":""},"stage_schema":{"definition":{"name":"stage_schema","type":"TEXT","scope":"BRANCH","description":"","visibility":"PUBLIC"},"value":""},"staging_type":{"definition":{"name":"staging_type","type":"TEXT","scope":"BRANCH","description":"","visibility":"PUBLIC"},"value":""},"error_message":{"definition":{"name":"error_message","type":"TEXT","scope":"TASKBATCH","description":"","visibility":"PRIVATE"},"value":""},"load_strategy":{"definition":{"name":"load_strategy","type":"TEXT","scope":"BRANCH","description":"","visibility":"PUBLIC"},"value":""},"stage_dataset":{"definition":{"name":"stage_dataset","type":"TEXT","scope":"BRANCH","description":"","visibility":"PUBLIC"},"value":""},"stage_project":{"definition":{"name":"stage_project","type":"TEXT","scope":"BRANCH","description":"","visibility":"PUBLIC"},"value":""},"target_prefix":{"definition":{"name":"target_prefix","type":"TEXT","scope":"BRANCH","description":"","visibility":"PUBLIC"},"value":""},"target_schema":{"definition":{"name":"target_schema","type":"TEXT","scope":"BRANCH","description":"","visibility":"PUBLIC"},"value":""},"batch_end_date":{"definition":{"name":"batch_end_date","type":"TEXT","scope":"BRANCH","description":"","visibility":"PUBLIC"},"value":""},"blob_container":{"definition":{"name":"blob_container","type":"TEXT","scope":"BRANCH","description":"","visibility":"PUBLIC"},"value":""},"stage_database":{"definition":{"name":"stage_database","type":"TEXT","scope":"BRANCH","description":"","visibility":"PUBLIC"},"value":""},"target_dataset":{"definition":{"name":"target_dataset","type":"TEXT","scope":"BRANCH","description":"","visibility":"PUBLIC"},"value":""},"target_project":{"definition":{"name":"target_project","type":"TEXT","scope":"BRANCH","description":"","visibility":"PUBLIC"},"value":""},"stage_warehouse":{"definition":{"name":"stage_warehouse","type":"TEXT","scope":"BRANCH","description":"","visibility":"PUBLIC"},"value":""},"storage_account":{"definition":{"name":"storage_account","type":"TEXT","scope":"BRANCH","description":"","visibility":"PUBLIC"},"value":""},"target_database":{"definition":{"name":"target_database","type":"TEXT","scope":"BRANCH","description":"","visibility":"PUBLIC"},"value":""},"target_platform":{"definition":{"name":"target_platform","type":"TEXT","scope":"BRANCH","description":"","visibility":"PUBLIC"},"value":""},"batch_start_date":{"definition":{"name":"batch_start_date","type":"TEXT","scope":"BRANCH","description":"","visibility":"PUBLIC"},"value":""},"stage_table_name":{"definition":{"name":"stage_table_name","type":"TEXT","scope":"BRANCH","description":"Holds the table name of the staged table name.","visibility":"PUBLIC"},"value":""},"static_row_count":{"definition":{"name":"static_row_count","type":"TEXT","scope":"TASKBATCH","description":"","visibility":"PRIVATE"},"value":"0"},"target_warehouse":{"definition":{"name":"target_warehouse","type":"TEXT","scope":"BRANCH","description":"","visibility":"PUBLIC"},"value":""},"encryption_method":{"definition":{"name":"encryption_method","type":"TEXT","scope":"BRANCH","description":"","visibility":"PUBLIC"},"value":""},"target_table_name":{"definition":{"name":"target_table_name","type":"TEXT","scope":"BRANCH","description":"Holds the target table name.","visibility":"PUBLIC"},"value":""},"cloud_storage_area":{"definition":{"name":"cloud_storage_area","type":"TEXT","scope":"BRANCH","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":"BRANCH","description":"","visibility":"PUBLIC"},"value":""},"source_column_list":{"definition":{"name":"source_column_list","type":"TEXT","scope":"BRANCH","description":"Used internally to hold a string value of matched columns.","visibility":"PUBLIC"},"value":""},"logged_rows_deleted":{"definition":{"name":"logged_rows_deleted","type":"TEXT","scope":"TASKBATCH","description":"","visibility":"PUBLIC"},"value":"0"},"logged_rows_updated":{"definition":{"name":"logged_rows_updated","type":"DECIMAL","scope":"TASKBATCH","description":"","visibility":"PUBLIC"},"value":"0"},"logged_rows_inserted":{"definition":{"name":"logged_rows_inserted","type":"DECIMAL","scope":"TASKBATCH","description":"","visibility":"PUBLIC"},"value":"0"},"logged_rows_updated_local":{"definition":{"name":"logged_rows_updated_local","type":"DECIMAL","scope":"TASKBATCH","description":"","visibility":"PRIVATE"},"value":"0"},"logged_rows_inserted_local":{"definition":{"name":"logged_rows_inserted_local","type":"DECIMAL","scope":"TASKBATCH","description":"","visibility":"PRIVATE"},"value":"0"}},"grids":{"stage_table_metadata":{"definition":{"name":"stage_table_metadata","scope":"BRANCH","definitions":[{"name":"ColumnName","type":"TEXT"},{"name":"Type","type":"TEXT"}],"description":"","visibility":"PUBLIC"},"values":[]},"advanced_connection_options":{"definition":{"name":"advanced_connection_options","scope":"BRANCH","definitions":[{"name":"parameter","type":"TEXT"},{"name":"value","type":"TEXT"}],"description":"","visibility":"PUBLIC"},"values":[]}}},"Google Analytics - 4 - Query Source":{"id":-2,"revision":1,"created":1619081773911,"timestamp":1619081773911,"components":{"4793":{"id":4793,"inputCardinality":"ONE","outputCardinality":"ZERO","connectorHint":"UNCONDITIONAL","executionHint":"FLOW","implementationID":515156205,"x":-320,"y":336,"width":32,"height":32,"inputConnectorIDs":[4799],"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":[]},"4794":{"id":4794,"inputCardinality":"ZERO","outputCardinality":"MANY","connectorHint":"UNCONDITIONAL","executionHint":"FLOW","implementationID":444132438,"x":-496,"y":-16,"width":32,"height":32,"inputConnectorIDs":[],"outputSuccessConnectorIDs":[],"outputFailureConnectorIDs":[],"outputUnconditionalConnectorIDs":[4802],"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":[]},"4795":{"id":4795,"inputCardinality":"ONE","outputCardinality":"MANY","connectorHint":"SUCCESS_FAIL","executionHint":"EXECUTE","implementationID":-1255297039,"x":-320,"y":96,"width":32,"height":32,"inputConnectorIDs":[4803],"outputSuccessConnectorIDs":[4800],"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":[]},"4796":{"id":4796,"inputCardinality":"ONE","outputCardinality":"MANY","connectorHint":"SUCCESS_FAIL","executionHint":"EXECUTE","implementationID":-1334993426,"x":-320,"y":208,"width":32,"height":32,"inputConnectorIDs":[4800],"outputSuccessConnectorIDs":[4799],"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":[]},"4797":{"id":4797,"inputCardinality":"ONE","outputCardinality":"ZERO","connectorHint":"UNCONDITIONAL","executionHint":"FLOW","implementationID":-1946388514,"x":-144,"y":-16,"width":32,"height":32,"inputConnectorIDs":[4801],"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":[]},"4798":{"id":4798,"inputCardinality":"ONE","outputCardinality":"MANY","connectorHint":"SUCCESS_FAIL","executionHint":"EXECUTE","implementationID":-454427654,"x":-320,"y":-16,"width":32,"height":32,"inputConnectorIDs":[4802],"outputSuccessConnectorIDs":[4801],"outputFailureConnectorIDs":[4803],"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":"Dataset","elements":{"1":{"slot":1,"values":{"1":{"slot":1,"type":"STRING","value":"${dataset}"}}}},"visible":true,"status":{},"columnNames":[]},"14":{"slot":14,"name":"Target Table","elements":{"1":{"slot":1,"values":{"1":{"slot":1,"type":"STRING","value":"${table}"}}}},"visible":true,"status":{},"columnNames":[]},"15":{"slot":15,"name":"Cloud Storage Area","elements":{"1":{"slot":1,"values":{"1":{"slot":1,"type":"STRING","value":"${cloud_storage_area}"}}}},"visible":true,"status":{},"columnNames":[]},"16":{"slot":16,"name":"Load Options","elements":{"1":{"slot":1,"values":{"1":{"slot":1,"type":"STRING","value":"On"}}},"3":{"slot":3,"values":{"1":{"slot":1,"type":"STRING","value":"On"}}}},"visible":true,"status":{},"columnNames":[]},"17":{"slot":17,"name":"Project","elements":{"1":{"slot":1,"values":{"1":{"slot":1,"type":"STRING","value":"${project}"}}}},"visible":true,"status":{},"columnNames":[]},"20":{"slot":20,"name":"Data Schema","elements":{"1":{"slot":1,"values":{"1":{"slot":1,"type":"STRING","value":""}}}},"visible":false,"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":[]}},"validationStatus":{},"status":{},"expectedFailure":null,"activationStatus":"ENABLED","outputIterationConnectorIDs":[],"inputIterationConnectorIDs":[]}},"successConnectors":{"4799":{"id":4799,"sourceID":4796,"targetID":4793},"4800":{"id":4800,"sourceID":4795,"targetID":4796},"4801":{"id":4801,"sourceID":4798,"targetID":4797}},"failureConnectors":{"4803":{"id":4803,"sourceID":4798,"targetID":4795}},"unconditionalConnectors":{"4802":{"id":4802,"sourceID":4794,"targetID":4798}},"trueConnectors":{},"falseConnectors":{},"iterationConnectors":{},"noteConnectors":{},"canUndo":false,"undoCommand":"","undoCreated":0,"canRedo":false,"redoCommand":"","redoCreated":0,"notes":{"4792":{"id":4792,"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":[]}}},"Google Analytics - 1 - Iterate Objects":{"id":-2,"revision":1,"created":1619081773916,"timestamp":1619081773916,"components":{"581":{"id":581,"inputCardinality":"ONE","outputCardinality":"MANY","connectorHint":"SUCCESS_FAIL","executionHint":"EXECUTE","implementationID":-1255297039,"x":-560,"y":16,"width":32,"height":32,"inputConnectorIDs":[599],"outputSuccessConnectorIDs":[592],"outputFailureConnectorIDs":[],"outputUnconditionalConnectorIDs":[],"outputTrueConnectorIDs":[],"outputFalseConnectorIDs":[],"exportMappings":{},"parameters":{"1":{"slot":1,"name":"Name","elements":{"1":{"slot":1,"values":{"1":{"slot":1,"type":"STRING","value":"Set default values"}}}},"visible":true,"status":{},"columnNames":[]},"2":{"slot":2,"name":"Script","elements":{"1":{"slot":1,"values":{"1":{"slot":1,"type":"STRING","value":"if target_platform.lower() == 'snowflake':\n    if not staging_type or staging_type.lower() not in ['snowflake managed', 'existing amazon s3 location']:\n        staging_type = 'Snowflake Managed'\n        context.updateVariable('staging_type', staging_type)\n        print(f'staging_type: {staging_type}')\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":[]},"582":{"id":582,"inputCardinality":"ONE","outputCardinality":"MANY","connectorHint":"SUCCESS_FAIL","executionHint":"EXECUTE","implementationID":-1255297039,"x":-224,"y":16,"width":32,"height":32,"inputConnectorIDs":[595],"outputSuccessConnectorIDs":[593],"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('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: {str(source_list)}\")\nprint(f\"distinct_table_names: {str(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":[]},"583":{"id":583,"inputCardinality":"ONE","outputCardinality":"MANY","connectorHint":"SUCCESS_FAIL","executionHint":"EXECUTE","implementationID":-1255297039,"x":-32,"y":16,"width":32,"height":32,"inputConnectorIDs":[593],"outputSuccessConnectorIDs":[597],"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":[]},"584":{"id":584,"inputCardinality":"ONE","outputCardinality":"MANY","connectorHint":"SUCCESS_FAIL","executionHint":"TRANSFORM","implementationID":-1180878087,"x":176,"y":48,"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":"Google Analytics - 2 - 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":"${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":"oauth"},"2":{"slot":2,"type":"STRING","value":"${oauth}"}}},"21":{"slot":21,"values":{"1":{"slot":1,"type":"STRING","value":"encryption_method"},"2":{"slot":2,"type":"STRING","value":"${encryption_method}"}}},"22":{"slot":22,"values":{"1":{"slot":1,"type":"STRING","value":"kms_key_id"},"2":{"slot":2,"type":"STRING","value":"${kms_key_id}"}}},"23":{"slot":23,"values":{"1":{"slot":1,"type":"STRING","value":"staging_type"},"2":{"slot":2,"type":"STRING","value":"${staging_type}"}}},"24":{"slot":24,"values":{"1":{"slot":1,"type":"STRING","value":"log_metrics"},"2":{"slot":2,"type":"STRING","value":"${log_metrics}"}}},"25":{"slot":25,"values":{"1":{"slot":1,"type":"STRING","value":"sub_RangeStartDate"},"2":{"slot":2,"type":"STRING","value":"${sub_RangeStartDate}"}}},"26":{"slot":26,"values":{"1":{"slot":1,"type":"STRING","value":"load_int"},"2":{"slot":2,"type":"STRING","value":"${load_int}"}}}},"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"}}},"4":{"slot":4,"elements":{"1":{"slot":1,"values":{"1":{"slot":1,"type":"GRID","value":"internal_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":"Google Analytics 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":"2"}}}},"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":[640]},"585":{"id":585,"inputCardinality":"ONE","outputCardinality":"MANY","connectorHint":"SUCCESS_FAIL","executionHint":"ITERATE","implementationID":-424773870,"x":176,"y":16,"width":32,"height":16,"inputConnectorIDs":[597],"outputSuccessConnectorIDs":[594,596],"outputFailureConnectorIDs":[600],"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":true,"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":[640],"inputIterationConnectorIDs":[]},"586":{"id":586,"inputCardinality":"ZERO","outputCardinality":"MANY","connectorHint":"UNCONDITIONAL","executionHint":"FLOW","implementationID":444132438,"x":-704,"y":16,"width":32,"height":32,"inputConnectorIDs":[],"outputSuccessConnectorIDs":[],"outputFailureConnectorIDs":[],"outputUnconditionalConnectorIDs":[599],"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":[]},"587":{"id":587,"inputCardinality":"ONE","outputCardinality":"ZERO","connectorHint":"UNCONDITIONAL","executionHint":"FLOW","implementationID":-1946388514,"x":416,"y":16,"width":32,"height":32,"inputConnectorIDs":[594],"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":[]},"588":{"id":588,"inputCardinality":"ONE","outputCardinality":"ZERO","connectorHint":"UNCONDITIONAL","executionHint":"FLOW","implementationID":515156205,"x":416,"y":96,"width":32,"height":32,"inputConnectorIDs":[600],"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":[]},"589":{"id":589,"inputCardinality":"ONE","outputCardinality":"MANY","connectorHint":"SUCCESS_FAIL","executionHint":"EXECUTE","implementationID":-2112089365,"x":608,"y":-64,"width":32,"height":32,"inputConnectorIDs":[598],"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":[]},"590":{"id":590,"inputCardinality":"ONE","outputCardinality":"MANY","connectorHint":"SUCCESS_FAIL","executionHint":"EXECUTE","implementationID":-1255297039,"x":-400,"y":16,"width":32,"height":32,"inputConnectorIDs":[592],"outputSuccessConnectorIDs":[595],"outputFailureConnectorIDs":[],"outputUnconditionalConnectorIDs":[],"outputTrueConnectorIDs":[],"outputFalseConnectorIDs":[],"exportMappings":{},"parameters":{"1":{"slot":1,"name":"Name","elements":{"1":{"slot":1,"values":{"1":{"slot":1,"type":"STRING","value":"Copy source list"}}}},"visible":true,"status":{},"columnNames":[]},"2":{"slot":2,"name":"Script","elements":{"1":{"slot":1,"values":{"1":{"slot":1,"type":"STRING","value":"# map external source list with two columns to internal source list with four columns\n# setting inc_columns and key_columns to zero - actual values set in stage 2\nsource_list = context.getGridVariable('source_list')\ninternal_source_list = [[x[0], x[1], '0', '0'] for x in source_list]\ncontext.updateGridVariable('internal_source_list', internal_source_list)\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":[]},"591":{"id":591,"inputCardinality":"ONE","outputCardinality":"MANY","connectorHint":"CONDITIONAL","executionHint":"FLOW","implementationID":-1357378929,"x":416,"y":-64,"width":32,"height":32,"inputConnectorIDs":[596],"outputSuccessConnectorIDs":[],"outputFailureConnectorIDs":[],"outputUnconditionalConnectorIDs":[],"outputTrueConnectorIDs":[598],"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":[]}},"successConnectors":{"592":{"id":592,"sourceID":581,"targetID":590},"593":{"id":593,"sourceID":582,"targetID":583},"594":{"id":594,"sourceID":585,"targetID":587},"595":{"id":595,"sourceID":590,"targetID":582},"596":{"id":596,"sourceID":585,"targetID":591},"597":{"id":597,"sourceID":583,"targetID":585}},"failureConnectors":{"600":{"id":600,"sourceID":585,"targetID":588}},"unconditionalConnectors":{"599":{"id":599,"sourceID":586,"targetID":581}},"trueConnectors":{"598":{"id":598,"sourceID":591,"targetID":589}},"falseConnectors":{},"iterationConnectors":{"640":{"id":640,"sourceID":585,"targetID":584}},"noteConnectors":{},"canUndo":false,"undoCommand":"","undoCreated":0,"canRedo":false,"redoCommand":"","redoCreated":0,"notes":{"580":{"id":580,"x":-586,"y":-508,"width":1110,"height":390,"text":"**Google Analytics 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:**\nGoogle Analytics - 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 2019\nAuthor: Matillion ELT\nChange Summary: Initial version of Google Analytics load into cloud data warehouse.\nChange Detail: Initial version\nJob variable **log_metrics** set to TRUE.\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":""},"load_int":{"definition":{"name":"load_int","type":"TEXT","scope":"BRANCH","description":"","visibility":"PUBLIC"},"value":"28"},"debug_mode":{"definition":{"name":"debug_mode","type":"TEXT","scope":"BRANCH","description":"","visibility":"PUBLIC"},"value":"FALSE"},"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":"PRIVATE"},"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":"PRIVATE"},"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":"PRIVATE"},"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":"PUBLIC"},"value":""},"stage_project":{"definition":{"name":"stage_project","type":"TEXT","scope":"BRANCH","description":"The project name where the staging data will be stored.","visibility":"PUBLIC"},"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":"PRIVATE"},"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":"PRIVATE"},"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":"PUBLIC"},"value":""},"target_project":{"definition":{"name":"target_project","type":"TEXT","scope":"BRANCH","description":"The project name where the target data will be stored.","visibility":"PUBLIC"},"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":""},"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":"BigQuery"},"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":"PRIVATE"},"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":"PUBLIC"},"value":""},"sub_RangeStartDate":{"definition":{"name":"sub_RangeStartDate","type":"TEXT","scope":"TASKBATCH","description":"","visibility":"PUBLIC"},"value":"2005-01-01"}},"grids":{"source_list":{"definition":{"name":"source_list","scope":"BRANCH","definitions":[{"name":"table","type":"TEXT"},{"name":"column","type":"TEXT"}],"description":"Contains the list of tables and columns to be processed. The incremental_column should be set to 1 to indicate which column the incremental load is to be based on, 0 otherwise. There should be only 1 incremental column per table","visibility":"PUBLIC"},"values":[]},"distinct_table_names":{"definition":{"name":"distinct_table_names","scope":"BRANCH","definitions":[{"name":"table_name","type":"TEXT"}],"description":"","visibility":"PRIVATE"},"values":[]},"internal_source_list":{"definition":{"name":"internal_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":[]},"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":[]}}},"Incremental lib - Check source metadata":{"id":-2,"revision":1,"created":1619081773905,"timestamp":1619081773905,"components":{"4882":{"id":4882,"inputCardinality":"ZERO","outputCardinality":"MANY","connectorHint":"UNCONDITIONAL","executionHint":"FLOW","implementationID":444132438,"x":-480,"y":0,"width":32,"height":32,"inputConnectorIDs":[],"outputSuccessConnectorIDs":[],"outputFailureConnectorIDs":[],"outputUnconditionalConnectorIDs":[4896],"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":[]},"4883":{"id":4883,"inputCardinality":"ONE","outputCardinality":"MANY","connectorHint":"SUCCESS_FAIL","executionHint":"EXECUTE","implementationID":-1255297039,"x":0,"y":160,"width":32,"height":32,"inputConnectorIDs":[4895],"outputSuccessConnectorIDs":[4892],"outputFailureConnectorIDs":[],"outputUnconditionalConnectorIDs":[],"outputTrueConnectorIDs":[],"outputFalseConnectorIDs":[],"exportMappings":{},"parameters":{"1":{"slot":1,"name":"Name","elements":{"1":{"slot":1,"values":{"1":{"slot":1,"type":"STRING","value":"Set Error Message"}}}},"visible":true,"status":{},"columnNames":[]},"2":{"slot":2,"name":"Script","elements":{"1":{"slot":1,"values":{"1":{"slot":1,"type":"STRING","value":"context.updateVariable('error_message', 'Source table not found')"}}}},"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":[]},"4884":{"id":4884,"inputCardinality":"ONE","outputCardinality":"MANY","connectorHint":"SUCCESS_FAIL","executionHint":"EXECUTE","implementationID":-1334993426,"x":-160,"y":160,"width":32,"height":32,"inputConnectorIDs":[4894],"outputSuccessConnectorIDs":[],"outputFailureConnectorIDs":[],"outputUnconditionalConnectorIDs":[4895],"outputTrueConnectorIDs":[],"outputFalseConnectorIDs":[],"exportMappings":{},"parameters":{"1":{"slot":1,"name":"Name","elements":{"1":{"slot":1,"values":{"1":{"slot":1,"type":"STRING","value":"Log Missing Source Table"}}}},"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":"Source Table Missing"},"2":{"slot":2,"type":"STRING","value":"${table_name}"}}}},"visible":true,"status":{},"columnNames":[]}},"validationStatus":{},"status":{},"expectedFailure":null,"activationStatus":"ENABLED","outputIterationConnectorIDs":[],"inputIterationConnectorIDs":[]},"4885":{"id":4885,"inputCardinality":"ONE","outputCardinality":"ZERO","connectorHint":"UNCONDITIONAL","executionHint":"FLOW","implementationID":-1946388514,"x":160,"y":0,"width":32,"height":32,"inputConnectorIDs":[4891],"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":[]},"4886":{"id":4886,"inputCardinality":"ONE","outputCardinality":"MANY","connectorHint":"CONDITIONAL","executionHint":"FLOW","implementationID":-1357378929,"x":-160,"y":0,"width":32,"height":32,"inputConnectorIDs":[4890],"outputSuccessConnectorIDs":[],"outputFailureConnectorIDs":[],"outputUnconditionalConnectorIDs":[],"outputTrueConnectorIDs":[4893],"outputFalseConnectorIDs":[4894],"exportMappings":{},"parameters":{"1":{"slot":1,"name":"Name","elements":{"1":{"slot":1,"values":{"1":{"slot":1,"type":"STRING","value":"Metadata returned?"}}}},"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":"row_count"},"2":{"slot":2,"type":"STRING","value":"Is"},"3":{"slot":3,"type":"STRING","value":"Greater than"},"4":{"slot":4,"type":"STRING","value":"0"}}}},"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":[]},"4887":{"id":4887,"inputCardinality":"ONE","outputCardinality":"MANY","connectorHint":"SUCCESS_FAIL","executionHint":"EXECUTE","implementationID":1155583855,"x":-320,"y":0,"width":32,"height":32,"inputConnectorIDs":[4896],"outputSuccessConnectorIDs":[4890],"outputFailureConnectorIDs":[],"outputUnconditionalConnectorIDs":[],"outputTrueConnectorIDs":[],"outputFalseConnectorIDs":[],"exportMappings":{"1":{"slot":1,"fromId":null,"fromName":"Row Count","mapTo":"row_count","validationStatus":null,"validationMessage":null}},"parameters":{"1":{"slot":1,"name":"Name","elements":{"1":{"slot":1,"values":{"1":{"slot":1,"type":"STRING","value":"Load available columns from source"}}}},"visible":true,"status":{},"columnNames":[]},"2":{"slot":2,"name":"Project","elements":{"1":{"slot":1,"values":{"1":{"slot":1,"type":"STRING","value":"[Environment Default]"}}}},"visible":false,"status":{},"columnNames":[]},"3":{"slot":3,"name":"Dataset","elements":{"1":{"slot":1,"values":{"1":{"slot":1,"type":"STRING","value":"[Environment Default]"}}}},"visible":false,"status":{},"columnNames":[]},"4":{"slot":4,"name":"Table Name","elements":{"1":{"slot":1,"values":{"1":{"slot":1,"type":"STRING","value":""}}}},"visible":false,"status":{},"columnNames":[]},"5":{"slot":5,"name":"Basic/Advanced","elements":{"1":{"slot":1,"values":{"1":{"slot":1,"type":"STRING","value":"Advanced"}}}},"visible":true,"status":{},"columnNames":[]},"6":{"slot":6,"name":"Table Columns","elements":{},"visible":false,"status":{},"columnNames":[]},"7":{"slot":7,"name":"SQL Query","elements":{"1":{"slot":1,"values":{"1":{"slot":1,"type":"STRING","value":"${get_metadata_sql}"}}}},"visible":true,"status":{},"columnNames":[]},"8":{"slot":8,"name":"Grid Variable","elements":{"1":{"slot":1,"values":{"1":{"slot":1,"type":"STRING","value":"source_table_metadata"}}}},"visible":true,"status":{},"columnNames":[]},"9":{"slot":9,"name":"Grid Variable Mapping","elements":{"1":{"slot":1,"values":{"1":{"slot":1,"type":"STRING","value":"column_name"}}},"2":{"slot":2,"values":{"1":{"slot":1,"type":"STRING","value":"data_type_name"}}},"3":{"slot":3,"values":{"1":{"slot":1,"type":"STRING","value":"length"}}},"4":{"slot":4,"values":{"1":{"slot":1,"type":"STRING","value":"numeric_scale"}}},"5":{"slot":5,"values":{"1":{"slot":1,"type":"STRING","value":"is_key"}}},"6":{"slot":6,"values":{"1":{"slot":1,"type":"STRING","value":"is_nullable"}}},"7":{"slot":7,"values":{"1":{"slot":1,"type":"STRING","value":"ordinal"}}},"8":{"slot":8,"values":{"1":{"slot":1,"type":"STRING","value":"entity_type"}}}},"visible":true,"status":{},"columnNames":[]},"10":{"slot":10,"name":"Limit","elements":{"1":{"slot":1,"values":{"1":{"slot":1,"type":"INTEGER","value":"100"}}}},"visible":false,"status":{},"columnNames":[]},"11":{"slot":11,"name":"Order By","elements":{},"visible":false,"status":{},"columnNames":[]},"12":{"slot":12,"name":"Sort","elements":{"1":{"slot":1,"values":{"1":{"slot":1,"type":"STRING","value":"Ascending"}}}},"visible":false,"status":{},"columnNames":[]},"13":{"slot":13,"name":"Filter Conditions","elements":{},"visible":false,"status":{},"columnNames":[]},"14":{"slot":14,"name":"Combine Conditions","elements":{"1":{"slot":1,"values":{"1":{"slot":1,"type":"STRING","value":"AND"}}}},"visible":false,"status":{},"columnNames":[]}},"validationStatus":{},"status":{},"expectedFailure":null,"activationStatus":"ENABLED","outputIterationConnectorIDs":[],"inputIterationConnectorIDs":[]},"4888":{"id":4888,"inputCardinality":"ONE","outputCardinality":"ZERO","connectorHint":"UNCONDITIONAL","executionHint":"FLOW","implementationID":515156205,"x":160,"y":160,"width":32,"height":32,"inputConnectorIDs":[4892],"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 - Source table not found"}}}},"visible":true,"status":{},"columnNames":[]}},"validationStatus":{},"status":{},"expectedFailure":null,"activationStatus":"ENABLED","outputIterationConnectorIDs":[],"inputIterationConnectorIDs":[]},"4889":{"id":4889,"inputCardinality":"ONE","outputCardinality":"MANY","connectorHint":"SUCCESS_FAIL","executionHint":"EXECUTE","implementationID":-1291941950,"x":0,"y":0,"width":32,"height":32,"inputConnectorIDs":[4893],"outputSuccessConnectorIDs":[4891],"outputFailureConnectorIDs":[],"outputUnconditionalConnectorIDs":[],"outputTrueConnectorIDs":[],"outputFalseConnectorIDs":[],"exportMappings":{},"parameters":{"1":{"slot":1,"name":"Name","elements":{"1":{"slot":1,"values":{"1":{"slot":1,"type":"STRING","value":"Delete metadata stage table"}}}},"visible":true,"status":{},"columnNames":[]},"2":{"slot":2,"name":"Project","elements":{"1":{"slot":1,"values":{"1":{"slot":1,"type":"STRING","value":"${stage_project}"}}}},"visible":true,"status":{},"columnNames":[]},"3":{"slot":3,"name":"Dataset","elements":{"1":{"slot":1,"values":{"1":{"slot":1,"type":"STRING","value":"${stage_dataset}"}}}},"visible":true,"status":{},"columnNames":[]},"4":{"slot":4,"name":"Table","elements":{"1":{"slot":1,"values":{"1":{"slot":1,"type":"STRING","value":"${metadata_stage_table_name}"}}}},"visible":true,"status":{},"columnNames":[]},"5":{"slot":5,"name":"Ignore Missing","elements":{"1":{"slot":1,"values":{"1":{"slot":1,"type":"STRING","value":"No"}}}},"visible":true,"status":{},"columnNames":[]}},"validationStatus":{},"status":{},"expectedFailure":null,"activationStatus":"ENABLED","outputIterationConnectorIDs":[],"inputIterationConnectorIDs":[]}},"successConnectors":{"4890":{"id":4890,"sourceID":4887,"targetID":4886},"4891":{"id":4891,"sourceID":4889,"targetID":4885},"4892":{"id":4892,"sourceID":4883,"targetID":4888}},"failureConnectors":{},"unconditionalConnectors":{"4895":{"id":4895,"sourceID":4884,"targetID":4883},"4896":{"id":4896,"sourceID":4882,"targetID":4887}},"trueConnectors":{"4893":{"id":4893,"sourceID":4886,"targetID":4889}},"falseConnectors":{"4894":{"id":4894,"sourceID":4886,"targetID":4884}},"iterationConnectors":{},"noteConnectors":{},"canUndo":false,"undoCommand":"","undoCreated":0,"canRedo":false,"redoCommand":"","redoCreated":0,"notes":{"4881":{"id":4881,"x":-396,"y":-122,"width":469,"height":68,"text":"**Custom version of Check source metadata**\n\nFor Google Analytics, a custom version of this library job is used here because the source_table_metadata grid variable contains an extra column for entity_type.","colour":"e6e63c"}},"variables":{"row_count":{"definition":{"name":"row_count","type":"DECIMAL","scope":"TASKBATCH","description":"","visibility":"PRIVATE"},"value":"0"},"stage_schema":{"definition":{"name":"stage_schema","type":"TEXT","scope":"BRANCH","description":"","visibility":"PUBLIC"},"value":""},"error_message":{"definition":{"name":"error_message","type":"TEXT","scope":"TASKBATCH","description":"","visibility":"PUBLIC"},"value":""},"stage_dataset":{"definition":{"name":"stage_dataset","type":"TEXT","scope":"BRANCH","description":"","visibility":"PUBLIC"},"value":null},"stage_project":{"definition":{"name":"stage_project","type":"TEXT","scope":"BRANCH","description":"","visibility":"PUBLIC"},"value":null},"stage_database":{"definition":{"name":"stage_database","type":"TEXT","scope":"BRANCH","description":"","visibility":"PUBLIC"},"value":""},"get_metadata_sql":{"definition":{"name":"get_metadata_sql","type":"TEXT","scope":"TASKBATCH","description":"","visibility":"PUBLIC"},"value":null},"target_table_name":{"definition":{"name":"target_table_name","type":"TEXT","scope":"BRANCH","description":"","visibility":"PUBLIC"},"value":null},"metadata_stage_table_name":{"definition":{"name":"metadata_stage_table_name","type":"TEXT","scope":"BRANCH","description":"","visibility":"PUBLIC"},"value":""}},"grids":{"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"},{"name":"entity_type","type":"TEXT"}],"description":"","visibility":"PUBLIC"},"values":[]}}},"Google Analytics - 3 - Stage Object":{"id":-2,"revision":2,"created":1619093580036,"timestamp":1619093580036,"components":{"4811":{"id":4811,"inputCardinality":"ONE","outputCardinality":"ZERO","connectorHint":"UNCONDITIONAL","executionHint":"FLOW","implementationID":-1946388514,"x":1376,"y":16,"width":32,"height":32,"inputConnectorIDs":[4867],"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":[]},"4812":{"id":4812,"inputCardinality":"ZERO","outputCardinality":"MANY","connectorHint":"UNCONDITIONAL","executionHint":"FLOW","implementationID":444132438,"x":-1648,"y":16,"width":32,"height":32,"inputConnectorIDs":[],"outputSuccessConnectorIDs":[],"outputFailureConnectorIDs":[],"outputUnconditionalConnectorIDs":[4866],"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":[]},"4813":{"id":4813,"inputCardinality":"ONE","outputCardinality":"MANY","connectorHint":"SUCCESS_FAIL","executionHint":"EXECUTE","implementationID":947479920,"x":-208,"y":16,"width":32,"height":32,"inputConnectorIDs":[4833],"outputSuccessConnectorIDs":[4835],"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":[]},"4814":{"id":4814,"inputCardinality":"ONE","outputCardinality":"MANY","connectorHint":"SUCCESS_FAIL","executionHint":"EXECUTE","implementationID":-313802845,"x":992,"y":16,"width":32,"height":32,"inputConnectorIDs":[4838],"outputSuccessConnectorIDs":[4846],"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":[]},"4815":{"id":4815,"inputCardinality":"ONE","outputCardinality":"MANY","connectorHint":"SUCCESS_FAIL","executionHint":"EXECUTE","implementationID":-1255297039,"x":-672,"y":16,"width":32,"height":32,"inputConnectorIDs":[4841],"outputSuccessConnectorIDs":[4837],"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 truncate(word, length = 12):\n  \"\"\"\n  `truncate()` will convert items to a string and ensure they are the length specified by user \n  Used within stringer function to ensure all columns are the same width \n  \"\"\"\n  if len(word.strip()) <= length:\n    return word\n  return \"{}...\".format( word[:(length-3)] )\n  \ndef stringer(lst, colwidth = 30):\n  \"\"\"\n  `stringer()` is used for printing Grid Variables in a more readable manner.\n  \n  It will handle formatting the grid variable to look more like how it is presented in the product \n  (as a table) \n  \"\"\"\n  msgs = [] \n  for row in lst:\n    msg = \" | \".join(\n    \t[\n        \"{:<{width}}\".format( truncate(str(x), length = colwidth), width = colwidth)\n        for x in row \n        ]\n    )\n    msgs.append(msg)\n    \n  return \"\\n\" + \"\\n\".join(msgs)\n\nsource_list = context.getGridVariable('source_list')\nprint(\"Grid variable 'source_list': {}\".format(stringer(source_list)))\n        \nsource_table_metadata = context.getGridVariable('source_table_metadata')\nprint(\"\\nGrid variable 'source_table_metadata': {}\".format(stringer(source_table_metadata)))\n\ninc_col_options = ['Updated', 'StartDate']\nprint(\"\\nVariable 'inc_col_options': {}\".format(inc_col_options))\n\nview_excluded_cols = ['EndDate']\nprint(\"\\nVariable 'view_excluded_cols': {}\".format(view_excluded_cols))\n\n# is_table() checks that a row of a grid variable belongs to the table we \n# are currently iterating over\nis_table = lambda x: x[0] == table_name\n\n# filter source_list to current table and extract list of columns\nsrc = [ x for x in source_list if is_table(x) ] \nrequested_columns = [ x[1] for x in src ]\n\n# extract key and inc columns\n# Note that the true/false values for IsKey are sometimes truncated to three letters, i.e. tru/fal\nkey_columns = [ x[0] for x in source_table_metadata if x[4].lower().startswith('t')]\n\n# loop through ordered list of possible incremental columns, using the first one found\nmetadata_columns = [ x[0] for x in source_table_metadata ]\ninc_columns = []\nfor i in inc_col_options:\n  if i in metadata_columns:\n    inc_columns = [i]\n    break\n\n# add missing columns, preserving order set by user\nall_columns = requested_columns\n\nfor x in key_columns:\n    if x not in all_columns:\n        all_columns.append(x)\n\nfor x in inc_columns:\n    if x not in all_columns:\n        all_columns.append(x)   \n        \n# for views, remove cols as appropriate\nif entity_type == 'view':    \n    for x in view_excluded_cols:\n        if x in all_columns:\n            all_columns.remove(x)\n\n# build new source column list with inc and key col flags\nnew_src = [] \n\nfor c in all_columns:\n    new_src.append(\n        [\n            table_name, \n            c,\n            '1' if c in inc_columns else '0',\n            '1' if c in key_columns else '0'\n  \t\t]\n  \t)\n    \ncontext.updateGridVariable('source_list', new_src)\nprint(\"\\nUpdated grid variable 'source_list': {}\".format(stringer(new_src)))\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":[]},"4816":{"id":4816,"inputCardinality":"ONE","outputCardinality":"MANY","connectorHint":"SUCCESS_FAIL","executionHint":"EXECUTE","implementationID":-1255297039,"x":32,"y":16,"width":32,"height":32,"inputConnectorIDs":[4835],"outputSuccessConnectorIDs":[4834],"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":[]},"4817":{"id":4817,"inputCardinality":"ONE","outputCardinality":"MANY","connectorHint":"SUCCESS_FAIL","executionHint":"EXECUTE","implementationID":1010676649,"x":-1488,"y":16,"width":32,"height":32,"inputConnectorIDs":[4866],"outputSuccessConnectorIDs":[4839],"outputFailureConnectorIDs":[],"outputUnconditionalConnectorIDs":[],"outputTrueConnectorIDs":[],"outputFalseConnectorIDs":[],"exportMappings":{"1":{"slot":1,"fromId":null,"fromName":"target_table_name","mapTo":"target_table_name","validationStatus":null,"validationMessage":null},"2":{"slot":2,"fromId":null,"fromName":"stage_table_name","mapTo":"stage_table_name","validationStatus":null,"validationMessage":null},"3":{"slot":3,"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":[]},"4818":{"id":4818,"inputCardinality":"ONE","outputCardinality":"MANY","connectorHint":"SUCCESS_FAIL","executionHint":"TRANSFORM","implementationID":-1180878087,"x":-1184,"y":16,"width":32,"height":32,"inputConnectorIDs":[4840],"outputSuccessConnectorIDs":[4847],"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":"Google Analytics - 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":"Google Analytics 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":"2"}}}},"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":[]},"4819":{"id":4819,"inputCardinality":"ONE","outputCardinality":"MANY","connectorHint":"SUCCESS_FAIL","executionHint":"EXECUTE","implementationID":-231597779,"x":-448,"y":16,"width":32,"height":32,"inputConnectorIDs":[4837],"outputSuccessConnectorIDs":[4833],"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":[]},"4820":{"id":4820,"inputCardinality":"ONE","outputCardinality":"MANY","connectorHint":"SUCCESS_FAIL","executionHint":"TRANSFORM","implementationID":-1180878087,"x":256,"y":16,"width":32,"height":32,"inputConnectorIDs":[4834],"outputSuccessConnectorIDs":[4836],"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":"Google Analytics - 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":"Google Analytics 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":"2"}}}},"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":[]},"4821":{"id":4821,"inputCardinality":"ONE","outputCardinality":"MANY","connectorHint":"SUCCESS_FAIL","executionHint":"EXECUTE","implementationID":332733136,"x":528,"y":16,"width":32,"height":32,"inputConnectorIDs":[4836],"outputSuccessConnectorIDs":[4842],"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}},"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":"Y"}}}},"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":[]},"4822":{"id":4822,"inputCardinality":"ONE","outputCardinality":"MANY","connectorHint":"SUCCESS_FAIL","executionHint":"TRANSFORM","implementationID":-1180878087,"x":816,"y":16,"width":32,"height":32,"inputConnectorIDs":[4849],"outputSuccessConnectorIDs":[4838],"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":"Google Analytics - 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":"Google Analytics 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":"2"}}}},"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":[]},"4823":{"id":4823,"inputCardinality":"ONE","outputCardinality":"MANY","connectorHint":"SUCCESS_FAIL","executionHint":"EXECUTE","implementationID":-1255297039,"x":-1344,"y":16,"width":32,"height":32,"inputConnectorIDs":[4839],"outputSuccessConnectorIDs":[4840],"outputFailureConnectorIDs":[],"outputUnconditionalConnectorIDs":[],"outputTrueConnectorIDs":[],"outputFalseConnectorIDs":[],"exportMappings":{},"parameters":{"1":{"slot":1,"name":"Name","elements":{"1":{"slot":1,"values":{"1":{"slot":1,"type":"STRING","value":"Override SQL statements"}}}},"visible":true,"status":{},"columnNames":[]},"2":{"slot":2,"name":"Script","elements":{"1":{"slot":1,"values":{"1":{"slot":1,"type":"STRING","value":"source_metadata_sql = \\\nf'''SELECT \n\ta.ColumnName\n    , a.DataTypeName\n    , a.Length\n    , a.NumericScale\n    , a.IsKey\n\t, a.IsNullable\n    , a.Ordinal\n    , b.TableType as EntityType\nFROM\n\tsys_tablecolumns a  \nLEFT JOIN sys_tables b on a.TableName = b.TableName\nWHERE \n    a.TableName = '{table_name}'\nORDER BY \n    a.Ordinal\n'''\n\ncontext.updateVariable('source_metadata_sql', source_metadata_sql)\nprint(f\"Updated variable 'source_metadata_sql' with: \\n{source_metadata_sql}\")\n\n# convention to store tables in snowflake as UPPER CASE to prevent issues with\n# the paramater: QUOTED_IDENTIFIERS_IGNORE_CASE\nif target_platform.lower() == 'snowflake':\n    \n    get_metadata_sql = \\\nf\"\"\"SELECT\n\t\"ColumnName\" AS \"COLUMN_NAME\",\n\t\"DataTypeName\" AS \"DATA_TYPE_NAME\", \n\t\"Length\" AS \"LENGTH\", \n\t\"NumericScale\" AS \"NUMERIC_SCALE\",\n\t\"IsKey\" AS \"IS_KEY\",\n\t\"IsNullable\" AS \"IS_NULLABLE\",\n\t\"Ordinal\" AS \"ORDINAL\",\n    \"EntityType\" AS \"ENTITY_TYPE\"\nFROM\n\t\"{stage_database}\".\"{stage_schema}\".\"{metadata_stage_table_name}\"\n\"\"\"\n  \nelif target_platform.lower() == 'bigquery':\n    \n    get_metadata_sql = \\\nf\"\"\"SELECT\n\tcolumnname as column_name,\n\tDataTypeName as data_type_name, \n\tLength as length, \n\tNumericScale as numeric_scale,\n\tiskey as is_key,\n\tisnullable as is_nullable,\n\tordinal as ordinal,\n    entitytype as entity_type\nFROM\n\t`{stage_project}`.`{stage_dataset}`.`{metadata_stage_table_name}`\n\"\"\"\n  \nelif target_platform.lower() == 'redshift':\n    \n    get_metadata_sql=\\\nf\"\"\"SELECT\n\tcolumnname as column_name,\n\tDataTypeName as data_type_name, \n\tLength as length, \n\tNumericScale as numeric_scale,\n\tiskey as is_key,\n\tisnullable as is_nullable,\n\tordinal as ordinal,\n    entitytype as entity_type\nFROM\n\t\"{stage_schema}\".\"{metadata_stage_table_name}\"\n\"\"\"\n\ncontext.updateVariable(\"get_metadata_sql\", get_metadata_sql)\nprint(f\"Updated variable 'get_metadata_sql' with: \\n{get_metadata_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":[]},"4824":{"id":4824,"inputCardinality":"ONE","outputCardinality":"MANY","connectorHint":"SUCCESS_FAIL","executionHint":"EXECUTE","implementationID":-1255297039,"x":-832,"y":16,"width":32,"height":32,"inputConnectorIDs":[4848],"outputSuccessConnectorIDs":[4841],"outputFailureConnectorIDs":[],"outputUnconditionalConnectorIDs":[],"outputTrueConnectorIDs":[],"outputFalseConnectorIDs":[],"exportMappings":{},"parameters":{"1":{"slot":1,"name":"Name","elements":{"1":{"slot":1,"values":{"1":{"slot":1,"type":"STRING","value":"Lookup entity type"}}}},"visible":true,"status":{},"columnNames":[]},"2":{"slot":2,"name":"Script","elements":{"1":{"slot":1,"values":{"1":{"slot":1,"type":"STRING","value":"# lookup entity type from source metadata\nsource_table_metadata = context.getGridVariable('source_table_metadata')\nentity_type = source_table_metadata[0][7].lower()\n\ncontext.updateVariable(\"entity_type\", entity_type)\nprint(\"\\nVariable 'entity_type': {}\".format(entity_type))\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":[]},"4825":{"id":4825,"inputCardinality":"ONE","outputCardinality":"MANY","connectorHint":"CONDITIONAL","executionHint":"FLOW","implementationID":-1357378929,"x":656,"y":16,"width":32,"height":32,"inputConnectorIDs":[4842],"outputSuccessConnectorIDs":[],"outputFailureConnectorIDs":[],"outputUnconditionalConnectorIDs":[],"outputTrueConnectorIDs":[4849],"outputFalseConnectorIDs":[4864],"exportMappings":{},"parameters":{"1":{"slot":1,"name":"Name","elements":{"1":{"slot":1,"values":{"1":{"slot":1,"type":"STRING","value":"Table?"}}}},"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":"entity_type"},"2":{"slot":2,"type":"STRING","value":"Is"},"3":{"slot":3,"type":"STRING","value":"Equal to"},"4":{"slot":4,"type":"STRING","value":"table"}}}},"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":[]},"4826":{"id":4826,"inputCardinality":"ONE","outputCardinality":"MANY","connectorHint":"SUCCESS_FAIL","executionHint":"EXECUTE","implementationID":-1255297039,"x":656,"y":288,"width":32,"height":32,"inputConnectorIDs":[4864],"outputSuccessConnectorIDs":[4844],"outputFailureConnectorIDs":[],"outputUnconditionalConnectorIDs":[],"outputTrueConnectorIDs":[],"outputFalseConnectorIDs":[],"exportMappings":{},"parameters":{"1":{"slot":1,"name":"Name","elements":{"1":{"slot":1,"values":{"1":{"slot":1,"type":"STRING","value":"Populate date_list"}}}},"visible":true,"status":{},"columnNames":[]},"2":{"slot":2,"name":"Script","elements":{"1":{"slot":1,"values":{"1":{"slot":1,"type":"STRING","value":"##############################################################################################\n# To avoid issues staging data from Google Analytics API, the start and end dates are split \n# into micro batches of 28, 14, 7, 6, 5, 4, 3, 2 or 1 day slices\n##############################################################################################\nfrom datetime import date, timedelta\nfrom datetime import datetime as dt\n\ndef truncate(word, length = 12):\n  \"\"\"\n  `truncate()` will convert items to a string and ensure they are the length specified by user \n  Used within stringer function to ensure all columns are the same width \n  \"\"\"\n  if len(word.strip()) <= length:\n    return word\n  return \"{}...\".format( word[:(length-3)] )\n  \ndef stringer(lst, colwidth = 30):\n  \"\"\"\n  `stringer()` is used for printing Grid Variables in a more readable manner.\n  \n  It will handle formatting the grid variable to look more like how it is presented in the product \n  (as a table) \n  \"\"\"\n  msgs = [] \n  for row in lst:\n    msg = \" | \".join(\n    \t[\n        \"{:<{width}}\".format( truncate(str(x), length = colwidth), width = colwidth)\n        for x in row \n        ]\n    )\n    msgs.append(msg)\n    \n  return \"\\n\" + \"\\n\".join(msgs)\n\nprint(f'Incremental column: {incremental_column}')\n\n# Set the micro batch interval.  Use shared job parameter or default to 28\nload_int = load_int.strip()\n\nif load_int.isnumeric():\n  _interval = int(load_int)\nelse:\n  _interval = 28\n\nprint(f'Load interval: {_interval} days')\n\n# Set job start date\ndefault_startdate = str(sub_RangeStartDate.strip())\nprint(f'Pipeline start date: {default_startdate}')\nprint(f'Current max date in target table: {max_value}')\n\nif not max_value: \n  max_value = default_startdate\n\ncontext.updateVariable('sub_RangeStartDate', max_value)\nprint(f'Job execution start date: {max_value}')\n\n# Set job end date to today\nsub_RangeEndDate = date.today().strftime('%Y-%m-%d')\ncontext.updateVariable('sub_RangeEndDate', sub_RangeEndDate)\nprint(f'Job execution end date: {sub_RangeEndDate}')\n\n# create job date objects\n_job_start_date = dt.strptime(max_value, '%Y-%m-%d')\n_job_end_date = dt.strptime(sub_RangeEndDate, '%Y-%m-%d')\n\n# delta is the number of days between job start and end dates\n_delta = (_job_end_date - _job_start_date).days\n\n# create an array to store the micro batch start and end date pairs\n_date_list = []\n_counter = 0\n\n# Loop over days to build micro batches\nwhile _counter <= _delta:\n  \n  # set batch start and end dates, ensuring there is no overlap\n  _batch_start_date = _job_start_date + timedelta( days = _counter)\n  _batch_end_date = _job_start_date + timedelta( days = _counter + _interval - 1)\n  \n  # ensure _batch_end_date is not after _job_end_date\n  if _batch_end_date > _job_end_date: \n    _batch_end_date = _job_end_date\n\n  # for first micro batch, use load_strategy exported from Decide Load Strategy\n  # this will be TRUNCATE INSERT if table missing or empty, or APPEND INSERT if data is present\n  # all other micro batches will always be APPEND INSERT\n  if _counter == 0:\n    _batch_load_strategy = load_strategy\n  else:\n    _batch_load_strategy = 'APPEND INSERT'\n    \n  if _batch_load_strategy == 'APPEND INSERT':\n    _batch_load_strategy = _batch_load_strategy + '-GOOGLE ANALYTICS'\n    \n  # append to the datelist array\n  _date_list.append([\n    _batch_start_date.strftime('%Y-%m-%d'),\n    _batch_end_date.strftime('%Y-%m-%d'),\n    _batch_load_strategy\n  ])\n  \n  # increment the counter \n  _counter += _interval\n\ncontext.updateGridVariable('date_list', _date_list)\nprint('Date list: {}'.format(stringer(_date_list)))\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":[]},"4827":{"id":4827,"inputCardinality":"ONE","outputCardinality":"MANY","connectorHint":"SUCCESS_FAIL","executionHint":"ITERATE","implementationID":-424773870,"x":816,"y":288,"width":32,"height":16,"inputConnectorIDs":[4844],"outputSuccessConnectorIDs":[4843],"outputFailureConnectorIDs":[],"outputUnconditionalConnectorIDs":[],"outputTrueConnectorIDs":[],"outputFalseConnectorIDs":[],"exportMappings":{},"parameters":{"1":{"slot":1,"name":"Name","elements":{"1":{"slot":1,"values":{"1":{"slot":1,"type":"STRING","value":"Iterate Micro Batching (View)"}}}},"visible":true,"status":{},"columnNames":[]},"3":{"slot":3,"name":"Grid Variable","elements":{"1":{"slot":1,"values":{"1":{"slot":1,"type":"STRING","value":"date_list"}}}},"visible":true,"status":{},"columnNames":[]},"4":{"slot":4,"name":"Grid Variable Mapping","elements":{"1":{"slot":1,"values":{"1":{"slot":1,"type":"STRING","value":"batch_start_date"},"2":{"slot":2,"type":"STRING","value":"batch_start_date"}}},"2":{"slot":2,"values":{"1":{"slot":1,"type":"STRING","value":"batch_end_date"},"2":{"slot":2,"type":"STRING","value":"batch_end_date"}}},"3":{"slot":3,"values":{"1":{"slot":1,"type":"STRING","value":"load_strategy"},"2":{"slot":2,"type":"STRING","value":"load_strategy"}}}},"visible":true,"status":{},"columnNames":[]},"5":{"slot":5,"name":"Break on Failure","elements":{"1":{"slot":1,"values":{"1":{"slot":1,"type":"STRING","value":"No"}}}},"visible":true,"status":{},"columnNames":[]},"6":{"slot":6,"name":"Concurrency","elements":{"1":{"slot":1,"values":{"1":{"slot":1,"type":"STRING","value":"Sequential"}}}},"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":[4868],"inputIterationConnectorIDs":[]},"4828":{"id":4828,"inputCardinality":"ONE","outputCardinality":"MANY","connectorHint":"SUCCESS_FAIL","executionHint":"TRANSFORM","implementationID":-1180878087,"x":816,"y":320,"width":32,"height":32,"inputConnectorIDs":[],"outputSuccessConnectorIDs":[],"outputFailureConnectorIDs":[],"outputUnconditionalConnectorIDs":[],"outputTrueConnectorIDs":[],"outputFalseConnectorIDs":[],"exportMappings":{"1":{"slot":1,"fromId":null,"fromName":"logged_rows_inserted","mapTo":"logged_rows_inserted","validationStatus":null,"validationMessage":null}},"parameters":{"1":{"slot":1,"name":"Name","elements":{"1":{"slot":1,"values":{"1":{"slot":1,"type":"STRING","value":"Stage and Load View"}}}},"visible":true,"status":{},"columnNames":[]},"2":{"slot":2,"name":"Job Name","elements":{"1":{"slot":1,"values":{"1":{"slot":1,"type":"STRING","value":"Google Analytics - 3-1 - Stage and Load View"}}}},"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":"oauth"},"2":{"slot":2,"type":"STRING","value":"${oauth}"}}},"2":{"slot":2,"values":{"1":{"slot":1,"type":"STRING","value":"dist_style"},"2":{"slot":2,"type":"STRING","value":"${dist_style}"}}},"3":{"slot":3,"values":{"1":{"slot":1,"type":"STRING","value":"table_name"},"2":{"slot":2,"type":"STRING","value":"${table_name}"}}},"4":{"slot":4,"values":{"1":{"slot":1,"type":"STRING","value":"stage_prefix"},"2":{"slot":2,"type":"STRING","value":"${stage_prefix}"}}},"5":{"slot":5,"values":{"1":{"slot":1,"type":"STRING","value":"stage_schema"},"2":{"slot":2,"type":"STRING","value":"${stage_schema}"}}},"6":{"slot":6,"values":{"1":{"slot":1,"type":"STRING","value":"target_prefix"},"2":{"slot":2,"type":"STRING","value":"${target_prefix}"}}},"7":{"slot":7,"values":{"1":{"slot":1,"type":"STRING","value":"target_schema"},"2":{"slot":2,"type":"STRING","value":"${target_schema}"}}},"8":{"slot":8,"values":{"1":{"slot":1,"type":"STRING","value":"bucket_name"},"2":{"slot":2,"type":"STRING","value":"${bucket_name}"}}},"9":{"slot":9,"values":{"1":{"slot":1,"type":"STRING","value":"stage_table_name"},"2":{"slot":2,"type":"STRING","value":"${stage_table_name}"}}},"10":{"slot":10,"values":{"1":{"slot":1,"type":"STRING","value":"target_table_name"},"2":{"slot":2,"type":"STRING","value":"${target_table_name}"}}},"11":{"slot":11,"values":{"1":{"slot":1,"type":"STRING","value":"batch_end_date"},"2":{"slot":2,"type":"STRING","value":"${batch_end_date}"}}},"12":{"slot":12,"values":{"1":{"slot":1,"type":"STRING","value":"batch_start_date"},"2":{"slot":2,"type":"STRING","value":"${batch_start_date}"}}},"13":{"slot":13,"values":{"1":{"slot":1,"type":"STRING","value":"source_column_list"},"2":{"slot":2,"type":"STRING","value":"${source_column_list}"}}},"14":{"slot":14,"values":{"1":{"slot":1,"type":"STRING","value":"target_platform"},"2":{"slot":2,"type":"STRING","value":"${target_platform}"}}},"15":{"slot":15,"values":{"1":{"slot":1,"type":"STRING","value":"target_database"},"2":{"slot":2,"type":"STRING","value":"${target_database}"}}},"16":{"slot":16,"values":{"1":{"slot":1,"type":"STRING","value":"target_warehouse"},"2":{"slot":2,"type":"STRING","value":"${target_warehouse}"}}},"17":{"slot":17,"values":{"1":{"slot":1,"type":"STRING","value":"stage_database"},"2":{"slot":2,"type":"STRING","value":"${stage_database}"}}},"18":{"slot":18,"values":{"1":{"slot":1,"type":"STRING","value":"stage_warehouse"},"2":{"slot":2,"type":"STRING","value":"${stage_warehouse}"}}},"19":{"slot":19,"values":{"1":{"slot":1,"type":"STRING","value":"target_dataset"},"2":{"slot":2,"type":"STRING","value":"${target_dataset}"}}},"20":{"slot":20,"values":{"1":{"slot":1,"type":"STRING","value":"target_project"},"2":{"slot":2,"type":"STRING","value":"${target_project}"}}},"21":{"slot":21,"values":{"1":{"slot":1,"type":"STRING","value":"stage_dataset"},"2":{"slot":2,"type":"STRING","value":"${stage_dataset}"}}},"22":{"slot":22,"values":{"1":{"slot":1,"type":"STRING","value":"stage_project"},"2":{"slot":2,"type":"STRING","value":"${stage_project}"}}},"23":{"slot":23,"values":{"1":{"slot":1,"type":"STRING","value":"cloud_storage_area"},"2":{"slot":2,"type":"STRING","value":"${cloud_storage_area}"}}},"24":{"slot":24,"values":{"1":{"slot":1,"type":"STRING","value":"storage_account"},"2":{"slot":2,"type":"STRING","value":"${storage_account}"}}},"25":{"slot":25,"values":{"1":{"slot":1,"type":"STRING","value":"blob_container"},"2":{"slot":2,"type":"STRING","value":"${blob_container}"}}},"26":{"slot":26,"values":{"1":{"slot":1,"type":"STRING","value":"logged_rows_inserted"},"2":{"slot":2,"type":"STRING","value":"${logged_rows_inserted}"}}},"27":{"slot":27,"values":{"1":{"slot":1,"type":"STRING","value":"encryption_method"},"2":{"slot":2,"type":"STRING","value":"${encryption_method}"}}},"28":{"slot":28,"values":{"1":{"slot":1,"type":"STRING","value":"kms_key_id"},"2":{"slot":2,"type":"STRING","value":"${kms_key_id}"}}},"29":{"slot":29,"values":{"1":{"slot":1,"type":"STRING","value":"load_strategy"},"2":{"slot":2,"type":"STRING","value":"${load_strategy}"}}},"30":{"slot":30,"values":{"1":{"slot":1,"type":"STRING","value":"staging_type"},"2":{"slot":2,"type":"STRING","value":"${staging_type}"}}},"31":{"slot":31,"values":{"1":{"slot":1,"type":"STRING","value":"log_metrics"},"2":{"slot":2,"type":"STRING","value":"${log_metrics}"}}},"32":{"slot":32,"values":{"1":{"slot":1,"type":"STRING","value":"incremental_column"},"2":{"slot":2,"type":"STRING","value":"${incremental_column}"}}}},"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":"stage_table_metadata"}}},"2":{"slot":2,"values":{"1":{"slot":1,"type":"GRID","value":"column_name"},"2":{"slot":2,"type":"GRID","value":"type"}}}},"values":{"1":{"slot":1,"type":"STRING","value":"stage_table_metadata"},"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":"Google Analytics 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":"2"}}}},"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":[4868]},"4829":{"id":4829,"inputCardinality":"ONE","outputCardinality":"MANY","connectorHint":"CONDITIONAL","executionHint":"FLOW","implementationID":-1357378929,"x":992,"y":288,"width":32,"height":32,"inputConnectorIDs":[4843],"outputSuccessConnectorIDs":[],"outputFailureConnectorIDs":[],"outputUnconditionalConnectorIDs":[],"outputTrueConnectorIDs":[4850],"outputFalseConnectorIDs":[4865],"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":[]},"4830":{"id":4830,"inputCardinality":"ONE","outputCardinality":"MANY","connectorHint":"SUCCESS_FAIL","executionHint":"EXECUTE","implementationID":-2112089365,"x":1152,"y":288,"width":32,"height":32,"inputConnectorIDs":[4850],"outputSuccessConnectorIDs":[4845],"outputFailureConnectorIDs":[],"outputUnconditionalConnectorIDs":[],"outputTrueConnectorIDs":[],"outputFalseConnectorIDs":[],"exportMappings":{},"parameters":{"1":{"slot":1,"name":"Name","elements":{"1":{"slot":1,"values":{"1":{"slot":1,"type":"STRING","value":"Log Row Counts"}}}},"visible":true,"status":{},"columnNames":[]},"2":{"slot":2,"name":"Metrics","elements":{"1":{"slot":1,"values":{"1":{"slot":1,"type":"STRING","value":"inserted"},"2":{"slot":2,"type":"STRING","value":"Numeric"},"3":{"slot":3,"type":"STRING","value":"${logged_rows_inserted}"}}},"2":{"slot":2,"values":{"1":{"slot":1,"type":"STRING","value":"updated"},"2":{"slot":2,"type":"STRING","value":"Numeric"},"3":{"slot":3,"type":"STRING","value":"0"}}},"3":{"slot":3,"values":{"1":{"slot":1,"type":"STRING","value":"deleted"},"2":{"slot":2,"type":"STRING","value":"Numeric"},"3":{"slot":3,"type":"STRING","value":"0"}}}},"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":[]}},"validationStatus":{},"status":{},"expectedFailure":null,"activationStatus":"ENABLED","outputIterationConnectorIDs":[],"inputIterationConnectorIDs":[]},"4831":{"id":4831,"inputCardinality":"MANY","outputCardinality":"MANY","connectorHint":"UNCONDITIONAL","executionHint":"FLOW","implementationID":-1343684451,"x":1152,"y":16,"width":32,"height":32,"inputConnectorIDs":[4865,4845,4846],"outputSuccessConnectorIDs":[],"outputFailureConnectorIDs":[],"outputUnconditionalConnectorIDs":[4867],"outputTrueConnectorIDs":[],"outputFalseConnectorIDs":[],"exportMappings":{},"parameters":{"1":{"slot":1,"name":"Name","elements":{"1":{"slot":1,"values":{"1":{"slot":1,"type":"STRING","value":"Join paths"}}}},"visible":true,"status":{},"columnNames":[]}},"validationStatus":{},"status":{},"expectedFailure":null,"activationStatus":"ENABLED","outputIterationConnectorIDs":[],"inputIterationConnectorIDs":[]},"4832":{"id":4832,"inputCardinality":"ONE","outputCardinality":"MANY","connectorHint":"SUCCESS_FAIL","executionHint":"TRANSFORM","implementationID":-1180878087,"x":-1008,"y":16,"width":32,"height":32,"inputConnectorIDs":[4847],"outputSuccessConnectorIDs":[4848],"outputFailureConnectorIDs":[],"outputUnconditionalConnectorIDs":[],"outputTrueConnectorIDs":[],"outputFalseConnectorIDs":[],"exportMappings":{},"gridExportMappings":{"1":{"slot":1,"fromGrid":"source_table_metadata","toGrid":"source_table_metadata","columnMapToFrom":{"entity_type":"entity_type","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":"Job Name","elements":{"1":{"slot":1,"values":{"1":{"slot":1,"type":"STRING","value":"Incremental lib - Check source metadata"}}}},"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":"metadata_stage_table_name"},"2":{"slot":2,"type":"STRING","value":"${metadata_stage_table_name}"}}},"2":{"slot":2,"values":{"1":{"slot":1,"type":"STRING","value":"target_table_name"},"2":{"slot":2,"type":"STRING","value":"${target_table_name}"}}},"3":{"slot":3,"values":{"1":{"slot":1,"type":"STRING","value":"get_metadata_sql"},"2":{"slot":2,"type":"STRING","value":"${get_metadata_sql}"}}},"4":{"slot":4,"values":{"1":{"slot":1,"type":"STRING","value":"stage_database"},"2":{"slot":2,"type":"STRING","value":"${stage_database}"}}},"5":{"slot":5,"values":{"1":{"slot":1,"type":"STRING","value":"stage_schema"},"2":{"slot":2,"type":"STRING","value":"${stage_schema}"}}},"6":{"slot":6,"values":{"1":{"slot":1,"type":"STRING","value":"stage_project"},"2":{"slot":2,"type":"STRING","value":"${stage_project}"}}},"7":{"slot":7,"values":{"1":{"slot":1,"type":"STRING","value":"stage_dataset"},"2":{"slot":2,"type":"STRING","value":"${stage_dataset}"}}}},"visible":true,"status":{},"columnNames":[]},"5":{"slot":5,"name":"Set Grid Variables","elements":{},"visible":true,"status":{},"columnNames":[]},"6":{"slot":6,"name":"Melt Name","elements":{"1":{"slot":1,"values":{"1":{"slot":1,"type":"STRING","value":"Google Analytics 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":"2"}}}},"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":[]}},"successConnectors":{"4833":{"id":4833,"sourceID":4819,"targetID":4813},"4834":{"id":4834,"sourceID":4816,"targetID":4820},"4835":{"id":4835,"sourceID":4813,"targetID":4816},"4836":{"id":4836,"sourceID":4820,"targetID":4821},"4837":{"id":4837,"sourceID":4815,"targetID":4819},"4838":{"id":4838,"sourceID":4822,"targetID":4814},"4839":{"id":4839,"sourceID":4817,"targetID":4823},"4840":{"id":4840,"sourceID":4823,"targetID":4818},"4841":{"id":4841,"sourceID":4824,"targetID":4815},"4842":{"id":4842,"sourceID":4821,"targetID":4825},"4843":{"id":4843,"sourceID":4827,"targetID":4829},"4844":{"id":4844,"sourceID":4826,"targetID":4827},"4845":{"id":4845,"sourceID":4830,"targetID":4831},"4846":{"id":4846,"sourceID":4814,"targetID":4831},"4847":{"id":4847,"sourceID":4818,"targetID":4832},"4848":{"id":4848,"sourceID":4832,"targetID":4824}},"failureConnectors":{},"unconditionalConnectors":{"4866":{"id":4866,"sourceID":4812,"targetID":4817},"4867":{"id":4867,"sourceID":4831,"targetID":4811}},"trueConnectors":{"4849":{"id":4849,"sourceID":4825,"targetID":4822},"4850":{"id":4850,"sourceID":4829,"targetID":4830}},"falseConnectors":{"4864":{"id":4864,"sourceID":4825,"targetID":4826},"4865":{"id":4865,"sourceID":4829,"targetID":4831}},"iterationConnectors":{"4868":{"id":4868,"sourceID":4827,"targetID":4828}},"noteConnectors":{},"canUndo":true,"undoCommand":"Set Parameter","undoCreated":1619081773806,"canRedo":false,"redoCommand":"","redoCreated":0,"notes":{"4805":{"id":4805,"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"},"4806":{"id":4806,"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"},"4807":{"id":4807,"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"},"4808":{"id":4808,"x":-1547,"y":-175,"width":963,"height":249,"text":"** Check source metadata **\n\nQuery source metadata to find key and incremental columns.  SQL statements from Build Strings are not used, instead we build custom SQL which includes the Entity Type column (Table or View)\n+ Add any missing key columns into __source_list__\n+ If the table contains 'Updated', add it to __source_list__ (if missing) and set as the incremental column\n\nFor views\n+ Add 'StartDate' to __source_list__ (if missing)\n+ Remove 'EndDate' from __source_list__ (if present).  We don't want this column being propagated as it will impact the final load process where duplicates are removed","colour":"e6e63c"},"4809":{"id":4809,"x":439,"y":-137,"width":834,"height":212,"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\n- __max_value__ is exported from Decide Load Strategy to be used for loading data from views","colour":"e6e63c"},"4810":{"id":4810,"x":565,"y":102,"width":708,"height":262,"text":"**Iterate over __date_list__ to load data from views**\n\n__sub_ViewStartDate__ will be the start date specified by the user for the first run, else it will be the __max_value__ from the target table\n__sub_ViewEndDate__ will be today's date\n\nThe API calls will be batched into periods of either a 27, 13, 6,5,4,3,2,1 or 0 days between the __sub_ViewStartDate__ and __sub_ViewEndDate__  so that the next step can effectively micro batch the stage and load process\n\n__date_list__ will contain the start and end date for each micro batch, along with the load strategy for the batch\n","colour":"e6e63c"}},"variables":{"oauth":{"definition":{"name":"oauth","type":"TEXT","scope":"BRANCH","description":"","visibility":"PUBLIC"},"value":""},"load_int":{"definition":{"name":"load_int","type":"TEXT","scope":"TASKBATCH","description":"","visibility":"PUBLIC"},"value":"28"},"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":""},"entity_type":{"definition":{"name":"entity_type","type":"TEXT","scope":"TASKBATCH","description":"","visibility":"PRIVATE"},"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":""},"batch_end_date":{"definition":{"name":"batch_end_date","type":"TEXT","scope":"TASKBATCH","description":"","visibility":"PRIVATE"},"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":""},"batch_start_date":{"definition":{"name":"batch_start_date","type":"TEXT","scope":"TASKBATCH","description":"","visibility":"PRIVATE"},"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":""},"sub_RangeEndDate":{"definition":{"name":"sub_RangeEndDate","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":""},"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":""},"sub_RangeStartDate":{"definition":{"name":"sub_RangeStartDate","type":"TEXT","scope":"TASKBATCH","description":"","visibility":"PUBLIC"},"value":"2005-01-01"},"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"},"logged_rows_inserted":{"definition":{"name":"logged_rows_inserted","type":"DECIMAL","scope":"TASKBATCH","description":"","visibility":"PRIVATE"},"value":"0"},"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":{"date_list":{"definition":{"name":"date_list","scope":"TASKBATCH","definitions":[{"name":"batch_start_date","type":"TEXT"},{"name":"batch_end_date","type":"TEXT"},{"name":"load_strategy","type":"TEXT"}],"description":"","visibility":"PRIVATE"},"values":[]},"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"},{"name":"entity_type","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":[]}}},"Google Analytics - 2 - Stage Object":{"id":-2,"revision":1,"created":1619081773995,"timestamp":1619081773995,"components":{"4871":{"id":4871,"inputCardinality":"ONE","outputCardinality":"ZERO","connectorHint":"UNCONDITIONAL","executionHint":"FLOW","implementationID":-1946388514,"x":-784,"y":16,"width":32,"height":32,"inputConnectorIDs":[4876],"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":[]},"4872":{"id":4872,"inputCardinality":"ZERO","outputCardinality":"MANY","connectorHint":"UNCONDITIONAL","executionHint":"FLOW","implementationID":444132438,"x":-1344,"y":16,"width":32,"height":32,"inputConnectorIDs":[],"outputSuccessConnectorIDs":[],"outputFailureConnectorIDs":[],"outputUnconditionalConnectorIDs":[4878],"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":[]},"4873":{"id":4873,"inputCardinality":"ONE","outputCardinality":"MANY","connectorHint":"SUCCESS_FAIL","executionHint":"TRANSFORM","implementationID":-1180878087,"x":-1152,"y":16,"width":32,"height":32,"inputConnectorIDs":[4878],"outputSuccessConnectorIDs":[],"outputFailureConnectorIDs":[4879],"outputUnconditionalConnectorIDs":[4877],"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":"Google Analytics - 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}"}}},"25":{"slot":25,"values":{"1":{"slot":1,"type":"STRING","value":"load_int"},"2":{"slot":2,"type":"STRING","value":"${load_int}"}}},"26":{"slot":26,"values":{"1":{"slot":1,"type":"STRING","value":"sub_RangeStartDate"},"2":{"slot":2,"type":"STRING","value":"${sub_RangeStartDate}"}}}},"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":"Google Analytics 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":"2"}}}},"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":[]},"4874":{"id":4874,"inputCardinality":"ONE","outputCardinality":"MANY","connectorHint":"SUCCESS_FAIL","executionHint":"EXECUTE","implementationID":-1291941950,"x":-960,"y":16,"width":32,"height":32,"inputConnectorIDs":[4877],"outputSuccessConnectorIDs":[4876],"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":"Project","elements":{"1":{"slot":1,"values":{"1":{"slot":1,"type":"STRING","value":"${stage_project}"}}}},"visible":true,"status":{},"columnNames":[]},"3":{"slot":3,"name":"Dataset","elements":{"1":{"slot":1,"values":{"1":{"slot":1,"type":"STRING","value":"${stage_dataset}"}}}},"visible":true,"status":{},"columnNames":[]},"4":{"slot":4,"name":"Target Table","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":[]},"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":[]},"4875":{"id":4875,"inputCardinality":"ONE","outputCardinality":"ZERO","connectorHint":"UNCONDITIONAL","executionHint":"FLOW","implementationID":515156205,"x":-1152,"y":144,"width":32,"height":32,"inputConnectorIDs":[4879],"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":{"4876":{"id":4876,"sourceID":4874,"targetID":4871}},"failureConnectors":{"4879":{"id":4879,"sourceID":4873,"targetID":4875}},"unconditionalConnectors":{"4877":{"id":4877,"sourceID":4873,"targetID":4874},"4878":{"id":4878,"sourceID":4872,"targetID":4873}},"trueConnectors":{},"falseConnectors":{},"iterationConnectors":{},"noteConnectors":{},"canUndo":false,"undoCommand":"","undoCreated":0,"canRedo":false,"redoCommand":"","redoCreated":0,"notes":{"4870":{"id":4870,"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":""},"load_int":{"definition":{"name":"load_int","type":"DECIMAL","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":null},"stage_project":{"definition":{"name":"stage_project","type":"TEXT","scope":"TASKBATCH","description":"   ","visibility":"PUBLIC"},"value":null},"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":""},"sub_RangeStartDate":{"definition":{"name":"sub_RangeStartDate","type":"TEXT","scope":"TASKBATCH","description":"","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":"Built","environment":"bigquery"}