Snowflake

https://docs.snowflake.com
Admin
Snowflake documentation
Tokens:3,354,348
Snippets:26,753
Trust Score:9.9
Update:3 days ago
Show doc for...
Tokens:
Raw
### Setup data for cursor examples

Source: https://docs.snowflake.com/en/migrations/aim-for-datawarehouses/code-conversion/translation-references/redshift/rs-sql-statements-create-procedure

Setup data for the cursor examples: creates a table and inserts a row.

```sql
 CREATE TABLE cursor_example
(
	col1 INTEGER,
	col2 VARCHAR(20)
);

INSERT INTO cursor_example VALUES (10, 'hello');

```

--------------------------------

### scai license install example

Source: https://docs.snowflake.com/en/migrations/aim-for-datawarehouses/manual-migration/SCAI_Command_Reference

Example of installing a license file from a specified path.

```bash
# Install license
scai license install --path /path/to/license.lic

```

--------------------------------

### Setup data for SIMILAR TO examples

Source: https://docs.snowflake.com/en/migrations/aim-for-datawarehouses/code-conversion/translation-references/redshift/redshift-conditions

Setup data for the SIMILAR TO examples. Creates a table and inserts sample rows used in the subsequent pattern-matching queries.

```sql
CREATE TABLE similar_table_ex (
    column_name VARCHAR(255)
);

INSERT INTO similar_table_ex (column_name)
VALUES
    ('abc_123'),
    ('a_cdef'),
    ('bxyz'),
    ('abcc'),
    ('start_hello'),
    ('apple'),
    ('banana'),
    ('xyzabc'),
    ('abc\ncccc'),
    ('\nabccc'),
    ('abc%def'),
    ('abc_xyz'),
    ('abc_1_xyz'),
    ('applepie'),
    ('start%_abc'),
    ('ab%_xyz'),
    ('abcs_123_xyz'),
    ('aabc123'),
    ('xyzxyz'),
    ('123abc\nanother line\nabc123');

```

--------------------------------

### scai data worker setup examples

Source: https://docs.snowflake.com/en/migrations/aim-for-datawarehouses/manual-migration/SCAI_Command_Reference

Shows two usage examples for the setup command: one with the default connection and one with a specific role.

```bash
# Set up DEW service with default connection
scai data worker setup --compute-pool MY_COMPUTE_POOL

# Set up with a specific role
scai data worker setup --compute-pool MY_COMPUTE_POOL --role MY_ROLE

```

--------------------------------

### Redshift Setup Data for UPDATE Examples

Source: https://docs.snowflake.com/en/migrations/aim-for-datawarehouses/code-conversion/translation-references/redshift/redshift-sql-statements

Setup data for the UPDATE examples, creating the employees and department_bonus tables and inserting sample data. This is used as the base for the subsequent examples.

```sql

 CREATE TABLE employees (
    id INTEGER IDENTITY(1,1),
    name VARCHAR(100),
    salary DECIMAL DEFAULT 20000,
    department VARCHAR(50) DEFAULT 'Marketing'
);

INSERT INTO employees (name, salary, department)
VALUES
    ('Alice', 500000, 'HR'),
    ('Bob', 600000, 'Engineering'),
    ('Charlie', 700000, 'Engineering'),
    ('David', 400000, 'Marketing'),
    ('Eve', 450000, 'HR'),
    ('Frank', 750000, 'Engineering'),
    ('Grace', 650000, 'Engineering'),
    ('Helen', 390000, 'Marketing'),
    ('Ivy', 480000, 'HR'),
    ('Jack', 420000, 'Engineering'),
    ('Ken', 700000, 'Marketing'),
    ('Liam', 600000, 'Engineering'),
    ('Mona', 470000, 'HR');

CREATE TABLE department_bonus (
    department VARCHAR(100),
    bonus DECIMAL
);

INSERT INTO department_bonus (department, bonus)
VALUES
    ('HR', 10000),
    ('Engineering', 50000),
    ('Marketing', 20000),
    ('Sales', 5000);



```

--------------------------------

### scai versions examples

Source: https://docs.snowflake.com/en/migrations/aim-for-datawarehouses/manual-migration/SCAI_Command_Reference

Examples of listing installed versions and getting machine-readable JSON output.

```bash
# Inspect installed copies next to channel configuration
scai versions

# Machine-readable inventory for scripts or agents
scai versions --json
```

