Snowflake Data Warehouse Design Documents

 

Here is some designs for data warehousing using Snowflake.   

LINK: Snowflake Design - Overall Data Warehouse

LINK: Snowflake Design - Environments

LINK: Snowflake Design - Pipelines

How To: Snowflake Data Ingestion Techniques

There are many ways to ingest data.  The below list of techniques are not an exhaustive list, but more of a list of some simple "Goto" techniques for your "Toolbox".    Without belaboring the subject, your ingestion technique will depend on wither your data is CSV, JSON, XML, Fix String, or Parquet files and wither it is a high or low Volume, Variety, Velocity dataset, and wither your reporting is "Real-Time", Hourly, Daily, Weekly, or Monthly cadence.  Below, I have provided three basic techniques that I have in my "Toolbox" that have evolved over the last 5 years using Snowflake on various ingestion challenges.

Technique A: I typically use this technique for ingesting data from an ERP, CRM, or Data Mastering Databases in which the volume or velocity are not extreme.   

1, 2, 3:  You would create a pull script (2) that will bulk pull the data from a table from a database (1) using some change control timestamp in the table (If available) or change control log in the database system itself. Depending on volume and change control available for the table, you can choose to pull the whole table each time or only pull new and changed records and place into a Parquet or CSV file (3).  Change control methods vary depending on the database or table design.   

4:  You then COPY INTO the file into Snowflake (4).  I like ingesting Parquet files, because they are compressed and ingest extremely fast into Snowflake.     

5:  Dedup the table (5).  This is where you need to know the Primary Key of the table.   If you need, you can treat the table like a Slowly Changing Dimension and perform a non-destructive method for identifying the most current records and deprecating the duplicates.   I typically use a soft delete timestamp.  This allows me a window into historical changes.   If its a high volume table and the changes are high volume you may wish to perform a hard delete.

6:  Publish (6) the table in the Store when all other supporting tables are also ready to be published.  Publish tables a group were any dependent tables published at the same time.  Snowflake's cloning process happens extremely fast, so any read blocking that you might incur will not be noticeable.   Make sure you use Snowflake's ability to keep the security grants when using the CREATE OR REPLACE statement on an existing table.

7, 8:  You will then create a VIEW(7) that transforms the data into a shape that fits your target table for the warehouse.   Selecting against that  VIEW you can either do an incremental Insert or do a FULL replacement of the table via CREATE TABLE (8).  For small tables you can just replace the whole table and save yourself the complexity of change control and incremental processing. 

9: Clone the Staging Table into the published schema area (8).    

Technique B:  This technique I typically use when I run across JSON, XML, or Fix String files to ingest.   Its like technique A, but it has an extra transformation and table staging step to flatten the semi-structured data and place into a staging table(s).

5.2, 5.4:  This is where you would create a VIEW (5.2) that would flatten the JSON or XML, or perform some special handling on the Fixed Length String file.    And then you would incrementally insert or do a full replaced of the staging table (5.4).  Then proceed on to the other steps to further transform and publish.

Technique C:  This technique is especially useful when you ingest a high volume table into Snowflake.   Its like technique A from step 1-6, but no transformations are performed.  You go straight to publishing via CLONING.  This technique assumes that you can trust the upstream data source for data quality & that the shape is exactly what you will be using for reporting.  For really fast ingesting I would suggest ingesting Parquet files.    

Please note that when dealing with High Volume, High Velocity data, that there are several other techniques that can help you perform your intense operational or business reporting cadence.  The above lists of techniques is not an exhaustive list, but more of a list of some "Goto" techniques for your "Toolbox".  

Proven Snowflake Data Mesh Design Suggestions

 

I have found that Snowflake is an extremely fast and easy way to get your enterprise data warehouse up in the cloud in a secure way and supports futuristic design ideas like "Data Meshing".   Snowflake has been extremely fast and reliable for my projects, even for high volume projects with a daily ingestion of 275 million records a day or more.   

