Skip to main content

Information Schema

Overview​

Information Schema file contains the metadata of the source that will be used for modelling of the Data Vault. The file is located in the sources directory of the project.

The simple structure of the file includes following mandatory columns

  • TABLE_SCHEMA
  • TABLE_NAME
  • COLUMN_NAME
  • ORDINAL_POSITION
  • DATA_TYPE

Snowflake​

The following SQL will extract information schema details from Snowflake's sample database containing TPCH dataset.

use schema snowflake_sample_data.tpch_sf1;

select *
  from information_schema.columns 
 where upper(table_schema) = 'TPCH_SF1' 
 order by table_name, ordinal_position ;

CSV Example​

The following section has a sample dataset used in various examples and tutorials on this site.

Information Schema Sample

TABLE_SCHEMA,TABLE_NAME,COLUMN_NAME,ORDINAL_POSITION,DATA_TYPE
TABLE_SCHEMA,TABLE_NAME,COLUMN_NAME,ORDINAL_POSITION,DATA_TYPE
TPCH_SF1,CUSTOMER,C_CUSTKEY,1,NUMBER
TPCH_SF1,CUSTOMER,C_NAME,2,TEXT
TPCH_SF1,CUSTOMER,C_ADDRESS,3,TEXT
TPCH_SF1,CUSTOMER,C_NATIONKEY,4,NUMBER
TPCH_SF1,CUSTOMER,C_PHONE,5,TEXT
TPCH_SF1,CUSTOMER,C_ACCTBAL,6,NUMBER
TPCH_SF1,CUSTOMER,C_MKTSEGMENT,7,TEXT
TPCH_SF1,CUSTOMER,C_COMMENT,8,TEXT
TPCH_SF1,CUSTOMER,CDC_FLAG,8,TEXT
TPCH_SF1,CUSTOMER,CDC_TIMESTAMP,8,TIMESTAMP
TPCH_SF1,LINEITEM,L_ORDERKEY,1,NUMBER
TPCH_SF1,LINEITEM,L_PARTKEY,2,NUMBER
TPCH_SF1,LINEITEM,L_SUPPKEY,3,NUMBER
TPCH_SF1,LINEITEM,L_LINENUMBER,4,NUMBER
TPCH_SF1,LINEITEM,L_QUANTITY,5,NUMBER
TPCH_SF1,LINEITEM,L_EXTENDEDPRICE,6,NUMBER
TPCH_SF1,LINEITEM,L_DISCOUNT,7,NUMBER
TPCH_SF1,LINEITEM,L_TAX,8,NUMBER
TPCH_SF1,LINEITEM,L_RETURNFLAG,9,TEXT
TPCH_SF1,LINEITEM,L_LINESTATUS,10,TEXT
TPCH_SF1,LINEITEM,L_SHIPDATE,11,DATE
TPCH_SF1,LINEITEM,L_COMMITDATE,12,DATE
TPCH_SF1,LINEITEM,L_RECEIPTDATE,13,DATE
TPCH_SF1,LINEITEM,L_SHIPINSTRUCT,14,TEXT
TPCH_SF1,LINEITEM,L_SHIPMODE,15,TEXT
TPCH_SF1,LINEITEM,L_COMMENT,16,TEXT
TPCH_SF1,NATION,N_NATIONKEY,1,NUMBER
TPCH_SF1,NATION,N_NAME,2,TEXT
TPCH_SF1,NATION,N_REGIONKEY,3,NUMBER
TPCH_SF1,NATION,N_COMMENT,4,TEXT
TPCH_SF1,ORDERS,O_ORDERKEY,1,NUMBER
TPCH_SF1,ORDERS,O_CUSTKEY,2,NUMBER
TPCH_SF1,ORDERS,O_ORDERSTATUS,3,TEXT
TPCH_SF1,ORDERS,O_TOTALPRICE,4,NUMBER
TPCH_SF1,ORDERS,O_ORDERDATE,5,DATE
TPCH_SF1,ORDERS,O_ORDERPRIORITY,6,TEXT
TPCH_SF1,ORDERS,O_CLERK,7,TEXT
TPCH_SF1,ORDERS,O_SHIPPRIORITY,8,NUMBER
TPCH_SF1,ORDERS,O_COMMENT,9,TEXT
TPCH_SF1,PART,P_PARTKEY,1,NUMBER
TPCH_SF1,PART,P_NAME,2,TEXT
TPCH_SF1,PART,P_MFGR,3,TEXT
TPCH_SF1,PART,P_BRAND,4,TEXT
TPCH_SF1,PART,P_TYPE,5,TEXT
TPCH_SF1,PART,P_SIZE,6,NUMBER
TPCH_SF1,PART,P_CONTAINER,7,TEXT
TPCH_SF1,PART,P_RETAILPRICE,8,NUMBER
TPCH_SF1,PART,P_COMMENT,9,TEXT
TPCH_SF1,PARTSUPP,PS_PARTKEY,1,NUMBER
TPCH_SF1,PARTSUPP,PS_SUPPKEY,2,NUMBER
TPCH_SF1,PARTSUPP,PS_AVAILQTY,3,NUMBER
TPCH_SF1,PARTSUPP,PS_SUPPLYCOST,4,NUMBER
TPCH_SF1,PARTSUPP,PS_COMMENT,5,TEXT
TPCH_SF1,REGION,R_REGIONKEY,1,NUMBER
TPCH_SF1,REGION,R_NAME,2,TEXT
TPCH_SF1,REGION,R_COMMENT,3,TEXT
TPCH_SF1,SUPPLIER,S_SUPPKEY,1,NUMBER
TPCH_SF1,SUPPLIER,S_NAME,2,TEXT
TPCH_SF1,SUPPLIER,S_ADDRESS,3,TEXT
TPCH_SF1,SUPPLIER,S_NATIONKEY,4,NUMBER
TPCH_SF1,SUPPLIER,S_PHONE,5,TEXT
TPCH_SF1,SUPPLIER,S_ACCTBAL,6,NUMBER
TPCH_SF1,SUPPLIER,S_COMMENT,7,TEXT