Hey @barchiel33 ,

After reviewing your context further, I believe the most effective approach would be to set up an automated pipeline within Databricks that periodically extracts data based on the frequency you need (daily, weekly, hourly, etc.), creating an optimized intermediate or final table that is ready for your analysis.

Instead of running ad-hoc queries directly from your local environment, the idea is to create an automated flow in Databricks that runs these extractions on a schedule and stores the results in a table. This would address the performance and latency issues you’re facing because the table would reside in the cloud environment where your Databricks instance runs, enabling virtually instant queries.

This table could either be managed directly by Databricks (a managed table format Delta) or stored in a specific cloud storage location like Azure Storage or AWS S3 (an external table). In either case, Databricks would simply maintain a logical pointer to that location, facilitating both management and governance.

Since you’re using Spotfire, I understand that your current workflow involves pulling data from Databricks into Spotfire for visualization or exploratory analysis, likely via Pandas. Although I’m not very familiar with Spotfire, these types of tools generally allow you to connect directly to tables or specific cloud storage locations (like S3 or Azure Blob Storage). Therefore, it’s possible that you could directly read from the table created by the pipeline.

However, if Spotfire doesn’t natively support Delta format for example, we could write the periodic data output from Databricks into Parquet or CSV files in a specific cloud storage location (Location). Spotfire could then connect to this location to consume the data directly.

This way, you’d have a fully automated, robust pipeline, optimized for fast querying, eliminating the latency you’ve encountered, and simplifying your day-to-day workflow. Spotfire would always have access to up-to-date data, available immediately.

I look forward to hearing your thoughts or any questions you may have regarding this approach.

Best regards, 🙂

Isi