Post-hook Snowflake and Macros

Help
,
1

Hi

I am struggling to get a post-hook to work after i create a model.

I have a table:

models/liabilities/stg_liability_benchmarks_dates.sql

which gives me a dates and references to run a procedure in snowflake. I envoke the run of the procedure with a macro in dbt:

{% macro iq_model_run_liability_benchmark() %}
{{ log(“Running iq_model_run_liability_benchmark”, info=True) }}
{% set table_ref = ref(‘stg_liability_benchmarks_dates’) %}
{% set query %}
SELECT LBP_ID, EFFECTIVE_DATE
FROM {{ table_ref }}
{% endset %}
{% set results = run_query(query) %}

{% for row in results.rows %}
    {% set sql_statement %}
        CALL IGG_IQ_DEV.IQ_MODEL.RUN_TASK_LIABILITY_BENCHMARK(True,'{{ row["EFFECTIVE_DATE"] }}','{{ row["LBP_ID"] }}');
    {% endset %}

    {% do run_query(sql_statement) %}
{% endfor %}

{% endmacro %}

This works if i manually run-operation iq_model_run_liability_benchmark after the model stg_liability_benchmarks_dates has run. However i need this to run in sequence of the rest of the models. Namely the macro to run after the stg_liability_benchmarks_dates is finished executing.

I have tried post-hook as a configuration and in the dbt_project.yml as per this:

liabilities:
  +materialized: table
  +schema: liabilities # Matches the folder and schema name
  stg_liability_benchmarks_dates:
    +post-hook: "{{iq_model_run_liability_benchmark()}}"

But i get the error: Encountered an error:
Compilation Error in model stg_liability_benchmarks_dates (models/liabilities/stg_liability_benchmarks_dates.sql)
‘None’ has no attribute ‘table’

Any idea how to get this to run as a post-hook operation?

2

its a compilation error. Could you please share ur model code

3

Name your project! Project names should contain only lowercase characters

and underscores. A good package name should reflect your organization’s

name or the intended use of these models

name: “igg_iq”
version: “1.0.0”
config-version: 2

This setting configures which “profile” dbt uses for this project.

profile: “default”

These configurations specify where dbt should look for different types of files.

The model-paths config, for example, states that models in this project can be

found in the “models/” directory. You probably won’t need to change these!

model-paths: [“models”]
analysis-paths: [“analyses”]
test-paths: [“tests”]
seed-paths: [“seeds”]
macro-paths: [“macros”]
snapshot-paths: [“snapshots”]

target-path: “target” # directory which will store compiled SQL files
clean-targets: # directories to be removed by dbt clean

  • “target”
  • “dbt_packages”

Configuring models

Full documentation: Model configurations | dbt Developer Hub

In dbt, the default materialization for a model is a view. This means, when you run

dbt run or dbt build, all of your models will be built as a view in your data platform.

The configuration below will override this setting for models in the example folder to

instead be materialized as tables. Any models you add to the root of the models folder will

continue to be built as views. These settings can be overridden in the individual model files

using the {{ config(...) }} macro.

models:
igg_iq:
market_curves:
+schema: market_curves
benchmarks:
+materialized: table
+schema: benchmarks
esg_analytics:
+materialized: table
+schema: esg_analytics
platform_admin:
+schema: platform_admin
portfolio_holdings:
+materialized: table
+schema: portfolio_holdings
portfolios:
+materialized: table
+schema: portfolios
securities:
+materialized: table
+schema: securities
security_info:
+materialized: table
+schema: security_info
security_info_bbg:
+materialized: table
+schema: security_info_bbg
market_bond_curves:
+materialized: table
+schema: market_bond_curves
market_derivatives_ois_curves:
+materialized: table
+schema: market_derivatives_ois_curves
market_derivatives_inflation_curves:
+materialized: table
+schema: market_derivatives_inflation_curves
oversight:
+materialized: table
+schema: oversight
liabilities:
+materialized: table
+schema: liabilities
stg_liability_benchmarks_dates:
+materialized: table
+post-hook: “{{ iq_model_run_liability_benchmark() }}”
balance_sheets:
+materialized: table
+schema: balance_sheets
analytics:
+materialized: table
+schema: analytics
LDI_characteristics:
+materialized: table
+schema: LDI_characteristics
fee_data:
+materialized: table
+schema: fee_data
packages:

  • package: dbt-labs/dbt_utils
    version: 0.9.6
4

Thanks for your help @Surya