--------------------------------

### Teradata setup data for PIVOT example

Source: https://docs.snowflake.com/en/migrations/aim-for-datawarehouses/code-conversion/translation-references/teradata/sql-translation-reference/teradata-built-in-functions

Teradata setup data for the PIVOT example: creates the star1 table and inserts sample rows.

```sql
 CREATE TABLE star1(
	country VARCHAR(20),
	state VARCHAR(10),
	yr INTEGER,
	qtr VARCHAR(3),
	sales INTEGER,
	cogs INTEGER
);

insert into star1 values ('USA', 'CA', 2001, 'Q1', 30, 15);
insert into star1 values ('Canada', 'ON', 2001, 'Q2', 10, 0);
insert into star1 values ('Canada', 'BC', 2001, 'Q3', 10, 0);
insert into star1 values ('USA', 'NY', 2001, 'Q1', 45, 25);
insert into star1 values ('USA', 'CA', 2001, 'Q2', 50, 20);
```

--------------------------------

### scai data migrate start - examples

Source: https://docs.snowflake.com/en/migrations/aim-for-datawarehouses/manual-migration/SCAI_Command_Reference

Shows how to start a migration with default config or with a custom config file. The first example uses the default config, the second specifies a custom YAML file.

```bash
# Start migration with default config
scai data migrate start --connection my-snowflake

# Start migration with custom config
scai data migrate start --config my-data-migration-config.yaml --connection my-snowflake
```

--------------------------------

### scai data orchestrator setup examples

Source: https://docs.snowflake.com/en/migrations/aim-for-datawarehouses/manual-migration/SCAI_Command_Reference

Shows usage examples for the scai data orchestrator setup command, including default connection, specific connection, and warehouse override.

```bash
# Set up with default connection
scai data orchestrator setup --compute-pool MY_COMPUTE_POOL

# Set up with specific connection
scai data orchestrator setup --compute-pool MY_COMPUTE_POOL --connection my-snowflake

# Set up with a specific warehouse
scai data orchestrator setup --compute-pool MY_COMPUTE_POOL --warehouse MY_WAREHOUSE

```

--------------------------------

### Setup Data for DELETE Examples

Source: https://docs.snowflake.com/en/migrations/aim-for-datawarehouses/code-conversion/translation-references/redshift/redshift-sql-statements

Creates the employees and departments tables and inserts sample data used in the DELETE examples. This setup is required for the subsequent DELETE operations.

```sql
 CREATE TABLE employees (
    id INT PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    department VARCHAR(255),
    manager_id INT REFERENCES employees(id)
);

INSERT INTO employees (id, name, department, manager_id) VALUES
(1, 'Alice', 'Sales', 2),
(2, 'Bob', 'Sales', 1),
(3, 'Charlie', 'Sales', 1),
(4, 'David', 'Marketing', 2),
(5, 'Eve', 'Marketing', 4),
(6, 'Frank', 'Marketing', 4),
(7, 'Grace', 'Engineering', 6),
(8, 'Helen', 'Engineering', 7),
(9, 'Ivy', 'Engineering', 7),
(10, 'John', 'Sales', 3),
(11, 'Joe', 'Engineering', 5);


CREATE TABLE departments (
    department_name VARCHAR(255)
);

INSERT INTO departments (department_name) VALUES
('Sales'),
('Marketing'),
('Engineering');

```

--------------------------------

### Setup data for LIKE examples

Source: https://docs.snowflake.com/en/migrations/aim-for-datawarehouses/code-conversion/translation-references/redshift/redshift-conditions

Creates the like_ex table and populates it with sample data used by all LIKE examples on this page.

```sql
CREATE TABLE like_ex(name VARCHAR(20));

INSERT INTO like_ex VALUES
  ('John  Dddoe'),
  ('Joe   Doe'),
  ('Joe   Doe '),
  (' Joe   Doe '),
  (' Joe \n Doe '),
  ('John_down'),
  ('Joe down'),
  ('Elaine'),
  (''),
  (null),
  ('1000 times'),
  ('100%');
```

--------------------------------

### Redshift: Setup data for UPDATE example

Source: https://docs.snowflake.com/en/migrations/aim-for-datawarehouses/code-conversion/translation-references/redshift/redshift-sql-statements

Creates target and source tables and inserts sample data for the UPDATE example. This setup is required before running the UPDATE query.

