Deprecating a model
Overview
In the previous tutorial, we discovered app_installs was replaced by app_installs_v2.
This tutorial will walk through the deprecation of app_installs and demonstrate
how SDF can help overcome the two big hurdles in deprecating a model:
- Preventing prod from breaking by tracking and resolving downstream dependencies
- Setting up guardrails to prevent future mistakes
Prerequisites
Completion of the previous tutorial.
Setup
Reference to Metadata Files
SDF’s semantic understanding is based on metadata files, defining your custom business
logic, and smart propagation of values. To get started, we need to reference the metadata
directory by simply including the relevant paths in our workspace configuration file, workspace.sdf.yml.
Open the file workspace.sdf.yml and uncomment the following:
...
# Uncomment here to begin the "Deprecating a Model" tutorial >>>>>>
- path: classifications # Classifiers
type: metadata
- path: reports # Reports based on SDF s information schema
type: report
- path: checks # Checks against SDF's information schema
type: check
# <<<<<<<
Note
- The classifications folder contains one YAML file for table classifiers, and one for column classifiers.
- The metadata folder is structured based on our models folder.
- The reports folder is currently empty.
We will talk about all of these a bit later.
Now, the paths are included in our project.
Guide
Before Deprecation
Before deprecating a model, we can run an impact analysis on the its downstream dependencies using SDF’s lineage command. We simply run:
sdf lineage staging.app_installs --forward --show result
Table: moms_flower_shop.staging.app_installs
moms_flower_shop.staging.app_installs.campaign_id
moms_flower_shop.staging.app_installs.campaign_name
│
│ copy
└──────┐
moms_flower_shop.analytics.agg_installs_and_campaigns.campaign_name
moms_flower_shop.staging.app_installs.campaign_type
moms_flower_shop.staging.app_installs.customer_id
│
│ mod
└──────┐
moms_flower_shop.analytics.agg_installs_and_campaigns.distinct_installs
moms_flower_shop.staging.app_installs.event_id
moms_flower_shop.staging.app_installs.install_time
│
│ mod
└──────┐
moms_flower_shop.analytics.agg_installs_and_campaigns.install_date
moms_flower_shop.staging.app_installs.platform
│
│ copy
└──────┐
moms_flower_shop.analytics.agg_installs_and_campaigns.platform
Wow! The model agg_installs_and_campaigns is also using the
old version table!
Classify the Model as Deprecated
The file classifications/table_classifiers.sdf.yml holds definitions of
a table level classifier called TABLE_STATUS. You can see that one of the
table status options is deprecated.
To add the classifier to our table, navigate to the table’s metadata file
metadata/staging/app_installs.sdf.yml. Remove the commented
section assigning the TABLE_STATUS.deprecated classifier to the table:
# Uncomment here to add a "deprecated" classifier to the table
classifiers:
- TABLE_STATUS.deprecated
Let’s compile and see how it looks:
sdf compile staging.app_installs --show result
Schema moms_flower_shop.staging.app_installs
This table is a staging table which adds campaign information to app install events
[TABLE_STATUS.deprecated]
┌───────────────┬───────────┬────────────┬──────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┐
│ column_name ┆ data_type ┆ classifier ┆ description │
╞═══════════════╪═══════════╪════════════╪══════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════╡
│ event_id ┆ bigint ┆ ┆ A unique identifier of the marketing campaign event │
│ customer_id ┆ bigint ┆ ┆ │
│ install_time ┆ timestamp ┆ ┆ │
│ platform ┆ varchar ┆ ┆ │
│ campaign_id ┆ bigint ┆ ┆ The identifier of the campaign that is associated with the event │
│ campaign_name ┆ varchar ┆ ┆ The campaign name associated with the campaign_id │
│ campaign_type ┆ varchar ┆ ┆ A substring of the campaign name contain the campaign type. Supported types - instagram_ads, friends_referrals, facebook_ads, google_search │
│ ┆ ┆ ┆ │
└───────────────┴───────────┴────────────┴──────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘
Read more about classifiers in the Classifiers Guide.
Create a Report of References to Deprecated Tables
We can create an SDF report to receive an instant snapshot of all models referencing deprecated tables throughout the entire data warehouse.
Create a new SQL file called deprecated_table_reference.sql in the
reports/ directory. Copy the following query to your new file:
WITH
deprecated_tables AS (
SELECT
table_id
FROM sdf.information_schema.tables
WHERE
CONTAINS(classifiers, 'TABLE_STATUS.deprecated')
)
SELECT
to_table_id AS table_id,
from_table_id AS upstream_deprecated_table_id
FROM sdf.information_schema.table_lineage
WHERE from_table_id IN (SELECT table_id FROM deprecated_tables)
AND to_table_id IS NOT NULL
To learn more about SDF’s rich information schema and how you can leverage it to write your custom own reports, read our information schema reference page.
Thanks to SDF’s lineage tracking capabilities, we already know that agg_installs_and_campaigns
is referencing the old table. It’s a good test to check -
we expect it to fail.
Let’s check it out (pun intended):
sdf report deprecated_table_reference
Report moms_flower_shop.pub.deprecated_table_reference
┌───────────────────────────────────────────────────────┬───────────────────────────────────────┐
│ table_id ┆ upstream_deprecated_table_id │
╞═══════════════════════════════════════════════════════╪═══════════════════════════════════════╡
│ moms_flower_shop.analytics.agg_installs_and_campaigns ┆ moms_flower_shop.staging.app_installs │
└───────────────────────────────────────────────────────┴───────────────────────────────────────┘
1 rows.
It works! Now, we should fix agg_installs_and_campaigns.
For more examples on getting health reports of your data warehouse and aggregate custom logic and static checks, read our full Reports Guide.
Resolve Downstream Dependencies
To make sure we won’t break prod if we delete our table, we can choose to resolve the dependency in one of two ways:
- If the model is not being used, we can just delete it altogether.
- Otherwise, we can update the
FROMstatement ofmodels/analytics/agg_installs_and_campaigns.sqllike we did for our previous model:
...
FROM staging.app_installs_v2 -- Change from app_installs to app_installs_v2
...
Let’s run our report again:
sdf report deprecated_table_reference
All clear!
Report moms_flower_shop.pub.deprecated_table_reference
┌──────────┬──────────────────────────────┐
│ table_id ┆ upstream_deprecated_table_id │
╞══════════╪══════════════════════════════╡
└──────────┴──────────────────────────────┘
0 rows.
That was really cool, ha? Let’s explore what other cool things we can do with SDF’s information schema and semantic understanding in our next tutorial - enriching your warehouse