Thread
#secoda-feature-requests
    a

    adamant-forest-45768

    4 days ago
    Hello hello. Based on a few conversations I've had in the support channel, may I humbly suggest that Secoda's ERD functionality not rely on configurations that can only be set in the database itself? For example: we use Redshift, and to display table columns and the full ERD in Secoda, it sounds like I need to declare each table's primary/foreign keys in Redshift using DDL. The issue is that basically all of our reporting tables are initialized by DBT, which only supports a few table constraints in model configurations, unfortunately not including primary/foreign key declarations. This is an issue because Redshift does not allow you to alter attributes (like primary key status) on existing columns; those constraints must be declared when the column is initialized (often when the table itself is initialized). So when DBT first creates the table and its columns, there's no chance to go back in and declare its primary/foreign keys without some truly unwieldy workarounds. It would be so helpful if I could tell Secoda what each table's primary and foreign keys are, and have the option to display columns, regardless of the table constraints that exist in Redshift. Thank you 🙂
    d

    dazzling-wolf-90175

    4 days ago
    Hello Ryan. I was trying to understand this issue as our organization is looking into DBT, but I am not sure I fully understand the problem from the Redshift side. I can create Redshift tables without PK/FK constraints, and then add them after the fact.
    create table foo (id bigint not null);
    create table bar (id bigint not null, foo_id bigint);
    
    insert into foo values (1);
    insert into foo values (2);
    insert into foo values (3);
    
    insert into bar values (1, 1);
    insert into bar values (2, 1);
    insert into bar values (3, 1);
    
    
    alter table foo add primary key(id);
    alter table bar add primary key(id);
    alter table bar add foreign key(foo_id) references foo(id);
    This works fine natively in Redshift. So then I wonder, is this more of a DBT limitation? Or am I miss understanding the problem you are describing?
    a

    adamant-forest-45768

    4 days ago
    Hello @dazzling-wolf-90175 🙂 Your code definitely works as written, although if you omit the "not null" constraints when creating the tables, it produces the same error I'm now getting: you can't make a nullable column the primary key. Unfortunately, the "not null" constraint is not something you can tell DBT to apply when it initializes a table. Like I said, there are potential workarounds, but they all kind of suck. (side note, I do hope y'all end up going forward with DBT...it's been a game changer for us)
    d

    dazzling-wolf-90175

    4 days ago
    Thanks for the feedback Ryan. I like to understand these gotchas whenever I can, really helps out when we start implementing.