Database Construction#

Input datasets in Temoa are stored in a relational database management system. For those unfamiliar with databases, you can think of them as collections of tables. Within each table, a ‘primary key’ uniquely identifies each row. A ‘foreign key’ is a column in one table that references the primary key of another table, thereby establishing relationships between tables and ensuring data consistency across the database.

The following Entity-Relationship (ER) diagram provides a visual overview of the Temoa v4 database schema and the relationships between its various tables:

        erDiagram
capacity_credit {
 INTEGER period PK
   TEXT region PK
   TEXT tech PK
   INTEGER vintage PK
   REAL credit
   TEXT notes
}
technology {
 TEXT tech PK
   INTEGER annual
   TEXT category
   INTEGER curtail
   TEXT description
   INTEGER exchange
   TEXT flag
   INTEGER flex
   INTEGER reserve
   INTEGER retire
   INTEGER seas_stor
   TEXT sector
   TEXT sub_category
   INTEGER unlim_cap
}
technology_type {
 TEXT label PK
   TEXT description
}
time_period {
 INTEGER period PK
   TEXT flag
   INTEGER sequence
}
time_period_type {
 TEXT label PK
   TEXT description
}
capacity_factor_process {
 INTEGER period PK
   TEXT region PK
   TEXT season PK
   TEXT tech PK
   TEXT tod PK
   INTEGER vintage PK
   REAL factor
   TEXT notes
}
time_of_day {
 TEXT tod PK
   INTEGER sequence
}
season_label {
 TEXT season PK
   TEXT notes
}
capacity_factor_tech {
 INTEGER period PK
   TEXT region PK
   TEXT season PK
   TEXT tech PK
   TEXT tod PK
   REAL factor
   TEXT notes
}
capacity_to_activity {
 TEXT region PK
   TEXT tech PK
   REAL c2a
   TEXT notes
}
commodity {
 TEXT name PK
   TEXT description
   TEXT flag
}
commodity_type {
 TEXT label PK
   TEXT description
}
construction_input {
 TEXT input_comm PK
   TEXT region PK
   TEXT tech PK
   INTEGER vintage PK
   TEXT notes
   TEXT units
   REAL value
}
cost_emission {
 TEXT emis_comm PK
   INTEGER period PK
   TEXT region PK
   REAL cost
   TEXT notes
   TEXT units
}
cost_fixed {
 INTEGER period PK
   TEXT region PK
   TEXT tech PK
   INTEGER vintage PK
   REAL cost
   TEXT notes
   TEXT units
}
cost_invest {
 TEXT region PK
   TEXT tech PK
   INTEGER vintage PK
   REAL cost
   TEXT notes
   TEXT units
}
cost_variable {
 INTEGER period PK
   TEXT region PK
   TEXT tech PK
   INTEGER vintage PK
   REAL cost
   TEXT notes
   TEXT units
}
demand {
 TEXT commodity PK
   INTEGER period PK
   TEXT region PK
   REAL demand
   TEXT notes
   TEXT units
}
demand_specific_distribution {
 TEXT demand_name PK
   INTEGER period PK
   TEXT region PK
   TEXT season PK
   TEXT tod PK
   REAL dsd
   TEXT notes
}
efficiency {
 TEXT input_comm PK
   TEXT output_comm PK
   TEXT region PK
   TEXT tech PK
   INTEGER vintage PK
   REAL efficiency
   TEXT notes
}
efficiency_variable {
 TEXT input_comm PK
   TEXT output_comm PK
   INTEGER period PK
   TEXT region PK
   TEXT season PK
   TEXT tech PK
   TEXT tod PK
   INTEGER vintage PK
   REAL efficiency
   TEXT notes
}
emission_activity {
 TEXT emis_comm PK
   TEXT input_comm PK
   TEXT output_comm PK
   TEXT region PK
   TEXT tech PK
   INTEGER vintage PK
   REAL activity
   TEXT notes
   TEXT units
}
emission_embodied {
 TEXT emis_comm PK
   TEXT region PK
   TEXT tech PK
   INTEGER vintage PK
   TEXT notes
   TEXT units
   REAL value
}
emission_end_of_life {
 TEXT emis_comm PK
   TEXT region PK
   TEXT tech PK
   INTEGER vintage PK
   TEXT notes
   TEXT units
   REAL value
}
end_of_life_output {
 TEXT output_comm PK
   TEXT region PK
   TEXT tech PK
   INTEGER vintage PK
   TEXT notes
   TEXT units
   REAL value
}
existing_capacity {
 TEXT region PK
   TEXT tech PK
   INTEGER vintage PK
   REAL capacity
   TEXT notes
   TEXT units
}
lifetime_process {
 TEXT region PK
   TEXT tech PK
   INTEGER vintage PK
   REAL lifetime
   TEXT notes
}
lifetime_survival_curve {
 INTEGER period PK
   TEXT region PK
   TEXT tech PK
   INTEGER vintage PK
   REAL fraction
   TEXT notes
}
lifetime_tech {
 TEXT region PK
   TEXT tech PK
   REAL lifetime
   TEXT notes
}
limit_activity {
 TEXT operator PK
   INTEGER period PK
   TEXT region PK
   TEXT tech_or_group PK
   REAL activity
   TEXT notes
   TEXT units
}
operator {
 TEXT operator PK
   TEXT notes
}
limit_activity_share {
 TEXT operator PK
   INTEGER period PK
   TEXT region PK
   TEXT sub_group PK
   TEXT super_group PK
   TEXT notes
   REAL share
}
limit_annual_capacity_factor {
 TEXT operator PK
   TEXT output_comm PK
   INTEGER period PK
   TEXT region PK
   TEXT tech PK
   REAL factor
   TEXT notes
}
limit_capacity {
 TEXT operator PK
   INTEGER period PK
   TEXT region PK
   TEXT tech_or_group PK
   REAL capacity
   TEXT notes
   TEXT units
}
limit_capacity_share {
 TEXT operator PK
   INTEGER period PK
   TEXT region PK
   TEXT sub_group PK
   TEXT super_group PK
   TEXT notes
   REAL share
}
limit_degrowth_capacity {
 TEXT operator PK
   TEXT region PK
   TEXT tech_or_group PK
   TEXT notes
   REAL rate
   REAL seed
   TEXT seed_units
}
limit_degrowth_new_capacity {
 TEXT operator PK
   TEXT region PK
   TEXT tech_or_group PK
   TEXT notes
   REAL rate
   REAL seed
   TEXT seed_units
}
limit_degrowth_new_capacity_delta {
 TEXT operator PK
   TEXT region PK
   TEXT tech_or_group PK
   TEXT notes
   REAL rate
   REAL seed
   TEXT seed_units
}
limit_emission {
 TEXT emis_comm PK
   TEXT operator PK
   INTEGER period PK
   TEXT region PK
   TEXT notes
   TEXT units
   REAL value
}
limit_growth_capacity {
 TEXT operator PK
   TEXT region PK
   TEXT tech_or_group PK
   TEXT notes
   REAL rate
   REAL seed
   TEXT seed_units
}
limit_growth_new_capacity {
 TEXT operator PK
   TEXT region PK
   TEXT tech_or_group PK
   TEXT notes
   REAL rate
   REAL seed
   TEXT seed_units
}
limit_growth_new_capacity_delta {
 TEXT operator PK
   TEXT region PK
   TEXT tech_or_group PK
   TEXT notes
   REAL rate
   REAL seed
   TEXT seed_units
}
limit_new_capacity {
 TEXT operator PK
   INTEGER period PK
   TEXT region PK
   TEXT tech_or_group PK
   REAL new_cap
   TEXT notes
   TEXT units
}
limit_new_capacity_share {
 TEXT operator PK
   INTEGER period PK
   TEXT region PK
   TEXT sub_group PK
   TEXT super_group PK
   TEXT notes
   REAL share
}
limit_resource {
 TEXT operator PK
   TEXT region PK
   TEXT tech_or_group PK
   REAL cum_act
   TEXT notes
   TEXT units
}
limit_seasonal_capacity_factor {
 TEXT operator PK
   INTEGER period PK
   TEXT region PK
   TEXT season PK
   TEXT tech PK
   REAL factor
   TEXT notes
}
region {
 TEXT region PK
   TEXT notes
}
limit_storage_level_fraction {
 TEXT operator PK
   INTEGER period PK
   TEXT region PK
   TEXT season PK
   TEXT tech PK
   TEXT tod PK
   INTEGER vintage PK
   REAL fraction
   TEXT notes
}
limit_tech_input_split {
 TEXT input_comm PK
   TEXT operator PK
   INTEGER period PK
   TEXT region PK
   TEXT tech PK
   TEXT notes
   REAL proportion
}
limit_tech_input_split_annual {
 TEXT input_comm PK
   TEXT operator PK
   INTEGER period PK
   TEXT region PK
   TEXT tech PK
   TEXT notes
   REAL proportion
}
limit_tech_output_split {
 TEXT operator PK
   TEXT output_comm PK
   INTEGER period PK
   TEXT region PK
   TEXT tech PK
   TEXT notes
   REAL proportion
}
limit_tech_output_split_annual {
 TEXT operator PK
   TEXT output_comm PK
   INTEGER period PK
   TEXT region PK
   TEXT tech PK
   TEXT notes
   REAL proportion
}
linked_tech {
 TEXT emis_comm PK
   TEXT primary_region PK
   TEXT primary_tech PK
   TEXT driven_tech
   TEXT notes
}
loan_lifetime_process {
 TEXT region PK
   TEXT tech PK
   INTEGER vintage PK
   REAL lifetime
   TEXT notes
}
loan_rate {
 TEXT region PK
   TEXT tech PK
   INTEGER vintage PK
   TEXT notes
   REAL rate
}
metadata {
 TEXT element PK
   TEXT notes
   INTEGER value
}
metadata_real {
 TEXT element PK
   TEXT notes
   REAL value
}
myopic_efficiency {
 TEXT input_comm PK
   TEXT output_comm PK
   TEXT region PK
   TEXT tech PK
   INTEGER vintage PK
   INTEGER base_year
   REAL efficiency
   INTEGER lifetime
}
output_built_capacity {
 TEXT region PK
   TEXT scenario PK
   TEXT tech PK
   INTEGER vintage PK
   REAL capacity
   TEXT sector
}
sector_label {
 TEXT sector PK
   TEXT notes
}
output_cost {
 INTEGER period PK
   TEXT region PK
   TEXT scenario PK
   TEXT tech PK
   INTEGER vintage PK
   REAL d_emiss
   REAL d_fixed
   REAL d_invest
   REAL d_var
   REAL emiss
   REAL fixed
   REAL invest
   TEXT sector
   REAL var
}
output_curtailment {
 TEXT input_comm PK
   TEXT output_comm PK
   INTEGER period PK
   TEXT region PK
   TEXT scenario PK
   TEXT season PK
   TEXT tech PK
   TEXT tod PK
   INTEGER vintage PK
   REAL curtailment
   TEXT sector
}
output_dual_variable {
 TEXT constraint_name PK
   TEXT scenario PK
   REAL dual
}
output_emission {
 TEXT emis_comm PK
   INTEGER period PK
   TEXT region PK
   TEXT scenario PK
   TEXT tech PK
   INTEGER vintage PK
   REAL emission
   TEXT sector
}
output_flow_in {
 TEXT input_comm PK
   TEXT output_comm PK
   INTEGER period PK
   TEXT region PK
   TEXT scenario PK
   TEXT season PK
   TEXT tech PK
   TEXT tod PK
   INTEGER vintage PK
   REAL flow
   TEXT sector
}
output_flow_out {
 TEXT input_comm PK
   TEXT output_comm PK
   INTEGER period PK
   TEXT region PK
   TEXT scenario PK
   TEXT season PK
   TEXT tech PK
   TEXT tod PK
   INTEGER vintage PK
   REAL flow
   TEXT sector
}
output_flow_out_summary {
 TEXT input_comm PK
   TEXT output_comm PK
   INTEGER period PK
   TEXT region PK
   TEXT scenario PK
   TEXT tech PK
   INTEGER vintage PK
   REAL flow
   TEXT sector
}
output_net_capacity {
 INTEGER period PK
   TEXT region PK
   TEXT scenario PK
   TEXT tech PK
   INTEGER vintage PK
   REAL capacity
   TEXT sector
}
output_objective {
 TEXT objective_name
   TEXT scenario
   REAL total_system_cost
}
output_retired_capacity {
 INTEGER period PK
   TEXT region PK
   TEXT scenario PK
   TEXT tech PK
   INTEGER vintage PK
   REAL cap_early
   REAL cap_eol
   TEXT sector
}
output_storage_level {
 INTEGER period PK
   TEXT region PK
   TEXT scenario PK
   TEXT season PK
   TEXT tech PK
   TEXT tod PK
   INTEGER vintage PK
   REAL level
   TEXT sector
}
planning_reserve_margin {
 TEXT region PK
   REAL margin
   TEXT notes
}
ramp_down_hourly {
 TEXT region PK
   TEXT tech PK
   TEXT notes
   REAL rate
}
ramp_up_hourly {
 TEXT region PK
   TEXT tech PK
   TEXT notes
   REAL rate
}
reserve_capacity_derate {
 INTEGER period PK
   TEXT region PK
   TEXT season PK
   TEXT tech PK
   INTEGER vintage PK
   REAL factor
   TEXT notes
}
rps_requirement {
 TEXT notes
   INTEGER period
   TEXT region
   REAL requirement
   TEXT tech_group
}
tech_group {
 TEXT group_name PK
   TEXT notes
}
storage_duration {
 TEXT region PK
   TEXT tech PK
   REAL duration
   TEXT notes
}
tech_group_member {
 TEXT group_name PK
   TEXT tech PK
}
time_season {
 INTEGER period PK
   TEXT season PK
   INTEGER sequence PK
   TEXT notes
}
time_season_all {
 INTEGER period PK
   TEXT season PK
   INTEGER sequence PK
   TEXT notes
}
time_season_sequential {
 INTEGER period PK
   TEXT seas_seq PK
   TEXT season PK
   INTEGER sequence PK
   TEXT notes
   REAL num_days
}
time_season_to_sequential {
 INTEGER period PK
   TEXT seas_seq PK
   TEXT season PK
   INTEGER sequence PK
   TEXT notes
   REAL num_days
}
time_segment_fraction {
 INTEGER period PK
   TEXT season PK
   TEXT tod PK
   TEXT notes
   REAL segment_fraction
}
technology one or zero--0+ capacity_credit : has
time_period one or zero--0+ capacity_credit : has
technology_type 1--0+ technology : has
time_period_type one or zero--0+ time_period : has
technology one or zero--0+ capacity_factor_process : has
time_of_day one or zero--0+ capacity_factor_process : has
time_period one or zero--0+ capacity_factor_process : has
season_label one or zero--0+ capacity_factor_process : has
season_label one or zero--0+ capacity_factor_tech : has
time_period one or zero--0+ capacity_factor_tech : has
time_of_day one or zero--0+ capacity_factor_tech : has
technology one or zero--0+ capacity_factor_tech : has
technology one or zero--0+ capacity_to_activity : has
commodity_type one or zero--0+ commodity : has
time_period one or zero--0+ construction_input : has
commodity one or zero--0+ construction_input : has
technology one or zero--0+ construction_input : has
time_period one or zero--0+ cost_emission : has
commodity 1--0+ cost_emission : has
time_period 1--0+ cost_fixed : has
technology 1--0+ cost_fixed : has
time_period 1--0+ cost_fixed : has
technology one or zero--0+ cost_invest : has
time_period one or zero--0+ cost_invest : has
time_period 1--0+ cost_variable : has
time_period 1--0+ cost_variable : has
technology 1--0+ cost_variable : has
time_period one or zero--0+ demand : has
commodity one or zero--0+ demand : has
time_period one or zero--0+ demand_specific_distribution : has
season_label one or zero--0+ demand_specific_distribution : has
time_of_day one or zero--0+ demand_specific_distribution : has
commodity one or zero--0+ demand_specific_distribution : has
commodity one or zero--0+ efficiency : has
technology one or zero--0+ efficiency : has
time_period one or zero--0+ efficiency : has
commodity one or zero--0+ efficiency : has
time_period one or zero--0+ efficiency_variable : has
season_label one or zero--0+ efficiency_variable : has
time_of_day one or zero--0+ efficiency_variable : has
commodity one or zero--0+ efficiency_variable : has
technology one or zero--0+ efficiency_variable : has
time_period one or zero--0+ efficiency_variable : has
commodity one or zero--0+ efficiency_variable : has
commodity one or zero--0+ emission_activity : has
commodity one or zero--0+ emission_activity : has
technology one or zero--0+ emission_activity : has
time_period one or zero--0+ emission_activity : has
commodity one or zero--0+ emission_activity : has
commodity one or zero--0+ emission_embodied : has
technology one or zero--0+ emission_embodied : has
time_period one or zero--0+ emission_embodied : has
time_period one or zero--0+ emission_end_of_life : has
commodity one or zero--0+ emission_end_of_life : has
technology one or zero--0+ emission_end_of_life : has
technology one or zero--0+ end_of_life_output : has
commodity one or zero--0+ end_of_life_output : has
time_period one or zero--0+ end_of_life_output : has
time_period one or zero--0+ existing_capacity : has
technology one or zero--0+ existing_capacity : has
technology one or zero--0+ lifetime_process : has
time_period one or zero--0+ lifetime_process : has
time_period 1--0+ lifetime_survival_curve : has
technology 1--0+ lifetime_survival_curve : has
technology one or zero--0+ lifetime_tech : has
time_period one or zero--0+ limit_activity : has
operator 1--0+ limit_activity : has
time_period one or zero--0+ limit_activity_share : has
operator 1--0+ limit_activity_share : has
operator 1--0+ limit_annual_capacity_factor : has
time_period one or zero--0+ limit_annual_capacity_factor : has
technology one or zero--0+ limit_annual_capacity_factor : has
commodity one or zero--0+ limit_annual_capacity_factor : has
operator 1--0+ limit_capacity : has
time_period one or zero--0+ limit_capacity : has
time_period one or zero--0+ limit_capacity_share : has
operator 1--0+ limit_capacity_share : has
operator 1--0+ limit_degrowth_capacity : has
operator 1--0+ limit_degrowth_new_capacity : has
operator 1--0+ limit_degrowth_new_capacity_delta : has
commodity one or zero--0+ limit_emission : has
operator 1--0+ limit_emission : has
time_period one or zero--0+ limit_emission : has
operator 1--0+ limit_growth_capacity : has
operator 1--0+ limit_growth_new_capacity : has
operator 1--0+ limit_growth_new_capacity_delta : has
time_period one or zero--0+ limit_new_capacity : has
operator 1--0+ limit_new_capacity : has
operator 1--0+ limit_new_capacity_share : has
time_period one or zero--0+ limit_new_capacity_share : has
operator 1--0+ limit_resource : has
region one or zero--0+ limit_seasonal_capacity_factor : has
time_period one or zero--0+ limit_seasonal_capacity_factor : has
season_label one or zero--0+ limit_seasonal_capacity_factor : has
technology one or zero--0+ limit_seasonal_capacity_factor : has
operator 1--0+ limit_seasonal_capacity_factor : has
time_of_day one or zero--0+ limit_storage_level_fraction : has
technology one or zero--0+ limit_storage_level_fraction : has
time_period one or zero--0+ limit_storage_level_fraction : has
operator 1--0+ limit_storage_level_fraction : has
time_period one or zero--0+ limit_storage_level_fraction : has
season_label one or zero--0+ limit_storage_level_fraction : has
technology one or zero--0+ limit_tech_input_split : has
operator 1--0+ limit_tech_input_split : has
time_period one or zero--0+ limit_tech_input_split : has
commodity one or zero--0+ limit_tech_input_split : has
technology one or zero--0+ limit_tech_input_split_annual : has
operator 1--0+ limit_tech_input_split_annual : has
time_period one or zero--0+ limit_tech_input_split_annual : has
commodity one or zero--0+ limit_tech_input_split_annual : has
commodity one or zero--0+ limit_tech_output_split : has
operator 1--0+ limit_tech_output_split : has
time_period one or zero--0+ limit_tech_output_split : has
technology one or zero--0+ limit_tech_output_split : has
commodity one or zero--0+ limit_tech_output_split_annual : has
operator 1--0+ limit_tech_output_split_annual : has
time_period one or zero--0+ limit_tech_output_split_annual : has
technology one or zero--0+ limit_tech_output_split_annual : has
commodity one or zero--0+ linked_tech : has
technology one or zero--0+ linked_tech : has
technology one or zero--0+ linked_tech : has
technology one or zero--0+ loan_lifetime_process : has
time_period one or zero--0+ loan_lifetime_process : has
technology one or zero--0+ loan_rate : has
time_period one or zero--0+ loan_rate : has
technology one or zero--0+ myopic_efficiency : has
sector_label one or zero--0+ output_built_capacity : has
technology one or zero--0+ output_built_capacity : has
time_period one or zero--0+ output_built_capacity : has
time_period one or zero--0+ output_cost : has
sector_label one or zero--0+ output_cost : has
time_period one or zero--0+ output_cost : has
technology one or zero--0+ output_cost : has
time_of_day one or zero--0+ output_curtailment : has
commodity one or zero--0+ output_curtailment : has
technology one or zero--0+ output_curtailment : has
time_period one or zero--0+ output_curtailment : has
commodity one or zero--0+ output_curtailment : has
time_period one or zero--0+ output_curtailment : has
time_period one or zero--0+ output_curtailment : has
sector_label one or zero--0+ output_emission : has
time_period one or zero--0+ output_emission : has
commodity one or zero--0+ output_emission : has
technology one or zero--0+ output_emission : has
time_period one or zero--0+ output_emission : has
sector_label one or zero--0+ output_flow_in : has
time_period one or zero--0+ output_flow_in : has
season_label one or zero--0+ output_flow_in : has
time_of_day one or zero--0+ output_flow_in : has
commodity one or zero--0+ output_flow_in : has
technology one or zero--0+ output_flow_in : has
time_period one or zero--0+ output_flow_in : has
commodity one or zero--0+ output_flow_in : has
technology one or zero--0+ output_flow_out : has
time_period one or zero--0+ output_flow_out : has
commodity one or zero--0+ output_flow_out : has
sector_label one or zero--0+ output_flow_out : has
time_period one or zero--0+ output_flow_out : has
season_label one or zero--0+ output_flow_out : has
time_of_day one or zero--0+ output_flow_out : has
commodity one or zero--0+ output_flow_out : has
technology 1--0+ output_flow_out_summary : has
technology one or zero--0+ output_net_capacity : has
time_period one or zero--0+ output_net_capacity : has
sector_label one or zero--0+ output_net_capacity : has
time_period one or zero--0+ output_net_capacity : has
technology one or zero--0+ output_retired_capacity : has
time_period one or zero--0+ output_retired_capacity : has
sector_label one or zero--0+ output_retired_capacity : has
time_period one or zero--0+ output_retired_capacity : has
technology one or zero--0+ output_storage_level : has
time_period one or zero--0+ output_storage_level : has
sector_label one or zero--0+ output_storage_level : has
time_period one or zero--0+ output_storage_level : has
season_label one or zero--0+ output_storage_level : has
time_of_day one or zero--0+ output_storage_level : has
region one or zero--1 planning_reserve_margin : has
technology one or zero--0+ ramp_down_hourly : has
technology one or zero--0+ ramp_up_hourly : has
technology one or zero--0+ reserve_capacity_derate : has
time_period one or zero--0+ reserve_capacity_derate : has
season_label one or zero--0+ reserve_capacity_derate : has
time_period 1--0+ rps_requirement : has
tech_group 1--0+ rps_requirement : has
region 1--0+ rps_requirement : has
tech_group one or zero--0+ tech_group_member : has
technology one or zero--0+ tech_group_member : has
time_period one or zero--0+ time_season : has
season_label one or zero--0+ time_season : has
season_label one or zero--0+ time_season_all : has
time_period one or zero--0+ time_season_all : has
time_period one or zero--0+ time_season_sequential : has
season_label one or zero--0+ time_season_sequential : has
season_label one or zero--0+ time_season_to_sequential : has
time_period one or zero--0+ time_season_to_sequential : has
time_period one or zero--0+ time_segment_fraction : has
season_label one or zero--0+ time_segment_fraction : has
time_of_day one or zero--0+ time_segment_fraction : has

