Snowflake Publishing

LakeXpress creates Snowflake tables from exported Parquet files, enabling queries on data stored in S3, Azure, or GCS.

Prerequisites

1. Snowflake Setup

LakeXpress needs a warehouse, a database and an external stage on the folder where it writes the Parquet files. It creates everything else itself: the target schemas (CREATE SCHEMA IF NOT EXISTS), the tables (CREATE TABLE / CREATE EXTERNAL TABLE), the views of external tables, and the COPY INTO of internal tables. The Parquet file format is given inline, so no FILE FORMAT object is needed.

The scripts below create a dedicated role and user with only the privileges LakeXpress needs. The object names are examples: keep them consistent with your credentials.json.

Step 1: Warehouse, database, role and user

-- Warehouse and database
USE ROLE SYSADMIN;
CREATE WAREHOUSE IF NOT EXISTS LAKEXPRESS_WH
  WAREHOUSE_SIZE = 'XSMALL'
  AUTO_SUSPEND = 60
  AUTO_RESUME = TRUE
  INITIALLY_SUSPENDED = TRUE;
CREATE DATABASE IF NOT EXISTS LAKEXPRESS_DB;

-- Role and user for LakeXpress
USE ROLE SECURITYADMIN;
CREATE ROLE IF NOT EXISTS LAKEXPRESS_ROLE;
GRANT ROLE LAKEXPRESS_ROLE TO ROLE SYSADMIN;
CREATE USER IF NOT EXISTS LAKEXPRESS_USER
  PASSWORD = '<strong-password>'
  DEFAULT_ROLE = LAKEXPRESS_ROLE
  DEFAULT_WAREHOUSE = LAKEXPRESS_WH;
GRANT ROLE LAKEXPRESS_ROLE TO USER LAKEXPRESS_USER;

-- Privileges (granted by SYSADMIN, owner of the warehouse and the database)
USE ROLE SYSADMIN;
GRANT USAGE ON WAREHOUSE LAKEXPRESS_WH TO ROLE LAKEXPRESS_ROLE;
GRANT USAGE, CREATE SCHEMA ON DATABASE LAKEXPRESS_DB TO ROLE LAKEXPRESS_ROLE;
GRANT USAGE ON SCHEMA LAKEXPRESS_DB.PUBLIC TO ROLE LAKEXPRESS_ROLE;  -- schema of the stage

LakeXpress owns the schemas it creates, so it needs no other privilege on them. If it publishes into a schema that already exists and that another role owns, also grant:

GRANT USAGE, CREATE TABLE, CREATE EXTERNAL TABLE, CREATE VIEW
  ON SCHEMA LAKEXPRESS_DB.<SCHEMA> TO ROLE LAKEXPRESS_ROLE;

For key pair or PAT authentication instead of a password, see Authentication Modes.

Step 2: Storage integration and stage

The stage must point to exactly the folder of the storage entry LakeXpress exports to (its directory): LakeXpress reads each table under <stage>/<sub_path>/<source schema>/<table>/. A storage integration lets Snowflake read that folder without storing cloud keys in Snowflake. Snowflake only reads the files, so read access is enough.

AWS S3

In AWS, create an IAM policy that can read the export folder, and an IAM role (for example snowflake-lakexpress) with that policy:

{
  "Version": "2012-10-17",
  "Statement": [
    {
      "Effect": "Allow",
      "Action": ["s3:GetObject", "s3:GetObjectVersion"],
      "Resource": "arn:aws:s3:::my-datalake-bucket/lakexpress/*"
    },
    {
      "Effect": "Allow",
      "Action": ["s3:ListBucket", "s3:GetBucketLocation"],
      "Resource": "arn:aws:s3:::my-datalake-bucket",
      "Condition": { "StringLike": { "s3:prefix": ["lakexpress/*"] } }
    }
  ]
}

Then, in Snowflake:

USE ROLE ACCOUNTADMIN;
CREATE STORAGE INTEGRATION IF NOT EXISTS LAKEXPRESS_S3_INT
  TYPE = EXTERNAL_STAGE
  STORAGE_PROVIDER = 'S3'
  ENABLED = TRUE
  STORAGE_AWS_ROLE_ARN = 'arn:aws:iam::123456789012:role/snowflake-lakexpress'
  STORAGE_ALLOWED_LOCATIONS = ('s3://my-datalake-bucket/lakexpress/');

-- Note STORAGE_AWS_IAM_USER_ARN and STORAGE_AWS_EXTERNAL_ID
DESC INTEGRATION LAKEXPRESS_S3_INT;

