Drupal - How to choose what tables to skip for drush sql-sync @dev @staging?

Drush sql-sync does not support shared databases that use prefixes. You might have some luck if you wrote a wrapper script that used drush sqlq 'show tables;' to get a list of available tables, filter them manually by prefix, and then pass them back to sql-sync via the --tables-list option.

In Drupal 7 these tables you should consider:

'cache', 'cache_field', 'cache_form', 'cache_filter', 'cache_menu', 'cache_page', 'history', 'sessions', 'watchdog'

In Drupal 8, these:

'cache', 'cache_bootstrap', 'cache_config', 'cache_container', 'cache_data', 'cache_default', 'cache_discovery', 'cache_filter', 'cache_menu', 'cache_page', 'history', 'search_index', 'sessions', 'watchdog'

This doesn't include specific tables which are related to Migrate module or any other custom tables which may store some temporary data.

Example drush command to dump and skip these tables (create just a structure without data):

drush sql-dump --structure-tables-list=cache,cache_filter,cache_menu,cache_page,history,sessions,watchdog --result-file=dump.sql

^{Hint: Add --ordered-dump for nicer human output, but a bit slower on import.}

Normally adding structure-table option should be enough for local operations ($options['structure-tables']), but for sql-sync, I think this should be added for specific alias in command-specific section, e.g.

$aliases['foo'] = array(
  // These options will only be set if the alias is used with the specified command.
  'command-specific' => array(
    'sql-sync' => array(
      'structure-tables-key' => 'common',
      'skip-tables-key' => 'common',
      'structure-tables' => array(
        // You can add more tables which contain data to be ignored by the database dump
        'common' => array('cache', 'cache_filter', 'cache_menu', 'cache_page', 'history', 'search_index', 'sessions', 'watchdog'),
      ),
      'skip-tables' => array(
        'common' => array('field_deleted_revision_63'),
      ),
    ),
    'sql-dump' => array(
      'ordered-dump' => TRUE,
      'structure-tables-key' => 'common',
      'skip-tables-key' => 'common',
    ),
  ), // end: command-specific
  // Applied only if the alias is used as the source.
  'source-command-specific' => array(
  ),
  // Applied only if the alias is used as the target.
  'target-command-specific' => array(
  ),
);