1. I highly suggest you divide your warehouse database into separate schemas.   This will enable you to keep the noise down for analysts and developers maintaining the warehouse and speed up delivery.    Each schema representing a specific state of the data.  There are two types of schemas:  Staging and Published.   And then there are 3 different schemas I would recommend:  Config (or you a name it Reference), Store,  and Insight schemas.   Staging schema type represents the RAW state of the data as well as it represents the data possibly being in a flux.  Note that Staging schema type is applied across all the schemas:  Config,  Store,  and Insight.   Each of these schemas will have a state of the data that is in flux as its being transformed.  The Store schema represents the published cleaned up, deduplicated, json-> table flattened, and the data is safe to be consumed by down stream services or analysts.  
2. The Insight schema is the highly modeled design that is shaped and aggregated in a way that is ready to be used for reporting.   Notice that is also has its own Staging.
3. The Config or Reference schema is a place to warehouse your configuration meta data and data mastery data for domains & hierarchies.
4. Each domain would export out of their transactional database into an S3 Bucket path.  Data ingestion into Snowflake via AWS S3 is extremely simple to do.  Snowflake processes many types of data formats like Parquet, JSON, or CSV files with ease.   
5. In the spirit of supporting "Data Mesh" concepts, you may find it more convenient all around to create separate warehouse databases for each business domain which are separated from the main enterprise reporting warehouse.   This reduces what the main enterprise reporting warehouse needs to publish to only the necessary aggregates.  Deeper analysis can be done by accessing the specific domain database.   Using the Data Mesh approach reduces bottlenecks by enabling you to scale out your teams independently for development, maintenance, OPS, and subject matter expertise for each business domain without introducing in heavy dependencies.
6. Most warehouses need some kind of data mastery in which data is created by the company and change controlled.  I have explored all kinds of means to master data.   But I find it the most simple to create an AWS RDS db, model the data you need, and place the necessary fields for change control.  As for UI needs, if the changes are not high volume, I wouldn't bother.   Make a change order process and have a DBA make the necessary changes.  But if it is high volume, then you should create a UI purpose built for mastering data by a team of people.   
7. Complex Warehouses will have some kind of configurations that drive reporting to the end customer.   This is like Master Data except this is more for pipeline control and setting arguments like thresholds, percentage ranges, etc...
8. You may need to process unstructured data or make predictions based on data in the warehouse. Such tools do not run within the Snowflake Environment.
• If you have non-structured data like video or audio or images, you will probably be using some kind of Machine Learning for image and audio processing and then import the results into your warehouse.
• If you have a need to make predictions, you will probably be using some kind of gradient boosting tools like XG-BOOST.   You'll be exporting data from the warehouse and importing the inference model back into your warehouse that then can be used to make predictions.
9. Business Intelligence Reporting tools are essential within the enterprise.   I personally have found that Looker is an excellent match with Snowflake and I have been able to deliver some extremely complex reporting.   Creating new reports using tools like Looker, is orders of magnitude faster than building reports using a web dev.
10. Data Quality is critical for a data warehouse.  I would start with validating all results for the basics (G.O.L.D.) then work on other tolerance checks:
• Gaps in the data
• Orphaned dimensions
• Late datasets
• Duplicate Records
11. Snowflake makes it extremely easy to do cloud sharing.  You can share internal or external to the company.   This makes it easy for analysts, systems, or partners across the company that need to import data to go directly to the warehouse and export what is needed based on their security rights.

As for processing pipelines and creating job schedules, I have found it most useful to build as much of the processes as close to the data as possible.  Creating Stored Procedures and executing them using Snowflake's Task Scheduler makes the data warehouse self contained when not dependent on having to pull data from API's,  other non Snowflake DBs, or other Non-Snowflake accessible file locations.   When creating pulls from other data sources like an API or a DB, I would suggest using Python and a Cron Scheduler and place the logic package in an a cheap AWS EC2 instance.   Most data services have example Python Logic for pulling data from their API that you can copy and paste.  You might also find that Databricks can be of great value here as well and also adds an additional value by enabling you to apply other complex logic like XG BOOST, Neural Networks, NLP and the like.   Over the years, I found that complex ETL services and tools are not required and add a level of unnecessary expense.  Personally, I would rather spend that money on more Snowflake and Databrick processing time.  But saying that, having a modern DAG job manager can be useful if you keep the design elegant.

