Data Transformations

ETL vs ELT

Panoply’s data sources and many online ingestion systems have changed the ingestion process from the classic ETL (Extract, Transform, Load) to an agile ELT (Extract, Load, Transform) process. You can read more about the differences between these two approaches.

Using ELT, Panoply moves the processing of data out of the ingestion flow, and allows users to have the data ready for use in its most raw form. All the aggregation and calculation processes occur post-ingestion. There are multiple ways to manage such post-ingestion processes. In Panoply, the main way to achieve them is by using views.

Views allow you to save DQL (Data Query Language) queries and give them aliases. These queries will return the result set directly to the SQL client or BI tool. With this solution, you can change business logic and questions on the fly without relying on rerunning procedures for historical data.

Views vs Pre-processing

In data analytics, agility and flexibility are very important for ongoing and ad hoc analysis. You want to have the ability to ask new questions all the time without being limited in what data was saved and how it was aggregated.

Transformation views have several benefits over pre-processing all of the data:

• Immediate availablity: As soon as new data comes in, you can query it. There's no delay due to processing thousands of lines of ETL code.
• Agility: When you modify your views, the changes take place immediately, for all future and past data, with no need to re-run the process over and over again. You can iterate quickly and get the results you need in seconds.
• Speed: Because ad-hoc queries can benefit from filtering and indexing that's not available for pre-processing all of the data ahead of time, your views can immediately access the raw data you're searching for.
• Portable SQL: Unlike many ETL tools, you're not vendor-locked into a specific technology that would make it difficult to port or use outside of Panoply.

How to use views

In the Panoply platform, we allow you to easily create views. Yes, you will need to know SQL to create them, but we assume that if you plan to ask complex questions you know how to ask them.

In Panoply’s workbench, you can write, run, and save queries.

To get started with views:

1. Click on Workbench in the top menu.
2. Type your SQL query. We support standard ANSI SQL. For more information about basic SQL, see our Basic SQL document.
3. Click Run to execute your query.
4. View the results directly in the Panoply workbench. The Panoply workbench display is limited to 100 records. The saved query will not have this limit when querying it with your BI tool.
5. Use the workbench menu to save or export your results.

The workbench menu includes:

• Close: Collapse the list of schemas, tables, and fields to make more room for the query and results areas.
• New: Start a new query.
• Open: Open a previously saved view. You can open views that were saved by external workbenches as well.
• Save/Save as: Save the query as a view.
• Export: Export the result set. The export is not limited to the 100 records visible in the Panoply workbench, but is limited to 1,000,000 records.

When you save a query as a view you can name the view and place it in a schema. The schema should exist beforehand.

After you have saved the view you can see it in the Tables screen. You can also query it directly in Panoply and in any external tool that you’ve connected to your Panoply account.