Renaming a content type during migration
As you've figured out, modifying the d6_node-based migration affects how the content of a given node type is migrated, but does not affect how the actual node type itself is migrated - that is done in d6_node_type. To rename the types themselves in migration, while you could modify the source query as you did, it's simpler to modify the .yml to remap values.
In upgrade_d6_node_type.yml,  you can replace

process:
  type: type
  name: name


with 

process:
  type:
    plugin: static_map
    bypass: true
    source: type
    map:
      story: post
      book_page: faq
  name:
    plugin: static_map
    bypass: true
    source: name
    map:
      story: post
      'Book Page': Faq