Baseline Conceptual ERP Data Model

Baseline ERP Conceptual Model by Scott Thornton.pdf

This model is a pdf file that is more detailed (down to attributes) that this website already lays out.   It defines the information and informational relationships a business will have to function as a business.   Anyone working with data will like this model, as it will help you to understand how business data ties together into a relational data model.  

Technology Decision Factors

When making a decision about what technology to use for a project, product, or service architecture there are many factors to consider that will impact the total cost in ownership for a company.

Skilled Workforce

Risk: Availability, Salary Cost, Training Cost, Churn Cost

Commentary: The skilled workforce factor sometimes gets forgotten in the hunt for a technology.  If a technology is highly specialized it will decrease chances of hiring someone with the appropriate skill level to deal with the technology.  It will also increase the cost of training and/or salary.  There can be a high churn rate for the skilled resource as they can get head hunted by other companies or they become concerned about their career as the skill set exercised is too narrow for their long term  career goals.  Skill sets must span both Development, Test, and Operations as well as training for end-users of the technology if necessary.

Technology Support

Risk: Code Stability, Documentation Quality, Tech Support, Market Survival, Backwards Compatibility

Commentary: If a technology is bleeding edge or produced by a one man band, it can suffer from poor documentation, poor technical support, instabilities, and may not survive its incubation period.   It is also important to have the assurance that the company producing the technology are committed to backward compatibility so you don't lose your coding investment.

Operational Cost 

Risk: Deployment and Provisioning Costs, Maintenance Costs, Stability, Tech Support, Hot Patch Support

Commentary: The day to day operational costs of technology is very important to take into consideration.  On-Site hardware costs, installation costs, backup costs, failover costs, global data and processing distribution costs, tuning costs, hardware maintenance costs, Software upgrade costs, hot patch costs, production trouble shooting costs, and operation staff costs is a lot to calculate.  On-Site may have a much higher cost of ownership then subscribing to cloud services.   Cloud Services provide a much simpler and predictable cost model and usually provide elastic provisioning to grow or shrink to your needs.  Something to really contemplate when selecting your technology platform.

Security Cost 

Risk: Customer Safety, Business Safety

Commentary: Data and processes in the hands of the wrong people can cause irreversible damage to a customer or the business.   Personal Identification Information (PII) and Business Critical Information must be secured and not taken lightly.  Is the usage of the technology managing such information?  Can the Technology tightly secure the information?   Can it keep up with latest security features and patches to protect against security breaches?  Can it detect security breaches?  Same questions for any sensitive processes.

Business Functional Requirements

Risk: Functionality,  Elastic Scalability, Reliability, Availability 

Commentary: I placed this last because these are the most obvious factors to consider.  Does the technology provide the proper level of functionality to support the business?   Can it scale with the company as its needs grow and shrink through out time?  Its is stable and reliable?  Can it deal with the 99.9, 99.99, 99.999 uptime availability requirements?   What is the Mean Time To Recovery (MTTR)?


Bottom Line: Remember that you get what you pay for, but don't pay for more than what is warranted.  And in a competitive world, the total cost of ownership of your enterprise technology balanced with the need to enable your company to compete is critical to get right.


Please See the following docs for the cost benefits for cloud technology solutions.
Cloud Economics
Cloud Platforms for Business Owners
Cloud Optimization - A Framework for making business decisions about cloud computing

Planning & Building Cubes: Tasks, Deliverables, Participants, & Schedules

