Reverse Engineer Data Model from Amazon Redshift

[Former Member]

First post / new member - apologies if I'm doing this wrong.

I'm looking for a data modelling tool that supports forward and reverse engineering of database tables for Amazon Redshift. SAP PowerDesigner seems to support Redshift, but the Reverse Engineering option is not importing some table options. It gets the table and column information, but misses the Distribution Style/Key, and the Sort style/key. These attributes are all enterable in PowerDesigner under the generic "Extended Attributes" tab, and while they seem to forward engineer okay (the CREATE TABLE SQL includes any values you set), the Reverse Engineer won't pull them from the database.

Here's what I'm doing:

- Open PowerDesigner

- File->Reverse Engineer->Database

- Choose DBMS = Amazon Redshift Database

- Provide ODBC credentials to my Redshift database

- Select my tables from the Database Reverse Engineering list of database tables

- They import

- Open a table and go to the Extended Attributes tab - None of the Redshift attributes are imported

- Go to the Preview tab and inspect the CREATE TABLE statement - It does not include the Distribution or Sort settings.

Is this a known bug/limitation with Redshift reverse engineer?

If so, is there a patch? I need to select a tool for our new data warehouse, and I can't get PowerDesigner over the line without Reverse Engineer capability.

If not, what am I doing wrong?

[Former Member]

I forgot to mention that you can find the programmatic names for the extended attributes (for use in your SQL query) in the XDB under the Profile\Table\Extended Attributes node.