SQL Project Setup: Git Repository, Naming Conventions & Database Initialization

Building an enterprise-grade SQL Data Warehouse requires foundational structure long before writing the first transformation query. A well-architected data engineering project rests on structured epic task management, standardized naming conventions, robust Git version control, and clean database initialization scripts.

1. Project Planning & Layered Task Breakdown

A modular data warehouse architecture typically separates data lifecycle stages into three distinct layers: Bronze, Silver, and Gold. To ensure clear execution, work is organized into high-level project Epics, with each epic containing a repeatable sequence of engineering tasks.

For every layer built within the data warehouse, the pipeline follows five standardized engineering tasks:

1. Analysis: Inspect source schemas, inspect data types, and map transformation rules.
2. Coding: Write DDL and DML scripts (table definitions, views, stored procedures).
3. Testing: Validate data integrity, surrogate keys, null constraints, and row counts.
4. Documentation: Maintain data catalog entries, script headers, and architecture descriptions.
5. Git Commit: Version-control code changes to preserve production reproducibility.

2. The Necessity of Project-Wide Naming Conventions

Real-Life Analogy

If you leave project naming rules open without strict guidelines, developers will naturally use their own personal coding styles. One developer creates dimension_customers (all lowercase with underscores), another creates DimensionProducts (PascalCase), and a third creates dim_categories (abbreviated prefix). Midway through the project, the schema becomes chaotic, forcing the team to run expensive, risky refactoring tasks just to rename database objects.

Establishing strict naming conventions at project inception eliminates ambiguities, unifies code reviews, and prevents technical debt.

Core Rules & Standards

• Casing Standard: Use snake_case across all database entities (tables, views, columns, procedures). Words are completely lowercased and separated by underscores (customer_info).
• Language Consistency: Standardize on English for all object identifiers and documentation across the codebase.
• Avoid Reserved Keywords: Never name an entity using ANSI SQL or T-SQL reserved keywords (e.g., naming a table table, select, or user).

3. Standardized Naming Conventions by Layer

Object naming strategies differ depending on whether the data is raw, standardized, or modeled for business intelligence reporting.

Gold Layer Category Prefixes

• dim_: Dimensional tables containing descriptive context (e.g., dim_customers, dim_products).
• fact_: Fact tables containing quantitative metrics and foreign key references (e.g., fact_sales).
• agg_: Aggregated/summary tables pre-built for reporting performance (e.g., agg_customer_sales_monthly).

4. Column, Metadata, and Script Naming Standards

In addition to table structures, fine-grained object types (surrogate keys, technical engineering metadata, and database scripts) require formal naming standards.

Surrogate Keys

Surrogate keys in the Gold dimensional model follow the pattern <table_name>_key.

• Example: customer_key (surrogate primary key for dim_customers).

Technical & Metadata Columns

Data engineering teams frequently append system metadata columns to track ETL runtime execution. To immediately distinguish technical metadata from business data payload columns, use the mandatory dw_ prefix.

• Example: dw_load_date, dw_updated_at, dw_source_file.

Conceptually: If any developer or BI Analyst sees a column starting with dw_, they instantly recognize it as internal infrastructure metadata injected by the data pipeline rather than raw transactional data.

Stored Procedures & ETL Automation Scripts

Stored procedures responsible for loading warehouse layers follow the functional pattern load_<target_layer>.

• Examples: load_bronze, load_silver, load_gold.

5. Setting Up Version Control with Git

Git acts as the central repository for infrastructure code, DDL scripts, ETL pipelines, and project documentation.

Benefits of Git for Data Projects

• Auditability & Rollbacks: Revert to previous working SQL scripts if a migration script introduces breaking changes.
• Collaboration: Enables multiple engineers to work on isolated features/layers simultaneously.
• Portfolio Showcase: A structured GitHub project displaying clean SQL code, setup scripts, and a professional README.md acts as a public showcase of data engineering capabilities.

Repository Configuration Settings

• Repository Name: SQL Data Warehouse Project
• Visibility: Public
• License: MIT License (Permits downstream reuse, modification, and open distribution)
• Initial File: Automatically initialize with a standard README.md

6. Repository Architecture and Folder Structure

A production-ready data warehouse repository separates source code, documentation, raw data samples, and tests into dedicated directories.

Structuring the README.md File

Written using Markdown (.md), the repository README acts as the project landing page. A thorough data engineering README includes:

1. Title & Overview: High-level system design explanation.
2. Project Requirements: Prerequisites (e.g., SQL Server version, SSMS, Git).
3. Data Architecture Flow: Explanation of Bronze, Silver, and Gold layers.
4. Licensing & Author Info: Usage rights and maintainer details.

7. Database & Schema Initialization in SQL Server (T-SQL)

