C3 AI Documentation Home

Query a Snowflake Table Programmatically with Virtualization

Use this quickstart when your data lives in a Snowflake table and your application must read the current rows without keeping a copy. This approach is virtualization: the rows stay in Snowflake. The C3 Agentic AI Platform reads the rows on demand each time your application queries the entity Type that models the table. Ingestion is the alternative, where the platform copies the rows into an entity Type that your application owns.

You connect to Snowflake with key-pair authentication and inspect the table. Then you model the table as an entity Type, bind that Type to the connection with a source collection, and query it. A later section shows how to make the connection part of your package. "Fix common problems" covers the errors you are most likely to see. The worked example reads a sample table of wind turbine measurements, PUBLIC.MEASUREMENTS. The example names (the snowflakeDemo source system, the SnowflakeMeasurement Type, and the snowflakeMeasurementColl collection) stand in for your own; use your own table, source, and Type names.

You work in two places. You run runtime commands in the C3 AI Console of your application. You define the entity Type in your IDE as package source, which the platform syncs to the application.

Four Types work together:

  • SqlSourceSystem: the source system, which is the connection to the Snowflake database.
  • JdbcCredentials: the credentials that authenticate the connection, including key-pair authentication.
  • SqlSourceCollection: the source collection, which binds one table to the entity Type that models it.
  • An entity Type that mixes External: the Type you query, which reads the Snowflake table.

The source system holds the connection, the credentials authenticate it, and the source collection routes every query on your entity Type through that connection.

Prerequisites

Before you start, make sure you have the following:

  • A C3 Agentic AI Platform application in dev mode, with access to the C3 AI Console. The platform accepts new Types, source systems, and source collections only while the application is in dev mode.
  • A role that allows data integration from code. You do not need cluster administrator access, because setCredentials stores credentials at the application level. To choose a role, see Data Integration Roles and Permissions.
  • Snowflake connection details: the account endpoint, database, warehouse, and a Snowflake user whose public key is registered in Snowflake.
  • The Snowflake user's private key in PEM format, and the key's passphrase if the key is encrypted.
  • A Snowflake table to read. The example uses PUBLIC.MEASUREMENTS.
  • The package source for your application, open in your IDE, such as VS Code with the C3 AI extension.

Step 1: Connect to Snowflake

In the Console, build the key-pair credentials, create the source system, and set the credentials on it. Every query in this quickstart runs through this connection.

JavaScript
var key = PrivateKeyAuth.createFromPem('<private-key-pem>', '<passphrase>', 'PKCS8', 'RSA');

var creds = JdbcCredentials.make({
  serverEndpoint: '<account>.snowflakecomputing.com',
  port: 443,
  datastore: 'snowflake',
  database: '<database>',
  username: '<user>',
  privateKeyAuth: key,
  properties: { warehouse: '<warehouse>' }
});

SqlSourceSystem.make({ name: 'snowflakeDemo' }).upsert();
SqlSourceSystem.forName('snowflakeDemo').setCredentials(creds, false);

SqlSourceSystem.forName('snowflakeDemo').ping();

Replace the following placeholders with your values:

  • <private-key-pem>: The private key in PEM format, including the BEGIN and END lines. Use \n for line breaks.
  • <passphrase>: The passphrase for the private key, or null if the key is unencrypted.
  • <account>.snowflakecomputing.com: The Snowflake account endpoint.
  • <database>, <warehouse>, <user>: The Snowflake database, warehouse, and user.

Keep the other values as shown. 'PKCS8' and 'RSA' name the key format and algorithm of a standard Snowflake key pair, 443 is the Snowflake port, and 'snowflake' sets the datastore kind. The name snowflakeDemo is your choice; later steps use it again.

Note three settings in this block:

  • Key pair: build the credentials with JdbcCredentials.make and set the key in privateKeyAuth. The JdbcCredentials.fromServerEndpoint factory takes a password and has no key parameter.
  • Warehouse: set the warehouse in properties.
  • Replace or merge: the second argument to setCredentials replaces (false) or merges with (true) any credentials stored earlier.

Run ping after every credential change to test the connection. The platform stores credentials without validating them, so setCredentials succeeds even when a value is wrong. A working connection returns {reachable: true}. A failed connection returns {reachable: false} with an errorMsg that describes the error.

Step 2: Inspect the source data

In the Console, preview the table before you model it. The entity Type in Step 3 maps its fields to these column names, and the names must match exactly, including case. Snowflake stores unquoted column names in uppercase.

The example table MEASUREMENTS has five columns: T (a timestamp), ACTIVEPOWER, GENERATORROTATIONSPEED, GEAROILTEMPERATURE (numbers), and ASSET (a string, for example TURBINE-1).

JavaScript
SqlSourceSystem.forName('snowflakeDemo').previewTable('<table>', 3);

Replace <table> with the table name, for example PUBLIC.MEASUREMENTS. PUBLIC is the Snowflake schema. A name qualified with its schema works whatever the connection's default schema is. The second argument is the number of rows to return. The result lists the column names, then the first three rows.

Step 3: Model the data

