How to Connect Snowflake to Elasticsearch: Best Methods

Snowflake is excellent for cloud data warehousing and large-scale analytics, but it is not designed to serve low-latency full-text search or application search directly. Elasticsearch fills that gap by indexing Snowflake data into searchable JSON documents that can support fast search, filtering, and analytics.

Connecting Snowflake to Elasticsearch is useful when curated warehouse data needs to power search experiences, operational dashboards, customer-facing applications, or faster exploration outside the warehouse.

In this guide, we’ll compare two ways to move Snowflake data into Elasticsearch: using Estuary for managed incremental sync and using CSV export/import for a manual one-time load. If you’re already familiar with both platforms, jump straight to the methods.

Snowflake vs Elasticsearch: Why Connect the Two?

Although Snowflake offers advanced data warehousing capabilities, it isn't optimized for handling unstructured data or real-time analytics. Elasticsearch, on the other hand, specializes in these areas with its robust full-text search engine and real-time data exploration capabilities.

Benefits of Connecting Snowflake to Elasticsearch:

• Elasticsearch’s distributed, flexible architecture excels at storing and analyzing time series data. On the contrary, Snowflake isn’t very efficient for this task. Finance, marketing, and IoT applications that rely on such data can benefit from using Elasticsearch.
  
• Elasticsearch provides a robust full-text search engine that supports complex querying, aggregations, and real-time data exploration. This helps you perform more efficient analysis of large datasets to gain better insights.
  
• Elasticsearch’s real-time analytical capabilities can help your organization detect and respond to fluctuating trends. This is essential for applications such as monitoring systems, real-time personalization, and dynamic pricing, which depend on current data.

How to Connect Snowflake to Elasticsearch: 2 Methods

There are two practical ways to move Snowflake data into Elasticsearch. Estuary is better for recurring incremental sync, while CSV export/import is only suitable for small, one-time, or occasional loads.

• Method 1: Using Estuary to sync Snowflake data to Elasticsearch
• Method 2: Using CSV export/import to load Snowflake data into Elasticsearch

Method 1: Using Estuary to Load Data from Snowflake to Elasticsearch

Estuary is a managed right data integration platform that supports both streaming and batch data movement. For Snowflake, Estuary uses Snowflake Streams and staging tables to capture inserts, updates, and deletes incrementally. The connector polls for changes on a configurable interval, which gives you recurring low-latency sync without keeping a Snowflake warehouse active continuously.

Some of Estuary’s essential features include a library of 200+ ready-to-use connectors and an intuitive user interface that allows you to easily design, monitor, and manage complex data workflows.

The platform caters to both technical and non-technical users, making it a valuable tool for optimizing data integration processes.

Prerequisites

• A Snowflake account with a database containing the tables you want to capture.
• A Snowflake virtual warehouse that Estuary can use to execute queries.
• A Snowflake schema for connector-managed streams and staging tables. Estuary uses ESTUARY_STAGING by default unless changed in advanced configuration.
• A Snowflake user or role with permission to read source tables and create streams and transient tables in the staging schema.
• JWT key-pair authentication for Snowflake.
• An Elasticsearch cluster with a known endpoint.
• An Elasticsearch role with the required privileges for the target indices.
• An Estuary account.

Step 1: Configure Snowflake as the Source

• Sign in to your Estuary account.
  
• Select Sources from the left-side navigation pane.
  
• On the Sources page, click the + NEW CAPTURE button.
  

• You will be redirected to the Create Capture page, where you will see a Search connectors field. Type Snowflake in this field.
  
• Click the Capture button of the Snowflake Data Cloud connector in the search results.
  

