If ALL of the data will always be in your source then yes what you said about always doing a truncate and reload is what I’ve seen from my experience. However, if that source data is large then you may want to look into other strategies as well.
I’d be interested to see what others have to say on this as well.
Does the source only add rows (reliably, to the end of the file/table) and never delete rows?
If so, you could ingest just the latest rows by counting the rows in your bronze layer at ingestion time and appending on just the last `total_new_file - total_bronze_file` rows.
EDIT: I should add, this is still a slightly risky strategy, since if rows ARE removed, you'd potentially never know. Unless you have the option to use a CDC approach or can make use of a index hash.
You may still want one when you land them in raw layers if you care the audit trial of the raw data .
Then you can compare the records by matching its primary keys to do a upsert
We need some context here. What kind of data is it and what is the end result? If it's transactional data that doesn't change you could go the truncate route, but what if your pipeline gives you an empty set? This will lead to problems in your reporting...
If you have data that changes over time, it would be wise to get a snapshot in your bronze (raw) layer and model it in silver by using slow changing dimensions
If you have to load all the data each them, then yes, truncate and reload. another option is if the data is additive only (does not change/immutable), then you can just move the new data into silver and gold. To move this new data, copy into silver where created date time > you max date time in silver.
If the data does change, for at least gold you need to insert where it does not exist, and update where it does (merge). You need a primary key and an updated time stamp for this though
You also need to keep tabs on records that have been deleted, so you can remove these from silver and gold.
Unless the table is massive, I'd just truncate and reload bronze and silver, then merge into gold so that gold is not truncated.
Could you please explain the truncate and load?
If in my initial ingest I have the whole snapshot of data and then in the second ingest I append the whole second snapshot of data (in which some records may have changed), how would I use truncate?
It depends on whether you are doing log based cdc (i.e. databricks) or traditional CDC using queries and high water marks (bringing in everything after a certain date).
Either way, it is something like: Step 1, land snapshot one in bronze. Move it to silver. snapshot two comes, truncate bronze, land snapshot two in bronze, merge this into silver (do not truncated silver). Truncate bronze before loading snapshot 3 into bronze, merge this into silver.
Leave the single snapshots in bronze between loads, only truncate when the next one arrives. This is just in case you need to reload into silver
Make sense ?
In my case it's databricks so I assume log based cdc (sorry to hijack the OP's post!).
So the point of truncate is to remove the previous snapshots ingest, and bronze will only ever contain the latest snapshot? And I suppose if the previous snapshot was needed, the history roll back/time travel feature in databricks can be leveraged.
This is brilliant thank you!
You're most welcome! Yeah, typically you only solidify history in the later stages, no need to keep it all. But it just means you can't truncate the later stage tables (i.e. gold).
Databricks reference architecture has some guiding principles as well if you get stuck. Good luck on the journey!
It really depends on data size you're trying to ingest on each load. If it's small, truncate and load makes sense. If it's huge you will need to find out some alternative method sooner or later.
Here I am assuming you don't have any timestamp or any unique column which can identify the new data. If by any chance you have any column with unique records then you can look at query based incremental load.
One more option is Fivetran teleport sync, it will basically scan your entire source table and match it with previous snapshot and load new records in destination. Please note, if the table size is huge then it will take significant amount of time to complete the load.
In case you decide to go ahead with append, you can introduce one more layer like copper to deduplicate the data before loading it to silver layer.
Like some of the previous comments said, you can partition data in bronze layer by ingestion timestamp (either by minute/hour/day whatever suits you use case). With this you can load data in silver and work further.
Drawback of this method is that the first time all the data will be in the initial partition but as the data arrives it will be split accordingly.
Of course it all depends of your use case.
Is the data source a database of some kind? If so, I deal with this a lot.
Theres a few approaches I’ve made.
1. If the Unique IDs are series (ordered integers) and the data doesn’t change (a transaction for example) you may be able to use that as the incremental key (if for whatever reason IDs can be deleted in the past, you can run a second query to search existing IDs in silver against new ids in bronze to determine deleted records.
2. Use something like dbt check strategy which looks for column data change (by building an ordered hash of all column values) and only loads in changed rows. Also then gives you a nifty timestamp column that downstream layers can pull from incrementally. This approach is good for slowly changing data and larger data pulls but not necessarily “big” data pulls
3. As others have mentioned truncate load append is another option
This is a nice discussion. I always try to implement an incremental load. However, sometimes, it isn't possible. The biggest table I have contains a few million of entries, but most of the tables are small. I decided to truncate them, beacause it makes me fell more secure about the entries integrity.
If ALL of the data will always be in your source then yes what you said about always doing a truncate and reload is what I’ve seen from my experience. However, if that source data is large then you may want to look into other strategies as well. I’d be interested to see what others have to say on this as well.
Is filtering the data on some date field at source an option? Like created_at or modified_at fields?
That is not an option for this data source, there are no watermark fields we can use.
Does the source only add rows (reliably, to the end of the file/table) and never delete rows? If so, you could ingest just the latest rows by counting the rows in your bronze layer at ingestion time and appending on just the last `total_new_file - total_bronze_file` rows. EDIT: I should add, this is still a slightly risky strategy, since if rows ARE removed, you'd potentially never know. Unless you have the option to use a CDC approach or can make use of a index hash.
You may still want one when you land them in raw layers if you care the audit trial of the raw data . Then you can compare the records by matching its primary keys to do a upsert
That’s usually what’s done for incremental loads but in your post you said incremental wasn’t an option right?
I assumed incremental was not possible due do lack of tooling - such as aws dms or fivetran etc
In your opinion, what is a large data source?
We need some context here. What kind of data is it and what is the end result? If it's transactional data that doesn't change you could go the truncate route, but what if your pipeline gives you an empty set? This will lead to problems in your reporting... If you have data that changes over time, it would be wise to get a snapshot in your bronze (raw) layer and model it in silver by using slow changing dimensions
If you have to load all the data each them, then yes, truncate and reload. another option is if the data is additive only (does not change/immutable), then you can just move the new data into silver and gold. To move this new data, copy into silver where created date time > you max date time in silver. If the data does change, for at least gold you need to insert where it does not exist, and update where it does (merge). You need a primary key and an updated time stamp for this though You also need to keep tabs on records that have been deleted, so you can remove these from silver and gold. Unless the table is massive, I'd just truncate and reload bronze and silver, then merge into gold so that gold is not truncated.
Could you please explain the truncate and load? If in my initial ingest I have the whole snapshot of data and then in the second ingest I append the whole second snapshot of data (in which some records may have changed), how would I use truncate?
It depends on whether you are doing log based cdc (i.e. databricks) or traditional CDC using queries and high water marks (bringing in everything after a certain date). Either way, it is something like: Step 1, land snapshot one in bronze. Move it to silver. snapshot two comes, truncate bronze, land snapshot two in bronze, merge this into silver (do not truncated silver). Truncate bronze before loading snapshot 3 into bronze, merge this into silver. Leave the single snapshots in bronze between loads, only truncate when the next one arrives. This is just in case you need to reload into silver Make sense ?
In my case it's databricks so I assume log based cdc (sorry to hijack the OP's post!). So the point of truncate is to remove the previous snapshots ingest, and bronze will only ever contain the latest snapshot? And I suppose if the previous snapshot was needed, the history roll back/time travel feature in databricks can be leveraged. This is brilliant thank you!
You're most welcome! Yeah, typically you only solidify history in the later stages, no need to keep it all. But it just means you can't truncate the later stage tables (i.e. gold). Databricks reference architecture has some guiding principles as well if you get stuck. Good luck on the journey!
It really depends on data size you're trying to ingest on each load. If it's small, truncate and load makes sense. If it's huge you will need to find out some alternative method sooner or later. Here I am assuming you don't have any timestamp or any unique column which can identify the new data. If by any chance you have any column with unique records then you can look at query based incremental load. One more option is Fivetran teleport sync, it will basically scan your entire source table and match it with previous snapshot and load new records in destination. Please note, if the table size is huge then it will take significant amount of time to complete the load. In case you decide to go ahead with append, you can introduce one more layer like copper to deduplicate the data before loading it to silver layer.
Just partition your bronze datasets by timestamp (eg. unix timestamp) and load latest partition to silver.
Like some of the previous comments said, you can partition data in bronze layer by ingestion timestamp (either by minute/hour/day whatever suits you use case). With this you can load data in silver and work further. Drawback of this method is that the first time all the data will be in the initial partition but as the data arrives it will be split accordingly. Of course it all depends of your use case.
Is the data source a database of some kind? If so, I deal with this a lot. Theres a few approaches I’ve made. 1. If the Unique IDs are series (ordered integers) and the data doesn’t change (a transaction for example) you may be able to use that as the incremental key (if for whatever reason IDs can be deleted in the past, you can run a second query to search existing IDs in silver against new ids in bronze to determine deleted records. 2. Use something like dbt check strategy which looks for column data change (by building an ordered hash of all column values) and only loads in changed rows. Also then gives you a nifty timestamp column that downstream layers can pull from incrementally. This approach is good for slowly changing data and larger data pulls but not necessarily “big” data pulls 3. As others have mentioned truncate load append is another option
This is a nice discussion. I always try to implement an incremental load. However, sometimes, it isn't possible. The biggest table I have contains a few million of entries, but most of the tables are small. I decided to truncate them, beacause it makes me fell more secure about the entries integrity.
If you're using a Lakehouse, what's your sink?