How to Set Up Git, Naming Conventions, and Database Creation for a Data Engineer Portfolio Project | SQL Project 6

TL;DR

Set up a SQL data warehouse project by planning bronze, silver, and gold layer tasks, defining naming conventions, and creating a documented Git repository. Use lowercase snake_case, retain source_system_entity names in bronze and silver, and assign business-oriented prefixes such as dim_, fact_, and agg_ in gold. Read on for the specific naming rules and repository structure.

Transcript

all right my friends so now it's clear for us that we have three layers and we have to go and build them so that means our big epics going to be after the layers so here I have added three more epics so we have build bronze layer build silver layer and gold layer and after that I went and start defining all the different tasks that we have to follo... Read More

Key Insights

• A layer-based project structure is organized around building the bronze, silver, and gold layers, with each layer treated as a major epic containing its own detailed tasks.
• A repeatable delivery pattern is analysis, coding, testing, documentation, and finally committing the completed work to the Git repository, which makes the construction process clearer before implementation begins.
• A naming convention is a shared set of rules for naming databases, schemas, tables, stored procedures, folders, columns, and other project objects consistently across multiple developers.
• Snake_case is the selected project-wide style, with every word written in lowercase and separate words joined by underscores, as illustrated by the table name customer_info.
• Bronze and silver table names follow the source_system_entity pattern because silver preserves the bronze naming rather than introducing a new data model or renaming source-aligned objects.
• Gold table names are business-aligned and begin with a category prefix because a gold table can integrate multiple sources, making a single source-system prefix inappropriate.
• Gold table categories include dim_ for dimensions, fact_ for fact tables, and agg_ for aggregated tables, followed by the entity or meaningful business subject represented by the table.
• A project repository provides a place to store code, track changes, collaborate, roll back when something goes wrong, and present a documented data warehouse project as part of a job portfolio.

Questions & Answers

Q: How do you structure a SQL data warehouse project?

Organize the project around three major epics: building the bronze, silver, and gold layers. For each layer, define tasks for analysis, coding, testing, documentation, and committing the completed work to Git.

Q: Why should naming conventions be defined before development?

Different developers may otherwise use conflicting styles, making project objects inconsistent. Defining the rules early prevents a large renaming task later and keeps databases, schemas, tables, stored procedures, folders, and columns consistent.

Q: What general naming rules should the project follow?

Use lowercase snake_case, separating words with underscores, as in customer_info. The team should also select one language for names and avoid using SQL reserved words as object names.

Q: How should bronze-layer tables be named?

Bronze tables should follow the source_system_entity pattern. Begin with the originating system, such as crm or erp, add an underscore, and finish with the entity name, such as customer_info.

Q: How should silver-layer tables be named?

Silver tables should retain the same names used in bronze. The naming remains one-to-one because the project does not rename the objects or introduce a new data model in the silver layer.

Q: How should gold-layer tables be named?

Gold tables should have meaningful, business-aligned names consisting of a category prefix, an underscore, and the entity name. Do not use a single source-system prefix because one gold table may integrate multiple sources.

Q: What prefixes identify gold-layer table categories?

Use dim_ for dimension tables, fact_ for fact tables, and agg_ for aggregated tables. The prefix is followed by the relevant entity or business subject, such as customers, products, sales, or monthly sales.

Q: How should the Git repository for the SQL data warehouse project be organized?

Create a public repository with a project-focused name, description, README, and MIT license. Add datasets, documents, scripts, and tests folders, using placeholder files when necessary to establish empty folders, and document the project in the Markdown README.

Summary & Key Takeaways

• Organize the data warehouse work around the bronze, silver, and gold layers. For each layer, define tasks covering analysis, coding, testing, documentation, and committing changes to Git. This detailed structure clarifies how the warehouse will be built and gives the project team a repeatable workflow for completing each major layer.

• Define naming conventions before development begins so every contributor follows the same rules. The project uses lowercase snake_case, one selected language, and names that avoid SQL reserved words. Bronze and silver tables use source_system_entity, while gold tables use meaningful business names with prefixes such as dim_, fact_, and agg_.

• Create a public Git repository named for the SQL data warehouse project, add a description, README, and MIT license, then prepare folders for datasets, documents, scripts, and tests. Placeholder files can establish otherwise empty folders. The README serves as the repository’s main page and documents the project in Markdown format.