External Tables

Viewing:

Overview

External tables allow you to connect Moose to database tables that are managed outside of your application. This is essential when working with:

CDC (Change Data Capture) services like ClickPipes, Debezium, or AWS DMS
Legacy database tables managed by other teams
Third-party data sources with controlled schema evolution

When to Use External Tables

External Table Use Cases

CDC Services

When schema is controlled by services like ClickPipes, Debezium, or AWS DMS

Legacy Integration

Connecting to existing tables managed by other teams or systems

Third-party Data

Working with data sources where you don't control schema evolution

Strict Change Management

Environments with formal database change approval processes

Configuration

Set lifeCycle: LifeCycle.EXTERNALLY_MANAGED to tell Moose not to modify the table schema:

ExternalTableExample.ts

import { OlapTable, LifeCycle } from "@514labs/moose-lib";
 
interface CdcUserData {
  id: string;
  name: string;
  email: string;
  updated_at: Date;
}
 
// Connect to CDC-managed table
const cdcUserTable = new OlapTable<CdcUserData>("cdc_users", {
  lifeCycle: LifeCycle.EXTERNALLY_MANAGED
});

ExternalTableExample.py

from moose_lib import OlapTable, OlapConfig, LifeCycle
from pydantic import BaseModel
from datetime import datetime
 
class CdcUserData(BaseModel):
    id: str
    name: str
    email: str
    updated_at: datetime
 
# Connect to CDC-managed table
cdc_user_table = OlapTable[CdcUserData]("cdc_users", OlapConfig(
    life_cycle=LifeCycle.EXTERNALLY_MANAGED
))

CDC Service Integration

ClickPipes Example

When using ClickPipes or similar CDC services, the schema is managed by the CDC pipeline:

ClickPipesExample.ts

import { OlapTable, LifeCycle } from "@514labs/moose-lib";
 
interface ClickPipesUserEvent {
  user_id: string;
  event_type: string;
  event_data: Record<string, any>;
  captured_at: Date;
  source_table: string;
}
 
const clickPipesEvents = new OlapTable<ClickPipesUserEvent>("clickpipes_events", {
  lifeCycle: LifeCycle.EXTERNALLY_MANAGED
});

ClickPipesExample.py

from moose_lib import OlapTable, OlapConfig, LifeCycle
from pydantic import BaseModel
from datetime import datetime
from typing import Dict, Any
 
class ClickPipesUserEvent(BaseModel):
    user_id: str
    event_type: str
    event_data: Dict[str, Any]
    captured_at: datetime
    source_table: str
 
clickpipes_events = OlapTable[ClickPipesUserEvent]("clickpipes_events", OlapConfig(
    life_cycle=LifeCycle.EXTERNALLY_MANAGED
))

Important Considerations

Warning:

Schema Matching Required: Your data model must exactly match the external table schema. Mismatches will cause runtime errors.

MooseTip:

No Automatic Migrations: Moose will not apply any schema changes to externally managed tables. You must handle schema evolution through your CDC service or external processes.

Schema Evolution

When the external schema changes (e.g., new columns added by CDC service):

Update your data model to match the new schema
Redeploy your application to pick up the changes
No migration needed - Moose will use the existing table structure

SchemaEvolutionExample.ts

// Original schema
interface UserData {
  id: string;
  name: string;
  email: string;
}
 
// After CDC service adds new column
interface UserData {
  id: string;
  name: string;
  email: string;
  created_at: Date; // New column added by CDC
}
 
const userTable = new OlapTable<UserData>("users", {
  lifeCycle: LifeCycle.EXTERNALLY_MANAGED
});

Lifecycle Management - Detailed explanation of all lifecycle modes
Applying Migrations - How migrations work with managed tables
Schema Changes - Understanding schema evolution patterns

Applying Migrations

Inserting Data