GRANT USAGE ON INTEGRATION LAKEXPRESS_S3_INT TO ROLE SYSADMIN;

USE ROLE SYSADMIN;
CREATE STAGE IF NOT EXISTS LAKEXPRESS_DB.PUBLIC.LAKEXPRESS_STAGE
  URL = 's3://my-datalake-bucket/lakexpress/'
  STORAGE_INTEGRATION = LAKEXPRESS_S3_INT;
GRANT USAGE ON STAGE LAKEXPRESS_DB.PUBLIC.LAKEXPRESS_STAGE TO ROLE LAKEXPRESS_ROLE;

Back in AWS, set the trust policy of the IAM role with the two values returned by DESC INTEGRATION:

{
  "Version": "2012-10-17",
  "Statement": [
    {
      "Effect": "Allow",
      "Principal": { "AWS": "<STORAGE_AWS_IAM_USER_ARN>" },
      "Action": "sts:AssumeRole",
      "Condition": { "StringEquals": { "sts:ExternalId": "<STORAGE_AWS_EXTERNAL_ID>" } }
    }
  ]
}

The matching storage entry has "directory": "s3://my-datalake-bucket/lakexpress/".

Azure Storage

USE ROLE ACCOUNTADMIN;
CREATE STORAGE INTEGRATION IF NOT EXISTS LAKEXPRESS_AZURE_INT
  TYPE = EXTERNAL_STAGE
  STORAGE_PROVIDER = 'AZURE'
  ENABLED = TRUE
  AZURE_TENANT_ID = '<tenant-id>'
  STORAGE_ALLOWED_LOCATIONS = ('azure://<account>.blob.core.windows.net/<container>/lakexpress/');

-- Open AZURE_CONSENT_URL to approve the Snowflake application, and note AZURE_MULTI_TENANT_APP_NAME
DESC INTEGRATION LAKEXPRESS_AZURE_INT;

GRANT USAGE ON INTEGRATION LAKEXPRESS_AZURE_INT TO ROLE SYSADMIN;

USE ROLE SYSADMIN;
CREATE STAGE IF NOT EXISTS LAKEXPRESS_DB.PUBLIC.LAKEXPRESS_STAGE
  URL = 'azure://<account>.blob.core.windows.net/<container>/lakexpress/'
  STORAGE_INTEGRATION = LAKEXPRESS_AZURE_INT;
GRANT USAGE ON STAGE LAKEXPRESS_DB.PUBLIC.LAKEXPRESS_STAGE TO ROLE LAKEXPRESS_ROLE;

In the Azure portal, give the Snowflake application (AZURE_MULTI_TENANT_APP_NAME) the Storage Blob Data Reader role on the container. The stage uses Snowflake's azure:// form of the folder that the LakeXpress storage entry names abfss://<container>@<account>.dfs.core.windows.net/lakexpress/.

Google Cloud Storage

USE ROLE ACCOUNTADMIN;
CREATE STORAGE INTEGRATION IF NOT EXISTS LAKEXPRESS_GCS_INT
  TYPE = EXTERNAL_STAGE
  STORAGE_PROVIDER = 'GCS'
  ENABLED = TRUE
  STORAGE_ALLOWED_LOCATIONS = ('gcs://my-datalake-bucket/lakexpress/');

-- Note STORAGE_GCP_SERVICE_ACCOUNT
DESC INTEGRATION LAKEXPRESS_GCS_INT;

GRANT USAGE ON INTEGRATION LAKEXPRESS_GCS_INT TO ROLE SYSADMIN;

USE ROLE SYSADMIN;
CREATE STAGE IF NOT EXISTS LAKEXPRESS_DB.PUBLIC.LAKEXPRESS_STAGE
  URL = 'gcs://my-datalake-bucket/lakexpress/'
  STORAGE_INTEGRATION = LAKEXPRESS_GCS_INT;
GRANT USAGE ON STAGE LAKEXPRESS_DB.PUBLIC.LAKEXPRESS_STAGE TO ROLE LAKEXPRESS_ROLE;

In Google Cloud, give the service account STORAGE_GCP_SERVICE_ACCOUNT the Storage Object Viewer role on the bucket. The stage uses Snowflake's gcs:// form of the folder that the LakeXpress storage entry names gs://my-datalake-bucket/lakexpress/.

Before publishing, LakeXpress checks that the stage URL is the storage directory, and stops with an error naming both if they differ. Snowflake's gcs:// and azure:// forms count as the same folder as LakeXpress's gs:// and abfss:// ones.

