# Snowflake to Kafka migrations


## How to create a migration

- Put your migration files under `/kafka-db-deploy/snowflake-kafka/build/changelog/{dml,ddl}` directory
- Only XML format is supported for migrations
- No special rollback sections/tags. To rollback a migration you will have 
to create a new migration with rollback queries manually.
- The changeset id must be unique across all changesets. Follow best practices below to ensure this.

## Best Practices

- Only one file per PR.
- Changeset `id` must match the filename ('.xml' excluded)
- Append changeset sequence number to the `id`

## Migration example

Example: [DS-5334-test-snowflake-to-kafka-migration.xml](https://github.com/theorchard/database/pull/13867/files)

```xml
<?xml version="1.0" encoding="UTF-8"?>
<changelog xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
 xsi:noNamespaceSchemaLocation="file:/var/app/InputFileSchema.xsd">
  <changeset id="DS-5334_test_snowflake_to_kafka_migration:1"
    author="ibolshakov"
    run-always="true"
    topic="test.snowflakeToKafka.migration"
    kafka-cluster-name="wrong-test-cluster-xyz">
        <precondition>
            <sqlquery>
              SELECT value FROM lateral flatten(input=>[1,2,3,4,5,'hello world']);
            </sqlquery>
        </precondition>
    </changeset>
</changelog>
```


## XML Fields and Attributes

The SQL inside `<sqlquery>` tag of `<precondition>` will be executed in Snowflake.  

Topic name defined in `topic` attribute must be pre-terraformed otherwise deploy job will fail.

- `id`: String (required)
- `author`: String (required)
- `run-on-change`: Boolean (optional)
- `run-on-change`: Boolean (optional)
- `run-always`: Boolean (optional)
- `kafka-cluster-name`: String (optional, default: `managed-kafka-cdc-destination`)
- `topic`: String (required)