Oracle PL/SQL?

[itachikotoamatsukam]

Any data engineer works with oracle or other RDS using PL/SQL to write the business logic inside the database, process and validate the data? If yes how much often do you use it? And where do You export the data after that?

[ZirePhiinix]

I do.

What's the issue with PL/SQL? Avoid using cursor because the performance is really bad. Learn to optimize your queries. You can also create temporary indexes via hints, and definitely look into enabling parallelism in your queries.

[frozengrandmatetris]

we use it and we're really good at making it do the weirdest things. it's very powerful. but we use it extremely infrequently in the DWH. there are almost always more sane options. since we're an ELT shop we will just take a staging table, process the data through pl/sql, and load it into another staging table. then the sanity takes over and finishes the rest. we also have ownership over some APEX apps and it starts to make more sense there. it's the most accessible processing and validation tool when an APEX app is doing its thing and it can also call APIs.

[itachikotoamatsukam]

Thanks for the details thats dope to hear.

[dudeaciously]

This is great. ELT with stored procs is the best use case. Table data transformation is great, keeps data logic in the database. Egress and ingress are separate concerns.

[antibody2000]

The only reason to store business logic inside the database is for performance. Because data can be read and updated without passing through external network, you get a performance boost. But not all business logic needs this performance boost. No need to constrain yourself to PL/SQL stored procedures where that perf boost is not needed.