G
Bill Reynolds
Bill Reynolds
·Posted in Template Studio

SELECT * in a template?

I have three template language questions that I can't find documented anywhere.

I'm trying to compose a CTE for the following SQL. There are three things I can't get to work:

1. SELECT * - can you do that in a template?
2. QUALIFY ROW_NUMBER() OVER ... how can you use QUALIFY after the FROM
3. Forcing spaces into "expressedBy ROW_NUMBER() OVER (" - the generated SQL for this ends up being "ROW_NUMBER()OVER(" without any spaces.

SELECT *

FROM CTE_PIVOT_SOURCE

QUALIFY ROW_NUMBER() OVER (PARTITION BY account_id, IDENTIFIER_TYPE__C, EFFECTIVE_START_DATE, EFFECTIVE_END_DATE ORDER BY SYSTEMMODSTAMP DESC) = 1

1 reply
Jonas De Keuster

Hi Bill,

a select * does not work, you really need to define the attributes and their order in the query.

BEst,

Jonas

G
SN
Siddardh Namburi
·Posted in Parameters

Vaultspeed slack channel

Hey Folks, Is there a vaultspeed slack channel for community members

1
1 reply
Jonas De Keuster

Hi Siddardh, no slack channel atm :)

G
SB
Stephane Beaulieu
·Posted in Snowflake

FMC Generic - How to generate JSON Files?

We are using dbt Cloud as a FMC. so we need to set FMC Type to Generic.

Based on info found here: Generic FMC - VaultSpeed Public Documentaton - VaultSpeed Documentation

It is said that

"The Generic FMC will deliver all the necessary logic and code for a proper FMC implementation.
The resulting code will contain all the mappings for managing the metadata and JSON files describing the order in which the mappings can be executed."

It is not indicated how those JSON files are generated.

Is someone could tell us if it is possible to generate those files and how?

Thanks

Stephane

dbt Cloud
Generic FMC
2 replies
SM
SB

Thank Stijn. We've figured it out, and now it is working like a charm 😃

G
SB
Stephane Beaulieu
·Posted in Parameters

Full load- Snowflake Tables

Hi,

Our source tables are full loaded, (no CDC, then) in Snowflake DB. We have set parameters to be sure that etl will retrieve data from our source schema table, but for some reason, the ETL generated code is trying to retrieve data from CDC_SourceTableName.. Even worse, the ETL process failed, because it can't find CDC_SourceTableName.

To make test, we have clone our source tables with prefix CDC_, and the ETL process ran successfully, but no data have been loaded in RDV tables...

Here are relevant parameters config:

Have we missed something?

Thanks

Stephane

2 replies
Jonas De Keuster

Hi Stephane.

We have 2 types of loads. INIT and INCR mappings.
I understand you were running the INCR mappings?

by design the INCR runs take data from the CDC schema's
So even if you use CDC setup with Full load with delete management, it will read the data for INCR loads there.

You can set the schema parameters for this source in such a way that it reads the data from your desired schema. You can play with the schema parameters to influence where the data should be read.

This pages gives an overview: https://docs.vaultspeed.com/space/VPD/3013804066/VaultSpeed+Loading+Logic

It is always recommended to use the INIT scripts for the first run, and then use the INCR ones for subsequent runs. It sounds like in your case, both need to source data from the same schema, so you'd need to set the schema names accordingly.

Lmk if you have further questions.

SB

Thanks Jonas! In fact, we try an INIT mapping since it is our first load. After verification, our parameter was good, but except for one mentionning CDC prefix. I'll remove it and see what's happen.

The link is helpful too.

G
Jonas De Keuster
Jonas De Keuster
·Posted in Releases

Release 5.7.3 | Enhanced DataOps & GIT integration, Databricks DLT support

VaultSpeed version 5.7.3 brings significant enhancements to the Data Product Development Lifecycle. This update allows for the use of multiple custom deployment scripts and integrates GIT branch deployment. Additionally, it introduces support for Databricks Delta Live Tables (DLT), which further optimizes the execution and management of data products.

https://vaultspeed.com/resources/articles/release-5.7.3

G