```sql
 CREATE TABLE target (
  k INT,
  v INT
);

CREATE TABLE src (
  k INT,
  v INT
);

INSERT INTO target (k, v) VALUES (0, 10);

INSERT INTO src (k, v) VALUES
  (0, 14),
  (0, 15),
  (0, 16);

```

--------------------------------

### Redshift Setup Data for Cursor Examples

Source: https://docs.snowflake.com/en/migrations/aim-for-datawarehouses/code-conversion/translation-references/redshift/rs-sql-statements-create-procedure

Setup data for the cursor examples. Creates two tables and inserts a sample row. This is the Redshift version.

```sql
 CREATE TABLE cursor_example
(
	col1 INTEGER,
	col2 VARCHAR(20)
);

CREATE TABLE cursor_example_results
(
	col1 INTEGER,
	col2 VARCHAR(20)
);

INSERT INTO cursor_example VALUES (10, 'hello');

```

--------------------------------

### Snowflake SQL UDF Emulation Setup

Source: https://docs.snowflake.com/en/migrations/snowconvert-docs/general/technical-documentation/issues-and-troubleshooting/conversion-issues/sqlServerEWI

Example setup for a table in Snowflake SQL demonstrating the conversion of the SQL Server example.

```sql

 CREATE OR REPLACE TABLE PRODUCT (
       ProductID INTEGER,
       Name VARCHAR(20),
       Color VARCHAR(20))
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "transact",  "convertedOn": "07/12/2024" }}'
;

INSERT INTO PRODUCT (PRODUCTID, NAME, COLOR) VALUES(1,'UMBRELLA','RED');
INSERT INTO PRODUCT (PRODUCTID, NAME, COLOR) VALUES(2,'SHORTS','BLUE');
INSERT INTO PRODUCT (PRODUCTID, NAME, COLOR) VALUES(3,'BALL','YELLOW');

--** SSC-PRF-TS0001 - PERFORMANCE WARNING - RECURSION FOR CTE NOT CHECKED. MIGHT REQUIRE RECURSIVE KEYWORD **

WITH
     !!!RESOLVE EWI!!! /*** SSC-EWI-TS0043 - WITH XMLNAMESPACES IS NOT SUPPORTED IN SNOWFLAKE ***/!!! XMLNAMESPACES ('uri' as ns1)
SELECT
       ProductID AS "ns1:ProductID",
       Name AS "ns1:Name",
       Color AS "ns1:Color"
FROM
       Product
!!!RESOLVE EWI!!! /*** SSC-EWI-TS0044 - FOR XML RAW CLAUSE IS NOT SUPPORTED IN SNOWFLAKE ***/!!!
FOR XML RAW;


```

--------------------------------

### Redshift Setup Data for INSERT Examples

Source: https://docs.snowflake.com/en/migrations/aim-for-datawarehouses/code-conversion/translation-references/redshift/redshift-sql-statements

Creates the employees and new_employees tables and inserts sample data into new_employees. This setup is used by the subsequent INSERT examples.

```sql
 CREATE TABLE employees (
    id INTEGER IDENTITY(1,1),
    name VARCHAR(100),
    salary INT DEFAULT 20000,
    department VARCHAR(50) DEFAULT 'Marketing'
);

CREATE TABLE new_employees (
    name VARCHAR(100),
    salary INT,
    department VARCHAR(50)
);

INSERT INTO new_employees (name, salary, department)
VALUES
    ('Grace Lee', 32000, 'Operations'),
    ('Hannah Gray', 26000, 'Finance');

```

--------------------------------

### scai settings get examples

Source: https://docs.snowflake.com/en/migrations/aim-for-datawarehouses/manual-migration/SCAI_Command_Reference

Examples of reading the update channel and auto-update preference.

```bash
# Read the update channel
scai settings get channel

# Read auto-update preference
scai settings get autoUpdate
```

--------------------------------

### SQL Server setup tables

Source: https://docs.snowflake.com/en/migrations/aim-for-datawarehouses/code-conversion/translation-references/transact/transact-dmls

SQL Server setup data for the UPDATE with JOIN examples. Creates Orders, Customers, and Products tables.

