- Blog
- 03.21.2018
- Data Fundamentals, Dev Dialogues
Adjust for Daylight Savings w/ Matillion ETL For Snowflake
Every year at the end of March, Europe puts the clocks forward 1 hour, signalling the longer days of summer in the northern hemisphere. You may need to adjust for Daylight Savings with Matillion ETL for Snowflake so that your data is accurate.
At the point of changeover, everyone’s local time skips forwards one hour relative to UTC, creating longer daylight in the evenings - at the cost of one more hour of darkness in the morning!
The rules guiding this changeover have been consolidated in Europe, and can easily be implemented in a Matillion ETL Transformation job. In this article we’ll create a reference table that could be used in any other ELT job to provide the start and end dates of Daylight Savings changeovers every year.
Date generation
The rules for finding the Daylight Savings changeover dates are simple:- Start of Daylight Savings - last Sunday in March
- End of Daylight Savings - last Sunday in October
UTC and BST calculations
Next it’s easy to convert the integer sequence into a date sequence, using a Snowflake SQL expression, such as TO_DATE. Matillion ETL for Snowflake has a Calculator component for this purpose, which guides you through adding derived columns. We’ll add a simple date first of all: Then, using some more date expressions, the following additional columns:- Day Name
- Year ID
- Month ID
- Final Sunday - using an analytic function to find the final Sunday in every month
- Is Last Sunday in March - flagging if this is the final Sunday in March
- Is Last Sunday in October - flagging if this is the final Sunday in March
Filtering and Aggregation for Daylight Savings
By this stage, we have a data set that includes the dates of interest (last Sunday in every March and last Sunday in every October), but also a whole lot of other dates which aren’t relevant. We need to filter them out. For this purpose we add a filter, in “OR” mode, which selects only the Daylight Savings start or end dates. The data is nearly at the correct granularity now, but just needs a final aggregation step to create one record per year. This is a Matillion ETL Aggregate component, which chooses the minimum and maximum of the filtered dates: the minimum is the Daylight Savings start, and the maximum is the Daylight Savings end.Creating the Output
After the aggregation, the data is correct but the column names are still rather unfriendly. This is easily addressed using a Rename component, which provides a simple mapping interface: The output data is now ready to be materialized into a table for general use. The Rewrite Table component in a Transformation job is perfect for this purpose, and simply writes the data into a new Snowflake table. The completed job looks like this, and is available for download as an attachment. We are now as prepared as we can be for losing an hour’s sleep!Begin your data journey
Want to try Matillion ETL for Snowflake? Arrange a free 1-hour training session now, or start a free 14-day trial.Ian Funnell
Data Alchemist
Ian Funnell, Data Alchemist at Matillion, curates The Data Geek weekly newsletter and manages the Matillion Exchange.
Featured Resources
Blog
Schema Drift and Dynamic S3 Files
This article is part 2 of a three-part series on schema drift ...
BlogBest Practices For Connecting Matillion ETL To Azure Blob Storage
Matillion makes data work more productive by empowering the entire ...
BlogHow to Choose Your GenAI Prompting Strategy: Zero Shot vs. Few Shot Prompts
Large language models (LLMs) produce output in response to a natural language input known as a prompt. The prompt contains ...
Share: