A Node.js/Express REST API service that synchronizes table metadata from Snowflake data warehouse into MongoDB, providing a cached and queryable metadata layer with pagination support.
Built in 2023-2026. This TypeScript application connects to Snowflake, extracts table and column metadata, stores it in MongoDB, and exposes REST API endpoints with Swagger documentation.
- 🔄 Snowflake Integration - Connects to Snowflake data warehouse and extracts metadata
- 📊 Table Metadata Sync - Fetches table names, schemas, columns, and statistics
- 🗄️ MongoDB Caching - Stores metadata in MongoDB for fast querying
- 📄 Pagination Support - Retrieve metadata with configurable page sizes
- 📚 Swagger UI - Interactive API documentation at
/api-docs - ✅ Input Validation - Joi-based request validation
- 🔐 Error Handling - Centralized error handling with custom error classes
- 📝 Request Logging - Winston-based logging for all requests
- 🚀 TypeScript - Full type safety with strict TypeScript configuration
graph LR
A[Client] -->|HTTP Request| B[Express API]
B -->|Sync Endpoint| C[Snowflake Service]
C -->|Query Metadata| D[Snowflake DW]
C -->|Store Results| E[MongoDB]
B -->|Query Endpoint| E
E -->|Return Data| B
B -->|JSON Response| A
F[Swagger UI] -.->|Documentation| B
G[Winston Logger] -.->|Logs| B
H[Joi Validation] -.->|Validate| B
- Node.js (v16 or higher)
- npm or yarn
- MongoDB (v4.4 or higher)
- Active Snowflake account with appropriate permissions
- Clone the repository:
git clone https://github.com/orassayag/snowflake-metadata-sync.git
cd snowflake-metadata-sync- Install dependencies:
npm install-
Configure your environment:
- Update MongoDB connection in
src/config/constants.config.ts - Configure Snowflake credentials (see Security section below)
- Update MongoDB connection in
-
Start MongoDB (if running locally):
mongod- Run the application:
# Development mode with hot reload
npm run dev
# Production build and start
npm run startThe server will start on http://localhost:8080
Edit src/config/constants.config.ts:
const CONSTANTS = {
SERVER: {
PORT: '8080',
EXPRESS_LIMIT: '5mb',
},
DATA: {
MONGODB: {
CONNECTION_URL: 'mongodb://127.0.0.1:27017/test',
},
SNOWFLAKE: {
ACCESS_URL: 'https://your-account.snowflakecomputing.com',
USERNAME: 'your-username',
PASSWORD: 'your-password',
ACCOUNT: 'YOUR_ACCOUNT_ID',
DATABASE: 'YOUR_DATABASE',
ROLE: 'YOUR_ROLE',
TABLE: 'YOUR_SCHEMA',
},
},
};Access Swagger UI at: http://localhost:8080/api-docs
POST /api/metadata/integration/syncFetches all table metadata from Snowflake and stores in MongoDB.
Response:
{
"status": "success"
}What it does:
- Clears existing metadata in MongoDB
- Queries Snowflake for all tables in the configured schema
- Fetches detailed column information for each table
- Stores complete metadata with relationships
GET /api/metadata/tables?page=1&limit=10Query Parameters:
page(required): Page number, starting from 1limit(required): Number of records per page
Response:
{
"data": [
{
"_id": "6467f0bfecd9682fd2ddbc36",
"created_on": "2021-11-10T05:04:44.572Z",
"name": "CALL_CENTER",
"database_name": "SNOWFLAKE_SAMPLE_DATA",
"schema_name": "TPCDS_SF100TCL",
"kind": "TABLE",
"cluster_by": "LINEAR( cc_call_center_sk )",
"rows": 60,
"bytes": 18944,
"retention_time": 1,
"automatic_clustering": "OFF",
"change_tracking": "OFF",
"is_external": "N",
"columns": [
{
"name": "CC_STATE",
"type": "TEXT"
},
{
"name": "CC_CALL_CENTER_SK",
"type": "NUMBER"
}
]
}
],
"page": 1,
"limit": 10,
"totalRecords": 24
}snowflake-metadata-sync/
├── src/
│ ├── app.ts # Express application entry point
│ ├── config/
│ │ └── constants.config.ts # Application constants
│ ├── controllers/
│ │ └── metadata.controller.ts # Request handlers
│ ├── custom/
│ │ └── error.custom.ts # Custom error class
│ ├── helpers/
│ │ └── express.helper.ts # Express utilities
│ ├── middlewares/
│ │ ├── errors.middleware.ts # Error handling
│ │ ├── joi.middleware.ts # Input validation
│ │ └── logs.middleware.ts # Request logging
│ ├── models/
│ │ └── metadata.model.ts # Business logic
│ ├── routes/
│ │ ├── index.public.routes.ts
│ │ └── public/
│ │ └── metadata.routes.ts # API routes
│ ├── schemas/
│ │ └── metadata.schema.ts # Mongoose schema
│ ├── services/
│ │ ├── logger.service.ts # Winston logger
│ │ ├── mongodb.service.ts # MongoDB connection
│ │ ├── snowflake.service.ts # Snowflake integration
│ │ └── swagger.service.ts # Swagger setup
│ ├── utils/
│ │ ├── apps.utils.ts
│ │ └── files.utils.ts
│ └── validations/
│ └── tables.validation.ts # Joi schemas
├── config/
│ ├── env.js # Environment loader
│ └── env.json # Environment config
├── dist/ # Compiled JavaScript
├── .eslintrc.json # ESLint configuration
├── tsconfig.json # TypeScript config
├── tsconfig.build.json # Build config
└── package.json
sequenceDiagram
participant Client
participant API
participant Snowflake
participant MongoDB
Note over Client,MongoDB: Integration Sync Flow
Client->>API: POST /api/metadata/integration/sync
API->>MongoDB: Clear existing metadata
API->>Snowflake: SHOW TABLES IN schema
Snowflake-->>API: Tables list
API->>Snowflake: SELECT columns info
Snowflake-->>API: Columns for each table
API->>MongoDB: Insert enriched metadata
MongoDB-->>API: Confirmation
API-->>Client: {status: "success"}
Note over Client,MongoDB: Query Tables Flow
Client->>API: GET /api/metadata/tables?page=1&limit=10
API->>MongoDB: Find with pagination
MongoDB-->>API: Results + count
API-->>Client: {data, page, limit, totalRecords}
Runs the app in development mode with hot reload via nodemon.
Compiles TypeScript and runs the production build.
Checks code for linting errors using ESLint.
Checks code formatting with Prettier.
Formats code using Prettier.
Kills all Node.js processes (Windows only).
The project uses:
- TypeScript - Type-safe JavaScript
- Express - Web framework
- Mongoose - MongoDB ODM
- Snowflake SDK - Snowflake connector
- Winston - Logging
- Swagger - API documentation
- Joi - Input validation
- ESLint - Code linting
- Prettier - Code formatting
- Nodemon - Development hot reload
# Run linter
npm run lint
# Check formatting
npm run prettier-check
# Auto-fix formatting
npm run prettier-fixThe project uses two TypeScript configurations:
tsconfig.json- Development configurationtsconfig.build.json- Production build configuration
src/config/constants.config.ts.
Before deploying or committing:
- Never commit real credentials - Move all sensitive data to environment variables
- Use .env files - Store credentials in
.env(add to.gitignore) - Implement authentication - Add JWT or API key authentication to endpoints
- Enable CORS properly - Replace
origin: '*'with specific allowed origins - Use HTTPS - In production, always use HTTPS
- Rate limiting - Add rate limiting middleware
- Input validation - Already implemented with Joi
- MongoDB authentication - Use authenticated MongoDB connections
- Snowflake permissions - Use least-privilege roles
- Audit logging - Log all data access for compliance
The application uses centralized error handling:
CustomErrorclass for application errors- Error middleware catches and formats all errors
- Consistent JSON error responses
- Winston logging for error tracking
Contributions to this project are released to the public under the project's open source license.
Everyone is welcome to contribute. Contributing doesn't just mean submitting pull requests—there are many different ways to get involved, including answering questions and reporting issues.
See CONTRIBUTING.md for detailed guidelines.
- Or Assayag - Initial work - orassayag
- Or Assayag orassayag@gmail.com
- GitHub: https://github.com/orassayag
- StackOverflow: https://stackoverflow.com/users/4442606/or-assayag?tab=profile
- LinkedIn: https://linkedin.com/in/orassayag
This application is licensed under the MIT License - see the LICENSE file for details.