Skip to main content

New UI for SQL

Available in Prophecy Release 3.3.9​

Prophecy's SQL Transformation Copilot has a new user interface in four different areas. Check them out in the following video or the descriptions. Find out what to expect in your new user interface for an even easier experience.

Model write options​

In this release, we've updated the UX and capabilities of the target model format and write options. In the previous release, you'd find Advance Settings are available for defining model write formats and lots of other DBT options (left). In this release, the advance settings are still available, but the most frequently used settings are accessible in the target model Gem as well (right).

ModelWrites

There are now the following tabs within the target model:

  • Type & Format: Update the format of the model, e.g., table, view, ephemeral, etc.
  • Location: Update the location by overwriting the Database, Schema, or Alias
  • Schema: Make schema changes
  • SQL Query: View and enable your custom SQL query
  • Write Options: Use Write Modes such as Overwrite, Append, and Merge

Variable Declaration and the Target Model SQL Query interface​

We've added the powerful functionality to configure variables directly in a SQL Query for each target model. Normally only advanced users would be able to use Jinja or DBT templating with declared variables for their last mile data processing. Declared variables are accessible by clicking Configuration to add the variable of interest (left). With this release, you can use the variable (along with standard DBT functions) in the target model SQL Query tab (right).

DelcarVars

Building SCD2 Tables​

Slowly changing dimensions (SCD) doesn't have to be so challenging. With this release, you can take advantage of Prophecy's visual interface to configure SCD type 2 writes and retain data history. Open the Target Model Gem, select Table format. For the merge write option, select SCD2 and choose a unique key and relevant timestamp column for your data. Now any data practitioner can capture updated records without deleting the previous records.

SCD2

Visual Expression Builder​

You can use a simplified expression builder within your data transformation Gems, Data Explorer, and Data Tests. The Visual Expression Builder takes you through building your expressions, following a step-by-step visual guide. It suggests expressions and functions to you, including nested and conditional functions. All the while, you don't have to worry about writing the expression syntax since it takes care of that for you.

expressionBuilder