When profiling Snowflake, allow users to choose between system and bernoulli sampling.

[hendrix04]

Is your feature request related to a problem? Please describe. Task #8336 made a change to use bernoulli for sampling so that views could be sampled. Turns out that the performance hit due to this is much larger than one might have expected. One of my table scans went from a 1 minute scan to a 13 minute scan due to the change.

Describe the solution you'd like When profiling a Snowflake table, allow users to choose what type of sampling that they want to use.

Describe alternatives you've considered Spending a lot of money on long running queries ;)

[TeddyCr]

@hendrix04 thanks for opening this. I think users should be able to choose (i.e. we should not force one or the other). While it takes longer for BERNOULLI to run, SYSTEM has one big drawback in which not all rows have the same probability to be picked up in the sampling (which is not the case with BERNOUILLI).

As both have tradeoffs, we should let user decide. 🙂 I'll circle back with more details.

[hendrix04]

@TeddyCr, Any more details here?

[TeddyCr]

Hey @hendrix04, we'll need to do a few things:

1. Update the profiler metadata pipeline json schema [here]. Here we'll need to add 2 things:
   • add Include views field. You can check what we do for the databaseServiceMetadataPipeline.json From there you can update the filter_entities to exclude views if views should be excluded. The table entity has a table type field table.json.

https://github.com/open-metadata/OpenMetadata/blob/158bd4b9cd5a7fae71dd10d3de1dc7520fa659d3/ingestion/src/metadata/orm_profiler/api/workflow.py#L318-L353

• add sampling method field. It should defaults to null as this feature will only be available for snowflake. this will be added in the same json schema than where you would make the previous edit. Then we should check if that method is set and pass it to be used in the sampler.

Let me know if you have other questions

[hendrix04]

@TeddyCr, do you think I should do #8429 in the same PR as this task?

[TeddyCr]

Let's tackle them separately. 🙂

[nakaken-churadata]

Can I try this issue for my next challenge?

[ayush-shah]

Sure @nakaken-churadata, go ahead, Let me know if you require any help around the same and if you need more context here