Temoa uses sqlite, a widely used, self-contained database system. Building a database first requires constructing a sql file, which is simply a text file that defines the structure of different database tables and includes the input data. The snippet below is from the time_period table used to define the test_system dataset:

 CREATE TABLE time_period
 (
   sequence INTEGER UNIQUE,
   period   INTEGER
       PRIMARY KEY,
   flag     TEXT
       REFERENCES time_period_type (label)
 );
 INSERT INTO "time_period" VALUES(1,2015,'e');
INSERT INTO "time_period" VALUES(2,2020,'f');
INSERT INTO "time_period" VALUES(3,2025,'f');
INSERT INTO "time_period" VALUES(4,2030,'f');
INSERT INTO "time_period" VALUES(5,2035,'f');

The first line creates the table. Lines 3-7 define the columns within this table. Note that period is the primary key. Therefore, the same time period cannot be entered twice; each name must be unique. Line 7, which helps define the flag column, declares a foreign key reference to the label column of the time_period_type table. As a result, if the user tries to enter a label in this table that does not exist in the time_period_type table, it will fail with an error. This foreign key reference ensures that the modeler doesn’t accidentally type the wrong label in this table. For context, there are two basic types of time periods in Temoa, e, which defines pre-existing periods, and f, which defines future time periods that are to be optimized.

This enforcement of names across tables using foreign keys helps immediately catch typos. As you can imagine, typos happen in plain text files and Excel when defining thousands of rows of data. Another big advantage of using databases is that the model run outputs are stored in separate database output tables. The outputs by model run are indexed by a scenario name, which makes it possible to perform thousands of runs, programatically store all the results, and execute arbitrary queries that instantaneously return the requested data.

Because some database table elements serve as foreign keys in other tables, we recommend that you populate input tables in the following order:

Group 1: labels used for internal database processing

commodity_type: Need to identify which type of commodity. Do NOT change these abbreviations.
technology_type: Need to identify which type of technology. Do NOT change these abbreviations. Categorizing and sub-categorizing can be done in the Technology table itself.
time_period_type: Used to distinguish which time periods are simply used to specify pre-existing vintages and which represent future optimization periods.

Group 2: sets used within Temoa

commodity: list of commodities used within the database
technology: list of technologies used within the database
time_period: list of both past and future time periods considered in the database
time_season: seasons modeled in the database (also contains segment fractions)
time_of_day: time of day segments modeled in the database

Group 3: parameters used to define processes within Temoa

metadata_real (global_discount_rate)
demand
demand_specific_distribution
efficiency
existing_capacity
capacity_factor_tech
capacity_factor_process (only if CF varies by vintage; overwrites capacity_factor_tech)
capacity_to_activity
cost_fixed
cost_invest
cost_variable
emission_activity
lifetime_tech
lifetime_process (only if LT varies by vintage; overwrites lifetime_tech)