```sql
CREATE TABLE Orders (
    OrderID INT PRIMARY KEY,
    CustomerID INT,
    ProductID INT,
    Quantity INT,
    OrderDate DATE
);

CREATE TABLE Customers (
    CustomerID INT PRIMARY KEY,
    CustomerName VARCHAR(100)
);

CREATE TABLE Products (
    ProductID INT PRIMARY KEY,
    ProductName VARCHAR(100),
    Price DECIMAL(10, 2)
);
```

--------------------------------

### SQL Server UDF Emulation Setup

Source: https://docs.snowflake.com/en/migrations/snowconvert-docs/general/technical-documentation/issues-and-troubleshooting/conversion-issues/sqlServerEWI

Example setup for a table in SQL Server to demonstrate potential UDF emulation strategies.

```sql

 CREATE  TABLE PRODUCT (ProductID INTEGER, Name VarChar(20), Color VarChar(20));
INSERT INTO PRODUCT(PRODUCTID, NAME, COLOR) VALUES(1,'UMBRELLA','RED');
INSERT INTO PRODUCT(PRODUCTID, NAME, COLOR) VALUES(2,'SHORTS','BLUE');
INSERT INTO PRODUCT(PRODUCTID, NAME, COLOR) VALUES(3,'BALL','YELLOW');

WITH XMLNAMESPACES ('uri' as ns1)  
SELECT ProductID as 'ns1:ProductID',  
       Name      as 'ns1:Name',  
       Color     as 'ns1:Color'  
FROM Product  
FOR XML RAW


```

--------------------------------

### scai data orchestrator start - usage examples

Source: https://docs.snowflake.com/en/migrations/aim-for-datawarehouses/manual-migration/SCAI_Command_Reference

Examples of starting the orchestrator: default cloud resume, with a specific role, and local foreground execution.

```bash
# Resume the cloud orchestrator
scai data orchestrator start

# Resume with a specific role
scai data orchestrator start --role MY_ROLE

# Run the orchestrator locally
scai data orchestrator start --local

```

--------------------------------

### scai data worker start examples

Source: https://docs.snowflake.com/en/migrations/aim-for-datawarehouses/manual-migration/SCAI_Command_Reference

Examples for the start command: resuming the cloud DEW service, resuming with a specific role, running a local worker with a config file, and generating a local config template.

```bash
# Resume cloud DEW service
scai data worker start

# Resume with a specific role
scai data worker start --role MY_ROLE

# Run local worker with config
scai data worker start --local my-agent-config.yaml

# Generate local config template
scai data worker start --local --auto-config

```

--------------------------------

### Setup inserts and alter for join examples

Source: https://docs.snowflake.com/en/migrations/aim-for-datawarehouses/code-conversion/translation-references/oracle/sql-queries-and-subqueries/joins

Setup statements executed before the join examples to distinguish results for each kind of JOIN. Inserts a new region and a country with a NULL region, and drops a foreign key constraint.

```sql
INSERT INTO hr.regions VALUES (5, 'Oceania');
ALTER TABLE hr.countries DROP CONSTRAINT countr_reg_fk;
INSERT INTO hr.countries VALUES ('--', 'Unknown Country', 0);

```

--------------------------------

### scai data validate start examples

Source: https://docs.snowflake.com/en/migrations/aim-for-datawarehouses/manual-migration/SCAI_Command_Reference

Examples of starting a data validation workflow with default or custom config. The first uses the default config, the second specifies a custom YAML file.

```bash
# Start validation with default config
scai data validate start --connection my-snowflake

# Start validation with custom config
scai data validate start --config my-data-validation-config.yaml --connection my-snowflake

```

--------------------------------

### Setup Table: Redshift

Source: https://docs.snowflake.com/en/migrations/aim-for-datawarehouses/code-conversion/translation-references/redshift/redshift-conditions

Creates the sales table and inserts sample data in Redshift. This setup is used for the BETWEEN condition examples.

```sql
 CREATE TABLE sales (
    id INTEGER IDENTITY(1,1),
    price FLOAT,
    departmentId INTEGER,
    saleDate DATE
);

INSERT INTO sales (price, departmentId, saleDate) VALUES
(5000, 1, '2008-01-01'),
(8000, 1, '2018-01-01'),
(5000, 2, '2010-01-01'),
(7000, 3, '2010-01-01'),
(5000, 1, '2018-01-01'),
(4000, 4, '2010-01-01'),
(3000, 4, '2018-01-01'),
(9000, 5, '2008-01-01'),
(7000, 5, '2018-01-01'),
(6000, 5, '2006-01-01'),
(5000, 5, '2008-01-01'),
(5000, 4, '2018-01-01'),
(8000, 3, '2006-01-01'),
(7000, 3, '2016-01-01'),
(2000, 2, '2018-01-01');

```

