Best method to perform scheduled ELT on bigquery tables

[brianlee9779]

Hey All,

    A bit of context: my company is currently using Dataprep (Trifacta) as our ELT (I say ELT because all of our raw data is loaded into bigquery initially using fivetran connectors) tool where the source and destination are both bigquery tables. We are going to move off of the product soon and I've been looking at different ELT solutions to replace Dataprep, and dataflow (what Dataprep is run on) seems to be a recurring product to use. However, as someone who isn't as adept data engineering or dev wise, I noticed another native solution that seems like it would work easily. Google Bigquery has its own scheduling functionality that lets you transform and write data to new tables within bigquery and it seems like a no brainer for someone who is advanced in SQL but not so much data pipelining. 

Now that you have the long and drawn out context, my question is this: is there a reason (or reasons) why the bigquery ELT approach above is not a good option? If it's not, is there a better approach that you would suggest? I want our future solution to be sustainable and scalable regardless of user access, but I'm skeptical that this approach is foolproof.

[shankul]

Based on what you have described, I'd recommend look at Dataform - a product that Google acquired. Its primary focus in ELT using SQL. Without knowing a lot more details, it is hard to comment on what your future needs may be but you can very easily leverage Dataflow if any of your future use cases required sophisticated ETL.