Skip to main content

Data modeling

SQL

Data models are transformations that are compiled into SQL code and executed on SQL warehouses. Use the model canvas to visually develop your models, and switch to the code view to see your transformations in SQL.

CodeEqualsVisual

Project editor​

Open any SQL project in the project editor. Here, you will find the complete end-to-end project lineage. The project lineage provides a quick, high-level understanding of how models refer to other models, seeds, or sources. You'll also be able to browse different project components and view data from your SQL environment.

ProjectLineage

Model canvas​

The model canvas is your main workspace to develop data models. To begin, you can create a new model by clicking on + next to the models pane.

AddModel

Once a model is open, the model-editing canvas appears. In the following image, we can see the customers model starts with three existing models. The data is transformed according to Aggregate, SQLStatement, and Join gems. The transformation gems are accessible via the gem drawer. As you develop the model, you can iteratively run and see sample data as well as the relevant logs.

Canvas

Code view​

Switch to the Code View to reveal the SQL queries generated from the visual model canvas. Each gem is represented by a CTE or subquery.

Code View

You may wish to edit the code view. Add a SQL statement in the code view and notice the visual editor displays the updated code.

Version control​

Projects built through Prophecy are stored in the dbt Core format as repositories on Git, which allows data teams to follow best software engineering practices like CI/CD.

Prophecy automatically saves your work as you develop in a working directory securely stored on the cloud. Just make sure to commit your changes every once in a while, to see your code reflected on your Git and to collaborate easier with your team.

SQL and dbt constructs​

Even if dbt and SQL constructs are not supported in the visual editor, it is still supported in code. In other words:

  • For SQL, Prophecy automatically maps unsupported SQL statements to a generic SQL gem, allowing you to still freely edit even unsupported code.
  • For dbt, Features in dbt-core that may lack their visual-editor alternatives still work as expected from the standard dbt cli. Prophecy will never modify your existing codebase in unsupported ways.

Share models​

If you want, you can import an existing dbt Core project or start from scratch. Then, you can publish projects to the Package Hub and share your models with other teams.

Learn more​

A word from Prophecy's co-Founder, Maciej! See how Prophecy allows every team, whether visual or code developers, to use the same software development best practices.

What's next​

To get started developing SQL models, check out the pages below or try this modeling tutorial.