--------------------------------

### Teradata Setup Data

Source: https://docs.snowflake.com/en/migrations/aim-for-datawarehouses/code-conversion/translation-references/general/subqueries

Setup data for Teradata examples. Creates tableA and tableB with two columns each and inserts sample rows.

```sql
CREATE TABLE tableA
(
    col1 INTEGER,
    col2 VARCHAR(20)
);

CREATE TABLE tableB
(
    col3 INTEGER,
    col4 VARCHAR(20)
);

INSERT INTO tableA VALUES (50, 'Hey');
INSERT INTO tableA VALUES (20, 'Example');

INSERT INTO tableB VALUES (50, 'Hey');
INSERT INTO tableB VALUES (20, 'Bye');

```

--------------------------------

### Snowflake setup tables

Source: https://docs.snowflake.com/en/migrations/aim-for-datawarehouses/code-conversion/translation-references/transact/transact-dmls

Snowflake setup data for the UPDATE with JOIN examples. Creates Orders, Customers, and Products tables with COMMENT metadata.

```sql
CREATE OR REPLACE TABLE Orders (
    OrderID INT PRIMARY KEY,
    CustomerID INT,
    ProductID INT,
    Quantity INT,
    OrderDate DATE
)
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "transact",  "convertedOn": "11/12/2024",  "domain": "test" }}'
;

CREATE OR REPLACE TABLE Customers (
    CustomerID INT PRIMARY KEY,
    CustomerName VARCHAR(100)
)
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "transact",  "convertedOn": "11/12/2024",  "domain": "test" }}'
;

CREATE OR REPLACE TABLE Products (
    ProductID INT PRIMARY KEY,
    ProductName VARCHAR(100),
    Price DECIMAL(10, 2)
)
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "transact",  "convertedOn": "11/12/2024",  "domain": "test" }}'
;
```

--------------------------------

### Install data validation orchestrator and workers

Source: https://docs.snowflake.com/en/migrations/aim-for-datawarehouses/manual-migration/data-validation

Reuse the same orchestrator and workers as data migration. For SPCS, run setup commands with a compute pool and connection. For a local worker, use the start command with the generated config file.

```bash
scai data worker generate-config .scai/settings/DataExchangeWorkerConfig.toml

# SPCS (first time):
scai data orchestrator setup --compute-pool MY_COMPUTE_POOL --connection my-snowflake
scai data worker setup --compute-pool MY_COMPUTE_POOL --connection my-snowflake

# Local Worker:
scai data worker start --local .scai/settings/DataExchangeWorkerConfig.toml
```

--------------------------------

### Setup Data for Expression List Examples

Source: https://docs.snowflake.com/en/migrations/aim-for-datawarehouses/code-conversion/translation-references/redshift/redshift-expressions

Creates tables table1, table2, and table3 and inserts sample data used in the subsequent examples.

```sql
CREATE TABLE table1 (
    quantity VARCHAR(50),
    fruit VARCHAR(50)
);

CREATE TABLE table2 (
    quantity VARCHAR(50),
    fruit VARCHAR(50)
);

CREATE TABLE table3 (
    id INT,
    name VARCHAR(50),
    quantity INT,
    fruit VARCHAR(50),
    price INT
);

INSERT INTO table1 (quantity, fruit)
VALUES
    ('one', 'apple'),
    ('two', 'banana'),
    ('three', 'cherry');

INSERT INTO table2 (quantity, fruit)
VALUES
    ('one', 'apple'),
    ('two', 'banana'),
    ('four', 'orange');

INSERT INTO table3 (id, name, quantity, fruit, price)
VALUES
    (1, 'Alice', 1, 'apple', 100),
    (2, 'Bob', 5, 'banana', 200),
    (3, 'Charlie', 10, 'cherry', 300),
    (4, 'David', 15, 'orange', 400);

```

--------------------------------

### Setup data for SQL Server

Source: https://docs.snowflake.com/en/migrations/aim-for-datawarehouses/code-conversion/translation-references/transact/transact-dmls

Creates three generic tables and populates them with sample data for SQL Server. Use this setup before running the LEFT JOIN conversion examples.

