Skip to main content

Forward Only Deployment Approach

A forward-only deployment approach (migration plan) is recommended to work with Synapse, and it is how BimlFlex updates the generated data solution artifacts on the target Synapse platform.

This approach means that changes in the structures are applied as new versions / structures in the target platform.

Why Forward-Only?​

Azure Synapse Analytics does not support all ALTER TABLE operations that are available in SQL Server. For example, Synapse does not allow changing a column's data type in place, adding columns with certain constraints, or modifying distribution keys. Because of these limitations, BimlFlex uses a forward-only strategy that avoids ALTER statements entirely.

What This Means in Practice​

When you make a schema change in BimlFlex metadata (such as adding a column, changing a data type, or renaming a table), BimlFlex generates:

  1. A CREATE TABLE statement for the new version of the table with the updated schema
  2. An INSERT...SELECT statement that migrates data from the old table to the new one
  3. A RENAME operation that swaps the old and new tables

This ensures compatibility with Synapse's DDL limitations while preserving your data.

How to Handle Schema Changes​

  1. Make the metadata change in the BimlFlex App (e.g., add a column in the Column Editor, change a data type)
  2. Rebuild the project in BimlStudio to regenerate DDL scripts
  3. Review the generated migration scripts before deploying — they will include CREATE, INSERT...SELECT, and RENAME operations
  4. Deploy the DDL scripts to your Synapse environment using the generated deployment scripts or SSDT
  5. Verify that data has been migrated correctly by comparing row counts and spot-checking key records

When Manual Intervention Is Needed​

The forward-only approach handles most common schema changes automatically. However, some changes require manual attention:

  • Dropping columns: BimlFlex will not automatically remove data — review the migration to ensure unwanted columns are excluded
  • Changing distribution keys: Synapse distribution changes require recreating the table with a new distribution strategy. Review the generated DDL to confirm the correct distribution
  • Changing primary keys or indexes: These may require dropping and recreating dependent objects. Test in a non-production environment first
  • Large table migrations: For very large tables, the INSERT...SELECT may take significant time. Consider scheduling these changes during maintenance windows

For the full Synapse implementation guide, see Implementing BimlFlex for Synapse.