← Back to blog

Snowflake TPC-H Schema Guide

February 15, 2026

schemasnowflakereference

NL2SQL works with the TPC-H modified, like in real life database schema in Snowflake. This guide covers the core tables and how they relate to each other.

TPC-H Schema Diagram

Core Tables

The schema lives in Snowflake database and includes seven primary tables:

ORDERS | Order headers with dates and totals | Fact table, ETL/ELT process populates surrogate keys from dimensions

LINEITEM | Individual line items within orders | Fact table, ETL/ELT process populates surrogate keys from dimensions

CUSTOMER | Customer records with demographics | Dimension table, modified to Slowly changing dimension Type 2 table, like in real life, with attributes VALID_FROM_DATE, VALID_TO_DATE, CURRENT_RECORD_INDICATOR

PART | Product catalog | Dimension table, TTL loading logic, Truncate-Then-Load

SUPPLIER | Supplier information | Dimension table, TTL loading logic, Truncate-Then-Load

NATION | Country lookup table | Dimension table, TTL loading logic, Truncate-Then-Load

REGION | Region lookup table | Dimension table, TTL loading logic, Truncate-Then-Load

Key Relationships

  • ORDERS.O_CUSTKEY → CUSTOMER.C_CUSTKEY
  • LINEITEM.L_ORDERKEY → ORDERS.O_ORDERKEY
  • LINEITEM.L_PARTKEY → PART.P_PARTKEY
  • LINEITEM.L_SUPPKEY → SUPPLIER.S_SUPPKEY
  • CUSTOMER.C_NATIONKEY → NATION.N_NATIONKEY
  • SUPPLIER.S_NATIONKEY → NATION.N_NATIONKEY
  • NATION.N_REGIONKEY → REGION.R_REGIONKEY

Common Query Patterns

Revenue by region: Ask: "What is the total revenue by region for 2025?"

Top customers: Ask: "Who are the top 5 customers by order count?"

Supplier analysis: Ask: "Which suppliers have the most line items for parts in the ECONOMY category?"