```sql
 CREATE TABLE GenericTable1 (
    Col1 INT,
    Col2 VARCHAR(10),
    Col3 VARCHAR(10),
    Col4 VARCHAR(10),
    Col5 VARCHAR(10),
    Col6 VARCHAR(100)
);

CREATE TABLE GenericTable2 (
    Col1 VARCHAR(10),
    Col2 VARCHAR(10),
    Col3 VARCHAR(10),
    Col4 VARCHAR(10),
    Col5 VARCHAR(10)
);

CREATE TABLE GenericTable3 (
    Col1 VARCHAR(10),
    Col2 VARCHAR(100),
    Col3 CHAR(1)
);

INSERT INTO GenericTable1 (Col1, Col2, Col3, Col4, Col5, Col6)
VALUES
(1, 'A1', 'B1', 'C1', NULL, NULL),
(2, 'A2', 'B2', 'C2', NULL, NULL),
(3, 'A3', 'B3', 'C3', NULL, NULL);

INSERT INTO GenericTable2 (Col1, Col2, Col3, Col4, Col5)
VALUES
('1', 'A1', 'B1', 'C1', 'X1'),
('2', 'A2', 'B2', 'C2', 'X2'),
('3', 'A3', 'B3', 'C3', 'X3');

INSERT INTO GenericTable3 (Col1, Col2, Col3)
VALUES
('X1', 'Description1', 'A'),
('X2', 'Description2', 'A'),
('X3', 'Description3', 'A');

```

--------------------------------

### scai init usage examples

Source: https://docs.snowflake.com/en/migrations/aim-for-datawarehouses/manual-migration/SCAI_Command_Reference

Provides several examples of using the scai init command with different options and source languages.

```bash
# Create a project in a new folder (recommended)
scai init my-project -l Teradata

# Create a project in the current directory
scai init -l Teradata

# Create project with source code
scai init my-project -l Oracle -i /path/to/code

# Create project with pre-split source code (skip arrange phase)
scai init my-project -l SqlServer -i /path/to/code --code-already-split

# Create project with a specific connection
scai init my-project -l Oracle -c my-snowflake-conn

```

--------------------------------

### Snowflake setup data for PIVOT example

Source: https://docs.snowflake.com/en/migrations/aim-for-datawarehouses/code-conversion/translation-references/teradata/sql-translation-reference/teradata-built-in-functions

Snowflake setup data for the PIVOT example: creates the star1 table with a COMMENT attribute and inserts the same sample rows as the Teradata version.

```sql
 CREATE OR REPLACE TABLE star1 (
	country VARCHAR(20),
	state VARCHAR(10),
	yr INTEGER,
	qtr VARCHAR(3),
	sales INTEGER,
	cogs INTEGER
)
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"teradata"}}'
;

INSERT INTO star1
VALUES ('USA', 'CA', 2001, 'Q1', 30, 15);

INSERT INTO star1
VALUES ('Canada', 'ON', 2001, 'Q2', 10, 0);

INSERT INTO star1
VALUES ('Canada', 'BC', 2001, 'Q3', 10, 0);

INSERT INTO star1
VALUES ('USA', 'NY', 2001, 'Q1', 45, 25);

INSERT INTO star1
VALUES ('USA', 'CA', 2001, 'Q2', 50, 20);
```

--------------------------------

### Snowflake setup data for JSON_TABLE example

Source: https://docs.snowflake.com/en/migrations/aim-for-datawarehouses/code-conversion/translation-references/teradata/sql-translation-reference/teradata-built-in-functions

Snowflake setup data for the JSON_TABLE conversion example. Creates a table with a VARIANT column and inserts the same JSON document using PARSE_JSON and TO_JSON.

```sql
 CREATE OR REPLACE TABLE myJsonTable (
 col1 integer,
 col2 VARIANT
 )
 COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"teradata"}}'
;

INSERT INTO myJsonTable
VALUES (1, TO_JSON(PARSE_JSON('{
"name": "Matt",
"age" : 30,
"songs" : [
	{"name" : "Late night", "genre" : "Jazz"},
	{"name" : "Wake up", "genre" : "Rock"},
	{"name" : "Who am I", "genre" : "Rock"},
	{"name" : "Raining", "genre" : "Blues"}
]}')));

```

--------------------------------

### Teradata setup data for JSON_TABLE example