• On the Snowflake connector configuration page, specify the following details:
  • Name: A unique name for the capture.
  • Host URL: Your Snowflake host, such as orgname-accountname.snowflakecomputing.com. Do not include https://.
  • Database: The Snowflake database to capture from.
  • Credentials: Use JWT key-pair authentication.
  • User: The Snowflake username for the connector.
  • Private Key: The private key associated with the public key configured on the Snowflake user.
  • Warehouse: The virtual warehouse Estuary should use to execute queries, or leave blank to use the user’s default warehouse.
  • Role: The Snowflake role assigned to the connector user, if applicable.
  • Advanced options: Use these only if you need to change the default staging schema or other advanced connector behavior.
    
• To complete the source configuration, click the NEXT button on the top right corner and then SAVE AND PUBLISH.
  
• The Snowflake CDC connector captures change events from source tables into Estuary collections using Snowflake Streams. By default, it polls for changes every 5 minutes, and the interval can be configured depending on your latency and cost requirements.

Step 2: Configure Elasticsearch as the Destination

• To configure Elasticsearch as the destination end of the integration pipeline, click MATERIALIZE COLLECTIONS in the pop-up window appearing after the successful capture.

Alternatively, click the Destinations option on the left pane of the dashboard. Then, click + NEW MATERIALIZATION and navigate to the Create Materialization page.

• Type Elastic in the Search connectors field. When the connector appears in the results, click its Materialization button.

• On the Elasticsearch connector configuration page, fill in the required details, such as a unique Name for the materialization and Endpoint host or URL.
• To authenticate to Elasticsearch, you can use a username and password or an API key.
• Use the Source Collections section to choose the collection of your Snowflake data and link it to your materialization.
• Finally, click the NEXT button on the top right corner and then SAVE AND PUBLISH to complete the destination configuration.

To use the Elasticsearch connector, you need an Elastic cluster endpoint and a role with the required privileges. Estuary’s docs list the required cluster privilege as monitor and the index privileges as read, write, view_index_metadata, and create_index for each target index. You can authenticate with a username and password or an API key.

The connector materializes Estuary collections into Elasticsearch indices. It can automatically create an index for each materialization binding, uses the last component of the collection name as the index name by default, and lets you customize the index using the index property.

If you need deleted Snowflake rows to be removed from Elasticsearch search results, review the Elasticsearch connector’s hardDelete option. By default, deletions are tracked as soft deletes through _meta/op; enabling hard deletes removes deleted items from the destination.

Why Use Estuary for Snowflake to Elasticsearch

• Incremental change capture: Estuary captures inserts, updates, and deletes from Snowflake tables using Snowflake Streams and staging tables.
• Configurable freshness: The Snowflake connector polls for changes on a configurable interval, with a 5-minute default designed to balance latency and Snowflake warehouse cost.
• Managed materialization to Elasticsearch: Estuary materializes captured Snowflake collections into Elasticsearch indices without requiring you to manually build Bulk API jobs.
• Schema-aware pipelines:Estuary collections have associated schemas, helping validate data and reduce downstream mapping issues.
• Transformations: You can use derivations to reshape collections before they are indexed into Elasticsearch.
• Operational simplicity: Estuary manages the pipeline so you do not have to manually export files, schedule jobs, or maintain custom loaders.

Method 2: Using CSV Export/Import to Move Data from Snowflake to Elasticsearch

This method involves exporting Snowflake data in CSV format to your local system and then loading it into Elasticsearch. While it's cost-effective, it lacks real-time capabilities and can be effort-intensive for large datasets.

Step 1: Exporting Snowflake Data as a CSV

You can use the SnowSQL command-line editor to download Snowflake data to your local system.

Prerequisites

• Ensure SnowSQL is installed on your system. 
  
• Snowflake account credentials. 
  
• Permission to read data from the Snowflake database and table.

Use SnowSQL to connect to Snowflake

Open your command prompt and type:

bash language-plaintext
snowsql -a <account_identifier> -u <user_name>

This command helps you connect to your Snowflake account using SnowSQL. When prompted, enter your password to connect to your Snowflake account.

Set the output format to CSV

Before running a query, set SnowSQL's output format to CSV

sql language-plaintext
ALTER SESSION SET OUTPUT_FORMAT = 'CSV';

