Skip to main content
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.
Use the Prophecy Agent to help you add gems throughout your pipeline.

​
Categories

You can select gems from the following categories. Each category opens a dropdown menu when you click it.

​
Bookmark favorite gems

You can bookmark frequently used gems so they’re easiliy available from the gem drawer. To bookmark a gem, click the star icon next to the gem in the category’s dropdown menu. Starred gems appear in the Favorites section of the toolbar, where they’re displayed as icon-only shortcuts. From Favorites, you can:
  • Click a gem to add it directly to the canvas.
  • Drag a gem onto the canvas.
  • Click the star again to remove it from your favorites.

​
Interactive gem 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 preconfigured pipeline! Gem example

​
Expressions

Many gems include expression components where you can implement custom or complex logic. You can either build expressions visually, or use SQL syntax to write expression in code. Prophecy will automatically convert visual expressions to code expressions, and vice versa.
The SQL dialect for expressions depends on the SQL warehouse connection of your fabric. Project code must match the requirements of the execution environment. As a result, expressions can differ between projects that run on Databricks, Snowflake, or other platforms.

​
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. Gem instance
If you select one or more gems, you can copy and paste them within the same pipeline or across pipelines. However, you cannot paste across projects that use different languages (for example, from SQL to Scala).

​
Gem configuration

When you open a gem, you can configure how the gem will work. Explore our individual gem documentation to understand each gem’s parameters.

​
Gem ports

Each gem handles ports differently. Some require specific input and output ports. Others allow only one port or none at all. Refer to the documentation for each gem to understand its port requirements. To add a port:
  1. Open the gem to access its configuration.
  2. On the left, choose the Input or Output tab under Ports.
  3. Click the + button next to Ports. If the + button doesn’t appear, the gem does not support adding more ports.
To remove ports from a gem:
  1. Open the gem to access its configuration.
  2. On the left, choose the Input or Output tab under Ports.
  3. Click the pencil icon to enter Edit mode.
  4. Hover over the port you want to remove and click the trash icon.
  5. Click Done to save your changes and exit Edit mode.
To hide ports from the gem configuration, you can collapse the ports panel using the collapse icon next to Ports.

​
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:

​
Applying actions to multiple gems

You can apply the following actions to multiple gems at once: Change Phase, Attach Annotation, Data Preview, Record Count, and Group. To apply actions to multiple gems:
  1. Drag to select multiple gems on the canvas.
  2. Click Actions at the bottom of the canvas.
  3. Select desired action.

​
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.
Normally, pipeline branches run in parallel. Using gem phases, you can develop your pipeline to run in different stages. This can be useful when one part of the pipeline depends on the results of another, allowing you to control the execution order and ensure that data flows correctly from one stage to the next.