Schema Propagation Issue: Python Script Gem Output Not Showing in Downstream Gems

[Mukeshy12390]

I am experiencing an issue with schema propagation. When I load data using a Python script Gem, the data loads successfully and is visible in the output preview. However, when I add a downstream Gem (like Add ID), the column schema does not appear in the configuration tab. How can I ensure the schema from the script Gem is correctly passed to the next step?

[Callum_Finlayson]

Thanks for the question @Mukeshy12390 .

TLDR: Use a “Custom Schema” in your Script Gem.

To make it clearer for other readers of this thread, I thought i’d provide an example below, but what you’re experiencing is one of the tradeoffs of using a script gem. The system doesn’t persist external schema files for Script gems, so it’s not available at design time, but IS at run time. The simple way around this is to define a “Custom Schema” within the Script Gem.

The challenge is that the compiler can’t “see” this column, so you don’t see it as an input column in the config (but it’s there!). Behind the scenes we are generating code that is SELECT * from this unknown schema - so we will definitely select it.

Option 1 - In my opinion, the best way to ensure that the schema is there for the next step is to not use a script gem if possible, however I understand that sometimes this is unavoidable.

Option 2 - The next best way, (assuming the schema is fixed, and not dynamically generated) is to explicitly define the columns/schema in the UI. I will demonstrate this below.

Option 3 - Is to do nothing, the columns will be selected if they’re there, but as explained above - Prophecy can’t see it at design time.

Option 4 - is to actually ask Prophecy’s Transformation Agent (AI) to make this change for you

I tried to create a simplified pipeline of what you described, so I can demonstrate “Option 2” below:

I’ll step through my gems below:

1. The data (sample titanic data from Kaggle):

   

2. My Python script (insert today’s date for every column):

   

3. The data (after the script - see “todays_date” column at the far right) in the preview:

   

4. Here are the details for the “add_id” gem (as @Mukeshy12390 as pointed out the “todays_date” column isn’t listed):

   

   (even though it definitely is part of the output - shown in the previous screenshot):

5. So if we absolutely need the script column(s), we can modify the script gem to output a “Custom Schema”. In your Script Gem, select the output tab:

   
   
   Then down the bottom select the “Custom Schema”
   

6. Then add “todays_date” (in my case):

   

7. If you look in the downstream gem you can see the column (note “todays_date”):

   

If you have more questions, please reply and I’ll do my best to answer! Cheers, @Callum_Finlayson