Logical Import Layer¶

snowform_logical_import_layer puts tables and views from imported databases into your own structure. You map each source object to a target database, schema and name, for example to collect all product data from several shares in one PRODUCTS schema. Consumers then only need access to your databases, and the shares stay an internal detail.

How It Works¶

By default the module creates a view for every mapping. A plain CREATE VIEW would lose the column comments of the source, so the module deploys a small Python stored procedure, CREATE_VIEW_WITH_COLUMN_COMMENTS. It reads the comments from the source’s INFORMATION_SCHEMA and creates the view with them. Terraform calls it once per mapping, and creates all views again whenever the procedure changes.

With resource_type = "dynamic_table" you get dynamic tables instead, which store the data and refresh on a schedule.

The roles in database_role_grants get USAGE on the target database and on all current and future schemas in it, plus SELECT on all current and future views.

Before You Start¶

The source objects and the target databases and schemas have to exist already.
The procedure is a preview resource in the provider. Enable it on the sysadmin provider:
```
preview_features_enabled = ["snowflake_procedure_python_resource"]
```
Put the schema that holds the procedure in the module’s depends_on. Otherwise Terraform can try to create the procedure before the schema exists.

Usage¶

module "logical_import_layer" {
  source = "github.com/inovex/snowform_logical_import_layer.git?ref=0.0.5"

  procedure_database = "COMMON"
  procedure_schema   = "COMMON"

  source_to_target_mappings = {
    "CUSTOMER_LEADS_IMPORTED_DB.SNOWFORM_SCHEMA.CUSTOMER_LEADS" = {
      target_database = "IMPORTED_INOVEX"
      target_schema   = "CUSTOMER_DATA"
      target_name     = "CUSTOMER_LEADS"
    }
  }

  database_role_grants = {
    "IMPORTED_INOVEX" = [snowflake_account_role.consumer.name]
  }

  providers = {
    snowflake.sysadmin      = snowflake.sysadmin
    snowflake.securityadmin = snowflake.securityadmin
  }
  depends_on = [snowflake_schema.common_common_schema]
}

Inputs¶

Name	Description
`procedure_database`, `procedure_schema`	Where the procedure is created. Required.
`source_to_target_mappings`	Map from the fully qualified source (`DB.SCHEMA.OBJECT`) to `{ target_database, target_schema, target_name }`. Required.
`database_role_grants`	Map from target database to the roles that get read access. Every database needs at least one role. Required.
`resource_type`	`"view"` (default) or `"dynamic_table"`.
`dynamic_table_warehouse`	Warehouse for refreshing dynamic tables. Needed for `"dynamic_table"`.
`dynamic_table_lag_duration`	Maximum staleness, like `"5 minutes"` or `"2 hours"`.
`dynamic_table_lag_downstream`	Refresh only when downstream dynamic tables refresh. Default `false`.

The outputs list the procedure name, the created views or dynamic tables, and the granted databases and roles.

Known Limitations¶

The read grants only cover views. With resource_type = "dynamic_table", grant SELECT on the dynamic tables yourself.
If the procedure fails, it returns an error message instead of raising an error. Terraform then reports success even though the view was not created, so check the views after the first deploy.