Blog Archives

When working with any relational database you have two methods to relate two tables.

Primary Key: Uniquely identifies each row within a table.

Salesforce for example as a business app, has a primary key on every field called “Id.”

Foreign Key: References a primary key in another table, establishing a relationship between tables.

Is ConvertedAccountId a a key in the Lead Table?

• ConvertedAccountId is not a primary key because it does not uniquely identify each record in the Lead table. It’s possible for multiple leads to point to the same account.
• ConvertedAccountId acts as a foreign key if it references the primary key (likely AccountId) in the Account table. This makes it a relational link between leads and accounts.

In relational databases, particularly business applications keys are required and enforced to perform CRUD (CREATE, READ, UPDATE and DELETE) operations and to index and performance tune your database. Snowflake by design does not require you to formally define primary keys when you build a table, nor does it enforce any constraints on the data you load into your tables. This course does not cover data warehouse design, but if you are interested you can view the following documents on Snowflake constraints.

While we are on the topic of keys, one realted topic is indexing. Snowflake does not use traditional indexes commonly found in relational database management systems (RDBMS). Snowflake uses its own proprietary micro-partitions, keys, an metadata to auto-optimize query performance.