This section details the fundamental components of the database table structure, as well as their relative roles. For our purposes, separations here are made at the schema-level. Sub
raw_*Raw data tables are the site of ingestion into the database from data sources. The first round of data integrity analysis is conducted on these tables. Their goal is to detect malformed data, gaps, failures in upload, and homoegenize format differences between data sources. For instance, it is known that data sources include the formats: json, text, csv. It is further known that some of these formats contain data that is not to be uploaded. The raw tables contain the target data sheared of this. All data in this table, excepting the created_on table are string values.
norm_*Data normalization involves the analysis of the raw tables. Data is interpreted at the second integrity level. The first integrity level addresses non-ingest of irrelevant data, failed upload of desirable data, and other malformations. Normalization involves translation of untyped (i.e. String by default) data into typed data. In other words, this is where integers become integers, floats become floats, dates become dates, etc. This is accordingly where we experience our problems with malformations of data, such as date format inconsistencies, inconsistencies in significant digits, special characters, etc. Once data has been normalized, it can be translated over to Stage tables.
Please note: the separation of Normalized and Stage data tables compartmentalizes the Ingest/Normalization cycle as separate from data that is in active use. This is a component of production-level design.
stg_*These tables contain the data that is being actively referenced by the system in its ongoing analysis. They are directly downstream of Normalized tables.
This is also where values are swapped out for FK from reference tables.
ana_*These tables contain data derived from use tables.
ref_*These tables are principally for reference material. This includes all fixed or near-fixed values, including data dictionaries and common reference points. Examples of the latter include NAICS 2017 and 2022 codes.
raw.ecn_2022_usraw.ecn_2022_coraw.ecn_2022_conorm.ecn_2022_usnorm.ecn_2022_conorm.ecn_2022_costg.ecn_2022_usCREATE TABLE stg.ecn_2022_us
(
id uuid NOT NULL DEFAULT gen_random_uuid(),
emp bigint,
emp_f text COLLATE pg_catalog."default",
emp_imp bigint,
emp_imp_f text COLLATE pg_catalog."default",
estab bigint,
estab_f text COLLATE pg_catalog."default",
firm bigint,
firm_f text COLLATE pg_catalog."default",
geo_id text COLLATE pg_catalog."default" NOT NULL,
geo_id_f text COLLATE pg_catalog."default",
name text COLLATE pg_catalog."default",
naics2022 text COLLATE pg_catalog."default",
naics2022_f text COLLATE pg_catalog."default",
naics2022_label text COLLATE pg_catalog."default",
payann bigint,
payann_f text COLLATE pg_catalog."default",
payann_imp bigint,
payann_imp_f text COLLATE pg_catalog."default",
payqtr1 bigint,
payqtr1_f text COLLATE pg_catalog."default",
rcptot bigint,
rcptot_f text COLLATE pg_catalog."default",
rcptot_imp bigint,
rcptot_imp_f text COLLATE pg_catalog."default",
taxstat text COLLATE pg_catalog."default",
taxstat_label text COLLATE pg_catalog."default",
typop text COLLATE pg_catalog."default",
typop_label text COLLATE pg_catalog."default",
year integer,
us text COLLATE pg_catalog."default",
CONSTRAINT ecn_2022_us_pkey PRIMARY KEY (id)
)
stg.ecn_2022_stCREATE TABLE stg.ecn_2022_st
(
id uuid NOT NULL DEFAULT gen_random_uuid(),
emp bigint,
emp_f text COLLATE pg_catalog."default",
emp_imp bigint,
emp_imp_f text COLLATE pg_catalog."default",
estab bigint,
estab_f text COLLATE pg_catalog."default",
firm bigint,
firm_f text COLLATE pg_catalog."default",
geo_id text COLLATE pg_catalog."default" NOT NULL,
geo_id_f text COLLATE pg_catalog."default",
name text COLLATE pg_catalog."default",
naics2022 text COLLATE pg_catalog."default",
naics2022_f text COLLATE pg_catalog."default",
naics2022_label text COLLATE pg_catalog."default",
payann bigint,
payann_f text COLLATE pg_catalog."default",
payann_imp bigint,
payann_imp_f text COLLATE pg_catalog."default",
payqtr1 bigint,
payqtr1_f text COLLATE pg_catalog."default",
rcptot bigint,
rcptot_f text COLLATE pg_catalog."default",
rcptot_imp bigint,
rcptot_imp_f text COLLATE pg_catalog."default",
taxstat text COLLATE pg_catalog."default",
taxstat_label text COLLATE pg_catalog."default",
typop text COLLATE pg_catalog."default",
typop_label text COLLATE pg_catalog."default",
year integer,
us text COLLATE pg_catalog."default",
CONSTRAINT ecn_2022_us_pkey PRIMARY KEY (id)
)
stg.ecn_2022_coCREATE TABLE stg.ecn_2022_co
(
id uuid NOT NULL DEFAULT gen_random_uuid(),
emp bigint,
emp_f text COLLATE pg_catalog."default",
emp_imp bigint,
emp_imp_f text COLLATE pg_catalog."default",
estab bigint,
estab_f text COLLATE pg_catalog."default",
firm bigint,
firm_f text COLLATE pg_catalog."default",
geo_id text COLLATE pg_catalog."default" NOT NULL,
geo_id_f text COLLATE pg_catalog."default",
name text COLLATE pg_catalog."default",
naics2022 text COLLATE pg_catalog."default",
naics2022_f text COLLATE pg_catalog."default",
naics2022_label text COLLATE pg_catalog."default",
payann bigint,
payann_f text COLLATE pg_catalog."default",
payann_imp bigint,
payann_imp_f text COLLATE pg_catalog."default",
payqtr1 bigint,
payqtr1_f text COLLATE pg_catalog."default",
rcptot bigint,
rcptot_f text COLLATE pg_catalog."default",
rcptot_imp bigint,
rcptot_imp_f text COLLATE pg_catalog."default",
taxstat text COLLATE pg_catalog."default",
taxstat_label text COLLATE pg_catalog."default",
typop text COLLATE pg_catalog."default",
typop_label text COLLATE pg_catalog."default",
year integer,
us text COLLATE pg_catalog."default",
CONSTRAINT ecn_2022_us_pkey PRIMARY KEY (id)
)
Reference: ECN