The stage can live in another schema: give its full name in credentials.json ("stage": "LAKEXPRESS_DB.LANDING.LAKEXPRESS_STAGE") and grant USAGE on that schema instead of PUBLIC. A stage name without a schema is looked up in PUBLIC.

Step 3: Check the setup as the LakeXpress user

USE ROLE LAKEXPRESS_ROLE;
USE WAREHOUSE LAKEXPRESS_WH;
DESC STAGE LAKEXPRESS_DB.PUBLIC.LAKEXPRESS_STAGE;  -- the URL must be the storage directory
LIST @LAKEXPRESS_DB.PUBLIC.LAKEXPRESS_STAGE;       -- after a first export: the Parquet files

2. Cloud Storage Setup

Both S3 and Snowflake credentials must be in your credentials.json:

{
  "s3_01": {
    "ds_type": "s3",
    "auth_mode": "profile",
    "info": {
      "directory": "s3://my-datalake-bucket/lakexpress/",
      "profile": "lakexpress"
    }
  },
  "snowflake_prod": {
    "ds_type": "snowflake",
    "auth_mode": "password",
    "info": {
      "account": "myorg-myaccount",
      "user": "LAKEXPRESS_USER",
      "password": "$env{LX_SNOWFLAKE_PASSWORD}",
      "warehouse": "LAKEXPRESS_WH",
      "database": "LAKEXPRESS_DB",
      "stage": "LAKEXPRESS_STAGE",
      "role": "LAKEXPRESS_ROLE"
    }
  }
}

role is optional: without it, LakeXpress uses the user's default role. The connection also accepts an optional schema.

Critical: The storage directory path must exactly match the Snowflake stage location.

Verify the match:

DESC STAGE LAKEXPRESS_DB.PUBLIC.LAKEXPRESS_STAGE;
-- URL column shows: s3://my-datalake-bucket/lakexpress/

-- This MUST match your S3 credentials directory
-- "directory": "s3://my-datalake-bucket/lakexpress/"  ✓ Correct
-- "directory": "s3://my-datalake-bucket/"              ✗ Wrong - files won't be found

Authentication Modes

Password

{
  "snowflake_prod": {
    "ds_type": "snowflake",
    "auth_mode": "password",
    "info": {
      "account": "myorg-myaccount",
      "user": "LAKEXPRESS_USER",
      "password": "$env{LX_SNOWFLAKE_PASSWORD}",
      "warehouse": "LAKEXPRESS_WH",
      "database": "LAKEXPRESS_DB",
      "stage": "LAKEXPRESS_STAGE"
    }
  }
}

Programmatic Access Token (PAT)

Recommended for automation:

{
  "snowflake_prod": {
    "ds_type": "snowflake",
    "auth_mode": "pat",
    "info": {
      "account": "myorg-myaccount",
      "user": "LAKEXPRESS_USER",
      "token": "your-personal-access-token",
      "warehouse": "LAKEXPRESS_WH",
      "database": "LAKEXPRESS_DB",
      "stage": "LAKEXPRESS_STAGE"
    }
  }
}

PATs require a network policy configured for the user.

PAT benefits:

• Easily rotated, configurable lifetime
• No MFA prompts -- suited for CI/CD pipelines

Other Authentication Modes

LakeXpress also supports two additional auth_mode values not shown above:

• keypair -- RSA private/public key pair authentication (info.private_key_path, optional info.private_key_passphrase)
• oauth -- OAuth token authentication (info.oauth_token or info.authenticator)

Table Types

External Tables

Data stays in cloud storage; Snowflake stores only metadata. Views are auto-created for cleaner querying.

• No data loading time or Snowflake storage costs
• Immediate availability after export
• Suited for: data lake integration, infrequent access, exploration, ETL staging

Options for config create:

--publish_target snowflake_prod
--publish_method external  # Default

Internal Tables

Data is loaded into Snowflake storage with full feature support (clustering, time travel, etc.). Can include PRIMARY KEY constraints from source.

• Faster queries with full Snowflake features
• Maintains PRIMARY KEY constraints
• Suited for: frequently queried data, production warehouses, complex joins

Options for config create:

--publish_target snowflake_prod
--publish_method internal
--pk_constraints  # Optional: include PK constraints

Dynamic Naming Patterns

Schema and table names support token-based patterns for time-partitioned exports, schema consolidation, or unique identifiers.

Supported Tokens

