beVault 3.12 - Migrate your model to uppercase entities

1. Context

beVault 3.12 introduces two new environment variables:

DFAKTO_METAVAULT_CONVENTIONS__AllowMixedCaseStagingTables

Default: false

When set to true, removes the enforcement of lowercasing staging table names, allowing mixed-case or fully uppercase staging table names.

DFAKTO_METAVAULT_CONVENTIONS__DefaultNamingConventions

Controls the casing of all auto-generated database object names (schemas, prefixes, suffixes, technical columns). The naming convention is applied instance-wide — it affects all projects in your beVault instance.

The standard guidelines for Snowflake and IBM Db2 recommend using UPPERCASE for table and column names. beVault 3.12 introduces the DFaktoUpper convention to fully align all auto-generated objects with this standard.

Note: The technical name of an entity is always used as-is for table generation — it is not affected by the naming convention alone. To uppercase entity technical names as well, you must run the uppercase_bevault.py migration script described below.

Naming convention examples

Behaviour when changing the naming convention

beVault does not automatically convert already-deployed database objects when you change the environment variable. When you change the convention and redeploy:

• beVault will not detect the previously deployed tables.

• beVault will create new schemas and tables under the new naming convention, leaving the old ones in place.

Example: Starting with DFakto convention and a Hub named product (row A), switching to DFaktoUpper and redeploying will create a new, empty table DV.H_product alongside the existing dv.h_product. You must manually migrate the data from the old tables to the new ones.

In addition, parts of your project that contain custom SQL code — such as staging table views, data quality controls, and Information Mart scripts — also require manual migration and are not automatically handled by beVault's deployment engine.

2. Warning

Because this manual migration can break your project if performed incorrectly, we strongly recommend making a full backup before starting.

To back up your beVault project:

1. In beVault, navigate to your project.

2. Open the Import/Export module.

3. Export your project and save the archive in a safe location.

To back up your target database:
Use your database's native backup tooling (e.g., BACKUP DATABASE in IBM Db2 or the Snowflake Time Travel / database cloning feature in Snowflake) before running any migration statements.

3. Migration steps

This guide describes how to migrate from the default naming convention (DFakto) to the full uppercase convention (DFaktoUpper).

Prerequisites

• beVault is already on version 3.12 or later.

• Python 3 is installed on the machine where you run the migration script.

• A full backup of your beVault project and target database has been made

Step-by-step

Step 1 — Export your beVault project

In beVault, go to the Import/Export module of your project and export it as a .zip archive.

Step 2 — Unzip the archive

Extract the archive to a local folder and navigate into the project root (the folder containing Staging/ and DataVault/ subdirectories).

Step 3 — Run the uppercase_bevault.py migration script

⬇️ Download: uppercase_bevault.py

python uppercase_bevault.py --root /path/to/your/unzipped/project

This script automatically updates the custom SQL code and metadata within your beVault project export to be compatible with the DFaktoUpper naming convention. It performs the following changes:

You can run only a subset of the migration using the --only flag:

# Migrate only staging tables
python uppercase_bevault.py --root /path/to/project --only staging

# Migrate only Data Vault entities
python uppercase_bevault.py --root /path/to/project --only datavault

# Migrate only Information Mart scripts
python uppercase_bevault.py --root /path/to/project --only im

Step 4 — Re-zip the modified project

Create a new .zip archive from the modified project folder.

Step 5 — Import the modified project into beVault

In beVault, go to the Import/Export module of your project and import the new archive.

Step 6 — Update the environment variables on your beVault server

Add or update the following environment variables on the server where beVault is installed:

DFAKTO_METAVAULT_CONVENTIONS__DefaultNamingConventions=DFaktoUpper
DFAKTO_METAVAULT_CONVENTIONS__AllowMixedCaseStagingTables=true

Step 7 — Restart beVault

Step 8 — Deploy a new version of your project

In the beVault Deploy module, create a new version and deploy it. beVault will generate the new schemas and tables using the DFaktoUpper naming convention.

ℹ️ The newly created tables will already contain ghost records (placeholder rows inserted automatically by beVault during deployment). You must account for these in the next step.

Step 9 — Copy data from the old tables to the new tables

See Section 4 for database-specific instructions.

4. Copy Data to New Tables

After the new deployment, beVault will have created new uppercase tables (e.g., DV.H_PRODUCT) alongside the original lowercase ones (e.g., "dv"."h_product"). The new tables already contain ghost records inserted during the deploy.

Strategy

The recommended approach for each table is:

1. Truncate the new uppercase table to remove the ghost records.

