SQL models are easy to deploy and orchestrate with Prophecy

SQL models are easy to deploy and orchestrate with Prophecy

Stop getting stuck managing SQL files, start making progress for your business today

Stop getting stuck managing SQL files, start making progress for your business today

Anya Bida
Assistant Director of R&D
Texas Rangers Baseball Club
July 27, 2023

Table of Contents

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.

Group 6777.png

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.

Deploy using software best practices

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!

Orchestrate pipelines and models

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.

Ready to give Prophecy a try?

You can create a free account and get full access to all features for 21 days. No credit card needed. Want more of a guided experience? Request a demo and we’ll walk you through how Prophecy can empower your entire data team with low-code ETL today.

Ready to give Prophecy a try?

You can create a free account and get full access to all features for 14 days. No credit card needed. Want more of a guided experience? Request a demo and we’ll walk you through how Prophecy can empower your entire data team with low-code ETL today.

Get started with the Low-code Data Transformation Platform

Meet with us at Gartner Data & Analytics Summit in Orlando March 11-13th. Schedule a live 1:1 demo at booth #600 with our team of low-code experts. Request a demo here.

Related content

PRODUCT

A generative AI platform for private enterprise data

LıVE WEBINAR

Introducing Prophecy Generative AI Platform and Data Copilot

Ready to start a free trial?

Visually built pipelines turn into 100% open-source Spark code (python or scala) → NO vendor lock-in
Seamless integration with Databricks
Git integration, testing and CI/CD
Available on AWS, Azure, and GCP
Try it Free

Lastest blog posts

Events

Data Intelligence and AI Copilots at the Databricks World Tour

Matt Turner
October 29, 2024
October 29, 2024
October 29, 2024
Events

Success With AI Takes Data, Big Data!

Matt Turner
October 7, 2024
October 7, 2024
October 7, 2024
ETL modernization

Weigh Your Options As You Move Off Alteryx

Raj Bains
November 18, 2024
November 18, 2024
November 18, 2024