Token	Description	Example Output
{schema}	Source schema name	tpch_1
{table}	Source table name	customer
{database}	Target Snowflake database (database of the credentials entry)	ANALYTICS
{date}	Current date (YYYYMMDD)	20251120
{timestamp}	Current timestamp (YYYYMMDD_HHMMSS)	20251120_112511
{uuid}	UUID4 identifier (consistent per run)	a1b2c3d4-...
{subpath}	CLI --sub_path parameter	toto

Note: All timestamps are in local time. The {table} token is mandatory for --publish_table_pattern.

CLI Arguments

--publish_schema_pattern PATTERN    # Schema name pattern (default varies by table type)
--publish_table_pattern PATTERN      # Table name pattern (must include {table} token)

Default Patterns

Table Type	Default Schema Pattern	Default Table Pattern
External	EXT_{schema}	{table}
Internal	{schema}	{table}

Common Patterns

Time-Based Partitioning

--publish_schema_pattern "EXT_{schema}_{date}"
--publish_table_pattern "{table}"
--publish_target snowflake_prod

# Results:
# Schema: EXT_tpch_1_20251120
# Tables: customer, orders, lineitem

Multi-Schema Consolidation

--source_schema_name schema1,schema2,schema3
--publish_schema_pattern "{subpath}"
--publish_table_pattern "{schema}_{table}"
--sub_path consolidated
--publish_target snowflake_prod

# Results:
# Schema: consolidated
# Tables: schema1_customer, schema2_customer, schema3_customer

Unique Run Identifiers

--publish_schema_pattern "EXT_{schema}_{uuid}"
--publish_table_pattern "{table}"
--publish_target snowflake_prod

# Results:
# Schema: EXT_tpch_1_a1b2c3d4-e5f6-7890-abcd-ef1234567890
# Tables: customer, orders, lineitem

Usage Examples

Ready-to-run commands for Snowflake are in Examples & Recipes: Snowflake.

Views and Customization

Automatic Views (External Tables Only)

For external tables, LakeXpress also creates a V_<table> view. The external table exposes the typed columns plus the raw Parquet VALUE column; the view leaves VALUE out.

LakeXpress creates every identifier quoted, so names keep the case of the source. With a lowercase source schema tpch_1, quote them in queries:

-- External table: typed columns + VALUE
SELECT * FROM "EXT_tpch_1"."customer";

-- View: typed columns only
SELECT * FROM "EXT_tpch_1"."V_customer";

View Options

Option	Description	Default
--no_views	Skip view creation	Views created

The view name prefix is not configurable -- it is hardcoded to V_ (there is no --snowflake_view_prefix CLI flag).

No views:

Options for config create:

--publish_target snowflake_prod
--publish_method external
--no_views

# Tables: customer, orders
# Views: None

Refreshing External Tables

Each publish drops and recreates the external tables with REFRESH_ON_CREATE = TRUE (and AUTO_REFRESH = FALSE), so they list the files present at publish time. Refresh by hand only if files change outside a LakeXpress run:

ALTER EXTERNAL TABLE "EXT_tpch_1"."customer" REFRESH;

Or via Snowflake CLI:

snow sql -q 'ALTER EXTERNAL TABLE "EXT_tpch_1"."customer" REFRESH;' -c lakexpress

Primary Key Constraints

Applies to internal tables only. LakeXpress propagates PRIMARY KEY constraints from the source database to Snowflake.

Usage

--publish_target snowflake_prod
--publish_method internal
--pk_constraints

How It Works

1. Reads PRIMARY KEY metadata from the source database
2. Stores PK information in the LakeXpress DB
3. Adds PRIMARY KEY constraints to the Snowflake DDL
4. Snowflake stores the constraints as metadata. It does not enforce them (duplicates are not rejected), but they document the model for BI and modeling tools

Requirements

• --publish_method internal only
• Source database must have PRIMARY KEY constraints defined
• LakeXpress DB must have is_primary_key column (auto-created in v0.1.16+)

Benefits

• Documents the data model in Snowflake
• BI and data modeling tools read the keys to infer relationships

LakeXpress creates plain PRIMARY KEY constraints, without RELY, so the Snowflake optimizer does not use them.

Write Strategies

Incremental tables support the append (default), upsert and delete_insert strategies. A table using upsert or delete_insert needs a primary key and is always created as an internal table, whatever --publish_method is. See Loading Strategies.

See Also

• Snowflake ReferenceData type mappings, CLI arguments, deprecated options
• CLI ReferenceAll command-line options
• Examples & RecipesWorking command examples