text: Guides & Tutorials
  How the Netlify Data Team Migrated from Databricks to Snowflake
  Last month, our data team at Netlify moved data stores from Databricks (DBX) to Snowflake. This was an all-hands-on-deck team effort as we re-engineered our Airflow data ingestion pipelines, migrated nearly 500 dbt models, and updated definitions for dozens of Mode reports and dashboards. This article is in two parts:
  - How we managed the migration, broke it into bite-sized chunks, and kept the team motivated throughout
  - Our reference list of SQL functions and analytics logic which we needed to update
  How We Made The Migration Succeed
  Netlify often uses the "build the plane while you're flying it" metaphor to describe software engineering efforts, and migrating data stores was no exception. The effort required ample communication, coordination, cheerleading, and stakeholder management, all while breaking none of the existing data infrastructure or interrupting anyone's usual data & BI routines. The sustained effort involved a team of four analytics engineers, three data analysts, and one manager; here's how we did it:
  Set Companywide Expectations Early
  To give us space and focus, business leaders agreed that during the migration, we would (1) pause all in-flight data work, (2) not begin any new data work, and (3) only carry out "keeping the lights on" data work. The business continued to have access to all dashboards/reports/tools and could continue querying new data stored/transformed on DBX while we worked on the migration. This arrangement was absolutely vital to our data migration project's success.
  Set Teamwide Expectations Early, Too
  We created one ironclad rule when doing the migration: don't refactor or optimize. The temptation to go, "Oh, while I'm migrating this, I'll just go ahead and clean up the rest of the code in this CTE" is very real, and we recognize that! However, refactoring beyond the bare minimum ("Does the dbt model run succesfully on Snowflake?") creates friction points:
  - PR reviews take longer, because now not only are we checking that SQL functions have been correctly migrated, we're now also checking whether the business logic is still achieving the same output.
  - Data comparison checks aren't as helpful. When migrating models, we would compare values in the legacy DBX model against the new Snowflake model to make sure our data was still the same. If we saw data discrepancies, we wouldn't know if it was due to the refactor or because we goofed somewhere in the basic SQL rewrite.
  - Refactoring steals team focus, which should be only on getting all 500 dbt models and dozens of Mode reports, in order of dependencies, moved over as quickly as possible.
  We communicated this by repeatedly saying:
  Our goal is to migrate SQL, not refactor or optimize.
  Break It Down
  Once the Snowflake user/database/warehouse environment was established, Emilie, our team manager, kicked off a GitHub issue with broad outlines of how the project should be broken down:
  - Do The Easy Stuff First - connect Snowflake to all our other data tools such as Fivetran, Segment, Census, Mode, Transform.
  - Port Data Sources - create a checklist of all data sources. Many were already handled by Fivetran, but we also had custom ingestion pipelines for production & billing data. We organized the data sources by how "easy" they were to migrate (i.e., Fivetran was easier than custom scripts running on Spark) and how "central" they were for downstream models (i.e., production data for teams & sites were prioritized over more peripheral data sources).
  - Focus On Horizontal Slices - if you look at a typical data transformation DAG in dbt, each data source has a corresponding set of base & staging models. We called these "horizontal slices," and each of us was responsible for migrating a set of these slices (usually based on our familiarity with these datasets). We also ran data checks on every staging model to catch and resolve data discrepancies early on (and before they could affect the next set of downstream models). Snapshot models were considered to be part of these slices.
  - Migrate Business Logic - Once enough base & staging models were migrated, we then moved onto fact, dimensional, and marts models. We divided these models that into business areas: core entities (users/teams/sites), finance, product, marketing, HR, etc., and assigned them based on the business teams we usually worked with. Like with base & staging models, we required data checks to be run on all tables during PR review to make sure our DBX & Snowflake numbers agreed.
  - Update Reports & Dashboards - Once enough fact/dimensional/marts models were migrated, we could then start updating our Mode reports and re-point them to Snowflake models. To prioritize these, we first migrated the top 20 most-used reports, then relied on our experience to decide which of the remaining reports we needed to migrate or archive. In a way, this step also served as a dashboard clean-up job! Furthermore, there were report URL links all across the business (Slack conversations, Google slides, Notion docs) and we didn't want to break any of them, so we followed these steps for each report:
  - Clone an existing report and give it an obvious name "[CLONED] Company Metrics."
  - In the cloned report, repoint sources to Snowflake views/tables.
  - In the cloned report, rewrite SQL as needed and fix any tables and charts so they work again.
  - Compare the cloned report (running on Snowflake) to the original report (running on Databricks) and make sure all numbers match.
  - Replace the original report's SQL with the cloned report's SQL. Now the original report is pulling from Snowflake.
  - Delete the cloned report.
  Own It, Overcommunicate It, and Share Progress
  Emilie's GitHub issue was a mega-issue (we quickly memorized the issue number - may #1873 go down in Netlify's history books) that outlined the work to be done in the following several weeks, but what would happen when the rubber hit the road? We're a distributed team that works async. How did we keep each other updated and avoid getting into each other's way?
  - We all owned the main GitHub issue, adding to it or making changes whenever needed. At last count, this issue had 73 checklist items!
  - Each checklist item was tagged with an owner, and each spawned a child issue for tracking work.
  - We prioritized reviewing each other's PRs.
  - We overcommunicated in our team channel. We flagged any problems or blockers in the channel (as well as on the relevant GitHub issue). We also ran async check-ins: we'd start a new conversation thread asking each one to respond with answers to (1) What’s been going well?, (2) What are you going to work on next?, and (3) What is blocking you/what are you unsure about? This helped us understand where each one of us stood on the migration effort and surfaced potential upcoming blockers, allowing us to proactively resolve or plan around them.
  - We frequently relied on help reviewing and resolving data discrepancies. Staring at a series of extremely-similar-looking spreadsheets will test anyone's soul, and a pair of fresh eyes quickly spotted things (and solutions!) we couldn't see before.
  The Final Sprint
  We were excited as more and more of the 73 checklist items on the main GitHub issue were checked off. However, near the end of any long-running project, things can get messy: the team's tired and there's a bunch of smaller, miscellaneous things that don't fit neatly under any single owner or function. To motivate the team and wrap up things, we compared all files in our dbt project folder for Snowflake against the existing dbt project for DBX (using
  diff). This produced a list of all dbt files that weren't yet in our Snowflake project, which became our "punch list" -- another checklist! We all combed through the list, checking off items as (1)
  already-migrated (but named differently or in a different folder), (2)
  won't-migrate, or (3)
  need-to-migrate. Ownership was quickly assigned to remaining
  need-to-migrate items, and we built a progress box reporting % of remaining models migrated. We reported that number almost every day, which motivated us to reach 100%! We also shared GTD-style time management strategies such as the Pomodoro Technique to help us push through the more tedious aspects of data comparisons and report migrations.
  Retro
  At our post-migration retro, our list of "things that went well" was much longer than our list of "things that could've gone better," which was great. We were quite happy with the basic structure and planning of the migration work, and how every one of us was vocal about what we needed to continue or unblock our individual work, which allowed the team to sustain momentum throughout the project. We thought we could've been better at estimating the time needed to re-engineer our custom data pipelines, which blocked some horizontal slice work which were central for building several of our core entity models. We also found it challenging to pause all other data work -- as analysts, we naturally want to be the best data partners we can be for the business. The lessons we learned here were valuable, and we hope sharing what we did will benefit other data teams as well.
  Migrating SQL & Analytics Logic
  Time for the SQL part! Because each database uses a different SQL dialect, a data migration project typically involves rewriting the SQL in model definitions and BI reports. Our experience was no different, moving from Databricks' dialect based on Apache Spark SQL to Snowflake's dialect which many people on our team were already comfortable working with. To share tips and learnings, we started up a Notion doc listing each SQL difference we stumbled across. Here's what we learned:
  Time-Savers
  We loved using the PostgreSQL-style operators
  ::and
  ||which made our SQL easier to read, especially when nested in other functions. We used these wherever we could, and they show up in some of the other tips below.
  -- Casting a field to a different data type (i.e., varchar)
  cast(col_name as varchar) -- DBX & Snowflake
  col_name::varchar -- Snowflake
  -- Casting a timestamp to a date
  to_date(timestamp_val) -- DBX & Snowflake
  timestamp_val::date -- Snowflake
  -- Concatenating strings
  concat(col_1, col_2) -- DBX & Snowflake
  col_1 || col_2 -- Snowflake
  Functions We Changed
  Moving from DBX to Snowflake meant some function names changed. Here are the ones we came across:
  -- Functions that changed, DBX -> Snowflake
  if() -> iff()
  boolean(col_name) -> col_name::boolean
  collect_list(col_name) -> array_agg(col_name)
  concat_ws(", ", collect_list(col_name)) -> listagg(col_name, ', ')
  Some date & timestamp functions also changed! They're covered in a separate section below.
  We missed some handy array-based functions:
  explode(),
  array_distinct(),
  array_except(), and
  reverse(). We develops workarounds in Snowflake to replicate these, relying on
  lateral flatten which is a more generic approach that also works for other variant types, not just arrays. Here's an example for Spark SQL's
  explode() function, and we studied the "practical example" at the end of of Snowflake's
  flatten documentation.
  -- A SQL table with a team_id column and an array of user_ids in each team.
  -- We want to "explode" (or fan out) the user_ids array so that there's now
  -- a row for each team_id and user_id pair.
  -- DBX
  team_id,
  explode(user_ids) as user_id
  from tbl
  -- Snowflake
  team_id,
  flattened.value::string as user_id
  from tbl,
  lateral flatten(input => user_ids) as flattened
  -- It will "explode" the input (user_ids) and put it in a special, temporary
  -- view named "flattened." The contents of "flattened" can now be called
  -- in the main SELECT clause.
  Our workarounds for
  array_distinct(),
  array_except(), and
  reverse() were more involved:
  To reduce an array to distinct items only, we flattened the array, did
  select distinct, then used
  array_agg()to roll it back up into an array.
  To get values in array A that weren't in array B, we flattened array A, used
  array_contains()to check each (flattened) element of array A in array B, filtered where
  array_contains()returned
  false, then used
  array_agg()to roll up any remaining array A elements into an array.
  We wrote a dbt macro to replicate
  reverse()functionality (the parameter
  max_sizeis the expected maximum number of elements of the array):
  {% macro reverse_array(column_name, max_size) %}
  case array_size({{column_name}})
  {% for i in range(max_size) %}
  when {{i+1}} then array_construct(
  {%- for j in (range(i+1)|reverse) %}
  {{column_name}}[{{j}}]
  {%- if not loop.last -%},{%- endif -%}
  {% endfor %}
  )
  {% endfor %}
  end
  {% endmacro %}
  Dates and Timestamps
  Every database handles dates & timestamps differently, and this was no exception.
  -- Functions that changed, DBX -> Snowflake
  date_sub(created_date, 3) -> dateadd(day, -3, created_date)
  from_unix_time(unixtime) -> unixtime::timestamp_ntz
  There's further nuances in how dates & timestamps are handled differently in Snowflake:
  Snowflake has multiple timestamp variations, encoding timezone information differently. One of our team's mantras is "timezones are the worst" and we defaulted to using
  timestamp_ntz("no time zone") wherever possible. This is also Snowflake's default variation when using
  timestampin general.
  The order of parameters in date/timestamp arithmetic functions was different! This tripped us up a few times, and is why data comparison checks were so important to spot these gotchas:
  -- Get the number of days between created_date and updated_date
  datediff(updated_date, created_date) -- DBX
  datediff(day, created_date, updated_date) -- Snowflake
  While
  to_date(timestamp_value)worked fine in Snowflake, we preferred to use
  timestamp_value::date.
  Formatting a date/timestamp as a string? While DBX's
  date_format()is equivalent to Snowflake's
  to_char(), symbols such as
  YYYY-MM-DDare different. Here's Snowflake's symbol list.
  JSON
  We felt Snowflake had better support for working with JSON, and were excited to use this functionality. Snowflake allows querying JSON blobs using either dot notation or bracket notation; we chose to use only bracket notation. Either way, our SQL looked much simpler on Snowflake!
  -- Querying JSON
  get_json_object(event_msg, '$.user_id') -- DBX
  event_msg['user_id'] -- Snowflake
  While Snowflake implicitly casts any extracted JSON value as a string, we made sure to explicitly cast them to the appropriate data type using
  ::notation:
  -- Casting JSON values
  event_msg['user_id']::string as user_id -- Snowflake
  If we had JSON data that was in string format, we used
  parse_json() to cast them to the
  variant type, which allows for JSON querying using bracket (or dot) notation. But sometimes Snowflake would throw errors if the JSON blob was bad. Where it made sense we used
  try_parse_json() instead, which allows Snowflake to fail quietly and return NULL instead of throwing an error.
  New Shiny Toys
  We like shiny new toys, and Snowflake's list of SQL functions and operators are full of these. Some gems we're calling out here:
  ilikeis a case insensitive version of
  like. No more
  lower(col_name)in
  whereclauses!
  - Like the above (see what we did here?),
  ilike anyis a case insensitive version of
  in, as in
  select * from tbl where col_name ilike any ('a', 'b')
  qualifylets you filter by window functions in the same query. Saves us a CTE! (This, however, fell into refactoring/optimizing territory. So we avoided using that during the migration, but are now using it wherever we can!)
  group by cubeis really handy for data comparisons, because it groups by all combinations of
  group byAND provides subtotal and total rows!
  try_to_*()are a suite of error-handling conversion functions which lets you handle casting values as different data types without throwing errors (as long as you know what you are doing, though!).
  Miscellaneous
  A few things that didn't fit in any of the above:
  - Snowflake's behavior around uppercasing/lowercasing table and column names can be confusing. Where DBX would downcast SQL statements, Snowflake upcasts them. This means
  select id from tblis interpreted by Snowflake as
  SELECT ID FROM TBL. But quoted names remain in their original case, so
  select "id" from tblis interpreted as
  SELECT "id" FROM TBLand will not work if
  tblhas column name
  ID. To deal with these, dbt comes with settings to handle quoting in Snowflake [docs] [discourse]. We also needed to consider column names in CSVs used by
  dbt seed. If they are lower-case, they will need to be enclosed in double-quotes when being queried. We instead chose to edit the CSVs and uppercased the column names.
  - DBX has the
  longdata type, which are for really long integers. This doesn't exist in Snowflake, and we used
  bigintinstead.
  - Division by zero! In DBX,
  select 1/0returns
  NULL, but in Snowflake, it throws a
  Division by zeroerror. The Snowflake function
  div0()will handle these errors and return a
  0instead.
  Migration Checklist
  Could you tell that we're obsessed with checklists in general? (We're also fans of "The Checklist Manifesto" -- not recommended for reading while on a plane or waiting for surgery!) Here's our checklist we used for every PR that involved migrating a dbt model.
  - Ran all models locally and they built successfully
  - Tested all models locally and they passed
  - If an incremental model, it has been run successfully twice (i.e., once as a full-refresh and once as an incremental build)
  - Done manual data comparisons (count(*), min(col), max(col)) between the DBX & Snowflake version of marts tables. Another method: take a BI aggregation query (e.g. revenue by month), and run it across both systems and compare the results in a spreadsheet or in a Mode report.
  - Removed DBX
  {{ config() }}parameters:
  file_format,
  partition_by
  - Checked that your models are materialized the same way as before (check
  dbt_project.yml)
  - Bonus: If you've learned new DBX-Snowflake migration tips, please contribute to the Snowflake Migration Tips doc!
  Conclusion
  Moving our data ingestion and transformation layer from Databricks to Snowflake required the Netlify data team to re-engineer our data ingestion pipelines, rewrite the SQL defining nearly 500 data models, and update dozens of reports and dashboards. We certainly learned a lot during this project, and we hope these lessons will be useful for other data teams undertaking their own data migration. Major thanks and many more props to the Netlify data team: Lauren Adabie, Paige Berry, Fran Lozano, Adam Stone, Tom Nagengast, Laurie Voss, and Emilie Schario, our data team manager!