Modelling & Technical Conventions

These are the structural conventions that apply across the whole schema. The data-model reference pages (Tables, Columns, Relationships, Orphan Tables) assume this vocabulary. For business terminology, see the Business Glossary.

Term	Definition	Analyst Notes
Star / galaxy schema	A dimensional model where numeric, per-event fact tables reference descriptive dimension tables. A galaxy (fact-constellation) schema has multiple fact tables sharing common dimensions.	The warehouse is a galaxy schema — expect several fct_* tables sharing dimensions like dim_date and dim_customer.
Fact table (fct_*)	An event or measurement at a fine grain — e.g. fct_checkin, fct_contract, fct_revenue. Holds measures (amounts, counts) and foreign keys to dimensions.	Facts sit at the centre of joins. Most carry one or more *_date_id keys and *_id foreign keys to dimensions.
Dimension table (dim_*)	Descriptive context that facts reference — e.g. dim_customer, dim_date, dim_rate, dim_organization_unit.	Filter archived = FALSE (or the table's equivalent) unless you deliberately want historical/deleted rows.
Bridge table (bridge_*)	Resolves a many-to-many relationship between two tables — e.g. bridge_customer_to_customer_code, bridge_revenue_to_purchased_contingent.	Some bridges are hard-deleted on deactivation and keep no history (see Discount Campaign).
Grain	The level of detail one row represents.	Facts here are typically at the finest, unaggregated per-event grain. fct_invoice is one row per invoice position, not per invoice.
*_id (surrogate / natural key)	Primary and foreign keys. A fact's <something>_id joins to the matching dimension's primary key.	Columns ending in _id are keys. NULL keys carry meaning (e.g. NULL employee_id = online self-signup).
date_id	An integer date key in YYYYMMDD form used to join facts to dim_date.	Convert the current date with CAST(TO_CHAR(CURRENT_DATE, 'YYYYMMDD') AS INTEGER). Facts carry role-specific keys like start_date_id, cancellation_date_id, accounting_date_id, checkin_date_id.
dim_date	The calendar dimension providing date, year_month, weekday_name, and day_number_of_week.	Join here for time-based grouping and correct weekday ordering (use day_number_of_week).
Anonymization / privacy masking	Source-system privacy settings are applied in the warehouse; some customer_id values may be NULL or masked.	On fct_checkin, a masked row has NULL customer_id when has_privacy_config = TRUE. Account for this in distinct-member counts.
Deprecated	A table or column flagged for removal. It remains available for a transition period, announced in the Changelog.	Prefer the replacement noted in the changelog; avoid building new work on deprecated objects.
Late-binding view (WITH NO SCHEMA BINDING)	A Redshift view that does not depend on its underlying tables, so those tables can be dropped and recreated by the ETL without breaking the view.	Explains why some objects survive ETL rebuilds.
Orphan table	A table with neither parent nor child foreign-key relationships — it stands alone in the schema. See the Data Model (Orphan Tables view).	Not necessarily a problem — many reference/lookup tables are intentionally standalone.

---

Critical SQL Rules

These rules apply across all queries against the data warehouse. Forgetting any of them is the most common source of incorrect results.

#	Rule
1	Always filter archived records: WHERE archived = FALSE on every table that has it
2	Always filter reversed contracts: WHERE is_reversed = FALSE on fct_contract and fct_contract_voucher
3	Date format: Most *_date_id columns are integers in YYYYMMDD format. Convert: CAST(TO_CHAR(CURRENT_DATE, 'YYYYMMDD') AS INTEGER)
4	NULL customer_id on check-ins: Privacy-protected records have NULL customer_id when has_privacy_config = TRUE
5	NULL employee_id on contracts: NULL means online self-signup (no salesperson)
6	Revenue: accrual vs cash: Use fct_revenue for P&L, fct_revenue_cash_based for cash flow
7	Member count = CONTRACT type only: Never count MODULE or FLAT_FEE contracts as members