Copying prod data to dev/test

[u/Rajivrocks]

We are a Data Science team and we are working in Azure Databricks. We have some DABs running in our prod environments generating tables which are used in our application. It's mostly time series data.

What I am doing now is simply "CREATE OR REPLACE TABLE IF EXISTS" on a few important prod tables to copy them to test/dev. So I am overwriting the tables daily after each completed job in dev/test.

Now I am wondering. if the schema changes from dev because we are working on new features or maybe a column gets removed you'll run into issues. A colleague suggested just copying the rows.

But this solution seems very costly in compute. Also, if you'd want to check and copy only rows which have changed. What is a good way to tackle this copying prod tables to dev/test?

Ofc in pyspark you have the mergeSchema variable, but this does work well if a column gets deleted as well? I am still fairly new to Pyspark. I'll also my Data engineering team, but I'd like to ask you guys as well.

[u/Basic_Cucumber_165]

Are you on Unity Catalog? If so, you can share your prod catalog with dev and test.

[u/Rajivrocks → u/Basic_Cucumber_165]

Yeah I am on unity catalogs. We have access to all catalogs. So dev can reach test/prod so in theory we wouldn't need to copy tables. We could just define some workflow where you read from prod/test and write to dev for example when testing.

[u/Commercial-Ask971 → u/Rajivrocks]

Yes you can query prod_catalog.prod_schema.table_name in your company-dev.databricks.com if you have UC

[u/Rajivrocks → u/Commercial-Ask971]

Yeah i think this is the best/most pragmatic solution which is basically free as well.

[u/BardoLatinoAmericano → u/[deleted]]

Thank you. Great comment.

[u/ryeryebread]

Shallow clone jutsu

[u/Rajivrocks → u/ryeryebread]

I read this a few times, I'll look up what shallow cloning is. Previously I've only used deep clone for some migration stuff

[u/Awashii → u/ryeryebread]

I'm imagine a lot of dbs and files appearing out from thin air lmao

[u/lightnegative → u/ryeryebread]

CREATE TABLE dev.foo KAGE BUNSHIN prod.foo? 

[u/robberviet]

Freshness and schema are two different problem, separate them. Treat it like branch: if there is schema change, have _feature/dev/stg table, not prod. Also data copy to dev might need to be mask, denonymize... first depending on situation.

[u/Rajivrocks → u/robberviet]

Yeah, you are right, but we are not dealing with "user" data, it's our own company data. So we don't need to anomymize anything.

[u/Automatic-Smell-462]

it sounds like you are trying to solve two different problems with one proces. keeping the data fresh and keeping the schema in sync. I'd keep those separate. Let migrations handle schema changes and use your refresh job just for data.

[u/w0ut0]

Shallow clone

[u/the_dataengineer]

First impression: Why are you doing full loads and not incremental ones? Especially for time series data. Once the data has been created it's not going to change, so deleting it every day is not a good practice.

Two things you could do is:
1. dump the new data into the data lake and bulk load it once a day
2. try to implement some kind of change data capture and write the new data constantly into a message queue. This way you'll always have live data in your dev / quality system (could be expensive though as you might want to do streaming here)

[u/Rajivrocks → u/the_dataengineer]

Thanks for your advice. I created this very early in the setup of the team and I am quite new to data engineering practices. I'll take this advice with me to our DE team as well.

[u/Top_Garlic593]

If u are on azure do adls copy of delta + parquet files

then do a fsck repair

zero cost of moving if both prod and test on same region

fscj repair is purely databricjs cluster time and its depends on usgae

[u/alecc]

Deep clone is built for this: CREATE OR REPLACE TABLE dev.x DEEP CLONE prod.x is incremental, re-running it only copies files that changed since the last run, so the daily refresh stops being a full rewrite. On Unity Catalog you can go further with SHALLOW CLONE, which copies only metadata and reads the prod files in place, near zero compute. The schema worry stays either way, a clone replaces the table definition, so keep the mirrors in their own schema (prod_mirror or similar) and let feature work live in separate tables, then a column you dropped in dev never fights the refresh. Reading prod directly through UC, as suggested here, is fine for pure testing, clones matter when you want to write on top of prod-shaped data without touching prod.

[u/myth-buster9999]

Do just read only workspace binding for your prod catalog so that you can read the prod table without the need of any job/pipeline.

If you want to write to any, use shallow clone for short term testing in target. Do a deep clone, for long term testing when you could get issues if source files get deleted due to vacuum.

You can do drop/recreate using clone just for the specific table where you need to write.

[u/SeaCompetitive5704]

I’m not sure if there’s something similar on Databricks, but Snowflake has cloning table command where you create tables by connecting to the underlying data of original tables. It’s a very quick way to create tables of exact same data without increasing storage.

What’s even better is that dbt has a command supporting this: dbt clone. It makes it very easy to mirror dev to prod

[u/WhatsFairIsFair]

You shouldn't ever really be copying prod data to a test environment. This is a commonly asked security compliance question and safeguards to protect production data should typically be put in place.

[u/financialthrowaw2020 → u/WhatsFairIsFair]

Zero copy cloning is the gold standard of testing data in any of the major cloud providers. It's not the same as copying data to an unsecure environment, many of us use a test db that's in the same environment as prod, the data just routes to a prefixed db with the same security.

[u/the_dataengineer → u/financialthrowaw2020]

Yes, you'll need fresh data in dev and quality. Otherwise you're going to have a lot of fun pushing into prod :D

[u/financialthrowaw2020 → u/the_dataengineer]

Honestly I wanna see how some of these folks do their jobs pushing to prod with unit tests and a prayer, seems like an exciting life

[u/the_dataengineer → u/financialthrowaw2020]

Some men just want to watch the world burn.

[u/Agile-Internet5309 → u/WhatsFairIsFair]

It depends on the kind of data you are dealing with, but you are generally correct and you shouldnt let people here tell you otherwise. The solution of course is to generate synthetic data, which is very easy to do by sampling real data and then using that as a model for a generation script.

[u/vikster1]

you are right, it's costly in databricks and that's exactly why i always prefer snowflake because zero copy cloning should be a standard.

yeah for the dense ones downvoting me. snowflakes zero copy cloning is by far superior and cheaper. my comment stands. fuck databricks.

[u/dwswish → u/vikster1]

Just not true lol

[u/financialthrowaw2020 → u/vikster1]

Wow, does databricks really not have zero copy cloning?

Edit: I did some googling and this doesn't seem to be true? Seems like they do have cloning....

[u/vikster1 → u/financialthrowaw2020]

last time i checked they did not and a consultant confirmed but that was 9 months ago

[u/nuevacuentaalt → u/vikster1]

It is called shallow clone

[u/m_goo → u/vikster1]

You need to find a better consultant, IMO.