Redshift is missing in target database list of AWS SCT Schema Convertion Tool

Perhaps the JSON you have is not quite correct. When I paste your JSON into jsonlint.com I get the following:

Parse error on line 7: 
...        "username": “user23"} ] }
-----------------------^
Expecting 'STRING', 'NUMBER', 'NULL', 'TRUE', 'FALSE', '{', '['

The AWS Redshift documentation on COPY SSH parameters has the following

The quote characters must be simple quotation marks (0x22), not slanted or "smart" quotation marks.

So double check you quote marks around the username you've provided, and if they are how you've provided them above, change it from to “user23" >> "user23" (note the first quote mark is different).

• The first quote mark in your code is unicode : U+201C and not U+0022

You are definately on the right track with Kimball rather than inmon for Redshift.

There are a number of patterns for this, I have used them all in different use cases

1. "ELT" pattern - Load the source tables to redshift fully, do not do any significant transformations until the data has been loaded. For this you can either load to s3, then use redshift copy command or I would recommend using "AWS data migration services", which can sync a source (e.g.mysql or postgres) to a target (e.g. redshift) Then, on a regular basis run sql processes within redshift to populate dims then facts. You can use third part cloud based tools to "simplify" this process if you want to - such as Matillion (i do not recommend using a third party tool)
2. "ETL pattern" - Transform the data in flight, using apache spark. and load the dims and facts into redshift spark->s3->redshift. I have used EMR for this which is good. this is also the approach taken if you use AWS Glue
3. Do not transform ! - similar to 1) but just use the tables that have been loaded.

Note that Redshift sometimes works BETTER if you have a wide table with repeated values rather than a fact and dimensions. The reason for this is that the columnar approach lets Redshift compress the different values down to a level that is pretty efficient. I do not have a formula for when to use many Dimensions vs a flat wide table, the only way is to try it and see!

Some links

AWS DMS for Redshift taret

AWS Glue