stg_gcs__whse_contracts

staging view

Client×site contracts and SLA targets, typed and FK-validated.

Definition

Cleaned mirror of `raw_gcs.whse_contracts`. Coerces the CSV's `Nullable(String)` columns into typed equivalents and enforces FKs to `stg_gcs__whse_clients` and `stg_gcs__whse_sites`. Raw term dates are `start_date`/`end_date` (aliased to `term_start`/`term_end` only in `int_whse__contract_sla`; the corpus reads the raw names). The `source_uri` is the reconciliation key into the client-sla-contracts corpus.

16 of 16

Column

Description

Tests · PII

contract_id

UUID5 from the source CSV. Primary key.

2 tests

client_id

Foreign key to stg_gcs__whse_clients.

client_contact +1 PII2 tests

site_id

Foreign key to stg_gcs__whse_sites.

2 tests

contract_ref

Human contract ref, e.g. `CN-2024-014`. Free-text.

billing_model

Billing model.

2 tests

otif_target_pct

OTIF SLA target percent. Coerced from String → Decimal(6,2).

2 tests

pick_accuracy_target_pct

Pick-accuracy SLA target percent. Coerced from String → Decimal(6,2).

2 tests

dock_to_stock_target_hrs

Inbound dock-to-stock SLA target in hours. Coerced from String → Decimal(6,2).

1 test

start_date

Contract start date. Coerced from String → Date. Raw name aliased to `term_start` downstream.

1 test

end_date

Contract end date. Coerced from String → Date. Raw name aliased to `term_end` downstream.

auto_renew

1 if the contract auto-renews, 0 otherwise. Coerced from the CSV's "true"/"false" strings.

1 test

monthly_fee_eur

Recurring monthly fee in EUR. Coerced from String → Decimal(14,2).

source_uri

Deterministic `gs://…/unstructured/client-sla-contracts/<contract_id>.md` URI on every row. Reconciliation key into the client-sla-contracts corpus.

1 test

source_file_url

Airbyte-supplied `_ab_source_file_url` — the gs:// URI the row came from.

source_file_modified_at

Airbyte-supplied `_ab_source_file_last_modified` — object mtime of the source file.

airbyte_extracted_at

When Airbyte extracted this row from GCS.