SQL Gems
In Prophecy and dbt, data models are groups of SQL statements used to create a single table or view. Prophecy simplifies data modeling by visualizing the data model as a series of steps, each represented by a gem.
Each gem corresponds to a SQL statement, which users can construct through an intuitive visual interface. Prophecy handles the underlying complexity by deciding whether each gem should generate a CTE or a subquery. Users simply configure the gem's interface, and Prophecy integrates the resulting SQL into the larger data model.
The table below lists each gem available for data modeling.
| Category | Description |
|---|---|
| Aggregate | Performs aggregation operations on datasets, such as computing sums, averages, and counts. |
| Deduplicate | Removes duplicate records from a dataset to ensure data uniqueness and integrity. |
| Except | Extract rows that are present in the first table but absent from all subsequent tables. |
| Filter | Filters records in a dataset based on specified conditions, allowing for the selection of relevant data. |
| FlattenSchema | Converts nested or hierarchical data structures into a flat table format. |
| Intersect | Return only the rows that appear in all input tables. |
| Join | Combines two or more datasets based on a common key. |
| Limit | Restricts the number of records in a dataset to a specified number. |
| Macro | Defines reusable code snippets or functions that can be invoked across multiple models. |
| Models | Sequences of steps to perform SQL-based transformations that results in a single table. |
| OrderBy | Sorts records in a dataset based on specified columns. |
| Reformat | Changes the format or structure of data within a dataset, such as modifying date formats or string cases. |
| Seed | Provides initial data to a pipeline or model, often used for testing or as reference data. |
| SQLStatement | Executes custom SQL statements within a pipeline or model, offering flexibility for complex transformations. |
| Union | Combine records from different sources. |
| WindowFunction | Performs calculations across a set of table rows related to the current row (like running totals or moving averages). |
What's next
To understand the generic structure of a gem, review the Gem concept page. To understand how to use visual expressions in SQL gems, visit Visual Gem Builder.