Snowflake Publishing

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

Prerequisites

1. Snowflake Setup

Required objects:

• Database (e.g., LAKEXPRESS_DB)
• Warehouse (e.g., LAKEXPRESS_WH)
• Stage pointing to S3 bucket (e.g., LAKEXPRESS_STAGE)

Required privileges:

USE ROLE ACCOUNTADMIN;
GRANT CREATE SCHEMA ON DATABASE LAKEXPRESS_DB TO ROLE SYSADMIN;
GRANT USAGE ON WAREHOUSE LAKEXPRESS_WH TO ROLE SYSADMIN;
GRANT USAGE ON STAGE LAKEXPRESS_DB.PUBLIC.LAKEXPRESS_STAGE TO ROLE SYSADMIN;

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"
    }
  }
}

Critical: The S3 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}	Source database name	tpch
{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 auto-creates views that expose typed columns instead of the raw Parquet VALUE column.

Options for config create:

-- External table (raw)
SELECT * FROM EXT_TPCH_1.CUSTOMER;
-- Shows: VALUE column (complex structure)

-- View (typed columns)
SELECT * FROM EXT_TPCH_1.V_CUSTOMER;
-- Shows: C_CUSTKEY, C_NAME, C_ADDRESS, etc.

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

External tables must be refreshed after data upload:

ALTER EXTERNAL TABLE EXT_TPCH_1.CUSTOMER REFRESH;
ALTER EXTERNAL TABLE EXT_TPCH_1.LINEITEM REFRESH;

Or via Snowflake CLI:

snow sql -q "ALTER EXTERNAL TABLE EXT_TPCH_1.LINEITEM 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 enforces constraints (query planner benefits)

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

• Query planner optimizations
• Better execution plans
• Documents data integrity

See Also

• Snowflake Reference - Data type mappings, CLI arguments, deprecated options
• CLI Reference - All command-line options
• Examples & Recipes - Working command examples