mitzen

Cline provide free version of Ox Alpha too.  

We can use packages from a git repository, for example -  https://github.com/kepungnzai/dbt-tutorial-setup.git To do that we can update our packages.yml in the root folder - packages.yml  packages :   - package : dbt-labs/dbt_utils     version : ">=1.3.0"   - package : dbt-labs/audit_helper     version : 0.14.0   - git : https://github.com/kepungnzai/dbt-tutorial-setup.git     revision : main then run  dbt deps And dbt get the packages from git And as you can see, the package apear under our folder - dbt_packages:-  

In dbt we can check and assess our data to see if it is stale or has not been updated for some time.  We can use the following configuration here to do it - config-> freshness. # dbt test --select "test_type:data" version : 2 sources :   - name : SNOWFLAKE_SAMPLE_DATA     database : SNOWFLAKE_SAMPLE_DATA       schema : TPCDS_SF10TCL     config :       freshness :         error_after :           count : 2           period : day     tables :       - name : store_sales We can run " dbt source freshness ".  In the first section, it error out because the day last update is 1 year 8 moths ago  We can easily bump up the number here to 3285 (9 years).  # dbt test --select "test_type:data" version : 2 sources :   - name : SNOWFLAKE_SAMPLE_DATA     database : SNOWFLAKE_SAMPLE_DATA       schema :...

In dbt we can easily create macro to automate common use tasks. One of that would be a audit log operation.  In  your folder called macro, please create a file called "audit_operation.sql". {% macro audit_operation(activity_type) %}   insert into raw . audit (run_id, TYPE_ACITIVITY) values ( '{{ 1 }}' , '{{ activity_type }}' ) {% endmacro %} And then we can use it in our dbt project like so. Instead of placing the SQL code in the commented code, we use macro.  name : jaffle_shop profile : jaffle_shop seed-paths : [ "seeds" ] model-paths : [ "models" ] macro-paths : [ "macros" ] test-paths : [ "tests" ]           # ← Make sure this exists clean-targets :   - "target"   - "dbt_packages" seeds :   jaffle_shop :     +schema : raw # on-run-start:  "insert into raw.audit (run_id, TYPE_ACITIVITY) values ('1', 'pre-hook')" on-run-start : "{{ audit_operation('macro-pre-hoo...

 dbt uses jinja as a template engine to work with file. Therefor we can configure our variable 2 ways. 1. dbt_project.yaml or vars.yml (on the project root) - we can introduce variable by using the followings - as you can see here we are introducing a variable call person.  name : jaffle_shop profile : jaffle_shop seed-paths : [ "seeds" ] model-paths : [ "models" ] macro-paths : [ "macros" ] test-paths : [ "tests" ]           # ← Make sure this exists clean-targets :   - "target"   - "dbt_packages" seeds :   # Builds seeds into '<your_schema_name>_raw'   jaffle_shop :     +schema : raw vars :   person : JohnDoe this is what your vars.yml looks like  vars :   person : JohnDoe2   sold_date_sk : 20160901 2. passing variable in from the command line using --var argument. Please note variable are passed in as json - so we need to do something like this. Please note: it is important to use double quote ...

This is quite a good place to get started with enterprising your dbt   https://www.phdata.io/blog/accelerating-and-scaling-dbt-for-the-enterprise/

We can extend our dbt capabilities through packages just like packages in other languages like nuget in dotnet. In fact, we might already been using packages already, if you are following previous tutorials where we use materialization and sources keyword - this package is called dbt_utils.  Let's see how we can confugure it :- packages :   - package : dbt-labs/dbt_utils     version : " >=1.3.0 "   - package : dbt-labs/audit_helper     version : 0.14.0 And them run " dbt deps " That's how we can install it If you're looking out for more dependencies then please go to dbt hub.https://hub.getdbt.com/ So you have the dbt util install how can you use it? Let's try    dbt_utils.equal_rowcount - this is quite a simple example to compare 2 table in your test if they have the same row count. In your model directory and look for your model that you like to compare. In my case it is model/snowflake_sample_data and then add the tests in  ...

We can use python in our dbt project as well. Here is an example of code that you can place under model folder def model ( dbt , session ):     dbt . config ( materialized = ' table ' )         source_store_sales = dbt . source ( ' SNOWFLAKE_SAMPLE_DATA ' , ' store_sales ' )         return source_store_sales This is equilvalent to : {{ config( materialized = ' view ' )}} with source_store_sales as (     select * from {{ source ( ' SNOWFLAKE_SAMPLE_DATA ' , ' store_sales ' ) }} ), renamed as (     select       *     from source_store_sales ) select * from renamed There are limitation when using python code where it only supports table and incremental. 

dbt data test is used to test validity of the data during the Transform and load stage and that can be easily setup using the followings example Please note: The model is your file name which is  " snowflake_sample_data_store_sales ". Remember dbt always use the filename as model name.  Data test models :   - name : snowflake_sample_data_store_sales     description : List of store sales records with basic cleaning and transformation applied.     columns :       - name : SS_ITEM_SK         description : The unique key for each location.         data_tests :           - not_null if you run dbt test now, you will get the following output here: Unit test  We also have unit test in dbt - that allow us to run unit test against our data. This is useful to ensure our sql used for our TL is working as expected.  You need to ensure the model name matches your filenam...

We can find the actual path of your executable by using powershell command. Normal we added some exec somewhere and forgot about it.  The powershell command can help to trace where it is, save me bunch of time trying to look for this exec. ( Get-Command dbt ) .Source