When initializing a Microsoft SQL Server data warehouse, execution must shift between system context databases (master) and the target relational warehouse database.

SQL Server Database Creation Flow
   |
   | 1. Switch context to master system DB
   v
Master Database
   |
   | 2. Execute CREATE DATABASE DataWarehouse
   v
DataWarehouse DB Created
   |
   | 3. Switch context to DataWarehouse
   v
Target Warehouse Context
   |
   | 4. Execute CREATE SCHEMA for bronze, silver, and gold
   v
Architectural Layers Established (bronze, silver, gold)

Basic Creation Commands

To create the database and switch context in SQL Server Management Studio (SSMS):

-- Step 1: Switch to the master system database
USE master;
GO

-- Step 2: Create the target Data Warehouse database
CREATE DATABASE DataWarehouse;
GO

-- Step 3: Switch context to the new Data Warehouse
USE DataWarehouse;
GO

8. Navigating T-SQL Batch Separators (GO) and Schema Creation

In SQL Server (T-SQL), CREATE SCHEMA statements must be executed as the first statement in a query batch. Attempting to create multiple schemas in a single script batch without proper batch separators triggers syntax compilation errors.

The Role of the GO Command

The GO statement is not an ANSI SQL command; it is a T-SQL batch separator recognized by utility tools like SSMS and sqlcmd. It signals the engine to compile and execute the preceding block of SQL statements completely before evaluating subsequent statements.

-- Create schemas for the architectural layers using batch separators
CREATE SCHEMA bronze;
GO

CREATE SCHEMA silver;
GO

CREATE SCHEMA gold;
GO

Verifying Schema Setup

Once created, custom schemas can be inspected in SSMS under: DataWarehouse -> Security -> Schemas

Note: If new schemas do not appear immediately in the SSMS Object Explorer, right-click the Schemas folder and select Refresh.

9. Engineering Production-Ready Idempotent Scripts

A script is idempotent if running it multiple times produces the exact same outcome without throwing errors or creating corrupt intermediate states. In environment automation pipelines, database setup scripts must check for existing structures before executing creation commands.

Header Comment Documentation Standards

Conceptually: Think of a script header as a permanent "sticky note" attached to the code. Three months down the line, developers will forget specific implementation details. Header comments instantly convey script intent, execution rules, and security risks.

Complete Script: scripts/init_database.sql

/*
===============================================================================
Script Name:    init_database.sql
Description:    Initializes the entire DataWarehouse database environment.
                - Checks if 'DataWarehouse' DB exists; drops it if present.
                - Re-creates the database from scratch.
                - Instantiates architectural schemas: bronze, silver, gold.
Warning:        RUNNING THIS SCRIPT DESTROYS ALL EXISTING DATA IN 'DataWarehouse'.
                Ensure complete data backups before running in non-dev environments.
===============================================================================
*/

USE master;
GO

-- Check if database exists, then drop it to enforce clean initialization state
IF EXISTS (SELECT 1 FROM sys.databases WHERE name = 'DataWarehouse')
BEGIN
    ALTER DATABASE DataWarehouse SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
    DROP DATABASE DataWarehouse;
END;
GO

-- Create database fresh
CREATE DATABASE DataWarehouse;
GO

USE DataWarehouse;
GO

-- Create Architectural Schemas
CREATE SCHEMA bronze;
GO

CREATE SCHEMA silver;
GO

CREATE SCHEMA gold;
GO

Summary and Key Takeaways

• Structured Planning: Organize warehouse development into logical Epics corresponding to architectural layers (Bronze, Silver, Gold), applying a standardized lifecycle (Analysis $\rightarrow$ Coding $\rightarrow$ Testing $\rightarrow$ Documentation $\rightarrow$ Git Commit).
• Strict Naming Standards: Standardize on snake_case, English terms, and avoid SQL reserved words early to eliminate technical debt.
• Layered Table Naming Rules:
  • Bronze/Silver: Prefixed by source system name (<source_system>_<entity>, e.g., crm_customer_info).
  • Gold: Prefixed by dimensional function (<category>_<entity>, e.g., dim_customers, fact_sales).
• Metadata Explicit Prefixing: Prefix data engineering metadata/technical tracking columns with dw_ (e.g., dw_load_date) to clearly separate system columns from source data payloads.
• Version Control Architecture: Maintain clean separation within the Git repository structure across dedicated directories (data_sets/, documents/, scripts/, tests/).
• Idempotent SQL Scripts: Write drop-and-recreate initialization scripts that can be safely re-executed in development environments.
• Script Headers & Warnings: Document purpose, authorship, and destructive warnings directly in file headers to prevent accidental production data loss.
• T-SQL Batch Boundaries: Use the GO separator between CREATE SCHEMA DDL blocks in T-SQL to satisfy SQL Server batch execution constraints.