Gems

Gems are functional units in a pipeline that perform tasks such as reading, transforming, writing, or handling other data operations. When you build pipelines in a SQL project, some gems will be powered by Prophecy Automate, and some gems will be powered by SQL dbt.

Categories

Category	Description	Execution
Source/Target	Read and write data from various data providers.	Prophecy Automate
Transform	Modify, enrich, or reshape data during processing.	SQL warehouse
Prepare	Clean, structure, and optimize data for analysis.	SQL warehouse
Join	Merge, split, or link datasets.	SQL warehouse
Parse	Interpret structured formats in columns like XML and JSON.	SQL warehouse
Report	Share results through channels such as email or Tableau.	Prophecy Automate
Subgraph	Group gems into reusable components.	Depends
Custom	Enhance and extend Prophecy’s functionality.	Depends

Interactive examples

To test a gem hands-on, you can try the interactive example of the gem. If you search for a gem in the project sidebar, you can open the associated example and run the pre-configured pipeline!

Gem instance

When you click on a gem from the gem drawer, an instance of that gem gets added to your pipeline canvas. Use the image and the table below to understand the UI of a gem.

Callout	UI element	Description
1	Gem label	The name of this particular gem instance. It must be unique within a given pipeline.
2	Gem type name	The type of gem.
3	Input ports	One or more ports that accept connections from upstream gems.
4	Output ports	One or more ports that connect to downstream gems.
5	Gem phase	The phase for this gem instance, which defines the order in which gem instances are executed.
6	Open	The button that lets you open the gem configuration.
7	Run button	A button that runs the pipeline up to and including the gem.
8	Action menu	A menu that includes options to change the phase of the gem, add run conditions, delete the gem, and more.

Gem configuration

When you open a gem, you can configure how the gem will work.

Visual and code view

Some gems can be configured in the visual view or the code view. Use the visual expression builder to populate fields in the visual view. Prophecy will automatically convert visual expressions into SQL expressions. You can edit these SQL statements or write your own in the code view.

Action menu

The action menu gives you more granular control over individual gems. When you expand the action menu, you see the following options:

Action	Description
Explain	Copilot adds a comment to the gem that explains what it does.
Fix	Copilot resolves an error in the gem configuration.
Label	Copilot renames the gem.
Add Comment	Manually write a comment that explains the gem.
Change Phase	Change the phase of the gem.
Delete	Remove the gem from the pipeline.

Gem phase

In a data pipeline, the phase of a gem determines the sequence in which it runs. Here’s how it works:

• Gems are assigned a numerical phase (e.g., 0, 1, -1), where lower values run first. For example, a gem with phase 0 will execute before a gem with phase 1.
• When a gem runs, all its upstream gems must also run. This means that if a downstream gem has phase 0 and an upstream gem has phase 1, the upstream gem will be grouped into phase 0 to ensure proper execution.
• Because of this dependency, the phase assigned to the last gem in a branch determines the phase of the entire branch. This means that when configuring gem phases, you only need to focus on the leaf nodes—the final gems in each branch of the pipeline.