Some very good introductory answers here.
Basically, get_template_part() allows theme developers to set up an order of specificity of template files. Think of it similarly to specificity as it applies to CSS selectors. When designing something, you want to start with the bare minimum of specificity, so that it can be easily overridden in parts of a design that need individual attention.
So for example, you’re styling a blog and you create a loop.php file which works well for marking up posts. But you plan ahead, and you call it in your template files later with additional context specifiers – say, on the index page, you call get_template_part( 'loop', 'index' );, on the single template, you call get_template_part( 'loop', 'single' );, on archive pages, you call get_template_part( 'loop', 'archive' );, and so on. This makes it very easy down the road when you decide to mark up the loop on your archive pages differently from the home page: just create a loop-archive.php template and it’ll be used rather than the generic loop.php.
But the magic behind get_template_part() is in the function locate_template(), which checks first the theme directory, then the parent directory (if one exists) for the file named. This is very useful for plugin development. In one of my plugins, I define a custom post type and created a loop template file for that custom post type in my plugin directory. But… I want to allow themes using my plugin to override my markup if they choose. This is where locate_template() really works wonders.
locate_template($template_names, $load = false, $require_once = true )
will look for each of the names in the $template_names array in the stylesheet directory, then in the template directory. Passing ‘true’ as the $load argument means that it will require the first file found, and will return an empty string if no template file was located. So I can do something like this in my plugin:
if ( '' === locate_template( 'loop-mycustomposttype.php', true, false ) )
include( 'loop-mycustomposttype.php' );
…which should hopefully make it very easy for theme developers to customize my plugin just by including a file called loop-mycustomposttype.php in their theme.