Select Snowflake Database Table

Execute the following SQL command to select the data to export:

SELECT * FROM your_database.your_schema.your_table;

Export Data as a CSV File

Direct the output of your query to a CSV file using ‘>’ filename.csv syntax in your command.

plaintext
SELECT * FROM your_database.your_schema.your_table > /path/to/your/outputfile.csv;

Replace the path of your output file with the actual path and filename to save the CSV file.

Exit SnowSQL

Type exit to close the SnowSQL command line session after the export is complete.

Verify the output CSV file for correctness

Open the exported CSV file with a text editor or a spreadsheet program to ensure the data has been correctly exported and formatted.

Step 2: Import Data into Elasticsearch Using Kibana

You can use the File Data Visualizer feature of the Kibana stack to import a CSV file into Elasticsearch. The feature allows you to explore your data with Kibana and import small amounts into Elasticsearch without data ingestion.

• Navigate to Kibana.
  
• Open the Machine Learning section and select Data Visualizer.

Image Source

• In current Elastic/Kibana documentation, Data Visualizer supports CSV, TSV, NDJSON, and log files up to 500 MB. The default file size limit can be changed through fileUpload:maxFileSize, but large or recurring loads are better handled with a pipeline rather than manual upload.
  
• Kibana sends the first 1000 lines of your CSV file to the find_file_structure endpoint, which runs its analysis and returns the findings.
  
• Review the automatically generated analysis and verify the data structure. 
  
• Use the Override settings button to adjust settings, especially if your CSV file lacks a header row or requires other specific configurations.
  
• After you’re done verifying the settings, click on the Import button at the bottom left of the page. 
  
• There are two modes of importing the CSV file: 

1. Simple: You can provide a new unique index name and choose whether to create an index pattern. 
   
2. Advanced: You can customize index settings, mappings, and ingest pipelines to create the index.

• The import process involves the following steps:

1. Processing File: This involves the conversion of data into NDJSON documents, which is suitable for data ingestion using the Elasticsearch bulk API. 
   
2. Creating Index: Create an index using specific settings and mapping objects. 
   
3. Creating Ingest Pipeline: Create an ingest pipeline using the ingest pipeline object. 
   
4. Uploading Data: This step involves loading data into the newly created index. 
   
5. Create Index Pattern: You can create a Kibana index pattern (only if opted).

Challenges of Using CSV Export/Import to Move Data from Snowflake to Elasticsearch

• Effort-intensive: Manually moving data from Snowflake to Elasticsearch requires a lot of effort. This process can be slow, especially with limited bandwidth and large data volumes.
  
• Lack of Real-time Capabilities: There are significant delays involved in manually extracting Snowflake data and loading it into Elasticsearch. As a result, this method isn’t a good choice if you require real-time analytics or decision-making.
  
• Data Integrity Issues: Maintaining consistency, efficiency, and accuracy throughout the migration process can be difficult without automation. The frequent manual intervention to execute and monitor tasks cause inconsistencies such as incorrect mapping of data, leading to data integrity issues.

Conclusion

Connecting Snowflake to Elasticsearch lets teams keep Snowflake as the analytics warehouse while using Elasticsearch for fast search, filtering, and operational analytics.

CSV export/import can work for small, one-time loads, demos, or quick testing. But it is manual, snapshot-based, and not suitable for continuously updated search indexes.

Estuary is a better fit when you want managed incremental sync from Snowflake to Elasticsearch. It captures Snowflake table changes using Snowflake Streams, polls on a configurable interval, and materializes the resulting collections into Elasticsearch indices.

Snowflake is one route into your cluster; our guide on loading data into Elasticsearch lays out the main ingestion methods and their trade-offs.

If you are indexing data from other warehouses, see our guides to syncing BigQuery to Elasticsearch and moving Redshift data into Elasticsearch.

If you don’t have an Estuary account, sign up to start building your Snowflake to Elasticsearch pipeline.