A staging layer organizes and consolidates ingested data before warehouse loading. It applies only light transformations: filtering, technical metadata such as ingestion timestamps, surrogate-key calculation, and changed-data identification. Business logic belongs later, in the data warehouse layer rather than staging. section 11.1.3
Staging is one step in a layered pipeline that commonly runs from extraction through staging and data warehouse to presentation. Layers improve visibility and control over data flows, but they are optional: a pipeline can omit or add layers when its architecture requires it. section 11.1.3 section 11.3
Choose the physical placement of staging according to source separation and access needs:
- Place it in additional schemas within an extraction database when source systems need to remain separated. section 11.1.4
- Place it in a schema in the data warehouse database when it serves as the intermediate point where data from extraction comes together. section 11.1.4
- Keep flows separated by layer instead of directly joining tables or views from different layers. section 11.3
- Use physical intermediate objects such as tables, materialized views, or dynamic tables, or store virtual intermediate results as views. section 11.1.3
Bakery staging schema
A STG schema holds two kinds of prepared input: a JSON_ORDERS_STG view that flattens customer-order JSON into relational columns, and PARTNER and PRODUCT tables supplied by an operational-system integration process. The flattened orders retain source_file_name and load_ts alongside customer, order, delivery, product, and quantity fields. After the integration-created tables are populated, their maintenance remains with the integration tool. section 11.2 section 11.2.2
use database BAKERY_DB; use schema STG; create view JSON_ORDERS_STG as select E.customer_orders:"Customer"::varchar as customer, E.customer_orders:"Order date"::date as order_date, CO.value:"Delivery date"::date as delivery_date, DO.value:"Baked good type":: varchar as baked_good_type, DO.value:"Quantity"::number as quantity, source_file_name, load_ts from EXT.JSON_ORDERS_EXT E, lateral flatten (input => customer_orders:"Orders") CO, lateral flatten (input => CO.value:"Orders by day") DO;
Inspect the SQL view definition to see JSON fields flattened into relational columns while preserving source-file and load-timestamp metadata. section 11.2.2
The staging transformation can be virtual: JSON_ORDERS_STG is a view rather than a copied table. This keeps the staging representation focused on reshaping and exposing incoming data, while later layers combine sources, apply consistent modeling, and produce consumer-facing aggregates. section 11.1.3 section 11.1.4