The following list of tasks, deliverables, participants, and schedules are just templates or guidelines of what our team had to go through in order to deliver new business critical cubes or adding new dimensions to business critical cubes.  These cubes where 500 Gb to 1Tb in size and came from processing 24Tb of data every day.  Agile methods could work, but only when the Developers are working on our cubes, which was just 4-6 weeks out of the 6 months it takes to roll out our cubes.  I know: wow, but this was how we evolved the phases to protect the business critical cubes from bad data.  The rest of the tasks and deliverables come from non-development sources.  Make of it as you will.   Hope this gives some insight in the efforts in building large business critical cubes verses your average application and reporting development.  Its the difference between building a dog house verses building a sky scraper.  So much more logistics and physics involved.

Green Light Phase:

• Tasks
• Business requirement gathering
• Requirement review
• Feature costing
• Feature stack ranking
• Document sign-offs
• Deliverables
• Business requirement document
• Feature costing spreadsheet
• Participants
• Key business stakeholders
• Product Managers
• Engineering Program Managers
• Development manager/leads
• Test manager/leads
• Operations manager/leads
• Data Quality manager/leads
• Schedule
• 4 weeks

--------------------------------------------------------------------

 

Planning Phase:

• Tasks
• Roadmap creation & review
• Hardware planning
• Deliverables
• Roadmap
• Hardware acquisition request
• Swag schedule
• Participants
• Key business stakeholders
• Product managers
• Engineering Program Managers
• Development managers/leads
• Test managers/leads
• Operations managers/leads
• DataQuality manager/leads
• Schedule
• 2 weeks

 --------------------------------------------------------------------

 

Design Phase:

• Tasks
• Business interviews
• Use case scenarios
• Dimensional modeling
• Volume analysis
• Hardware configuration
• Document review & sign-offs
• Deliverables
• Data contracts
• Functional specs
• Service Level Agreement (SLA) Document
• Technical specs
• Test specs
• Participants
• Product Managers
• Engineering Program Managers
• Develoment leads
• Test leads
• Operation leads
• DataQuality leads
• Schedule
• 4 weeks

 --------------------------------------------------------------------
 Building Phase:

• Tasks
• Development
• Cube Engineering Team Deliverables
• Dimension Storehouse
• Managed dimensions
• Static dimensions
• Datamart ETL
• DataMart DB
• Cube
• Baseline agg indexes
• Monitoring & instrumentation
• Reports
• Deployment document
• Release to Operations (RTO) package
• Trouble shooting guide
• ETL Pipeline Team Deliverables
• Aggregated Data streams
• Auto discovered dimensions files
• Participants
• Product managers
• Engineering Program Managers
• Developers
• Testers
• Operations
• Schedule
• 4-6 weeks

--------------------------------------------------------------------

 

Testing Phase 

• Tasks
• Testing
• Integration testing
• Cube Team Deliverables
• Deployment document
• Sample managed dimensions
• Troubleshooting guide
• Releast to Operation (RTO) package
• Rollout plan
• ETL Pipeline Team Deliverables
• Sample streams
• Sample auto-discovery files
• Releast to Operation (RTO) Package
• Participants
• Product managers
• Engineering Program Managers
• Testers
• Developers
• DataQuality
• Schedule
• 2-4 weeks

--------------------------------------------------------------------

 

Release to Operations (RTO) Phase

• Tasks
• Deployment
• Hardware build out
• Historical data load
• Configure monitoring
• Deliverables
• Fully deployed solution
• Participants
• Engineering Program Managers
• Developers
• Testers
• Operations
• Schedule
• 2-4 weeks

--------------------------------------------------------------------

Release to Customer (RTC) Phase

•  Tasks
• Data Quality Certification
• User Acceptance Testing (UAT)
• Customer communications on new release
• Release to Customer (RTC) Sign-off
• Deliverables
• Certification plan & exit criteria
• UAT plan & exit criteria
• RTC Check off list
• Participants
• Key business stakeholders
• Product manager
• Engineering Program Managers
• Developers
• Testers
• Operations
• DataQuality team
• Key users
• Schedule
• 2-4 weeks

