Airline Revenue Analytics data model
Kimball warehouse for airline revenue management: transaction-grain bookings, a daily flight-leg periodic snapshot for load factor and RASM, and transaction-grain ancillary sales over conformed passenger/flight/route/fare-class/airport/aircraft dimensions. Passenger PII is classified and masked.
Airline commercial analytics covering ticketed bookings, daily flight-leg load-factor snapshots, and ancillary product sales over conformed passenger, flight, route, fare-class, airport, and aircraft dimensions — a Kimball star schema with SCD2 passenger history and regulated PII (passport number, frequent-flyer number) masked. It is a Kimball dimensional model: fact tables at a declared grain surrounded by conformed dimensions, ready for BI tools and a semantic layer.
- 11
- tables
- 115
- columns
- 15
- relationships
- 7
- PII columns
Opening the template creates a project with the ERD, platform-native DDL, a dbt project and documentation already generated (recommended platform: Snowflake). Edit it in plain English or on the canvas, then ship it to any supported warehouse.
Subject areas
Design decisions
Flight-leg snapshot separate from bookings
Load factor and revenue-per-available-seat-mile are semi-additive leg-level metrics computed daily, independent of booking volume and timing.
Two date roles on the booking fact
Booking date and travel date differ and drive different analyses (lead time, advance-purchase curves); both reference dim_date via a role-playing date key.
Ancillary sales as a separate fact
Ancillary revenue (bags, seats, meals) has its own product dimension and grain and is increasingly material; isolating it keeps the booking fact clean and supports unbundling analysis.
Masking on passport and frequent-flyer numbers
GDPR requires restricting access to government and loyalty identifiers; passport numbers are fully masked and FF numbers partially masked.
Tables in the Airline Revenue Analytics model
Every table with its grain, columns, types and keys. PK marks a primary key, BK a business key and PII a column classified as personal data, which gets a masking strategy by default.
Fact tables (3)
fct_booking
Grain: one row per ticketed booking segment (transaction)
Transaction-grain booking fact — one row per ticketed segment.
| Column | Type | Keys |
|---|---|---|
| booking_key | bigint | PK |
| date_key | bigint | |
| passenger_key | bigint | |
| flight_key | bigint | |
| route_key | bigint | |
| fare_class_key | bigint | |
| travel_date_key | bigint | |
| pnr_code | varchar | |
| ticket_number | varchar | |
| base_fare | decimal | |
| taxes_amount | decimal | |
| surcharge_amount | decimal | |
| total_fare | decimal | |
| miles_earned | integer | |
| seat_count | smallint | |
| booking_lead_time_days | integer | |
| channel | varchar | |
| is_award_ticket | boolean | |
| is_cancelled | boolean |
fct_flight_leg_snapshot
Grain: one row per flight leg per departure day (periodic snapshot)
Daily flight-leg snapshot — load factor is semi-additive.
| Column | Type | Keys |
|---|---|---|
| date_key | bigint | |
| flight_key | bigint | |
| route_key | bigint | |
| aircraft_key | bigint | |
| origin_airport_key | bigint | |
| destination_airport_key | bigint | |
| seats_available | smallint | |
| seats_sold | smallint | |
| passengers_boarded | smallint | |
| no_show_count | smallint | |
| load_factor | decimal | |
| leg_revenue | decimal | |
| revenue_per_seat_mile | decimal | |
| on_time_flag | boolean | |
| departure_delay_minutes | integer |
fct_ancillary_sale
Grain: one row per ancillary product sale (transaction)
Transaction-grain ancillary revenue (bags, seats, meals, lounge).
| Column | Type | Keys |
|---|---|---|
| ancillary_sale_key | bigint | PK |
| date_key | bigint | |
| passenger_key | bigint | |
| flight_key | bigint | |
| ancillary_type_key | bigint | |
| ancillary_order_id | varchar | |
| quantity | smallint | |
| unit_price | decimal | |
| gross_amount | decimal | |
| discount_amount | decimal | |
| net_amount | decimal | |
| is_bundled | boolean |
Dimension tables (7)
dim_date
Grain: one row per calendar day
| Column | Type | Keys |
|---|---|---|
| date_key | bigint | PK |
| full_date | date | BK |
| day_of_week | varchar | |
| month_number | smallint | |
| quarter | smallint | |
| year | smallint | |
| is_weekend | boolean | |
| is_holiday | boolean | |
| travel_season | varchar |
dim_passenger
Grain: one row per passenger per SCD2 version
SCD type 2
| Column | Type | Keys |
|---|---|---|
| passenger_key | bigint | PK |
| passenger_id | varchar | BK |
| first_name | varchar | PII |
| last_name | varchar | PII |
| varchar | PII | |
| phone | varchar | PII |
| date_of_birth | date | PII |
| passport_number | varchar | PII |
| nationality | varchar | |
| country_of_residence | varchar | |
| frequent_flyer_number | varchar | PII |
| loyalty_tier | varchar | |
| lifetime_miles | bigint | |
| is_corporate_traveler | boolean |
dim_flight
Grain: one row per scheduled flight
| Column | Type | Keys |
|---|---|---|
| flight_key | bigint | PK |
| flight_id | varchar | BK |
| flight_number | varchar | |
| carrier_code | varchar | |
| carrier_name | varchar | |
| is_codeshare | boolean | |
| operating_carrier | varchar | |
| service_type | varchar |
dim_route
Grain: one row per origin-destination route
| Column | Type | Keys |
|---|---|---|
| route_key | bigint | PK |
| route_id | varchar | BK |
| origin_airport_code | varchar | |
| destination_airport_code | varchar | |
| origin_region | varchar | |
| destination_region | varchar | |
| distance_miles | integer | |
| is_international | boolean | |
| haul_type | varchar |
dim_fare_class
Grain: one row per fare class
| Column | Type | Keys |
|---|---|---|
| fare_class_key | bigint | PK |
| fare_class_code | varchar | BK |
| fare_class_name | varchar | |
| cabin | varchar | |
| booking_class | varchar | |
| is_refundable | boolean | |
| is_changeable | boolean | |
| baggage_allowance_kg | smallint |
dim_airport
Grain: one row per airport
| Column | Type | Keys |
|---|---|---|
| airport_key | bigint | PK |
| airport_code | varchar | BK |
| airport_name | varchar | |
| city | varchar | |
| country | varchar | |
| region | varchar | |
| timezone | varchar | |
| is_hub | boolean |
dim_aircraft
Grain: one row per aircraft type
| Column | Type | Keys |
|---|---|---|
| aircraft_key | bigint | PK |
| aircraft_id | varchar | BK |
| aircraft_type | varchar | |
| manufacturer | varchar | |
| total_seats | smallint | |
| economy_seats | smallint | |
| business_seats | smallint | |
| first_seats | smallint | |
| range_miles | integer |
Lookup tables (1)
dim_ancillary_type
Grain: one row per ancillary product type
| Column | Type | Keys |
|---|---|---|
| ancillary_type_key | bigint | PK |
| ancillary_code | varchar | BK |
| ancillary_name | varchar | |
| ancillary_category | varchar |
Relationships
Each relationship is a foreign key on the first table referencing the second.
- fct_booking.date_key → dim_date (many-to-one)
- fct_booking.passenger_key → dim_passenger (many-to-one)
- fct_booking.flight_key → dim_flight (many-to-one)
- fct_booking.route_key → dim_route (many-to-one)
- fct_booking.fare_class_key → dim_fare_class (many-to-one)
- fct_flight_leg_snapshot.date_key → dim_date (many-to-one)
- fct_flight_leg_snapshot.flight_key → dim_flight (many-to-one)
- fct_flight_leg_snapshot.route_key → dim_route (many-to-one)
- fct_flight_leg_snapshot.aircraft_key → dim_aircraft (many-to-one)
- fct_flight_leg_snapshot.origin_airport_key → dim_airport (many-to-one)
- fct_flight_leg_snapshot.destination_airport_key → dim_airport (many-to-one)
- fct_ancillary_sale.date_key → dim_date (many-to-one)
- fct_ancillary_sale.passenger_key → dim_passenger (many-to-one)
- fct_ancillary_sale.flight_key → dim_flight (many-to-one)
- fct_ancillary_sale.ancillary_type_key → dim_ancillary_type (many-to-one)
Related templates
All templates →From requirements to AI-ready context, in one platform.
Describe your business in a conversation, start from a template or connect your own database. Your first model is ready in minutes.