Brink POS First Integration

Purpose

This document describes the Brink POS first integration as the reference implementation of the ETL source adapter framework.

It is the single end-to-end reference for understanding which Brink API endpoints feed the pipeline, what staging tables they produce, what curated tables they populate, and how the four framework stages orchestrate the full flow.

Overview

Brink POS is the first and currently only active source adapter for the KDSAI ETL pipeline. It integrates with Brink's SOAP-based API to pull order, labor, and settings data for restaurant tenants, transforming it through the ETL source adapter framework into a shared warehouse schema.

The integration covers the full extraction-to-curated path:

1. Extract raw SOAP responses from Brink and store them in S3.
2. Normalize the XML payloads into load-ready Parquet artifacts.
3. Load normalized artifacts into tenant-scoped Redshift staging tables.
4. Transform staging data into shared curated tables inside each tenant Redshift database.

Active Brink Endpoints

The following Brink API endpoints are currently active in the extract flow:

Settings2.GetDestinations is available as a staging table and is used as a lookup join in curated.pos_orders, but the standalone extract-to-staging flow is not yet independently scheduled.

ETL Pipeline Stages

The Brink POS adapter uses four implemented stages through the ETL source adapter framework:

1. extract_raw

• Calls each active Brink SOAP endpoint and persists the raw XML response to S3 under the tenant-scoped raw prefix.
• Creates the initial ingestion batch and records raw artifact paths.
• Validates SOAP response envelopes and result codes before persisting.

2. normalize_load

• Reads raw XML artifacts from S3.
• Normalizes Brink XML payloads into Parquet files following the Brink normalization contracts.
• Writes load-ready Parquet to S3 under the tenant-scoped load prefix.
• Advances checkpoint on success, writes failed package on exception.

3. load_to_staging

• Resolves tenant warehouse connection and target staging tables.
• Runs Redshift COPY commands to load Parquet artifacts into staging tables.
• Records per-table load diagnostics.
• Advances checkpoint on success, creates failed package on exception.

4. staging_to_curated

• Validates required staging tables are present.
• Executes curated target SQL (DELETE then INSERT) inside a single transaction per tenant.
• Collects diagnostics for each curated target.
• Advances checkpoint on success, writes failed package on exception.

Reserved stage: curated_to_s3

The framework reserves a fifth stage name (curated_to_s3) for future export of curated tables to S3 Parquet. This stage is not implemented.

Staging Tables

The following staging tables are produced by the Brink extract flow, grouped by source endpoint:

Sales2.GetOrders

Labor.GetShifts

Settings2.GetItems

Settings2.GetEmployees

Settings2.GetJobs

Settings2.GetDestinations (lookup only)

Curated Targets

Seven curated tables are populated from Brink staging data. Each curated target runs a DELETE then INSERT inside a single transaction scoped by tenant and business date or snapshot date.

Data Flow Diagram

flowchart TD
    subgraph Brink API
        E1["Sales2.GetOrders"]
        E2["Labor.GetShifts"]
        E3["Settings2.GetItems"]
        E4["Settings2.GetEmployees"]
        E5["Settings2.GetJobs"]
        E6["Settings2.GetDestinations"]
    end

    subgraph S3 Raw
        R1["Raw XML payloads"]
    end

    subgraph S3 Load
        L1["Normalized Parquet artifacts"]
    end

    subgraph Redshift Staging
        S1["brink_sales2_orders<br/>+ 17 child tables"]
        S2["brink_labor_shifts<br/>brink_labor_breaks"]
        S3["brink_settings2_items<br/>brink_settings2_item_groups<br/>+ 2 child tables"]
        S4["brink_settings2_employees<br/>+ 3 child tables"]
        S5["brink_settings2_jobs<br/>+ 2 child tables"]
        S6["brink_settings2_get_destinations"]
    end

    subgraph Redshift Curated
        C1["curated.pos_orders"]
        C2["curated.pos_order_items"]
        C3["curated.pos_menu_items"]
        C4["curated.employees"]
        C5["curated.pos_jobs"]
        C6["curated.labor_shifts"]
        C7["curated.pos_destinations"]
    end

    E1 --> R1
    E2 --> R1
    E3 --> R1
    E4 --> R1
    E5 --> R1
    E6 --> R1

    R1 --> L1

    L1 --> S1
    L1 --> S2
    L1 --> S3
    L1 --> S4
    L1 --> S5
    L1 --> S6

    S1 --> C1
    S6 --> C1
    S1 --> C2
    S3 --> C2
    S3 --> C3
    S4 --> C4
    S5 --> C5
    S2 --> C6
    S6 --> C7

