AI code conversion with source-system verification¶

Overview of AI code conversion with source-system verification¶

AI code conversion with source-system verification currently requires an instance of SQL Server hosted in Snowpark Container Services (SPCS). This SQL Server instance acts as a host for the test cases that will be executed by the source-system verification process. The test results from this instance will be used as a baseline to compare against the test results generated by executing the test cases on the converted Snowflake SQL code.

Prerequisites for AI code conversion with source-system verification¶

• An instance of SQL Server should be running in your SPCS environment. Download and run this shell script to deploy an instance of SQL Server in the SPCS environment.

• Understand and agree to the legal responsibilities of using AI code conversion with source-system verification on your source data platform code inside the SPCS environment.

• Create a custom specification file (for example, spec.yaml). This file contains the connection parameters for the SQL Server instance running inside SPCS.

  Sample for spec.yaml

  version: 1
  mode: TWO_SIDED
  n_tests: 5
  repair: true
  num_workers: 4
  source_test_database:
    connection_params:
      hostname: "mssql-server-demo-service.n4yw.svc.spcs.internal"
      port: 1433
      username: "sa"
      password: "StrongPassw0rd"
    connection_metadata:
      type: "SPCS"
      spcs_service:
        name: "MSSQL_SERVER_DEMO_SERVICE"
        database: "SNOWCONVERT_AI"
        schema: "PUBLIC"
  project:
    custom_instructions:
      - "Use MERGE instead of INSERT for upserts"
      - "Preserve original column aliases"
    exclude: "staging_*"
    extra_target_prerequisites: "CREATE SCHEMA IF NOT EXISTS analytics;"
    path_replacements:
      "/old/source/path": "/new/source/path"
      "/legacy/scripts": "/migrated/scripts"
    verified_objects:
      - "DBO.CUSTOMERS"
      - "DBO.ORDERS"
    use_custom_database: true
    extra_file_dependencies:
      "main_procedure.sql":
        - "helper_functions.sql"
        - "common_types.sql"
      "etl_load.sql":
        - "staging_tables.sql"
  # Arbitrary CLI flags — passed through directly without code changes
  additional_options:
    --some-new-flag: 42
    --experimental.feature: true
  

  Copy

  • The mode “TWO_SIDED” indicates that the AI code conversion with source-system verification process will run on both source database code and target database code.

  • The host name, port number, and credentials for the SQL Server database running in SPCS is specified under source\_test\_database.

  • The name of the container service, database name and the schema name where the test cases for the source will be executed is specified under connection\_metadata.

  Project options

  The project section configures how the conversion and verification process handles your source objects. The following options are available:

  Option	CLI flag	Description
  custom_instructions	--project.custom-instructions	A list of custom instructions that guide the AI conversion process.
  exclude	--project.exclude	A glob pattern to exclude specific objects from the conversion.
  extra_target_prerequisites	--project.extra-target-prerequisites	SQL statements to run on the target database before verification.
  path_replacements	--project.path-replacements	A dictionary that maps old file paths to new file paths.
  verified_objects	--project.verified-objects	A list of fully qualified object names to include in verification.
  use_custom_database	--project.use-custom-database	A boolean flag that enables the use of a custom database for the conversion.
  extra_file_dependencies	--project.extra-file-dependencies	A dictionary that maps a filename to a list of dependency filenames that it requires.

  Additional options

  The additional_options section accepts arbitrary key-value pairs that are passed directly to the CLI as flags. This means new CLI flags can be used immediately through the spec file without requiring code changes.

  For example, the following spec entries:

  additional_options:
    --some-new-flag: 42
    --experimental.feature: true
  

  Copy

  Are passed through to the API as:

  {
    "--some-new-flag": 42,
    "--experimental.feature": ""
  }
  

  Copy

  Boolean values set to true are passed as flag-only options (empty string value). Non-boolean values are passed with their specified value.

  How the YAML maps to the API payload

  The project options and additional options from the YAML spec are converted into a flat dictionary of CLI flags in the API payload. The following example shows how the full spec maps:

  {
    "--project.custom-instructions": "'[\"Use MERGE instead of INSERT for upserts\",\"Preserve original column aliases\"]'",
    "--project.exclude": "'\"staging_*\"'",
    "--project.extra-target-prerequisites": "'\"CREATE SCHEMA IF NOT EXISTS analytics;\"'",
    "--project.path-replacements": "'{\"//old/source/path\":\"//new/source/path\",\"//legacy/scripts\":\"//migrated/scripts\"}'",
    "--project.verified-objects": "'[\"DBO.CUSTOMERS\",\"DBO.ORDERS\"]'",
    "--project.use-custom-database": "",
    "--project.extra-file-dependencies": "'{\"main_procedure.sql\":[\"helper_functions.sql\",\"common_types.sql\"],\"etl_load.sql\":[\"staging_tables.sql\"]}'",
    "--n-tests": 5,
    "--some-new-flag": 42,
    "--experimental.feature": ""
  }
  

  Copy

  Note the following mapping behaviors:

  • Project options are prefixed with --project. and use kebab-case (for example, use_custom_database becomes --project.use-custom-database).

  • List values are serialized as JSON arrays.

  • Dictionary values are serialized as JSON objects.

  • Boolean flags (such as use_custom_database: true) are passed with an empty string value, indicating a flag-only option.

  • Top-level spec fields like n_tests are mapped as --n-tests.

  • Entries in additional_options are passed through as-is.

Steps to run AI code conversion with source-system verification¶

1. From the AI code conversion page, connect to Snowflake using a valid connection string. SnowConvert currently supports programmatic access tokens, in addition to the standard authentication methods for AI verification.

   

2. Accept the disclaimers and confirm your account identifier. Select Continue to proceed to the Select objects to verify with AI.

   

3. Select Upload custom instructions on the top left of the Select objects page.

4. Select the latest version of the yaml file you created as a prerequisite and select Save.

   

   You have now configured the AI code conversion process to run in two-sided mode. This will compare test results from the source SQL Server database to the converted Snowflake SQL code.

5. Proceed with the AI code conversion steps to complete the source-system verification process.

Impact on costs and dependencies¶

The costs for running AI code conversion with source-system verification may be different from the default AI code conversion, depending on your Snowflake credits consumption for SPCS and Cortex AI. A summary of the estimated costs and number of objects affected by the source-system verification process can be found in the Selection Summary. We recommend reviewing the Selection Summary before proceeding to AI convert.