Project Resources

Before we start building the SQL Data Warehouse, let's make sure you have all the tools and resources you need. Every tool listed here is free to use, so there's no cost barrier to getting started.

In this lesson, we'll walk through each tool, explain what it does, and why it's important for the project.

SQL Server Express is a free edition of Microsoft SQL Server. It's the actual database engine that will store and manage all of our data. Think of it as the brain behind everything — it's where our tables live, where our queries run, and where our data warehouse physically exists.

SQL Server Express supports all the core features we need:

• Creating databases and tables
• Running SQL queries
• Stored procedures and views
• Importing and exporting data

The Express edition is limited to 10 GB per database, but that's more than enough for our project and for learning.

How to Install

1. Go to the official Microsoft SQL Server downloads page.
2. Download SQL Server 2022 Express (or the latest available version).
3. Run the installer and choose the Basic installation type.
4. Follow the prompts — the default settings work fine for our purposes.
5. Once installed, note down the server name (usually something like localhost\SQLEXPRESS).

After installation, SQL Server runs as a background service on your computer. You won't interact with it directly — instead, you'll use a GUI tool called SSMS, which we'll cover next.

SQL Server Management Studio (SSMS) is a free graphical tool from Microsoft that lets you connect to SQL Server and interact with your databases visually. It's where you'll write and execute SQL queries, design tables, inspect data, and manage your entire data warehouse.

Think of SSMS as the cockpit — SQL Server is the engine, but SSMS is where you sit and control everything.

Key Features of SSMS

• Query Editor: Write and run SQL statements with syntax highlighting and IntelliSense (auto-complete).
• Object Explorer: Browse databases, tables, views, and stored procedures in a tree structure.
• Results Grid: View query results in a table format.
• Execution Plans: Visualize how SQL Server processes your queries (useful for optimization).
• Import/Export Wizard: Load data from CSV files or other sources into your tables.

Connecting to SQL Server

When you open SSMS, it asks you to connect to a server. Here's what to enter:

Once connected, you'll see the Object Explorer on the left side. From there, you can expand Databases to see all the databases on your server. This is where our data warehouse will appear once we create it.

GitHub is a platform for version control and collaboration. We'll use it to access the project's source code, SQL scripts, and CSV data files.

Version control means tracking changes to files over time. In a professional setting, every SQL script, every stored procedure, and every schema change is stored in a version control system like Git. This way, you can:

• See the history of every change
• Roll back to a previous version if something breaks
• Collaborate with teammates without overwriting each other's work

Project Repository

The project repository contains everything you need:

• SQL Scripts: All the CREATE TABLE, INSERT, and stored procedure scripts organized by layer (Bronze, Silver, Gold).
• Source Data (CSV files): Sample CRM and ERP data files that simulate real-world source systems.
• Documentation: Architecture diagrams and project descriptions.

You can either clone the repository using Git or simply download it as a ZIP file from the GitHub page.

Downloading the Project Files

If you're not familiar with Git yet, the simplest approach is:

1. Navigate to the project repository on GitHub.
2. Click the green Code button.
3. Select Download ZIP.
4. Extract the ZIP file to a folder on your computer.

Inside, you'll find folders organized by the data warehouse layers and the source data files we'll be importing.

DrawIO (also known as diagrams.net) is a free online diagramming tool. In this project, we'll use it for creating architecture diagrams and data flow diagrams.

Before writing any SQL, it's important to plan and visualize what you're building. Architecture diagrams help you understand:

• Where data comes from (source systems)
• How data moves through each layer (Bronze → Silver → Gold)
• What transformations happen at each stage
• What the final output looks like for reporting

Why Diagrams Matter

In real-world data projects, you almost always start with a diagram before writing code. It helps you:

• Communicate your design to stakeholders and teammates
• Identify problems early before you've written hundreds of lines of SQL
• Document the system so future developers can understand what was built

You can use DrawIO directly in your browser at app.diagrams.net — no installation needed. You can also install the desktop version if you prefer working offline.

Notion is a free project management and note-taking tool. We'll use it to organize and track the tasks involved in building the data warehouse.

A real data warehouse project involves many steps — creating databases, building tables, writing ETL logic, testing, documentation, and more. Without a structured plan, it's easy to lose track of what's done and what's left.

How We'll Use Notion

We'll set up a simple project board with tasks like:

• ✅ Set up SQL Server and SSMS
• ✅ Download project files from GitHub
• ⬜ Create the data warehouse database
• ⬜ Build Bronze layer tables
• ⬜ Load source data into Bronze
• ⬜ Build Silver layer (cleaning and transformations)
• ⬜ Build Gold layer (analytical views)
• ⬜ Test and validate

This gives you a clear roadmap and a sense of progress as you complete each step.

Why Project Management Skills Matter

Even if you're working solo, practicing project management is a valuable habit. In industry, data engineers use tools like Notion, Jira, or Azure DevOps to:

• Break large projects into manageable tasks
• Track progress and deadlines
• Collaborate with team members
• Document decisions and blockers

Starting this habit now will make you more effective in professional settings.

Here's a quick recap of all the tools and what they're for:

Make sure to install SQL Server Express and SSMS before moving on to the next lesson. The other tools can be accessed through your web browser whenever you need them.