Astera introduces the newest addition to the platform, Astera Data Services - a complete API lifecycle management solution. Learn More


Deliver Schema Changes to your Data Warehouse with the Forward Engineering Feature

By |2022-10-19T14:50:51+00:00July 28th, 2021|

In today’s data-driven world, data warehouses are essential for powering analytics projects. Key data warehousing techniques such as dimensional modeling play a critical role in ensuring that trusted data is available in a format consistent with your business needs.

Astera DW Builder is an end-to-end data warehouse automation platform that accelerates the development process of building your centralized repository, from months and weeks to days. It provides a feature-rich data modeler that supports dimensional data modeling and speeds up building the underlying data warehouse structure exponentially based on business reporting and analytics needs.

After you have created your dimensional model, it needs to be deployed on your destination database. Astera DW Builder offers a robust feature called Forward Engineering that simplifies this step considerably.

Let’s take a look at the purpose of this feature and the value it adds to the overall data warehouse development process.

What is Forward Engineering in Astera DW Builder?

Forward Engineering is a useful feature that allows you to transform a logical data model into a physical data model by generating the database schema.  The generated schema is then used to create a new database or alter an existing one. In simple words, it allows you to “replicate” the database schema or changes made to it onto your desired destination.

The Forward Engineering option enables you to synchronize the physical changes you have made to the data model, such as adding or removing entities, indexes, attributes, and relationships with the database. However, this option doesn’t reflect any logical changes, like entity type, SCD field type, etc., as Forward Engineering is only concerned with the database-specific implementation of the data model.

Why Do You Need Forward Engineering?

Forward engineering in Data warehouse

Forward Engineering in Data Warehouse

Build your Destination Database Schema Easily

After creating your data models, you will have to replicate the physical schema on the destination warehouse before the data is loaded into them.

Forward Engineering saves users from the intricacies involved in manually recreating the dimensional model in the target database, which is generally a resource-intensive task. The product simplifies this step considerably and reduces the work to just a few clicks, allowing your team to work with a functional database schema in a matter of minutes.

Update your Data Warehouse Quickly

Reporting and analytics requirements continually change as the business operations evolve with time. You might need to add or remove dimension and fact entities or modify fields and their attributes in the data model. Implementing all such changes in your destination database requires manual work and can take days before the new information is available to the data consumers.

With Forward Engineering, you can quickly modify your data models and synchronize them with the physical database to ensure that your analysts and business users can build reports with up-to-date information.

How Forward Engineering Works in Astera DW Builder

Forward Engineering enables you to create a DDL script (SQL script) of your data model or apply changes made in the data model directly to your destination database. All you have to do is click on the Generate DDL Script button, follow a few steps, and it will take care of the rest.

You can use this feature in several ways. You can:

  • Generate a DDL script to create a new database
  • Export a differential script, which contains the changes made at the data model level to alter an existing database, and run it manually
  • Apply changes made to the database schema directly using the Apply Schema Diff option that runs the script in the database. The last option identifies the changes made in the data model, which are not part of your already deployed physical schema, and applies them to the destination database implicitly.

The following steps highlight how you can use the Forward Engineering feature in Astera DW Builder after you are done building the dimensional data model:

Step #1:

The Generate DDL Script option is found in the data model toolbar and in the entities’ context menu.

Generate DDL Script option for forward engineering

Step #2:

Clicking on this command opens up a dialogue box in which the user is required to choose the directory path and script name.

Choose the directory path and script name when selecting forward engineering

Step #3:

Click on save. The saved file will appear in the output window. This DDL Script is then executed in the new or existing database.

forward engineering in database

That’s it. Your changes have been deployed to your destination database.

Verifying your Data Model for Forward Engineering

Verifying your Data Model for Forward Engineering

Before you deploy the dimensional data model to the destination warehouse, it is recommended to double-check it for warnings and errors, which can be problematic in the subsequent processes.

To ensure this,  Astera DW Builder runs hundreds of verification checks before generating the DDL script through the built-in data model verification module. It checks the model for potential warnings or errors specific to the entities, relationships, indexes, or constraints. You can easily fix all the errors and verify your model again, ensuring that the forward engineered schema and changes being applied are correct and comply with the database rules.


As you can see, Forward Engineering is instrumental to building the underlying framework of your central repository. It is a robust feature that accelerates the process of creating and deploying the physical schema of your enterprise data warehouse while providing confidence about the high quality of your implementation.

Want to experience how Forward Engineering can help streamline your data warehousing project? Request a personalized demo today with our team.

Related Articles

Data Model Verification to Improve the Quality of Your Data...

What does it take to ensure the quality and robustness of your data warehouse implementation? An in-depth data model verification...
read more

Uses of Building an SCD Data Warehouse in Astera DW...

An SCD Data Warehouse is the crux to tracking changes in data. Change is ever-present in every business. From an...
read more