I’m trying to setup a persistent model for the first time in a postgres db integration. I have been following instructions from these references:
- Transform Model | Holistics Docs
- Model Storage Settings | Holistics Docs
- Persistence Query Model | Holistics Docs
I don’t see evidence that holistics is attempting to make the persistent model, so I must have something wrong.
user for datasource: holistics_user
schema for persistence: holistics_persistence
Snip from the persistent model.aml file:
Model p_clarus_c_survey_question_answer {
type: 'query'
label: "p_Survey Questions and Answers"
description: 'Full list of questions with answers for surveys. This is the base for distinct question types'
data_source_name: 'compass'
owner: '[email protected]'
query: @sql
select r.id as r_id,
c.name as client,
r.created,
r.type as survey_type,
t.question,
t.answer
from {{ #clarus_c_survey_response_light r }}
JOIN
{{ #public_client c }} ON c.slug = r.client,
lateral jsonb_each_text(r.custom_data -> 'survey'::text) t(question, answer);;
models: [
clarus_c_survey_response_light,
public_client
]
persistence: FullPersistence {
schema: 'holistics_persistence'
}
dimension r_id {
label: "R Id"
type: "number"
hidden: true
definition: @sql {{ #SOURCE.r_id }};;
}
<snip>
}
/schedules.aml
// import './common_models/clarus/value_survey/1_base_models/clarus_c_survey_response_p_question_answer.model.aml' {p_clarus_c_survey_question_answer}
const schedules = [
// Schedule orders model to run every 15 minutes
Schedule { models: [p_clarus_c_survey_question_answer], cron: '*/15 * * * *' }
]
The import command is commented because I’m getting an error from the online editor stating:
[Syntax Error] Cannot find name 'import'. (2:1)
When I publish the dataset, I get the CTE in the executed query:
WITH "p_clarus_c_survey_question_answer" AS (
select r.id as r_id,
c.name as client,
r.created,
r.type as survey_type,
t.question,
t.answer
from (
WITH "clarus_c_survey_response_light" AS (
SELECT
c.name as client,
r.id,
r.created,
r.type,
r.user_id,
r.client_id,
r.user_uuid,
r.custom_data
FROM public.clarus_survey_response r
join (
SELECT
"public_client"."id" AS "id",
"public_client"."slug" AS "slug",
"public_client"."name" AS "name",
"public_client"."type" AS "type",
"public_client"."status" AS "status",
"public_client"."custom_data" AS "custom_data"
FROM
"public"."client" "public_client"
) AS "c" on c.slug = r.client
)
SELECT
"clarus_c_survey_response_light"."id" AS "id",
"clarus_c_survey_response_light"."created" AS "created",
"clarus_c_survey_response_light"."type" AS "type",
"clarus_c_survey_response_light"."user_id" AS "user_id",
"clarus_c_survey_response_light"."client_id" AS "client_id",
"clarus_c_survey_response_light"."user_uuid" AS "user_uuid",
"clarus_c_survey_response_light"."custom_data" AS "custom_data",
"clarus_c_survey_response_light"."client" AS "client"
FROM
"clarus_c_survey_response_light"
) AS "r"
JOIN
(
SELECT
"public_client"."id" AS "id",
"public_client"."slug" AS "slug",
"public_client"."name" AS "name",
"public_client"."type" AS "type",
"public_client"."status" AS "status",
"public_client"."custom_data" AS "custom_data"
FROM
"public"."client" "public_client"
) AS "c" ON c.slug = r.client,
lateral jsonb_each_text(r.custom_data -> 'survey'::text) t(question, answer)
)
SELECT
"public_client"."type" AS "pc_t_f101f2",
COUNT("p_clarus_c_survey_question_answer"."created") AS "c_pccsqa_c_388ce0"
FROM
"p_clarus_c_survey_question_answer"
LEFT JOIN "public"."client" "public_client" ON "p_clarus_c_survey_question_answer"."client" = "public_client"."slug"
GROUP BY
1
ORDER BY
2 DESC
LIMIT 100000
Is the import directive required?
In psql, I don’t see any footprint from holistics in the new schema.
I’ve waited for the 15m boundary for cron to run and still no dice. How do I debug where it’s failing?