Parent Child Insert Improvements / Improved Key Generation Techniques

[brianscott]

Hello Dev Gurus - 

Populating a parent / child relationship into a RDBMS in Alteryx is a lot harder than it should be.  The hacks workflows must go through to do anything other than populating single tables makes Alteryx extremely cumbersome to use for any non-trivial ETL process; especially if you live in an existing database environment using database housed, sequentially generated primary key values.

Consider a workflow that generates data to the point where you create a cascading set of rows:

• One super parent.  Table A. 
• Two children rows in Table B. 
• Two children rows to rows in Table B, into Table C.

In Alteryx, the only way I have found to do this is by altering my tables to have UUID columns, populating them and then using dynamic selects afterwards, i.e.: 

• Populate my row bound for table A with a UUID column I generate in stream.  
• Make a new insert into Table A. 
• Perform dynamic selects from Table A where GUID == current row GUID.
• Get the primary key back out from that data set. 
• Add the primary key to the data bound for Table B. 
• Generate a UUID column for the data bound for Table B. 
• Repeat. . . . 

Not only this, but this 'technique' mandates you either use a block until done and a batch macro to insure your data going to Table A finishes up, or block until done using WaitASecond tool for the lazy among us (like me).  It is amazingly clunky.  

In my coding days, there were a variety of ORM tools that would let you do an insert and then immediately make back available to you the primary key that was generated, whether the key was created via database or by the code library itself.  If Hibernate 3.0 released 12 years ago can make this work, I'm pretty sure that the people creating Alteryx tomorrow can do the same thing one way or the other.  

Basically what we need is an output-insert tool that has a data stream back out that comes with the data stream and the fancy new primary key that is to the row.  Easy mode is to have it only operate for tables that have sequentially generated primary key values.  Alteryx 2022.1 mode is to give the user some key generation technique options at tool configuration time.  

Thank you for listening to my Ted Talk regarding improvements to the output tool to make ETL operations more efficient.  

[KylieF]

Thank you for your feedback and such a thorough walk through of the issue and ask!

I've reviewed your idea and it looks good for product once the necessary like criteria has been met. Please be sure to check out our Submission Guidelines as well as like any other ideas that would be most helpful to your Alteryx experience.