Skip to main content

Exercise 1

To create singular test to validate uniqueness check on surrogate keys in co2_emissions_by_country model​

note

Surrogate Key refers to primary combinations of two or more columns which on combination should be unique across the table. Eg: Year and Country column together make the unique combination in co2_emissions_by_country table

Follow the instructions below using DBT:

  1. Open your dbt project and navigate to the tests folder.

  2. Create a new file called assert_surrogate_key_co2_emissions_by_country.sql or any suitable name for the singular test file.

  3. In the file, define the query to check the uniqueness of primary key combination. In this case, Primary key combination (Surrogate key) is Year and Country columns.

  4. Specify the table name in the query by referencing the co2_emissions_by_country table. You can use the ref() function to refer to the table. For example, FROM {{ ref('co2_emissions_by_country') }}.

  5. Define the query in such a way that it should result output if there are duplicate records found for the surrogate keys.

tip

To check uniqueness or to detect duplicates, use group by clause on primary key columns together with having clause and count check.

  1. Save the file.

  2. Run the dbt test project to validate the result. You can use the dbt test command in your terminal to see if test case is successfully completed.