Is there a way to export the SQL produced by an insight in order to test against the SQL that I am trying to convey in the insight? My goal is to debug & test my insights against what I know produces the expected output

Hi Phillipe, perhaps this recent Community post will help you with this? https://community.gooddata.com/metrics-and-maql-57/get-sql-behind-metrics-541?postid=1435#post1435

Hi Michael! That's a great start thanks! Is there any way to make the SQL identifiers more readable/related to the names of the datasets imported?

Unfortunately not @Philippe Hébert. Please note that in case of the hosted GoodData platform, the physical database is not 1:1 copy of the output stage tables. The data is normalized and optimized into a physical data model, some surrogate keys generated and applied and a series of table caches is used. So it is not that straightforward to read the actual SQL. In general - tables with f_ prefix are those representing the tables from the output stage (roughly) and the mid-part of their their name is based on the identifier. so f_invoice_abvcdsf would be representing dataset.invoice. And the names of columns are also usually based on the identifiers of the fields. Tables starting qt_ are some caches usually generated by previous queries and are not directly related to the datasets you uploaded (are constructed from them though).

Thanks for the info Michal. So I can only get a partial picture of how the insight is computed, given that some of the structure is different and does not reproduce the LDM.

Yes, it might be still readable since the f_ tables are almost identical to your datasets, but it can be challenging to read. If you are debugging a particular insight, you might try to use the same tool (explain) but use different “explain type”. For example if you switch to the “Optimized Query Tree” it will display a tree of logical/mathematical operations at the very bottom is the data at the source level (attributes, facts) and at the very top is the insight with all its metrics. The attributes and facts are displayed with their IDs but if you hover those IDs it will show you the title. The numbers in square brackets always define the dimensionality (combination of attributes) on which that particular operation is happening. So with this you can at least see the logical order of the operations and for example where the filter is being applied, if the data are first aggregated and then multiplied or vice versa etc.

Great, thanks Michal!