# Databricks Naming Modelling Guidelines > Guidelines for naming conventions, metadata columns, and modelling approaches in the Databricks area of the Data Intelligence Platform. Use this skill when designing, naming, or modelling datasets within Databricks, ensuring consistency and adherence to NZTA standards. Use when this capability is needed. - Skill: `tomevault-io/databricks-naming-modelling-guidelines` (Agent Skill, multi-file: 2 files) - Install (CLI): `npx skillmds@latest add tomevault-io/databricks-naming-modelling-guidelines` - Raw SKILL.md: https://api.skillmd.com/api/skills/tomevault-io/databricks-naming-modelling-guidelines/raw - Safety review: pending (external: skill-scanner PASS, skillspector PASS) - Works with: Claude Code, Claude.ai, OpenAI Codex - Category: Coding & Dev Tools - Author: tomevault-io (https://skillmd.com/u/tomevault-io) - Updated: 2026-09-17 - Page: https://skillmd.com/skills/tomevault-io/databricks-naming-modelling-guidelines --- # Databricks Naming & Modelling Guidelines These guidelines ensure consistent naming, metadata management, and modeling across Databricks within NZTA’s Data Intelligence Platform. They are intended for anyone performing data engineering or modeling in Databricks. ## 1. General Guidelines - **Metadata timestamps:** Always use UTC for metadata columns (e.g., load time, update time) to prevent confusion with time zones or daylight savings. - **Snake case:** Prefer `snake_case` for naming, except when object names derive directly from a source system. - **No plurals or abbreviations:** Avoid plurals and nonstandard abbreviations in table and column names. ## 2. Metadata Columns - Prefix metadata columns with double underscores, and use all uppercase (e.g., `__LOAD_AT`). - Key columns for metadata tracking include: - `__EXTRACT_AT` (timestamp, mandatory): When data was extracted (UTC) - `__LOAD_AT` (timestamp, mandatory): When data was loaded (UTC) - `__RECORD_SOURCE` (string, mandatory): Source or filename of record - Others, as required: `__SOURCE_FILE_PATH`, `__SEQUENCE_NUMBER`, `__EXTRACT_RUN_ID`, `__RESCUED_DATA` | Column | Type | Mandatory | Notes | |--------------------|-----------|-----------|------------------------------------------------------| | __EXTRACT_AT | timestamp | Y | Extraction time (UTC) | | __LOAD_AT | timestamp | Y | Load time (UTC) | | __RECORD_SOURCE | string | Y | Record/source identifier | | __SOURCE_FILE_PATH | string | N | Path to source file | | __SEQUENCE_NUMBER | integer | N | Used for tracking duplicates/logs | | __EXTRACT_RUN_ID | string | N | Pipeline run ID | | __RESCUED_DATA | string | N | Only for streaming tables | ## 3. Table & Column Naming ### Bronze Layer (Raw Source) - Table names match the source object/file (lowercase enforced). - If source lacks a specific name, use snake_case. Example: `assessmentcontract_type`. - Keep column names as per source (including case), unless absent, then use snake_case. - Do not use plurals/abbreviations (e.g., avoid `assessments`, `adj_reasons`). ### Copper Layer (Historized Source) - Table and column names match source. Minimal business rules/calculations. - Track all changes (SCD2). Recommended to use Lakeflow Declarative Pipelines. - Add metadata columns like `__START_AT`, `__END_AT`. ### Silver Layer (Business Modeling) - Schemas: Use `silver_[business_area]` for the schema name (e.g., `silver_purchasing`). - Tables: Use snake_case table names within the silver schema; use the `stg_` prefix for staging tables where needed. - Use snake_case for both objects and columns. - Rename columns for business clarity (e.g., `contract_name` not `name`). - Avoid system-specific/non-standard abbreviations. - Flags: Use `boolean` type, prefix with `is_` or `has_`. - Spelling: Use NZ English (e.g., `centre`, not `center`). ### Gold Layer (Analytical) - Star schema is preferred for analytical modeling. - Table prefixes: - Dimensions: `dim_` - Facts: `fact_` - Snapshots: `snapshot_` - Summaries: `summary_` - Surrogate keys: `dim__key` (e.g., `dim_camera_site_key`). - Unknown row: Add default unknown row for dimensions (`0` for integer, `Unknown` for string). ## 4. Column Suffixes - For date/time columns, use clear suffixes: `_date`, `_datetime`, `_time`, `_datetime_utc`. - Business dates/times: Store in NZ Standard Time unless specified as UTC. ## 5. Edge Cases - **Log files/unstructured data:** Add row numbers and use metadata columns to manage duplicates or variable formats. - **Role-playing dimensions:** Use views for date dimensions playing multiple roles. - **Fact-less fact tables:** Use for many-to-many relationships; these have no measures. ## 6. Example Naming Conventions **Dimension Table:** ``` dim_camera_site_key site_code site_name stage_start_datetime stage_end_datetime __LOAD_AT __UPDATE_AT __START_AT __END_AT __IS_ACTIVE __IS_LATEST __CHANGE_HASH ``` **Fact Table:** ``` fact_incident_stage_history incident_id stage_code stage_name stage_start_datetime stage_end_datetime dim_incident_key dim_stage_key __LOAD_AT __UPDATE_AT ``` **Metadata Columns:** ``` __EXTRACT_AT __LOAD_AT __RECORD_SOURCE ``` **Date Dimension Example:** ``` dim_date date_key (YYYYMMDD) date month year week __LOAD_AT ``` ## 7. Source Source page version: 11 --- > Source: [aiden-liu/confluence_page_to_instructions_skills](https://github.com/aiden-liu/confluence_page_to_instructions_skills) — distributed by [TomeVault](https://tomevault.io).