0

I am looking for any general guidelines to allocate table space quota to different layers/schemas in ETL flow of a data warehouse (% of total space in each layer). As per my research, ETL flow can consists of 4 layers for a data warehouse:

  1. Staging -truncate and load data from source files.
  2. ODS- Type 1 persistent tables.
  3. Transformation layer.
  4. DWH layer- Final dimensional model layer.

I understand space requirement may vary based on project requirements, however still any general guideline (if any such in data warehousing and ETL space) to estimate the space will be helpful.

Thanks,

3
  • If you have a limited total space, why do you want to impose quota on subsystems that are not independent but form a complete solution? Much better would be to start without quotas and observe the system's behavior, possibly adjusting parameters when resource consumption seems too high. Commented May 12, 2020 at 6:27
  • Thanks Hans !!! yup , seems good and useful advice. Commented May 12, 2020 at 6:29
  • This doesn't mean that quota are not useful, of course, for example when resources must be protected against accidental or malicious overuse, or for accounting purposes. Commented May 12, 2020 at 7:02

1 Answer 1

1

Required space will undoubtly not only depend on the project requirements, but on the specific technology. Said that, the most simple approach here I can think of which is pretty independent from the technology is to start with a small (but representative) subset of the whole data, lets say 5% of what you are going to process, run the ETL, measure the space consumption and then extrapolate.

Especially layers 1, 2 and 4 will usually grow linearly with the amount of input data, layer 3 may have a fixed size or grow linearly as well, depending on the specific kind of transformation (and if you don't try something stupid like storing the result of a cross join over two huge tables intermediately). That should give you a rough size estimation which is usually precise enough for most practical purposes.

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.