Group 4: parameters used to define constraints within Temoa (non-exhaustive)

limit_activity
limit_capacity
limit_emission
limit_growth_capacity
limit_new_capacity
limit_resource
limit_tech_input_split
limit_tech_output_split

For help getting started, consider using the temoa tutorial <model_name> command to generate a template project or inspect the example SQL file at temoa/tutorial_assets/utopia.sql. To begin building your own database file, use temoa/db_schema/temoa_schema_v4.sql, which is a database file with the requisite structure but no data added. We recommend leaving the database structure intact, and simply adding data to the schema file, or constructing an empty database from the schema file and then using a script or database editor to import data. Once the sql file is complete, you can convert it into a binary sqlite file by installing sqlite3 and executing the following command:

$ sqlite3 my_database.sqlite < my_database.sql

Note

The command above using the < operator for input redirection is supported in Linux, macOS, and the Windows Command Prompt. Note that PowerShell does not support the Unix-style < redirection operator. In PowerShell, you can achieve the same result by piping the file content into sqlite3:

Get-Content my_database.sql | sqlite3 my_database.sqlite

Alternatively, you can load a SQL file from within the sqlite3 interactive interface using the .read command:

sqlite3 my_database.sqlite
sqlite> .read my_database.sql

When running on Windows, be sure to use the correct path separators (\) if you provide absolute paths to your files.

