Snowflake Database Emulator ❄️

The Snowflake Database Emulator is a specialized local development utility designed to simulate the behavior of a Snowflake cloud data warehouse using a local PostgreSQL stack. This allows engineers to develop, test, and validate data-intensive applications without incurring cloud computation costs or requiring active network connectivity.

---

🛠️ Technical Architecture

1. Data Persistence Core (PostgreSQL)

We leverage PostgreSQL 15 as the local storage engine. While Snowflake and PostgreSQL have different underlying architectures (Columnar vs Row-based), PostgreSQL's support for standard SQL and binary JSON (JSONB) makes it an ideal candidate for emulating Snowflake's relational and semi-structured (VARIANT) data types.

2. SQL Dialect Emulation (Adapter Pattern)

A key challenge in Snowflake emulation is the discrepancy in SQL dialect parameters.

• Snowflake utilizes positional ? placeholders for parameter binding.
• PostgreSQL requires numbered $n placeholders.

Our SnowflakeConnector implements a light-weight transformation layer that dynamically translates Snowflake-compliant SQL strings into PostgreSQL-compatible queries at runtime. This maintains code transparency and ensures that business logic remains vendor-agnostic.

3. Containerized Infrastructure (Docker)

The environment is fully containerized using Docker Compose, ensuring parity across development environments.

• Mock DB: An isolated PostgreSQL instance pre-configured with project-specific DDLs.
• Adminer: A web-based database management interface for real-time data inspection.

---

🚀 Getting Started

Prerequisites

• Docker & Docker Compose
• Node.js (v18+)
• npm

1. Initialize Infrastructure

Spin up the containerized database and management tools:

npm run db:up

2. Environment Setup

Install the necessary Node.js dependencies for the emulation server:

npm install

3. Launch Emulator Server

Start the Express-based emulation server in development mode:

npm run dev

4. Database Management

To stop the environment and remove containers:

npm run db:down

5. Database Visibility (Adminer)

Monitor the emulated database in real-time via http://localhost:8085.

• System: PostgreSQL
• Server: snowflake-mock
• Username: snowflake_user
• Password: snowflake_password
• Database: snowflake_db

---

📊 Presentation Endpoints

Data Retrieval (GET /orders)

Retrieves all records currently persisted in the mock Snowflake environment.

curl http://localhost:3000/orders

External Ingestion Simulation (POST /sync)

Simulates a common enterprise workflow where data is fetched from an external source (upstream database/API) and ingested into the Snowflake warehouse.

curl -X POST http://localhost:3000/sync

• Process: Fetches mock payloads -> Translates SQL -> Persists via positional binds -> Confirms sync.

Raw Query Emulation (POST /query)

Submit raw Snowflake SQL using ? syntax to see the emulation in action.

curl -X POST http://localhost:3000/query \
     -H "Content-Type: application/json" \
     -d '{ "sql": "SELECT * FROM orders WHERE location_name = ?", "binds": ["Draper Store"] }'

---

📂 Repository Structure

• docker-compose.yml: Infrastructure-as-code definition for mock services.
• init.sql: Bootstrap DDL and seed data.
• src/connector.ts: Core SQL translation and database adapter logic.
• src/server.ts: RESTful API surface for interacting with the emulator.
• src/services/MockExternalService.ts: Simulation logic for upstream data providers.