{"id":21,"date":"2007-06-18T11:32:56","date_gmt":"2007-06-18T11:32:56","guid":{"rendered":"https:\/\/www.wapshere.com\/missmiis\/?p=21"},"modified":"2023-01-09T07:32:44","modified_gmt":"2023-01-09T07:32:44","slug":"some-thoughts-on-clearing-delta-tables","status":"publish","type":"post","link":"https:\/\/www.wapshere.com\/missmiis\/some-thoughts-on-clearing-delta-tables","title":{"rendered":"Some Thoughts on Clearing Delta Tables"},"content":{"rendered":"

When planning delta imports from SQL, thought needs to be given to clearing down the delta tables. If you have any type of code running in response to the imported data then you have to accept that import errors will<\/em> occur, some of them not even your fault! <\/span><\/span><\/p>\n

What should happen to the rows in the Delta table that were not imported due to errors? Ideally you will let these rows stay put, giving MIIS another go at them on the next Delta Import. But what, then, should you do with the rows that were successfully imported?<\/span><\/span><\/p>\n

The solution will depend on the number of objects you’re dealing with, how often you manage a Full Import, and what the repercussions are of missed imports. In a system where a complete set of Full Imports can be run overnight, you may well find that a few missed deltas during the day are not significant. Alternatively, if the Fulls are run weekly or less often, you are completely dependant on the reliability of your Deltas for failed imports to get another chance.<\/span><\/span><\/p>\n

Some possible solutions: optimistic, pessimistic and best-odds.<\/span><\/span><\/p>\n

Optimistic<\/span><\/span><\/h3>\n

Assume all Delta Imports are successful. Clear out the Delta table as an automatic step before generating the new Delta table.<\/span><\/span><\/p>\n

This approach works fine in simple systems where Full Imports can be run fairly regularly (at least once a day) to mop up any missed imports. A big problem with it is that an Import must be run after every run<\/em> of the DTS. This can quickly turn into a complete pain when you’re troubleshooting.<\/span><\/span><\/p>\n

Pessimistic<\/span><\/span><\/h3>\n

Each row in the Delta table is independently checked for import success, and only removed once the import is confirmed.<\/span><\/span><\/p>\n

You could perhaps write some code that checks through the import log file after each import, and only deletes lines from the Delta table that look correct in the log. <\/span><\/span><\/p>\n

On my MIIS course, the lovely Hugh Simpson-Wells<\/a> suggested an extra MA where objects are sync’d back from the metaverse just so you can compare them with the source data. This is not something I’ve ever tried, but I guess it would be the way to go if you needed 100% verification on those imports.<\/span><\/span><\/p>\n

Best-Odds<\/span><\/span><\/h3>\n

The best-odds approach that I adopted was to do a simple check for errors following the Delta Import task. If any<\/em> errors occur, I keep the entire<\/em> Delta table. Otherwise the Delta table is cleared.<\/span><\/span><\/p>\n

The main downside of this approach is that a single (possibly trivial) error will prevent the clearing, and will cause your delta table to grow and grow. MIIS will not be bothered by being given the same import data over and over, but it does slow everything down. You can also run into problems when the same object appears multiple times in the table with conflicting instructions (like a ‘Delete’ followed by a ‘Modify’). Adding a de-duping step to your DTS helps, but the only solution is to ensure that import errors are sorted out ASAP.<\/span><\/span><\/p>\n

There are a number of technical examples I should include at this point, but this is a philosophising post so I won’t do it here. There’s more to come on how to write a VBScript to run your MIIS jobs<\/a>, check for errors, and then fire off whatever else you need to do. <\/span><\/span><\/p>\n\n\n

<\/p>\n","protected":false},"excerpt":{"rendered":"

When planning delta imports from SQL, thought needs to be given to clearing down the delta tables. If you have any type of code running in response to the imported data then you have to accept that import errors will occur, some of them not even your fault! What should happen to the rows in…<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"jetpack_post_was_ever_published":false,"footnotes":"","jetpack_publicize_message":"","jetpack_is_tweetstorm":false,"jetpack_publicize_feature_enabled":true,"jetpack_social_post_already_shared":false,"jetpack_social_options":[]},"categories":[34,28,3,5],"tags":[],"class_list":["post-21","post","type-post","status-publish","format-standard","hentry","category-ilm2007","category-miis2003","category-philosophising","category-sql"],"jetpack_publicize_connections":[],"jetpack_featured_media_url":"","jetpack_shortlink":"https:\/\/wp.me\/pkp1o-l","jetpack_sharing_enabled":true,"_links":{"self":[{"href":"https:\/\/www.wapshere.com\/missmiis\/wp-json\/wp\/v2\/posts\/21","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.wapshere.com\/missmiis\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.wapshere.com\/missmiis\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.wapshere.com\/missmiis\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.wapshere.com\/missmiis\/wp-json\/wp\/v2\/comments?post=21"}],"version-history":[{"count":2,"href":"https:\/\/www.wapshere.com\/missmiis\/wp-json\/wp\/v2\/posts\/21\/revisions"}],"predecessor-version":[{"id":3326,"href":"https:\/\/www.wapshere.com\/missmiis\/wp-json\/wp\/v2\/posts\/21\/revisions\/3326"}],"wp:attachment":[{"href":"https:\/\/www.wapshere.com\/missmiis\/wp-json\/wp\/v2\/media?parent=21"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.wapshere.com\/missmiis\/wp-json\/wp\/v2\/categories?post=21"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.wapshere.com\/missmiis\/wp-json\/wp\/v2\/tags?post=21"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}