Now you can specify this database as the source for both input and output data in the config file.

Data Quality#

In addition to numerous internal checks, Temoa (optionally) employs two quality checks on data read in from the database. The outputs (actions and warnings) generated by these processes are reported in the log file for the run.

Both of the checks below can be run to QA data by running the model in CHECK mode and inspecting the log file. During CHECK mode runs, no solve is attempted on the model.

Price Checking#

The “price checker” reviews cost data in the 3 cost tables and considers technology lifetime. It screens for possible inconsistencies that would corrupt output quality. Larger models may have well over 100K cost entries and an overlooked investment cost for a particular vintage tech in a particular region could easily be overlooked. Price checks performed/reported:

Missing Costs (Check 0): This check looks for technologies that have no fixed/invest/variable costs at all. Other checks are more discriminating, so this check is only reported when Temoa is run in debug mode by using the -d flag on the run command.
Missing Fixed/Investment Costs (Check 1a): This check identifies technologies that are not flagged as uncapacitated with neither a fixed or investment cost associated. These might be problematic for solve because the model minimizes cost, so capacity in these technologies would be free. uncapacitated technologies have no capacity measure, so fixed/investment costs are prohibited for them and that is checked elsewhere.
Inconsistent Fixed/Investment Cost (Check 1b): This check looks for inconsistent application of fixed or base costs in the “base” or vintage year across all vintages and regions. So, if a tech has a fixed cost in some particular region and vintage year, but not in all, it will be flagged as a likely omission.
Inconsistent Fixed & Variable Costs (Check 2): This check identifies techs that have inconsistencies in the application of fixed - variable costs. Techs that have any fixed cost for a particular [region, tech, vintage] process, but do not have entries that match the variable cost entries for the same process are flagged, and vice-versa. This would hopefully identify an accidental omission of some of the fixed/var costs for processes that have at least 1 entry for either.
Lifetime Costing (Check 3): This check identifies costs that fall short or are missing during the process’s lifetime. If a process has a variable cost in any year during the lifetime, but not all years, it is flagged. Same for fixed cost.
Uncapacitated Tech Costs: Any technology flagged as uncapacitated will trigger warnings here if it has any fixed/invest costs.