Source: https://docs.snowflake.com/en/migrations/aim-for-datawarehouses/code-conversion/translation-references/teradata/sql-translation-reference/teradata-built-in-functions

Teradata setup data for the JSON_TABLE conversion example. Creates a table with a JSON column and inserts a JSON document containing an array of songs.

```sql
 create table myJsonTable(
 col1 integer,
 col2 JSON(1000)
 );


insert into myJsonTable values(1,
new json('{
"name": "Matt",
"age" : 30,
"songs" : [
	{"name" : "Late night", "genre" : "Jazz"},
	{"name" : "Wake up", "genre" : "Rock"},
	{"name" : "Who am I", "genre" : "Rock"},
	{"name" : "Raining", "genre" : "Blues"}
]}'));

```

--------------------------------

### Databricks setup data for WHERE clause examples

Source: https://docs.snowflake.com/en/migrations/sma-docs/translation-reference/spark-sql/spark-sql-dml/select/where

Creates the person table and inserts sample data used by all WHERE clause examples on this page. Run this before executing the pattern queries.

```sql
CREATE TABLE person (id INT, name STRING, age INT);
INSERT INTO person VALUES
    (100, 'John',   30),
    (200, 'Mary', NULL),
    (300, 'Mike',   80),
    (400, 'Dan' ,   50);
```

--------------------------------

### SUBSTR_UDF usage example

Source: https://docs.snowflake.com/en/migrations/aim-for-datawarehouses/code-conversion/function-references/teradata

Shows a usage example of the SUBSTR_UDF function with various start positions and lengths, and the corresponding output. The output demonstrates the function's behavior with negative and zero start positions.

```sql
:force:
SELECT
    PUBLIC.SUBSTR_UDF('ABC', -1, 1),
    PUBLIC.SUBSTR_UDF('ABC', -1, 2),
    PUBLIC.SUBSTR_UDF('ABC', -1, 3),
    PUBLIC.SUBSTR_UDF('ABC', 0, 1),
    PUBLIC.SUBSTR_UDF('ABC', 0, 2);

```

```sql
:force:
'','','A','','A'

```

--------------------------------

### Input folder structure example

Source: https://docs.snowflake.com/en/migrations/snowconvert-docs/general/getting-started/running-snowconvert/conversion/teradata-conversion-settings

Example input folder structure for the 'Prepare my code' setting, showing three SQL files (DDL_Macros.sql, DDL_Procedures.sql, DDL_Tables.sql) inside the 'in' folder.

```text
├───in
│       DDL_Macros.sql
│       DDL_Procedures.sql
│       DDL_Tables.sql

```

--------------------------------

### Input folder example

Source: https://docs.snowflake.com/en/migrations/snowconvert-docs/general/getting-started/running-snowconvert/conversion/oracle-conversion-settings

Example input folder containing three SQL files with DDL statements for packages, procedures, and tables.

```text
├───in
│       DDL_Packages.sql
│       DDL_Procedures.sql
│       DDL_Tables.sql

```

--------------------------------

### Setup Data for Logical Conditions

Source: https://docs.snowflake.com/en/migrations/aim-for-datawarehouses/code-conversion/translation-references/redshift/redshift-conditions

Setup data for the logical conditions examples. Creates an employee table and inserts sample rows with NULL values for active, hire_date, and salary to demonstrate handling of unknown values.

```sql
 CREATE TABLE employee (
    employee_id INT,
    active BOOLEAN,
    department VARCHAR(100),
    hire_date DATE,
    salary INT
);

INSERT INTO employee (employee_id, active, department, hire_date, salary) VALUES
    (1, TRUE, 'Engineering', '2021-01-15', 70000),
    (2, FALSE, 'HR', '2020-03-22', 50000),
    (3, NULL, 'Marketing', '2019-05-10', 60000),
    (4, TRUE, 'Engineering', NULL, 65000),
    (5, TRUE, 'Sales', '2018-11-05', NULL);

```

--------------------------------

### scai settings set examples

Source: https://docs.snowflake.com/en/migrations/aim-for-datawarehouses/manual-migration/SCAI_Command_Reference

Examples of setting the channel and auto-update preferences.

```bash
# Switch to the preview channel
scai settings set channel=preview

# Disable auto-update
scai settings set autoUpdate=false

# Set both atomically
scai settings set channel=stable autoUpdate=true
```

