UndercastAI Data Marketplace
Instant access to high-quality datasets to enhance your journey from 0 to GenAI...
UndercastAI currently offers two tiers of listings in the Snowflake Marketplace:
Data Essentials is free to use and includes common core metrics used in analyses
Data Essentials + has a free trial and includes access to more complex dataset integrations that have been cleansed and validated for analyses
Data Essentials Documentation
DIM_DATE
The DIM_DATE table is a vital component in any data model where time-based analysis is essential. It is designed to support a comprehensive range of date-related dimensions necessary for effective data analysis across various time periods. As a core table in the data warehouse architecture, it facilitates an in-depth examination of trends and patterns over time, enhancing data-driven decision-making. This table includes a persistent and uninterrupted sequence of dates that spans the entire range needed for detailed analysis. It is enriched with numerous fields that enable precise data segmentation and filtering according to a diverse set of temporal logic. Specific attributes include:
Basic Date Components
- Full Date: Complete date entry.
- Year: Year part of the date.
- Month: Month part of the date.
- Day: Day part of the date.
Week and Day Calculations
- Day of the Week: Categorizes data by day of the week.
- Day of the Year: Categorizes data by day of the year.
Quarterly Analysis
- Quarter of the Year: Identifies the quarter of the year.
- Quarter Start Flag: Indicates the start of a quarter.
- Quarter End Flag: Indicates the end of a quarter.
Monthly and Yearly Flags
- Month Start Flag: Indicates the beginning of a month.
- Month End Flag: Indicates the end of a month.
- Year Start Flag: Indicates the beginning of a year.
- Year End Flag: Indicates the end of a year.
Leap Year Identification
- Leap Year Flag: Boolean flag to identify leap years.
Fiscal Year and Quarter
- Fiscal Year: Fiscal year part of the date.
- Fiscal Quarter: Fiscal quarter part of the date.
- Fiscal Year-Quarter: Composite field combining fiscal year and quarter for simplified reporting.
DIM_TIME
Table: DIM_TIME
The DIM_TIME table is essential for any data model that requires detailed time-based analysis. It supports a wide range of time-related dimensions necessary for effective data analysis across different periods of the day. This table facilitates a granular examination of trends and patterns based on various time attributes, enhancing data-driven decision-making. Specific attributes include:
Basic Time Components
- TIME_ID: Unique identifier for each time record.
- TIME_OF_DAY: A textual representation of the time of day, such as Morning, Afternoon, Evening, etc.
- HOUR: The hour component of the time, ranging from 0 to 23.
- MINUTE: The minute component of the time, ranging from 0 to 59.
Detailed Time Segments
- QUARTER_HOUR: A textual representation of the quarter hour, such as 00, 15, 30, 45.
- DAYTIME_NAME: A textual representation of a part of the day, such as Early Morning, Late Night, etc.
- DAY_NIGHT: A textual representation indicating whether it is day or night.
Indexes
- Primary Key: TIME_ID
Usage
This table is typically used to provide time-related context and granularity in data analysis. It can be joined with fact tables to enrich data with detailed time attributes, enabling more precise temporal analysis and reporting.
Data Essentials + Documentation
DIM_ZIPCODE
The DIM_ZIP_CODE table is a vital part of any data model involving postal code-based analysis. It supports a comprehensive range of ZIP code-related dimensions necessary for effective data analysis across different postal areas. This table facilitates detailed examination of trends and patterns based on postal codes, enhancing data-driven decision-making at the local level. Specific attributes include:
Basic ZIP Code Components
- ZIP Code ID: Unique identifier for each ZIP code.
- ZIP Code: The postal code itself.
Hierarchical Data
- Place ID: Identifier linking the ZIP code to a specific place.
- County: The county in which the ZIP code is located.
- State: The state in which the ZIP code is located.
Geographical Coordinates
- Latitude: The latitude of the ZIP code area.
- Longitude: The longitude of the ZIP code area.
Additional Attributes
- ZIP Code Type: Type of ZIP code (e.g., standard, P.O. box, military).
- Time Zone: The time zone of the ZIP code area.
DIM_PLACE
The DIM_PLACE table is a vital component in any data model where geographical analysis is essential. It supports a comprehensive range of place-related dimensions necessary for effective data analysis across various locations. As a core table in the data warehouse architecture, it enables detailed examination of trends and patterns based on geographical data, enhancing location-based decision-making. This table includes a persistent and unique sequence of place identifiers and is enriched with numerous fields that enable precise data segmentation and filtering according to various geographical hierarchies. Specific attributes include:
Basic Place Components
- GEOID_PLACE: Place geoid code, a common code across census data composed of a 2-digit state code and a census code from the source. (VARCHAR)
- PLACE_NAME: Official name of the place. (VARCHAR)
- AREA_NAME: Name of the area. (VARCHAR)
County Information
- COUNTY_NAME_PRIMARY: Primary county associated with the place. (VARCHAR)
- COUNTY_NUMERIC_PRIMARY: Primary county code. (VARCHAR)
- COUNTY_NAME_LIST: List of counties associated with the place. (ARRAY)
State Information
- STATE_NAME: Name of the state. (VARCHAR)
- STATE_CODE: Standard abbreviation for the state. (VARCHAR)
- STATE_FIPS_CODE: FIPS code of the state. (VARCHAR)
ZIP Code Information
- GEOID_ZCTA_PRIMARY: Census ZCTA (primary). (VARCHAR)
- ZIPCODE: Postal code of the place. (VARCHAR)
- GEOID_ZCTA_LIST: List of ZCTA codes associated with the place. (ARRAY)
Geographical Coordinates
- PRIM_LAT_DMS: Primary latitude in degrees-minutes-seconds. (VARCHAR)
- PRIM_LONG_DMS: Primary longitude in degrees-minutes-seconds. (VARCHAR)
- PRIM_LAT_DEC: Primary latitude in decimal degrees. (NUMBER)
- PRIM_LONG_DEC: Primary longitude in decimal degrees. (NUMBER)
Area Information
- AREALAND_PLACE: Land area of the place in square meters (as of 2020). (VARCHAR)
- AREAWATER_PLACE: Water area of the place in square meters (as of 2020). (VARCHAR)
DIM_STATE
The DIM_STATE table is crucial for data models requiring state-level analysis. It supports a comprehensive range of state-related dimensions necessary for effective data analysis across different states. This table enhances data-driven decision-making by enabling detailed examination of trends and patterns at the state level. Specific attributes include:
Basic State Components
- STATE_CODE: State postal abbreviation. (VARCHAR)
- Example: “CA” for California.
- STATE_FIPS_CODE: State FIPS code, a unique identifier assigned to each state by the federal government. (VARCHAR)
- Example: “06” for California.
- STATENS: State NS code, a unique identifier for each state. (VARCHAR)
- Example: A unique alphanumeric code specific to the state.
- STATE_NAME: Name of the state. (VARCHAR)
- Example: “California”.