TL;DR

In the first blog post of our tech series we provided real-world use cases with Prophecy’s low-code data engineering platform, and shared how to import existing DBT projects into the Prophecy visual interface to easily create new models in just minutes. In this blog post, the second in our series, we’re going to show how you can build, deploy, and orchestrate high quality SQL models quickly and easily.

Problem:

• SQL developers create models in silos
• Thousands of lines of SQL code are hard to follow and manage
• Proprietary software can restrict access to software development practices
• SQL models need to interplay with data pipelines created in other languages
  

Solution:

• Build models interchangeably using Prophecy’s visual canvas or code editor
• Commit model logic to Git using the industry best standards for code quality
• Code is versioned, tested, integrated, and deployed as software should be
• Orchestrate SQL models alongside other data pipelines
  

Get started with a free trial of Prophecy here.

Let’s see how this works:

Visual developers, data analysts, Scala/Python users, and SQL coders can now all work together seamlessly in Prophecy. Everyone’s projects are committed to Git in organized codebases; data transformations are run on modern storage and compute engines.

In Part 1 of this series (link), we explored interactive SQL model development using Prophecy’s visual canvas and code editor. Prophecy even makes it easy to take advantage of DBT Core’s advanced features.

Here in Part 2, we see how to take advantage of Prophecy’s integration with Git. Prophecy’s Project is a GIT repository or a directory that contains all of your transformation logic. Each Prophecy Project contains a dbt Core™️ project.

As a good teammate, we don’t want to write changes directly on the main branch of our Git repository. Every member should have their own branch on which they can freely build and play around with the project, without interrupting each other’s work.

Therefore, to start development we have to create our own development branch. In order to ensure that this branch is individualized and obviously separate form the main branch, as mentioned, it’s important to label the branch with a unique identifier that easily represents the changes you’re making. In our example below, we suggest you label this branch with your own name.

As we can see in the video above, Prophecy enables every data citizen to easily deploy code using the following 4 steps:

1. Commit: We start by creating a named version of our code and uploading it to our development branch on the secure GIT repository. On the left hand side you can see the Current branch and the associated  history of commits and on the right side, there’s a list of Entities changed (models, jobs, etc) and their status.
2. Pull: Before your changes can be safely merged into the main branch, we have to make sure that we’re up to date with it. If your colleagues introduced any code on main we have to Pull it first.
3. Merge: Now that our development branch is up to date, we can merge it to master. Here we can either create a Pull Request or if you’re the owner of the repository force Merge the changes. Once the code is merged, you can now see the latest commits present on your main branch.
4. Release: Finally, now that our changes are all versioned on GIT, we can release them to our scheduler. Simply specify a Release Version number, e.g. 1.0 , and the Release Note, which should clearly outline the latest changes.

Anyone can take advantage of the above software development cycle. Prophecy takes you through the steps and makes best practices accessible for every data practitioner. Watch data quality issues disappear. Don’t forget to observe data operations once the projects are deployed - check for job failures, tests passing, and investigate issues in real time.  

Next you’ll want to schedule your models on your favorite SQL Warehouse; we recommend Databricks’ Serverless Pro warehouse thanks to it’s simplified management and advanced analytics capabilities. Now what happens when your data is accessed by both SQL models AND Scala / Python data pipelines? We need a way to schedule, in a particular order, all the code relevant for a particular set of data.

With Prophecy, we can orchestrate both pipelines and models using a drag and drop interface. Prophecy integrates with Databricks and Airflow schedulers. Let’s see this in action!

The steps involved in this level of orchestration in Prophecy are easy to execute with the following:

• Create a Job called “OrchestrateRetail”
• Compose your Job using pipelines, dbt Core SQL models and custom scripts
• Consider using the Bronze, Silver, and Gold architecture format
• Configure the Job to run on schedule
• Alert the right people on success or failure
• Monitor each step for errors, test failures, and log details
  

Many data practitioners will be surprised on how easily they can deploy SQL models. Prophecy customers report the entire lifecycle - from model development to deployment to orchestration - is reduced from weeks to days. Code-savvy users love the dbt Core SQL standardization, code versions, and the ability to review changes.

As teams mature in their model development, they’ll want to publish and subscribe to data products, reducing code duplication. In the next blog, we’ll explore how to build and share data products with Prophecy.