{"action":"created","comment":{"url":"https://api.github.com/repos/Clever/s3-to-redshift/pulls/comments/60997721","id":60997721,"diff_hunk":"@@ -26,6 +27,7 @@ var (\n \tdataDate        = flag.String(\"date\", \"\", \"data date we should process, must be full RFC3339\")\n \tconfigFile      = flag.String(\"config\", \"\", \"schema & table config to use in YAML format\")\n \tgzip            = flag.Bool(\"gzip\", true, \"whether target files are gzipped, defaults to true\")\n+\ttimeGranularity = flag.String(\"granularity\", \"day\", \"how often we expect to append new data\")","path":"s3_to_redshift.go","position":4,"original_position":12,"commit_id":"9bcaf5cd834dc3f0006450da1476078a052f422e","original_commit_id":"614d2cad4c2d79fab8a4626fa76209aecad3a159","user":{"login":"evaninja","id":14317599,"avatar_url":"https://avatars.githubusercontent.com/u/14317599?v=3","gravatar_id":"","url":"https://api.github.com/users/evaninja","html_url":"https://github.com/evaninja","followers_url":"https://api.github.com/users/evaninja/followers","following_url":"https://api.github.com/users/evaninja/following{/other_user}","gists_url":"https://api.github.com/users/evaninja/gists{/gist_id}","starred_url":"https://api.github.com/users/evaninja/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/evaninja/subscriptions","organizations_url":"https://api.github.com/users/evaninja/orgs","repos_url":"https://api.github.com/users/evaninja/repos","events_url":"https://api.github.com/users/evaninja/events{/privacy}","received_events_url":"https://api.github.com/users/evaninja/received_events","type":"User","site_admin":false},"body":"How strongly do you feel about this? I have a preference to keep the defaults behavior, given that no use case actually exists right now for non-day granularity (and I generally prefer the principle of engineering things as the use case arises). However, if you feel strongly that we should enforce day granularity, we can absolutely add it in.","created_at":"2016-04-25T22:02:55Z","updated_at":"2016-04-25T22:02:55Z","html_url":"https://github.com/Clever/s3-to-redshift/pull/62#discussion_r60997721","pull_request_url":"https://api.github.com/repos/Clever/s3-to-redshift/pulls/62","_links":{"self":{"href":"https://api.github.com/repos/Clever/s3-to-redshift/pulls/comments/60997721"},"html":{"href":"https://github.com/Clever/s3-to-redshift/pull/62#discussion_r60997721"},"pull_request":{"href":"https://api.github.com/repos/Clever/s3-to-redshift/pulls/62"}}},"pull_request":{"url":"https://api.github.com/repos/Clever/s3-to-redshift/pulls/62","id":67586669,"html_url":"https://github.com/Clever/s3-to-redshift/pull/62","diff_url":"https://github.com/Clever/s3-to-redshift/pull/62.diff","patch_url":"https://github.com/Clever/s3-to-redshift/pull/62.patch","issue_url":"https://api.github.com/repos/Clever/s3-to-redshift/issues/62","number":62,"state":"open","locked":false,"title":"IP-737: Replace matching data within time range","user":{"login":"evaninja","id":14317599,"avatar_url":"https://avatars.githubusercontent.com/u/14317599?v=3","gravatar_id":"","url":"https://api.github.com/users/evaninja","html_url":"https://github.com/evaninja","followers_url":"https://api.github.com/users/evaninja/followers","following_url":"https://api.github.com/users/evaninja/following{/other_user}","gists_url":"https://api.github.com/users/evaninja/gists{/gist_id}","starred_url":"https://api.github.com/users/evaninja/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/evaninja/subscriptions","organizations_url":"https://api.github.com/users/evaninja/orgs","repos_url":"https://api.github.com/users/evaninja/repos","events_url":"https://api.github.com/users/evaninja/events{/privacy}","received_events_url":"https://api.github.com/users/evaninja/received_events","type":"User","site_admin":false},"body":"**JIRA**: https://clever.atlassian.net/browse/IP-737\r\n\r\n**Overview**: We are updating key Pulse charts to refresh hourly (Instant Pulse!) and so as a result we'll need to modify some logic in s3-to-redshift to allow multiple syncs to run per day (and to refresh new data within a certain time range, specified by `granularity`). These changes:\r\n\r\n1. Introduce a `granularity` flag, which determines how often we expect to store copies of data in each table. Currently, this is a daily interval for all tables involved.\r\n2. Modify the `--force` conditions to allow updates to the most recent sync for new, incoming data (i.e. in the current day, or hour, depending on `granularity`).\r\n3. Introduce an extra step in the SQL transaction (`TruncateInTimeRange`) that, for a particular sync, deletes data before the subsequent COPY refreshes that data.\r\n\r\n**Testing**: Wrote unit tests for Redshift SQL functions. Performed manual testing against prod for the following cases:\r\n\r\n- Verified that `--force` is no longer required for updates to the most recent data!\r\n- New sync overwrites data, but does not append it. In essence, an idempotent operation! I tested this by deleting a subset of rows for a particular date, and ensuring that they were added back after running the s3-to-redshift sync (and that no duplicate rows were appended).\r\n- Verified that `--force` is still required for updates outside of the most recent time granularity time range.\r\n- Tested both `--granularity=day` and `--granularity=hour`\r\n\r\n**Roll Out**:\r\n- Make sure to first modify `business-metrics` and `mongo-to-s3` to set `--granularity=day`. This is technically optional because this flag defaults to `day`, but would be nice for clarity purposes. At the very least we should document in those clients how often we expect the granularity to be.\r\n- Optional: standardize the s3 filenames.. so that we truncate exactly to the desired granularity in the filename (Benji's suggestion).\r\n- Reschedule `mongo-to-s3` and `business-metrics` cron jobs","created_at":"2016-04-22T23:36:16Z","updated_at":"2016-04-25T22:02:55Z","closed_at":null,"merged_at":null,"merge_commit_sha":"3a58320c051269b82b098956b1f3bb0f4b51139b","assignee":{"login":"cgclever","id":13650281,"avatar_url":"https://avatars.githubusercontent.com/u/13650281?v=3","gravatar_id":"","url":"https://api.github.com/users/cgclever","html_url":"https://github.com/cgclever","followers_url":"https://api.github.com/users/cgclever/followers","following_url":"https://api.github.com/users/cgclever/following{/other_user}","gists_url":"https://api.github.com/users/cgclever/gists{/gist_id}","starred_url":"https://api.github.com/users/cgclever/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/cgclever/subscriptions","organizations_url":"https://api.github.com/users/cgclever/orgs","repos_url":"https://api.github.com/users/cgclever/repos","events_url":"https://api.github.com/users/cgclever/events{/privacy}","received_events_url":"https://api.github.com/users/cgclever/received_events","type":"User","site_admin":false},"milestone":null,"commits_url":"https://api.github.com/repos/Clever/s3-to-redshift/pulls/62/commits","review_comments_url":"https://api.github.com/repos/Clever/s3-to-redshift/pulls/62/comments","review_comment_url":"https://api.github.com/repos/Clever/s3-to-redshift/pulls/comments{/number}","comments_url":"https://api.github.com/repos/Clever/s3-to-redshift/issues/62/comments","statuses_url":"https://api.github.com/repos/Clever/s3-to-redshift/statuses/9bcaf5cd834dc3f0006450da1476078a052f422e","head":{"label":"Clever:IP-737-replace-redshift-matching-data","ref":"IP-737-replace-redshift-matching-data","sha":"9bcaf5cd834dc3f0006450da1476078a052f422e","user":{"login":"Clever","id":1885509,"avatar_url":"https://avatars.githubusercontent.com/u/1885509?v=3","gravatar_id":"","url":"https://api.github.com/users/Clever","html_url":"https://github.com/Clever","followers_url":"https://api.github.com/users/Clever/followers","following_url":"https://api.github.com/users/Clever/following{/other_user}","gists_url":"https://api.github.com/users/Clever/gists{/gist_id}","starred_url":"https://api.github.com/users/Clever/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/Clever/subscriptions","organizations_url":"https://api.github.com/users/Clever/orgs","repos_url":"https://api.github.com/users/Clever/repos","events_url":"https://api.github.com/users/Clever/events{/privacy}","received_events_url":"https://api.github.com/users/Clever/received_events","type":"Organization","site_admin":false},"repo":{"id":29368865,"name":"s3-to-redshift","full_name":"Clever/s3-to-redshift","owner":{"login":"Clever","id":1885509,"avatar_url":"https://avatars.githubusercontent.com/u/1885509?v=3","gravatar_id":"","url":"https://api.github.com/users/Clever","html_url":"https://github.com/Clever","followers_url":"https://api.github.com/users/Clever/followers","following_url":"https://api.github.com/users/Clever/following{/other_user}","gists_url":"https://api.github.com/users/Clever/gists{/gist_id}","starred_url":"https://api.github.com/users/Clever/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/Clever/subscriptions","organizations_url":"https://api.github.com/users/Clever/orgs","repos_url":"https://api.github.com/users/Clever/repos","events_url":"https://api.github.com/users/Clever/events{/privacy}","received_events_url":"https://api.github.com/users/Clever/received_events","type":"Organization","site_admin":false},"private":false,"html_url":"https://github.com/Clever/s3-to-redshift","description":"Library and worker to handle transfer of data in s3 into redshift. Includes table creation and manipulation, as well as time-based insertion.","fork":false,"url":"https://api.github.com/repos/Clever/s3-to-redshift","forks_url":"https://api.github.com/repos/Clever/s3-to-redshift/forks","keys_url":"https://api.github.com/repos/Clever/s3-to-redshift/keys{/key_id}","collaborators_url":"https://api.github.com/repos/Clever/s3-to-redshift/collaborators{/collaborator}","teams_url":"https://api.github.com/repos/Clever/s3-to-redshift/teams","hooks_url":"https://api.github.com/repos/Clever/s3-to-redshift/hooks","issue_events_url":"https://api.github.com/repos/Clever/s3-to-redshift/issues/events{/number}","events_url":"https://api.github.com/repos/Clever/s3-to-redshift/events","assignees_url":"https://api.github.com/repos/Clever/s3-to-redshift/assignees{/user}","branches_url":"https://api.github.com/repos/Clever/s3-to-redshift/branches{/branch}","tags_url":"https://api.github.com/repos/Clever/s3-to-redshift/tags","blobs_url":"https://api.github.com/repos/Clever/s3-to-redshift/git/blobs{/sha}","git_tags_url":"https://api.github.com/repos/Clever/s3-to-redshift/git/tags{/sha}","git_refs_url":"https://api.github.com/repos/Clever/s3-to-redshift/git/refs{/sha}","trees_url":"https://api.github.com/repos/Clever/s3-to-redshift/git/trees{/sha}","statuses_url":"https://api.github.com/repos/Clever/s3-to-redshift/statuses/{sha}","languages_url":"https://api.github.com/repos/Clever/s3-to-redshift/languages","stargazers_url":"https://api.github.com/repos/Clever/s3-to-redshift/stargazers","contributors_url":"https://api.github.com/repos/Clever/s3-to-redshift/contributors","subscribers_url":"https://api.github.com/repos/Clever/s3-to-redshift/subscribers","subscription_url":"https://api.github.com/repos/Clever/s3-to-redshift/subscription","commits_url":"https://api.github.com/repos/Clever/s3-to-redshift/commits{/sha}","git_commits_url":"https://api.github.com/repos/Clever/s3-to-redshift/git/commits{/sha}","comments_url":"https://api.github.com/repos/Clever/s3-to-redshift/comments{/number}","issue_comment_url":"https://api.github.com/repos/Clever/s3-to-redshift/issues/comments{/number}","contents_url":"https://api.github.com/repos/Clever/s3-to-redshift/contents/{+path}","compare_url":"https://api.github.com/repos/Clever/s3-to-redshift/compare/{base}...{head}","merges_url":"https://api.github.com/repos/Clever/s3-to-redshift/merges","archive_url":"https://api.github.com/repos/Clever/s3-to-redshift/{archive_format}{/ref}","downloads_url":"https://api.github.com/repos/Clever/s3-to-redshift/downloads","issues_url":"https://api.github.com/repos/Clever/s3-to-redshift/issues{/number}","pulls_url":"https://api.github.com/repos/Clever/s3-to-redshift/pulls{/number}","milestones_url":"https://api.github.com/repos/Clever/s3-to-redshift/milestones{/number}","notifications_url":"https://api.github.com/repos/Clever/s3-to-redshift/notifications{?since,all,participating}","labels_url":"https://api.github.com/repos/Clever/s3-to-redshift/labels{/name}","releases_url":"https://api.github.com/repos/Clever/s3-to-redshift/releases{/id}","deployments_url":"https://api.github.com/repos/Clever/s3-to-redshift/deployments","created_at":"2015-01-16T21:35:10Z","updated_at":"2016-03-09T12:40:45Z","pushed_at":"2016-04-25T20:52:37Z","git_url":"git://github.com/Clever/s3-to-redshift.git","ssh_url":"git@github.com:Clever/s3-to-redshift.git","clone_url":"https://github.com/Clever/s3-to-redshift.git","svn_url":"https://github.com/Clever/s3-to-redshift","homepage":"","size":592,"stargazers_count":16,"watchers_count":16,"language":"Go","has_issues":true,"has_downloads":true,"has_wiki":true,"has_pages":false,"forks_count":0,"mirror_url":null,"open_issues_count":1,"forks":0,"open_issues":1,"watchers":16,"default_branch":"master"}},"base":{"label":"Clever:master","ref":"master","sha":"f92417cccaa82973d7eae512dc7d56d1d527ffcb","user":{"login":"Clever","id":1885509,"avatar_url":"https://avatars.githubusercontent.com/u/1885509?v=3","gravatar_id":"","url":"https://api.github.com/users/Clever","html_url":"https://github.com/Clever","followers_url":"https://api.github.com/users/Clever/followers","following_url":"https://api.github.com/users/Clever/following{/other_user}","gists_url":"https://api.github.com/users/Clever/gists{/gist_id}","starred_url":"https://api.github.com/users/Clever/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/Clever/subscriptions","organizations_url":"https://api.github.com/users/Clever/orgs","repos_url":"https://api.github.com/users/Clever/repos","events_url":"https://api.github.com/users/Clever/events{/privacy}","received_events_url":"https://api.github.com/users/Clever/received_events","type":"Organization","site_admin":false},"repo":{"id":29368865,"name":"s3-to-redshift","full_name":"Clever/s3-to-redshift","owner":{"login":"Clever","id":1885509,"avatar_url":"https://avatars.githubusercontent.com/u/1885509?v=3","gravatar_id":"","url":"https://api.github.com/users/Clever","html_url":"https://github.com/Clever","followers_url":"https://api.github.com/users/Clever/followers","following_url":"https://api.github.com/users/Clever/following{/other_user}","gists_url":"https://api.github.com/users/Clever/gists{/gist_id}","starred_url":"https://api.github.com/users/Clever/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/Clever/subscriptions","organizations_url":"https://api.github.com/users/Clever/orgs","repos_url":"https://api.github.com/users/Clever/repos","events_url":"https://api.github.com/users/Clever/events{/privacy}","received_events_url":"https://api.github.com/users/Clever/received_events","type":"Organization","site_admin":false},"private":false,"html_url":"https://github.com/Clever/s3-to-redshift","description":"Library and worker to handle transfer of data in s3 into redshift. Includes table creation and manipulation, as well as time-based insertion.","fork":false,"url":"https://api.github.com/repos/Clever/s3-to-redshift","forks_url":"https://api.github.com/repos/Clever/s3-to-redshift/forks","keys_url":"https://api.github.com/repos/Clever/s3-to-redshift/keys{/key_id}","collaborators_url":"https://api.github.com/repos/Clever/s3-to-redshift/collaborators{/collaborator}","teams_url":"https://api.github.com/repos/Clever/s3-to-redshift/teams","hooks_url":"https://api.github.com/repos/Clever/s3-to-redshift/hooks","issue_events_url":"https://api.github.com/repos/Clever/s3-to-redshift/issues/events{/number}","events_url":"https://api.github.com/repos/Clever/s3-to-redshift/events","assignees_url":"https://api.github.com/repos/Clever/s3-to-redshift/assignees{/user}","branches_url":"https://api.github.com/repos/Clever/s3-to-redshift/branches{/branch}","tags_url":"https://api.github.com/repos/Clever/s3-to-redshift/tags","blobs_url":"https://api.github.com/repos/Clever/s3-to-redshift/git/blobs{/sha}","git_tags_url":"https://api.github.com/repos/Clever/s3-to-redshift/git/tags{/sha}","git_refs_url":"https://api.github.com/repos/Clever/s3-to-redshift/git/refs{/sha}","trees_url":"https://api.github.com/repos/Clever/s3-to-redshift/git/trees{/sha}","statuses_url":"https://api.github.com/repos/Clever/s3-to-redshift/statuses/{sha}","languages_url":"https://api.github.com/repos/Clever/s3-to-redshift/languages","stargazers_url":"https://api.github.com/repos/Clever/s3-to-redshift/stargazers","contributors_url":"https://api.github.com/repos/Clever/s3-to-redshift/contributors","subscribers_url":"https://api.github.com/repos/Clever/s3-to-redshift/subscribers","subscription_url":"https://api.github.com/repos/Clever/s3-to-redshift/subscription","commits_url":"https://api.github.com/repos/Clever/s3-to-redshift/commits{/sha}","git_commits_url":"https://api.github.com/repos/Clever/s3-to-redshift/git/commits{/sha}","comments_url":"https://api.github.com/repos/Clever/s3-to-redshift/comments{/number}","issue_comment_url":"https://api.github.com/repos/Clever/s3-to-redshift/issues/comments{/number}","contents_url":"https://api.github.com/repos/Clever/s3-to-redshift/contents/{+path}","compare_url":"https://api.github.com/repos/Clever/s3-to-redshift/compare/{base}...{head}","merges_url":"https://api.github.com/repos/Clever/s3-to-redshift/merges","archive_url":"https://api.github.com/repos/Clever/s3-to-redshift/{archive_format}{/ref}","downloads_url":"https://api.github.com/repos/Clever/s3-to-redshift/downloads","issues_url":"https://api.github.com/repos/Clever/s3-to-redshift/issues{/number}","pulls_url":"https://api.github.com/repos/Clever/s3-to-redshift/pulls{/number}","milestones_url":"https://api.github.com/repos/Clever/s3-to-redshift/milestones{/number}","notifications_url":"https://api.github.com/repos/Clever/s3-to-redshift/notifications{?since,all,participating}","labels_url":"https://api.github.com/repos/Clever/s3-to-redshift/labels{/name}","releases_url":"https://api.github.com/repos/Clever/s3-to-redshift/releases{/id}","deployments_url":"https://api.github.com/repos/Clever/s3-to-redshift/deployments","created_at":"2015-01-16T21:35:10Z","updated_at":"2016-03-09T12:40:45Z","pushed_at":"2016-04-25T20:52:37Z","git_url":"git://github.com/Clever/s3-to-redshift.git","ssh_url":"git@github.com:Clever/s3-to-redshift.git","clone_url":"https://github.com/Clever/s3-to-redshift.git","svn_url":"https://github.com/Clever/s3-to-redshift","homepage":"","size":592,"stargazers_count":16,"watchers_count":16,"language":"Go","has_issues":true,"has_downloads":true,"has_wiki":true,"has_pages":false,"forks_count":0,"mirror_url":null,"open_issues_count":1,"forks":0,"open_issues":1,"watchers":16,"default_branch":"master"}},"_links":{"self":{"href":"https://api.github.com/repos/Clever/s3-to-redshift/pulls/62"},"html":{"href":"https://github.com/Clever/s3-to-redshift/pull/62"},"issue":{"href":"https://api.github.com/repos/Clever/s3-to-redshift/issues/62"},"comments":{"href":"https://api.github.com/repos/Clever/s3-to-redshift/issues/62/comments"},"review_comments":{"href":"https://api.github.com/repos/Clever/s3-to-redshift/pulls/62/comments"},"review_comment":{"href":"https://api.github.com/repos/Clever/s3-to-redshift/pulls/comments{/number}"},"commits":{"href":"https://api.github.com/repos/Clever/s3-to-redshift/pulls/62/commits"},"statuses":{"href":"https://api.github.com/repos/Clever/s3-to-redshift/statuses/9bcaf5cd834dc3f0006450da1476078a052f422e"}}}}