In your IDE, add one file to the package source, then sync the package to the application. The file defines the SnowflakeMeasurement entity Type, and its field mapping decides what your application can query.

Save the Type as src/SnowflakeMeasurement.c3typ. SnowflakeMeasurement mixes in two Types:

  • External: the Type reads a table outside the platform, named by schema name on the Type. The platform creates no table for an External Type and stores no rows for it.
  • NoSystemCols: the table has none of the system columns that the platform adds to its own tables.

The schema name on each field names the source column it reads:

Type
entity type SnowflakeMeasurement mixes External, NoSystemCols schema name 'PUBLIC.MEASUREMENTS' {

  @db(dataTypeOverride='datetime')
  id: ~ schema name 'T'

  activePower: double schema name 'ACTIVEPOWER'
  rotationSpeed: double schema name 'GENERATORROTATIONSPEED'
  gearOilTemp: double schema name 'GEAROILTEMPERATURE'
  asset: string schema name 'ASSET'
}

Replace PUBLIC.MEASUREMENTS with your schema and table, and each column name with yours.

Every entity Type has an id field. The line id: ~ schema name 'T' redeclares that field and maps it to the column T. Map id to a column whose values identify a row. If no single column does, mix in a CompositeKey Type instead. When the id column is not a string, add @db(dataTypeOverride='<type>'), as shown for the timestamp column T.

Save the file and wait for the platform to finish syncing the package source. After the platform deploys the Type, SnowflakeMeasurement returns the Type in the Console.

Step 4: Create the source collection

In the Console, create the source collection. The source collection routes queries for SnowflakeMeasurement through the source system and its credentials. Without the source collection, the platform has no connection to serve the Type.

JavaScript
SqlSourceCollection.make({
  name: 'snowflakeMeasurementColl',
  source: SnowflakeMeasurement,
  sourceSystem: SqlSourceSystem.forName('snowflakeDemo')
}).upsert();

This source collection has no cdcOrder and no transform reads from the Type, so the platform never copies rows. Each query on SnowflakeMeasurement reads Snowflake directly. In Data Fusion the Virtual Table mode of a SQL source collection gives the same result. For the canvas steps, see Connect with SQL Source System. The Ingest a Snowflake Table Programmatically quickstart uses the same External mixin for its Source Type. The ingest quickstart then adds a cdcOrder and a transform to copy the rows.

The Console route suits iteration in a dev application. To deploy the connection with your package instead, follow "Make the connection part of your package".

Step 5: Query the data live

In the Console, fetch from the Type. Application code queries the Type the same way. The platform sends the query to Snowflake, runs it there, and returns the rows. c3Grid displays the result as a table in the Console:

JavaScript
c3Grid(SnowflakeMeasurement.fetch({ limit: 20 }));

The grid shows up to 20 rows with the columns id, activePower, rotationSpeed, gearOilTemp, and asset.

Filters and paging also run in Snowflake. Filters use C3 filter expressions on the Type's field names:

JavaScript
c3Grid(SnowflakeMeasurement.fetch({ filter: "asset == 'TURBINE-1'", limit: 50 }));

Each fetch runs in Snowflake at call time, so results reflect the current table. Each query also uses Snowflake warehouse credits. For frequent queries on the same data, compare the warehouse cost with the cost of ingestion.

Make the connection part of your package

Declare the source system and source collection as package metadata when the connection must deploy with your package. Objects you create in the Console are runtime updates, and deploying a new version of the package overrides them.

The folder under metadata/ must match the Type name, and each file carries the same fields you passed to make:

  1. Create metadata/SqlSourceSystem/snowflakeDemo.json:

    JSON
    { "name": "snowflakeDemo" }

  2. Create metadata/SqlSourceCollection/snowflakeMeasurementColl.json. In a file, source is the Type name as a string, and sourceSystem names the source system by Type and name:

    JSON
    {
  "name": "snowflakeMeasurementColl",
  "source": "SnowflakeMeasurement",
  "sourceSystem": { "type": "SqlSourceSystem", "name": "snowflakeDemo" }
}

  3. Sync the package to the application.

Keep credentials out of the package. Credentials are secrets: set them at runtime with setCredentials, as in Step 1.

Fix common problems

Find the symptom you see, then apply the fix in the same row.

SymptomCauseFix
createFromPem fails with Invalid private key format: unable to read encrypted data (wrong passphrase) or Invalid private key format: Passphrase required for encrypted key (no passphrase)The key is encrypted and the passphrase is wrong or missing.Pass the key's passphrase as the second argument, then rerun Step 1.
ping returns {reachable: false} and errorMsg mentions a missing passwordThe credentials carry no private key, for example because they were built with fromServerEndpoint and no privateKeyAuth.Build the credentials with JdbcCredentials.make, set privateKeyAuth, and rerun setCredentials and ping.
ping returns {reachable: false} with another error, or queries fail after setCredentials succeededWrong endpoint, user, key, or passphrase; the platform does not validate credentials when you set them.Correct the values, rerun setCredentials from Step 1, and run ping again.
fetch fails or returns empty columnsThe schema name values on the Type do not match the table or column names.Rerun previewTable and align the names, including case.

See also

Was this page helpful?