Modeling Questionnaire

Here are some strategic questions to ask key business personnel to help accurately model how the business uniquely defines its information.

Entity Questions:1. What is the list of Items (or Entities) that you are tracking or would like to track?
2. How do you uniquely identify each {Entity Name Here} ?
3. Do you manually assign identification to each {Entity Name Here}?
4. Can the unique identifier change for a {Entity Name Here}?

Entity Relationship Questions:
1. Can {Entity A} have 1 or more {Entity B}?
2. Can {Entity B} have 1 or more {Entity A}?
3. Can {Entity A} exist without {Entity B}?
4. Can {Entity B} exist without {Entity A}?

Entity Subtyping Questions:
1. Are there one or more types of {Entity Name Here}?
2. Is a {Entity Name Here} a type of {Entity Name Here}?
3. Can a {Entity Name Here} be classified under more than one subtype?

Entity Attribute Questions:
1. What is the list of attributes that you directly associate with a {Entity Name Here}?
2. Is this {Entity Attribute Name Here} Required?
3. Is this {Entity Attribute Name Here} Changeable?
4. Is this {Entity Attribute Name Here} made up of multiple values?
5. Is this {Entity Attribute Name Here} a Calculated value?
6. Is this {Entity Attribute Name Here} unique?
7. Does this {Entity Attribute Name Here} be selected from a standard list of values?

Entity Mastering and Business Ownership Questions:
1. What is the list business areas that are affected by this information model?
2. Does {Entity Name Here} get mastered by this {Business Area}?
3. Does {Entity Name Here} get used by this {Business Area}?

Logical Data Modeling: An Introduction

Data Modeling In General
I found it worthy to note that the purpose of creating a normalized logical data model is to accurately document the business entities and relationships between them in a detailed model. The value of this logical model is two fold: One is for the business owners to have detailed comprehension of their own business information; Second is to transfer this business knowledge to the developers to equip them to accurately build the system to the exact business needs. They will also create an optimized physical data model (Schema) for the system based on this logical model. Therefore it is critical that you interview the appropriate knowledgeable business people and document the business as much as you can. You will run across business areas in which the business may not be well defined. It will then be your responsibility to understand this area as much as you can, model it accurately, and get approval on the design by the business owners.

Meta Modeling: A WarningDO NOT fall into the temptation to meta model a business area that is not well understood by the business owners in the attempt to avoid your due diligence. This includes throwing in many-to-many relationships into the model to solve problems where the relationship between entities were unclear. This will lead to complications in development and result in a system hard to maintain, use, and raises the cost of ownership. Think like a lawyer. The point is to model the business accurately and provide a natural means to describe and enforce the definition and relationships of the business entities that need to be managed. Only after you have exhausted all avenues of research is when you should use a meta model. But isolate that meta model to be applied specifically to the area that is requiring that level of flexability. Then the meta model will be your friend and not your enemy.

Step 1: Use Case Modeling
The first thing you need to do is document the real world scenarios of the business areas you need to model. This should be detailed in proper sentences to work out the nouns and verbs. The nouns and verbs will be your key to discovering the data model. Of course there are many other benefits as well: Classifying your users, identifying external systems and data dependences, identifying user interfaces, discovering required reports, understand business processes, and finding the scope of the system you are to build.