Source Tracing#

Temoa works backwards from demands to identify chains of technologies required to meet the demand. Source Tracing is designed to ensure that this backward tracing from demands describes a proper commodity network without gaps that might allow intermediate commodities to be treated as a free “source” commodity. Further description of possible network problems is included in the Commodity Network section.

Source Tracing pre-builds the entire commodity network in each region-period contained in the data and analyzes it for “orphans” which likely represent gaps in the network that would lead to erroneous output data. The operation is enabled by tagging foundational commodities for which there are no predecessors as “source” commodities in the Commodity database table with an s tag. Orphans (or chains of orphans) on either the demand or supply side are reported and suppressed in the data to prevent network corruption. Additionally, Temoa performs cycle detection on the commodity network to identify circular dependencies that could lead to non-convergence or erroneous results. Users can configure the cycle detection behavior using the following settings:

cycle_count_limit: Limits the number of cycles reported in the log. A value of -1 allows unbounded detection, 0 causes the system to log an error on the first detected cycle and then suppresses further cycle reports for the remainder of the run (without terminating execution), and a positive integer sets a specific limit. Default is 100.
cycle_length_limit: Minimum length of cycles to report. This can be used to filter out small, expected circularities if necessary. Default is 1. The length limit is inclusive, so a cycle of length 1 is a self-loop, and a cycle of length n has n unique nodes.

Note that the myopic mode requires the use of Source Tracing to ensure accuracy as some orphans may be produced by endogenous decisions in myopic runs.

Database Construction

Contents

Database Construction#

Data Quality#

Price Checking#

Source Tracing#