Curated Target Details

curated.pos_orders

Order header fact table. One row per business order. Enriched with order_type, destination, service_channel, and fulfillment_channel from a LEFT JOIN to brink_settings2_get_destinations on destination_id. Status is derived from is_refund, is_future_order, and is_closed flags. Payment method, terminal name, store metadata, currency, and loyalty fields are not yet populated.

curated.pos_order_items

Order line fact table. One row per order entry. Quantity is set to 1 per Brink entry. Modifier data is aggregated from brink_sales2_order_entry_modifiers into JSON and a text summary. Category ID comes from brink_settings2_item_groups filtered to item_group_ordinal = 1. Category name is not yet populated.

curated.pos_menu_items

Menu item snapshot dimension. One row per item per snapshot date. Category ID from brink_settings2_item_groups (ordinal 1). is_86d and availability_status are derived from is_active. kds_delay maps from days_available. Category name and station/KDS routing are not yet populated.

curated.employees

Employee dimension. One row per employee snapshot. is_active is derived as NOT COALESCE(terminated, FALSE). SCD2 columns (effective_from_utc, effective_to_utc, is_current) are deferred to NULL.

curated.pos_jobs

Job (workforce role) dimension. One row per job snapshot. Contains boolean role flags (is_bartender, is_delivery_driver, is_delivery_dispatcher, is_hostess) and training flag. SCD2 columns and is_active are deferred to NULL.

curated.labor_shifts

Labor shift fact table. One row per shift. Break data is aggregated inline from brink_labor_breaks via LEFT JOIN and GROUP BY: break_count = COUNT(b.shift_id), total_break_minutes = COALESCE(SUM(b.minutes), 0). shift_number maps from s.number in staging.

curated.pos_destinations

Destination reference dimension. One row per destination snapshot. Used both as a standalone curated table and as a lookup join in curated.pos_orders for order routing context. SCD2 columns are deferred to NULL.

Current Limitations

The following fields and capabilities are not yet populated or implemented:

Unmapped curated fields

• pos_orders: payment_method, terminal_name, store_id, store_name, points_used, additional_payment, currency_code, order_key, tenant_key are NULL.
• pos_order_items: category_name, kds_delay, order_item_key, currency_code, size_name are NULL.
• pos_menu_items: category_name, kds_station, menu_item_key, currency_code, timezone_name, 86d_reason, 86d_at are NULL.
• employees, pos_jobs, pos_destinations: SCD2 columns (effective_from_utc, effective_to_utc, is_current), enrichment keys, and currency_code are NULL.

Unimplemented capabilities

• curated_to_s3 export stage is reserved but not implemented.
• Settings2.GetDestinations standalone extract scheduling is not active (used only as lookup join).
• Additional Brink endpoints such as Settings2.GetTables, Settings2.GetKitchenQueues, Settings2.GetTaxes, Settings.GetTenders are not in the active extract flow.
• Surrogate key generation (order_key, employee_key, job_key, labor_shift_key, destination_key, menu_item_key, tenant_key) is deferred.

Related Documents

• ETL Source Adapter Framework - framework architecture and stage responsibilities
• Warehouse Data Index - warehouse model overview
• Brink POS Staging Model - staging table documentation
• Brink POS Curated Mappings - curated-to-staging column mappings
• Curated Model - curated table documentation
• ETL Ingestion Flow - end-to-end ingestion flow diagram
• ETL Architecture - top-level ETL and data ingestion architecture