--------------------------------

### Create transaction_values_test table

Source: https://docs.snowflake.com/en/migrations/aim-for-datawarehouses/code-conversion/translation-references/redshift/rs-sql-statements-create-procedure

Setup table for the COMMIT examples. This table is used in the subsequent procedure examples.

```sql

 CREATE TABLE transaction_values_test
(
    col1 INTEGER
);

```

--------------------------------

### scai ai-convert start examples

Source: https://docs.snowflake.com/migrations/aim-for-datawarehouses/manual-migration/README

Examples of filtering objects for AI-powered conversion, including combining with other flags.

```bash
# AI-convert only tables
scai ai-convert start --where "target.objectType = 'table'"

# AI-convert objects from a specific schema
scai ai-convert start --where "source.schema = 'dbo'"

# AI-convert a single object by name
scai ai-convert start --where "source.name = 'calculate_totals'"

# Combine with other flags
scai ai-convert start --where "target.objectType = 'procedure'" -w -y
scai ai-convert start --where "source.schema = 'reporting'" -i config/instructions.yml
```

--------------------------------

### SQL Server UDF Emulation Setup

Source: https://docs.snowflake.com/en/migrations/aim-for-datawarehouses/code-conversion/issues-and-troubleshooting/conversion-issues/sqlServerEWI

Setup script for a SQL Server table to demonstrate the original behavior.

```sql

 CREATE  TABLE PRODUCT (ProductID INTEGER, Name VarChar(20), Color VarChar(20));
INSERT INTO PRODUCT(PRODUCTID, NAME, COLOR) VALUES(1,'UMBRELLA','RED');
INSERT INTO PRODUCT(PRODUCTID, NAME, COLOR) VALUES(2,'SHORTS','BLUE');
INSERT INTO PRODUCT(PRODUCTID, NAME, COLOR) VALUES(3,'BALL','YELLOW');

WITH XMLNAMESPACES ('uri' as ns1)  
SELECT ProductID as 'ns1:ProductID',  
       Name      as 'ns1:Name',  
       Color     as 'ns1:Color'  
FROM Product  
FOR XML RAW



```

--------------------------------

### ASCII example query

Source: https://docs.snowflake.com/en/migrations/aim-for-datawarehouses/code-conversion/translation-references/transact/transact-built-in-functions

Example query using the Transact-SQL ASCII function to get the ASCII codes for 'A' and 'a'.

```sql
:force:
SELECT ASCII('A') AS A , ASCII('a') AS a;

```

--------------------------------

### Snowflake setup data for STRTOK_SPLIT_TO_TABLE

Source: https://docs.snowflake.com/en/migrations/aim-for-datawarehouses/code-conversion/translation-references/teradata/sql-translation-reference/teradata-built-in-functions

Snowflake setup data for the STRTOK_SPLIT_TO_TABLE example. Creates a table with a COMMENT attribute and inserts the same two rows as the Teradata setup.

```sql
CREATE OR REPLACE TABLE strtokTable
(
	col1 INTEGER,
	col2 VARCHAR(100)
)
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"teradata"}}'
;

INSERT INTO strtokTable
VALUES (4, 'hello-world-split-me');

INSERT INTO strtokTable
VALUES (1, 'string$split$by$dollars');

```

--------------------------------

### PATINDEX_UDF usage example

Source: https://docs.snowflake.com/en/migrations/aim-for-datawarehouses/code-conversion/function-references/sql-server

Example usage of PATINDEX_UDF searching for 'an' in 'banana', returning the starting position 2.

```sql
:force:
SELECT PATINDEX_UDF('an', 'banana');

```

```text
:force:
2

```

--------------------------------

### Initialize project and add SQL Server connection

Source: https://docs.snowflake.com/en/migrations/aim-for-datawarehouses/manual-migration/data-migration

Initializes a SQL Server project and registers a source connection, then verifies connectivity. Requires SCAI CLI, a Snowflake connection, and a registered source connection.

```bash
# Example: SQL Server project
scai init my-project -l Sqlserver -c my-snowflake
scai connection add-sqlserver --source-connection prod-sql --auth standard \
  --host db.example.com --database mydb --user scai --password '***'
scai connection set-default -l sqlserver -s prod-sql

# Verify connectivity
scai connection test -l sqlserver -s prod-sql --json
```
Tokens:
Raw