2. Insert all rows from the old lowercase table into the new table using SELECT *.

⚠️ Perform these operations during a maintenance window, or when no active data loads are running, to avoid data consistency issues.

ℹ️ Views (staging mapping views, Information Mart views) do not need to be migrated manually — they are automatically re-generated by beVault during deployment with the correct uppercase references.

4.1 IBM Db2

In IBM Db2, table and schema names created with quoted identifiers (as beVault does) are case-sensitive. The old lowercase tables must therefore be referenced using double-quoted identifiers.

The TRUNCATE TABLE statement in IBM Db2 requires the IMMEDIATE keyword to take effect within the current transaction.

Generate all migration statements automatically

For projects with many entities, use the following query against the Db2 system catalog (SYSCAT.TABLES) to generate the complete set of migration statements in one pass. Copy the output and execute it sequentially.

-- Run this query to generate TRUNCATE + INSERT statements for all table pairs.
-- It identifies tables whose schema/name differs only by case (old lowercase → new UPPERCASE).
SELECT
    'TRUNCATE TABLE '
        || RTRIM(new_t.tabschema) || '.' || RTRIM(new_t.tabname)
        || ' IMMEDIATE;'                                          || CHR(10)
    || 'INSERT INTO '
        || RTRIM(new_t.tabschema) || '.' || RTRIM(new_t.tabname) || CHR(10)
    || 'SELECT * FROM "'
        || RTRIM(old_t.tabschema) || '"."' || RTRIM(old_t.tabname) || '";'
    AS migration_sql
FROM syscat.tables old_t
JOIN syscat.tables new_t
    ON  UPPER(RTRIM(old_t.tabschema)) = RTRIM(new_t.tabschema)
    AND UPPER(RTRIM(old_t.tabname))   = RTRIM(new_t.tabname)
    AND RTRIM(old_t.tabschema)       <> RTRIM(new_t.tabschema)   -- different case → different objects
WHERE UPPER(RTRIM(old_t.tabschema)) IN ('DV', 'STG', 'BK', 'IM', 'META', 'REF', 'SRC', 'BV')
  AND old_t.type = 'T'    -- base tables only (excludes views)
ORDER BY new_t.tabschema, new_t.tabname;

4.2 Snowflake

In Snowflake, unquoted identifiers are case-insensitive and are stored in uppercase by default. However, beVault creates tables using double-quoted identifiers to preserve the exact original casing. As a result, "dv"."h_product" (quoted lowercase) and DV.H_PRODUCT (unquoted uppercase) are treated as two distinct objects, which is why they coexist after the deployment.

When referencing the old lowercase tables, always use double-quoted identifiers.

Generate all migration statements automatically

Use the query below against Snowflake's INFORMATION_SCHEMA.TABLES to generate the complete migration script for all tables at once. Copy the output and execute it sequentially.

-- Run this query to generate TRUNCATE + INSERT statements for all table pairs.
-- It identifies tables whose schema/name differs only by case (old lowercase → new UPPERCASE).
SELECT
    'TRUNCATE TABLE '
        || new_t.table_schema || '.' || new_t.table_name || ';'  || CHR(10)
    || 'INSERT INTO '
        || new_t.table_schema || '.' || new_t.table_name          || CHR(10)
    || 'SELECT * FROM "'
        || old_t.table_schema || '"."' || old_t.table_name || '";'
    AS migration_sql
FROM information_schema.tables old_t
JOIN information_schema.tables new_t
    ON  UPPER(old_t.table_schema) = new_t.table_schema
    AND UPPER(old_t.table_name)   = new_t.table_name
    AND old_t.table_schema       <> new_t.table_schema   -- different case → different objects
WHERE UPPER(old_t.table_schema) IN ('DV', 'STG', 'BK', 'IM', 'META', 'REF', 'SRC', 'BV')
  AND old_t.table_type = 'BASE TABLE'    -- base tables only (excludes views)
ORDER BY new_t.table_schema, new_t.table_name;

Post-migration validation and cleanup

Once all data has been copied, we recommend the following checks before removing the old tables:

1. Verify row counts — For each migrated table pair, confirm that the new table contains the same number of rows as the old one:

   SQL

   -- Example for a Hub (adapt schema/table names as needed)
   SELECT 'old' AS source, COUNT(*) FROM "dv"."h_product"
   UNION ALL
   SELECT 'new' AS source, COUNT(*) FROM DV.H_PRODUCT;
   

2. Run a full pipeline test — Execute a complete data load and verify that Information Marts produce the expected output.

3. Drop old schemas and tables — Only after confirming the migration is successful, drop the old lowercase schemas and their contents.