Prophecy, a low-code suite for data engineering, has expanded its platform to include SQL development. Once users connect Prophecy to their Git and SQL Warehouse, they are empowered with a glass-box approach to model development. Users can import existing DBT projects to the visual interface or create a new model in just minutes. New visual designs are converted to well-formatted DBT Core SQL code; a single project supports both visual and code edits.
‍
Additionally, Prophecy users access advanced DBT features through an easy-to-use visual interface. Data practitioners across the organization can now materialize models, use CTEs and subqueries, build loops and create macros. Every data practitioner becomes a highly productive data engineer.

Let’s see what’s offered and how to get started.

Why do I need my SQL models stored to Git?

Folks familiar with first generation GUI tools might still be in the mindset that SQL models or pipelines remain caged in that proprietary tool. It’s time to rethink our relationship with our tools. Instead, Prophecy saves model code to Git for a glass-box approach to model development. This unlocks collaboration opportunities where analysts and engineers can easily view and share code, streamlines the release process, and ensures tight version control. Just as with any other project in Git.  

Getting started

When you create a Prophecy account (link), here is what you’re setting up:

💠 Prophecy’s visual interface as pictured above, where data pipelines are built using a drag and drop interface or with SQL code. The visual and code interfaces are interchangeable.  Wow! We’ll learn more on this below.

💠  A connection to Git - all data models (or pipelines) are saved to your Git using versioned, well-formatted SQL code. Prophecy creates code that you are able to view, edit, and share. A completely glass-box approach.

💠 A connection to your data - Prophecy connects directly to Databricks’ Serverless SQL Warehouse, (as well as ADLS, JDBC, etc), providing a very accessible interface for distributed, scalable warehouses and engines.

After connecting to Git and to Databricks, we can import an existing DBT project! Projects are easier to explain when presented in the visual view. Prophecy seamlessly integrates new and existing DBT projects, so each project is easy to self-serve. But Prophecy is not just for the visual coder. Prophecy incorporates both visual and code edits into a single project. Let’s see this in action:

• Import existing DBT  projects to Prophecy’s visual interface
• Open a model. Add a transformation Gem.  
• Interactively run and view data for easy debugging
• Modify a column. Hints help to boost productivity!
• Prophecy incorporates visual edits to the high quality codebase
• Now let’s try the reverse: Modify the code
• Prophecy incorporates code edits to the visual view
  

So far we imported a DBT project to Prophecy’s visual interface. Starting right away, Prophecy is automatically converting DBT code to visual pipelines, allowing us to edit both code and visual views. Visual development is accessible and boosts productivity for anyone who loves interacting with data - no coding experience needed.
‍

Create a new DBT model in just minutes

Next we will demo how to create a new model, the SQL construct for writing tables or views, by simply dragging to our visual canvas.

To our model we’ll add data sources, transform data using basic excel-like functions, and then write our final table to our Databricks Serverless Warehouse. We interactively run the model and preview data to make sure we’re on the right track, making debugging easier than ever.

At each step, our visual design is converted to well-formatted DBT Core SQL code. This code is then stored on git so that every data practitioner is guided with best software development practices. This process takes minutes and delivers a giant leap in productivity!

‍

‍

To summarize: anyone can design and execute data pipelines using basic, excel-like functions. SQL veterans will appreciate no longer writing boilerplate code. Click to create a project in Prophecy, and your code is standardized in a readable codebase. As a result, SQL models are versioned, subject to peer review, and changes are easy to track.

Let’s make DBT core’s advanced features intuitive:

Those who appreciate best software engineering practices will love this section, because we’re exploring the best features of DBT. (spoiler alert: you don’t have to understand how to code in order to reap these benefits). DBT core is the best format for code. However, these standard formats can be challenging to learn and implement. Let’s remove the barriers and make DBT’s best features accessible for everyone.

‍

✅ Materialize Models

Prophecy makes it easy to select how to materialize your models - as table, view, ephemeral, or incremental. With Prophecy just click to specify how to materialize your model.

✅ CTEs vs Subqueries

Prophecy is smart about when to use Common Table Expressions (CTEs) or Subqueries; designed with performance and code quality in mind.

‍

✅ Loop

It’s easy to build loops in Prophecy. Just define the variable, open the Gem of interest, then add the loop expression. Try a for-loop. Run the model to verify the results. That was easy! Now domain experts can self-serve!

✅ Macros

Macros (or functions) allow the user to call a repeatable piece of code. With Prophecy, just click to create a function with your business logic. Now you can call the function from any of the visual components. Interactively run the model to test if the function is working as expected. Prophecy converts the visually designed macro into standardized, performant code.

‍

‍

Prophecy takes care of code organization and makes concepts like model materialization accessible. Data practitioners can use loops and macros without getting stuck with jinja templating or invalid yml files. Don’t forget to generate functional tests using Prophecy’s visual interface. Your tests are incorporated into the release process automatically. Build better models faster, with Prophecy.

Democratizing Data Transformations for Everyone

Prophecy offers a complete data engineering platform accessible to new SQL users yet accelerates development for even the most savvy SQL expert. Here we explored how the visual query editor unlocks productivity for business analysts, analytics engineers, and anyone looking to build data models. Truly an end-to-end platform, Prophecy enables best software development practices for all data practitioners across an organization.

Take the next steps by signing up for a 2 week trial If you want to stay up to date on our latest insights and product features, check out our blog. Up next, we’ll boost productivity and best practices with orchestration and CI/CD in Prophecy’s easy-to-use interface. Then, by popular demand, we’ll show how every data practitioner can share data products via publish / subscribe. Stay tuned!