Migrating paragraph items to their host nodes from Drupal 7 to Drupal 9?
I was thinking about this in the wrong way. The source plugin returns a list of results to migrate one at a time - the SQL query I was using would cause you to get for example, 5 rows for one node, each with a paragraph id. That is why only one paragraph was showing up on nodes at the end of a migration.
That isn't what you want - what you want instead is one row with the node id and 5 paragraph IDs.
I was able to do that by adjusting my source plugin query to the following:
  /**
   * {@inheritdoc}
   */
  public function query() {
    $query = $this-&gt;select('node', 'n');
    $query-&gt;join('D7_FIELD_NAME', 'fc', 'fc.entity_id = n.nid');
    $query-&gt;fields('n', [
      'nid',
      'title'
    ]);
    $query-&gt;condition('n.status', 1);
    $query-&gt;addExpression('GROUP_CONCAT(fc.D7_FIELD_NAME_VALUE)', 'paragraph_ids');
    $query-&gt;groupBy('n.nid');
    $query-&gt;groupBy('n.title');
    $query-&gt;orderBy('fc.delta');
    return $query;
  }

This produces a result set of
nid | paragraph_ids
-------------------
1     2, 4, 6, 8
2     13, 18, 12, 9
...........

Once you have that part, then you can fill in your Migration definition:
  D9_DESTINATION_FIELD_NAME:
    - plugin: explode
      delimiter: ','
      source: paragraph_ids
    - plugin: to_array
    - plugin: sub_process
      process:
        target_id:
          - plugin: migration_lookup
            migration:
              - accordion_items_paragraph
              - editor_items
              - highlight_files
            source: value
          - plugin: extract
            index:
              - 0
        target_revision_id:
          - plugin: migration_lookup
            migration:
              - accordion_items_paragraph
              - editor_items
              - highlight_files
            source: value
          - plugin: extract
            index:
              - 1

explode changes the comma delimited list into an array, and to_array is a small plugin I made to make it an array of arrays, each with an index of &quot;value&quot; and the id.
sub_process iterates over that and does a lookup against my other 3 paragraph migrations to drop the new destination id into target_id and target_revision_id.
The nid mapping from the original question still works, and passing multiple node migrations provides the full lookup capability. So for any nodes the result set returns, they are updated accordingly.
Now all migrated Paragraphs show up on their respective nodes in the correct order. I was able to apply this pattern to 3 different entity_reference_revision fields in Drupal 9 that held Paragraphs.