PostgreSQL Loader

The PostgreSQL Loader is responsible for persisting extracted data into a Postgres database. It bridges the gap between the internal Arrow data format and the relational world, ensuring that data is landed with correct schemas and optimized write strategies.

Configuration

Sinks are defined in gojjam_ingest_sinks.yml. The Postgres loader uses the sqlalchemy engine to manage connections and handle data typing.

Basic Usage

version: "1.0"
sinks:
  - name: "postgres_warehouse"
    type: "postgres"
    source_folder: "./ingest"
    write_mode: "APPEND" # Options: APPEND or OVERWRITE
    config:
      host: "localhost"
      port: 5432
      database: "gojjam_db"
      user: "admin"
      password: "secure_password"

---

Key Features

1. Flexible Write Modes

Gojjam supports two primary strategies for handling existing data:

• APPEND: New records are added to the existing table. This is ideal for immutable event streams or incremental fetches.
• OVERWRITE: The existing table is dropped and replaced with the current chunk of data. Use this for dimension tables or when a full refresh is required.

2. Auto-Schema Creation

The loader proactively ensures your environment is ready. Before writing data, it executes a CREATE SCHEMA IF NOT EXISTS command based on your schema configuration. This prevents pipeline failures when deploying to new environments.

3. High-Performance SQL Mapping

Gojjam ensures that:

• Data types are correctly mapped to Postgres types.
• The write operation is handled as a single unit of work.
• No partial or corrupt data is left behind if an error occurs.

---

Configuration Reference

Field	Type	Description
name	String	A unique identifier for the sink (e.g., postgres_silver).
type	String	The target database system type. Must be postgres.
source_folder	String	The directory path (e.g., ./ingest) where the loader looks for the SQL models it will execute and load.
write_mode	Enum	Determines target table handling: APPEND or OVERWRITE.
config.host	String	The hostname or IP address of the target PostgreSQL instance.
config.port	Integer	The port number to connect to PostgreSQL (typically 5432).
config.database	String	The name of the specific database inside the PostgreSQL instance.
config.user	String	The database user account name used for authentication.
config.password	String	The password for the database user account.
config.schema	String	The targeted database schema namespace (e.g., public, raw, staging).

---

Implementation Note:

The loader handles commits and rollbacks for you. As a developer, this means you don't have to worry about manually managing the transaction state—Gojjam keeps your database integrity safe out of the box.