Step 2: Extended Relational Analysis
Data modeling using the rules of normalization (http://www.datamodel.org/NormalizationRules.html) is not the most natural and easy way to model any system. This can be proven by the very fact that many people don’t truly understand how to normalize a data model. But there is good news. There is a different approach that makes it childs play to create a normalized data model using a technique called Extended Relational Analysis (ERA). I’ve learned it years ago and has been critical to my career. So I highly recommend it. This doesn’t mean that you don’t need to understand the normalization rules, but it does mean that you don’t have to memorize them in to a daily magical chant so you don’t forget. Think of ERA as an as a way of thinking and a means to organize your thoughts rather than a software tool.

ERA technique is broken up into three areas: Entity Analysis; Relationship Analysis; Attribute Analysis. Entity analysis uses nouns out of the use case model to help define each entity that a business is required to deal with. Relationship Analysis uses verbs out of the use case model to help define each relationship between business entities. Attributes Analysis uses the modifiers (Adjectives and Adverbs) to help finish the definition of each business entity defined or create new ones missed with the other two analysis steps. There are many classes you can take out there to help you learn this technique in modeling (http://www.era-sql.com/). Its worth the expense, trust me. It will make your modeling efforts much easier and the end results increase your chances of lowering your total cost of ownership of any system you build. Just do it.
For more detail see http://www.pmcomplete.com/BPM/HTML/bpm659v.asp.

Best Practices for SQL Design Patterns

I’m a firm believer in creating symmetry through design patterns where it is applicable. Using good modeling techniques and naming conventions on data and process definitions will enable easier code generation. 80% of an application can be code generated based on templates that enforce design patterns. The remaining 20% may need to vary a little from the standards when it is too “costly” to do so. The following naming and coding suggestions have proved to be the most practical for me in starting any project.

Table Name convention In the past I‘ve seen a several databases that had some difficult naming conventions that increased the cost of ownership due to confusion. The databases that have followed the below suggestions have lowered the cost of ownership by making it easy to understand.

Pascal naming convention: The first letter of every word is capitalized.

Good Example: Person, PersonAddress, GBITax
Bad Example: personaddress, Person_Address, personAddress, tbl_PersonAddress
Do not pluralize the table name: It is best not to place an 'S' at the end of every table. It can be assumed that a table contains many entries.

Good Example: Person, Country, UserSignup, DetailRecord
Bad Example: Persons, DetailRecords
General Rules for placing Prefix to tables: Prefixes can be useful for classifying tables into business areas when the number of tables becomes too unwieldy to deal with in a single database. Usually one should start asking the question of whether or not they should split the database into multiple databases when this starts to happen, but there are cases when this may not be the desired direction. In this case you should start thinking about Prefixes. You can use abbreviations also long as they are consistent.

Good Example: CatalogItem, UtilMessageLog, PromoRule
Bad Example: tblPerson
Do not use the ‘_’ as a spacer: From a developers and analyst standpoint these little guys can be quite annoying. And with the Pascal naming convention the use of ‘_’ for readability becomes redundant.

Column Name convention 

Pascal naming convention: The first letter of every word is capitalized.

Good Example: PersonID, FirstName, ZipCode, SSN
Bad Example: Personid, Firstname, lastName
Do not place the name of the data type in column name: It is not a good idea to place the name of the data type in the column name. If you change the data type of the column, then your column name is misleading. And changing the name of a column is full of troubling changes. There are a few exceptions: Date is one example.

Good Example: StartBatchDate, FirstName, USDAmount
Bad Example: ti_event_id, vcFirstName
Usage of Abbreviations: Abbreviations are good for reducing the size of a column name. One should always be consistent and only use abbreviations that are commonly understood. Remember that the name of the column should be self-descriptive. Avoid over-use of abbreviations which can make it difficult to understand and support. When in doubt spell it out.

Good Example: StartBatchDate, UserNum, SubscriptionDesc
Bad Example: ti_event_id, vcFirstName, SbDc, X
Schema Structure
Use Constraints: It is sad to say that there are many databases that exist in this world that do not use primary key (PK), foreign key (FK), and alternate key (AK) constraints. Unless there are technical reasons for not having these, you should always use them, if for nothing else, to insure referential integrity. The use of data integrity constraints also makes the database design self documenting (Example: Schema extraction process into Visio or Erwin.)

Name Your Indexes: If you name your indexes instead of having SQL Server to generate the name dynamically, you will have an easier time altering those indexes in the future. You will then explicitly know the name of the index rather then having to dynamically lookup the index name to alter it. If you dynamically generate the name of the index, it will be different from environment to environment (Example Environments: Development, Test, & Production).

Stored Procedure Naming Convention

Name Format: Don’t use SP as a prefix for your stored procedures. SP in SQL Server stands for System Procedure (Example: SP_HelpText). {VERIFY} Also, if you use the SP prefix, SQL Server will check the master database first and then the application database. So it is also a performance issue as well.

Good Example: GetPerson, ImportProductKey, CountPIDs, CommitPayments, GetProductReport
Bad Example: SP_GetPerson
Pascal naming convention: The first letter of every word is capitalized.

Good Example: GetPerson, ImportProductKey, CountPIDs, CommitPayments
Bad Example: Get_Person, IProductKey, COUNTPIDS
Examples of names: Get; Set; Ins (Insert); Upd (Update); Del (Delete); Import; Extract; Load; Transform; Export; Count; Process; Commit;

Abbreviation / Acronym dictionary: You should have a list of the most commonly used abbreviations. It’s not a hard and fast rule that a person can’t use an abbreviation that is not on the list. It is just a means to help create symmetry into the system. This list should be in a database design / development standards document.

Examples:
Abbreviation NameNum - Number
Ind - Indicator
Dtm - Date and Time (Millisecond)
Id - Identifier
Cnt - Count
Amt - Amount
Acct - Account
USD - United States Dollar Currency Code

Stored Procedure Argument Naming Convention
This is where coding preferences really start to vary from developer to developer. The concepts here are just helpful tips that I have found very useful as a SQL Developer.

Argument Prefix: @Arg @Arg. Looking through a complex stored procedure and attempting to quickly find where the stored procedure arguments are being used can be time consuming. The use of @Arg Prefix can ease this issue. I’ve found myself avoiding lots of coding mistakes by having done so.

Example: GetPerson(@ArgPersonID int)

Coding Stored Procedures

The Return statement: Avoided using the return statement to return any values other then error codes if even that. One should use Output parameters where possible for returning values. (This is for stored procedures. Functions are a different beast all together.)

Set NoRowCount On: Place Set NoRowCount On at the beginning of every stored procedure to reduce the noise coming back to Query Analyzer.

Error Trapping: Always check for errors at every statement and log these errors to a MessageLog table. Every stored procedure must do this.

Transactions: K.I.S.S. is the general rule here. Keeping it simple will avoid some of the pitfalls many fall into with complex rollback designs. Always think of a unit of work in which several processes must live and die together. If any process within that unit of work fails, then the unit of work should be rolled back. If a unit of work is extensive it would be a good idea to break it up into multiple units of work with each unit of work isolated in its own stored procedure. That stored procedure can have multiple stored procedures calls, but try to keep the design flat so as not to have a stored procedure that calls a stored procedure that calls a stored procedures etc… . Avoid combining units of work into one gigantic transaction. People from all walks of life will walk over to your desk and slap you with a wet noodle.

Avoid Deadlocks: To avoid deadlocks, get in the habit of accessing tables in the same order through out your system. This will alleviate the most common cause of deadlocks.

InLine Code Documentation: Document the “You Know What” out of the stored procedure within the code. No excuses or exceptions here.

Stored Procedure Code Generator: This is a very useful tool in creating fundamental (Basic) insert, update, delete, get, and set stored procedures. I’ve called them fundamental stored procedures as they are generated per table. These stored procedures are mainly used by the UI. I’ve found that generating these stored procedures increases the symmetry and stability and reduces human error and derivation. This kind of tool can be used to generate 80% of all stored procedures where a UI is needed. The other 20% are complex stored procedures that need more LTC (Loving Tender Care) from the developers.

Once you’ve generated these stored procedures, your better off maintaining them manually, Though some generators allow for “Re-Entry” in which manual code changes are kept while regenerating the rest of the stored procedure.

I do have a stored procedure code generator that I’ve created over the years. It needs a little bit of updating, but works fine as-is and can save lots of time and money. It will generate very clean and supportable code. There are many code generators available. I’ve created my own, because I like total control of my tools and I’ve had it before many code generators existed.